1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

DBMS_STATS command is taking 2 days of time to UPDATE Statistics of ORACLE DATABASE

Discussion in 'Server Administration and Options' started by srinu_hp, Nov 6, 2008.

  1. srinu_hp

    srinu_hp Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    we are using the below command for updating the Statistics of all the xxxxxxxx schema objects in the ORACLE DATABASE, But the problem is It is running for more than 2 Days, it is a very huge time, can you please suggest to us for doing this process a bit faster.

    Code (Text):

    dbms_stats.gather_schema_stats
        (ownname =>'xxxxxxxxx',
         options =>'GATHER',
         estimate_percent=>dbms_stats.auto_sample_size,
         method_opt=>'for all columns size repeat',
         cascade=>true,
         degree=>15
        );
     
    in Our ORACLE Databse Server.
    Please give the Solution ASAP....
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Re: DBMS_STATS command is taking 2 days of time to UPDATE Statistics of ORACLE DATABA

    hi srinu, you have not mentioned which version you are using?

    gathering stats does take a long time, maybe even days. Even for small estimates some required operations can take large time for large tables. An interesting technique to speed things up is to gather only "stale" stats. There is an excellent article here for 9i and before. In Oracle 10g, by default stale statistics for the database are gathered once a day.

    http://www.oracle-base.com/articles/8i/RefreshingStaleStatistics8i.php
     
  3. srinu_hp

    srinu_hp Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Re: DBMS_STATS command is taking 2 days of time to UPDATE Statistics of ORACLE DATABA

    ITS oracle 10g
     
  4. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Re: DBMS_STATS command is taking 2 days of time to UPDATE Statistics of ORACLE DATABA

    Then look at the STATISTICS_LEVEL parameter.
    Code (Text):
    SQL> show parameter statistics_level

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    statistics_level                     string      TYPICAL
    If it is set either TYPICAL (which is by default) or set to ALL then you need not to worry about generating statistics manually by using DBMS_STATS package. Oracle itself will take care of when to gather statistics and when to not unless you have 20% rows insert/delete during day time.
     
  5. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Re: DBMS_STATS command is taking 2 days of time to UPDATE Statistics of ORACLE DATABA

    You are right Tyro & Arju.

    Also check how many CPU's are there in the server. I noticed that you used DEGREE=>15. There should be min of 16 CPU's in order to get the max benefit from this query.

    Regards,
    Prashant