How to quickly find the lock waiting in the Oracle database
---- In large database systems, in order to ensure data consistency, the system is locked accordingly when the data in the database is operated.
---- These locks have a number of types such as "only lock", "row lock", "shared row lock", and each type, "Row a record in time), "Page Level Lock" (one page is locked, that is, the minimum assignable unit of the record in the database), "Table-level lock" (locking the entire table).
---- If the "Row-Level Rowed", other functions in this table can be modified or deleted in this table in addition to the "Row-Level Randid". If "" is "UPDATE) or delete. Table-level row lock ", all other users can only query the table (SELECT), 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 cause of the program, it has not been submitted for a long time after locking resources; or due to the user's reasons, if the data that needs to be modified, it is not modified and submitted in time, It 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 for database administrators.
---- Due to the increasing complexity of the database application system, once there is a case where the lock resource is not released, it will cause a large number of users operating in 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
---- Very slow, (executive 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 $ sessions 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 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.
---- Thus, 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, enter 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_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 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 MY_LOCK table, and create an index on the field you want to use to speed up the query speed Drop Table my_lock; create Table my_lock as select iD1, kaddr, sid, request , Type from V $ loc 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_sqltext as select 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 blocking other user processes is found in normal operation, it can be notified that the user is submitted, thereby reaching the purpose of releasing the lock resource; if it is not normal, ie, its state is "inactive", and its last_call_t 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 of the locked. Alter System Kill Session 'SID, Serial #'; ---- SQL script is as follows: set echo off feedback off proMpt 'Delete old records .....' 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 a where nVL (a.username, 'null') <> NULL;
INSERT INTO MY_LOCK SELECT ID1, KADDR, SID, REQUEST, TYPE AULOCK;
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 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, BBTYPE, 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 "Identise 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 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.
