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: @OracleSK
Mladen Gogala <dmarc-noreply@xxxxxxxxxxxxx> 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.
