Re: gather_plan_stats hint

From: Stefan Koehler <contact_at_soocs.de>
Date: Thu, 14 May 2015 12:46:55 +0200 (CEST)
Message-ID: <615272855.506908.1431600415164.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

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. As far as i understood Orlando - the latter is exactly what he faces, otherwise the gather_plan_stats approach would have been worked too. So he can just use "Real Time SQL Monitoring" or the more funky approaches like DTrace + os_explain for example.

I usually combine "Method R" with Tanel Poder's approach to overcome such situations:

1) TOP Waits / TOP SQL / Method R / 10046
2) V$SESSTAT counters
3) Process stack samples

Hope this clears what i tried to communicate.

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 12:14 geschrieben:
>
> Hi Stefan,
> Of course that the OP needs to wait for completion. The principles and
> the theory of tracing are explained in the most groundbreaking Oracle
> tuning book ever, "Optimizing Oracle for Performance" by Cary Millsap. I
> am saying that about the book, after having read all books piblished by
> Tom Kyte, Jonathan Lewis and Christian Antognini. 10046 is the way to
> go. That book answers to the question how to tune and why tune in the
> first place.

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

Received on Thu May 14 2015 - 12:46:55 CEST