Useful Gather Statistics Commands In Oracle

This article contains all the useful gather statistics related commands.

1. Gather dictionary stats:

-- It gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and other internal schemas.

EXEC DBMS_STATS.gather_dictionary_stats;

2. Gather fixed object stats:

--- Fixed object means gv$ or v$views


3. Gather full database stats:

EXEC DBMS_STATS.gather_database_stats;

-- With estimate_percent to 15 percent or any other value , if the db size very huge.

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

-- With auto sample size and parallel degree

EXEC DBMS_STATS.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);

4. Gather schema statistics:

EXEC DBMS_STATS.gather_schema_stats('DBACLSS');

EXEC DBMS_STATS.gather_schema_stats('DBACLASS', estimate_percent => 25);

EXEC DBMS_STATS.gather_schema_stats('DBACLASS', estimate_percent => 100, cascade => TRUE);


exec dbms_stats.gather_schema_stats( ownname => 'DBACLASS',method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'ALL', degree => 8, cascade => TRUE,

5. Gather table statistics:

EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP');
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('DBACLASS', 'EMP', estimate_percent => 15, cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'DBACLASS' , tabname => 'EMP',cascade => true,
method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 8);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'DBACLASS' , tabname => 'EMP',
cascade => true, method_opt=>'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', degree => 8);

6. Gather stats for single partition of a table:

ownname => 'SCOTT',
tabname => 'TEST', --- TABLE NAME
partname => 'TEST_JAN2016' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
degree => 8);

7. Lock/unlock statistics:

-- Lock stats of a schema:
EXEC DBMS_STATS.lock_schema_stats('DBACLASS');

-- Lock stats of a table:
EXEC DBMS_STATS.lock_table_stats('DBACLASS', 'EMP');

-- Lock stats of a partition:
EXEC DBMS_STATS.lock_partition_stats('DBACLASS', 'EMP', 'EMP');

-- unlock stats of a schema:

EXEC DBMS_STATS.unlock_schema_stats('DBACLASS');
-- unlock stats of a table:

EXEC DBMS_STATS.unlock_table_stats('DBACLASS', 'DBACLASS');
--unlock stats of a partition:

EXEC DBMS_STATS.unlock_partition_stats('DBACLASS', 'EMP', 'TEST_JAN2016');

--- check stats status:

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

8 . Delete statistics:

-- Delete complete db statistics:
EXEC DBMS_STATS.delete_database_stats;

-- Delete schema statistics:
EXEC DBMS_STATS.delete_schema_stats('DBACLASS');

-- Delete table statistics:
EXEC DBMS_STATS.delete_table_stats('DBACLASS', 'EMP');

-- Delete column statistics:
EXEC DBMS_STATS.delete_column_stats('DBACLASS', 'EMP', 'EMPNO');

-- Delete index statistics:

EXEC DBMS_STATS.delete_index_stats('DBACLASS', 'EMP_PK');

-- Delete dictionary statistics:
EXEC DBMS_STATS.delete_dictionary_stats;

-- Delete fixed object statistics:

exec dbms_stats.delete_fixed_objects_stats;

-- Delete system statistics:

exec dbms_stats.delete_system_stats('STAT_TAB');

8. Setting statistics preference:

-- View preference details for the database:

SELECT dbms_stats.get_prefs('PUBLISH') EST_PCT FROM dual;

-- View Publish preference for table

-- View Publish preference for schema:

select dbms_stats.get_prefs('PUBLISH', 'SCOTT') from dual

-- View preference details for table

select dbms_stats.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'PUBLISH') FROM DUAL;
select DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'INCREMENTAL') FROM DUAL;
select DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'GRANULARITY') FROM DUAL;
select DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'STALE_PERCENT') FROM DUAL;
select DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'ESTIMATE_PERCENT') FROM DUAL;
select DBMS_STATS.get_prefs(ownname=>'DBACLASS',tabname=>'EMP',pname=>'DEGREE') FROM DUAL;

-- Set table preferences

exec dbms_stats.set_table_prefs('DBACLASS','EMP','PUBLISH','FALSE');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','ESTIMATE_PERCENT','20');
exec dbms_stats.set_table_prefs('DBACLASS','EMP','DEGREE','8');

-- Set schema preferences:


-- Set database preference:

exec dbms_stats.set_database_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_database_prefs('DEGREE', '16');

-- Set global preference:

exec dbms_stats.set_global_prefs('PUBLISH', 'TRUE');
exec dbms_stats.set_global_prefs('DEGREE', '16');

9 . Deleting preferences :

-- Deleting schema preference:

exec dbms_stats.delete_schema_prefs('DBACLASS', 'DEGREE');
exec dbms_stats.delete_schema_prefs('DBACLASS', 'CASCADE');

-- Delete database preference:
exec dbms_stats.delete_database_prefs('ESTIMATE_PERCENT', FALSE);
exec dbms_stats.delete_database_prefs('DEGREE', FALSE);

10 . Publish pending statistics:

-- For schema DBACLASS
exec dbms_stats.publish_pending_stats('DBACLASS',null);

-- For table DBACLASS.EMP

11. Delete pending statistics:

-- for table DBACLASS.EMP
exec dbms_stats.delete_pending_stats('DBACLASS', 'EMP');

-- For schema DBACLASS
exec dbms_stats.delete_pending_stats('DBACLASS', null);

12. Upgrade stats table:

----- If we are importing stats table from higher version to lower version,
then before importing in the database, we need to upgrade the stats table.


13. View/modify statistics retention period:

-- View current stats retention

select dbms_stats.get_stats_history_retention from dual;

-- Modify the stats retention


14. create stats table:

--- Create staging table to store the statistics data

exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');

15. Export stats data:

-- Export full database stats to a table SCOTT.STAT_BACKUP

exec dbms_stats.export_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for table DBACLASS.EMP to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Export stats for schema DBACLASS to a stats table SCOTT.STAT_BACKUP

exec dbms_stats.export_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Export fixed object stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export dictionary stats to table SCOTT.STAT_BACKUP

exec dbms_stats.export_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Export stats for index DBACLAS.EMP_UK1 to SCOTT.STAT_BACKUP table

exec dbms_stats.export_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP');

16. Import stats table data:

-- Import full database stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_database_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for table DBACLASS.EMP from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_table_stats(ownname=>'DBACLASS', tabname=>'EMP', statown =>'SCOTT',stattab=>'STAT_BACKUP', cascade=>true);

-- Import stats for schema DBACLASS from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_schema_stats(ownname=>'DBACLASS', statown =>'SCOTT' , stattab=>'STAT_BACKUP');

-- Import fixed object stats from stats table SCOTT.STAT_BACKUP

exec dbms_stats.import_fixed_objects_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import dictionary stats from table SCOTT.STAT_BACKUP

exec dbms_stats.import_dictionary_stats(statown => 'SCOTT' ,stattab=>'STAT_BACKUP');

-- Import stats for index DBACLAS.EMP_UK1 from SCOTT.STAT_BACKUP table

exec dbms_stats.import_index_stats(ownname=>'DBACLASS', indname=>'EMP_UK1', statown =>'SCOTT',stattab=>'STAT_BACKUP');

17 . Few stats related sql queries:

-- Check stale stats for table:

select owner,table_name,STALE_STATS from dba_tab_statistics where owner='&SCHEMA_NAME' and table_name='&TABLE_NAME';

--Check stale stats for index:

select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner='&SCHEMA_NAME' and index_name='&INDEX_NAME';

-- For getting history of TABLE statistics
setlines 200
col owner for a12
col table_name for a21
select owner,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&TABLE_NAME';

-- Space used to store statistic data in SYSAUX tablespace:

SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';

-- 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 not in ('SYS','SYSTEM');