Re: Which is the frequency to execute GATHER_DICTIONARY_STATS & GATHER_FIXED_OBJECTS_STATS

Good question, Juan. I think it can be boiled down to this:

1. Save hash_value, plan_hash_value, elapsed_time and executions (and perhaps cpu_time and buffer_gets) from v$sql once day or once an hour (I don't care).
2. If the plan_hash_value changed for a certain hash_value, then check if elapsed_time per execution goes up or down. If down, fine. If up, goto step 3.
3. Check if forcing the plan back to its former self fixes the problem. If yes, stop. If no, goto step 4.
4. Examine why it changed - more data? skew? extra layer of branch blocks in index? If yes, gather stats.
5. Now do the traditional T&T (Trace & Tune) dance.

If anybody is interested, I know that Morten Egan and Torben Holm (both of them Miracle-folks) have created two different utilities for checking the plan_hash_value and sending an email with the information. I think we charge nothing for them, but perhaps it's really 42 kroner. I don't know. I'm just the director.

Somewhere in the statspack tree you'll also find a script for checking if the plan_hash_value changed for a given SQL statement (you can use statspack level 6 or higher to collect this stuff).

Mogens

Juan Carlos Reyes Pacheco wrote:

>Mogens,
>I understand the advice is to trace, this means gather statistics now
>and trace now, and every month for example and see if execution plans
>goes worst, if they goes worst then is time to gather statistics
>again?
>Thank you :)
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l