This discussion is archived
3 Replies Latest reply: Jan 3, 2013 7:54 AM by Rob_J RSS

Gather Stats

user10745179 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

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