5 Replies Latest reply: Jan 19, 2013 10:23 AM by Aman.... RSS

    Stats collection running long in 11g

    828912
      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
          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
            Run AWR OR ASH Report will show you everything.
            • 3. Re: Stats collection running long in 11g
              moreajays
              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....
                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....
                  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....