+ Reply to Thread
Results 1 to 9 of 9
  1. 10-20-2006 02:50 AM #1
    navisd
    navisd is offline Junior Member
    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?
    Reply With Quote Reply With Quote
  2. 10-20-2006 02:54 AM #2
    Kamkan
    Kamkan is offline Junior Member
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72
    Hi,
    Could u post more details abt total rows and no of deleted rows?
    Reply With Quote Reply With Quote
  3. 10-20-2006 03:18 AM #3
    navisd
    navisd is offline Junior Member
    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.
    Reply With Quote Reply With Quote
  4. 10-20-2006 03:55 AM #4
    davey23uk
    davey23uk is offline Senior Advisor
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,323
    because the statistics you gathered werent good enough, and the RBO was just using indexes as it should
    Reply With Quote Reply With Quote
  5. 10-20-2006 05:33 AM #5
    pareshjavkar
    pareshjavkar is offline Member
    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?
    Reply With Quote Reply With Quote
  6. 10-20-2006 06:27 AM #6
    davey23uk
    davey23uk is offline Senior Advisor
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,323
    what do you have it set to at the moment
    Reply With Quote Reply With Quote
  7. 10-20-2006 07:56 AM #7
    navisd
    navisd is offline Junior Member
    Join Date
    Oct 2006
    Posts
    8
    Mode of optimizer is set to CHOOSE
    and version of oracle is 8.1.7.0
    Reply With Quote Reply With Quote
  8. 10-20-2006 08:05 AM #8
    davey23uk
    davey23uk is offline Senior Advisor
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,323
    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?
    Reply With Quote Reply With Quote
  9. 10-20-2006 11:11 AM #9
    slimdave
    slimdave is offline Foreign Script Kiddie
    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
    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