8 Replies Latest reply on Oct 11, 2010 6:05 AM by g777

    11g automatic stats gathering

    g777
      hi *

      would you confirm, please, if 11g doesn't have by default enabled automatic stats gathering as 10g has?
      My 2nd question is then, what is the best way to set such an automatic job to gather stats on a regular basis? If you have any script/code, please share.
        • 1. Re: 11g automatic stats gathering
          sb92075
          would you confirm, please, if 11g doesn't have by default enabled automatic stats gathering as 10g has?
          V11 has same default as V10
            1  select job_name from dba_scheduler_jobs
            2* where job_name like '%STAT%'
          SQL> /
          
          JOB_NAME
          ------------------------------
          BSLN_MAINTAIN_STATS_JOB
          MGMT_STATS_CONFIG_JOB
          Edited by: sb92075 on Oct 8, 2010 6:56 AM
          • 2. Re: 11g automatic stats gathering
            g777
            none of them counts stats as 10g
            look here: http://www.dba-oracle.com/t_gather_stats_job.htm
            There is no GATHER_STATS_JOB in version 11!

            experts, please advice and share your procedures.
            • 3. Re: 11g automatic stats gathering
              'Experts' are usually those that do read documentation.
              By simply visiting http://tahiti.oracle.com
              and typing
              Automatic statistics
              I was able to find the answer
              http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94714

              Not sure why you aren't able to do some research on your own.

              Rest assured: they are enabled by default.

              ------------------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: 11g automatic stats gathering
                Also it appears you are referring to an article by Burleson, and didn't even read it completely.
                He does provide a (as always questionable) solution.

                ------------
                Sybrand Bakker
                Senior Oracle DBA
                • 5. Re: 11g automatic stats gathering
                  Mohamed ELAzab
                  hello,
                  It is enabled by default in 11g and you don't need any scripts you just set up the window to run for example during Friday from 10 pm to 12 pm .
                  kind regards
                  Mohamed
                  • 6. Re: 11g automatic stats gathering
                    g777
                    I have read the BC workaround, but I wasn't sure what is the job's name that does it by default.
                    What do you think it is?
                    The 2 mentioned above don't seem to be right (see BC article).
                    If it DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC, then where is it called from? I cannot see it in the default installation job list.
                    Although I know that stats are running looking at the tables last_analyzed column, because I'm writing to clarify it, to be able to control it fully aware what's that animal is in 11g.

                    the simple question is - how to disable it?
                    • 7. Re: 11g automatic stats gathering
                      And the simple answer is you shouldn't do that, as there is no gain in it.
                      You are going to be a victim of Compulsive Statistics Gathering Disorder.
                      Oracle does a pretty good job at it, and whatever you are going to cook up as replacement, it will always be less robust.
                      Apart from that: Why know better than Oracle, and don't use features you pay for?
                      Also I already posted how to disable it!
                      The simple question is: Why didn't you read that bit?
                      -------------
                      Sybrand Bakker
                      Senior Oracle DBA
                      1 person found this helpful
                      • 8. Re: 11g automatic stats gathering
                        g777
                        You're right I missed the answer, thanks for your hints.
                        I know the consequences of disabling stats.
                        But one should know that the default it's not always the best, e.g. open_cursors parameter.

                        P.S. Although you gave me the answer I dislike the way you contribute to this thread. You see, you're not my boss, you're not obliged to respond in my thread, so cool down, dear friend. Don't shout with exclamations signs, please.
                        Perhaps I have no time to do a deep research and need the solution fast, and I believe this forum is also for that reason.
                        So don't question people's questions, as there are always wrong answers, not questions. If you know the answer, just pass it.

                        Thanks to all of you,