探讨因Oracle表碎片化而引发的故障诊断与分析
点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！
2.1 登录检查数据库运行情况
发现数据库存在大量enq类等待事件，主要为索引相关"enq: TX - index contention"。
INST_ID EVENT# EVENT COUNT(1)<br> ---------- ---------- ---------------------------------------------------------------- ----------<br> 1 180 db file scattered read 1<br> 1 187 gc cr request 1<br> 1 1496 enq: PS - contention 1<br> 1 538 inactive session 1<br> 1 307 enq: TX - row lock contention 2<br> 1 198 gc cr block 3-way 2<br> 1 205 gc current block busy 3<br> 1 441 SQL*Net message to client 4<br> 1 308 enq: TX - allocate ITL entry 80<br> 1 324 enq: HW - contention 81<br> 1 309 enq: TX - index contention 5203<br> 2 205 gc current block busy 1<br> 2 189 gc cr multi block request 1<br> 2 87 latch: MGA shared context root latch 1<br> 2 531 wait list latch free 1<br> 2 871 LGWR wait for redo copy 1<br> 2 354 row cache lock 1<br> 2 187 gc cr request 2<br> 2 179 db file sequential read 2<br> 2 180 db file scattered read 4<br> 2 562 PGA memory operation 5<br> 2 186 gc current request 5<br> 2 121 gc buffer busy acquire 8<br> 2 441 SQL*Net message to client 12<br> 2 324 enq: HW - contention 45<br> 2 307 enq: TX - row lock contention 92<br> 2 529 latch free 195<br> 2 308 enq: TX - allocate ITL entry 726<br> 2 309 enq: TX - index contention 1709<br> 3 1493 PX Deq: Slave Session Stats 1<br> 3 354 row cache lock 1<br> 3 186 gc current request 1<br> 3 179 db file sequential read 1<br> 3 632 process diagnostic dump 1<br> 3 198 gc cr block 3-way 2<br> 3 205 gc current block busy 2<br> 3 18 Disk file operations I/O 3<br> 3 441 SQL*Net message to client 8<br> 3 308 enq: TX - allocate ITL entry 438<br> 3 309 enq: TX - index contention 983<br> 4 180 db file scattered read 1<br> 4 18 Disk file operations I/O 1<br> 4 186 gc current request 1<br> 4 1090 enq: TX - contention 1<br> 4 221 gc current grant 2-way 1<br> 4 205 gc current block busy 1<br> 4 197 gc cr block 2-way 1<br> 4 198 gc cr block 3-way 2<br> 4 307 enq: TX - row lock contention 3<br> 4 441 SQL*Net message to client 8<br> 4 324 enq: HW - contention 20<br> 4 308 enq: TX - allocate ITL entry 52<br> 4 309 enq: TX - index contention 964<br> 5 1483 PX Deq: Join ACK 1<br> 6 610 ksxr poll remote instances 1<br> 6 179 db file sequential read 1
