This discussion is archived
5 Replies Latest reply: Jan 19, 2013 8:23 AM by Aman.... RSS

Stats collection running long in 11g

828912 Newbie
Currently Being Moderated
Oracle11.2.0.1
Sun Solaris.

Stats collection running long for almost 12 hours from 6PM to 6AM daily and stats collection autotask is getting STOPPED after the window duration.

DBA_TABLES.last_analyzed is not getting updated after 2AM in the morning. I believe it is scanning some big tables, but not able to pin point the table/index.

And we've some a bunch of 100GB tables in the DB. Is there a way to find out which table it's gathering stats between 2AM and 6AM and not getting completed?


Thanks for your time!
  • 1. Re: Stats collection running long in 11g
    sb92075 Guru
    Currently Being Moderated
    user1014212 wrote:
    Oracle11.2.0.1
    Sun Solaris.

    Stats collection running long for almost 12 hours from 6PM to 6AM daily and stats collection autotask is getting STOPPED after the window duration.

    DBA_TABLES.last_analyzed is not getting updated after 2AM in the morning. I believe it is scanning some big tables, but not able to pin point the table/index.

    And we've some a bunch of 100GB tables in the DB. Is there a way to find out which table it's gathering stats between 2AM and 6AM and not getting completed?


    Thanks for your time!
    1) enable SQL_TRACE
    2) query V$SESSION_LONGOPS
  • 2. Re: Stats collection running long in 11g
    TSharma-Oracle Guru
    Currently Being Moderated
    Run AWR OR ASH Report will show you everything.
  • 3. Re: Stats collection running long in 11g
    moreajays Pro
    Currently Being Moderated
    Hi,

    I would suggest to find out those tables with huge Table & last_analyzed is Null , these tables would be possible tables responsible for long run.
    Only AWR ot other SQL Hist stats tables can help you to get details.
    To fix this issue you should run stats gathering manually in day time (not in maintenance window) either with auto stats or with little estimate_percent (10 or 5)
    After getting first successful analyze (manually) , maintenance stats will pick up new incremental stats thereafter

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 4. Re: Stats collection running long in 11g
    Aman.... Oracle ACE
    Currently Being Moderated
    moreajays wrote:
    Hi,

    I would suggest to find out those tables with huge Table & last_analyzed is Null , these tables would be possible tables responsible for long run.
    Only AWR ot other SQL Hist stats tables can help you to get details.
    To fix this issue you should run stats gathering manually in day time (not in maintenance window) either with auto stats or with little estimate_percent (10 or 5)
    After getting first successful analyze (manually) , maintenance stats will pick up new incremental stats thereafter
    Ajay,

    I don't think that your statement that after the gathering the statistics manually, it would be next done incrmentally is correct. The incremental statistics are introduced only for partitioned tables AFAIK. Can you post a link from the documentation that your mentioned behavior exists?

    Aman....
  • 5. Re: Stats collection running long in 11g
    Aman.... Oracle ACE
    Currently Being Moderated
    What happens when you kill the job and try to gather the stats manually using a lesser percentage and also using more than one process using the Parallel clause? The tables that go into the stats job are those who would be considered to have the modifications done over them more than 10%. V$segment_statistics can show and dba_tab_modification_info views can put some light on it.

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points