Re: Excessive Logical IOs against which Table/Index

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

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

On Mon, Apr 25, 2016 at 12:02 PM, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi,
>
> I would sample logical reads from v$segment_statistics while the query is
> still runing. If you calculate the delta you should see which segment keeps
> changing.
> An other option is query v$active_session_history.
>
> Regards
>
> Lothar
>
>
> On 25.04.2016 19:10, Jessica Mason 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
>
>
>

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