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

Thread: Stats Collection - Performance degradation

  1. #1
    Join Date
    Oct 2006
    Posts
    8

    Unhappy Stats Collection - Performance degradation

    I have an observation i collected the statistics on index & table but query responding slow , now when i tried to run query after deletion it gave result with in second. Why this happening?

  2. #2
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72
    Hi,
    Could u post more details abt total rows and no of deleted rows?

  3. #3
    Join Date
    Oct 2006
    Posts
    8
    Tablesize : 531 M
    Rows : 35,35,676
    Indexes Sizes varies between 70M ~ 150M.
    I rebuild all the indexes and result is same.

    how i will find the deleted rows.
    Last edited by navisd; 10-20-2006 at 03:25 AM.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    because the statistics you gathered werent good enough, and the RBO was just using indexes as it should

  5. #5
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Quote Originally Posted by davey23uk
    because the statistics you gathered werent good enough, and the RBO was just using indexes as it should
    Will
    optimizer_mode=CHOOSE Help in this regard?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what do you have it set to at the moment

  7. #7
    Join Date
    Oct 2006
    Posts
    8
    Mode of optimizer is set to CHOOSE
    and version of oracle is 8.1.7.0

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    well changing it from CHOOSE to CHOOSE wont help much

    you need to work out what stats are requried for your data

    how do uyou generate them?

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Post the explain plans with and without statistics on the table, as well as the query.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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