After 11 GB, we use the v $ wait_chains view to diagnose the database hang and Contention11g. after the database hang is occupied, we will perform hanganalyze on the database for analysis. after 11 GB, we can use a new view v $ wait_chains to diagnose the number of 11 GB, and then use the v $ wait_chains view to diagnose the database hang and Contention
Before 11g, we usually do the hang analyze analysis on the database after the database has hang. after 11g, we can use a new view v $ wait_chains to diagnose database hang and contention. In the 11gR1 version, Oracle implements a function through the diag process. it performs a local hang analyze every 3 seconds and a global hang analyze every 10 seconds. This information is stored in the memory. Oracle calls this memory "hang analysis cache ". This part of memory information plays a very important role in diagnosing hang and contention in our database. The database also has some features and tools that need to use this memory area. For example, Hang Management, Resource Manager Idle Blocker Kill, SQL Tune Hang Avoidance and pmon cleanup, and some external tools such as Procwatcher.
Let's take a look at the definition of the v $ wait_chains view. Take 11gR2 as an example.
SQL> desc v $ wait_chains
Name Null? Type
-----------------------------------------------------------------------------
CHAIN_ID NUMBER
CHAIN_IS_CYCLE VARCHAR2 (5)
CHAIN_SIGNATURE VARCHAR2 (801)
CHAIN_SIGNATURE_HASH NUMBER
INSTANCE NUMBER
OSID VARCHAR2 (25)
PID NUMBER
SID NUMBER
SESS_SERIAL # NUMBER
BLOCKER_IS_VALID VARCHAR2 (5)
BLOCKER_INSTANCE NUMBER
BLOCKER_OSID VARCHAR2 (25)
BLOCKER_PID NUMBER
BLOCKER_SID NUMBER
BLOCKER_SESS_SERIAL # NUMBER
BLOCKER_CHAIN_ID NUMBER
IN_WAIT VARCHAR2 (5)
TIME_SINCE_LAST_WAIT_SECS NUMBER
WAIT_ID NUMBER
WAIT_EVENT NUMBER
WAIT_EVENT_TEXT VARCHAR2 (64)
P1 NUMBER
P1_TEXT VARCHAR2 (64)
P2 NUMBER
P2_TEXT VARCHAR2 (64)
P3 NUMBER
P3_TEXT VARCHAR2 (64)
IN_WAIT_SECS NUMBER
TIME_REMAINING_SECS NUMBER
NUM_WAITERS NUMBER
ROW_WAIT_OBJ # NUMBER
ROW_WAIT_FILE # NUMBER
ROW_WAIT_BLOCK # NUMBER
ROW_WAIT_ROW # NUMBER
Continue to query the definition of this view. We can find that the data comes from the base table x $ ksdhng_chains. As we have discussed earlier that a process will perform a global hang once every 10 seconds, this view contains global information. Although it starts with v $.
SQL> select * from V $ FIXED_VIEW_DEFINITION where view_name like '% WAIT_CHAINS % ';
VIEW_NAME VIEW_DEFINITION
-------------------- Success ------------------------------------------------------------------------------------------------------------------------------------------------------
V $ WAIT_CHAINS select s. chain_id, decode (s. chain_is_cycle, 0, 'false', 'true'), s. chain_signature, s. chain_signature_hash, s. instance, s. osid,
S. pid, s. sid, s. sess_serial #, decode (s. blocker_is_valid, 0, 'false', 'true'), decode (s. blocker_is_valid, 0, to_number (null), s.
Blocker_instance), s. blocker_osid, decode (s. blocker_is_valid, 0, to_number (null), s. blocker_pid), decode (s. blocker_is_valid, 0,
To_number (null), s. blocker_sid), decode (s. blocker_is_valid, 0, to_number (null), s. blocker_sess_serial #), decode (s. blocker_chain
_ Id, 0, to_number (null), s. blocker_chain_id), decode (s. in_wait, 0, 'false', 'true'), decode (s. in_wait, 0, s. time_since_last_wait
_ Secs, to_number (null), decode (s. in_wait, 0, to_number (null), s. wait_id), decode (s. in_wait, 0, to_number (null), s. wait_event ),
S. wait_event_text, decode (s. in_wait, 0, to_number (null), s. p1), s. p1_text, decode (s. in_wait, 0, to_number (null), s. p2), s. p2
_ Text, decode (s. in_wait, 0, to_number (null), s. p3), s. p3_text, decode (s. in_wait, 0, to_number (null), s. in_wait_secs), decode (
S. in_wait, 0, to_number (null), s. time_remaining_secs), s. num_waiters, decode (s. in_wait, 0, to_number (null), s. row_wait_obj #),
Decode (s. in_wait, 0, to_number (null), s. row_wait_file #), decode (s. in_wait, 0, to_number (null), s. row_wait_block #), decode (s. in_w
Ait, 0, to_number (null), s. row_wait_row #) from X $ KSDHNG_CHAINS s
Oracle provides some scripts on mos for information diagnosis. One is available for common versions, and the other is dedicated to 11gR2. In the v $ session view of 11gR2, there is a field called final_blocking_session. This field can be used to view the top-level blocking person. The final blocker is generally at the top of wait_chain. This session will cause problems. Let's take a look at common queries. First, we can create two sessions to update a row together.
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr (wait_event_text, 1, 30)
2 FROM v $ wait_chains;
CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID BLOCKER_OSID SUBSTR (WAIT_EVENT_TEXT, 1, 30)
-----------------------------------------------------------------------------------------------------------------------------------------------
1 0 8 31377 31447 enq: TX-row lock contention
1 1 15 31447 SQL * Net message from client
General query:
Next, execute the next basic formatted script.
Sets pages 1000
Set lines 120
Set heading off
Column w_proc format a50 tru
Column instance format a20 tru
Column inst format a28 tru
Column wait_event format a50 tru
Column p1 format a16 tru
Column p2 format a16 tru
Column p3 format a15 tru
Column Seconds format a50 tru
Column sincelw format a50 tru
Column blocker_proc format a50 tru
Column waiters format a50 tru
Column chain_signature format a100 wra
Column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '| osid W_PROC, 'Sid' | I. instance_name INSTANCE,
'Inst #: '| instance INST, 'blocking Process:' | decode (blocker_osid, null, '', blocker_osid) |
'From instance' | blocker_instance BLOCKER_PROC, 'number of waiters: '| num_waiters waiters,
'Wait Event: '| wait_event_text wait_event, 'p1:' | P1 p1, 'p2: '| P2 p2, 'p3:' | P3 p3,
'Seconds in Wait: '| in_wait_secs Seconds, 'seconds Since Last Wait:' | time_since_last_wait_secs sincelw,
'Wait Chain: '| chain_id |': '| chain_signature, 'blocking Wait Chain:' | decode (blocker_chain_id, null,
'', Blocker_chain_id) blocker_chain
FROM v $ wait_chains wc,
V $ instance I
WHERE wc. instance = I. instance_number (+)
AND (num_waiters> 0
OR (blocker_osid IS NOT NULL
AND in_wait_secs> 10 ))
Order by chain_id,
Num_waiters DESC)
Where rownum: <101;
As shown in the final result, we can clearly see that process 12476 blocks process 13018. Process 13018 is waiting for enq: TX-row lock contention.
Current Process: 12476 SID orcl INST #: 1
Blocking Process: from Instance
Number of waiters: 2
Final Blocking Process: from Instance
Program:
Wait Event: SQL * Net message from client P1: 1650815232 P2: 1 P3: 0
Seconds in Wait: 2503
Seconds Since Last Wait:
Wait Chain: 1: 'SQL * Net message from client' <= 'enq: TX-row lock contention'
Blocking Wait Chain:
Current Process: 13018 SID orcl INST #: 1
Blocking Process: 12476 from Instance 1
Number of waiters: 0
Final Blocking Process: 12476 from Instance 1
Program: oracle @ rhel5 (TNS V1-V3)
Wait Event: enq: TX-row lock contention P1: 1415053318 P2: 458753 P3: 2465
Seconds in Wait: 441
Seconds Since Last Wait:
Wait Chain: 1: 'SQL * Net message from client' <= 'enq: TX-row lock contention'
Blocking Wait Chain:
Use the final_blocking_session field to find the top-end blocking process.
Sets pages 1000
Set lines 120
Set heading off
Column w_proc format a50 tru
Column instance format a20 tru
Column inst format a28 tru
Column wait_event format a50 tru
Column p1 format a16 tru
Column p2 format a16 tru
Column p3 format a15 tru
Column Seconds format a50 tru
Column sincelw format a50 tru
Column blocker_proc format a50 tru
Column fblocker_proc format a50 tru
Column waiters format a50 tru
Column chain_signature format a100 wra
Column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '| osid W_PROC, 'Sid' | I. instance_name INSTANCE,
'Inst #: '| instance INST, 'blocking Process:' | decode (blocker_osid, null, '', blocker_osid) |
'From instance' | blocker_instance BLOCKER_PROC,
'Number of waiters: '| num_waiters waiters,
'Final Blocking Process: '| decode (p. spid, null ,'',
P. spid) | 'from instance' | s. final_blocking_instance FBLOCKER_PROC,
'Program: '| p. Program image,
'Wait Event: '| wait_event_text wait_event, 'p1:' | wc. p1 p1, 'p2: '| wc. p2 p2, 'p3: '| wc. p3 p3,
'Seconds in Wait: '| in_wait_secs Seconds, 'seconds Since Last Wait:' | time_since_last_wait_secs sincelw,
'Wait Chain: '| chain_id |': '| chain_signature, 'blocking Wait Chain:' | decode (blocker_chain_id, null,
'', Blocker_chain_id) blocker_chain
FROM v $ wait_chains wc,
Gv $ session s,
Gv $ session bs,
Gv $ instance I,
Gv $ process p
WHERE wc. instance = I. instance_number (+)
AND (wc. instance = s. inst_id (+) and wc. sid = s. sid (+)
And wc. sess_serial # = s. serial # (+ ))
AND (s. final_blocking_instance = bs. inst_id (+) and s. final_blocking_session = bs. sid (+ ))
AND (bs. inst_id = p. inst_id (+) and bs. paddr = p. addr (+ ))
AND (num_waiters> 0
OR (blocker_osid IS NOT NULL
AND in_wait_secs> 10 ))
Order by chain_id,
Num_waiters DESC)
Where rownum: <101;
Current Process: 12028 SID orcl INST #: 1
Blocking Process: from Instance Number of waiters: 2
Wait Event: SQL * Net message from client P1: 1650815232 P2: 1 P3: 0
Seconds in Wait: 1155 Seconds Since Last Wait:
Wait Chain: 1: 'SQL * Net message from client' <= 'enq: TX-row lock contention' <= 'enq: TX-row lock c
Ontention'
Blocking Wait Chain:
Current Process: 12164 SID orcl INST #: 1
Blocking Process: 12028 from Instance 1 Number of waiters: 1
Wait Event: enq: TX-row lock contention P1: 1415053318 P2: 589825 P3: 2599
Seconds in Wait: 964 Seconds Since Last Wait:
Wait Chain: 1: 'SQL * Net message from client' <= 'enq: TX-row lock contention' <= 'enq: TX-row lock c
Ontention'
Blocking Wait Chain:
Current Process: 12342 SID orcl INST #: 1
Blocking Process: 12164 from Instance 1 Number of waiters: 0
Wait Event: enq: TX-row lock contention P1: 1415053318 P2: 327708 P3: 2417
Seconds in Wait: 954 Seconds Since Last Wait:
Wait Chain: 1: 'SQL * Net message from client' <= 'enq: TX-row lock contention' <= 'enq: TX-row lock c
Ontention'
Blocking Wait Chain:
Current Process: 12476 SID orcl INST #: 1
Blocking Process: from Instance Number of waiters: 1
Wait Event: SQL * Net message from client P1: 1650815232 P2: 1 P3: 0
Seconds in Wait: 578 Seconds Since Last Wait:
Wait Chain: 2: 'SQL * Net message from client' <= 'enq: TX-row lock contention'
Blocking Wait Chain:
Current Process: 12527 SID orcl INST #: 1
Blocking Process: 12476 from Instance 1 Number of waiters: 0
Wait Event: enq: TX-row lock contention P1: 1415053318 P2: 458753 P3: 2465
Seconds in Wait: 567 Seconds Since Last Wait:
Wait Chain: 2: 'SQL * Net message from client' <= 'enq: TX-row lock contention'
Blocking Wait Chain:
Here we can see that the current session is 2395 waiting for enq: TM-contention, and its top-level blocking is 2309. Through these scripts, we can easily query and find out who is blocking or even the top blocking. Of course, in our diaghang. SQL script, we see the following content. Here, Oracle uses our memory for direct access and obtains all chain information from x $ ksdhng_chains for the final hang analysis diagnosis.
-- Dump hang analysis chains
Oradebug direct_access enable trace
Oradebug direct_access disable reply
Oradebug direct_access set content_type = 'text/plain'
Oradebug direct_access select * from x $ ksdhng_chains
Chains most likely to have caused the hang:
[A] Chain 1 Signature: 'SQL * Net message from client' <= 'enq: TX-row lock contention'
Chain 1 Signature Hash: 0x38c48850
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified:
{
Instance: 1 (orcl. orcl)
OS id 13018
Process id: 39, oracle @ rhel5 (TNS V1-V3)
Session id: 1
Session serial #: 516
}
Is waiting for 'enq: TX-row lock contention 'with wait info:
{
P1: 'Name | mode' = 0x54580006
P2: 'usn <16 | slot' = 0x70001
P3: 'sequence '= 0x9a1
Time in wait: 27.987600 sec
Timeout after: never
Wait id: 52
Blocking: 0 sessions
Current SQL: update waitchain set name = 'liuyang2' where id = 3
Short stack: ksedsts () + 379 <-ksdxfstk () + 19 <-ksdxcb () + 1585 <-sspuser () + 100 <-semtimedop () + 36 <-sskgpwwait () + 202 <-skgpwwait () + 125 <-ksliwat () + 1564 <-kslwaitctx () + 138 <-ksqcmi () + 5784 <-ksqgtlctx () + 3334 <-ksqgelctx () + 450 <-ktcwit1 () + 260 <-kdddgb () + 7099 <-kdusru () + 412 <-kauupd () + 278 <-updrow () + 1596 <-qerupFetch () + 631 <-updaul () + 1079 <-updThreePhaseExe () + 262 <-updexe () + 571 <-opiexe () + 13536 <-kpoal8 () + 1888 <-opiodr () + 958 <-ttcpip () + 1109 <-opitsk () + 1325 <-opiino () + 827 <-opiodr () + 958 <-opidrv () + 479 <-sou2o () + 80 <-opimai_real () + 109 <-ssthrdmain () + 175 <-main () + 147 <-_
Wait history:
* Time between current wait and wait #1: 0.000071 sec
1. event: 'DB file sequential read'
Time waited: 0.000008 sec
Wait id: 51 p1: 'File # '= 0x3
P2: 'Block # '= 0x26d1a
P3: 'blocks '= 0x1
* Time between wait #1 and #2: 0.000043 sec
2. event: 'DB file sequential read'
Time waited: 0.000008 sec
Wait id: 50 p1: 'File # '= 0x3
P2: 'Block # '= 0xc5
P3: 'blocks '= 0x1
* Time between wait #2 and #3: 0.000047 sec
3. event: 'DB file sequential read'
Time waited: 0.000016 sec
Wait id: 49 p1: 'File # '= 0x3
P2: 'Block # '= 0x1bd
P3: 'blocks '= 0x1
}
And is blocked
=> Oracle session identified:
{
Instance: 1 (orcl. orcl)
OS id 12476
Process id: 37, oracle @ rhel5 (TNS V1-V3)
Session id: 52
Session serial #: 155
}
Which is waiting for 'SQL * Net message from client' with wait info:
{
P1: 'Driver ID' = 0x62657100
P2: '# bytes' = 0x1
Time in wait: 34 min 50 sec
Timeout after: never
Wait id: 49
Blocking: 2 sessions
Current SQL:
Short stack: ksedsts () + 379 <-ksdxfstk () + 19 <-ksdxcb () + 1585 <-sspuser () + 100 <-read () + 35 <-ntpfprd () + 95 <-nsbasic_brc () + 327 <-nsbrecv () + 72 <-nioqrc () + 449 <-_ PGOSF25_opikndf2 () + 954 <-opitsk () + 611 <-opiino () + 827 <-opiodr () + 958 <-opidrv () + 479 <-sou2o () + 80 <-opimai_real () + 109 <-ssthrdmain () + 175 <-main () + 147 <-_ libc_start_main () + 220 <-_ start () + 33
Wait history:
* Time between current wait and wait #1: 0.000020 sec
1. event: 'SQL * Net message to client'
Time waited: 0.000000 sec
Wait id: 48 p1: 'Driver ID' = 0x62657100
P2: '# bytes' = 0x1
* Time between wait #1 and #2: 0.000039 sec
2. event: 'SQL * Net message from client'
Time waited: 0.000260 sec
Wait id: 47 p1: 'Driver ID' = 0x62657100
P2: '# bytes' = 0x1
* Time between wait #2 and #3: 0.000462 sec
3. event: 'asynch descriptor resize'
Time waited: 0.000004 sec
Wait id: 46 p1: 'Outstanding # aio' = 0x0
P2: 'Current aio limit '= 0x80
P3: 'New aio limit '= 0x81
Chain 1 Signature: 'SQL * Net message from client' <= 'enq: TX-row lock contention'
Chain 1 Signature Hash: 0x38c48850
-------------------------------------------------------------------------------
