Gather stats in Oracle : Schema,Tables and Indexes

In my previous articles i have given idea about the performance tuning techniques,Indexes in sql.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.

How to gather stats in Oracle?

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. As i explained in first paragraph the 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.

Optimization is process where SQL can run in efficient time.

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

I would like to start with syntax of DBMS_STATS package. As this is package and we need to use multiple system generated procedures to gather the stats. I would like to start with gathering stas for Schema.We require to use GATHER_SCHEMA_STATS procedure of DBMS_STATS package.

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

Scenario 1 : Gather stats for Schema

If we have applied or recreated indexes to multiple data tables then we require to gather stats at schema level.

exec dbms_stats.gather_schema_stats

(‘Amit_Schema’,DBMS_STATS.AUTO_SAMPLE_SIZE);

Scenario 2 : Gather Stats Percent-wise in schema :

exec dbms_stats.gather_schema_stats(ownname=>’Amit_Schema’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_schema_stats(ownname => ‘Amit_Schema’, estimate_percent => 50);

If we gather stats for a table, column, or index, if the data dictionary already containing statistics for the object, then Oracle will update the existing statistics. Oracle will save the older stats to reuse that again.

When you gather the statistics at object level oracle optimizer can re-parse the explain plan of query and will automatically chooses the explain plan of the query.

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(‘Amit_schema’, ‘Employee’);

It will gather the stats Employee table in Amit_Schema schema.

Example 2 :

sqlplus Amit_Schema/PassW@rd@12@admin

Query :

exec dbms_stats.gather_table_stats (
     ownname          => ‘Amit_Schema’,
     tabname          => ‘Employee’,
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt       => ‘for all columns size auto’,
     cascade          => true,
     degree           => 5
)
/
PL/SQL procedure successfully completed.

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 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(‘Amit_Schema’);

The above statement will delete the stats of Amit_Schema.

Gathering stats other examples :

GATHER_DATABASE_STATS
GATHER_DICTIONARY_STATS
GATHER_FIXED_OBJECTS_STATS
GATHER_INDEX_STATS
GATHER_SCHEMA_STATS
GATHER_SYSTEM_STATS
GATHER_TABLE_STATS

These are different procedures of DBMS_STATS package.

How to gather stats for partitioned schema object :

We can gather stats for partitioned schema object also. The partitioned schema object may contain multiple set of statistics. We can gather the stat using the gathering global statistics.So we require to collect global statistics of the schema.

I hope you get clear idea about the gather stats in oracle with examples. If you like this article or if you have any questions kindly comment in comments section.