Re: No blocking session for wait event: 'Library cache pin'

  • From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
  • To: Mark.Bobak@xxxxxxxxxxxx
  • Date: Wed, 3 Aug 2011 04:54:35 +0300

+1 to what Mark & Marcin said (although I prefer using the V$SESSION.STATE
too, not decode the WAIT_TIME manually)

And it looks like the CPU usage comes from excessive LIOs which come from
the nested loop which is driven by all the rows that come from the
MTL_SYSTEM_ITEMS_B
table... so if you get thousands of rows from that table, you'll end up
doing the index range scan (the other child under that nested loops join)
also thousands of times. If this query worked well in past, it's probably a
stats issue (or just that there's more rows in that driving table now).

--
Tanel Poder
http://blog.tanelpoder.com/
http://www.ExpertOracleExadata.com

On Tue, Aug 2, 2011 at 11:19 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>wrote:

> Sanjeev,****
>
> ** **
>
> I think your problem here is misunderstanding your script output.  As per
> Tanel’s script, your process was ‘ON CPU’.  If it’s on cpu, it’s not
> waiting, and the wait event indicates that last thing that it waited on, but
> it has long since moved on.****
>
> ** **
>
> The key is to consider the value of V$SESSION.STATE, in combination with
> V$SESSION.EVENT.  If STATE column is anything other than ‘WAITING’, then you
> are **not** waiting, and are in fact, on the CPU.  In this case, if STATE
> is ‘WAITED SHORT TIME’, then you waited on the event recorded in EVENT for
> less than a centisecond.  If it’s ‘WAITED KNOWN TIME’, then the time you
> waited is reflected in WAIT_TIME, and if STATE is ‘WAITED UNKNOWN TIME’, you
> need to enable timed_statistics.****
>
> ** **
>
> So, you’re not really stuck on library cache pin wait, at all.****
>
> ** **
>
> Hope that helps,****
>
> ** **
>
> -Mark****
>
>
>

Other related posts: