Shulou(Shulou.com)05/31 Report--

Editor to share with you Oracle 11.2.0.3 database CJQ process caused by row cache lock waiting events affect job can not stop how to do, I hope you will learn something after reading this article, let's discuss it together!

First, phenomenon description

A production system application staff reported that the job exception initiated by him did not work properly, resulting in no data in the relevant table. He tried to stop job but failed, and he was unable to re-launch job.

Second, problem analysis

The login mode of job is generally 'DBMS_SCHEDULER',' by logging in through the PL/SQL tool and running the following statement to view

Select S.STATUS

S.INST_ID

S.SID

S.PADDR

S.SQL_ID

S.SQL_EXEC_START

S.ACTION

S.LAST_CALL_ET

S.BLOCKING_INSTANCE

S.BLOCKING_SESSION

S.EVENT

S.WAIT_CLASS

From v$session S

Where module = 'DBMS_SCHEDULER'

Query a kill job, the status is killed, but not released. After querying the spid through the following statement, the system process is killed by logging in to the background kill-9.

Select p.spid from v$session sjorie vandalism where p.addr=s.paddr and s.sid=508

After that, let the application launch job again, but the job still cannot be executed. Through the background, it is found that a waiting event row cache lock has occurred in the job. The waiting session is 1209, and the value of P1 is 11. Check that no result is returned through the following statement, and cannot locate where the row cache lock occurs.

Select * from v$rowcache where cache#=11

Query 1209 session found that the system background process CJQ0, CJQn and Jnnn are oracle job queue processes, why does the background process lock the normal job?

Select * from v$session where sid=1209

The application staff found the following anomalies through the JOB task provided by PL/SQL

(task CSH_BRANCH_D_1DAY is missing in the screenshot above, because the captured image is already processed. In fact, it was there when asked, but forgot to capture it.)

Currently running a lot of job, but none of them have session_id. It feels like a zombie job, since you can't kill job through a session.

We tried to stop through the dbms_scheduler package, but we couldn't stop it, and there was still a row cache lock event, which was still locked by the 1209 background process.

Row cache lock can not locate the specific lock object through P1, why does the background process lock the job? through the preliminary analysis of the above results, it is because there are many rigid job that have not been stopped normally, which leads to the lock situation when the same job task is initiated or the job is processed again, and it is locked by the background process CJQn.

SQL > Show parameter processes

By looking at the parameter job_queue_ processes, the value is 1000 and the value is 10000, which does not reach the peak value.

Further analysis

Log in to the background and collect relevant information and analysis through DEBUG command

Log in to the database with sysdba:

$sqlplus / as sysdba

Or

$sqlplus-prelim / as sysdba short stack + dump (excluding lock element)

266: 256 million 10-- > short stack+ dump

267: 25611-- > short stack+ dump + global cache of RAC

Level 11 and 267 will dump global cache and generate larger trace files, which are generally not recommended.

In general, if there are not too many processes, it is recommended to use 266, because this can dump the function stack of the process, which can be used to analyze what the process is doing.

But generating short stack is time-consuming, and if there are a large number of processes, such as 2000, it may take more than 30 minutes. In this case, you can generate level 10 or level 258, which collects more short short stack than level 10, but less lock element data than level 10.

The following is the intercept output of hanganalyze. You can see that the main wait is that row cache lock,session id:7/porcess id:128 is locked by session id:1209/process id: 49, which is consistent with the above analysis.

Chains most likely to have caused the hang:

[a] Chain 1 Signature: 'rdbms ipc message' Oracle session identified by:

{

Instance: 2 (cmbd.cmbd2)

Os id: 19792004

Process id: 49, oracle@jkptdb2 (CJQ0)

Session id: 1209

Session serial #: 9

}

Which is waiting for 'rdbms ipc message' with wait info:

{

P1: 'timeout'=0x14

Time in wait: 0.168064 sec

Heur. Time in wait: 2.171697 sec

Timeout after: 0.031936 sec

Wait id: 72756966

Blocking: 1 session

Wait history:

* time between current wait and wait # 1: 0.000350 sec

1. Event: 'rdbms ipc message'

Time waited: 0.200014 sec

Wait id: 72756965 p1: 'timeout'=0x14

* time between wait # 1 and # 2: 0.000304 sec

2. Event: 'rdbms ipc message'

Time waited: 0.200016 sec

Wait id: 72756964 p1: 'timeout'=0x14

* time between wait # 2 and # 3: 0.000307 sec

3. Event: 'rdbms ipc message'

Time waited: 0.200041 sec

Wait id: 72756963 p1: 'timeout'=0x14

}

Chain 1 Signature: 'rdbms ipc message'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.