On 04/26/2016 08:32 AM, Stefan Koehler wrote:
Hello Mladen,
please do not escalate this thread like the "Clone Tool" one ;-)
This gap is filled by oratop, SQL*Developer, Toad, tora or any other tool. The problem is just in the V$SQL_PLAN. If it ran for 48 hours, there was sufficient time to catch it in action. Real time monitoring needs Oracle license. Even the good, old "explain plan" with /*+ GATHER_PLAN_STATISTICS */ would do the trick. This is sort of a trivial problem that doesn't require a fancy solution. Fixing under-performing SQL is what a DBA does.
10046 trace does not help in this situation as you only get the CPU consumption
break-down to object / plan steps after the SQL has finished (in
execution plan dump). I use Method R as well all the time, but it simply does
not work when a SQL runs like forever (like in the case of Jessica) and
you want these details right now. This gap is filled by Real-Time SQL
Monitoring to a specific point.
SystemTap and DTrace do not intercept system calls at all or trace system calls
in case of logical I/Os. Logical I/Os are processed in user space
(Oracle code). Luca Canali already provides a SystemTap script to translate the
"cryptical" logical I/O function (parameters) into a readable output
like "tbs# rfile# block# obj#" - you just have to check the link that i
provided.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Mladen Gogala <gogala.mladen@xxxxxxxxx> hat am 26. April 2016 um 13:51
geschrieben:
On 04/25/2016 01:44 PM, Stefan Koehler wrote:
1) SQL Monitoring Report (needs additional license)Whoa! DTrace and systemtap? What has happened to the good old 10046
2) DTrace for
Solaris:https://alexanderanokhin.wordpress.com/2011/11/13/dynamic-tracing-of-oracle-logical-io/
3) SystemTap for
Linux:https://db-blog.web.cern.ch/blog/luca-canali/2014-12-life-oracle-io-tracing-logical-and-physical-io-systemtap
trace? I can wholeheartedly recommend an ancient book called "Optimizing
Oracle for Performance" by Cary Millsap and Jeff Holt. That book
describes the tuning methodology, in detail. I find systemtap
information less than useful. The OS tracing tools intercept system
calls and can summarize what types of calls were made and what were the
arguments, as well as which routine has made the call. Unless there is a
very good knowledge of Oracle source and naming scheme, the result is
pretty much meaningless.
I am still sticking to the method developed by Cary and Jeff, as it is a
still unsurpassed tuning methodology. Admittedly, their book is a bit
old, written for the version 8i, but still equally as relevant as it was
on the day it was released.
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
--
//www.freelists.org/webpage/oracle-l
