How to handle Oracle lock wait

How to handle Oracle lock wait

Management of lock wait is a problem that database administrators encounter in their daily work. Generally, large ERP lock wait processes are relatively small, there will be a relatively large number of lock waits for small application systems.

The General Lock wait processing method is as follows:

1) first look for the object holding the lock:

Script: select. sid, B. spid from (select s. sid, s. paddr from v $ session s, v $ lock l WHERE l. sid = s. sid and l. block = 1) a, v $ process B where. paddr = B. addr;

2) kill the corresponding session:

Script: alter system kill session 'sid, serial #';

The above operations can handle about 80% of lock waits, and 20% of lock waits cannot be solved through the above statements. Recently, I encountered a problem, when the kill session appears prompt "ORA-00031: session marked for kill", this problem can have two ways to deal:

Method 1: restart the database: This method is definitely not recommended. Every company has high requirements on system availability, once the system is restarted, IT indicates that the system is unavailable during the restart process, and the IT system availability is reduced. (This method is suitable for users who have different Oracle databases and do not even know how to use sqlplus)

Method 2: Kill threads from the operating system. Because unix and windows are different, the processing methods are also different;

1) perform the following operations on unix and linux:

A) identify the process information of the Operating System Based on the sid information.

Script: select p. spid, oSUSEr, s. program from v $ session s, v $ process p where s. paddr = p. addr and s. sid = & SID;

 

SQL> select p. spid, osuser, s. program from v $ session s, v $ process p where s. paddr = p. addr and s. sid = & SID;

Enter value for sid: 23

Old 1: select p. spid, osuser, s. program from v $ session s, v $ process p where s. paddr = p. addr and s. sid = & SID

New 1: select p. spid, osuser, s. program from v $ session s, v $ process p where s. paddr = p. addr and s. sid = 23

 

SPID OSUSER PROGRAM

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

8782014 ecqadm dw. sapECQ_DVEBMGS00 @ joeoneecq (TNS V1-V3)

 

B) Kill the process through the operating system under the root user

 

Script: kill-9 8782014

2. In the Windows operating system, oracle.exe processes can only be deployed in task management. Of course, this process cannot be terminated directly. Oracle provides the corresponding operation tool orakill. The detailed operations are as follows:

 

 

C: \ Users \ Administrator> orakill

 

Usage: orakill sid thread

 

Where sid = the Oracle instance to target

Thread = the thread id of the thread to kill

 

The thread id shoshould be retrieved from the spid column of a query such:

 

Select spid, osuser, s. program from

V $ process p, v $ session s where p. addr = s. paddr

 
Orakill sid thread

Sid: indicates the Instance name of the process to be killed.

Thread: the ID of the thread to be killed, that is, the spid obtained in step 1.

Example: c:> orakill orcl 8782014

Conclusion: The company's ERP system has been running for three years, and the data volume has reached 2 TB. The database is also the busiest of all the databases in the company, however, no kill session operation has been performed since running. Compared with another self-developed system of the company, the data volume is less than 10 Gb, but the lock wait occurs almost every month and DBA intervention is required. Lock wait or deadlock is not a database issue but an application issue. You will often encounter applications with these problems. Congratulations, you will have a chance to raise your salary.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Sharing pool for Oracle Performance Optimization