How to quickly find the lock wait in the Oracle database [resend]

 

How to quickly find the lock wait in the Oracle database

In a large database system, to ensure data consistency, the system locks data when operating the data in the database.

These locks include read-only locks, exclusive locks, shared exclusive locks, and other types, and each type has row-level locks (one record is locked at a time ), "Page-level locks" (one page is locked at a time, that is, the minimum allocable unit for storing records in the database) and "table-level locks" (locking the entire table ).

If the row-level exclusive lock is set to "Row-level exclusive lock", other rows in the table can be updated or deleted by other users, if it is a table-level exclusive lock, all other users can only perform the Select Operation on the table, but cannot modify or delete any records. After the program commits or rolls back the modifications, the locked resources are released, allowing other users to perform operations.

However, sometimes, due to program reasons, the job is not submitted after the resource is locked for a long time; or, for user reasons, if the data to be modified is called up, the job is not modified and submitted in time, it is placed aside, or because the client appears "dead" in the way of the client server, but the server does not detect it, so that the locked resources are not released in time, affecting the operations of other users.

Therefore, it is a challenge for database administrators to quickly diagnose users who lock resources and solve their locking problems.

As database application systems become more and more complex, once lock resources are not released in a timely manner, a large number of users who operate on the same table will not be able to operate, thus affecting the use of the system. At this time, DBAs should solve the problem as quickly as possible. However, because the query statement "Get the user name waiting to lock the resource" is executed in Oracle 8.0.x

Select a. username, A. Sid, A. Serial #, B. id1
From v $ session A, V $ lock B
Where a. lockwait = B. kaddr

Very slow (executed quickly in Oracle 7.3.4), and run the query statement "find and block user processes of other users ".
Select a. username, A. Sid, A. Serial #, B. id1
From v $ session A, V $ lock B
Where B. id1 in
(Select distinct E. id1
From v $ Session D, V $ lock E
Where D. lockwait = E. kaddr)
And a. Sid = B. Sid
And B. Request = 0

Execution is also very slow. Therefore, it is usually necessary to set the status in the V $ session to "inactive" (not active) and the last operation time has exceeded 20 minutes (last_call_et> 20*60 seconds) to solve the problem.
However, this method is actually "throwing off the baby with dirty water ". Although some users' processes are "inactive" and haven't been active for a long time, they are in the lock wait status.

Therefore, I came up with a solution. That is, by saving the relevant records in the V $ lock and V $ session views at the time of the problem in the self-created table and then querying the table, the speed is greatly improved, you can quickly find problems. After actual use, the effect is very good. After receiving a response from the user, you can find out the processes that affect other users due to resource lock in a few seconds and perform corresponding processing.

First, log on to the database as a DBA (not necessarily system), create three basic tables: my_session, my_lock, and my_sqltext, and create corresponding indexes on the columns that will be queried. The statement is as follows: REM extracts the fields of interest from the V $ session view, creates a my_session table, and creates an index on the fields to be used in the query to speed up the query.

Drop table my_session;
Create Table my_session
As
Select a. username, A. Sid, A. Serial #,
A. lockwait, A. Machine, A. status,
A. last_call_et, A. SQL _hash_value, A. Program
From v $ session
Where 1 = 2;

Create unique index my_session_u1 on my_session (SID );
Create index my_session_n2 on my_session (lockwait );
Create index my_session_n3 on my_session (SQL _hash_value );

Rem extracts fields from the V $ lock view, creates the my_lock table, and creates indexes on the fields to be used in the query to speed up the query.
Drop table my_lock;
Create Table my_lock
As
Select id1, kaddr, Sid, request, Type
From v $ lock
Where 1 = 2;

Create index my_lock_n1 on my_lock (SID );
Create index my_lock_n2 on my_lock (kaddr );

Rem extracts fields from the V $ sqltext view, creates a my_sqltext table, and creates an index on the fields to be used in the query to speed up the query.
Drop table my_sqltext;
Create Table my_sqltext
As
Select hash_value, SQL _text
From v $ sqltext
Where 1 = 2;

Create index my_sqltext_n1 on my_sqltext (hash_value );

Create an SQL script file so that it can be called directly from SQL * Plus if necessary. First, use the truncate table name command to delete the records in the table. The truncate command, instead of the DELETE command, is used because the record will be replayed during the execution of the DELETE command, which is slow and the space occupied by the index is not actually released, if the insert and delete operations are performed repeatedly, the space occupied by the index increases and the query speed slows down. The truncate command does not generate replays, And the execution speed is faster than that of delete, and the index space is released accordingly. After deleting a record, insert the relevant records in the three views into the three tables created by the user. Finally, the query results are displayed immediately because there is an index and the number of records is relatively small after the filter condition is inserted.
If the process that blocks other user processes is normal, you can notify the user to submit the process to release the lock resource. if the process is not normal, that is, if the status is "inactive" and Its last_call_et is a relatively long time, you can run the following statement to clear the process and the system will automatically roll back it, to release the locked resources.

Alter system kill session 'sid, serial #';
The SQL script is as follows:
Set echo off
Set feedback off
Prompt 'delete old record .....'
Truncate table my_session;
Truncate table my_lock;
Truncate table my_sqltext;

Prompt 'get data .....'
Insert into my_session
Select a. username, A. Sid, A. Serial #,
A. lockwait, A. Machine, A. status,
A. last_call_et, A. SQL _hash_value, A. Program
From v $ session
Where nvl (A. username, 'null') <> 'null;

Insert into my_lock
Select id1, kaddr, Sid, request, Type
From v $ lock;

Insert into my_sqltext
Select hash_value, SQL _text
From v $ sqltext S, my_session m
Where S. hash_value = M. SQL _hash_value;

Column username format A10
Column Machine format A15
Column last_call_et format 99999 heading "seconds"
Column Sid format 9999

Prompt "waiting for other users"
Select a. Sid, A. Serial #,
A. Machine, A. last_call_et, A. username, B. id1
From my_session A, my_lock B
Where a. lockwait = B. kaddr;

Prompt "Waiting users"
Select a. Sid, A. Serial #,
A. Machine, A. last_call_et, A. username,
B. B. type, A. Status, B. id1
From my_session A, my_lock B
Where B. id1 in
(Select distinct E. id1
From my_session D, my_lock E
Where D. lockwait = E. kaddr)
And a. Sid = B. Sid
And B. Request = 0;

Prompt "find its SQL"
Select a. username, A. Sid, A. Serial #,
B. id1, B. type, C. SQL _text
From my_session A, my_lock B, my_sqltext C
Where B. id1 in
(Select distinct E. id1
From my_session D, my_lock E
Where D. lockwait = E. kaddr)
And a. Sid = B. Sid
And B. Request = 0
And C. hash_value = A. SQL _hash_value;

The above ideas can also be used in other large database systems such as Informix, Sybase, and DB2. By using this script, you can greatly improve the situation of obtaining the current lock wait in the system, so as to promptly solve the lock wait problem in the database application system. In addition, because its program name and corresponding SQL statement have been extracted, you can record it later and hand it over to its developers for analysis and fundamentally solve it.

 

Source: World computer REPORT Date: