Gather Statistics Commands
1. Gather dictionary stats:
EXEC DBMS_STATS.gather_dictionary_stats;
It gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and other internal schemas.
2. Gather fixed object stats:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_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('QRUM');
EXEC DBMS_STATS.gather_schema_stats('QRUM', estimate_percent => 25);
EXEC DBMS_STATS.gather_schema_stats('QRUM', estimate_percent => 100, cascade => TRUE);
-- STATS WITH AUTO ESTIMATION and degree 8
exec dbms_stats.gather_schema_stats( ownname => 'QRUM',method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'ALL', degree => 8, cascade => TRUE,
estimate_percent=>dbms_stats.auto_sample_size);
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:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'TEST', --- TABLE NAME
partname => 'TEST_JAN2016' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
GRANULARITY => 'APPROX_GLOBAL AND PARTITION',
degree => 8);
END;
/
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:
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','PUBLISH','FALSE');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','ESTIMATE_PERCENT','20');
exec dbms_stats.SET_SCHEMA_PREFS('DBATEST','CASCADE','TRUE');
-- 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
EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('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.
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(OWNNAME =>'RAJ',STATTAB =>'STAT_TEST');
13. View/modify statistics retention period:
-- View current stats retention
select dbms_stats.get_stats_history_retention from dual;
-- Modify the stats retention
exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);
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');
