关于session的诊断,可以基于动态性能视图,ash,awr..
自己也写过一些简单的脚本,在平时的工作中也能够完成一些基本的工作。今天在看taner分享的脚本snapper的时候,让自己眼前一亮,也发现自己存在着很多的不足的地方。
可以从脚本中看到他孜孜不倦的分享着自己的心得,而且自己也写了一些更加深入的一些工具集来解析oracle的技术细节。
脚本的功能很多,林林总总下来代码有近2000多行,功能点很多,自己看文档也简单尝试了几个。发现还不错。可以在平时的工作中也基于自己的需求来做一些修改。
简单示范几个脚本的使用例子。
比如我想多次抓取session的细节,可以通过如下的参数来辅助,下面的例子就是示范抓取所有的session信心,没5秒抓取一次,一共抓取2次
SQL> @snapper ash=sid+event+wait_class+sql_id,ash1=sql_id 5 2 all
Sampling SID all with interval 5 seconds, taking 2 snapshots...
------------------------------------------------------------------------------------------
Active% | SID | EVENT | WAIT_CLASS | SQL_ID
2% | 6 | control file parallel write | System I/O |
-------------------------
Active% | SQL_ID
2% |
-- End of ASH snap 1, end=2014-09-27 07:39:44, seconds=5, samples_taken=41
-- End of ASH snap 2, end=2014-09-27 07:39:49, seconds=5, samples_taken=45
PL/SQL procedure successfully completed.
下面还有一些作者提供的例子。
@snapper ash,stats 1 1 515
-- (Output one 1-second snapshot of session 515 using dbms_output and exit
-- Wait, gv$sesstat and gv$sess_time_model statistics are reported by default
-- Starting from V3 the ASH style session activity report is shown as well)
--
-- @snapper stats,gather=w 1 1 515
-- only Wait event statistics are reported, no ASH)
-- @snapper ash,gather=st 1 1 515
-- only gv$sesstat and gv$sess_Time_model statistics are gathered + ASH)
-- @snapper trace,ash,gather=stw,pagesize=0 10 90 117,210,313
-- (Write 90 10-second snapshots into tracefile for session IDs 117,210,313
-- all statistics are reported, do not print any headers)
-- @snapper trace,ash 900 999999999 "select sid from v$session"
-- (Take a snapshot of ALL sessions every 15 minutes and write the output to trace,
-- loop (almost) forever )
-- @snapper out,trace 300 12 "select sid from v$session where username='APPS'"
-- (Take 12 5-minute snapshots of all sessions belonging to APPS user, write
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuYTM1gDOwMTNjVWNzcTYxQjZhNWMlRGOmJjYmRDZ1ITZfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)