- 10 Jun, 2022
- 0 Comments
- 3 Mins Read
Gather Stats In Oracle
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. |
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';
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.
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.