Blog

Gather Stats In Oracle

            In any performance tuning technique we require to create multiple indexes on table. After creating the indexes the stats of objects will change.So after any of the operations to see the good results in SQL performance we require to gather stats in Oracle . After gathering the statastics it will fine tune your query and it will fetch records fast

You must gather statistics on a regular basis to provide the optimizer with information about schema objects. New statistics should be gathered after a schema object’s data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, you should collect new statistics on the number of rows. After updating data in a table, you do not need to collect new statistics on the number of rows but you might need new statistics on the average row length.

Statistics can be generated with the ANALYZE  statement or with the package DBMS_STATS.

              To gather stats in oracle we require to use the DBMS_STATS package. It will collect the statistics in parallel with collecting the global statistics for partitioned objects. The DBMS_STATS package specialy used only for optimizer statistics.

 dbms_stats is very vital for good SQL performance. We require to gather the stats before adjusting or setting up any optimizer parameters in oracle.

The less the query cost the execution time of query is fast. We must have to gather the statistics on regular basis for database object to give the best information to oracle optimizer to run queries in best possible time. Using the analyze statement is traditional way of checking the cost of query. But now a days to gather stats in oracle we need to use DBMS_STATS package.

Usages of DBMS_STATS Package :

1.To modify stats

2.To view stats

3.To delete stats

4.To export or import stats

         You must gather statistics on a regular basis to provide the optimizer with information about schema objects. New statistics should be gathered after a schema object’s data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, you should collect new statistics on the number of rows. After updating data in a table, you do not need to collect new statistics on the number of rows but you might need new statistics on the average row length.

Statistics can be generated with the ANALYZE statement or with the package DBMS_STATS.

ownname

This is nothing but the schema name

tabname

Name of table for gathering stats

estimate_percent

Estimate of percentage of rows (NULL means compute). Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.

method_opt

This is also default parameter which indicates FOR ALL COLUMNS SIZE AUTO.

cascade

This statement is used to Gather statistics on the indexes for this table.

degree

This indicates degree of parallelism. The default for degree is NONE.

Check Last Analysed date of table

col table_name for a15
select table_name, to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where owner='SYS' AND TABLE_NAME = 'TESTTABLE';

     Syntax for gathering stats for schema level:

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample, method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed)

    Gathering stats for table :

          We can collect the stats in table level. If user creates the indexes or use any partitioning technique after that we require to gather stats. We can gather stats using the gather_table_stats procedure of dbms_stats package.

Syntax :

 exec dbms_stats.gather_table_stats(‘Schema_name’, ‘Table_name’); 

Example 1 :

 exec dbms_stats.gather_table_stats(‘abc_schema’, ‘Employee’); 

It will gather the stats Employee table in abc_Schema schema.

Gathering Stats for index :

Gathering index stats are also important. We have already shown the way to gather stats of index with table. If we require to gather stats for only index then following syntax is useful.

Syntax :

exec dbms_stats.gather_index_stats(‘table_name’, ‘Index name’);

Deleting schema Stats :

We can delete the stats of schema using following statement :

exec dbms_stats.delete_schema_stats(‘abc_Schema’);

The above statement will delete the stats of abc_Schema.

Hope It Helps!

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.