dbms_stats.gather_table_stats in Oracle

I will explain dbms_stats.gather_table_stats tips in Oracle in this post.

 

dbms_stats.gather_table_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.

 

 

 

 

Gather Table Stats

Syntax of gather_table_stats is as follows.

 

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   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,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);





EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_OWNER', 'TABLE_NAME', method_opt => 'FOR COLUMNS (empno, deptno)'); 


To gather table stats, use following script.

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

 

 

Read the following post to learn more details about DBMS_STATS.

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

 

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

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 1,482 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and 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 [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply