Oracle Gather Database Stats Dictionary and Fixed Object Statistics via DBMS_STATS

Hi,

I will explain how to gather database dictionary and fixed object stats in Oracle database in this article.

 

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.

 

To gather database stats, use following script.

EXEC DBMS_STATS.gather_database_stats;

 

 

 

To gather Schema stats, use following script.

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

 

To gather table stats, use following script.

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

 

To gather dictionary stats, use following script.

EXEC DBMS_STATS.gather_dictionary_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;
/

 

 

 

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

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

One thought on “Oracle Gather Database Stats Dictionary and Fixed Object Statistics via DBMS_STATS

  • May 20, 2019 at 5:21 pm
    Permalink

    I’m not on board with this post. Your rationalizations are fautless, but sadly it’s unreliable to rely on whatever others may believe. Please expand this, because I think you are an informative writer and I want to see more from you!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *