常见snapper.sql脚本使用方法
钱季
标签（空格分隔）： Oracle 脚本
SID查询方式
SQL> @snapper ash 5 1 17233,10178
Sampling SID 17233,10178 with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------
ActSes %Thread | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
----------------------------------------------------------------------------
.25 (25%) | 1 | a658w75muz25f | 0 | ON CPU| ON CPU
.25 (25%) | 1 | 77327qvda8qyg | 0 | ON CPU| ON CPU
.17 (17%) | 1 | buad56gf2rswv | 0 | ON CPU| ON CPU
.08 (8%) | 1 | gjyc96sfxwcuu | 0 | ON CPU| ON CPU
.04 (4%) | 1 | | | ON CPU| ON CPU
-- End of ASH snap 1, end=2019-10-14 09:39:41, seconds=5, samples_taken=24, AAS=.8
PL/SQL procedure successfully completed.
SQL> @snapper ash 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------
ActSes %Thread|INST| SQL_ID |SQL_CHILD| EVENT |WAIT_CLASS
----------------------------------------------------------------------------------
5.40 (540%)| 1| a658w75muz25f| 0 | ON CPU |ON CPU
2.40 (240%)| 1| gjyc96sfxwcuu| 0 | ON CPU |ON CPU
2.05 (205%)| 1| 77327qvda8qyg| 0 | ON CPU |ON CPU
1.45 (145%)| 1| buad56gf2rswv| 0 | ON CPU |ON CPU
.60 (60%)| 1| 1kc4vgbzbzcdw| 0 | gc buffer busy acquire |Cluster
.45 (45%)| 1| | 0 | ON CPU |ON CPU
.35 (35%)| 1| a8vquysgphy63| 0 | db file sequential read|User I/O
.35 (35%)| 1| da77m3uhvxnk6| 0 | gc buffer busy acquire |Cluster
.30 (30%)| 1| 1kc4vgbzbzcdw| 0 | ON CPU |ON CPU
.25 (25%)| 1| cgrw6mw52002m| 1 | db file sequential read|User I/O
-- End of ASH snap 1, end=2019-10-14 09:42:09, seconds=5, samples_taken=20, AAS=16.1
PL/SQL procedure successfully completed.
特定用户方式
SQL> @snapper ash 5 1 user=DBWEBOPR
Sampling SID user=DBWEBOPR with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------------------------------
ActSes %Thread|INST|SQL_ID |SQL_CHILD|EVENT |WAIT_CLASS
--------------------------------------------------------------------------------
4.38 (438%)| 1|a658w75muz25f|0 |ON CPU |ON CPU
2.38 (238%)| 1|gjyc96sfxwcuu|0 |ON CPU |ON CPU
1.52 (152%)| 1|77327qvda8qyg|0 |ON CPU |ON CPU
.90 (90%)| 1|buad56gf2rswv|0 |ON CPU |ON CPU
.33 (33%)| 1|a8vquysgphy63|0 |db file sequential read|User I/O
.14 (14%)| 1| | |log file sync |Commit
.14 (14%)| 1|cgrw6mw52002m|1 |db file sequential read|User I/O
.10 (10%)| 1|ddktnmp9urgsk|1 |ON CPU |ON CPU
.10 (10%)| 1|a8vquysgphy63|0 |ON CPU |ON CPU
.10 (10%)| 1|a658w75muz25f|0 |db file sequential read|User I/O
-- End of ASH snap 1, end=2019-10-14 09:43:02, seconds=5, samples_taken=21, AAS=11
PL/SQL procedure successfully completed.
查询子句方式——特定用户方式及客户端程序
SQL> @snapper ash 5 1 "select inst_id,sid from gv$session where username='DBWEBOPR' and program not like 'sqlplus%'"
Sampling SID select inst_id,sid from gv$session where username='DBWEBOPR' and program not like 'sqlplus%' with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
------------------------------------------------------------------------------------
ActSes %Thread|INST|SQL_ID |SQL_CHILD| EVENT | WAIT_CLASS
------------------------------------------------------------------------------------
4.30 (430%)| 1|a658w75muz25f|0 | ON CPU | ON CPU
2.00 (200%)| 1|gjyc96sfxwcuu|0 | ON CPU | ON CPU
1.35 (135%)| 1|77327qvda8qyg|0 | ON CPU | ON CPU
1.25 (125%)| 1|buad56gf2rswv|0 | ON CPU | ON CPU
.55 (55%)| 1|1kc4vgbzbzcdw|0 | gc buffer busy acquire | Cluster
.45 (45%)| 1|a8vquysgphy63|0 | db file sequential read | User I/O
.35 (35%)| 1|da77m3uhvxnk6|0 | db file scattered read | User I/O
.25 (25%)| 1|da77m3uhvxnk6|0 | gc buffer busy acquire | Cluster
.25 (25%)| 1|cgrw6mw52002m|1 | db file sequential read | User I/O
.25 (25%)| 1|da77m3uhvxnk6|0 | gc cr multi block request| Cluster
-- End of ASH snap 1, end=2019-10-14 09:53:29, seconds=5, samples_taken=20, AAS=13.2
PL/SQL procedure successfully completed.
查询子句方式——等待事件
SQL> @snapper ash 5 1 "select inst_id,sid from gv$session where event='db file sequential read'"
Sampling SID select inst_id,sid from gv$session where event='db file sequential read' with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
-----------------------------------------------------------------------------------
ActSes %Thread|INST|SQL_ID |SQL_CHILD| EVENT | WAIT_CLASS
-----------------------------------------------------------------------------------
2.46 (246%)| 1|a658w75muz25f |0 | ON CPU | ON CPU
.71 (71%)| 1|77327qvda8qyg |0 | ON CPU | ON CPU
.11 (11%)| 1|a8vquysgphy63 |0 | db file sequential read| User I/O
.07 (7%)| 1|cgrw6mw52002m |1 | db file sequential read| User I/O
.04 (4%)| 1|4gt7c0kkphfqf |0 | db file sequential read| User I/O
.04 (4%)| 1|buad56gf2rswv |0 | ON CPU | ON CPU
.04 (4%)| 1| | | ON CPU | ON CPU
.04 (4%)| 1|az0531ww2ysbq |0 | db file sequential read| User I/O
.04 (4%)| 1|a8vquysgphy63 |0 | gc cr request | Cluster
.04 (4%)| 1|azypzch715n3b |0 | db file sequential read| User I/O
-- End of ASH snap 1, end=2019-10-14 09:56:15, seconds=5, samples_taken=28, AAS=3.8
PL/SQL procedure successfully completed.
锁问题分析
SQL> @snapper ash 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------------------------------------------
ActSes %Thread|INST|SQL_ID |SQL_CHILD| EVENT |WAIT_CLASS
------------------------------------------------------------------------------------------
1.00 (100%)| 1|6pypjxah56shb|0 | enq: TX - row lock contention|Application
.02 (2%)| 1|1nx1518vff191|4 | ON CPU |ON CPU
-- End of ASH snap 1, end=2019-10-14 10:04:23, seconds=5, samples_taken=48, AAS=1
PL/SQL procedure successfully completed.
SQL> @snapper ash=sid+sqlid+event+wait_class 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
------------------------------------------------------------------------------
ActSes %Thread|SID|SQL_ID | EVENT | WAIT_CLASS
------------------------------------------------------------------------------
1.00 (100%)| 37|6pypjxah56shb| enq: TX - row lock contention| Application
-- End of ASH snap 1, end=2019-10-14 10:09:41, seconds=5, samples_taken=41, AAS=1
-- sid=37的会话是锁资源请求者
PL/SQL procedure successfully completed.
SQL> @snapper ash=sqlid+event+wait_class+blocking_session+p2+p3 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------------------------------------------------
ActSes %Thread|SQL_ID |EVENT | WAIT_CLASS |BLOCKING_SES|P2 |P3
--------------------------------------------------------------------------------------------------
1.00 (100%)|6pypjxah56shb|enq: TX - row lock contention| Application|43 |131073|2994
-- End of ASH snap 1, end=2019-10-14 10:07:12, seconds=5, samples_taken=45, AAS=1
-- sid=43是锁资源持有者，即sid=43会话阻塞了sid=37的会话。
PL/SQL procedure successfully completed.
SQL> @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------
ActSes %Thread | SID | EVENT |WAIT_CLASS
----------------------------------------------------------------
.57 (57%) | 7621 | ON CPU |ON CPU
.48 (48%) | 12463 | ON CPU |ON CPU
.48 (48%) | 4253 | gc buffer busy acquire|Cluster
.48 (48%) | 15838 | gc buffer busy acquire|Cluster
.48 (48%) | 11289 | ON CPU |ON CPU
.43 (43%) | 12697 | ON CPU |ON CPU
.43 (43%) | 6790 | ON CPU |ON CPU
.43 (43%) | 17230 | ON CPU |ON CPU
.43 (43%) | 6782 | ON CPU |ON CPU
.38 (38%) | 14683 | ON CPU |ON CPU
--------------------------------------------------------------
ActSes %Thread | PLSQL_OBJE | PLSQL_SUBP | SQL_ID
--------------------------------------------------------------
5.48 (548%) | | | a658w75muz25f
2.48 (248%) | | | gjyc96sfxwcuu
1.38 (138%) | | | 77327qvda8qyg
1.00 (100%) | | | da77m3uhvxnk6
1.00 (100%) | | | 1kc4vgbzbzcdw
.81 (81%) | | | buad56gf2rswv
.48 (48%) | | |
.48 (48%) | | | a8vquysgphy63
.19 (19%) | | | cgrw6mw52002m
.14 (14%) | | | 0vfkbuu6nb6rm
-- End of ASH snap 1, end=2019-10-14 10:34:52, seconds=5, samples_taken=21, AAS=15.1
PL/SQL procedure successfully completed.
查看plsql的执行情况
SQL> @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------
ActSes %Thread | SID | EVENT | WAIT_CLASS
----------------------------------------------------------------------
.57 (57%) | 10465 | gc buffer busy acquire | Cluster
.57 (57%) | 8200 | ON CPU | ON CPU
.52 (52%) | 13828 | ON CPU | ON CPU
.48 (48%) | 1146 | ON CPU | ON CPU
.48 (48%) | 11601 | ON CPU | ON CPU
.43 (43%) | 13539 | ON CPU | ON CPU
.43 (43%) | 6802 | ON CPU | ON CPU
.38 (38%) | 4799 | ON CPU | ON CPU
.38 (38%) | 13286 | ON CPU | ON CPU
.38 (38%) | 6782 | ON CPU | ON CPU
--------------------------------------------------------------
ActSes %Thread | PLSQL_OBJE | PLSQL_SUBP | SQL_ID
--------------------------------------------------------------
4.29 (429%) | | | a658w75muz25f
2.05 (205%) | | | gjyc96sfxwcuu
1.19 (119%) | | | 77327qvda8qyg
1.00 (100%) | | | da77m3uhvxnk6
1.00 (100%) | | | 1kc4vgbzbzcdw
.90 (90%) | | | buad56gf2rswv
.57 (57%) | | |
.29 (29%) | | | bn9phj9vn58a8
.24 (24%) | | | a8vquysgphy63
.14 (14%) | | | cgrw6mw52002m
-----------------------------------------------------------------------------------
ActSes %Thread | PROGRAM | MODULE | ACTION
-----------------------------------------------------------------------------------
10.38 (1038%) | JDBC Thin Client | JDBC Thin Client |
2.00 (200%) | expora@w20k08da (TNS V1-V | expora@w20k08da (TNS V1-V |
.10 (10%) | IFCRMDataInYx@w4m901de (T | IFCRMDataInYx@w4m901de (T |
.10 (10%) | oracle@h5l2001rs (LGWR) | |
.10 (10%) | oracle@h5l2001rs (LMS2) | |
.10 (10%) | oracle@h5l2001rs (LMD0) | |
.05 (5%) | oracle@h5l2001rs (DBW2) | |
.05 (5%) | oracle@h5l2001rs (DIA0) | |
.05 (5%) | oracle@h5l2001rs (LMS0) | |
.05 (5%) | oracle@h5l2001rs (LMS1) | |
-- End of ASH snap 1, end=2019-10-14 10:36:41, seconds=5, samples_taken=21, AAS=13
PL/SQL procedure successfully completed.
查看session级硬解析情况
SQL> @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.27 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
-------------------------------------------------------------------------------------------------------------
SID, USERNAME, TYPE, STATISTIC , DELTA, HDELTA/SEC,%TIME, GRAPH ,NUM_WAITS,WAITS/SEC,AVERAGES
-------------------------------------------------------------------------------------------------------------
3, (PSP0) , TIME, background cpu time, 1000, 204.41us, .0%, [ ], , ,
8, (DIA0) , TIME, background cpu time, 2000, 408.82us, .0%, [ ], , ,
12, (CKPT) , TIME, background cpu time, 1000, 204.41us, .0%, [ ], , ,
24, (CJQ0) , TIME, background cpu time, 1000, 204.41us, .0%, [ ], , ,
33, SYS , TIME, DB CPU ,1034842, 211.53ms,21.2%, [@@@ ], , ,
-- End of Stats snap 1, end=2019-10-14 15:27:22, seconds=4.9
-------------------------------------------------------------------------
ActSes %Thread |SID | EVENT | WAIT_CLASS
-------------------------------------------------------------------------
1.00 (100%) | 37 | enq: TX - row lock contention | Application
---------------------------------------------------------------
ActSes %Thread |SID | SQL_ID | MODULE
---------------------------------------------------------------
1.00 (100%) | 37 | 6pypjxah56shb | SQL*Plus
-- End of ASH snap 1, end=2019-10-14 15:27:22, seconds=5, samples_taken=42, AAS=1
PL/SQL procedure successfully completed.
类似资料：