Importance of Oracle Gather Stats

Discussion in 'Oracle' started by bashamsc, Feb 11, 2013.

  1. bashamsc

    bashamsc New Member

    Joined:
    May 22, 2007
    Messages:
    51
    Likes Received:
    7
    Trophy Points:
    0
    Location:
    chennai
    Before going to unterstand abot Oracle gather stats we need to understand below things

    1. Optimizer
    2. Explain Plan
    3. dba_tables


    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

    Code:
    BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS (
      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
      );
    END;
    /
    
    Gather stats on table

    Code:
    BEGIN
    DBMS_STATS.GATHER_table_STATS ('SCOTT','EMP' );
    END;
    /
    
     
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice