一、说明
Snapper由tanel poder大佬编制，用于性能分析的脚本。snapper从V$SESSTAT 等视图中获取快照，并显示快照期间某个会话的性能计数器增量了多少，旨在为现场 DBA 提供一种快速、简单的临时性能故障排除工具，每当数据库出现问题时，他们就必须亲自动手（并快速解决问题！）。它旨在成为一个灵活的第一轮性能故障排除工具，一个故障排除的入口点工具。
- 1、Snapper 不会在数据库中创建任何对象
- 2、Snapper 只是一个匿名 PL/SQL 块，动态解析和编译
- 3、Snapper 不需要对数据库架构或设置进行任何更改！
二、语法
SET LINESIZE 10000 TRIMOUT ON TRIMSPOOL OFF HEAD OFF PAGESIZE 5000
snapper.sql <ash[1-3]|stats|all>[,out][,trace][,pagesize=X][,gather=[s][t][w][l][e][b][a]]> <seconds_in_snap> <snapshot_count> <sid(s)_to_snap>
1、<ash[1-3]|stats|all>部分
这部分接gv$session中的字段，比如ash=sql_id+event+wait_class明可以自己指定用于报告的GV$SESSION列。列是由+号分隔的(因为逗号是snapper参数分隔符,而不是ASH列分隔符)。
除了"ash"报告外,还可以在同一个snapper采样快照期间获得另外3个报告。如果想要每个Snapper快照生成多个TOP报告,只需包含ash1=col1+col2,ash2=col3+col4,…参数即可
stats,如果传入stats，则采样gv$sesstat、gv$sess_time_model、gv$session_event性能计数器,并报告这些统计数据在Snapper运行期间增加了多少(增量)
all 报告ASH和stats两个部分
2、[,out][,trace][,pagesize=X]
这部分用于格式化输出，可以不填则默认out，每个参数代表如下：
- out
使用dbms_output.put_line()进行输出。由于dbms_output的限制,输出只有在Snapper运行完成时才会显示。这是默认设置。 - trace
将输出写入server process的trace文件，(必须对sys.dbms_system.ksdwrt()拥有执行权限,如果愿意,也可以同时使用out和trace参数) - pagesize
在X个快照后显示标题行。如果pagesize=0,则不显示任何标题。pagesize=-1将只显示简洁的标题一次。
3、[,gather=[s][t][w][l][e][b][a]]>
这部分可以省略，如果神略默认表示gather=stw如果指定,则收集以下内容:
-
会话级别统计信息:
- s - 来自gv$sesstat的会话统计信息
- t - 来自gv$sess_time_model的会话时间模型信息
- w - 来自gv$session_event和gv$session_wait的会话等待统计信息
-
实例级别统计信息:
- l - 实例 Latch,从v$latch获取统计信息（ gets +ime_gets ）
- e - 实例入队锁从v$enqueue_stat获取统计信息
- b - buffer get 来自x$kcbsw的统计信息-- 在 10.2.x 以下的版本中有用
- a - 以上所有统计信息
下面部分用于控制状态采样数据的信息
- sinclude - 如果指定,则只显示与sinclude的LIKE模式匹配的GV$SESSTAT统计信息(在10g+版本中使用REGEXP_LIKE)
- linclude - 如果指定,则只显示与linclude的LIKE模式匹配的GV$LATCH锁定统计信息(在10g+版本中使用REGEXP_LIKE)
- tinclude - 如果指定,则只显示与tinclude的LIKE模式匹配的GV$SESS_TIME_MODEL统计信息(在10g+版本中使用REGEXP_LIKE)
- winclude - 如果指定,则只显示与winclude的LIKE模式匹配的GV$SESSION_EVENT等待统计信息(在10g+版本中使用REGEXP_LIKE)
可以按任意顺序组合上述参数,用逗号分隔它们，但是不要使用空格,否则它们将被sqlplus视为下一个参数，如果想使用空格,请用双引号括起整个sqlplus参数。
4、<seconds_in_snap>
采集快照之间的秒数
5、<snapshot_count>
要采集的快照数量(最大值为power(2,31)-1)
6、<sid(s)_to_snap>
这部分可以是一个会话ID、用逗号分隔的多个会话ID,或者返回SID列表的SQL语句(如果需要在该参数文本中使用空格,请用双引号将其括起来)。
如果想为所有sid采集快照,请使用"all"作为<sids_to_snap>参数的值。或者,可以使用"select inst_id,sid from gv$session"作为<sids_to_snap>参数的值来捕获所有SID。可以编写任何查询(包含多个and/or条件),以指定只捕获你想要的SID的复杂规则。
从版本3.0开始,在<sids_to_snap>参数中,除了sid之外,还可以使用以下会话ID选择选项(只要能在gv$session或者gv$process中约束会话即可):
sid=123
user=tanel
user=tanel%
spid=1234
ospid=1234
pid=1234
qc=123
qcsid=123
program=sqlplus%
machine=linux01
osuser=oracle
module=HR
"action=Find Order"
client_id=tanelpoder
三、测试
@snapper ash,stats 1 1 515
使用dbms_output输出会话515的1秒快照并退出,默认情况下会报告gv$sesstat和gv$sess_time_model统计信息
测试语句
create table t1 as select * from dba_objects;
insert into t1 select * from t1;
SQL> @snapper ash,stats 1 1 393
Sampling SID 393 with interval 1 seconds, taking 1 snapshots...
-- Session Snapper v4.33 - by Tanel Poder ( https://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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
393, SYS , STAT, recursive calls , 12, 9.73, , , , , 8.33ms recursive CPU per recursive call
393, SYS , STAT, recursive cpu usage , 10, 8.11, , , , , 100ms total recursive CPU usage
393, SYS , STAT, session logical reads , 88327, 71.65k, , , , , 88.35k total buffer visits
393, SYS , STAT, user I/O wait time , 15, 12.17, , , , , ~ per execution
393, SYS , STAT, non-idle wait time , 49, 39.75, , , , , ~ per execution
393, SYS , STAT, non-idle wait count , 396, 321.21, , , , , ~ per execution
393, SYS , STAT, messages sent , 65, 52.72, , , , , ~ per execution
393, SYS , STAT, enqueue requests , 2225, 1.8k, , , , , ~ per execution
393, SYS , STAT, enqueue releases , 2225, 1.8k, , , , , ~ per execution
393, SYS , STAT, physical read total IO requests , 160, 129.78, , , , , 15.56k bytes per request
393, SYS , STAT, physical read total bytes , 2490368, 2.02M, , , , , ~ per execution
393, SYS , STAT, physical write total IO requests , 120, 97.34, , , , , 702.87k bytes per request
393, SYS , STAT, physical write total multi block requests , 80, 64.89, , , , , 40 total single block write requests
393, SYS , STAT, physical write total bytes , 84344832, 68.42M, , , , , ~ per execution
393, SYS , STAT, cell physical IO interconnect bytes , 86835200, 70.44M, , , , , ~ per execution
393, SYS , STAT, total cf enq hold time , 6, 4.87, , , , , ~ per execution
393, SYS , STAT, total number of cf enq holders , 24, 19.47, , , , , ~ per execution
393, SYS , STAT, db block gets , 57456, 46.61k, , , , , ~ per execution
393, SYS , STAT, db block gets from cache , 57459, 46.61k, , , , , ~ per execution
393, SYS , STAT, db block gets from cache (fastpath) , 46792, 37.96k, , , , , ~ per execution
393, SYS , STAT, consistent gets , 31167, 25.28k, , , , , ~ per execution
393, SYS , STAT, consistent gets from cache , 31167, 25.28k, , , , , ~ per execution
393, SYS , STAT, consistent gets pin , 30943, 25.1k, , , , , ~ per execution
393, SYS , STAT, consistent gets pin (fastpath) , 30944, 25.1k, , , , , ~ per execution
393, SYS , STAT, consistent gets examination , 224, 181.7, , , , , ~ per execution
393, SYS , STAT, consistent gets examination (fastpath) , 224, 181.7, , , , , ~ per execution
393, SYS , STAT, logical read bytes from cache , 726294528, 589.13M, , , , , ~ per execution
393, SYS , STAT, db block changes , 84766, 68.76k, , , , , ~ per execution
393, SYS , STAT, change write time , 31, 25.15, , , , , ~ per execution
393, SYS , STAT, free buffer requested , 10704, 8.68k, , , , , ~ per execution
393, SYS , STAT, hot buffers moved to head of LRU , 1820, 1.48k, , , , , ~ per execution
393, SYS , STAT, free buffer inspected , 2346, 1.9k, , , , , ~ per execution
393, SYS , STAT, write clones created in foreground , 1, .81, , , , , ~ per execution
393, SYS , STAT, calls to kcmgcs , 31157, 25.27k, , , , , ~ per execution
393, SYS , STAT, calls to kcmgas , 67, 54.35, , , , , ~ per execution
393, SYS , STAT, calls to get snapshot scn: kcmgss , 18, 14.6, , , , , ~ per execution
393, SYS , STAT, redo entries , 53002, 42.99k, , , , , ~ per execution
393, SYS , STAT, redo size , 85325488, 69.21M, , , , , ~ bytes per user commit
393, SYS , STAT, redo ordering marks , 38, 30.82, , , , , ~ per execution
393, SYS , STAT, undo change vector size , 2345864, 1.9M, , , , , ~ per execution
393, SYS , STAT, no work - consistent read gets , 10239, 8.31k, , , , , ~ per execution
393, SYS , STAT, rollback changes - undo records applied , 8, 6.49, , , , , ~ per execution
393, SYS , STAT, transaction rollbacks , 8, 6.49, , , , , ~ per execution
393, SYS , STAT, active txn count during cleanout , 208, 168.72, , , , , ~ per execution
393, SYS , STAT, cleanout - number of ktugct calls , 208, 168.72, , , , , ~ per execution
393, SYS , STAT, commit batch/immediate requested , 8, 6.49, , , , , ~ per execution
393, SYS , STAT, commit immediate requested , 8, 6.49, , , , , ~ per execution
393, SYS , STAT, commit batch/immediate performed , 8, 6.49, , , , , ~ per execution
393, SYS , STAT, commit immediate performed , 8, 6.49, , , , , ~ per execution
393, SYS , STAT, KTFB alloc req , 11, 8.92, , , , , ~ per execution
393, SYS , STAT, KTFB alloc space (block) , 77594624, 62.94M, , , , , ~ per execution
393, SYS , STAT, KTFB alloc time (ms) , 471016, 382.06k, , , , , ~ per execution
393, SYS , STAT, KTFB apply req , 2, 1.62, , , , , ~ per execution
393, SYS , STAT, KTFB apply time (ms) , 8, 6.49, , , , , ~ per execution
393, SYS , STAT, table scan rows gotten , 508770, 412.69k, , , , , ~ per execution
393, SYS , STAT, table scan disk non-IMC rows gotten , 508525, 412.49k, , , , , ~ per execution
393, SYS , STAT, table scan blocks gotten , 9822, 7.97k, , , , , ~ per execution
393, SYS , STAT, HSC Heap Segment Block Changes , 17934, 14.55k, , , , , ~ per execution
393, SYS , STAT, Heap Segment Array Inserts , 17897, 14.52k, , , , , ~ per execution
393, SYS , STAT, buffer is pinned count , 18, 14.6, , , , , .02 % buffer gets avoided thanks to buffer pin caching
393, SYS , TIME, DB CPU , 1248192, 1.01s, 101.2%, [@@@@@@@@@@], , ,
393, SYS , TIME, sql execute elapsed time , 1994702, 1.62s, 161.8%, [##########], , ,
393, SYS , TIME, DB time , 1994702, 1.62s, 161.8%, [##########], , , -61.8 % unaccounted-for time*
393, SYS , WAIT, Disk file operations I/O , 1875, 1.52ms, .2%, [ ], 20, 16.22, 93.75us average wait
393, SYS , WAIT, direct path sync , 69817, 56.63ms, 5.7%, [W ], 7, 5.68, 9.97ms average wait
393, SYS , WAIT, Data file init write , 44786, 36.33ms, 3.6%, [W ], 84, 68.14, 533.17us average wait
393, SYS , WAIT, control file sequential read , 518, 420.17us, .0%, [ ], 126, 102.2, 4.11us average wait
393, SYS , WAIT, control file parallel write , 3019, 2.45ms, .2%, [ ], 21, 17.03, 143.76us average wait
393, SYS , WAIT, db file sequential read , 109, 88.41us, .0%, [ ], 14, 11.36, 7.79us average wait
393, SYS , WAIT, db file single write , 4949, 4.01ms, .4%, [ ], 7, 5.68, 707us average wait
393, SYS , WAIT, events in waitclass Other , 545328, 442.34ms, 44.2%, [WWWWW ], 14, 11.36, 38.95ms average wait
-- End of Stats snap 1, end=2024-05-11 08:42:04, seconds=1.2
---------------------------------------------------------------------------------------------------------------
ActSes %Thread | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS
---------------------------------------------------------------------------------------------------------------
.56 (56%) | 1 | 9vp603v4vmjrn | 0 | ON CPU | ON CPU
.33 (33%) | 1 | 9vp603v4vmjrn | 0 | DLM cross inst call completion | Other
.11 (11%) | 1 | 9vp603v4vmjrn | 0 | direct path sync | User I/O
-- End of ASH snap 1, end=2024-05-11 08:42:04, seconds=1, samples_taken=9, AAS=1
PL/SQL procedure successfully completed.
以上结果显示为两部分，上面部分为stats部分，下面部分为ash部分。这个ash不是oracle的ash报告，而是对v$session进行采样得到。其中stats部分可以根据TYPE列将其分解为三部分，TYPE为STAT部分展示v$sesstat内容，TYPE为TIME部分展示TIME_MODEL内容，TYPE为WAIT部分展示v$session_event部分。
@snapper stats,gather=w 1 1 515
使用dbms_output输出会话515的1秒快照并退出仅报告等待事件统计信息,不包括ASH
SQL> @snapper stats,gather=w 1 1 393
Sampling SID 393 with interval 1 seconds, taking 1 snapshots...
-- Session Snapper v4.33 - by Tanel Poder ( https://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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
393, SYS , WAIT, log buffer space , 15348, 14.67ms, 1.5%, [W ], 2, 1.91, 7.67ms average wait
393, SYS , WAIT, log file switch completion , 4119, 3.94ms, .4%, [ ], 2, 1.91, 2.06ms average wait
-- End of Stats snap 1, end=2024-05-11 09:13:37, seconds=1
PL/SQL procedure successfully completed.
@snapper ash,gather=st 1 1 515
使用dbms_output输出会话515的1秒快照并退出只收集gv$sesstat和gv$sess_Time_model统计信息+ASH
SQL> @snapper stats,gather=st 1 1 393
Sampling SID 393 with interval 1 seconds, taking 1 snapshots...
-- Session Snapper v4.33 - by Tanel Poder ( https://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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
393, SYS , STAT, non-idle wait time , 135, 101.11, , , , , ~ per execution
393, SYS , STAT, non-idle wait count , 13, 9.74, , , , , ~ per execution
393, SYS , STAT, messages sent , 13, 9.74, , , , , ~ per execution
393, SYS , STAT, redo log space requests , 13, 9.74, , , , , ~ per execution
393, SYS , STAT, redo log space wait time , 135, 101.11, , , , , ~ per execution
393, SYS , TIME, DB CPU , 166908, 125ms, 12.5%, [@@ ], , ,
393, SYS , TIME, sql execute elapsed time , 2068448, 1.55s, 154.9%, [##########], , ,
393, SYS , TIME, DB time , 2068448, 1.55s, 154.9%, [##########], , , -54.91 % unaccounted-for time*
-- End of Stats snap 1, end=2024-05-11 09:15:19, seconds=1.3
PL/SQL procedure successfully completed.
@snapper trace,ash,gather=stw,pagesize=0 10 90 117,210,313
将会话ID 117、210、313的90个10秒快照写入跟踪文件报告所有统计信息,不打印任何标题
SQL> @snapper trace,stats,gather=stw,pagesize=0 1 1 393
Sampling SID 393 with interval 1 seconds, taking 1 snapshots...
PL/SQL procedure successfully completed.
[oracle@19c02 trace]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_452276.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_452276.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME: /u01/app/oracle/product/19.3.0/dbhome_1
System name: Linux
Node name: 19c02
Release: 5.4.17-2136.300.7.el8uek.x86_64
Version: #2 SMP Fri Oct 8 16:23:01 PDT 2021
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 54
Unix process pid: 452276, image: oracle@19c02 (TNS V1-V3)
*** 2024-05-11T09:17:15.158476+08:00
*** SESSION ID:(265.45923) 2024-05-11T09:17:15.158497+08:00
*** CLIENT ID:() 2024-05-11T09:17:15.158500+08:00
*** SERVICE NAME:(SYS$USERS) 2024-05-11T09:17:15.158504+08:00
*** MODULE NAME:(sqlplus@19c02 (TNS V1-V3)) 2024-05-11T09:17:15.158507+08:00
*** ACTION NAME:() 2024-05-11T09:17:15.158509+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2024-05-11T09:17:15.158512+08:00
393, SYS , WAIT, SQL*Net message from client , 2000000, 1.58s, 158.1%, [WWWWWWWWWW], 0, 0, 2s average wait
*** 2024-05-11T09:17:27.655559+08:00
393, SYS , STAT, non-idle wait time , 129, 98.97, , , , , ~ per execution
393, SYS , STAT, non-idle wait count , 13, 9.97, , , , , ~ per execution
393, SYS , STAT, messages sent , 13, 9.97, , , , , ~ per execution
393, SYS , STAT, redo log space requests , 13, 9.97, , , , , ~ per execution
393, SYS , STAT, redo log space wait time , 129, 98.97, , , , , ~ per execution
393, SYS , TIME, DB CPU , 1008448, 773.66ms, 77.4%, [@@@@@@@@ ], , ,
393, SYS , TIME, sql execute elapsed time , 2018607, 1.55s, 154.9%, [##########], , ,
393, SYS , TIME, DB time , 2018607, 1.55s, 154.9%, [##########], , , -54.86 % unaccounted-for time*
393, SYS , WAIT, log file switch (checkpoint incomplete) , 3295673, 2.53s, 252.8%, [WWWWWWWWWW], 0, 0, 3.3s average wait
@snapper trace,ash 900 999999999 all
每15分钟对所有会话进行一次快照,并将输出写入跟踪文件,持续循环(几乎)永不停止
@snapper out,trace 300 12 “select inst_id,sid from gv$session where username=‘APPS’”
对属于APPS用户的所有会话进行12次5分钟快照,将输出写入dbms_output和跟踪文件
@snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all
从所有会话中获取ash会话活动快照，还要求 Snapper 收集时间模型统计信息 (t) 和 V$SESSTAT 统计信息 (s)，事件模型中只包CPU关键字，会话等待中只包含parse关键字
SQL> @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.33 - by Tanel Poder ( https://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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1, (PSP0) , TIME, background cpu time , 452, 101.85us, .0%, [ ], , ,
4, (SVCB) , TIME, background cpu time , 77, 17.35us, .0%, [ ], , ,
5, (LGWR) , TIME, background cpu time , 12821, 2.89ms, .3%, [ ], , ,
6, (LG01) , TIME, background cpu time , 69333, 15.62ms, 1.6%, [# ], , ,
7, (LREG) , TIME, background cpu time , 151, 34.03us, .0%, [ ], , ,
8, (MMON) , TIME, background cpu time , 545, 122.81us, .0%, [ ], , ,
13, (TT02) , TIME, background cpu time , 90, 20.28us, .0%, [ ], , ,
128, (GEN1) , TIME, background cpu time , 157, 35.38us, .0%, [ ], , ,
130, (PMAN) , TIME, background cpu time , 347, 78.19us, .0%, [ ], , ,
131, (CKPT) , TIME, background cpu time , 618, 139.26us, .0%, [ ], , ,
132, (SMCO) , TIME, background cpu time , 142, 32us, .0%, [ ], , ,
133, (W001) , TIME, background cpu time , 66, 14.87us, .0%, [ ], , ,
137, (W002) , TIME, background cpu time , 42, 9.46us, .0%, [ ], , ,
253, (PMON) , TIME, background cpu time , 206, 46.42us, .0%, [ ], , ,
254, (GEN0) , TIME, background cpu time , 199, 44.84us, .0%, [ ], , ,
256, (DBRM) , TIME, background cpu time , 1049, 236.38us, .0%, [ ], , ,
257, (DIA0) , TIME, background cpu time , 1442, 324.93us, .0%, [ ], , ,
258, (LG00) , TIME, background cpu time , 354842, 79.96ms, 8.0%, [# ], , ,
260, (PXMN) , TIME, background cpu time , 64, 14.42us, .0%, [ ], , ,
265, SYS , TIME, DB CPU , 584724, 131.76ms, 13.2%, [@@ ], , ,
276, (W006) , TIME, background cpu time , 20, 4.51us, .0%, [ ], , ,
380, (CLMN) , TIME, background cpu time , 307, 69.18us, .0%, [ ], , ,
381, (MMAN) , TIME, background cpu time , 72, 16.22us, .0%, [ ], , ,
382, (DIAG) , TIME, background cpu time , 74, 16.67us, .0%, [ ], , ,
384, (DBW0) , TIME, background cpu time , 1175312, 264.84ms, 26.5%, [### ], , ,
386, (W000) , TIME, background cpu time , 22, 4.96us, .0%, [ ], , ,
388, (MMNL) , TIME, background cpu time , 1074, 242.01us, .0%, [ ], , ,
392, (CJQ0) , TIME, background cpu time , 226, 50.93us, .0%, [ ], , ,
393, SYS , TIME, DB CPU , 2509011, 565.36ms, 56.5%, [@@@@@@ ], , ,
-- End of Stats snap 1, end=2024-05-11 09:32:17, seconds=4.4
--------------------------------------------------------------------------
ActSes %Thread | EVENT | WAIT_CLASS
--------------------------------------------------------------------------
.71 (71%) | db file async I/O submit | System I/O
.54 (54%) | ON CPU | ON CPU
.24 (24%) | log file parallel write | System I/O
.15 (15%) | log file switch (checkpoint incompl | Configuration
.12 (12%) | direct path read | User I/O
.10 (10%) | Data file init write | User I/O
.07 (7%) | log buffer space | Configuration
.05 (5%) | DLM cross inst call completion | Other
.05 (5%) | direct path sync | User I/O
.02 (2%) | LGWR worker group ordering | Other
-- End of ASH snap 1, end=2024-05-11 09:32:17, seconds=5, samples_taken=41, AAS=2.1
PL/SQL procedure successfully completed.
SQL> @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 user=SYS
Sampling SID user=SYS with interval 5 seconds, taking 1 snapshots...
-- Session Snapper v4.33 - by Tanel Poder ( https://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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
265, SYS , TIME, DB CPU , 1745066, 287.66ms, 28.8%, [@@@ ], , ,
393, SYS , STAT, parse count (total) , 2, .33, , , , , ~ softparses per hardparse
393, SYS , TIME, DB CPU , 4279025, 705.37ms, 70.5%, [@@@@@@@@ ], , ,
-- End of Stats snap 1, end=2024-05-11 09:34:48, seconds=6.1
-----------------------------------------------------------------------------------
ActSes %Thread | SID | EVENT | WAIT_CLASS
-----------------------------------------------------------------------------------
.67 (67%) | 393 | ON CPU | ON CPU
.20 (20%) | 393 | direct path read | User I/O
.11 (11%) | 393 | log file switch (checkpoint incompl | Configuration
.02 (2%) | 393 | log file switch completion | Configuration
-------------------------------------------------------------------------
ActSes %Thread | SID | SQL_ID | MODULE
-------------------------------------------------------------------------
1.00 (100%) | 393 | 9vp603v4vmjrn | sqlplus@19c02 (TNS V1-V3)
-- End of ASH snap 1, end=2024-05-11 09:34:48, seconds=5, samples_taken=45, AAS=1
PL/SQL procedure successfully completed.
四、原文及参考
http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper
https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql
https://tanelpoder.com/2013/02/10/session-snapper-v4-the-worlds-most-advanced-oracle-troubleshooting-script/
最后修改时间：2024-05-11 14:14:31
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
