Re: Excessive Logical IOs against which Table/Index

From: Stefan Koehler <contact_at_soocs.de>
Date: Mon, 25 Apr 2016 19:44:46 +0200 (CEST)
Message-ID: <99998954.863085.1461606287039.JavaMail.open-xchange_at_app04.ox.hosteurope.de>

Hello Jessica,
unfortunately you have not mentioned your Oracle version and OS, but several ways to identify the objects.

I guess you want to know possibilities for step 3 in case of logical I/O - so here we go:

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
4. SQLd360 using ASH (needs additional license and can be queried manually as well, but i highly recommend SQLd360 for it): https://mauro-pagano.com/2016/01/06/execution-plan-tree-temperature/

Your step 1 and 2 can be done with one tool - the well known fish called "Snapper": http://blog.tanelpoder.com/files/scripts/snapper.sql

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Jessica Mason <jessica.masson85_at_gmail.com> hat am 25. April 2016 um 19:10 geschrieben:
>
> Hello List,
>
> Last week, I was involved in a production issue, where a data load job, which normally takes few hours to complete, had been running for more than
> 48 hours. I tried to take the following systematic approach to identify the cause -
>
> Step 1 - Identify the session and started profiling it. All the time, the session was on CPU.
>
> Step 2 - To understand why the session was burning CPU, the v$sesstat view was queried and below were the top statistics that were changing :
>
> 43126075162624 logical read bytes from cache
> 240440566773 table scan rows gotten
> 2632208820 session logical reads
> 2632206511 consistent gets
> 2632206511 consistent gets from cache
> 2632205708 consistent gets from cache (fastpath)
>
> Step 3 - Next, I wanted to know the object ( table/index) against which these logical IOs were happening so that I could focus on the operations,
> involving these objects, in the execution plan but didn't know which view to query.
>
> The above information could have saved us lot to time to identify the cause ( in this case, an unique index was dropped and Oracle was doing FTS on
> a table which was referred 6 times in the query fetching million of records).
>
> So, my questions to the list is that which v$ view should I have checked?
> Or is there a better approach to troubleshoot such issues?
>
>
> Thanks
> JM

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

Received on Mon Apr 25 2016 - 19:44:46 CEST