Statistics Turned Off
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Statistics Turned Off

  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Statistics Turned Off

    Hello... I would like to get some opinions/ideas on the advantages of turning statistics off on a production database. I am currently working on an 11g instance and just surprised to find out that we do not collect stats on it.

    Thanks for the inputs/ideas/advices.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by coyferrer View Post
    Hello... I would like to get some opinions/ideas on the advantages of turning statistics off on a production database. I am currently working on an 11g instance and just surprised to find out that we do not collect stats on it.

    Thanks for the inputs/ideas/advices.
    Scenario #1... user complains that critical query that used to be sub-second is now taking up to four minutes to complete. How do you validate that statement and check - let's say - how many buffer_gets the query is doing now and has being doing in the past if you do not have stats?

    Scenario #2... last Monday new functionality was added to the system and now users of some well established old functionalities are complaining, specially in the 7am to 9am timeframe. How do you compare early morning work-load let's say today's vs. last Wednesday if you do not have stats?

    Scenario #3... a SAN controller was upgraded and management wants to know how is this affecting I-O service levels at database side. How do you answer this question if you do not have stats?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    Hi Pavb,

    Thanks for your reply.

    I am in the same boat as you; i firmly believe in the importance of collecting stats. What I am trying to comprehend is why would anyone NOT want to do that. The response that I got so far are the ff.:

    - we know the data really well;
    - when migrating the database, we had issues with the stats so we turned it off.

    To me, those are not valid reasons. But then I am just a developer, not a DBA.

    Thoughts?

    Thanks!

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by coyferrer View Post
    Thoughts?
    Mother teached me to say nothing if I have nothing good to way so... I'm saying nothing.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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