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.
col event format a8 col p2text format a5 col p2 format 999999 col p3text format a5
continues
Page 634
Listing 25.2Continuedcol 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: |
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 reviewand possible reschedulingof 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.
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: