Dear Readers,

Trust you are doing well.

As you already aware, object statistics play a very important role in optimizer in choosing optimal execution plan for any query. In this post, I will be focussing on commands that we use with respect to in stats gathering. This post can be considered as a one stop reference for any application objects statistics gathering stuff.

Stats gathering

Table level:

exec dbms_stats.gather_table_stats(‘SCOTT’, ‘TAB1’);
exec dbms_stats.gather_table_stats(‘SCOTT’, ‘TAB1’, estimate_percent => 15);
exec dbms_stats.gather_table_stats(‘SCOTT’, ‘TAB1’, estimate_percent => dbms_stats.auto_sample_size , cascade => TRUE);
exec dbms_stats.gather_table_stats(‘SCOTT’, ‘TAB1’, estimate_percent => dbms_stats.auto_sample_size, method_opt => ‘for all columns size auto’, cascade => TRUE, degree => 16);

Table partition/subpartition level:

exec dbms_stats.gather_table_stats ( ownname => ‘SCOTT’, tabname => ‘TAB1’, partname => ‘TAB_AUG2023′, method_opt=>’for all indexed columns size 1’, GRANULARITY => ‘APPROX_GLOBAL AND PARTITION’, degree => 8);
exec dbms_stats.gather_table_stats ( ownname => ‘SCOTT’, tabname => ‘TAB1’, partname => ‘TAB_AUG2023’, GRANULARITY => ‘PARTITION’, degree => 8);
exec dbms_stats.gather_table_stats ( ownname => ‘SCOTT’, tabname => ‘TAB1’, partname => ‘TAB_AUG2023’, GRANULARITY => ‘ALL’, degree => 8);
exec dbms_stats.gather_table_stats ( ownname => ‘SCOTT’, tabname => ‘TAB1’, partname => ‘TAB_AUG2023’, GRANULARITY => ‘SUBPARITITION’, degree => 8);

Index level:

EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘IND_TAB1_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘IND_TAB1_PK’, estimate_percent => 15);

Schema level:

exec dbms_stats.gather_schema_stats(‘SCOTT’);
exec dbms_stats.gather_schema_stats(‘SCOTT’, estimate_percent => 15);
exec dbms_stats.gather_schema_stats(‘SCOTT’, estimate_percent => 15, cascade => TRUE);
exec dbms_stats.gather_schema_stats( ownname => ‘SCOTT’, method_opt => ‘FOR ALL COLUMNS SIZE 1’, granularity => ‘ALL’, degree => 8, cascade => TRUE);

DB level:

exec dbms_stats.gather_database_stats;
exec dbms_stats.gather_database_stats(estimate_percent => 15);
exec dbms_stats.gather_database_stats(estimate_percent => 15, cascade => TRUE);

Histogram statistics

Histogram creation:

exec dbms_stats.gather_table_stats (ownname => ‘SCOTT’, tabname => ‘TAB1’, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => ‘SCOTT’, tabname => ‘TAB1’, method_opt => ‘FOR ALL INDEXED COLUMNS SIZE AUTO’, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => ‘SCOTT’, tabname => ‘TAB1’, method_opt => ‘FOR ALL HIDDEN COLUMNS SIZE AUTO’, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => ‘SCOTT’, tabname => ‘TAB1’, method_opt => ‘FOR COLUMNS COLUMN1,COLUMN2 SIZE AUTO’, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => ‘SCOTT’, tabname => ‘TAB1’, method_opt => ‘FOR ALL COLUMNS SIZE 10’, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => ‘SCOTT’, tabname => ‘TAB1’, method_opt => ‘FOR ALL COLUMNS SIZE REPEAT’, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => ‘SCOTT’, tabname => ‘TAB1’, method_opt => ‘FOR ALL COLUMNS SIZE SKEWONLY’, cascade => TRUE);

Histogram deletion:

EXEC DBMS_STATS.DELETE_COLUMN_STATS(ownname => ‘SCOTT’, tabname => ‘TAB1’,”);
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘SCOTT’, tabname => ‘TAB1′,method_opt=>’FOR COLUMNS SIZE 1’);
EXEC DBMS_STATS.DELETE_COLUMN_STATS(ownname => ‘SCOTT’, tabname => ‘TAB1′,method_opt=>”,COL_STAT_TYPE=>’HISTOGRAM’);

Statistics export/import

Statistics table creation

exec dbms_stats.create_stat_table(ownname => ‘SCOTT’, stattab => ‘STATS_TABLE_SCOTT’, tblspace=>’USERS’);

Statistics export

exec dbms_stats.export_database_stats(statown => ‘SCOTT’ ,stattab=>’STATS_TABLE_SCOTT’);
exec dbms_stats.export_table_stats(ownname=>’SCOTT’, tabname=>’TAB1′, statown =>’SCOTT’,stattab=>’STATS_TABLE_SCOTT’, cascade=>true);
exec dbms_stats.export_schema_stats(ownname=>’SCOTT’, statown =>’SCOTT’ , stattab=>’STATS_TABLE_SCOTT’);
exec dbms_stats.export_index_stats(ownname=>’SCOTT’, indname=>’IND_TAB1_PK’, statown =>’SCOTT’,stattab=>’STATS_TABLE_SCOTT’);

Statistics import

exec dbms_stats.import_database_stats(statown => ‘SCOTT’ ,stattab=>’STATS_TABLE_SCOTT’);
exec dbms_stats.import_table_stats(ownname=>’SCOTT’, tabname=>’TAB1′, statown =>’SCOTT’,stattab=>’STATS_TABLE_SCOTT’, cascade=>true);
exec dbms_stats.import_schema_stats(ownname=>’SCOTT’, statown =>’SCOTT’ , stattab=>’STATS_TABLE_SCOTT’);
exec dbms_stats.import_index_stats(ownname=>’SCOTT’, indname=>’IND_TAB1_PK’, statown =>’SCOTT’,stattab=>’STATS_TABLE_SCOTT’);

Statistics copy

exec dbms_stats.copy_table_stats (‘SCOTT’,’TAB1′,’TAB_JUL2023′,’TAB_AUG2023′);

Lock/unlock statistics

Lock statistics

exec dbms_stats.lock_schema_stats(‘SCOTT’);
exec dbms_stats.lock_table_stats(‘SCOTT’, ‘TAB1’);
exec dbms_stats.lock_partition_stats(‘SCOTT’, ‘TAB1’, ‘TAB_AUG2023’);

Unlock statistics

exec dbms_stats.unlock_schema_stats(‘SCOTT’);
exec dbms_stats.unlock_table_stats(‘SCOTT’, ‘TAB1’);
exec dbms_stats.unlock_partition_stats(‘SCOTT’, ‘TAB1’, ‘TAB_AUG2023’);

Delete stats

exec dbms_stats.delete_database_stats;
exec dbms_stats.delete_schema_stats(‘SCOTT’);
exec dbms_stats.delete_table_stats(‘SCOTT’, ‘TAB1’);
exec dbms_stats.delete_column_stats(‘SCOTT’, ‘TAB1’, ‘TAB_COL1’);
exec dbms_stats.delete_index_stats(‘SCOTT’, ‘IND_TAB1_PK’);

Some useful command to check information of current statistics

Check statistics for a table:

select owner,table_name,partition_name,subpartition_name,last_analyzed,num_rows,stale_stats,stattype_locked from dba_tab_statistics where owner=’&SCHEMA_NAME’ and table_name=’&TABLE_NAME’;

Check stale stats for indexes for a particular table:

select owner,table_name,partition_name,subpartition_name,last_analyzed,num_rows,stale_stats,stattype_locked from dba_tab_statistics where owner=’&SCHEMA_NAME’ and table_name=’&TABLE_NAME’;

Check stale stats for a particular index:

select owner,table_name,partition_name,subpartition_name,last_analyzed,num_rows,stale_stats,stattype_locked from dba_tab_statistics where owner=’&SCHEMA_NAME’ and index_name=’&INDEX_NAME’;

Check whether table stats locked or not:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner=’&SCHEMA_NAME’ and table_name=’&TABLE_NAME’;

Schema wise stats gathering status:

select owner,trunc(last_analyzed),count(0) from dba_tab_statistics where owner=’&SCHEMA_NAME’ group by owner,trunc(last_analyzed) order by 2,3;

Hope so u will find this post very useful:-)

Stay tuned for Fixed/Dictinory objects statistics gathering – one stop reference , in which I will provide different commands to work with fixed/dictinory statistics.

Cheers

Regards,
Adityanath