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

Thread: CBO question. Urgent

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    CBO question. Urgent

    Guys,
    On our datawarehouse we have this montly load process. I just inherited this system so I am not a dw dba.
    For Oracle 817

    Question1. You have collected stats (estimate with 30% sample) on 400
    tables. Lets say I have a 1million row table and I truncate the whole table and reload the same data back in. What does CBO think of the stats? Is the stats old and I need to reanalyze it or would it be current?

    Question2. Lets say I just analyzed Table A which is 1 million. Exported the stats then truncated the table, reloaded the table with same data then reimported the stats back in. what does CBO think?

    thanks
    "High Salaries = Happiness = Project Success."

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    if you dont touch the stats, the cbo can only go on what it has. it will think it has the same data as before

    If you export and import the same stats then you will have the same thing - will think it is the same data.

    if you d a major change in your data you must regenerate your stats

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I would recalc the stats -- it's only in very particular circumstances that you could expect to have exactly the same physical order of rows, number of empty blocks etc., and both of these will make a difference to the stats.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    number of empty blocks etc., and both of these will make a difference to the stats.
    Dave :

    Dbms_Stats always updates this col with zero if Null.. & oracle suggests to use Dbms_Stats instead of Analyze ( which will fill in the number of blocks that are empty ).. Do u think its important??

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    Dave :

    Dbms_Stats always updates this col with zero if Null.. & oracle suggests to use Dbms_Stats instead of Analyze ( which will fill in the number of blocks that are empty ).. Do u think its important??

    Abhay.
    I'm not sure i understand your question here.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    I'm not sure i understand your question here.
    Well, my point was.. " is 'Empty Blocks' a really important fig? "

    coz if u use DBMS_STATS package to gather Table Stats, the column Empty_Blocks will be zero.. So my Q "is it a really important stat value to be considered"

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Maybe not
    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