Re: Excessive Logical IOs against which Table/Index
From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Mon, 25 Apr 2016 20:12:09 +0200
Message-ID: <7a31904c-58b0-cedc-f47c-f9c56806fa16_at_bluewin.ch>
Stefan? Would be happy to be that slim. ;-) On 25.04.2016 20:04, Kellyn Pot'Vin-Gorman wrote:
> Thanks, Stefan! I missed that in the original post.
> Kellyn
>
> 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
> <mailto: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 <tel:25.04.2016%2019>: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
>
>
>
Date: Mon, 25 Apr 2016 20:12:09 +0200
Message-ID: <7a31904c-58b0-cedc-f47c-f9c56806fa16_at_bluewin.ch>
Stefan? Would be happy to be that slim. ;-) On 25.04.2016 20:04, Kellyn Pot'Vin-Gorman wrote:
> Thanks, Stefan! I missed that in the original post.
> Kellyn
>
> 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
> <mailto: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 <tel:25.04.2016%2019>: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-lReceived on Mon Apr 25 2016 - 20:12:09 CEST