【博客文章2025】RAC集群性能调优诊断脚本
Author: Bo Tang
1. RAC集群性能调优应该关注的重要视图：
1.1 gv$system_event：
select * FROM gv$system_event order by 1,2; |
1.2 查询结果：
gv$system_event的字段如下：
eg. 1
eg. ADR block file read
eg. 4259 次数
eg. 0 厘秒
eg. 43 厘秒
eg. 0.01=43/4259=0.01 厘秒
eg.432068=43*10^-2*10^6=430000 微秒
TOTAL_WAITS_FG eg. 66 次数
TOTAL_TIMEOUTS_FG eg. 0 厘秒
eg. 2 厘秒
AVERAGE_WAIT_FG eg. 0.02=2/66=0.03 厘秒
TIME_WAITED_MICRO_FG eg. 15860=2*10^-2*10^6=20000 微秒
eg.1780066010
eg. 1893977003
eg. 0
eg. Other
eg. 0
INST_ID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO TOTAL_WAITS_FG TOTAL_TIMEOUT S_FG TIME_WAITED_FG AVERAGE_WAIT_FG TIME_WAITED_MICRO_FG EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS CON_ID 1 ADR block file read 4259 0 43 0.01 432068 66 0 2 0.02 15860 1780066010 1893977003 0 Other 0 1 ADR block file write 2530 0 54 0.02 5408270 0 0 0 4092822979 1893977003 0 Other 0 1 ADR file lock 792 0 2 0 15273 0 0 0 0 0 660190475 1893977003 0 Other 0 1 AQ Background Master: slave start 102 0 3 0.03 31182 0 0 0 0 0 521075289 1893977003 0 Other 0 1 AQ: RAC AQ Network 6013686 6013686 4224 0 42239349 0 0 0 0 0 2363885740 1893977003 0 Other 0 1 AQPC idle 19940 19939 60232632 3020.69 602326319689 0 0 0 0 0 3367618326 2723168908 6 Idle 0 1 AQPC: new master 3 0 0 0 4 0 0 0 0 0 1719027518 1893977003 0 Other 0 1 ASM File Group Sync 2 0 0 0.14 2825 1 0 0 0.1 974 769429583 1740759767 8 User I/O 0 1 ASM IO for non-blocking poll 1049708 0 267 0 2665678 521056 0 124 0 1244750 3288045901 1740759767 8 User I/O 0 1 ASM background starting 157 0 187 1.19 1869749 0 0 0 0 0 2662459136 1893977003 0 Other 0 1 ASM background timer 313575 0 60233910 192.09 602339095475 0 0 0 0 0 2042354203 2723168908 6 Idle 0 1 ASM cluster membership changes 344140 278431 60234936 175.03 602349360024 0 0 0 0 0 2317972841 2723168908 6 Idle 0 1 ASM file metadata operation 336057 0 12988 0.04 129877116 3790 0 25 0.01 246550 3967390525 1893977003 0 Other 0 1 ASM: MARK subscribe to msg channel 1 0 0 0.33 3328 0 0 0 0 0 2388386205 1893977003 0 Other 0 1 ASMB cookie valid check 1 0 305 305.32 3053186 0 0 0 0 0 3861676729 1893977003 0 Other 1 AWR Flush 402 0 0 0 561 4 0 0 0 9 2288329625 1893977003 0 Other 0 1 CGS skgxn join retry 1 0 5 5.18 51823 0 0 0 0 0 2151285256 1893977003 0 Other 0 1 CGS wait for IPC msg 6048085 6048085 3372 0 33718414 0 0 0 0 0 1736567536 1893977003 0 Other 0 1 CRS call completion 11 0 48 4.34 476891 5 0 16 3.15 157549 3329884903 1893977003 0 Other 0 1 CSS Xgrp shared operation 3 0 0 0.02 519 0 0 0 0 0 1812662268 1893977003 0 Other 0 1 CSS group membership query 56 0 1 0.02 12705 41 0 1 0.02 6232 321492953 1893977003 0 Other 0 1 CSS group registration 18 0 7 0.38 68207 0 0 0 0 0 3315172079 1893977003 0 Other 0 1 CSS initialization 707 0 194 0.27 1941177 94 0 23 0.25 234380 366154626 1893977003 0 Other 0 1 CSS operation: action 1260 0 55 0.04 548415 98 0 8 0.09 83852 2051030365 1893977003 0 Other 0 1 CSS operation: data query 18649 0 1067 0.06 10672114 82 0 1 0.02 12604 4255804750 1893977003 0 Other 0 1 CSS operation: data update 20249 0 827 0.04 8273003 0 0 0 0 0 3392052408 1893977003 0 Other 0 1 CSS operation: query 1938 0 19 0.01 188108 212 0 1 0.01 13779 1558934147 1893977003 0 Other 0 1 Compression analysis 1924 0 6 0 56617 24 0 0 0 1144 2929689230 1893977003 0 Other 0 1 DFS lock handle 20736 20108 463 0.02 4625345 15 8 2 0.13 20063 3595075359 1893977003 0 Other 0 1 DIAG idle wait 7882816 6187104 120297699 15.26 1202976989473 0 0 0 0 0 3176176482 2723168908 6 Idle 0 1 DLM cross inst call completion 221534 0 3291 0.01 32907731 3528 0 904 0.26 9043346 2765459430 1893977003 0 Other 0 1 Data Guard Broker Wait 32 0 0 0 24 31 0 0 0 20 4100251918 1893977003 0 Other 0 1 Data Guard: Gap Manager 9970 0 60225502 6040.67 602255023760 0 0 0 0 0 2369083111 2723168908 6 Idle 0 1 Data Guard: Timer 19942 0 60234224 3020.47 602342238487 0 0 0 0 0 1799268421 2723168908 6 Idle 0 1 Data Pump slave startup 1 0 0 0.01 69 0 0 0 0 0 1618891965 1893977003 0 Other 0 1 Data file init write 256 0 161 0.63 1611769 0 0 0 0 0 2326919048 1740759767 8 User I/O 0 1 Datapump dump file I/O 1144 0 153 0.13 1529793 0 0 0 0 0 4155572307 1740759767 8 User I/O 0 1 Disk file Mirror Read 821154 0 56603 0.07 566032348 13211 0 653 0.05 6529522 13102552 1740759767 8 User I/O 0 1 Disk file operations I/O 41918 0 594 0.01 5937083 17662 0 396 0.02 3961679 166678035 1740759767 8 User I/O 0 1 Failed Logon Delay 5 5 500 100.01 5000737 5 5 500 100.01 5000737 387973045 1893977003 0 Other 0 1 GCR CSS group join 2 0 0 0.01 205 0 0 0 0 0 1308860334 1893977003 0 Other 0 1 GCR CSS group query 18536 0 8 0 84295 0 0 0 0 0 2771226010 1893977003 0 Other 0 1 GCR CSS group update 18529 0 5 0 54399 0 0 0 0 0 2548959420 1893977003 0 Other 0 1 GCR sleep 3961746 0 120417937 30.4 1204179368277 0 0 0 0 0 341609523 2723168908 6 Idle 0 1 GPnP Get Item 10 0 56 5.61 561021 0 0 0 0 0 2801929040 1893977003 0 Other 0 1 IMR mount phase II completion 1 0 30 30.03 300293 0 0 0 0 0 4139178294 1893977003 0 Other 0 1 IMR slave acknowledgement msg 1806686 0 58758 0.03 587579300 0 0 0 0 0 269767187 1893977003 0 Other 0 1 IMR slave process init 2 0 1 0.56 11150 0 0 0 0 0 2448812170 1893977003 0 Other 0 1 IPC group service call 517181 0 1499 0 14986907 2398 0 1 0 11349 2966135248 2000153315 7 Network 0 1 IPC send completion sync 627887 601816 731 0 7308975 23225 0 273 0.01 2726670 2795431108 1893977003 0 Other 0 1 JS kgl get object wait 46 0 523 11.38 5234448 11 0 125 11.41 1254887 1955925289 4166625743 3 Administrative 0 1 KJC: Wait for msg sends to complete 128697 9 633 0 6331213 274 0 4 0.01 39965 4079369678 1893977003 0 Other 0 1 KSV master wait 464161 450506 2063 0 20633214 7560 0 700 0.09 7002186 1510356050 1893977003 0 Other 0 1 LGWR all worker groups 2192 0 120 0.05 1198324 0 0 0 0 0 1249397642 1893977003 0 Other 0 1 LGWR any worker group 9679 0 329 0.03 3286108 0 0 0 0 0 1793502753 1893977003 0 Other 0 1 LGWR wait for redo copy 3971 0 12 0 121684 0 0 0 0 0 4266849434 1893977003 0 Other 0 1 LGWR worker group idle 82112 0 120451235 1466.91 1204512350185 0 0 0 0 0 91842589 2723168908 6 Idle 0 1 LGWR worker group ordering 5396 0 161 0.03 1611548 0 0 0 0 0 3116432628 1893977003 0 Other 0 1 Log archive I/O 19 0 18 0.95 180681 0 0 0 0 0 2089793129 4108307767 9 System I/O 0 1 OFS idle 200254 200250 60235323 300.79 602353227363 0 0 0 0 0 1048659203 2723168908 6 Idle 0 1 OFS operation completion 1 0 0 0 48 1 0 0 0 48 303766224 4166625743 3 Administrative 0 1 PGA memory operation 370763 0 520 0 5198226 244518 0 317 0 3165865 1800992443 1893977003 0 Other 0 1 PING 435118 114042 60234032 138.43 602340315888 0 0 0 0 0 900394413 2723168908 6 Idle 0 1 PL/SQL lock timer 978 978 133581 136.59 1335812209 15 15 150 10.02 1502557 818280116 2723168908 6 Idle 0 1 PX Deq Credit: need buffer 24 0 0 0.01 2767 1 0 0 0.01 136 2267953574 2723168908 6 Idle 0 1 PX Deq Credit: send blkd 18100 0 317 0.02 3170491 14308 0 151 0.01 1514937 2610814049 2723168908 6 Idle 0 1 PX Deq: Execute Reply 29955 0 969 0.03 9690812 25231 0 353 0.01 3526878 2599037852 2723168908 6 Idle 0 1 PX Deq: Execution Msg 53911 0 26012 0.48 260121267 53911 0 26012 0.48 260121267 98582416 2723168908 6 Idle 0 1 PX Deq: Join ACK 29848 0 1758 0.06 17580418 25170 0 1102 0.04 11019756 4205438796 1893977003 0 Other 0 1 PX Deq: Msg Fragment 17866 0 54 0 535884 17866 0 54 0 535884 77145095 2723168908 6 Idle 0 1 PX Deq: Parse Reply 29848 0 3834 0.13 38343485 25170 0 1723 0.07 17227020 4255662421 2723168908 6 Idle 0 1 PX Deq: Signal ACK EXT 29834 0 64 0 636707 25156 0 39 0 393519 1974015973 1893977003 0 Other 0 1 PX Deq: Slave Session Stats 47796 0 486 0.01 4855928 43118 0 458 0.01 4575803 2306891574 1893977003 0 Other 0 1 PX Deq: reap credit 10775572 10775527 6974 0 69737439 351199 351199 102 0 1020392 146029171 1893977003 0 Other 0 1 PX Idle Wait 18724 0 1016139857 54269.38 10161398570061 0 0 0 0 0 3382828453 2723168908 6 Idle 0 1 PX qref latch 341 341 0 0 535 333 333 0 0 501 175758063 1893977003 0 Other 0 1 Parameter File I/O 377 0 14 0.04 141019 166 0 6 0.04 59394 1179235204 1740759767 8 User I/O 0 1 REPL Capture/Apply: RAC AQ qmn coordinator 10045 10040 60289256 6001.92 602892558998 0 0 0 0 0 3846605265 2723168908 6 Idle 0 1 REPL Capture/Apply: RAC inter-instance ack 1 0 0 0.03 252 0 0 0 0 0 1864425273 1893977003 0 Other 0 1 RMA: IPC0 completion sync 710210 0 461 0 4607194 0 0 0 0 0 739096548 2723168908 6 Idle 0 1 Redo Transport Open 10 0 0 0 5 0 0 0 0 0 3470397773 1893977003 0 Other 0 1 SCM slave idle 5792089 0 60229479 10.4 602294786853 0 0 0 0 0 338940857 2723168908 6 Idle 0 1 SGA: MMAN sleep for component shrink 8 8 9 1.07 85550 0 0 0 0 0 1989349184 2723168908 6 Idle 0 1 SGA: allocation forcing component growth 4 0 66 16.42 656955 2 0 33 16.43 328670 3493282803 1893977003 0 Other 0 1 SQL*Net break/reset to client 156 0 3 0.02 27553 142 0 3 0.02 25304 1963888671 4217450380 1 Application 0 1 SQL*Net message from client 147471 0 279166326 1893.03 2791663258483 141260 0 279127435 1975.98 2791274353629 1421975091 2723168908 6 Idle 0 1 SQL*Net message to client 145921 0 22 0 222259 141272 0 22 0 220813 2067390145 2000153315 7 Network 0 1 SQL*Net more data from client 242 0 1 0 8510 242 0 1 0 8510 3530226808 2000153315 7 Network 0 1 SQL*Net more data to client 541 0 4 0.01 42399 541 0 4 0.01 42399 554161347 2000153315 7 Network 0 1 Space Manager: slave idle wait 2490108 0 959554432 385.35 9595544320702 0 0 0 0 0 2942611488 2723168908 6 Idle 0 1 Streams AQ: load balancer idle 14 14 51457 3675.54 514574999 0 0 0 0 0 1646312927 2723168908 6 Idle 0 1 Streams AQ: qmn coordinator idle wait 45008 0 60232142 1338.25 602321421450 0 0 0 0 0 989870553 2723168908 6 Idle 0 1 Streams AQ: qmn slave idle wait 37649 98 61042206 1621.35 610422060915 0 0 0 0 0 1830121438 2723168908 6 Idle 0 1 Streams AQ: waiting for time management or cleanup tasks 3 1 60233423 20077807.83 602334234987 0 0 0 0 0 3702640206 2723168908 6 Idle 0 1 Sync ASM discovery 2 0 25 12.6 251934 0 0 0 0 0 1532208117 1893977003 0 Other 0 1 Sync ASM rebalance 22464 0 5918 0.26 59176207 20 0 2 0.12 24704 1759761944 1893977003 0 Other 0 1 VKRM Idle 1 0 60235010 60235010.07 602350100683 0 0 0 0 0 58311103 2723168908 6 Idle 0 1 XDB SGA initialization 1 0 2 1.66 16618 0 0 0 0 0 1110641933 1893977003 0 Other 0 1 asynch descriptor resize 3079 3079 1 0 5946 1047 1047 0 0 1400 2505166323 1893977003 0 Other 0 1 buffer busy waits 1002 0 439 0.44 4386167 11 0 112 10.18 1119518 2161531084 3875070507 4 Concurrency 0 1 buffer deadlock 2 2 0 0 15 0 0 0 0 0 218992928 1893977003 0 Other 0 1 class slave wait 6773384 5508110 331809643 48.99 3318096431030 0 0 0 0 0 1055154682 2723168908 6 Idle 0 1 control file heartbeat 1 1 404 403.9 4039030 1 1 404 403.9 4039030 40893507 1893977003 0 Other 0 1 control file parallel write 208435 0 16146 0.08 161455160 3731 0 118 0.03 1179300 4078387448 4108307767 9 System I/O 0 1 control file sequential read 2435078 0 55134 0.02 551339509 64832 0 1418 0.02 14181152 3213517201 4108307767 9 System I/O 0 1 control file single write 1 0 0 0.04 398 1 0 0 0.04 398 2383414886 4108307767 9 System I/O 0 1 cursor: mutex S 2 0 0 0.11 2154 0 0 0 0 0 1575214430 3875070507 4 Concurrency 0 1 cursor: mutex X 5 0 0 0 13 4 0 0 0 11 877525844 3875070507 4 Concurrency 0 1 cursor: pin S 118 0 16 0.14 160803 24 0 3 0.11 26592 352301881 3875070507 4 Concurrency 0 1 cursor: pin S wait on X 188 0 75 0.4 750004 81 0 35 0.44 354956 1729366244 3875070507 4 Concurrency 0 1 datafile move cleanup during resize 1358 0 1 0 9329 1232 0 0 0 612 930916630 1893977003 0 Other 0 1 db file async I/O submit 124993 0 5 0 53065 0 0 0 0 0 133155944 4108307767 9 System I/O 0 1 db file parallel read 328 0 19 0.06 192847 65 0 4 0.06 42235 834992820 1740759767 8 User I/O 0 1 db file parallel write 127666 0 3882 0.03 38820798 0 0 0 0 0 1620694733 4108307767 9 System I/O 0 1 db file scattered read 8779 0 657 0.07 6570133 718 0 66 0.09 664893 506183215 1740759767 8 User I/O 0 1 db file sequential read 66309 0 2540 0.04 25403263 12152 0 497 0.04 4965435 2652584166 1740759767 8 User I/O 0 1 db file single write 1476 0 38 0.03 377030 1232 0 29 0.02 289290 1307477558 1740759767 8 User I/O 0 1 direct path read 23521 0 610 0.03 6099633 717 0 25 0.03 250414 3926164927 1740759767 8 User I/O 0 1 direct path read temp 209080 0 5451 0.03 54509564 99357 0 2384 0.02 23841585 861319509 1740759767 8 User I/O 0 1 direct path sync 2781 0 7 0 70101 0 0 0 0 0 2093619153 1740759767 8 User I/O 0 1 direct path write 498 0 51 0.1 513766 3 0 0 0.09 2669 885859547 1740759767 8 User I/O 0 1 direct path write temp 165971 0 6567 0.04 65674714 87274 0 3354 0.04 33537305 38438084 1740759767 8 User I/O 0 1 dispatcher timer 10038 10038 60233166 6000.51 602331659106 0 0 0 0 0 4090013609 2723168908 6 Idle 0 1 enq: AE - lock 11 1 1 0.08 8342 2 1 0 0.1 2050 3963940642 1893977003 0 Other 0 1 enq: AF - task serialization 141 0 7 0.05 69526 6 0 0 0.05 3017 955173113 1893977003 0 Other 1 enq: AP - contention 2 0 0 0.08 1503 0 0 0 0 0 197381757 1893977003 0 Other 0 1 enq: AS - service activation 5 0 0 0.05 2494 3 0 0 0.05 1474 2558928434 1893977003 0 Other 0 1 enq: CB - role operation 1 0 0 0.13 1308 0 0 0 0 0 3015879322 3875070507 4 Concurrency 0 1 enq: CF - contention 15135 12424 873 0.06 8734367 1260 0 48 0.04 479995 1494394835 1893977003 0 Other 0 1 enq: CN - race with init 1 0 0 0.02 232 1 0 0 0.02 232 613420004 1893977003 0 Other 0 1 enq: CO - master slave det 10010 10010 1008 0.1 10076178 0 0 0 0 0 606473003 1893977003 0 Other 0 1 enq: CR - block range reuse ckpt 2422 0 57 0.02 569257 6 0 0 0.03 1717 2576559565 1893977003 0 Other 0 1 enq: CU - contention 1 0 0 0.23 2316 1 0 0 0.23 2316 325782660 1893977003 0 Other 0 1 enq: DL - contention 20 20 1 0.04 7748 15 15 1 0.04 6136 1349347951 1893977003 0 Other 0 1 enq: DM - contention 4 1 0 0.07 2640 4 1 0 0.07 2640 3294817508 1893977003 0 Other 0 1 enq: DR - contention 333 0 23 0.07 225795 0 0 0 0 0 721778600 1893977003 0 Other 0 1 enq: DW - contention 219 218 22 0.1 224100 0 0 0 0 0 2910919470 1893977003 0 Other 0 1 enq: FB - contention 3135 0 100 0.03 998920 46 0 1 0.02 10428 1238611814 1893977003 0 Other 0 1 enq: FH - contention 31 0 2 0.07 20639 0 0 0 0 0 3728789504 1893977003 0 Other 0 1 enq: FS - contention 1 0 0 0.06 622 1 0 0 0.06 622 1309055815 1893977003 0 Other 0 1 enq: FU - contention 27 0 3 0.13 34161 0 0 0 0 0 2941830639 1893977003 0 Other 0 1 enq: HW - contention 1598 7 63 0.04 632357 117 0 7 0.06 68208 1645217925 3290255840 2 Configuration 0 1 enq: IA - contention 1 0 0 0.03 258 0 0 0 0 0 2673474179 1893977003 0 Other 0 1 enq: JG - queue lock 25 0 31 1.24 309162 0 0 0 0 0 1946447820 1893977003 0 Other 0 1 enq: JS - job run lock - synchronize 665 665 40 0.06 400915 0 0 0 0 0 6961751 1893977003 0 Other 0 1 enq: KD - determine DBRM master 2 2 0 0.03 585 0 0 0 0 0 2775294757 1893977003 0 Other 0 1 enq: KI - contention 6 0 0 0.06 3601 5 0 0 0.07 3305 1656318846 1893977003 0 Other 0 1 enq: KM - contention 12 0 1 0.05 6355 1 0 0 0.05 463 1504129838 1893977003 0 Other 0 1 enq: KR - Add rule to ruleset 4 0 0 0.06 2549 2 0 0 0.08 1527 3809944109 1893977003 0 Other 0 1 enq: KR - Drop rule force 3 0 0 0.05 1372 2 0 0 0.04 807 8998880 1893977003 0 Other 0 1 enq: KR - Remove rule from ruleset 3 0 0 0.04 1325 2 0 0 0.03 672 1527114466 1893977003 0 Other 0 1 enq: KT - contention 6 0 1 0.11 6442 0 0 0 0 0 1301289702 1893977003 0 Other 0 1 enq: MM - MMON Autotask scheduling 3 0 0 0.08 2336 0 0 0 0 0 4246084819 1893977003 0 Other 0 1 enq: MR - contention 1 1 0 0.06 624 0 0 0 0 0 3113891348 1893977003 0 Other 0 1 enq: MR - datafile online 1 0 0 0.02 207 0 0 0 0 0 2431157470 1893977003 0 Other 0 1 enq: MW - contention 335 0 19 0.06 192672 0 0 0 0 0 3384888932 1893977003 0 Other 0 1 enq: OD - Serializing DDLs 15 15 1 0.04 5722 0 0 0 0 0 143199788 1893977003 0 Other 0 1 enq: PD - contention 19 0 1 0.04 8151 0 0 0 0 0 245034220 1893977003 0 Other 0 1 enq: PE - contention 4 0 0 0.05 2198 3 0 0 0.05 1501 4129138703 1893977003 0 Other 0 1 enq: PG - contention 2 0 0 0.02 388 2 0 0 0.02 388 925144608 1893977003 0 Other 0 1 enq: PP - contention 1 1 0 0.09 881 0 0 0 0 0 2373399366 1893977003 0 Other 0 1 enq: PR - contention 15 0 29 1.96 294119 5 0 10 2.01 100580 3733307663 1893977003 0 Other 0 1 enq: PS - contention 21298 2324 834 0.04 8342940 16784 651 602 0.04 6017811 2071012023 1893977003 0 Other 0 1 enq: PV - syncstart 7 0 7 0.99 69557 0 0 0 0 0 3063016909 1893977003 0 Other 0 1 enq: PW - perwarm status in dbw0 1 1 0 0.05 512 0 0 0 0 0 1340848367 1893977003 0 Other 0 1 enq: RC - Result Cache: Contention 4 1 0 0.03 1336 0 0 0 0 0 2649722911 4217450380 1 Application 0 1 enq: RD - RAC load 2 1 0 0.04 749 0 0 0 0 0 3612504684 1893977003 0 Other 0 1 enq: RF - DG Broker Current File ID 2 0 0 0.03 637 0 0 0 0 0 2735020950 1893977003 0 Other 0 1 enq: RF - synch: DG Broker metadata 1 0 0 0.06 577 0 0 0 0 0 2609577957 1893977003 0 Other 0 1 enq: RH - quarantine 1 0 0 0.1 955 0 0 0 0 0 3350442546 1893977003 0 Other 0 1 enq: RL - RAC wallet lock 2 0 0 0.06 1211 0 0 0 0 0 2687533677 1893977003 0 Other 0 1 enq: RO - fast object reuse 3560 0 100 0.03 1000845 233 0 7 0.03 73222 143262751 4217450380 1 Application 0 1 enq: RP - contention 3 0 0 0.1 3011 0 0 0 0 0 647852263 1893977003 0 Other 0 1 enq: RR - contention 1 0 0 0.07 749 0 0 0 0 0 2152050500 1893977003 0 Other 0 1 enq: RT - contention 1 1 0 0.08 804 1 1 0 0.08 804 1143317824 1893977003 0 Other 0 1 enq: SM - contention 3035 0 126 0.04 1260644 0 0 0 0 0 1335721068 1893977003 0 Other 0 1 enq: SQ - contention 12 0 4 0.37 44503 11 0 4 0.4 44418 2322460838 3290255840 2 Configuration 0 1 enq: SR - contention 2 2 0 0.13 2606 0 0 0 0 0 3056899600 1893977003 0 Other 0 1 enq: SV - contention 1 0 0 0.02 158 0 0 0 0 0 818518977 3290255840 2 Configuration 0 1 enq: SW - contention 29 0 1 0.04 11031 2 0 0 0.04 770 608373385 1893977003 0 Other 0 1 enq: TA - contention 1986 0 50 0.03 500043 0 0 0 0 0 2605232529 1893977003 0 Other 0 1 enq: TB - SQL Tuning Base Cache Load 1 0 0 0.07 659 1 0 0 0.07 659 2878002362 1893977003 0 Other 0 1 enq: TB - SQL Tuning Base Cache Update 3 0 0 0.04 1298 0 0 0 0 0 3315745697 1893977003 0 Other 0 1 enq: TD - KTF dump entries 1005 0 20 0.02 204539 0 0 0 0 0 1752153790 1893977003 0 Other 0 1 enq: TK - Auto Task Serialization 346 346 33 0.1 333937 0 0 0 0 0 917975477 1893977003 0 Other 0 1 enq: TK - Auto Task Slave Lockout 19 19 2 0.08 15329 0 0 0 0 0 1739764420 1893977003 0 Other 0 1 enq: TM - contention 6170 20 135 0.02 1349013 49 6 2 0.04 20483 668627480 4217450380 1 Application 0 1 enq: TO - contention 731 141 28 0.04 277646 229 0 8 0.03 75818 3701540314 1893977003 0 Other 0 1 enq: TQ - DDL contention 3 0 71 23.56 706812 1 0 22 21.98 219822 717240345 1893977003 0 Other 0 1 enq: TQ - TM contention 1 0 22 22.49 224890 0 0 0 0 0 1371132976 1893977003 0 Other 0 1 enq: TS - contention 1714 1588 44 0.03 437459 53 0 1 0.03 13685 2667616873 1893977003 0 Other 0 1 enq: TT - contention 107 26 4 0.04 38289 5 0 0 0.04 2159 4205956891 1893977003 0 Other 0 1 enq: TX - allocate ITL entry 3 0 0 0.08 2286 0 0 0 0 0 281768874 3290255840 2 Configuration 0 1 enq: TX - contention 188 179 85 0.45 854115 11 11 0 0.02 2506 1629782133 1893977003 0 Other 0 1 enq: TX - index contention 38 0 1 0.03 11410 0 0 0 0 0 1035026728 3875070507 4 Concurrency 0 1 enq: UL - contention 21 3 2937 139.88 29374597 14 2 2927 209.09 29272608 738183602 4217450380 1 Application 0 1 enq: US - contention 282 0 8 0.03 75651 6 0 0 0.05 2712 2458904239 1893977003 0 Other 0 1 enq: WF - contention 4172 1 349 0.08 3491830 0 0 0 0 0 1641438405 1893977003 0 Other 0 1 enq: WL - contention 5 5 0 0.09 4385 0 0 0 0 0 1555037586 1893977003 0 Other 0 1 enq: WM - WLM Plan activation 1 0 0 0.06 562 1 0 0 0.06 562 2390244800 1893977003 0 Other 0 1 enq: WP - contention 1 0 0 0.06 621 0 0 0 0 0 2389341073 1893977003 0 Other 0 1 enq: WT - contention 267 0 10 0.04 104229 1 0 0 0.06 645 1872744606 1893977003 0 Other 0 1 enq: XR - quiesce database 1 0 0 0.06 578 1 0 0 0.06 578 707813539 1893977003 0 Other 0 1 enq: ZH - compression analysis 962 962 17 0.02 168324 12 12 0 0.02 2889 2630448508 1893977003 0 Other 0 1 external table open 6 0 2 0.26 15898 0 0 0 0 0 332943844 1740759767 8 User I/O 0 1 external table read 2 0 338 168.94 3378786 0 0 0 0 0 1850448852 1740759767 8 User I/O 0 1 external table write 44 0 0 0 20 0 0 0 0 0 2573156426 1740759767 8 User I/O 0 1 gc buffer busy acquire 1389 0 149 0.11 1491780 35 0 5 0.15 51006 1912606394 3871361733 11 Cluster 0 1 gc buffer busy release 484 0 143 0.3 1433959 0 0 0 0 0 105117041 3871361733 11 Cluster 0 1 gc cr block 2-way 48053 0 1238 0.03 12378268 4680 0 123 0.03 1234124 737661873 3871361733 11 Cluster 0 1 gc cr block busy 3784 0 377 0.1 3768155 112 0 10 0.09 98742 1520064534 3871361733 11 Cluster 0 1 gc cr disk read 1536 0 28 0.02 283031 0 0 0 0 0 1445598276 3871361733 11 Cluster 0 1 gc cr grant 2-way 25509 0 430 0.02 4303145 4849 0 95 0.02 950788 3201690383 3871361733 11 Cluster 0 1 gc cr grant busy 98 0 3 0.03 28007 0 0 0 0 0 3261528052 3871361733 11 Cluster 0 1 gc cr multi block grant 5205 0 169 0.03 1692765 602 0 21 0.04 212163 2774151265 3871361733 11 Cluster 0 1 gc cr multi block mixed 9651 0 984 0.1 9842105 974 0 69 0.07 693044 2272899030 3871361733 11 Cluster 0 1 gc current block 2-way 39893 0 822 0.02 8220867 8053 0 178 0.02 1780839 111015833 3871361733 11 Cluster 0 1 gc current block busy 56380 0 1817 0.03 18171380 242 0 5 0.02 54348 2701629120 3871361733 11 Cluster 0 1 gc current block congested 5 0 1 0.21 10584 0 0 0 0 0 3785617759 3871361733 11 Cluster 0 1 gc current grant 2-way 10754 0 194 0.02 1942671 701 0 14 0.02 141681 2685450749 3871361733 11 Cluster 0 1 gc current grant busy 32199 0 572 0.02 5724151 737 0 18 0.02 182032 2277737081 3871361733 11 Cluster 0 1 gc current multi block request 2565 0 97 0.04 972057 9 0 0 0.04 3361 3897775868 3871361733 11 Cluster 0 1 gc current retry 27 0 0 0.02 4412 0 0 0 0 0 1742950045 3871361733 11 Cluster 0 1 gc current split 97 0 3 0.03 28468 0 0 0 0 0 1457266432 3871361733 11 Cluster 0 1 gc index operation 98 0 14 0.14 137396 0 0 0 0 0 4185841437 3871361733 11 Cluster 0 1 gc quiesce 12 0 1233 102.79 12334356 0 0 0 0 0 758475196 3871361733 11 Cluster 0 1 gcs ddet enter server mode 2 0 193 96.73 1934601 0 0 0 0 0 1895771704 1893977003 0 Other 0 1 gcs log flush sync 12153 0 997 0.08 9972141 0 0 0 0 0 3480025058 1893977003 0 Other 0 1 gcs remote message 40997649 4 120433241 2.94 1204332405652 0 0 0 0 0 2910564005 2723168908 6 Idle 0 1 gcs resource directory to be unfrozen 9 0 25 2.79 250989 0 0 0 0 0 2871858069 1893977003 0 Other 0 1 gcs to be enabled 2 0 0 0 0 0 0 0 0 0 1965140390 1893977003 0 Other 0 1 gcs yield cpu 38738817 38737107 120437337 3.11 1204373366945 0 0 0 0 0 332747553 2723168908 6 Idle 0 1 ges LMD to inherit communication channels 1 0 0 0.2 1950 0 0 0 0 0 2542045584 1893977003 0 Other 0 1 ges LMON to get to FTDONE 2 0 1 0.27 5316 0 0 0 0 0 1312215341 1893977003 0 Other 0 1 ges cgs registration 2 0 79 39.57 791487 1 0 79 79.15 791486 2656205476 1893977003 0 Other 0 1 ges enter server mode 19 0 71 3.74 710246 0 0 0 0 0 2120155596 1893977003 0 Other 0 1 ges global resource directory to be frozen 4 2 4 1.05 41977 0 0 0 0 0 2627274657 1893977003 0 Other 0 1 ges inquiry response 2762 0 81 0.03 814813 1241 0 39 0.03 390002 4086135771 1893977003 0 Other 0 1 ges ipc enter server mode 2 0 0 0 0 0 0 0 0 0 4261098405 1893977003 0 Other 0 1 ges lmd and pmon to attach 1 0 0 0.01 124 0 0 0 0 0 2595201782 1893977003 0 Other 0 1 ges lmd sync during reconfig 5 0 1 0.14 6804 0 0 0 0 0 1612310333 1893977003 0 Other 0 1 ges lms sync during dynamic remastering and reconfig 7 0 1 0.18 12559 0 0 0 0 0 1631772444 1893977003 0 Other 0 1 ges remote message 17016675 1 120447116 7.08 1204471155686 0 0 0 0 0 317011907 2723168908 6 Idle 0 1 ges resource directory to be unfrozen 5398 0 0 0 2339 2293 0 0 0 1150 1761249154 1893977003 0 Other 0 1 get branch/thread/sequence enqueue 10 0 1 0.14 14061 0 0 0 0 0 1383326310 1893977003 0 Other 0 1 global enqueue expand wait 1 0 0 0.16 1551 0 0 0 0 0 3498297576 1893977003 0 Other 0 1 heartbeat redo informer 602276 0 60233833 100.01 602338333499 0 0 0 0 0 4233989021 2723168908 6 Idle 0 1 index (re)build lock or pin object 308 0 1 0 6068 192 0 0 0 2810 3347698104 4166625743 3 Administrative 0 1 instance state change 2 0 0 0.01 119 2 0 0 0.01 119 1525267497 1893977003 0 Other 0 1 jobq slave wait 56615 55764 2831989 50.02 28319886151 0 0 0 0 0 782339817 2723168908 6 Idle 0 1 kfk: async disk IO 2748 0 82 0.03 823526 2464 0 70 0.03 699170 1568594048 4108307767 9 System I/O 0 1 kjctssqmg: quick message send wait 24 24 0 0.01 1629 0 0 0 0 0 3613653811 1893977003 0 Other 0 1 kksfbc child completion 9 8 41 4.53 407284 5 4 20 4.07 203432 2679325 1893977003 0 Other 0 1 ksxr poll remote instances 792494 792494 283 0 2831846 0 0 0 0 0 3586266983 1893977003 0 Other 0 1 ksxr wait for mount shared 1 0 0 0 0 0 0 0 0 0 3974056937 1893977003 0 Other 0 1 kupp process wait 1 0 41 41.48 414829 1 0 41 41.48 414829 3632212035 1893977003 0 Other 0 1 latch free 535570 0 6655 0.01 66550823 44 0 1 0.02 8224 3474287957 1893977003 0 Other 0 1 latch: MGA heap latch 9 0 0 0.04 3214 0 0 0 0 0 500901621 3875070507 4 Concurrency 0 1 latch: MGA shared context latch 67 0 0 0.01 4508 2 0 0 0 71 2598281784 3875070507 4 Concurrency 0 1 latch: MGA shared context root latch 42 0 1 0.01 5254 0 0 0 0 0 1233153647 3875070507 4 Concurrency 0 1 latch: active service list 6 0 1 0.13 7961 3 0 0 0 129 2767884586 1893977003 0 Other 0 1 latch: cache buffers chains 471 0 20 0.04 196604 14 0 3 0.23 32697 2779959231 3875070507 4 Concurrency 0 1 latch: cache buffers lru chain 16 0 0 0.02 3751 0 0 0 0 0 3401628503 1893977003 0 Other 0 1 latch: call allocation 5 0 0 0.01 607 4 0 0 0 135 139039345 1893977003 0 Other 0 1 latch: checkpoint queue latch 4 0 0 0 180 0 0 0 0 0 22066106 1893977003 0 Other 0 1 latch: enqueue hash chains 12 0 0 0.04 4527 0 0 0 0 0 1847483002 1893977003 0 Other 0 1 latch: gc element 45 0 1 0.02 8664 0 0 0 0 0 1925256273 1893977003 0 Other 0 1 latch: gcs resource hash 56 0 1 0.01 6711 0 0 0 0 0 142175773 1893977003 0 Other 0 1 latch: ges resource hash list 214 0 8 0.04 78805 55 0 1 0.03 14227 665933121 1893977003 0 Other 0 1 latch: kjci process context latch 144 0 1 0.01 7423 0 0 0 0 0 3664355268 1893977003 0 Other 0 1 latch: lob segment hash table latch 1 0 0 0.18 1796 0 0 0 0 0 310348786 1893977003 0 Other 0 1 latch: messages 651 0 19 0.03 192122 0 0 0 0 0 1973577887 1893977003 0 Other 0 1 latch: object queue header operation 4 0 0 0.04 1615 0 0 0 0 0 295718413 1893977003 0 Other 0 1 latch: parallel query alloc buffer 2 0 0 0.07 1445 0 0 0 0 0 676951513 1893977003 0 Other 0 1 latch: redo allocation 41 0 2 0.04 17161 0 0 0 0 0 266850936 1893977003 0 Other 0 1 latch: redo writing 4 0 0 0.03 1032 0 0 0 0 0 2539661515 3290255840 2 Configuration 0 1 latch: session allocation 1 0 0 0.02 156 1 0 0 0.02 156 2900750527 1893977003 0 Other 0 1 latch: shared pool 1743 0 39 0.02 388300 661 0 14 0.02 137431 2696347763 3875070507 4 Concurrency 0 1 latch: undo global data 8 0 0 0.03 2042 0 0 0 0 0 2221529869 1893977003 0 Other 0 1 libcache interrupt action by LCK 842141 0 27 0 269466 0 0 0 0 0 670556006 3875070507 4 Concurrency 0 1 library cache load lock 294 0 89 0.3 893822 210 0 54 0.26 542582 2952162927 3875070507 4 Concurrency 0 1 library cache lock 27599 4046 15366 0.56 153664788 2648 6 310 0.12 3104940 916468430 3875070507 4 Concurrency 0 1 library cache pin 26978 2811 651 0.02 6505532 2186 0 61 0.03 606307 2802704141 3875070507 4 Concurrency 0 1 library cache: bucket mutex X 487 0 72 0.15 718975 58 0 0 0 386 2601513493 3875070507 4 Concurrency 0 1 library cache: mutex X 2532 0 161 0.06 1613857 469 0 44 0.09 440613 1646780882 3875070507 4 Concurrency 0 1 local write wait 5536 0 311 0.06 3112272 4928 0 262 0.05 2618915 1570123276 1740759767 8 User I/O 0 1 lock escalate retry 8 8 0 0 127 0 0 0 0 0 2545148957 1893977003 0 Other 0 1 log buffer space 5 0 1 0.15 7380 0 0 0 0 0 3357856061 3290255840 2 Configuration 0 1 log file parallel write 133396 0 9150 0.07 91504342 0 0 0 0 0 3999721902 4108307767 9 System I/O 0 1 log file sequential read 1228 0 280 0.23 2800847 4 0 0 0.04 1759 549236675 4108307767 9 System I/O 0 1 log file single write 48 0 2 0.03 15647 2 0 0 0.04 767 215477332 4108307767 9 System I/O 0 1 log file switch completion 5 0 4 0.76 37978 1 0 1 1.37 13678 3834950329 3290255840 2 Configuration 0 1 log file sync 514 0 541 1.05 5412642 358 0 37 0.1 370133 1328744198 3386400367 5 Commit 0 1 lreg timer 200640 200622 60234597 300.21 602345969049 0 0 0 0 0 3113908805 2723168908 6 Idle 0 1 name-service call wait 2248 0 631 0.28 6311823 1482 0 59 0.04 585238 3954828229 1893977003 0 Other 0 1 oracle thread bootstrap 4184 0 7554 1.81 75536365 59 0 229 3.89 2292721 4097944222 1893977003 0 Other 0 1 oradebug request completion 1 0 0 0.13 1293 0 0 0 0 0 367999153 1893977003 0 Other 0 1 os thread creation 4188 0 669 0.16 6687935 9 0 1 0.11 10077 2423447952 1893977003 0 Other 0 1 pman timer 200251 200250 60231365 300.78 602313650775 0 0 0 0 0 635602798 2723168908 6 Idle 0 1 pmon timer 400492 400323 120417540 300.67 1204175403709 0 0 0 0 0 3539483025 2723168908 6 Idle 0 1 rdbms ipc message 14001664 12356401 1684577764 120.31 16845777640444 0 0 0 0 0 866018717 2723168908 6 Idle 0 1 rdbms ipc reply 105 0 289 2.75 2891441 85 0 287 3.37 2868213 2587381521 1893977003 0 Other 0 1 read by other session 55 0 2 0.03 15790 2 0 0 0.06 1222 3056446529 1740759767 8 User I/O 0 1 reliable message 20062 0 3025 0.15 30246993 12776 0 446 0.03 4456630 906644781 1893977003 0 Other 0 1 resmgr:internal state change 5 5 51 10.29 514636 0 0 0 0 0 4043670897 3875070507 4 Concurrency 0 1 resmgr:plan change 1 0 1 0.74 7438 1 0 1 0.74 7438 3804628168 1893977003 0 Other 0 1 rolling migration: cluster quiesce 1 0 0 0 12 0 0 0 0 0 2107633232 1893977003 0 Other 0 1 row cache lock 141453 141339 4468 0.03 44676031 521 471 24 0.05 243563 1714089451 3875070507 4 Concurrency 0 1 row cache mutex 1685 0 159 0.09 1592334 209 0 13 0.06 133714 306610566 3875070507 4 Concurrency 0 1 service monitor: inst recovery completion 1 0 0 0.01 108 0 0 0 0 0 3615400802 3871361733 11 Cluster 0 1 shared server idle wait 2 0 60239260 30119630.18 602392603635 0 0 0 0 0 1786390478 2723168908 6 Idle 0 1 smon timer 2079 1966 60225276 28968.39 602252758143 0 0 0 0 0 1403232821 2723168908 6 Idle 0 1 transaction 19 0 0 0.01 2211 0 0 0 0 0 2153157043 1893977003 0 Other 0 1 undo segment extension 100 94 67 0.67 666350 0 0 0 0 0 1781586680 3290255840 2 Configuration 0 1 undo_retention publish retry 1993 0 1 0 6062 0 0 0 0 0 313295972 1893977003 0 Other 0 1 utl_file I/O 146 0 1 0 5745 0 0 0 0 0 2804400934 1740759767 8 User I/O 0 1 wait for unread message on broadcast channel 924886 920909 180711006 195.39 1807110059015 148 6 5477 37.01 54767505 218649935 2723168908 6 Idle 0 1 wait list latch free 23 0 3 0.13 29371 0 0 0 0 0 2530878290 1893977003 0 Other 0 1 watchdog main loop 800956 0 240859250 300.71 2408592497041 800956 0 240859250 300.71 2408592497041 2748706322 2723168908 6 Idle 0 2 ADR block file read 3471 0 43 0.01 433156 66 0 3 0.05 31427 1780066010 1893977003 0 Other 0 ...... 2 watchdog main loop 800989 0 240844116 300.68 2408441163490 800989 0 240844116 300.68 2408441163490 2748706322 2723168908 6 Idle 0 |
1.2 gv$sys_time_model：
这个视图注重CPU相关的消耗，下面的查询结果中加粗显示与CPU关系密切的统计项目：
select * FROM gv$sys_time_model order by 1,2; |
1 268357648 hard parse (bind mismatch) elapsed time 2936383 0 1 290749718 inbound PL/SQL rpc elapsed time 6902 0 1 294489758 cpu time 0 0 1 372226525 hard parse elapsed time 208402935 0 1 751169994 Java execution elapsed time 0 0 1 908589701 Tablespace encryption elapsed time 0 0 1 1159091985 repeated bind elapsed time 112237 0 1 1226623006 cpu time 0 0 1 1311180441 PL/SQL compilation elapsed time 7382435 0 1 1431595225 parse time elapsed 215674692 0 1 1824284809 failed parse elapsed time 2620667 0 1 1990024365 connection management call elapsed time 28293561 0 1 2264996169 background IM repopulation elapsed time 0 0 1 2266488992 OLAP engine elapsed time 0 0 1 2411117902 RMAN cpu time 0 0 1 2446918491 background IM prepopulation elapsed time 0 0 1 2451517896 background cpu time 24627971412 0 1 2643905994 PL/SQL execution elapsed time 42630213 0 1 2748282437 DB CPU 17033647805 0 1 2775283018 background IM population elapsed time 0 0 1 2799640666 cpu time 0 0 1 2821698184 sql execute elapsed time 17060456703 0 1 2966986086 background IM trickle repopulation elapsed time 0 0 1 3138706091 hard parse (sharing criteria) elapsed time 31451224 0 1 3649082374 DB time 17276914589 0 1 3780626361 background IM population cpu time 0 0 1 4092422623 background IM prepopulation cpu time 0 0 1 4125607023 failed parse (out of shared memory) elapsed time 0 0 1 4127043053 sequence load elapsed time 227484 0 1 4157170894 background elapsed time 25852077989 0 1 4273067414 OLAP engine CPU time 0 0 2 268357648 hard parse (bind mismatch) elapsed time 7275822 0 2 290749718 inbound PL/SQL rpc elapsed time 3733 0 2 294489758 cpu time 0 0 2 372226525 hard parse elapsed time 393394650 0 2 751169994 Java execution elapsed time 0 0 2 908589701 Tablespace encryption elapsed time 0 0 2 1159091985 repeated bind elapsed time 84515 0 2 1226623006 cpu time 0 0 2 1311180441 PL/SQL compilation elapsed time 5148328 0 2 1431595225 parse time elapsed 400188381 0 2 1824284809 failed parse elapsed time 252491278 0 2 1990024365 connection management call elapsed time 26631125 0 2 2264996169 background IM repopulation elapsed time 0 0 2 2266488992 OLAP engine elapsed time 0 0 2 2411117902 RMAN cpu time 0 0 2 2446918491 background IM prepopulation elapsed time 0 0 2 2451517896 background cpu time 16310120618 0 2 2643905994 PL/SQL execution elapsed time 5442743 0 2 2748282437 DB CPU 16094529404 0 2 2775283018 background IM population elapsed time 0 0 2 2799640666 cpu time 0 0 2 2821698184 sql execute elapsed time 15540487504 0 2 2966986086 background IM trickle repopulation elapsed time 0 0 2 3138706091 hard parse (sharing criteria) elapsed time 37545007 0 2 3649082374 DB time 16295005257 0 2 3780626361 background IM population cpu time 0 0 2 4092422623 background IM prepopulation cpu time 0 0 2 4125607023 failed parse (out of shared memory) elapsed time 0 0 2 4127043053 sequence load elapsed time 66410 0 2 4157170894 background elapsed time 16395317005 0 2 4273067414 OLAP engine CPU time 0 0 |
2. RAC等待类的查询语句：
包含每个实例信息的版本：
SELECT inst_id, wait_class time_cat , ROUND ( (time_secs), 2) time_secs, ROUND ((time_secs) * 100 / SUM(time_secs) OVER (),2) pct FROM (SELECT inst_id,wait_class , sum(time_waited_micro) / 1000000 time_secs FROM gv$system_event WHERE wait_class <> 'Idle' AND time_waited > 0 GROUP BY inst_id,wait_class UNION SELECT inst_id , 'CPU', ROUND((SUM(VALUE) / 1000000),2) time_secs FROM gv$sys_time_model WHERE stat_name IN ('background cpu time', 'DB CPU') group by inst_id ) ORDER BY time_secs DESC; |
不包含每个实例信息的版本：
SELECT wait_class time_cat , ROUND ( (time_secs), 2) time_secs, ROUND ((time_secs) * 100 / SUM(time_secs) OVER (),2) pct FROM (SELECT wait_class , sum(time_waited_micro) / 1000000 time_secs FROM gv$system_event WHERE wait_class <> 'Idle' AND time_waited > 0 GROUP BY wait_class UNION SELECT 'CPU', ROUND((SUM(VALUE) / 1000000),2) time_secs FROM gv$sys_time_model WHERE stat_name IN ('background cpu time', 'DB CPU') ) ORDER BY time_secs DESC; |
不包含每个实例信息的版本查询结果示例：
time_cat time_secs pct ---------------------------------------- CPU 59154.66 87.78 Other 4968.12 7.37 System I/O 1460.99 2.17 User I/O 1299.48 1.93 Concurrency 179.85 0.27 Cluster 155.03 0.23 Network 106.33 0.16 Application 36.64 0.05 Commit 21.34 0.03 Administrative 8.48 0.01 Configuration 2.28 0 |
3. RAC集群细节类别等待事件的查询语句:
包含每个实例信息的版本：
WITH system_event AS (SELECT CASE WHEN wait_class = 'Cluster' --转而查询细项 THEN event ELSE wait_class END wait_type, e.* FROM gv$system_event e) SELECT inst_id,wait_type, ROUND(total_waits/1000,2) waits_1000 , ROUND(time_waited_micro/1000000/3600,2) time_waited_hours, ROUND(time_waited_micro/1000/total_waits,2) avg_wait_ms , ROUND(time_waited_micro*100/SUM(time_waited_micro) OVER(),2) pct_time FROM (SELECT inst_id,wait_type, SUM(total_waits) total_waits, SUM(time_waited_micro) time_waited_micro FROM system_event e GROUP BY inst_id,wait_type UNION SELECT inst_id,'CPU',NULL, SUM(VALUE) FROM gv$sys_time_model WHERE stat_name IN ('background cpu time', 'DB CPU') group by inst_id ) WHERE wait_type <> 'Idle' ORDER BY time_waited_micro DESC; |
不包含每个实例信息的版本：
WITH system_event AS |
不包含每个实例信息的版本查询结果示例：
CPU 17.65 88.21 Other 48762.51 1.41 0.1 7.07 System I/O 5317.69 0.42 0.29 2.12 User I/O 3227.13 0.38 0.42 1.88 Concurrency 1874.22 0.05 0.1 0.25 Network 13767.72 0.03 0.01 0.15 gc cr block 2-way 176.13 0.01 0.21 0.05 Application 126.98 0.01 0.29 0.05 gc current block busy 96.01 0.01 0.32 0.04 Commit 1.66 0.01 13.36 0.03 gc current block 2-way 79.28 0.01 0.26 0.03 gc quiesce 0.02 0 704.5 0.02 gc cr multi block mixed 22.42 0 0.73 0.02 gc current grant busy 54.22 0 0.17 0.01 Administrative 0.56 0 15.82 0.01 gc cr block busy 5.91 0 1.4 0.01 gc cr grant 2-way 31.87 0 0.17 0.01 gc buffer busy release 0.82 0 4.74 0.01 gc current grant 2-way 15.64 0 0.19 0 Configuration 2.51 0 0.93 0 gc buffer busy acquire 1.84 0 1 0 gc cr multi block grant 5.55 0 0.33 0 gc current multi block request 4.02 0 0.38 0 gc cr disk read 4.97 0 0.16 0 gc index operation 0.19 0 1.53 0 gc current split 0.2 0 0.34 0 gc cr grant busy 0.13 0 0.3 0 gc current block congested 0.01 0 2 0 gc current retry 0.04 0 0.16 0 gc current grant congested 0 0 1.63 0 service monitor: inst recovery completion 0 0 0.24 0 |
gc cr/current block 2-way 2个实例的Global Cache block requests等待事件。Master实例内存中有所需要的数据块的等待事件。Master实例可以发送该数据块给所需要的实例。
gc cr/current block 3-way 3个以上实例的Global Cache block requests等待事件。Master实例内存中没有所需要的数据块，需要发送请求给拥有该数据块的实例（就是请求实例和Master实例外的第三方）。
gc cr/current multi block request 1个请求访问多个数据块，一般是全表扫描或者全索引扫描。
gc cr/current grant 2-way master实例发现所有实例中都没有所需要的块，请求实例需要执行IO。
gc cr/current block busy 请求实例需要等待拥有数据块的实例处理完成某些操作后才能传送数据块。 请求实例也需要把redo log刷回redo log文件后才能传送该一致性数据块拷贝。单实例的环境也可能会有buffer busy的情况。
gc cr/current block congested 因为CPU或者内存不够导致LMS进程跟进不及请求。
gc cr/current block lost 内连网故障。
4. RAC集群IO方面等待事件的查询语句:
包含每个实例信息的版本：
SELECT inst_id,event, SUM(total_waits) total_waits, ROUND(SUM(time_waited_micro) / 1000000, 2) time_waited_secs, ROUND(SUM(time_waited_micro)/1000 /SUM(total_waits), 2) avg_ms FROM gv$system_event WHERE wait_class <> 'Idle' AND( event LIKE 'gc%block%way' OR event LIKE 'gc%multi%' or event like 'gc%grant%' OR event = 'db file sequential read') GROUP BY inst_id,event HAVING SUM(total_waits) > 0 ORDER BY event; |
不包含每个实例信息的版本：
SELECT event, SUM(total_waits) total_waits, |
不包含每个实例信息的版本查询结果示例：
db file sequential read 109435 46.02 0.42 gc cr block 2-way 198083 41.73 0.21 gc cr grant 2-way 37592 6.37 0.17 gc cr grant busy 131 0.04 0.3 gc cr multi block grant 7273 2.32 0.32 gc cr multi block mixed 25240 20.07 0.8 gc current block 2-way 86665 21.88 0.25 gc current grant 2-way 17833 3.33 0.19 gc current grant busy 61179 10.66 0.17 gc current grant congested 1 0 1.63 gc current multi block request 4542 1.76 0.39 |
5. 实验环境脚本：
同时在第5部分介绍用于验证的实验环境，和工作负载脚本。工作负载的数据泵导入请参考这篇文章：https://www.botangdb.com/mytec/mytec_sqltuning/202204/00900085.html的“向12c数据库（库名：orcl）导入APPS用户方案（脱敏的EBS套件的APPS用户方案供下载：https://www.botangdb.com/myupload/202204/ebs_workload.dmp）”部分。之后，请将如下的4个文件上传于同一个目录，加上执行位，然后执行for.sh脚本。当工作负载在RAC系统上执行一段时间后，执行本文以上的查询语句，以对RAC集群进行性能诊断与分析。
工作负载运行环境：Oracle RAC Database 23.5.0.24.07（/u01/app/oracle/product/23.5.0/dbhome_1），SID为cdb1，插件数据库为pdb1_1。
