Re: gather_plan_stats hint
Date: Thu, 14 May 2015 19:27:03 +0200 (CEST)
Message-ID: <371537974.510336.1431624423472.JavaMail.open-xchange_at_app04.ox.hosteurope.de>
Hi Mladen,
hmm, sorry but to me it seems like you just read some parts of the OP's or my sentences and then interpret your opinion into it.
> The OP says that the problem manifests itself by changing the execution time from seconds to 40 minutes.
Did he write this? I just can read the following "When it is bad it does not finish even after 30/40 mins or even hours." The OP wanted to compare the
E-Rows to A-Rows and tried the gather_plan_stats approach, so if the bad one would just run 40 minutes (or less) he could use the same approach and
would not have asked here. I read his statement as the SQL runs much longer than 30/40 minutes or does not finish at all. In consequence a 10046 trace
would not provide the information (A-Rows vs. E-Rows) he is looking for.
> And trace file does not hide cpu usage. One of the sorting options is execpu.
I did not say this as it includes the CPU usage of course. I wrote "… it still hides a lot of details (e.g. root cause of high CPU time in complex
scenarios)" and this is true. It may hide the *root cause* of high CPU time, not the high CPU time itself. Here is a blog post of mine about such an
issue to make it more clear: http://tinyurl.com/pdgz3dm
> It's unlikely to be the plan that is a problem since the time goes up and down.
Really? Just think about the same execution plan, but with different binds and data skew. SQL runs fast for one set of binds, but slow for the other
one and so the runtime may go up and down until ECS / ACS may kick in.
Just my 2 cents.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK
> Mladen Gogala <dmarc-noreply_at_freelists.org> hat am 14. Mai 2015 um 17:16 geschrieben:
>
> 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 - 19:27:03 CEST