Check IF Stats of the Indexes are Gathered but not Published

Using below query you can check if the stats on the indexes on you table are gathered but not Published :-

SELECT 'YES' pending

     , owner

     , table_name

     , index_name

     , partition_name

     , subpartition_name

     , num_rows

     , blevel

     , leaf_blocks

     , distinct_keys

     , avg_leaf_blocks_per_key

     , avg_data_blocks_per_key

     , clustering_factor

     , sample_size

     , last_analyzed

  FROM dba_ind_pending_stats

WHERE table_name = 'RA_CUSTOMER_TRX_LINES_ALL'

UNION ALL

SELECT 'NO' pending

     , owner

     , table_name

     , index_name

     , partition_name

     , subpartition_name

     , num_rows

     , blevel

     , leaf_blocks

     , distinct_keys

     , avg_leaf_blocks_per_key

     , avg_data_blocks_per_key

     , clustering_factor

     , sample_size

     , last_analyzed

  FROM dba_ind_statistics

WHERE table_name = 'RA_CUSTOMER_TRX_LINES_ALL'

ORDER BY 2

       , 3

       , 4

       , 1;



Please make sure if the stats are gathered then it should be published.

IF you see 'YES' in the resultset then run the below script to publish it.


exec dbms_stats.publish_pending_stats('AR','RA_CUSTOMER_TRX_LINES_ALL');