Gather Optimizer Statistics Options


exec dbms_stats.gather_table_stats('tab_owner','table_name'); 

exec dbms_stats.gather_index_stats('idx_owner','index_name'); 

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD'); 

select sname, pname, pval1 from sys.aux_stats$; 

exec dbms_stats.gather_fixed_objects_stats(); 

exec dbms_stats.gather_dictionary_stats(options=> 'GATHER AUTO'); 

exec DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>480); 

exec DBMS_STATS.GATHER_SYSTEM_STATS('stop');

Gathering Workload Statistics

To gather workload statistics, perform either of the following tasks:

Run the DBMS_STATS.GATHER_SYSTEM_STATS('start') procedure at the beginning of the workload window, then the DBMS_STATS.GATHER_SYSTEM_STATS('stop') procedure at the end of the workload window.
Run DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N) where N is the number of minutes when statistics gathering is stopped automatically.

To delete system statistics, run:

dbms_stats.delete_system_stats().

Workload statistics are deleted and reset to the default noworkload statistics.

Locked Statitics


SELECT distinct 'exec dbms_stats.unlock_table_stats('''||owner||''', '''||table_name||''');' 

FROM dba_tab_statistics  

where stattype_locked = 'ALL' 

and OWNER not in ( 'SYS' , 'SYSTEM', 'WMSYS'); 



SELECT distinct 'exec dbms_stats.unlock_index_stats('''||owner||''', '''||index_name||''');' 

FROM dba_ind_statistics  

where stattype_locked = 'ALL' 

and OWNER not in ( 'SYS' , 'SYSTEM', 'WMSYS'); 



select owner, table_name 

FROM dba_tab_statistics  

where stattype_locked = 'ALL' 

and OWNER not in ( 'SYS' , 'SYSTEM', 'WMSYS');


SQL> exec dbms_stats.unlock_table_stats('tab_owner','table_name');

Optimizer Stats Gathering Preferences


declare 

a varchar2(100); 

b a%type; 

begin 

a:= dbms_stats.get_prefs('ESTIMATE_PERCENT'); 

b:= dbms_stats.get_prefs('STALE_PERCENT'); 



dbms_output.put_line('ESTIMATE_PERCENT : '||a); 

dbms_output.put_line('STALE PERCENT    : '||b); 



end; 

/ 


BEGIN 

dbms_stats.set_global_prefs('estimate_percent','DBMS_STATS.AUTO_SAMPLE_SIZE'); 

dbms_stats.set_global_prefs('stale_percent', '10'); 

dbms_stats.alter_stats_history_retention(31); 

END; 


begin 

dbms_stats.set_table_prefs(  

'icmprd',  

'&1',  

'method_opt', 
 
'for all columns size 1'); 
 
end;  

/ 




BEGIN 

dbms_stats.alter_stats_history_retention(31); 

END;

Last time Optimizer Gathering Ran


Col log_date form a20   

select * from (  

 select log_id,log_date from dba_scheduler_job_log where additional_info like '%GATHER_STATS_PROG%'  

 order by log_date desc)  

 where rownum <=5;

Ask Tom

In theory are this three command enough to gather all statistics in the database and is the more advisable on 11g?


EXEC DBMS_STATS.gather_dictionary_stats; 

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 

EXEC DBMS_STATS.GATHER_database_STATS( GATHER_SYS=>FALSE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS AUTO'); 

exec dbms_stats.gather_table_stats(owner, table_name, method_opt=>'for all columns size 1' ); -- No Histograms

Note:

You typically only need to collect fixed table stats after a major parameter change, they stay about the same size in general otherwise.
You only need to gather dictionary stats after the dictionary tables change radically - perhaps after a major application upgrade (lots of DDL)
Your application stats are something you need to worry about - not all tables will need all statistics gathered all of the time...

TABLE Level

exec DBMS_STATS.SET_TABLE_PREFS (USER,'FCT_TABLE,'INCREMENTAL','FALSE');

SELECT
owner, table_name,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'GRANULARITY') granularity,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'STALE_PERCENT') stale_percent,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'ESTIMATE_PERCENT') estimate_percent,
DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'CASCADE') cascade,
DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_tables
WHERE table_name like 'FCT%'
ORDER BY owner, table_name;

Note: Use the DBA view versus the above for better performance

select * from DBA_TAB_STAT_PREFS;

SCHEMA Level

exec DBMS_STATS.SET_SCHEMA_PREFS (USER,'STALE_PERCENT','8');

SELECT
username,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'INCREMENTAL') incremental,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'GRANULARITY') granularity,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'STALE_PERCENT') stale_percent,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'ESTIMATE_PERCENT') estimate_percent,
DBMS_STATS.get_prefs(ownname=>USER,pname=>'CASCADE') cascade,
DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dba_users
WHERE username like '%WH'
ORDER BY username;

DATABASE Level

exec DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

SELECT
DBMS_STATS.get_prefs(pname=>'INCREMENTAL') incremental,
DBMS_STATS.get_prefs(pname=>'GRANULARITY') granularity,
DBMS_STATS.get_prefs(pname=>'STALE_PERCENT') publish,
DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT') estimate_percent,
DBMS_STATS.get_prefs(pname=>'CASCADE') cascade,
DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt
FROM dual;

Fixed Table and Data Dictionary Stats

The following SQL*Plus script can be used to determine the status of these statistics. It does not check statistics for other internal schemas like for example SYSTEM as these are less critical. If needed, the WHERE-clause in the first query can to be modified accordingly.

alter session set nls_date_format='YYYY-Mon-DD';
col last_analyzed for a13
set termout off
set trimspool off
set feedback off
spool dictionary_statistics

prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner = 'SYS'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

spool off

Fixed objects are the "X$" tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when optimizer statistics are missing. The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a sub-optimal execution plan, which could cause severe performance problems in your system. It is for this reason that we strong recommend you gather fixed objects statistics.

Prior to Oracle Database 12c Release 1 fixed object statistics are not created or maintained by the automatic statistics gathering job. You can collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks. Blocks is always set to 0 since the X$ tables are in memory structures only and are not stored on disk. You must have the ANALYZE ANY DICTIONARY or SYSDBA privilege or the DBA role to update fixed object statistics.

Because of the transient nature of the X$ tables it is important that you gather fixed object statistics when there is a representative workload on the system. This may not always be feasible on large system due to additional resource need to gather the statistics. If you can’t do it during peak load you should do it after the system has warmed up and the three key types of fixed object tables have been populated:

Structural Data For example, views covering datafiles, controlfile contents, etc.
Session-based Data For example, v$session, v$access, etc.
Workload Data For example, v$sql, v$sql_plan etc.

It is recommended that you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module, or make changes to the database configuration. For example if you increase the SGA size then all of the X$ tables that contain information about the buffer cache and shared pool may change significantly, such as X$ tables used in v$buffer_pool or v$shared_pool_advice.

From Oracle Database 12c Release 1 the automatic statistics gathering job will gather statistics for fixed tables that have missing stats. For this to happen, there will need to be some time available inside the batch window after statistics for the other tables in the system have been gathered. Even with this new functionality, it is still good practice to gather fixed table stats with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS when there's a representative workload running, especially after major changes have been made to the system.

EXEC DBMS_STATS.gather_dictionary_stats;

2. Gather fixed object stats:

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

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

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);

-- STATS WITH AUTO ESTIMATION and degree 8 

exec dbms_stats.gather_schema_stats( ownname => 'DBACLASS',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');
   


   
       To use DBMS_STATS.GATHER_DATABASE_STATS in Oracle 19c, follow these steps:

1. Connect to the database: Connect to the Oracle database as a user with the necessary privileges (typically SYS or a user with the DBA role).

2. Execute the procedure: Execute the DBMS_STATS.GATHER_DATABASE_STATS procedure, like this:

BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS();
END;
/

This will gather statistics for the entire database.

Optional parameters:

- ESTIMATE_PERCENT: Specify the percentage of rows to sample (default is 100%, which means a full scan).
- METHOD_OPT: Specify the method for gathering statistics (e.g., 'FOR ALL COLUMNS SIZE AUTO').
- GRANULARITY: Specify the granularity of statistics (e.g., 'GLOBAL' or 'PARTITION').

Example with optional parameters:

BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(
    ESTIMATE_PERCENT => 10,
    METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
    GRANULARITY => 'GLOBAL'
  );
END;
/

This will gather statistics for the entire database with a 10% sample size, using the 'FOR ALL COLUMNS SIZE AUTO' method, and global granularity.

Note:

- Make sure to analyze the database regularly to ensure accurate statistics.
- You can also gather statistics for specific schemas, tables, or indexes using other DBMS_STATS procedures (e.g., GATHER_SCHEMA_STATS, GATHER_TABLE_STATS, GATHER_INDEX_STATS).
- Oracle 19c introduces new features and options for statistics gathering, so be sure to check the documentation for the latest information.
   
   
   HOME