Re: Excessive Logical IOs against which Table/Index

I would suggest on issues like this, to try TUNAs360 from Mauro Pagano, or SNAPPER from Tanel Poder. Both free. I see SNAPPER more session centric, which in your case that is all you needed. In the other hand, TUNAs360 is easier to use and tells you more about other sessions, and more about the SQL that you are executing on your resource-intensive session of interest.

Then, once you identify the SQL taking long, use SQLd360, which is also free and also from Mauro. The benefit of these tools is that you can share the output for others to help, and you can easily document your findings out of the same output.

Carlos

> On Apr 25, 2016, at 10:10 AM, Jessica Mason <jessica.masson85_at_gmail.com> wrote:
>
>
> 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