How to check whether statistics are collected successfully

You can observe whether the collection of statistics has been completed through the LAST_ANALYZED field in dba_ind_statistics and dba_tab_statistics, and only the completed collection will be recorded in these two views.

The test process is as follows (test environment 11.2.0.3 RAC):

1 create a test table with about 200w rows and the data source is dba_objects.

2 View the statistics in the view:

Select a.OWNER,a.INDEX_NAME,a.TABLE_NAME,a.LAST_ANALYZED from dba_ind_statistics a where table_name='STATEST1' and table_owner='YCR'

Select b.TABLENAMErect b.owner from dba_tab_statistics b.LASTRONERANALYZED from dba_tab_statistics b where table_name='STATEST1' and owner in ('OWNER)

Through observation, the statistics of the index are collected automatically when the index is created, and the statistics of the table are empty.

3 collect statistics manually and interrupt manually when executing for about 38 seconds (passed the test, it takes about 60 seconds for this table to collect statistics)

Begin

Dbms_stats.gather_table_stats (ownname = > 'YCR',tabname = >' STATEST1', estimate_percent = > 100, cascade = > true)

End

/

4 View the statistics in the view:

Select a.OWNER,a.INDEX_NAME,a.TABLE_NAME,a.LAST_ANALYZED from dba_ind_statistics a where table_name='STATEST1' and table_owner='YCR'

Select b.TABLENAMErect b.owner from dba_tab_statistics b.LASTRONERANALYZED from dba_tab_statistics b where table_name='STATEST1' and owner in ('OWNER)

Because the statistics have not been collected completely, the data in these two tables have not changed, and the information in the table will not be updated until the statistics are collected again.