Re: Excessive Logical IOs against which Table/Index

(1) Historical data is only stored for 21 days in the production database and this is a monthly job. So, there was no optimal plan to compare ie the whole exercise.

(2) The information was gathered from v$ views. I have only SELECT_CATALOG_ROLE granted to me for this database and have to troubleshoot all the issues using v$ views only.

Thanks
JM

On Mon, Apr 25, 2016 at 10:52 PM, Kellyn Pot'Vin-Gorman <dbakevlar_at_gmail.com
> wrote:

> I'm going to take a shot at this with questions instead of answers,
> (sorry!)
> 1. Do you have the execution plans for the optimal vs. the plan that was
> used during this execution?
> 2. Did you gather this information from simple V$views or did you trace
> the session?
>
> Thanks,
> 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: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