在oracle10g及oracle11g使用oradebug生成systemstate dump檔案系列二

背景

  資料庫出現效能問題時，有時需要生成一個systemstate DUMP檔案，便於進行全面的分析與診斷，本文了解如何生成此檔案的命令語法以及
如何用AWK指令碼格式化生成的DUMP檔案，如此便於查閱與分析，可讀性強。  

結論

1,oradebug生成SYSTEMSTATE DUMP檔案
2,採用ass109.awk指令碼格式化生成的DUMP檔案
3,oracle10g及oracle11g方法命令相同

測試

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


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


SQL> 


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_27273.trc
SQL> 


[oracle@seconary format_tool]$ ll
total 36
-rwxrwxrwx 1 oracle oinstall 34549 Nov 24  2003 ass109.awk
[oracle@seconary format_tool]$ awk -f ass109.awk  /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_27273.trc


Starting Systemstate 1
..............................................................................
.........................................................
Ass.Awk Version 1.0.9 - Processing /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_27273.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 'jobq slave wait'    
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'    
24: 0: waiting for 'jobq slave wait'    
25: 0: waiting for 'jobq slave wait'    
26: 0: waiting for 'rdbms ipc message'  
27: 0: waiting for 'jobq slave wait'    
28: 0: waiting for 'jobq slave wait'    
29: 0: waiting for 'jobq slave wait'    
30: 0: waiting for 'jobq slave wait'    
31: 0: waiting for 'jobq slave wait'    
32: 0: waiting for 'jobq slave wait'    
33: 0: waiting for 'jobq slave wait'    
34: 0: waiting for 'jobq slave wait'    
35: 0: waiting for 'jobq slave wait'    
36: 0: waiting for 'jobq slave wait'    
37: 0: waiting for 'jobq slave wait'    
38: 0: waiting for 'jobq slave wait'    
41: 0: waiting for 'jobq slave wait'    
42: 0: waiting for 'jobq slave wait'    
43: 0: waiting for 'jobq slave wait'    
44: 0: waiting for 'jobq slave wait'    
46: 0: waiting for 'jobq slave wait'    
48: 0: waiting for 'jobq slave wait'    
49: 0: waiting for 'jobq slave wait'    
50: 0: waiting for 'jobq slave wait'    
52: 0: waiting for 'jobq slave wait'    
54: 0: waiting for 'jobq slave wait'    
55: 0: waiting for 'jobq slave wait'    
56: 0: waiting for 'jobq slave wait'    
57: 0: waiting for 'jobq slave wait'    
59: 0: waiting for 'jobq slave wait'    
60: 0: waiting for 'jobq slave wait'    
63: 0: waiting for 'jobq slave wait'    
64: 0: waiting for 'jobq slave wait'    
65: 0: waiting for 'jobq slave wait'    
66: 0: waiting for 'jobq slave wait'    
67:                                     
69: 0: waiting for 'jobq slave wait'    
70: 0: waiting for 'jobq slave wait'    
71: 0: waiting for 'jobq slave wait'    
72: 0: waiting for 'jobq slave wait'    
73: 0: waiting for 'jobq slave wait'    
74: 0: waiting for 'SQL*Net message from client' 
75: 0: waiting for 'jobq slave wait'    
76: 0: waiting for 'jobq slave wait'    
77: 0: waiting for 'jobq slave wait'    
78: 0: waiting for 'jobq slave wait'    
79: 0: waiting for 'jobq slave wait'    
80: 0: waiting for 'jobq slave wait'    
81: 0: waiting for 'jobq slave wait'    
82: 0: waiting for 'jobq slave wait'    
83: 0: waiting for 'jobq slave wait'    
85: 0: waiting for 'jobq slave wait'    
86: 0: waiting for 'jobq slave wait'    
87: 0: waiting for 'jobq slave wait'    
89: 0: waiting for 'jobq slave wait'    
90: 0: waiting for 'jobq slave wait'    
91: 0: waiting for 'jobq slave wait'    
92: 0: waiting for 'jobq slave wait'    
93: 0: waiting for 'jobq slave wait'    
94: 0: waiting for 'jobq slave wait'    
95: 0: waiting for 'jobq slave wait'    
96: 0: waiting for 'jobq slave wait'    
97: 0: waiting for 'jobq slave wait'    
98: 0: waiting for 'jobq slave wait'    
99: 0: waiting for 'jobq slave wait'    
100:0: waiting for 'jobq slave wait'    
101:0: waiting for 'jobq slave wait'    
102:0: waiting for 'jobq slave wait'    
103:0: waiting for 'jobq slave wait'    
104:0: waiting for 'jobq slave wait'    
105:0: waiting for 'jobq slave wait'    
106:0: waiting for 'jobq slave wait'    
107:0: waiting for 'jobq slave wait'    
108:0: waiting for 'jobq slave wait'    
109:0: waiting for 'jobq slave wait'    
110:0: waiting for 'jobq slave wait'    
114:0: waiting for 'jobq slave wait'    
116:0: waiting for 'jobq slave wait'    
117:0: waiting for 'jobq slave wait'    
118:0: waiting for 'jobq slave wait'    
119:0: waiting for 'jobq slave wait'    
120:0: waiting for 'jobq slave wait'    
123:0: waiting for 'jobq slave wait'    
134:0: waiting for 'jobq slave wait'    
136:0: waiting for 'jobq slave wait'    
137:0: waiting for 'jobq slave wait'    
138:0: waiting for 'jobq slave wait'    
139:0: waiting for 'rdbms ipc message'  
142:0: waiting for 'jobq slave wait'    
143:0: waiting for 'jobq slave wait'    
144:0: waiting for 'jobq slave wait'    
145:0: waiting for 'jobq slave wait'    
146:0: waiting for 'jobq slave wait'    
147:0: waiting for 'jobq slave wait'    
148:0: waiting for 'jobq slave wait'    
149:0: waiting for 'jobq slave wait'    
150:0: waiting for 'jobq slave wait'    
151:0: waiting for 'jobq slave wait'    
152:0: waiting for 'jobq slave wait'    
156:0: waiting for 'jobq slave wait'    
158:0: waiting for 'jobq slave wait'    
160:0: waiting for 'jobq slave wait'    
162:0: waiting for 'jobq slave wait'    
164:0: waiting for 'jobq slave wait'    
167:0: waiting for 'jobq slave wait'    
169:0: waiting for 'jobq slave wait'    
170:0: waiting for 'jobq slave wait'    
172:0: waiting for 'jobq slave wait'    
173:0: waiting for 'jobq slave wait'    
174:0: waiting for 'Space Manager: slave idle wait' 
176:0: waiting for 'jobq slave wait'    
178:0: waiting for 'jobq slave wait'    
179:0: waiting for 'jobq slave wait'    


NO BLOCKING PROCESSES FOUND




33572 Lines Processed.
[oracle@seconary format_tool]$ 

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


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi




[ora10g@seconary format_tool]$ awk -f ass109.awk /home/ora10g/admin/ora10g/udump/ora10g_ora_2324.trc


Starting Systemstate 1
.............................
Ass.Awk Version 1.0.9 - Processing /home/ora10g/admin/ora10g/udump/ora10g_ora_2324.trc


System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  waiting for 'pmon timer'            
3:  waiting for 'rdbms ipc message'     
4:  waiting for 'rdbms ipc message'     
5:  waiting for 'rdbms ipc message'     
6:  waiting for 'rdbms ipc message'     
7:  waiting for 'rdbms ipc message'     
8:  waiting for 'smon timer'            
9:  waiting for 'rdbms ipc message'     
10: waiting for 'rdbms ipc message'     
11: waiting for 'rdbms ipc message'     
12: waiting for 'rdbms ipc message'     
13:                                     
14:                                     
15:                                     
16:                                     
17:                                     
18:                                     
19:                                     
20:                                     
21:                                     
22: last wait for 'SQL*Net message from client' 
23: waiting for 'rdbms ipc message'     
24: waiting for 'rdbms ipc message'     
25: waiting for 'i/o slave wait'        
26: waiting for 'i/o slave wait'        
27: waiting for 'Streams AQ: qmn coordinator idle wait' 
30: waiting for 'Streams AQ: waiting for time management or cleanup tasks' 
31: waiting for 'Streams AQ: qmn slave idle wait' 


NO BLOCKING PROCESSES FOUND




12430 Lines Processed.
[ora10g@seconary format_too

參考資料

awk格式指令碼下載地址： />

個人簡介

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/