Why do we need Gather Stats?

Many times we have heard about gather stat during our project implementation and maintenance. So before understanding Oracle gather stats we will go through below items first:

• Optimizer
• Explain Plan
• dba_tables

1. Optimizer :- Optimizer is one which determines the most efficient way of execution of the sql query and fast retrieval of result set.

2. Explain Plan :- Explain Plan displays the plan used by Oracle Optimizer to execution the sql query.

3. dba_tables :- Oracle dba_tables stores the information regarding all oracle tables. This table contains the information regarding number of rows and last analyzed.

Now we will understand about gather stats. Oracle gather stats helps optimizer to choose most efficient path by updating the stats of the table in the dba_tables. Optimizer uses the information available in dba_table and creates the explain plan to execute a query in a most efficient way.

When the stats are not properly updated then optimizer may execute wrong explain plan for the fast retrieval of result set.

So Oracle gather stats helps optimizer to take correct explain plan to execution the sql query in an efficient way and fast retrieval of result set.

For generating stats Oracle has stored procedure. 

We need to use:

DBMS_STATS.GATHER_SCHEMA_STATS to gather schema objects stats and DBMS_STATS.GATHER_table_STATS to gather table stats.