How to quickly find the lock waiting in the Oracle database
In a large database system, in order to ensure the consistency of the data, the system is locked accordingly when the data in the database is performed.
These locks have a "only lock", "row lock", "shared row lock", and each type has "row lock" (a record in a time), "page" "(Switch a page, that is, the minimum assignable unit stored in the database)," Table-level lock "(locking the entire table).
If the "Row-Level Locked" is except that the list is locked, the other lines can be modified by other users, if the table-level row it Lock, all other users can only query the table (select) operation, and any records are not modified or deleted. When the program is submitted to the modification of the changes (ROLLBACK), the locked resource is released, allowing other users to operate.
However, sometimes, due to the reason, it is not submitted for its work for a long time after locking resources; or due to the user's reason, if the data that needs to be modified, it is not modified and submitted in time, but is placed on the side. Or because the client appears "crane" in the client server mode, the server is not detected, thereby causing the locked resource to be released in time, affecting the operation of other users.
Thus, how to quickly diagnose users who lock resource and solve their locking is a challenge of database administrators.
Since the database application system is increasingly complex, once there is a case where the lock resource is not released, it will cause a large number of users who operate on the same table that cannot be operated, which affects the use of the system. At this point, DBA should solve the problem as quickly as possible. However, since the Query statement "getting a username" that is waiting to be locked in Oracle 8.0.x 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
Slow, "Execute very fast in Oracle 7.3.4), and execute the" Find User Process "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
It is also very slow. Therefore, it is often only possible to clear the problem by using the V $ SESSION state "inactive" and the last operation time to the current operation time until the time of more than 20 minutes (Last_Call_ET> 20 * 60 seconds). solve. However, this method is actually "poured with dirty water". Because some users have the process, although they are also "inactive", there is already no longer active, but that is because they are locked. Therefore, I came up with a solution. That is, by saving the relevant records in the V $ LOCK, the relevant records in the V $ SESSION view occurs in the table established, the table is queried, and the speed is greatly improved, and the problem can be quickly found. It is used in actual use. After receiving the user, you can detect the process of locking resources and affecting the processes of other users due to locking resources.
First, log in into the database in DBA identity (not system), create three basic tables: my_session, my_lock, my_sqltext, and establish a corresponding index on the column of the query. The statement is as follows: REM Creates a My_SESSION table from the V $ SESSION view, and creates an index on the field to be used to speed up the query speed.
Drop Table My_Session; Create Table My_SESSIONASSELECT 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 a where 1 = 2;
CREATE UNIQUE INDEX MY_SESSION (SID); CREATE INDEX MY_SESSION_N2 ON My_SESSION (LOCKWAIT); CREATE INDEX MY_SESSION_N3 ON My_SESSION (SQL_HASH_VALUE);
REM Remove the field from the V $ LOCK view, create an index on the field you want to use, to speed up the query speed Drop Table my_lock; create Table my_lockasselect 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 Remove the field from the V $ SQLText view, create a My_SQLText table, and create an index on the field you want to use to speed up the query speed Drop Table my_sqltext; create Table my_sqltextasselect hash_value, SQL_Text from V $ sqltext where 1 = 2;
Create index my_sqltext_n1 on my_sqltext (haveh_value);
Then, create a SQL script file for direct calls from SQL * Plus when needed. Among them, first delete the records in the table with the TRUNCATE TABLE table command. The reason why the truncate command is used instead of using the delete command, because the delete command is executed, it will generate the replay record, the speed is slow, and the space occupied by the index is not truly released. If INSERT and DELETE are repeated, the index is The space will grow continuously, and the query speed will slow down. The Truncate command does not produce a referential record, and the speed is executed fast, and the index space is released accordingly. After deleting the record, insert the related records in the three views into the three tables you created. Finally, inquiring it, due to indexing, since the conditions are filtered after insertion, the number of records is relatively small, so the query speed is very fast, and it can be seen immediately. At this time, if the process of the blocked other user processes is found in normal operation, it can be notified that the user will be submitted, thereby reaching the purpose of releasing the lock resource; if it is not normal, it is "inactive", And its Last_Call_ET has been more than a long time, then the following statement can be cleared, and the system will automatically roll back, thereby releasing the resource that locks. Alter System Kill Session 'SID, Serial #'; SQL script is as follows: set echo offset feedback offprompt 'Delete old records .....' truncate table my_lock; truncate table my_lock; truncate Table my_sqltext;
Prompt 'Get data .....' INSERT INTO MY_SESSIONSELECT 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 a where nVL (a.username, 'null') <> NULL;
INSERT INTO MY_LOCKSELECT ID1, KADDR, SID, Request, Type from V $ LOCK
Insert Into my_sqltextselect hash_value, SQL_Text from V $ SQLText S, my_session m where s.hash_value = m.sql_hash_value;
COLUMN UserName Format A10COLUMN MACHINE FORMAT A15COLUMN LAST_CALL_ET FORMAT 99999 Heading "Seconds" Column Sid Format 9999
Prompt "Waiting for the user of others" SELECT A.SID, A.Serial #, a.machine, a.last_call_et, a.user, B.ID1 from my_session a, my_lock b where a.lockwait = B.kaddr;
PROMPT "Waiting User" SELECT A.SID, A.Serial #, a. Machine, A.last_Call_Et, A.USERNAME, 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 "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, DB2. By using this script, you can greatly improve the situation of the current lock waiting in the system, thereby resolving the lock waiting problem in the database application system in time. Moreover, since its Program name and the corresponding SQL statement have actually taken out, it can be recorded afterwards, and it is given to the developer to analyze and fundamentally solved.
Source: Computer World Daily Date: 2004-12-03
