3 Replies Latest reply: Jan 3, 2013 9:54 AM by Rob_J RSS

    Gather Stats

    user10745179
      Hi,

      My senior asked me to gather schema stats every day after importing the schema,

      I have doubts about the gather schema stats

      Why we gather schema stats, how we gather stats in oracle 10g, 11g?

      what are parameters used while running gather stats.


      Thanks in advanced.
        • 1. Re: Gather Stats
          Fran
          Check:
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm

          exactly for your issue: dbms_stats.gather_schema_stats
          • 2. Re: Gather Stats
            sb92075
            user10745179 wrote:
            Hi,

            My senior asked me to gather schema stats every day after importing the schema,

            I have doubts about the gather schema stats

            Why we gather schema stats, how we gather stats in oracle 10g, 11g?
            With V10+ Oracle, default behavior is that statistics are gathered once every 24 hours
            • 3. Re: Gather Stats
              Rob_J
              Hi,

              Have a read of this information on [url http://www.ora00600.com/scripts/statistics/gathering_statistics.html]gathering stats in oracle and then have a read of a great article from Richard Foote about [url http://richardfoote.wordpress.com/2010/02/16/how-does-an-execution-plan-suddenly-change-when-the-statistics-remain-the-same-in-limbo/]execution plan changes when stats remain the same.

              Gathering stats is one of those hotly debated topics. Some say you should, some say you shouldn't. You need to find what works for you because, ultimately, system performance, stability and plan stability are the ultimate goals. If you are using 11g you could look at SQL Plan Management.

              Rob