Re: gather_plan_stats hint
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Thu, 14 May 2015 01:04:22 -0400
Message-ID: <55542CD6.4070606_at_yahoo.com>
On 05/13/2015 11:01 PM, Orlando L wrote:
> All,
>
> We have a SQL performing badly in prod intermittently in 11g. When it
> runs ok it finishes fine in seconds. When it is bad it does not finish
> even after 30/40 mins or even hours.
>
> I have to find out what is going on, so I used the gather_plan_stats
> hint, but I dont see a problem in the estimate vs actual when it runs
> good. However, I am not able to see the same thing when the query is
> slow or hangs. How do I see those values for such cases. TIA.
>
> Orlando.
>
The late Douglas Adams has immortalized the number 42 as the universal answer, but in your case the answer is another number: 10046. When you have a query that occasionally creates trouble, you trace it and see what is it waiting for.
Date: Thu, 14 May 2015 01:04:22 -0400
Message-ID: <55542CD6.4070606_at_yahoo.com>
On 05/13/2015 11:01 PM, Orlando L wrote:
> All,
>
> We have a SQL performing badly in prod intermittently in 11g. When it
> runs ok it finishes fine in seconds. When it is bad it does not finish
> even after 30/40 mins or even hours.
>
> I have to find out what is going on, so I used the gather_plan_stats
> hint, but I dont see a problem in the estimate vs actual when it runs
> good. However, I am not able to see the same thing when the query is
> slow or hangs. How do I see those values for such cases. TIA.
>
> Orlando.
>
The late Douglas Adams has immortalized the number 42 as the universal answer, but in your case the answer is another number: 10046. When you have a query that occasionally creates trouble, you trace it and see what is it waiting for.
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 14 2015 - 07:04:22 CEST