jobq slave wait

昨天無意間在DB中發現如下問題

SQL> select sid,username,machine,program from v$session where SCHEMANAME='SYS';

SID USERNAME MACHINE PROGRAM
---------- --------------- ------------------------- ------------------------------
1 SHASAX01 oracle@SHASAX01 (PMON)
2 SHASAX01 oracle@SHASAX01 (DBW0)
3 SHASAX01 oracle@SHASAX01 (LGWR)
4 SHASAX01 oracle@SHASAX01 (CKPT)
5 SHASAX01 oracle@SHASAX01 (SMON)
6 SHASAX01 oracle@SHASAX01 (RECO)
7 SHASAX01 oracle@SHASAX01 (CJQ0)
8 SHASAX01 oracle@SHASAX01 (QMN0)
12
17
42
52
58
63
67
68
75
77
81
88
97
107
110
116
129
135
146
148

28 rows selected.

分析

SQL> select sid,event from v$session_wait where sid='12';

SID EVENT
---------- ----------------------------------------------------------------
12 jobq slave wait

SQL> select sid,event from v$session_wait where sid='17';

SID EVENT
---------- ----------------------------------------------------------------
17 jobq slave wait

SQL> host
$ ps -ef | grep ora_j
oracle 483582 1 0 16:32:03 - 0:00 ora_j014_FFV2DEV2
oracle 553192 1 0 16:32:03 - 0:00 ora_j015_FFV2DEV2
oracle 651374 1 0 16:32:03 - 0:00 ora_j018_FFV2DEV2
oracle 663802 1 0 16:32:03 - 0:00 ora_j013_FFV2DEV2
oracle 684060 1 0 16:32:03 - 0:00 ora_j009_FFV2DEV2
oracle 806920 1192114 0 16:32:16 pts/1 0:00 grep ora_j
oracle 868354 1 0 16:32:03 - 0:00 ora_j011_FFV2DEV2
oracle 872622 1 0 16:32:03 - 0:00 ora_j010_FFV2DEV2
oracle 888922 1 0 16:32:03 - 0:00 ora_j006_FFV2DEV2
oracle 901160 1 0 16:32:03 - 0:00 ora_j008_FFV2DEV2
oracle 913488 1 0 16:32:03 - 0:00 ora_j001_FFV2DEV2
oracle 938110 1 0 16:32:03 - 0:00 ora_j003_FFV2DEV2
oracle 995412 1 0 16:32:03 - 0:00 ora_j012_FFV2DEV2
oracle 1011764 1 0 16:32:03 - 0:00 ora_j019_FFV2DEV2
oracle 1065132 1 0 16:32:03 - 0:00 ora_j017_FFV2DEV2
oracle 1073386 1 0 16:32:03 - 0:00 ora_j007_FFV2DEV2
oracle 1089688 1 0 16:32:03 - 0:00 ora_j016_FFV2DEV2
oracle 1110124 1 0 16:32:03 - 0:00 ora_j005_FFV2DEV2
oracle 1114248 1 0 16:32:03 - 0:00 ora_j004_FFV2DEV2
oracle 1171538 1 0 16:32:03 - 0:00 ora_j002_FFV2DEV2
oracle 1208540 1 0 16:32:03 - 0:00 ora_j000_FFV2DEV2
$ exit

SQL> show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 20
SQL>

解決方法

SQL> alter system set job_queue_processes=0 scope=both;

System altered.

SQL> alter system set job_queue_processes=40 scope=both;

System altered.

SQL> select sid,username,machine,program from v$session where SCHEMANAME='SYS';

SID USERNAME MACHINE PROGRAM
---------- --------------- ------------------------- ------------------------------
1 SHASAX01 oracle@SHASAX01 (PMON)
2 SHASAX01 oracle@SHASAX01 (DBW0)
3 SHASAX01 oracle@SHASAX01 (LGWR)
4 SHASAX01 oracle@SHASAX01 (CKPT)
5 SHASAX01 oracle@SHASAX01 (SMON)
6 SHASAX01 oracle@SHASAX01 (RECO)
8 SHASAX01 oracle@SHASAX01 (QMN0)
41 SYS SHASAX01 sqlplus@SHASAX01 (TNS V1-V3)
52 SHASAX01 oracle@SHASAX01 (CJQ0)

9 rows selected.

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7364032/viewspace-17465/，如需轉載，請註明出處，否則將追究法律責任。