Gather Schema Stats using DBMS_STATS.GATHER_SCHEMA_STATS

DBMS_STATS package was introduced in Oracle 8i and used to gather Database,table,Schema,dictionary and fixed object statistic in Oracle database.

Statistic of objects should be up to date in Oracle database for Oracle optimizer. Because Oracle optimizer uses database statistics to generate lots of execution plans in same time and If statistics are up to date ,then Optimizer decide correct execution plans.

If you have done lots of change on any schema, you need to gather schema stats.

Gather Schema stats syntax is as follows.

DBMS_STATS.GATHER_SCHEMA_STATS ( 
   ownname          VARCHAR2, 
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE, 
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), 
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL, 
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL, 
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE,
  obj_filter_list  ObjectTab DEFAULT NULL);

To gather Schema stats, use following script.

EXEC DBMS_STATS.gather_schema_stats('SCHEMA_NAME', estimate_percent => 25, cascade => TRUE);

If you want to COMPUTE the statistics (which means to actually consider every row and not just estimate the statistics), use the following syntax:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT', estimate_percent => NULL);

However, you can also just specify the name of the schema:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT');

This will use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. Find all other information (available parameters, usage) in the Oracle documentation.

Gather Schema Stats Job

You can gather schema stats periodically using the dbms jobs, thus Oracle will gather the schema jobs everytime, and the schema stats will be up to date.

You can use the following script for the schema stats job. This job will run every Sunday at 11 am.

SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN
SELECT MAX (job) + 1 INTO l_job FROM dba_jobs;
DBMS_JOB.submit(l_job,
'BEGIN DBMS_STATS.gather_schema_stats(''<schema name>'',estimate_percent => dbms_stats.auto_sample_size, degree=>32 ); END;',
trunc(next_day(SYSDATE,'SUNDAY'))+11/24,
'TRUNC (SYSDATE+7)+11/24');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

reference:https://ittutorial.org/gather-schema-stats-using-dbms_stats-gather_schema_stats-in-oracle/

Gather Schema Stats Job

Related posts: