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

Thread: ANALYZE improves app performance how?

  1. #1
    Join Date
    Nov 2002
    Posts
    11

    ANALYZE improves app performance how?

    Before running ANALYZE statement, the application was running slow. After analyze the schema, it's working fast. Is that mean i've not written effective Sql query?

    I am not a DBA. But i like to know what will happen after analyzing the database. How often i've to ANALYZE the database schema?
    analyzing the database will decreases the database performance???

    I hope to get good reply

    Thnx

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (Keeping it simple)
    Analyze calculates statistics that the Cost Based Optimizer (CBO) uses to chose how to run your query efficiently. The effect you see has nothing to do with the quality of your query. Analyze needs to be rerun when the statistics of your tables change siginificantly - maybe daily for a system starting up (big % changes in volumes), maybe monthly for a mature system. Of course it all depends . . . Try searching on those buzz-words.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (was interrupted - she is very pretty!)
    In development - new tables should start to be Analyzed when they get a "moderate" amount of data in them (perhaps in the 100 to 1000 row range - no need when they have only 10 rows). Old tables should be re-Analyzed whenever an Index is added.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Nov 2002
    Posts
    11
    Thanx Dapi

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    taskchat, you're welcome and a nice exception - it's amazing how many "posters" don't give any feedback!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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