Previous | Table of Contents | Next

Page 633

This output was easy to interpret because there was only one common resource between 125 and 41, and it was easy to narrow down to which resource was causing the lock problem. In a majority of cases, there will be more than one resource, which is common between two sessions, and of course one of them will be locked. In such cases, it would be difficult to use the previous output to interpret which resource is locked. In that case, you have to use the view v$sqltext, which stores the text of SQL statements belonging to the shared SQL cursors in the System Global Area (SGA). Using the previous output, find the session id of the session that is waiting to acquire the lock and then use the next select to interpret which resource the session is trying to lock and is waiting for:

Select sqltext

from v$sqltext a,v$session b
where a.address = b.sql_address
and   a.hash_value = b.sql_hash_value
and   b.sid     = 125
order by piece;


SQL_TEXT
--------------------------------------------------------

update employee set sname = `SMITH' where emp_id = `100';

This means that session 41 has already locked the employee record 100, and session 125 is trying to lock the same row. Thus, using the previous lock monitor report and the SQL statement in execution makes it easy to pinpoint which resource is locked.

Another useful view is v$session_wait, which lists the events for which active sessions are waiting. The event that is registered in v$session_wait is enqueue. Therefore, if you query v$session_wait for a session that is locked or you think is locked, you get the output in
Listing 25.2.

Listing 25.2Finding Locks on a Session Using v$session_wait
col  event format a8
col  p2text format a5
col  p2 format 999999
col  p3text format a5
                                        continues

Page 634

Listing 25.2Continued
col  p3 format 999999

col  wait_time format 999999999


col  secs format 99999


col  state format a8


col  seq# format 99999


set pagesize 24


set verify off


select event, p2text,p2,p3text,p3,seq#,wait_time,state,seconds_in_wait secs


from v$session_wait


where    sid  = 125;




   EVENT P2TEX      P2 P3TEX      P3   SEQ#  WAIT_TIME    STATE  SECS

-------- ----- ------- ----- ------- ------ ---------- -------- ------



enqueue    id1   65548   id2   91271   6845          0  WAITING   850

Columns p2 and p3 contain the values id1 and id2, which are the values that can be obtained from v$lock. The output also reports the number of seconds the session has remained locked.

TIP
When trying to interpret the output from v$session wait, always remember to check the value of the state column because it is a very important parameter. The possible values in this column are:

Waiting: Indicates the session is currently waiting for the event. When the value is Waiting in the state column, the seconds_in_wait column will contain the actual time in milliseconds for which the session waited for the event.

Waited unknown time: This value is shown when the TIMED_STATISTICS parameter is set to false.

Waited short time: Indicates that the session waited for a very brief period.

Waited known time: Indicates that the session has acquired the event it was waiting for. The wait_time column will actually contain the time for which the session waited.

Another important view, v$sysstat, contains all the important statistics of the entire system. You can use v$sysstat to get an overview of the number of times any session has waited for a lock.

Use the following SQL:

select decode(class,1,'User',

                    2,'Redo',
                    4,'Enqueue',
                    8,'Cache',
                    16,'OS',
                    32,'Par Ser',
                    64,'SQL',
                     128,'Debug') class1,

statistic#,name,value
from v$sysstat
WHERE name = `enqueue waits';

Page 635


Class    S#N Name                                      Value

------- ---- ------------------------------ ----------------


Enqueue   23 enqueue waits                             19989

From this select, you can conclude that there were 19989 waits for lock on the system. Note this counter gets incremented when the lock is released; if a session is currently waiting for a lock, the value is not reflected in the counter. If the value of enqueue waits is too high, the application needs review—and possible rescheduling—of concurrently running jobs that access the same resources.

Another useful script can be found in the $ORACLE_HOME/rbms/admin directory. This script is called utllockt.sql, and it prints the lock information in a tree format. This makes the information easier to read and makes it easier to decipher who is locking the resources.

Avoiding Locks: Possible Solutions

This section provides some general practices to avoid locking.

Some applications use control tables to generate the next serial number. A typical example of this type is the next order number in an order table. There would be a control table storing the last order number generated. Any program that needs to generate a new order number would select from this table the last order number generated and then increment it by one. After the last order number is incremented it is updated back to the control table.

In a multiuser environment, where there could be many sessions trying to enter an order in the database, the control table could be a bottleneck. Consider a case where one session selects the last order number from the control table and, after updating the incremented order number, hangs before committing for some reason; then the whole application could come to
a standstill, with users queuing behind this hung session. Another bottleneck created by this type of order number generation occurs when one session updates the incremented order number back to the control table and then does some additional processing before committing; then the time for which the control table record is locked will be the time for which the processing lasts. The additional processing time after capturing the next serial number from the control table will greatly limit the throughput of the system.

There are several possible solutions to these problems:

Previous | Table of Contents | Next