批操作效能影響診斷
分公司的資料庫出現效能問題，幫忙協助診斷。
資料庫是Oracle 10.2.0.3 Rac環境，不過現在處於單節點工作狀態。當前的問題是頁面訪問很慢，沒有其他進一步的資訊。
根據描述，登陸伺服器後首先使用top檢查了系統執行狀況，結果發現系統並不是很忙：
bash-3.00$ /usr/local/bin/top
load averages: 1.76, 1.63, 1.59; up 19+23:45:48 16:12:20
194 processes: 189 sleeping, 5 on cpu
CPU states: 78.6% idle, 17.5% user, 3.9% kernel, 0.0% iowait, 0.0% swap
Memory: 32G phys mem, 8755M free mem, 31G swap, 31G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
1151 oracle 1 1 0 20G 20G cpu 0:19 27.59% oracle
26096 oracle 11 32 0 20G 20G sleep 3:07 24.00% oracle
1177 oracle 11 30 0 20G 20G cpu 2:17 20.57% oracle
1187 oracle 11 44 0 20G 20G cpu 2:03 18.42% oracle
25298 oracle 11 50 0 20G 20G sleep 3:06 15.12% oracle
1557 oracle 1 52 0 20G 20G sleep 1:57 13.64% oracle
27086 oracle 12 52 0 20G 20G sleep 3:15 10.45% oracle
1175 oracle 1 24 0 20G 20G sleep 0:19 6.12% oracle
1551 oracle 1 29 0 20G 20G cpu 1:28 4.56% oracle
27076 oracle 1 100 -20 20G 20G sleep 14:56 4.02% oracle
27080 oracle 1 100 -20 20G 20G sleep 14:29 3.20% oracle
27090 oracle 2 59 0 20G 20G sleep 2:48 2.49% oracle
10672 oracle 1 59 0 3800K 2536K cpu 0:00 0.41% top
27131 oracle 1 59 0 20G 20G sleep 0:19 0.34% oracle
27123 oracle 11 59 0 20G 20G sleep 0:59 0.32% oracle
IOWAIT為0，使用者部分只有17.5%，而IDLE達到了將近80%，這種情況應該是系統很輕閒才對。
根據判斷問題和系統壓力沒有關係，問題應該發生在資料庫層面上，而作業系統層面工作正常。
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 9月 15 16:12:28 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select event, count(*)
2 from v$session
3 where username != user
4 group by event
5 having count(*) > 4;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
enq: TX - row lock contention 7
SQL*Net message from client 57
既然前臺頁面反映系統很慢，肯定存在等待，檢查一下當前系統中主要的等待事件是什麼。發現絕大部分會話都處於空閒狀態，但是有一些會話在等待鎖。
檢查V$LOCK檢視：
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---- -- -------- ---- ----- ------- ----- -----
00000008D43C7918 00000008D43C7938 548 XR 0 0 1 0 12409 2
00000008D43C79B0 00000008D43C79D0 551 RT 2 2 6 0 12291 2
00000008D43C7A48 00000008D43C7A68 484 TX 11272251 19 0 6 229 0
00000008D43C7AE0 00000008D43C7B00 551 MR 1 0 4 0 12291 2
00000008D43C7B78 00000008D43C7B98 551 MR 2 0 4 0 12291 2
.
.
.
00000008D43CA4D0 00000008D43CA4F0 551 MR 205 0 4 0 12291 2
00000008D43CA568 00000008D43CA588 459 TX 11272251 19 0 6 130 0
00000008D43CA600 00000008D43CA620 551 DM 1 0 4 0 12287 2
00000008D43CA698 00000008D43CA6B8 442 TX 11272251 19 0 6 104 0
00000008D43CA730 00000008D43CA750 475 TX 11272251 19 0 6 38 0
00000006943C6468 00000006943C6488 1097 XR 4 0 1 0 0 2
00000006943C6500 00000006943C6520 1029 TX 11272251 19 0 6 638 0
00000006943C6598 00000006943C65B8 989 TX 11272251 19 0 6 390 0
00000006943C6630 00000006943C6650 1097 CF 0 0 2 0 12403 2
00000006943C66C8 00000006943C66E8 1097 RS 25 1 2 0 12399 2
00000006943C67F8 00000006943C6818 1098 RT 2 0 6 0 12399 2
00000006943C69C0 00000006943C69E0 1098 RT 2 1 6 0 12291 2
00000006943C6A58 00000006943C6A78 998 TX 11272251 19 0 6 378 0
00000006943C6AF0 00000006943C6B10 1096 TS 3 2 3 0 12277 2
00000008D7C13778 00000008D7C137A0 484 TM 222937 0 2 0 229 2
00000008D7C13878 00000008D7C138A0 484 TM 222971 0 3 0 229 2
00000008D7C13978 00000008D7C139A0 484 TM 223250 0 2 0 229 2
00000008D7C13A78 00000008D7C13AA0 484 TM 243441 0 3 0 229 2
00000008D7C13B78 00000008D7C13BA0 484 TM 223188 0 3 0 229 2
00000008D7C13C78 00000008D7C13CA0 459 TM 222903 0 2 0 141 2
00000008D7C13D78 00000008D7C13DA0 459 TM 222971 0 3 0 130 2
00000008D7C13E78 00000008D7C13EA0 459 TM 223250 0 3 0 141 2
00000008D7C13F90 00000008D7C13FB8 459 TM 222937 0 2 0 130 2
00000008D7C14090 00000008D7C140B8 459 TM 243441 0 3 0 130 2
00000008D7C14190 00000008D7C141B8 459 TM 223188 0 3 0 130 2
00000008D7C14290 00000008D7C142B8 442 TM 222937 0 2 0 104 2
00000008D7C14390 00000008D7C143B8 442 TM 222971 0 3 0 104 2
00000008D7C14490 00000008D7C144B8 442 TM 223250 0 2 0 104 2
00000008D7C14590 00000008D7C145B8 442 TM 243441 0 3 0 104 2
00000008D7C14690 00000008D7C146B8 442 TM 223188 0 3 0 104 2
00000008D7C147A8 00000008D7C147D0 475 TM 222937 0 2 0 38 2
00000008D7C148A8 00000008D7C148D0 475 TM 222971 0 3 0 38 2
00000008D7C149A8 00000008D7C149D0 475 TM 223250 0 2 0 38 2
00000008D7C14AA8 00000008D7C14AD0 475 TM 243441 0 3 0 38 2
00000008D7C14BA8 00000008D7C14BD0 475 TM 223188 0 3 0 38 2
00000008D7C14CA8 00000008D7C14CD0 476 TM 223477 0 2 0 0 2
00000008D7C14DA8 00000008D7C14DD0 476 TM 223483 0 3 0 0 2
00000008D7C14EA8 00000008D7C14ED0 476 TM 223482 0 3 0 0 2
00000008D7C14FC0 00000008D7C14FE8 476 TM 223485 0 3 0 0 2
00000008D7C162F0 00000008D7C16318 434 TM 223188 0 3 0 3392 2
0000000697C12138 0000000697C12160 1029 TM 222937 0 2 0 638 2
0000000697C12238 0000000697C12260 1029 TM 222971 0 3 0 638 2
0000000697C12338 0000000697C12360 1029 TM 223250 0 2 0 638 2
0000000697C12438 0000000697C12460 1029 TM 243441 0 3 0 638 2
0000000697C12538 0000000697C12560 1029 TM 223188 0 3 0 638 2
0000000697C12638 0000000697C12660 989 TM 222937 0 2 0 390 2
0000000697C12738 0000000697C12760 989 TM 222971 0 3 0 390 2
0000000697C12838 0000000697C12860 989 TM 223250 0 2 0 390 2
0000000697C12950 0000000697C12978 989 TM 243441 0 3 0 390 2
0000000697C12A50 0000000697C12A78 989 TM 223188 0 3 0 390 2
0000000697C12B50 0000000697C12B78 998 TM 222937 0 2 0 378 2
0000000697C12C50 0000000697C12C78 998 TM 222971 0 3 0 378 2
0000000697C12D50 0000000697C12D78 998 TM 223250 0 2 0 378 2
0000000697C12E50 0000000697C12E78 998 TM 243441 0 3 0 378 2
0000000697C12F50 0000000697C12F78 998 TM 223188 0 3 0 378 2
00000008D7CAEA98 00000008D7CAEAD0 434 TX 11272251 19 6 0 1457 1
00000008D7CCE1B8 00000008D7CCE1F0 476 TX 17694738 16 6 0 0 2
00000008D7D256F8 00000008D7D25730 459 TX 14876753 15 6 0 141 2
00000008D7D7EEB8 00000008D7D7EEF0 442 TX 18022408 20 6 0 104 2
00000008D7D7FFF8 00000008D7D80030 475 TX 16056394 10 6 0 38 2
00000008D7D9EE78 00000008D7D9EEB0 484 TX 2490446 5278 6 0 229 2
0000000697D9BE58 0000000697D9BE90 989 TX 5767249 353 6 0 390 2
0000000697DB9B98 0000000697DB9BD0 998 TX 10354767 11 6 0 378 2
0000000697DD78D8 0000000697DD7910 1029 TX 13041703 13 6 0 638 2
已選擇137行。
可以看到，V$LOCK檢視中存在大量的鎖資訊，多個會話處於被鎖定的狀態，進一步檢查這個檢視：
SQL> select * from v$lock where block = 1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---- -- -------- --- ----- ------- ----- -----
00000008D7CAEA98 00000008D7CAEAD0 434 TX 11272251 19 6 0 1491 1
SQL> select * from v$lock where sid = 434;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---- -- --------- ---- ----- ------- ----- -----
00000008D7C162F0 00000008D7C16318 434 TM 223188 0 3 0 3506 2
00000008D7CAEA98 00000008D7CAEAD0 434 TX 11272251 19 6 0 1571 1
找到鎖定其他會話的會話，查詢鎖定的物件：
SQL> select owner, object_name, object_type
2 from dba_objects
3 where object_id = 223188;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
ZHEJIANG ORD_HIT_COMM TABLE
這張表是系統中資料量最大的一張表，也是前臺頁面經常需要訪問的一張表，這張表被鎖定肯定會導致眾多的會話處於等待狀態。
SQL> select sql_text
2 from v$sql
3 where hash_value =
4 (select sql_hash_value from v$session where sid = 434);
SQL_TEXT
--------------------------------------------------------------------------------------------
update ord_hit_comm h set h.enable_flag = '2', h.last_update_date = sysdate where h.plat_id in ('DATA100000000000028
24837', 'DATA10000000000012870015', 'DATA10000000000012690072',
'DATA10000000000012390004', 'DATA10000000000012870014', 'DATA1000000000
0012870016') and h.sender_orgid = 'DATA10000000000013061647' and h.contract_id in ('ZJJY10000000000044867537',
'ZJJY10000000000040142580', 'ZJJY10000000000040142582',
'ZJJY10000000000040142584', 'ZJJY10000000000040142586', 'ZJJY1
0000000000040142588', 'ZJJY10000000000040142590', 'ZJJY100000000000401
42592', 'ZJJY10000000000040142594', 'ZJJY10000000000040142596',
'ZJJY1000000000004014
這個會話執行的操作顯然是一個大批次的更新操作，按道理來說，這種操作應該是夜間執行的後臺JOB才對，莫非是JOB執行的時間出現了偏差，檢查詳細的會話資訊：
SQL> select username, program, terminal, action
2 from v$session where sid = 434;
USERNAME PROGRAM TERMINAL ACTION
------------------------------ --------------- --------------- --------------------
ZHEJIANG_PROJ plsqldev.exe KYLIN-PC SQL Window - New
顯然是有人在處理批次更新，正是這個操作導致了大量會話被鎖：
SQL> select * from v$lock where id1 = 223188;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ------ -- ------- ---- ----- ------- ----- -----
00000008D7C13B78 00000008D7C13BA0 484 TM 223188 0 3 0 624 2
00000008D7C14190 00000008D7C141B8 459 TM 223188 0 3 0 525 2
00000008D7C14690 00000008D7C146B8 442 TM 223188 0 3 0 499 2
00000008D7C14BA8 00000008D7C14BD0 475 TM 223188 0 3 0 433 2
00000008D7C150C0 00000008D7C150E8 456 TM 223188 0 3 0 346 2
00000008D7C155C0 00000008D7C155E8 476 TM 223188 0 3 0 324 2
00000008D7C15AD8 00000008D7C15B00 547 TM 223188 0 3 0 185 2
00000008D7C162F0 00000008D7C16318 434 TM 223188 0 3 0 3787 2
0000000697C12538 0000000697C12560 1029 TM 223188 0 3 0 1033 2
0000000697C12A50 0000000697C12A78 989 TM 223188 0 3 0 785 2
0000000697C12F50 0000000697C12F78 998 TM 223188 0 3 0 773 2
0000000697C13468 0000000697C13490 1036 TM 223188 0 3 0 392 2
0000000697C13C80 0000000697C13CA8 976 TM 223188 0 3 0 95 2
0000000697C13F80 0000000697C13FA8 990 TM 223188 0 3 0 46 2
0000000697C14898 0000000697C148C0 995 TM 223188 0 3 0 24 2
已選擇15行。
除了在進行批處理的會話外，還有14個會話被鎖，這些會話在得不到鎖之前，一直處於等待狀態，因此前臺頁面的感覺就是系統響應很慢，出不來結果。
SQL> select num_rows, blocks from dba_tables where table_name = 'ORD_HIT_COMM' and wner = 'ZHEJIANG';
NUM_ROWS BLOCKS
---------- ----------
13301787 963930
對一個上千萬的表的批次更新，這種操作不但會佔用大量的系統資源，更重要的是會長時間的鎖住大量的資料，導致系統平均響應時間迅速增加，甚至使得系統處於不可用的狀態。因此這種操作對於OLTP系統而言是非常危險的，應該在合理的時間利用合理的方法進行操作。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-674033/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 效能診斷工具Oracle
- 案例 - EBS SQL效能診斷SQL
- 等待事件效能診斷方法事件
- Oracle效能診斷藝術Oracle
- mysql刪除和更新操作對效能的影響MySql
- mysql資料庫效能診斷MySql資料庫
- MySQL效能診斷與調優MySql
- Oracle效能診斷一例Oracle
- 基於等待事件的效能診斷事件
- Oracle效能診斷檢視總結Oracle
- 影響flashback table的操作！
- 基於等待事件的效能診斷(轉)事件
- 一次ORACLE IO效能診斷案例Oracle
- .NET Core-全域性效能診斷工具
- 某公司oracle 效能調優診斷案例Oracle
- Oracle SQLT 診斷SQL語句效能(3)OracleSQL
- Oracle SQLT 診斷SQL語句效能(2)OracleSQL
- Oracle SQLT 診斷SQL語句效能(1)OracleSQL
- 利用資源限制效能診斷resource limitMIT
- Oracle效能問題診斷一例Oracle
- [zt] 影響SQL效能的原因SQL
- 第49問：如何快速判斷 IO 延遲對 MySQL 效能的影響MySql
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- Rollback&Truncate操作對高水位線影響之效能優化篇優化
- sp_sysmon效能診斷結果分析(zt)
- AIX+weblogic效能診斷記錄1AIWeb
- 深入淺出等待事件和效能診斷01事件
- 深入淺出等待事件和效能診斷02事件
- 深入淺出等待事件和效能診斷04事件
- 深入淺出等待事件和效能診斷05事件
- 影響mysql效能的因素都有哪些MySql
- 影響HTTP效能的常見因素HTTP
- 影響MySQL效能的硬體因素MySql
- 影響MySQL效能的硬體因MySql
- Part II 診斷和優化資料庫效能優化資料庫
- Spark效能優化：診斷記憶體的消耗Spark優化記憶體
- 使用Dstat來進行Linux綜合效能診斷Linux