Auto optimizer stats not collecting when it should?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Auto optimizer stats not collecting when it should?

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    Auto optimizer stats not collecting when it should?

    We have a Warehouse database, and I have noticed that the rows in some particular tables have increased significantly but the automated gather stats job has not analyzed stats for these tables. For example, one table has increased from 10 millions to 22 million rows, the last_analyzed is 19th April 2010. I have checked the optimizer job is running and it does every night, see below:
    CLIENT_NAME JOB_STATUS
    ---------------------------------------------------------------- ------------------
    JOB_START_TIME
    ---------------------------------------------------------------------------
    JOB_DURATION
    ------------------------------
    auto optimizer stats collection SUCCEEDED
    28-FEB-11 22.00.02.231000 EUROPE/LISBON
    +000 00:05:42

    auto optimizer stats collection SUCCEEDED
    01-MAR-11 22.00.02.229000 EUROPE/LISBON
    +000 00:04:50

    auto optimizer stats collection SUCCEEDED
    02-MAR-11 22.00.01.397000 EUROPE/LISBON
    +000 00:04:43

    The stale_percent is 10%.
    SQL> select dbms_stats.get_param('stale_percent') from dual;

    DBMS_STATS.GET_PARAM('STALE_PERCENT')
    ---------------------------------------------------------------
    10

    Does anyone know what it could be?
    Its Oracle version 11.1.0.7.0
    Thanks.
    Regards Sheryl Smith

  2. #2
    Join Date
    Nov 2010
    Posts
    8
    According to http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm
    monitoring should be activated on the tables to be included for gather auto/stale

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Found out that the stats on these tables had been previously locked by the DBA before.
    The 'auto optimizer stats collection job' will gather stats on all objects. You don't need to specifically set monitoring on any tables.
    Thanks.
    Regards, Sheryl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width