Gather Database Stats , Schema Stats & Dictionary and Fixed Object Statistics via DBMS_STATS in Oracle

I will explain how to Gather Database Stats , Schema Stats & Dictionary and Fixed Object Statistics via DBMS_STATS in Oracle Database in this article. Let’s go to review how to use DBMS_STATS Gather Database Schema Stats.

DBMS_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.

DBMS_STATS Gather Database Schema Stats

You should gather database stats periodically like one time in every week. Or if you upgrade database, you must gather database stats immediately.

To gather database stats, use following script.

EXEC DBMS_STATS.gather_database_stats;

Gather Schema Stats

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

To gather Schema stats, use following script.

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

Gather Table Stats

To gather table stats, use following script.

EXEC DBMS_STATS.gather_table_stats('MEHMET', 'DEVECI', estimate_percent => 25, cascade => TRUE);

Gather Dictionary Stats

To gather dictionary stats, use following script.

EXEC DBMS_STATS.gather_dictionary_stats;

Parallel Gather Database Stats

You can gather database stats with 8 Parallel to perform it very fast.

BEGIN
DBMS_STATS.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size, degree=>8 );
END;
/

Gather All Database Stats

You can gather database, dictionary and fixed objects stats in the same script with 16 Parallel like following. Thus, all objects stats are gathered in the database and database stats will be up to date completely.

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
DBMS_STATS.gather_dictionary_stats;
DBMS_STATS.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size, degree=>16 );
END;
/

Periodic Gather Stats Job

You can create a dba job for this long running task as follows. You can create for Schema stats job which will run in 8 parallel each Sunday at 11:00 AM automatically.

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(''MSD'',estimate_percent => dbms_stats.auto_sample_size, degree=>8 ); END;',
trunc(next_day(SYSDATE,'SUNDAY'))+11/24,
'TRUNC (SYSDATE+7)+11/24');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

You can create a dba job for this long running task as follows. You can create for database stats job which will run in 16 parallel each Sunday at 11:00 AM automatically.

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_database_stats(estimate_percent => dbms_stats.auto_sample_size, degree=>16 ); END;',
trunc(next_day(SYSDATE,'SUNDAY'))+6/24,
'TRUNC (SYSDATE+7)+6/24');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )