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

Thread: advantage and disadvantage of analyze

  1. #1
    Join Date
    Jan 2001
    Posts
    230

    Question

    Hello:

    I would like to know that what are the advantage and disadvantage
    of analyzing the dynamic tables and indexes every day.
    Also, which optimizer_mode should I use?


    Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Check my reply to "question on CBO reliability " further down this same page.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Rule based optimization will be discontinued in the future versions of Oracle. If you are just starting out, your best bet is to stay away from it.

    The advantage to analyzing every day is you capture the most up-to-date picture of your data. The optimizer will usually perform better with recent statistics.

    The down-side is that you have to analyze every night. Analyzing takes CPU and I/O. If you have other jobs that run at night, this may introduce contention in your nightly processing.
    Jeff Hunter

  4. #4
    Join Date
    Jan 2001
    Posts
    230

    Question

    Which option is better to use?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Better is a relative term. Better for my environment is analyze once a week. Better for my last environment was analyze every day. It all depends on the frequency your data changes.
    Jeff Hunter

  6. #6
    Join Date
    Sep 2000
    Posts
    103
    Data is put into my database on a adaily basis. So, do u think I need to run analyze everyday.

    Thanks.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    [QUOTE][i]Originally posted by pst [/i]
    [B]Data is put into my database on a adaily basis. So, do u think I need to run analyze everyday.

    Thanks. [/B][/QUOTE]
    Depends on the % of data that you add. If you are only increasing the table size by 1% each day, then no, you don't have to analyze every day. That being said, the more often you can analyze, the better...
    Jeff Hunter

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