When DBMS_STATS.GATHER_TABLE_STATS collects statistics on a partitioned table, generally it does so at the partition and table (global) level (the default behavior can be modified by changing the GRANULARITY parameter). This is done in two steps:
- First, partition level stats are gathered by scanning the partition(s) that have stale or empty stats,
- Then a full table scan is executed to gather the global statistics.
As more partitions are added to a given table, the longer the execution time for GATHER_TABLE_STATS, due to the full table scan requited for global stats.
In this article ...
... Oracle 11g is concerned.
... It is about Partitioned Table
The following setting was used in sqlplus and in shell (export NLS_LANG=American_America.UTF8):
-- Set sqlplus format
SET LineSize 255 PageSize 255 Trim ON ;
COL Object_Name FORMAT a30 ;
COL Partition_Name FORMAT a10 ;
COL Last_Analyzed FORMAT a20 ;
COL Num_Rows FORMAT 9999 ;
COL Stale_Stats FORMAT a3 ;
ALTER SESSION SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS' ;
Create Partitioned Table
Let's create a new brand partitioned table, like the following one:
-- Create test_part_tab table
CREATE TABLE test_part_tab (
Col_Num NUMBER(5) NOT NULL,
Col_Chr CHAR(5) NOT NULL,
Col_Dat DATE )
PARTITION BY RANGE (Col_Num) (
PARTITION P_LESS02 VALUES LESS THAN (2),
PARTITION P_LESS04 VALUES LESS THAN (4),
PARTITION P_LESS06 VALUES LESS THAN (6) ) ;
/*
Table created.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics
WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB
TEST_PART_TAB:P_LESS02
TEST_PART_TAB:P_LESS04
TEST_PART_TAB:P_LESS06
*/
Here we go, now we have a brand new partitioned table without any row ans any stats.
Statistics Gathering ... before
Let see how oracle gather stats for partitioned table.
-- Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Add one row
INSERT INTO test_part_tab VALUES (1,'A1', SYSDATE) ;
COMMIT ;
/*
1 row created.
Commit complete.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics
WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/27 14:09:09 0
TEST_PART_TAB:P_LESS02 2012/02/27 14:09:09 0
TEST_PART_TAB:P_LESS04 2012/02/27 14:09:09 0
TEST_PART_TAB:P_LESS06 2012/02/27 14:09:09 0
*/
After adding one row in partition P_LESS02, statistics still show zero rows. Not good! Let's gather stats again.
-- Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics
WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/27 14:24:10 1
TEST_PART_TAB:P_LESS02 2012/02/27 14:24:10 1
TEST_PART_TAB:P_LESS04 2012/02/27 14:24:10 0
TEST_PART_TAB:P_LESS06 2012/02/27 14:24:10 0
*/
Ok now stats are up to date. However we can notice that all partitions stats and global stats have been updated whereas only one partition has been modified. In this case, it doesn't matter because it's small table. But imagine how long it can take for a huge partitioned table. You know what ... Oracle 11g has a new feature called "incremental global stats" that can improve this issue.
Incremental Global Statistics
Let's see what Oracle say about it:
INCREMENTAL - Determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:
INCREMENTAL value for the partitioned table is set to TRUE;
PUBLISH value for the partitioned table is set to TRUE;
User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.
Sounds good! Let's activate this option and see how it works:
-- Enable incremental stats for a table
EXEC DBMS_STATS.SET_TABLE_PREFS (OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', pname=>'INCREMENTAL', pvalue=>'TRUE') ;
/*
PL/SQL procedure successfully completed.
*/
-- Check incremental status
SELECT dbms_stats.get_prefs('INCREMENTAL', tabname=>'TEST_PART_TAB') "INCREMENTAL" FROM dual ;
/*
INCREMENTAL
---------------
TRUE
*/
Incremental Global Statistics is now activated on TEST_PART_TAB partitioned tables.
Let's do some test on it.
Step 1: Gather stats without adding row
-- Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Estimate_Percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/27 15:04:31 1
TEST_PART_TAB:P_LESS02 2012/02/27 15:04:31 1
TEST_PART_TAB:P_LESS04 2012/02/27 15:04:31 0
TEST_PART_TAB:P_LESS06 2012/02/27 15:04:31 0
*/
-- Gather stats again
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Estimate_Percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/27 15:22:52 1
TEST_PART_TAB:P_LESS02 2012/02/27 15:04:31 1
TEST_PART_TAB:P_LESS04 2012/02/27 15:04:31 0
TEST_PART_TAB:P_LESS06 2012/02/27 15:04:31 0
*/
Well well well ... the first time all the partitions have been analysed. This is probably because we have swithed to the incremental stats mode. And then only the global stats is gathered.
Step 2: Gather stats with adding row
-- Add one row
INSERT INTO test_part_tab VALUES (1,'A2', SYSDATE) ;
COMMIT ;
/*
1 row created.
Commit complete.
*/
-- Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 14:24:10 0
TEST_PART_TAB:P_LESS06 2012/02/27 14:24:10 0
*/
Yeah! It works. As you can see here it has updated the stats of the only partition which has changed and then get the global stats. Let's try again to make sure by adding two rows.
-- Add two rows
INSERT INTO test_part_tab VALUES (3,'C1', SYSDATE) ; INSERT INTO test_part_tab VALUES (5,'E1', SYSDATE) ;
COMMIT ;
/*
1 row created.
1 row created.
Commit complete.
*/
-- Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/27 19:35:52 4
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/02/27 19:35:52 1
*/
As expected Oracle will only scan the modified partitions and then gather the global stats based on partitions stats.
This option should greatly improve stats gathering time spend on huge partitioned table.
Going Further ...
What if we add one more partition ...
We will add some more partition and see how Oracle react.
-- Adding partition
ALTER TABLE TEST_PART_TAB ADD PARTITION P_LESS08 VALUES LESS THAN (8) ;
/*
Table altered.
*/
-- Check stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/27 19:35:52 4
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS08
*/
-- Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/28 11:41:59 4
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS08 2012/02/28 11:41:59 0
*/
Only the new added partition and the global stats are analysed .
What if we split a partition ...
We will split the first partition and see how Oracle react.
-- Adding partition
ALTER TABLE TEST_PART_TAB SPLIT PARTITION P_LESS02 AT (0) INTO (PARTITION P_LESS00, PARTITION P_LESS02) ;
/*
Table altered.
*/
-- Check stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/28 11:41:59 4
TEST_PART_TAB:P_LESS00
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS08 2012/02/28 11:41:59 0
*/
-- Gather stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/28 12:13:59 4
TEST_PART_TAB:P_LESS00 2012/02/28 12:13:59 0
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS08 2012/02/28 11:41:59 0
*/
Only the new splitted partition and the global stats are analysed.
What if we exchange a partition ...
We will split the first partition and see how Oracle react.
-- Create a temporary table for exchange partition
CREATE TABLE tmp_exch AS SELECT * FROM test_part_tab PARTITION (p_less06) ;
/*
Table created.
*/
-- Add a new row into tmp_exch table
INSERT INTO tmp_exch VALUES (5,'E2', SYSDATE) ;
COMMIT ;
/*
1 row created.
Commit complete.
*/
-- Gather stats for tmp_exch table
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TMP_EXCH', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check tmp_exch table stats
SELECT table_name Object_Name, Last_Analyzed, num_rows FROM user_tab_statistics WHERE table_name = 'TMP_EXCH';
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TMP_EXCH 2012/03/12 18:35:11 2
*/
-- Check TEST_PART_TAB table stats
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/28 12:13:59 4
TEST_PART_TAB:P_LESS00 2012/02/28 12:13:59 0
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS08 2012/02/28 11:41:59 0
*/
-- Do an exchange partition
ALTER TABLE TEST_PART_TAB
EXCHANGE PARTITION P_LESS06
WITH TABLE TMP_EXCH
INCLUDING INDEXES
WITH VALIDATION
UPDATE GLOBAL INDEXES;
/*
Table altered.
*/
-- Check TEST_PART_TAB table stats again
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/02/28 12:13:59 4
TEST_PART_TAB:P_LESS00 2012/02/28 12:13:59 0
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/03/12 18:35:11 2
TEST_PART_TAB:P_LESS08 2012/02/28 11:41:59 0
*/
-- Gather stats for TEST_PART_TAB table
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Granularity=>'AUTO', Cascade=>TRUE) ;
/*
PL/SQL procedure successfully completed.
*/
-- Check TEST_PART_TAB table stats again
SELECT table_name||NVL2(partition_name,':'||partition_name,'') Object_Name, Last_Analyzed, num_rows
FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB' ;
/*
OBJECT_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------------- --------
TEST_PART_TAB 2012/03/12 18:48:49 5
TEST_PART_TAB:P_LESS00 2012/02/28 12:13:59 0
TEST_PART_TAB:P_LESS02 2012/02/27 19:31:15 2
TEST_PART_TAB:P_LESS04 2012/02/27 19:35:52 1
TEST_PART_TAB:P_LESS06 2012/03/12 18:48:49 2
TEST_PART_TAB:P_LESS08 2012/02/28 11:41:59 0
*/
In the exchange partition case even if the imported stats from the exchange partition is up to date, Oracle will still analysed the partition exchanged then recalculate the global statistique.
What about the indexes ...
Interresting things about local partitionned indexes is ... incremental statistic doesn't work!
For indexes, Oracle still analyze in the old way ... all the index partitions and the global index.
But we can still implement something easy to only gather index stats of modified partitions. When we gather stats with Granularity=>'AUTO', Cascade=>FALSE on a partitioned table, Oracle will flush this table monitoring information from memory into dictionary (use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to flush all monitoring information).
We can now see about the stale stats in user_ind_statistics view:
-- Check index stats
SELECT index_name object_name, partition_name, num_rows, last_analyzed, stale_stats
FROM user_ind_statistics
WHERE table_name = 'TEST_PART_TAB';
/*
OBJECT_NAME PARTITION_ NUM_ROWS LAST_ANALYZED STALE_STATS
------------------------------ ---------- -------- -------------------- -----------
PK_TEST_PART_TAB 15 2012/03/13 14:30:26 YES
PK_TEST_PART_TAB P_LESS00 0 2012/03/13 14:30:26 NO
PK_TEST_PART_TAB P_LESS02 2 2012/03/13 14:30:26 NO
PK_TEST_PART_TAB P_LESS04 1 2012/03/13 14:30:26 NO
PK_TEST_PART_TAB P_LESS06 12 2012/03/13 14:30:26 YES
PK_TEST_PART_TAB P_LESS08 0 2012/03/13 14:30:26 NO
*/
From that you can create a script to recompile only stale stats.
Conclusion ...
Oracle 11g Incremental Global Statistics On Partitioned Tables is an interresting feature which can help to reduce time consuming in stats gathering on huge partitioned table. Oracle will manage itself which partition to analyse or reanalyse depends on changes. But it doesn't work on indexes.
TODO ...
- Stats Histogram
- Issue with WRI$_OPTSTAT_SYNOPSIS$
Enjoyed this article? Please like it or share it.
Please connect with one of social login below (or fill up name and email)