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

Also be aware that if direct adaptive read kicks in that gets you blocks of first row pieces, for which the query may then demand non-first row pieces by db file sequential read, even if the secondary row pieces are in the same block since it was not buffer cached unless the table is in keep cache.  

I can't remember at the moment whether mtl_system_items has over 255 columns (which generates multiple row pieces even without row migration).  

A short section of extended trace will make this quite obvious if it is the root cause (as opposed to getting a newly bad plan or organic growth as per Tanel, which are both more likely).  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Tuesday, August 02, 2011 9:55 PM
To: Mark.Bobak_at_proquest.com
Cc: sanjeevorcle_at_gmail.com; ORACLE-L
Subject: Re: No blocking session for wait event: 'Library cache pin'  

+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_at_proquest.com>
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

 



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