基於oracle11g生成systemstate dump檔案分析持鎖會話有等待鎖會話及閂latch之系列三

背景

oracle systemstate dump檔案可以從全域性顯示程式及會話的等待詳細資訊

結論

1, 透過AWK格式化即可顯示每個程式等待哪些資源，也會顯示持鎖程式及會話的資訊
2, 透過檢視原始SYSTEMSTATE DUMP檔案可以檢視哪些會話及程式在等待每個程式,也可以獲知持鎖程式及會話有相關資訊
3  檢視原始SYSTEMSTATE DUMP檔案，可以透過關鍵字process x以及blocked by進行分析等待會話及持鎖會話
4, 原始檔案的owner及creator可以把樹狀結構的SO物件關聯起來
5, so有程式SO,會話SO，佇列SO，還有LATCH SO
6, 原始systemstate dump檔案包含資訊非常複雜，價值量巨大

測試

1，資料庫版本


SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


2,模擬事務鎖情況


會話1
SQL> select spid,pid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));


SPID                                                    PID
------------------------------------------------ ----------
13675                                                   180


SQL> create table t_lock(a int);


Table created.


SQL> insert into t_lock values(1);


1 row created.


SQL> commit;


Commit complete.






會話2
SQL>  select spid,pid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));


SPID                                                    PID
------------------------------------------------ ----------
13654                                                   174


鎖住
SQL> delete from t_lock;


3，在監控會話生成一個systemstate dump檔案
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_13950.trc
SQL> 


[oracle@seconary ~]$ cd format_tool/
[oracle@seconary format_tool]$ awk -f ass109.awk  /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_13950.trc


Starting Systemstate 1
..............................................................................
...............................................................................
........................
Ass.Awk Version 1.0.9 - Processing /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_13950.trc


System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  0: waiting for 'pmon timer'         
3:  0: waiting for 'VKTM Logical Idle Wait' 
4:  0: waiting for 'rdbms ipc message'  
5:  0: waiting for 'DIAG idle wait'     
6:  0: waiting for 'rdbms ipc message'  
7:  0: waiting for 'rdbms ipc message'  
8:  0: waiting for 'DIAG idle wait'     
9:  0: waiting for 'rdbms ipc message'  
10: 0: waiting for 'rdbms ipc message'  
11: 0: waiting for 'rdbms ipc message'  
12: 0: waiting for 'rdbms ipc message'  
13: 0: waiting for 'smon timer'         
14: 0: waiting for 'rdbms ipc message'  
15: 0: waiting for 'rdbms ipc message'  
16: 0: waiting for 'rdbms ipc message'  
17:                                     
18:                                     
19: 0: waiting for 'resmgr:cpu quantum' 
20: 0: waiting for 'Streams AQ: qmn coordinator idle wait' 
21: 0: waiting for 'Streams AQ: qmn slave idle wait' 
22: 0: waited for 'Streams AQ: waiting for time management or cleanup tasks' 
23: 0: waiting for 'jobq slave wait'    
類似內容略    
171:0: waiting for 'jobq slave wait'    
172:0: waiting for 'jobq slave wait'    
173:0: waiting for 'jobq slave wait'    
174:0: waiting for 'enq: TX - row lock contention'[Enqueue TX-0098000F-0000037A] ---可見174程式即V$PROCESS的PID，為上述的會話2正在等待TX鎖，等待命令為DELETE語句
     Cmd: Delete
175:0: waiting for 'jobq slave wait'    
176:0: waiting for 'jobq slave wait'    
177:0: waiting for 'jobq slave wait'    
178:0: waiting for 'jobq slave wait'    
179:0: waiting for 'jobq slave wait'    
180:0: waiting for 'SQL*Net message from client' 
181:0: waiting for 'jobq slave wait'    
Blockers
~~~~~~~~


        Above is a list of all the processes. If they are waiting for a resource
        then it will be given in square brackets. Below is a summary of the
        waited upon resources, together with the holder of that resource.
        Notes:
        ~~~~~
         o A process id of '???' implies that the holder was not found in the
           systemstate.


                    Resource Holder State
Enqueue TX-0098000F-0000037A   180: 0: waiting for 'SQL*Net message from client'


Object Names
~~~~~~~~~~~~
Enqueue TX-0098000F-0000037A                                  




53477 Lines Processed.
[oracle@seconary format_tool]$ 


4,經過AWK格式化後，SYSTEMSTATE DUMP檔案只能看到每個程式在等待什麼，但如果想每個程式，都有哪些程式或會話在等待這個程式，還要是看原始的SYSTEMSTATE DUMP檔案
  所以我們只要在原始的SYSTEMSTATE DUMP檔案檢視process 180，因為這個會話是持鎖會話，而174程式的會話需要等待它
  如下為獲取的到的原始檔案部分


PROCESS 180: --180號程式，對應V$PROCESS的PID
  ----------------------------------------
  SO: 0xdd6548c0, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3 --owner為nil說明它是頂層SO
   proc=0xdd6548c0, name=process, file=ksu.h LINE:11459, pg=0
  (process) Oracle pid:180, ser:4, calls cur/top: (nil)/0xd27e7f20
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x0) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
  ksudlp FALSE at location: 0
  (post info) last post received: 134 0 2
              last post received-location: ksl2.h LINE:2165 ID:kslpsr
              last process to post me: dd5a8e80 1 6
              last post sent: 0 0 26
              last post sent-location: ksa2.h LINE:282 ID:ksasnd
              last process posted by me: dd5a8e80 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0xdd9a34d0
    O/S info: user: oracle, term: UNKNOWN, ospid: 13675 
    OSD pid info: Unix process pid: 13675, image: oracle@seconary (TNS V1-V3) --UNIX PROCESS PID 13675對應V$PROCESS的SPID
Dump of memory from 0x00000000DD535A20 to 0x00000000DD535C28
0DD535A20 00000000 00000000 00000000 00000000  [................]
        Repeat 31 times
0DD535C20 00000000 00000000                    [........]        
    (FOB) flags=2050 fib=0xd8f353a0 incno=0 pending i/o cnt=0
     fname=/oracle/oradata/guowang/users01.dbf
     fno=4 lblksz=8192 fsiz=107360
    (FOB) flags=2050 fib=0xd8f34188 incno=8 pending i/o cnt=0
     fname=/oracle/oradata/guowang/system01.dbf
     fno=1 lblksz=8192 fsiz=99840
    ----------------------------------------
    SO: 0xdc808b40, type: 4, owner: 0xdd6548c0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3  --OWNER為0xdd6548c0，說明這個SO是孩子級的SO,它是180程式的子級SO
     proc=0xdd6548c0, name=session, file=ksu.h LINE:11467, pg=0
    (session) sid: 330 ser: 501 trans: 0xd98335c0, creator: 0xdd6548c0  --SID為V$SESSION的SID，為330，CREATOR與上級的PROCESS 180對應
              flags: (0x45) USR/- flags_idl: (0x0) -/-/-/-/-/-
              flags2: (0x40008) -/-
              DID: , short-term DID: 
              txn branch: (nil)
              oct: 0, prv: 0, sql: (nil), psql: 0xde1653d0, user: 84/SCOTT
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/3, ospid: 13674
      machine: seconary program: sqlplus@seconary (TNS V1-V3)
      application name: SQL*Plus, hash value=3669949024
    Current Wait Stack:  ---這會顯示當前會話在等待什麼事件
     0: waiting for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=100 seq_num=101 snap_id=1
        wait times: snap=1 min 25 sec, exc=1 min 25 sec, total=1 min 25 sec
        wait times: max=infinite, heur=1 min 25 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x1a0
    There are 1 sessions blocked by this session.  --顯示哪些會話在等待當前的會話及所屬的程式
    Dumping one waiter:
      inst: 1, sid: 145, ser: 643   ---顯示等待的會話及會話序列號
      wait event: 'enq: TX - row lock contention'  --顯示等待事件資訊
        p1: 'name|mode'=0x54580006  --等待的P1,P2,P3
        p2: 'usn<<16 | slot'=0x98000f
        p3: 'sequence'=0x37a
      row_wait_obj#: 74375, block#: 98891, row#: 0, file# 4  --顯示具體等待的物件及資料塊和行號及檔案
      min_blocked_time: 79 secs, waiter_cache_ver: 823

個人簡介

8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。

服務過的客戶：

中國電信

中國移動

中國聯通

中國電通

國家電網

四川達州商業銀行

湖南老百姓大藥房

山西省公安廳

中國郵政

北京302醫院     

河北廊坊新奧集團公司

 專案經驗:

中國電信3G專案AAA系統資料庫部署及最佳化
      中國聯通4G資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg 
       貴州移動crm及客服資料庫效能最佳化專案
       貴州移動crm及客服務資料庫sql稽核專案
       深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq   :   305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900    

itpub部落格名稱：wisdomone1    http://blog.itpub.net/9240380/