+ Reply to Thread
Results 1 to 5 of 5
  1. 02-05-2003 09:49 AM #1
    taskchat
    taskchat is offline Junior Member
    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
    Reply With Quote Reply With Quote
  2. 02-05-2003 10:36 AM #2
    DaPi's Avatar
    DaPi
    DaPi is offline Old Cranky Philosopher
    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
    Reply With Quote Reply With Quote
  3. 02-05-2003 03:04 PM #3
    DaPi's Avatar
    DaPi
    DaPi is offline Old Cranky Philosopher
    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
    Reply With Quote Reply With Quote
  4. 02-05-2003 03:55 PM #4
    taskchat
    taskchat is offline Junior Member
    Join Date
    Nov 2002
    Posts
    11
    Thanx Dapi
    Reply With Quote Reply With Quote
  5. 02-07-2003 04:05 AM #5
    DaPi's Avatar
    DaPi
    DaPi is offline Old Cranky Philosopher
    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
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Bookmarks

Posting Permissions

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

Forum Rules

 Click Here to Expand Forum to Full Width