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.

Performance Tuning

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.

ownnameThis is nothing but the schema name
tabnameName of table for gathering stats
estimate_percentEstimate 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_optThis is also default parameter which indicates FOR ALL COLUMNS SIZE AUTO.
cascadeThis statement is used to Gather statistics on the indexes for this table.
degreeThis 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.

12 Replies to “Gather stats in Oracle : Schema,Tables and Indexes”

  1. great work buddy…..!
    could you please provide info about stale stats also. it would be really helpfull.

    1. Hi pradnya,

      For any version of oracle this syntax is been used for gathering stats. I have tested it on 11 G and 12 c.

      Kindly let me know if you are facing any issues.

      Regards,
      Amit S

  2. Hi Amit,

    Great posting 👌🏻👍🏻thanks a lot for explaining in detail , no matter how many years years of experience we posses it’s worth to refresh once the concepts and this also helps in better preparing for interviews.

    now a days in google there are tons of content but always a challenge to pick the best content ,so this is best useful content totally useful and learned a lot

    keep the good work 👌👌
    Thanks Again

  3. SQL> BEGIN dbms_stats.gather_index_stats(‘t1′,’i1’); END;

    *
    ERROR at line 1:
    ORA-20000: Unable to analyze INDEX “T1”.”I1″, insufficient privileges or does
    not exist

    SQL> SQL> Connected.
    SQL>

    It should be “exec dbms_stats.gather_index_stats(‘owner’, ‘Index name’);”

    1. Hi Sudhir,

      Please connect to your DBA for giving you access of gather stats. You don’t have privileges’.

Comments are closed.