Re: Which is the frequency to execute GATHER_DICTIONARY_STATS & GATHER_FIXED_OBJECTS_STATS
- From: Mogens Nørrgaard <mln@xxxxxxxxxxxx>
- To: juancarlosreyesp@xxxxxxxxx
- Date: Thu, 24 Nov 2005 07:56:46 +0100
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 :)
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
