I will explain Gather Schema Stats using DBMS_STATS.GATHER_SCHEMA_STATS in Oracle in this post.
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);
EXEC DBMS_STATS.gather_schema_stats('MEHMET', estimate_percent => 25, cascade => TRUE);
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(''MEHMET'',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; /
Read the following post to learn more details about DBMS_STATS.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )