Re: Excessive Logical IOs against which Table/Index

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 26 Apr 2016 14:32:34 +0200 (CEST)
Message-ID: <1504767219.884203.1461673954530.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hello Mladen,
please do not escalate this thread like the "Clone Tool" one ;-)

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: _at_OracleSK

> Mladen Gogala <gogala.mladen_at_gmail.com> 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)
> > 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
> Whoa! DTrace and systemtap? What has happened to the good old 10046
> 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
>
> --
> http://www.freelists.org/webpage/oracle-l

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

Received on Tue Apr 26 2016 - 14:32:34 CEST