-
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."
-
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
-
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.
-
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"
-
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.
-
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"
-
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
|