Database Reference
In-Depth Information
Once all the synopses are in place, the
dbms_stats
package uses the monitoring information to know which
partition (or subpartition) was modified and, therefore, requires new object statistics. Therefore, when using
incremental statistics, you should
not
target the partitions (or subpartition) that changed. Instead, you let the
dbms_stats
package find out what it needs to do. The following example, based on the
incremental_stats.sql
script,
illustrates (take a closer look at the
last_analyzed
timestamp to know which objects the statistics were gathered on):
SQL> SELECT object_type || ' ' || nvl(subpartition_name, partition_name) AS object,
2 object_type, num_rows, blocks, avg_row_len,
3 to_char(last_analyzed, 'HH24:MI:SS') AS last_analyzed
4 FROM user_tab_statistics
5 WHERE table_name = 'T'
6 ORDER BY partition_name, subpartition_name;
OBJECT OBJECT_TYPE NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------- ------------ -------- ------- ----------- -------------
SUBPARTITION Q1_SP1 SUBPARTITION 1786 46 116 14:52:22
SUBPARTITION Q1_SP2 SUBPARTITION 2173 46 116 14:52:22
PARTITION Q1 PARTITION 3959 92 116 14:52:22
SUBPARTITION Q2_SP1 SUBPARTITION 1804 46 116 14:52:22
SUBPARTITION Q2_SP2 SUBPARTITION 2200 46 116 14:52:22
PARTITION Q2 PARTITION 4004 92 116 14:52:22
SUBPARTITION Q3_SP1 SUBPARTITION 1815 46 116 14:52:22
SUBPARTITION Q3_SP2 SUBPARTITION 2233 46 116 14:52:22
PARTITION Q3 PARTITION 4048 92 116 14:52:22
SUBPARTITION Q4_SP1 SUBPARTITION 1795 46 116 14:52:22
SUBPARTITION Q4_SP2 SUBPARTITION 2194 46 116 14:52:22
PARTITION Q4 PARTITION 3989 92 116 14:52:22
TABLE TABLE 16000 368 117 14:52:22
SQL> INSERT INTO t SELECT * FROM t SUBPARTITION (
q1_sp1
);
SQL> execute dbms_stats.gather_table_stats(ownname => user, tabname => 't',
granularity=>'all'
)
SQL> SELECT object_type || ' ' || nvl(subpartition_name, partition_name) AS object,
2 object_type, num_rows, blocks, avg_row_len,
3 to_char(last_analyzed, 'HH24:MI:SS') AS last_analyzed
4 FROM user_tab_statistics
5 WHERE table_name = 'T'
6 ORDER BY partition_name, subpartition_name;
OBJECT OBJECT_TYPE NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------- ------------ -------- ------ ----------- -------------
SUBPARTITION Q1_SP1 SUBPARTITION
3572
110 116
14:54:39
SUBPARTITION Q1_SP2 SUBPARTITION 2173 46 116 14:52:22
PARTITION Q1 PARTITION
5745
156 116
14:54:40
SUBPARTITION Q2_SP1 SUBPARTITION 1804 46 116 14:52:22
SUBPARTITION Q2_SP2 SUBPARTITION 2200 46 116 14:52:22
PARTITION Q2 PARTITION 4004 92 116 14:52:22
SUBPARTITION Q3_SP1 SUBPARTITION 1815 46 116 14:52:22
SUBPARTITION Q3_SP2 SUBPARTITION 2233 46 116 14:52:22
