Tuesday, December 27, 2016

How to analyze a specific partition only using dbms_stats

To analyze only specific partitions, including subpartitions, use the dbms_stats.gather_table_stats while specifying the directives PART_NAME and GRANULARITY.

The following query reveals that several of the recently added subpartitions in the COMPLETED_ORDERS partition have not yet been analyzed:
SELECT P.PARTITION_NAME "partition name",P.NUM_ROWS "partition rows",P.LAST_ANALYZED "last analyzed", SP.SUBPARTITION_NAME "subpart name", SP.NUM_ROWS "subpartition rows",SP.LAST_ANALYZED "subpart last analyzed"
FROM DBA_TAB_PARTITIONS P INNER JOIN DBA_TAB_SUBPARTITIONS SP
ON (P.TABLE_NAME = SP.TABLE_NAME)
WHERE P.TABLE_NAME='ORDERS'
AND P.PARTITION_NAME = SP.PARTITION_NAME
AND P.PARTITION_NAME = 'COMPLETED_ORDERS'
AND SP.NUM_ROWS IS NULL
ORDER BY P.PARTITION_NAME DESC,SP.SUBPARTITION_NAME;

Output:

partition name partition rows last analyzed subpart name subpartition rows subpart last analyzed
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2017    
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2018    
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2019    
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2020    

To analyze them, use dbms_stats with the PART_NAME directive, and the GRANULARITY directive set to "SUBPARTITION"
BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName        => 'SCOTT'
    ,TabName        => 'ORDERS'
    ,partname       => 'COMPLETED_ORDERS'
    ,Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
    ,method_opt        => 'FOR ALL COLUMNS SIZE AUTO'
    ,Degree            => DBMS_STATS.AUTO_DEGREE
    ,Cascade           => DBMS_STATS.AUTO_CASCADE
    ,granularity       => 'SUBPARTITION'
    ,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE);
END;
/

After statistics gathering, execute the query again, but without the "AND SP.NUM_ROWS IS NULL" predicative:

partition name partition rows last analyzed subpart name subpartition rows subpart last analyzed
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2017
0
27/12/2016 09:10:04
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2018
0
27/12/2016 09:10:05
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2019
0
27/12/2016 09:10:07
COMPLETED_ORDERS
2527257
19/10/2015 07:17:44 CO_SP_2020
0
27/12/2016 09:10:08


The 12.2 version of dbms_stats.gather_table_stats is documented here

No comments:

Post a Comment