Re: gather_plan_stats hint
Date: Thu, 14 May 2015 11:16:21 -0400
Message-ID: <5554BC45.7080406_at_yahoo.com>
On 05/14/2015 06:46 AM, Stefan Koehler wrote:
> Hi Mladen,
> i got your point and i use the "Method R" approach all the time as well. But from time to time the wait interface (10046 trace) is just not enough as
> it still hides a lot of details (e.g. root cause of high CPU time in complex scenarios).
>
> The main "problem" of 10046 and execution plans is that you need to execute the SQL in full fashion to get all the details and this may be not
> possible due to ORA errors or just because of the never ending SQL.
The OP says that the problem manifests itself by changing the execution time from seconds to 40 minutes. Forty minutes is not a huge amount of time. And trace file does not hide cpu usage. One of the sorting options is execpu. If they use sar, they can pinpoint the exact time of the incident and see the CPU usage during that period.
It's unlikely to be the plan that is a problem since the time goes up and down. This sounds like a resource contention. I would still advise trace as the best tool to use. And yes, if they need the accurate numbers, they will have to let the SQL finish. Not every problem is caused by the plan. Tuning package and monitoring can only uncover plan issues. It can not and does not discover other issues, like somebody compressing a multi GB file using pbzip2 and starving everybody else of CPU. Commands like
tar cvf - /hugedir -b 262144 |pbzip2 -c|dd of=/mnt/usb30extdrive/backup/machine.tar.bz2
can not be spotted using diagnostic pack and they can and do fully saturate both IO bandwidth and CPU resources. Queries may be just slightly slower if something like the command above runs in the background.
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 14 2015 - 17:16:21 CEST