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

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-----
Sent: Thursday, 26 April 2001 3:21
To: Multiple recipients of list ORACLE-L

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: Steve Adams
  INET: steve.adams_at_ixora.com.au

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).