Re: Excessive Logical IOs against which Table/Index

From: Kellyn Pot'Vin-Gorman <dbakevlar_at_gmail.com>
Date: Mon, 25 Apr 2016 11:56:16 -0600
Message-ID: <CAN6wuX3whXVHoY0vSY9q-K5q7cQiae9Ur85CXz6qENF2o4UVUg_at_mail.gmail.com>



Correct me if I'm wrong, Stefan and Jonathan, but with her limited access, will she be able to use the SQL Monitor or tools that query ASH, (not even looking at licensing...)

I know I'm limited with what I can offer on the EM or AWR/ASH side as she would need access to this data and licenses...

Thoughts?
Kellyn

[image: Kellyn Pot'Vin on about.me]

Kellyn Pot'Vin-Gorman
about.me/dbakevlar
  <http://about.me/dbakevlar>

On Mon, Apr 25, 2016 at 11:44 AM, Stefan Koehler <contact_at_soocs.de> wrote:

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

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 25 2016 - 19:56:16 CEST

Original text of this message