RE: Buffer busy waits -- p3=1012 and 1016

Steve, thank you very much for your help.

• Original Message ----- To: Diego Cutrone <dcutrone_at_afip.gov.ar>; Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, April 26, 2001 8:32 AM

> Hi Diego,
>
> There is a subtle distinction between reason codes 1012 and 1016, but the
tuning
> advice for both is to avoid contention for exclusive locks on the buffers.
> Reason code 0 means that a process had to wait for a buffer that was busy
being
> read into cache by another process.
>
> Incidentally, these reason codes have changed in 8i. The new reason codes
are
> 3-digit numbers. So far as I can tell, the first digit is 1 for consistent
gets,
> and 2 for current mode gets. The second digit is 2 when requesting an
exclusive
> lock, and 3 when requesting a shared lock on the buffer. The third digit
is
> normally 0, but can be 1 if the buffer is needed for a (consistent read)
> rollback. If anyone reading this has source code access, I would
appreciate some
> confirmation of this. Anyway, the two reason codes that you need to know
about
> for 8i are 130 (block being read into cache by another process) and 220
(waiting
> for exclusive access to the block).
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> From: Diego Cutrone [mailto:dcutrone_at_afip.gov.ar]
> Sent: Thursday, 26 April 2001 3:21
> To: Multiple recipients of list ORACLE-L
> Subject: Buffer busy waits -- p3=1012 and 1016
>
>
> Hi list,
>
> I'm analyzing parameter "p3" from trace 10046 level 8 and I've got some
doubts
> about it.
>
> What is the difference between p3=1012 and p3=1016?
> According to some docs I've read,
>
> 1012="A modification is happening on a XCUR (or SCUR)
> buffer and it has not yet completed" (and I think that
> the session suffering the wait needs the block in
> CURRENT mode, thats because it has to wait)
>
> 1016="The session wants the block in SCUR or XCUR
> mode"
> (And I think that the session that wants the block has
> to wait because the block has been modified by another session)
>
> Aren't these two p3 values the same?
>
> The only difference seems to be that in the first case
> the block is *currently* being modified by another
> session, while in the second case it has already been
> modified.
>
> am I right? Please someone correct me if I'm wrong.
>
> TIA
>
>
>
>
>
> PD: Some examples below:
>
> SOURCE P1,P2,P3 OWNER SEGMENT_NAME
> SEGMENT_TYPE
> p1=2 p2=1887 p3=1012 SYS R10 R
>
> p1=2 p2=1887 p3=1016 SYS R10 R
>
> p1=2 p2=19697 p3=1016 SYS R08 R
>
> p1=42 p2=16853 p3=0 GL GL_JE_LINES_N1 I
>
> p1=42 p2=22492 p3=0 GL GL_JE_LINES_N1 I
>
> p1=43 p2=68119 p3=0 GL GL_JE_LINES T
>
>
> What about this P3=0 case ?
> I think it means that the block wanted by the session. A is being read by
> another session (say B) from disk
> to the SGA. So session A has to wait for the read to complete.
> What can I do to eliminate (or at least improve) this wait?
> (may be to cache these table blocks in the SGA??)
>
> thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Diego Cutrone
  INET: dcutrone_at_afip.gov.ar

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).