-
dbms_stats.gather_schema_stats
hi,
What is the scenario , we require to issue
EXEC dbms_stats.gather_schema_stats(’SCOTT’, cascade=>TRUE);
-
you are gathering stats on the scott schema
-
On what case, usualy you have do this step ?Or can we do this pro actively?
-
You do that when you find out SCOTT owned objects have outdated or no longer representative performance statistics
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by gkrishn
On what case, usualy you have do this step ?Or can we do this pro actively?
oh boy ... you want to ask Jenn about it in this very same forum
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
My users are archiving many old datas.
Would it be useful executing this step after archival is complete ?
How do i know if i have to execute EXEC dbms_stats.gather_schema_stats or not?
Can i run this in business hours ?
ANy issue in over writing old statistics.
I am new and with lot of concern on how to proceed.any suggestions will be helpful
-
Please read http://www.dba-oracle.com/concepts/t...statistics.htm so you can grasp at least the basic concept.
Rule of thumbs #1
Do nothing when less than 20% of your data has changed.
Rule of thumbs #2
If your database is very small (under 20 Gig) many people just schedules a weekly job to take care of gathering statistics, most likely during the weekend.
Hope this helps.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
-
Gathering schema stats takes long time depending on how much is the schema size and what hour of the day you are doing this. At what frequency and what sample percent is again a debatable issue. But i think you may try doing it with 25% sample and weekly basis.
-
Originally Posted by malay_biswal
At what frequency and what sample percent is again a debatable issue.
Frequency - You mean to say,if its done once,next run will be faster ?eg:weekly basis
Table-Gathering statistics for a table,will touch its indexes also ?or need to do it seperatly.
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
|