Troubleshooting Locks

1. This script will find the sessions which are into WAITING state. (It filters out all idle sessions)

select sid, username, event, blocking_session,

seconds_in_wait, wait_time

from v$session where state in ('WAITING')

and wait_class != 'Idle';

Looking at this information, we can immediately find out that which session are waiting for a lock on a table and that locks being held by other sessions on the same resource (BLOCKING_SESSION).

2. If we want to know which SQL statements are causing this lock contention, we can find out easily, by issuing the following query joining the V$SESSION and V$SQL views-

Select sid, sql_text

from v$session s, v$sql q

where Sid in (254,270)

and

(q.sql_id = s.sql_id or

q.sql_id = s.prev_sql_id);

Note- In this example we have considered sessions with session id 254 is blocking 270. So unless session 254 commits or rollbacks the transaction, session 270 continues to wait.In this case if session 270 continues to wait we have to kill blocking session.

3. In Oracle Database 10g, wait events are divided into various wait classes, based on their type. The grouping of events lets you focus on specific classes and exclude nonessential ones such as idle events. We use V$SESSION_WAIT_CLASS view for this purpose.

Select wait_class_id, wait_class,

total_waits, time_waited

from v$session_wait_class

Where sid = 270;

The output of this query shows the wait classes and how many times the session (in our case session- 270) has waited for events in each waits class. For row lock contention, we should look into application wait class. We should check for how many times Application wait class occurred and for how much time the session has waited for this wait class (Time is represented in centiseconds (cs)—hundredths of a second—since the instance started.). If you think that this TIME_WAITED value is high for the session. We should explore the cause of these waits in the application wait class. The times for individual waits are available in the V$SYSTEM_EVENT view.

4. Use following query to identify individual waits in the application wait class (class id 4217450380):

Select event, total_waits, time_waited

from v$system_event e, v$event_name n

where n.event_id = e.event_id

and wait_class_id = 4217450380;

The output of this query shows lock contentions (indicated by the event enq: TX - row lock contention) and other wait events that constitute Application Wait Class. If waiting time in TX - row lock contention is higher, then it should concern you. It may be possible that a badly written application made its way through to the production database, causing these lock contention problems. However, we should not immediately draw that conclusion. The output of above query merely indicates that the users have experienced lock-contention-related waits for total number of time, for much total time in centiseconds. It is possible that mostly 1- or 2-cs waits and only one large wait account for the total wait time, and in that case, the application isn't faulty. A single large wait may be some freak occurrence skewing the data and not representative of the workload on the system.

5. Oracle 10g provides a new view, V$EVENT_HISTOGRAM, that shows the wait time periods and how often sessions have waited for a specific time period. Use following query against V$EVENT_HISTOGRAM

Select wait_time_milli bucket, wait_count

from v$event_histogram

where event =

'enq: TX - row lock contention';

The output looks like this:

BUCKET WAIT_COUNT

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

1 252

2 0

4 0

8 0

16 1

32 0

64 4

128 52

256 706

512 392

1024 18

2048 7

4096 843

The V$EVENT_HISTOGRAM view shows the buckets of wait times and how many times the sessions waited for a particular event—in this case, a row lock contention—for that duration. For example, sessions waited 252 times for less than 1 millisecond (ms), once less than 16 ms but more than 1 ms, and so on. The sum of the values of the WAIT_COUNT column is same as the value shown in the event enq: TX - row lock contention, output of query no 5. The V$EVENT_HISTOGRAM view shows that the most waits occurred in the ranges of 256 ms, 512 ms, and 4,096 ms, which is sufficient evidence that the applications are experiencing locking issues and that this locking is the cause of the slowness in Bill's session. Had the view showed numerous waits in the 1-ms range, we wouldn't have been as concerned, because the waits would have seemed normal.