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 statement or with the package . 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. 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 : Example 1 : It will gather the stats Employee table in abc_Schema schema.

1
2
3
4
5
6
7
8
9
10
11
12

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

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)

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

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

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

exec dbms_stats.delete_schema_stats(‘abc_Schema’);