Before going to unterstand abot Oracle gather stats we need to understand below things
2. Explain Plan
1. Optimizer :- Optimizer is one which determines the most efficent way of execution of the sql query and fast retrival of result set.
2. Expalain 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 regaring no. of rows and last analyzed.
Now we are little bit information regarding Optimizer , Explain Plan and dba_tables. These information will help us to understan the concept of gather stats.
Oracle gather stats helps optimizer to choose most efficent 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 efficent way.
When the stats are not properly updated then optimizer may execute wrong explain plan for the fast retrival of result set.
Recently we had IPL players auction. We have seen that team owner's had player's stats to judge them and buy them for their team. If they didn't had correct stats about a player then they will end up in putting more money on a player who not up to the mark.
In the same way Gather stats helps optimizer to take correct explain plan to execution the sql query in efficent way and fast retrival 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.
Example code to gaher stats
Gather stats on schema
ownname => 'SCOTT', -- Schema Name
estimate_percent => null,
block_sample => false,
method_opt => 'FOR ALL COLUMNS',
degree => null,
granularity => 'ALL',
cascade => true, -- Make sure we include indexes
options => 'GATHER' -- Gather mode
Gather stats on table
DBMS_STATS.GATHER_table_STATS ('SCOTT','EMP' );