DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: dbms_stat package

  1. #1
    Join Date
    Oct 2004
    Posts
    8

    dbms_stat package

    Is that a good idea to gather statistic for entire schema or only table ,index and cluster . How often we will gather the statistic of all table. Is that weekly will be good ?
    Thanks in advance

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    benchmark, benchmark, benchmark

    only you can answer those questions by testing at different levels and intervals
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about turning on monitoring and letting ther database decide when it needs to re-analyze -- this is enabled by default in 10G
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by slimdave
    How about turning on monitoring and letting ther database decide when it needs to re-analyze -- this is enabled by default in 10G
    much better answer, have u implemented in production ?

    I've been running this in dev and test (gather stale I take it) for 5 months and just got burned on a table that had not been analyzed for 2 months supposedly because it had not been affected by enough dml, with new stats the queries on that table are running like a champ now. This just happened so i have not fully done root cause - just sharing my experience
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    Monitoring was introduced in 9i.

  6. #6
    Join Date
    Jul 2002
    Posts
    335
    Originally posted by stmontgo
    much better answer, have u implemented in production ?

    I've been running this in dev and test (gather stale I take it) for 5 months and just got burned on a table that had not been analyzed for 2 months supposedly because it had not been affected by enough dml, with new stats the queries on that table are running like a champ now. This just happened so i have not fully done root cause - just sharing my experience
    Was that your thread the other day? I've never heard or seen this (but saying that most of our stuff is still RBO - don't ask why) it'll be a useful thing to bear in mind for the future.

  7. #7
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Monitoring was introduced in 9i
    It's there for tables on 8i too

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by bazza
    Was that your thread the other day? I've never heard or seen this (but saying that most of our stuff is still RBO - don't ask why) it'll be a useful thing to bear in mind for the future.
    yep, twas me, i had hoped to used gather stale stats but am now evaluating it again, it was nice as compute stats runs in 8hrs in production as opposed to 30 mins for gather stale in test and dev.
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width