dbms_stats.gather_schema_stats

[gkrishn]

hi,
What is the scenario , we require to issue
EXEC dbms_stats.gather_schema_stats(’SCOTT’, cascade=>TRUE);

[davey23uk]

you are gathering stats on the scott schema

[gkrishn]

On what case, usualy you have do this step ?Or can we do this pro actively?

[PAVB]

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

[gkrishn]

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

[PAVB]

You do that when you find out SCOTT owned objects have outdated or no longer representative performance statistics

[PAVB]

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.

[gkrishn]

Very much . Thank you!!!

[malay_biswal]

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.

[gkrishn]

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.