RE: No blocking session for wait event: 'Library cache pin'
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <tanel@xxxxxxxxxxxxxx>, <Mark.Bobak@xxxxxxxxxxxx>
- Date: Wed, 3 Aug 2011 11:51:02 -0400
+1 as well.
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tanel Poder
Sent: Tuesday, August 02, 2011 9:55 PM
To: Mark.Bobak@xxxxxxxxxxxx
Cc: sanjeevorcle@xxxxxxxxx; 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@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:
