[20220518]enq FU - contention等待事件.txt
[20220518]enq FU - contention等待事件.txt
--//檢查生產系統，發現inactive session 和 enq: FU - contention 等待事件,從來沒有遇到過,仔細探究看看.
--//本文集中探究enq: FU - contention 等待事件.
1.環境：
例項1> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.ashtop檢視：
例項1> @ashtop event 1=1 &day
Total Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
334312 3.9 93% | 2022-05-16 10:52:20 2022-05-17 10:52:12 15444 32265
13951 .2 4% | inactive session 2022-05-16 10:52:18 2022-05-17 10:52:11 6248 8976
2854 .0 1% | enq: FU - contention 2022-05-16 11:11:39 2022-05-17 10:14:18 1 2854
2342 .0 1% | RMAN backup & recovery I/O 2022-05-16 19:30:08 2022-05-17 01:52:46 1 496
1236 .0 0% | control file sequential read 2022-05-16 10:57:37 2022-05-17 10:51:08 528 996
734 .0 0% | enq: TX - row lock contention 2022-05-16 15:43:39 2022-05-16 17:16:01 3 734
--//inactive session 和 enq: FU - contention等待事件，先集中探究enq: FU - contention等待事件.
--//inactive session 等待事件另外寫一篇blog.
3.分析:
例項1> @ enq fu
EQ_NAME EQ REQ_REASON TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME REQ_DESCRIPTION EVENT#
------------------------------ -- ---------- ---------- ----------- ---------- ----------- ------------- ---------------------------------------------------------------------------------------------------- ----------
Cross-Instance Call Invocation CI contention 0 0 0 0 0 Coordinates cross-instance function invocations 427
DBFUS FU contention 1 0 1 0 0 This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark 1328
Statistics
--//FU: This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
--//翻譯如下： 此佇列用於序列化資料庫特性使用情況和高水位標記統計資料的捕獲，怎麼意思???
--//注:事後從輸出也看出ENQ=FU的TOTAL_REQ#=1,SUCC_REQ#=1.說明有1個程式目前持有enq FU的enquence.
例項1> @ ashtop sql_id,module1 "event='enq: FU - contention'" &day
Total Distinct Distinct
Seconds AAS %This SQL_ID MODULE1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- -------------------- ------------------- ------------------- ---------- --------
2854 .0 100% | mmon_slave 2022-05-16 11:11:39 2022-05-17 10:14:18 1 2854
--//來自mmon_slave模組, 時間跨度接近一天。
--//在例項1上執行:
$ ls -l *mmon*
-rw-r----- 1 oracle asmadmin 3442 2022-05-17 08:34:09 ywdb1_mmon_25038.trc
-rw-r----- 1 oracle asmadmin 369 2022-05-17 08:34:09 ywdb1_mmon_25038.trm
--//檢查跟蹤檔案發現：
*** 2022-05-14 09:31:52.750
*** SESSION ID:(7897.1) 2022-05-14 09:31:52.750
*** CLIENT ID:() 2022-05-14 09:31:52.750
*** SERVICE NAME:(SYS$BACKGROUND) 2022-05-14 09:31:52.750
*** MODULE NAME:() 2022-05-14 09:31:52.750
*** ACTION NAME:() 2022-05-14 09:31:52.750
Unable to schedule a MMON slave at: Auto DBFUS Main
Slave was not permitted to be scheduled
- A slave for this action is already running.
Unable to schedule a MMON slave at: Auto DBFUS Main
*** 2022-05-14 10:31:54.627
Slave was not permitted to be scheduled
- A slave for this action is already running.
Unable to schedule a MMON slave at: Auto DBFUS Main
*** 2022-05-14 11:31:56.643
Slave was not permitted to be scheduled
- A slave for this action is already running.
Unable to schedule a MMON slave at: Auto DBFUS Main
--//間隔1小時
*** 2022-05-14 13:32:00.531
Slave was not permitted to be scheduled
- A slave for this action is already running.
Unable to schedule a MMON slave at: Auto DBFUS Main
--//間隔2小時
..
*** 2022-05-16 14:33:33.430
Slave was not permitted to be scheduled
- A slave for this action is already running.
Unable to schedule a MMON slave at: Auto DBFUS Main
*** 2022-05-16 23:33:52.277
Slave was not permitted to be scheduled
- A slave for this action is already running.
Unable to schedule a MMON slave at: Auto DBFUS Main
--//間隔9小時
*** 2022-05-17 08:34:09.598
Slave was not permitted to be scheduled
- A slave for this action is already running.
--//間隔9小時.
--//從2022-05-14 09:31:52.750開始就出現問題，似乎還有一個slave在執行。
--//注:事後發現從間隔時間上看可以排程時間間隔1小時,不過後面寫跟蹤檔案的間隔似乎不斷增加,
--//上班再仔細看看,補充測試：
--//http://blog.itpub.net/267265/viewspace-2641133/ => [20190412]bash顯示日期相減.txt
$ zdate
2022/05/19 08:36:15
$ grep "\*\*\* 2022" ywdb1_mmon_25038.trc | awk '{print $2 " " $3}' |xargs -IQ date -d "Q" "+%s" | awk 'NR==1{a=$1} NR>1{print ($1-a)/3600;a=$1}'
1.00056
1.00056
2.00111
2.00111
2.00111
4.00194
4.00222
4.00194
8.00417
8.00389
8.00444
9.005
9.00528
9.00472
9.005
9.005
9.005
9.005
9.005
--//可以看出跟蹤檔案記錄時間的變化，開始1，2，4，8，9....
--//檢查例項2,在例項2上檢視ywdb2_mmon_17152.trc檔案，發現如下:
*** 2022-05-14 09:12:09.707
*** SESSION ID:(7897.1) 2022-05-14 09:12:09.707
*** CLIENT ID:() 2022-05-14 09:12:09.707
*** SERVICE NAME:(SYS$BACKGROUND) 2022-05-14 09:12:09.707
*** MODULE NAME:() 2022-05-14 09:12:09.707
*** ACTION NAME:() 2022-05-14 09:12:09.707
***KEWFDBFUS: Auto-DBFUS slave failed, return Code: 2***KEWFDBFUS: Auto-DBFUS slave failed, return Code: 2 ...............
--//似乎沒有換行, 2022-05-14 09:12:09.707出現問題,感覺是執行Auto-DBFUS slave failed.怎麼意思不理解.
--//注:輸出時沒有時間資訊.
--//結合例項1,例項2看到的情況,可以做這樣的猜測,例項1上執行時,另外的例項2上也在執行的情況下,oracle在執行前要獲取enq FU的enquennce,
--//這樣保證不能在2個例項上同時執行.
--//還可以猜測例項1上已經有一個程式持有enq FU的enquennce,這樣例項1再次執行時報告A slave for this action is already running,
--//而例項2在執行前需要持有enq FU的enquence,因為例項1上的enq FU沒有釋放,這樣報***KEWFDBFUS: Auto-DBFUS slave failed, return Code: 2.
--//注:以上的分析來自事後,慢慢看下面的展開分析.
例項1> @ashtop SESSION_ID,module1,inst_id "event='enq: FU - contention'" &1day
Total Distinct Distinct
Seconds AAS %This SESSION_ID MODULE1 INST_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ---------- ---------- ------- ------------------- ------------------- ---------- --------
238 .0 8% | 3408 mmon_slave 2 2022-05-17 02:12:05 2022-05-17 06:14:11 1 238
237 .0 8% | 3971 mmon_slave 2 2022-05-16 20:11:55 2022-05-17 03:14:05 1 237
120 .0 4% | 8482 mmon_slave 2 2022-05-16 21:11:56 2022-05-16 21:13:55 1 120
119 .0 4% | 14 mmon_slave 2 2022-05-16 22:11:58 2022-05-16 22:13:56 1 119
119 .0 4% | 576 mmon_slave 2 2022-05-16 19:11:53 2022-05-16 19:13:52 1 119
119 .0 4% | 1712 mmon_slave 2 2022-05-17 10:12:20 2022-05-17 10:14:18 1 119
119 .0 4% | 2563 mmon_slave 2 2022-05-16 16:11:47 2022-05-16 16:13:46 1 119
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
119 .0 4% | 3973 mmon_slave 2 2022-05-17 05:12:11 2022-05-17 05:14:09 1 119
119 .0 4% | 4254 mmon_slave 2 2022-05-17 01:12:03 2022-05-17 01:14:02 1 119
119 .0 4% | 5102 mmon_slave 2 2022-05-16 12:11:40 2022-05-16 12:13:39 1 119
119 .0 4% | 5937 mmon_slave 2 2022-05-16 14:11:44 2022-05-16 14:13:42 1 119
119 .0 4% | 6213 mmon_slave 2 2022-05-16 23:11:59 2022-05-16 23:13:58 1 119
119 .0 4% | 6775 mmon_slave 2 2022-05-17 04:12:08 2022-05-17 04:14:07 1 119
119 .0 4% | 7060 mmon_slave 2 2022-05-16 15:11:46 2022-05-16 15:13:44 1 119
119 .0 4% | 7066 mmon_slave 2 2022-05-17 00:12:01 2022-05-17 00:14:00 1 119
119 .0 4% | 7069 mmon_slave 2 2022-05-16 13:11:42 2022-05-16 13:13:40 1 119
119 .0 4% | 7350 mmon_slave 2 2022-05-17 07:12:14 2022-05-17 07:14:12 1 119
119 .0 4% | 7903 mmon_slave 2 2022-05-17 09:12:18 2022-05-17 09:14:16 1 119
119 .0 4% | 8753 mmon_slave 2 2022-05-16 17:11:49 2022-05-16 17:13:48 1 119
118 .0 4% | 294 mmon_slave 2 2022-05-16 18:11:51 2022-05-16 18:13:49 1 118
118 .0 4% | 1716 mmon_slave 2 2022-05-17 08:12:16 2022-05-17 08:14:14 1 118
118 .0 4% | 7918 mmon_slave 2 2022-05-17 11:12:21 2022-05-17 11:14:19 1 118
22 rows selected.
--//似乎enq: FU - contention等待事件出現在例項2.還可以猜測每次等待120秒上下,我估計過了120秒,該程式可能被kill掉,不然
--//SESSION_ID不會出現不同的情況.
--//注:事後在家裡我才發現應該在查詢時應該增加一個欄位session_serial#.
--//單獨取出FIRST_SEEN欄位,排序,經過一系列編輯處理後生成如下指令碼:
select to_date('2022-05-16 13:11:42') - to_date('2022-05-16 12:11:40') from dual ;
select to_date('2022-05-16 14:11:44') - to_date('2022-05-16 13:11:42') from dual ;
select to_date('2022-05-16 15:11:46') - to_date('2022-05-16 14:11:44') from dual ;
select to_date('2022-05-16 16:11:47') - to_date('2022-05-16 15:11:46') from dual ;
select to_date('2022-05-16 17:11:49') - to_date('2022-05-16 16:11:47') from dual ;
select to_date('2022-05-16 18:11:51') - to_date('2022-05-16 17:11:49') from dual ;
select to_date('2022-05-16 19:11:53') - to_date('2022-05-16 18:11:51') from dual ;
select to_date('2022-05-16 20:11:55') - to_date('2022-05-16 19:11:53') from dual ;
select to_date('2022-05-16 21:11:56') - to_date('2022-05-16 20:11:55') from dual ;
select to_date('2022-05-16 22:11:58') - to_date('2022-05-16 21:11:56') from dual ;
select to_date('2022-05-16 23:11:59') - to_date('2022-05-16 22:11:58') from dual ;
select to_date('2022-05-17 00:12:01') - to_date('2022-05-16 23:11:59') from dual ;
select to_date('2022-05-17 01:12:03') - to_date('2022-05-17 00:12:01') from dual ;
select to_date('2022-05-17 02:12:05') - to_date('2022-05-17 01:12:03') from dual ;
select to_date('2022-05-17 04:12:08') - to_date('2022-05-17 02:12:05') from dual ; --//2個小時
select to_date('2022-05-17 05:12:11') - to_date('2022-05-17 04:12:08') from dual ;
select to_date('2022-05-17 07:12:14') - to_date('2022-05-17 05:12:11') from dual ; --//2個小時
select to_date('2022-05-17 08:12:16') - to_date('2022-05-17 07:12:14') from dual ;
select to_date('2022-05-17 09:12:18') - to_date('2022-05-17 08:12:16') from dual ;
select to_date('2022-05-17 10:12:20') - to_date('2022-05-17 09:12:18') from dual ;
select to_date('2022-05-17 11:12:21') - to_date('2022-05-17 10:12:20') from dual ;
--//我不執行了,前後對比你就可以猜測oracle間隔1個小時排程這個執行.注意記錄跟蹤檔案時間的變化看上面的計算。
--//這樣一天相當於 24*120 = 2880秒,與我前面看到2854 秒相當接近.
--//利用tpt的dash_wait_chains2指令碼,注意該命令檢視的是dba_hist_active_sess_history檢視.
例項1> @ tpt/ash/dash_wait_chains2 BLOCKING_SESSION||','||BLOCKING_SESSION_SERIAL#||',@'||BLOCKING_INST_ID||'=>'||session_id||','||SESSION_SERIAL#||',@'||inst_id||'=>'||event "event='enq: FU - contention'" &day
-- Display ASH Wait Chain Signatures script v0.7 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN FIRST_SEEN LAST_SEEN
------ ---------- ---------- ----------------------------------------------------------------------------- ------------------- -------------------
4% 120 0 -> 3958,15327,@1=>2563,19865,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 16:11:53 2022-05-16 16:13:43
4% 120 0 -> 3958,15327,@1=>576,18913,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 19:12:01 2022-05-16 19:13:51
4% 120 0 -> 3958,15327,@1=>8482,27213,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 21:12:05 2022-05-16 21:13:56
4% 120 0 -> 3958,15327,@1=>3973,13337,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 05:12:12 2022-05-17 05:14:02
4% 120 0 -> 3958,15327,@1=>7060,61585,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 15:11:40 2022-05-16 15:13:31
4% 120 0 -> 3958,15327,@1=>1712,2789,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 10:12:23 2022-05-17 10:14:13
4% 110 0 -> 3958,15327,@1=>3971,21789,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 03:12:18 2022-05-17 03:13:58
4% 110 0 -> 3958,15327,@1=>3971,20963,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 20:12:13 2022-05-16 20:13:53
4% 110 0 -> 3958,15327,@1=>14,53877,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 22:12:07 2022-05-16 22:13:48
4% 110 0 -> 3958,15327,@1=>7066,18339,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 00:12:11 2022-05-17 00:13:52
4% 110 0 -> 3958,15327,@1=>3408,13363,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 02:12:16 2022-05-17 02:13:56
4% 110 0 -> 3958,15327,@1=>6775,4533,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 04:12:10 2022-05-17 04:13:50
4% 110 0 -> 3958,15327,@1=>8753,64495,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 17:12:06 2022-05-16 17:13:46
4% 110 0 -> 3958,15327,@1=>7350,25325,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 07:12:26 2022-05-17 07:14:06
4% 110 0 -> 3958,15327,@1=>5937,52023,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 14:11:58 2022-05-16 14:13:38
4% 110 0 -> 3958,15327,@1=>5102,12305,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 12:11:53 2022-05-16 12:13:33
4% 110 0 -> 3958,15327,@1=>3408,13837,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 06:12:24 2022-05-17 06:14:04
4% 110 0 -> 3958,15327,@1=>7903,51465,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 09:12:30 2022-05-17 09:14:11
4% 110 0 -> 3958,15327,@1=>294,55109,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 18:12:08 2022-05-16 18:13:49
4% 110 0 -> 3958,15327,@1=>7069,16489,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 13:11:55 2022-05-16 13:13:36
4% 100 0 -> 3958,15327,@1=>6213,65203,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-16 23:12:09 2022-05-16 23:13:40
4% 100 0 -> 3958,15327,@1=>1716,28411,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 08:12:38 2022-05-17 08:14:08
4% 100 0 -> 3958,15327,@1=>4254,31191,@2=>enq: FU - contention -> ,,@=>3958,15327,@1=> 2022-05-17 01:12:23 2022-05-17 01:13:54
1% 20 0 -> ,,@=>1716,28411,@2=>enq: FU - contention 2022-05-17 08:12:17 2022-05-17 08:12:27
0% 10 0 -> ,,@=>4254,31191,@2=>enq: FU - contention 2022-05-17 01:12:12 2022-05-17 01:12:12
0% 10 0 -> ,,@=>3971,20963,@2=>enq: FU - contention 2022-05-16 20:12:03 2022-05-16 20:12:03
0% 10 0 -> ,,@=>5937,52023,@2=>enq: FU - contention 2022-05-16 14:11:46 2022-05-16 14:11:46
0% 10 0 -> ,,@=>8753,64495,@2=>enq: FU - contention 2022-05-16 17:11:55 2022-05-16 17:11:55
0% 10 0 -> ,,@=>7069,16489,@2=>enq: FU - contention 2022-05-16 13:11:43 2022-05-16 13:11:43
0% 10 0 -> ,,@=>5102,12305,@2=>enq: FU - contention 2022-05-16 12:11:40 2022-05-16 12:11:40
30 rows selected.
--//sid=3958出現自鎖，在例項1上。注意前面120秒,再次驗證自己的判斷.
--//注:dash_wait_chains2 顯示的是一個等待或者阻塞的鏈條,你可以發現sid=2563,19865也出現在前面執行
--//@ashtop SESSION_ID,module1,inst_id "event='enq: FU - contention'" &1day 的輸出中.
例項1> @ sid 3958
sid = 3958
SPID PID SID SERIAL# CLIENT_INFO PNAME TRACEFILE PROGRAM TERMINAL SQL_ID STATUS C50
----- --- ---- ------- ----------- ----- ---------------------------------------------------------------- -------------------- -------- ------------- -------- --------------------------------------------------
31997 430 3958 15327 M001 /u01/app/oracle/diag/rdbms/ywdb/ywdb1/trace/ywdb1_m001_31997.trc oracle@fyhis1 (M001) UNKNOWN a78s414xwpn92 ACTIVE alter system kill session '3958,15327' immediate;
--//注意:STATUS=ACTIVE,也就是SQL_ID=a78s414xwpn92還在執行.
例項1> @ sql_id a78s414xwpn92
--SQL_ID = a78s414xwpn92
SELECT MAX(TOTAL_MB), MIN(TOTAL_MB), SUM(TOTAL_MB), COUNT(*) FROM V$ASM_DISKGROUP;
--//也就是前面執行的sql語句(sql_id=a78s414xwpn92)一直在執行,沒有執行完成,這樣例項1一直持有enq FU的enquence沒有釋放.
--//手工開啟新的回話,嘗試手工執行該sql語句很慢,好像掛起一樣,kill該回話.繼續分析.
--//SELECT MAX(TOTAL_MB), MIN(TOTAL_MB), SUM(TOTAL_MB), COUNT(*) FROM V$ASM_DISKGROUP;
--//例項1執行:
例項1> @ bgx mmon
PROGRAM MODULE ACTION SID PID SPID
-------------------------- ------------ ------------------- ---- ------- ------
oracle@fyhis1 (MMON) 7897 28 25038
例項1> @ bgx m001
PROGRAM MODULE ACTION SID PID SPID
-------------------------- ------------ ------------------- ---- ------- ------
oracle@fyhis1 (M001) MMON_SLAVE 0000009 FINISHED190 3958 430 31997
--//ACTION='0000009 FINISHED190' 什麼意思.
--//例項2執行:
例項2> @ bgx mmon
PROGRAM MODULE ACTION SID PID SPID
-------------------------- ------------ ------------------- ---- ------- ------
oracle@fyhis2 (MMON) 7897 28 17152
例項2> @ bgx m001
no rows selected
--//實際上到這裡也基本知道出現enq: FU - contention等待事件的原因.主要是執行sql_id=a78s414xwpn92一直無法完成,導致
--//例項1一直持有enq: FU的enquence,這樣例項2再次執行(間隔1小時)時無法獲取enq: FU的enquence,一直在不斷的請求,
--//120秒後直接kill m001模組.
--//例項2報***KEWFDBFUS: Auto-DBFUS slave failed, return Code: 2.
--//例項1報:
--//Unable to schedule a MMON slave at: Auto DBFUS Main
--// Slave was not permitted to be scheduled
--// - A slave for this action is already running.
--//Unable to schedule a MMON slave at: Auto DBFUS Main
--//剩下的問題是分析為什麼sql_id=a78s414xwpn92無法執行完成.另外寫一篇blog分析.
--//我簡單看了一下查詢V$ASM_DISKGROUP就是訪問基表 X$KFGRP，我單獨查詢
--//select * from X$KFGRP where rownum=1;
--//會話都會掛起，我在其它rac環境做了類似測試，執行都很快完成，看來也許是訪問許可權出了問題。嘗試gdb跟蹤：
# rlwrap gdb -f -p 21329
--//提示如下。新升級的版本gdb要使用很麻煩，暫時放棄探究。
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.132.el6.x86_64 libaio-0.3.107-10.el6.x86_64 numactl-2.0.7-8.el6.x86_64
--//另外說明一下m001對應的程式是可以kill掉的.
例項1> @ bgkill m001
INDX KSUPRPNM PROCESS_FLAG KSUPRPID
---------- ------------------------------ ----------------- ------------------------
430 oracle@fyhis1 (M001) 2 31997
--//即使kill掉該程式,也應該沒用,因為再次執行時還是要執行sql_id=a78s414xwpn92,從前面的手工執行的情況看該語句執行很慢.因為
--//還會出現enq: FU -contention 等待事件.要想不再出現可以修改_swrf_mmon_dbfus=false.
例項2> @ hide dbfus
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------- ----------------------------------------- ------------- ------------- ------------ ----- ---------
_swrf_mmon_dbfus Enable/disable SWRF MMON DB Feature Usage TRUE TRUE TRUE FALSE IMMEDIATE
_swrf_test_dbfus Enable/disable DB Feature Usage Testing TRUE FALSE FALSE FALSE IMMEDIATE
--//alter system set "_swrf_mmon_dbfus"=false scope=memory;
--//安全一點先嚐試如下：
--//alter system disable restricted session;
--//alter system enable restricted session;
$ ps -fp 31997
UID PID PPID C STIME TTY TIME CMD
oracle 31997 1 0 May14 ? 00:00:06 ora_m001_ywdb1
$ kill -9 31997
--//select * from X$KFGRP where rownum=1;
--//會話都會掛起。
例項1> alter system set "_swrf_mmon_dbfus"=false scope=memory;
System altered.
--//先臨時關閉它看看。
4.附上使用的指令碼.
$ cat bgx.sql
select s.program, s.module, s.action, s.sid, p.pid, p.spid
from v$session s, v$process p
where s.paddr=p.addr and S.PROGRAM like upper('%&1%') and p.background=1
order by s.program;
$ cat bgkill.sql
column KSUPRPNM format a30
SELECT indx,ksuprpnm,TO_CHAR(ksuprflg,'XXXXXXXXXXXXXXXX') process_flag,KSUPRPID
FROM x$ksupr
WHERE BITAND(ksuprflg,4) != 4 and KSUPRPID is not null and ksuprpnm like upper('%&&1%')
ORDER BY indx ;
$ cat enq.sql
column EQ_NAME format a30
column REQ_REASON format a36
column REQ_DESCRIPTION format a100
select * from v$enqueue_statistics where EQ_TYPE like upper('%&&1%') or REQ_DESCRIPTION like '%&&1%';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2895255/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- enq: WF - contention等待事件ENQ事件
- enq: CF - contention 等待事件ENQ事件
- enq: TS - contention 等待事件ENQ事件
- 等待事件之enq: HW - contention事件ENQ
- enq:TM-contention事件等待ENQ事件
- 消除 enq: DX - contention 等待事件ENQ事件
- zt_Oracle enq: TX contention 和 enq: TM contention 等待事件OracleENQ事件
- Oracle等待事件之enq: TM – contentionOracle事件ENQ
- 等待事件enq: TX - row lock contention事件ENQ
- oracle等待事件之enq: CF – contentionOracle事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- 故障排除 | enq：TX - index contention等待事件ENQIndex事件
- 等待事件enq TX row lock contention分析事件ENQ
- 如何診斷等待事件 enq: HW - contention事件ENQ
- 【效能調整】等待事件 enq: SQ - contention事件ENQ
- 事務上的等待事件 —— enq: UL - contention事件ENQ
- enq: SQ - contention 等待事件處理辦法ENQ事件
- enq: TX - row lock contention等待事件處理ENQ事件
- enq: TX - index contention等待ENQIndex
- oracle 11.2.0.4 rac叢集等待事件enq: TM - contentionOracle事件ENQ
- enq: TM - contention TM 等待事件的原因及模擬ENQ事件
- [20161208]等待事件enq: HW - contention事件ENQ
- [20140311]等待事件enq HW - contention事件ENQ
- 大量insert引起的enq: HW - contention等待ENQ
- 故障處理】佇列等待之enq: US - contention案例佇列ENQ
- 【故障處理】佇列等待之enq: US - contention案例佇列ENQ
- 資料庫出現很高的enq: DX - contention 等待資料庫ENQ
- 【故障處理】佇列等待之enq IV - contention案例佇列ENQ
- enq: US - contentionENQ
- enq: HW - contentionENQ
- enq: TM - contentionENQ
- enq:TM contentionENQ
- enq: DX - contentionENQ
- enq: TS - contentionENQ
- enq：Library cache lock/pin等待事件ENQ事件
- 關於enq: TX - index contention 等待的探討與測試ENQIndex
- enq: TX - allocate ITL entry等待事件分析ENQ事件
- enq:TX - index contentionENQIndex