Long Delay When Gathering Incremental Statistics?
Jim EganSep 21 2012 — edited Jan 22 2013I'm on 11.2.0.3.0 working with a list partition table with four partitions. It is a dimension table with 120 columns. There are 80 (single column) bitmap indexes and 2 regular indexes. All indexes are local and there are a total of about 26 million rows.
I am trying to use (and figure out) the incremental statistics feature. When I gather statistics on a specific partition it takes about 2 minutes. All four partitions gathered in series takes about 8 minutes. I use the following command for all four partitions (different partition name each time):
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'PROD', TABNAME => 'PART_DIM', PARTNAME => 'PART1', GRANULARITY => 'PARTITION', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE, DEGREE => 6);
The statistics at the partition level (ALL_PART_COL_STATISTICS) look right and the statistics at the table level (ALL_TAB_COL_STATISTICS) look OK too. But this query indicates that the table level statistics weren't "incremental". The "incremental" column value is all "no".
SELECT o.name, decode(bitand(h.spare2, 8), 8, 'yes', 'no') incremental, U.USERNAME AS TABLE_OWNER, C.NAME AS COLUMN_NAME
FROM sys.hist_head$ h, sys.obj$ o, all_users U, SYS.COL$ C
WHERE h.obj# = o.obj#
AND U.USER_ID = O.OWNER#
AND C.COL# = H.COL#
AND C.OBJ# = H.OBJ#
AND o.name = 'PART_DIM'
AND U.USERNAME = 'PROD';
What exactly is the query telling me? How bad is it that the answer is "no" instead of "yes"?
To get the answer to "yes" I have to gather statistics differently. Instead, I use this command once:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'PROD', TABNAME => 'PART_DIM', GRANULARITY => 'AUTO', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE, DEGREE => 6);
The problem is that the above command takes much longer to execute - 66 minutes instead of 8 minutes. The LAST_ANALYZED column from ALL_TAB_PARTITIONS is telling me that each partition still took it's normal 2 minutes to gather statistics. But the command doesn't come back for 58 minutes later. What is the delay?