--4.通过Snapper脚本，他的主要功能是以跟采样周期成反比的频率对v$session视图经行取样。
--实验1. SYS@PROD1> start system_activity_setup.sql SYS@PROD1> SYS@PROD1> @@system_activity_teardown.sql SYS@PROD1> SET ECHO OFF SYS@PROD1> SYS@PROD1> DROP TYPE t_system_activity_tab; DROP TYPE t_system_activity_tab * ERROR at line 1: ORA-04043: object T_SYSTEM_ACTIVITY_TAB does not exist SYS@PROD1> DROP TYPE t_system_activity; DROP TYPE t_system_activity * ERROR at line 1: ORA-04043: object T_SYSTEM_ACTIVITY does not exist SYS@PROD1> DROP TYPE t_system_wait_class_tab; DROP TYPE t_system_wait_class_tab * ERROR at line 1: ORA-04043: object T_SYSTEM_WAIT_CLASS_TAB does not exist SYS@PROD1> DROP TYPE t_system_wait_class; DROP TYPE t_system_wait_class * ERROR at line 1: ORA-04043: object T_SYSTEM_WAIT_CLASS does not exist SYS@PROD1> SYS@PROD1> DROP FUNCTION system_activity; DROP FUNCTION system_activity * ERROR at line 1: ORA-04043: object SYSTEM_ACTIVITY does not exist SYS@PROD1> SYS@PROD1> DROP PUBLIC SYNONYM system_activity; DROP PUBLIC SYNONYM system_activity * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist SYS@PROD1> SYS@PROD1> CREATE TYPE t_system_wait_class AS OBJECT ( 2 wait_class# NUMBER, 3 wait_class VARCHAR2(64), 4 time_waited NUMBER 5 ); 6 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_system_wait_class_tab IS TABLE OF t_system_wait_class; 2 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_system_activity AS OBJECT ( 2 tstamp DATE, 3 aas NUMBER, 4 time_waited_other NUMBER, 5 time_waited_queueing NUMBER, 6 time_waited_network NUMBER, 7 time_waited_administrative NUMBER, 8 time_waited_configuration NUMBER, 9 time_waited_commit NUMBER, 10 time_waited_application NUMBER, 11 time_waited_concurrency NUMBER, 12 time_waited_cluster NUMBER, 13 time_waited_system_io NUMBER, 14 time_waited_user_io NUMBER, 15 time_waited_scheduler NUMBER, 16 time_cpu NUMBER 17 ); 18 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_system_activity_tab IS TABLE OF t_system_activity; 2 / SYS@PROD1> SYS@PROD1> CREATE FUNCTION system_activity(p_interval IN NUMBER DEFAULT 15, -- wait 15s between two snapshots 2 p_count IN NUMBER DEFAULT 1) -- take 1 snapshot 3 RETURN t_system_activity_tab 4 PIPELINED 5 AS 6 l_snap1 t_system_wait_class_tab; 7 l_snap2 t_system_wait_class_tab; 8 l_cpu1 NUMBER; 9 l_cpu2 NUMBER; 10 l_tstamp DATE; 11 l_other NUMBER; 12 l_queueing NUMBER; 13 l_network NUMBER; 14 l_administrative NUMBER; 15 l_configuration NUMBER; 16 l_commit NUMBER; 17 l_application NUMBER; 18 l_concurrency NUMBER; 19 l_cluster NUMBER; 20 l_system_io NUMBER; 21 l_user_io NUMBER; 22 l_scheduler NUMBER; 23 l_total NUMBER; 24 BEGIN 25 SELECT t_system_wait_class(wait_class#, wait_class, time_waited/1E2) 26 BULK COLLECT INTO l_snap1 27 FROM v$system_wait_class 28 WHERE wait_class <> 'Idle'; 29 30 SELECT sum(value)/1E6 31 INTO l_cpu1 32 FROM v$sys_time_model 33 WHERE stat_name IN ('DB CPU','background cpu time'); 34 35 FOR i IN 1..p_count 36 LOOP 37 dbms_lock.sleep(p_interval); 38 39 SELECT t_system_wait_class(wait_class#, wait_class, time_waited/1E2) 40 BULK COLLECT INTO l_snap2 41 FROM v$system_wait_class 42 WHERE wait_class <> 'Idle'; 43 44 SELECT sum(value)/1E6, sysdate 45 INTO l_cpu2, l_tstamp 46 FROM v$sys_time_model 47 WHERE stat_name IN ('DB CPU','background cpu time'); 48 49 l_other := 0; 50 l_queueing := 0; 51 l_network := 0; 52 l_administrative := 0; 53 l_configuration := 0; 54 l_commit := 0; 55 l_application := 0; 56 l_concurrency := 0; 57 l_cluster := 0; 58 l_system_io := 0; 59 l_user_io := 0; 60 l_scheduler := 0; 61 l_total := 0; 62 63 FOR r IN (SELECT snap1.wait_class, 64 snap2.time_waited-snap1.time_waited AS time_waited 65 FROM table(l_snap1) snap1, 66 table(l_snap2) snap2 67 WHERE snap1.wait_class# = snap2.wait_class#) 68 LOOP 69 CASE r.wait_class 70 WHEN 'Other' THEN l_other := r.time_waited; 71 WHEN 'Queueing' THEN l_queueing := r.time_waited; 72 WHEN 'Network' THEN l_network := r.time_waited; 73 WHEN 'Administrative' THEN l_administrative := r.time_waited; 74 WHEN 'Configuration' THEN l_configuration := r.time_waited; 75 WHEN 'Commit' THEN l_commit := r.time_waited; 76 WHEN 'Application' THEN l_application := r.time_waited; 77 WHEN 'Concurrency' THEN l_concurrency := r.time_waited; 78 WHEN 'Cluster' THEN l_cluster := r.time_waited; 79 WHEN 'System I/O' THEN l_system_io := r.time_waited; 80 WHEN 'User I/O' THEN l_user_io := r.time_waited; 81 WHEN 'Scheduler' THEN l_scheduler := r.time_waited; 82 END CASE; 83 l_total := l_total + r.time_waited; 84 END LOOP; 85 l_total := l_total + (l_cpu2 - l_cpu1); 86 l_total := nullif(l_total,0); -- avoid ORA-01476: divisor is equal to zero 87 PIPE ROW(t_system_activity(l_tstamp, 88 l_total/nullif(p_interval,0), 89 l_other/l_total*100, 90 l_queueing/l_total*100, 91 l_network/l_total*100, 92 l_administrative/l_total*100, 93 l_configuration/l_total*100, 94 l_commit/l_total*100, 95 l_application/l_total*100, 96 l_concurrency/l_total*100, 97 l_cluster/l_total*100, 98 l_system_io/l_total*100, 99 l_user_io/l_total*100, 100 l_scheduler/l_total*100, 101 (l_cpu2 - l_cpu1)/l_total*100)); 102 l_snap1 := l_snap2; 103 l_cpu1 := l_cpu2; 104 END LOOP; 105 RETURN; 106 END system_activity; 107 / SYS@PROD1> SYS@PROD1> SHOW ERROR No errors. SYS@PROD1> SYS@PROD1> CREATE PUBLIC SYNONYM system_activity FOR system_activity; SYS@PROD1> SYS@PROD1> GRANT EXECUTE ON system_activity TO PUBLIC; SYS@PROD1> start system_activity.sql SYS@PROD1> SET ECHO OFF Enter value for 1: 15 Enter value for 2: 20 PROD1 / 2017-03-08 Time AvgActSess Other% Queue% Net% Adm% Conf% Comm% Appl% Conc% Clust% SysIO% UsrIO% Sched% CPU% -------- ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ 16:30:08 0.0 0.0 0.0 0.0 0.0 0.0 5.4 0.0 37.6 0.0 5.4 0.0 0.0 51.6 16:30:23 0.1 0.0 0.0 0.0 0.0 16.8 0.0 0.0 0.0 0.0 41.3 39.9 0.0 2.0 16:30:38 2.3 0.0 0.0 0.0 0.0 24.5 0.0 0.0 0.0 0.0 53.3 17.1 0.0 5.1 16:30:53 3.4 0.0 0.0 0.0 0.0 24.4 0.0 0.0 0.0 0.0 66.1 6.5 0.0 3.0 16:31:09 2.8 1.6 0.0 0.0 0.0 24.8 0.0 0.0 0.0 0.0 65.4 5.2 0.0 3.0 16:31:29 1.9 0.0 0.0 0.0 0.0 19.2 0.5 0.0 8.5 0.0 36.5 29.8 0.0 5.4 16:31:49 4.0 3.2 0.0 0.0 0.0 24.9 0.0 0.0 0.0 0.0 57.5 11.9 0.0 2.6 16:32:09 3.7 0.0 0.0 0.0 0.0 17.8 0.0 0.0 0.0 0.0 69.8 9.1 0.0 3.2 16:32:28 4.0 0.0 0.0 0.0 0.0 19.0 0.4 0.0 0.1 0.0 62.4 15.4 0.0 2.7 16:32:43 2.8 0.0 0.0 0.0 0.0 17.5 0.0 0.0 0.0 0.0 67.6 12.2 0.0 2.7 16:33:09 3.0 0.0 0.0 0.0 0.0 14.4 0.0 0.0 22.6 0.0 48.7 10.6 0.0 3.8 16:33:27 3.7 0.0 0.0 0.0 0.0 21.0 1.2 0.0 0.0 0.0 61.3 13.5 0.0 2.9 16:33:44 3.4 0.0 0.0 0.0 0.0 24.8 0.0 0.0 0.0 0.0 62.3 9.2 0.0 3.6 16:33:59 3.1 0.0 0.0 0.0 0.0 24.7 0.0 0.0 0.0 0.0 62.7 9.5 0.0 3.2 16:34:14 3.0 0.0 0.0 0.0 0.0 21.9 0.0 0.0 0.0 0.0 63.0 11.7 0.0 3.4 16:34:32 3.6 0.0 0.0 0.0 0.0 19.7 0.5 0.0 0.1 0.0 61.7 14.3 0.0 3.7 16:34:47 4.2 25.8 0.0 0.0 0.0 15.1 0.0 0.0 0.0 0.0 45.8 11.2 0.0 2.1 16:35:05 4.0 0.4 0.0 0.0 0.0 22.4 0.0 0.0 0.0 0.0 67.3 8.1 0.0 1.9 16:35:25 4.1 0.0 0.0 0.0 0.0 23.2 0.4 0.0 0.1 0.0 57.4 15.6 0.0 3.4 16:35:43 4.7 8.0 0.0 0.0 0.0 19.2 0.0 0.0 0.0 0.0 62.2 8.4 0.0 2.1 --实验2. SYS@PROD1> start time_model_setup.sql SYS@PROD1> SYS@PROD1> @@time_model_teardown.sql SYS@PROD1> SET ECHO OFF SYS@PROD1> SYS@PROD1> DROP TYPE t_time_model_tab; DROP TYPE t_time_model_tab * ERROR at line 1: ORA-04043: object T_TIME_MODEL_TAB does not exist SYS@PROD1> DROP TYPE t_time_model; DROP TYPE t_time_model * ERROR at line 1: ORA-04043: object T_TIME_MODEL does not exist SYS@PROD1> DROP TYPE t_sys_time_model_tab; DROP TYPE t_sys_time_model_tab * ERROR at line 1: ORA-04043: object T_SYS_TIME_MODEL_TAB does not exist SYS@PROD1> DROP TYPE t_sys_time_model; DROP TYPE t_sys_time_model * ERROR at line 1: ORA-04043: object T_SYS_TIME_MODEL does not exist SYS@PROD1> SYS@PROD1> DROP FUNCTION time_model; DROP FUNCTION time_model * ERROR at line 1: ORA-04043: object TIME_MODEL does not exist SYS@PROD1> SYS@PROD1> DROP PUBLIC SYNONYM time_model; DROP PUBLIC SYNONYM time_model * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist SYS@PROD1> SYS@PROD1> CREATE TYPE t_sys_time_model AS OBJECT (stat_name VARCHAR2(64), value NUMBER); 2 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_sys_time_model_tab IS TABLE OF t_sys_time_model; 2 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_time_model AS OBJECT (tstamp DATE, stat_name VARCHAR2(64), aas NUMBER, activity NUMBER); 2 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_time_model_tab IS TABLE OF t_time_model; 2 / SYS@PROD1> SYS@PROD1> CREATE FUNCTION time_model(p_interval IN NUMBER DEFAULT 15, -- wait 15s between two snapshots 2 p_count IN NUMBER DEFAULT 1) -- take 1 snapshot 3 RETURN t_time_model_tab 4 PIPELINED 5 AS 6 l_snap1 t_sys_time_model_tab; 7 l_snap2 t_sys_time_model_tab; 8 l_tstamp DATE; 9 l_tot_db_time NUMBER; 10 BEGIN 11 SELECT t_sys_time_model(stat_name, value*1E-6) BULK COLLECT 12 INTO l_snap1 13 FROM v$sys_time_model; 14 15 FOR i IN 1..p_count 16 LOOP 17 dbms_lock.sleep(p_interval); 18 19 SELECT t_sys_time_model(stat_name, value*1E-6) BULK COLLECT 20 INTO l_snap2 21 FROM v$sys_time_model; 22 23 SELECT sum(snap2.value-snap1.value), sysdate 24 INTO l_tot_db_time, l_tstamp 25 FROM table(l_snap1) snap1 26 JOIN table(l_snap2) snap2 ON snap1.stat_name = snap2.stat_name 27 WHERE snap1.stat_name IN ('DB time','background elapsed time'); 28 29 FOR r IN (SELECT rpad('.',level-1,'.')||stat_name AS stat_name, 30 aas, 31 activity 32 FROM (SELECT snap1.stat_name AS stat_name, 33 (snap2.value-snap1.value)/nullif(p_interval,0) AS aas, 34 (snap2.value-snap1.value)/nullif(l_tot_db_time,0)*100 AS activity, 35 decode(snap1.stat_name, 36 'background elapsed time', 101, 37 'background cpu time', 102, 38 'RMAN cpu time (backup/restore', 103, 39 'DB time', 4, 40 'DB CPU', 5, 41 'connection management call elapsed time', 6, 42 'sequence load elapsed time', 7, 43 'sql execute elapsed time', 8, 44 'parse time elapsed', 9, 45 'hard parse elapsed time', 10, 46 'hard parse (sharing criteria) elapsed time', 11, 47 'hard parse (bind mismatch) elapsed time', 12, 48 'failed parse elapsed time', 13, 49 'failed parse (out of shared memory) elapsed time', 14, 50 'PL/SQL execution elapsed time', 15, 51 'inbound PL/SQL rpc elapsed time', 16, 52 'PL/SQL compilation elapsed time', 17, 53 'Java execution elapsed time', 18, 54 'repeated bind elapsed time', 19) AS id, 55 decode(snap1.stat_name, 56 'background elapsed time', NULL, 57 'background cpu time', 101, 58 'RMAN cpu time (backup/restore)', 102, 59 'DB time', NULL, 60 'DB CPU', 4, 61 'connection management call elapsed time', 4, 62 'sequence load elapsed time', 4, 63 'sql execute elapsed time', 4, 64 'parse time elapsed', 4, 65 'hard parse elapsed time', 9, 66 'hard parse (sharing criteria) elapsed time', 10, 67 'hard parse (bind mismatch) elapsed time', 11, 68 'failed parse elapsed time', 9, 69 'failed parse (out of shared memory) elapsed time', 13, 70 'PL/SQL execution elapsed time', 4, 71 'inbound PL/SQL rpc elapsed time', 4, 72 'PL/SQL compilation elapsed time', 4, 73 'Java execution elapsed time', 4, 74 'repeated bind elapsed time', 4) AS parent_id 75 FROM table(l_snap1) snap1, 76 table(l_snap2) snap2 77 WHERE snap1.stat_name = snap2.stat_name) 78 CONNECT BY parent_id = PRIOR id 79 START WITH parent_id IS NULL 80 ORDER SIBLINGS BY id) 81 LOOP 82 PIPE ROW(t_time_model(l_tstamp, r.stat_name, r.aas, r.activity)); 83 END LOOP; 84 l_snap1 := l_snap2; 85 END LOOP; 86 END time_model; 87 / SYS@PROD1> SYS@PROD1> SHOW ERROR No errors. SYS@PROD1> SYS@PROD1> CREATE PUBLIC SYNONYM time_model FOR time_model; SYS@PROD1> SYS@PROD1> GRANT EXECUTE ON time_model TO PUBLIC; SYS@PROD1> start time_model.sql Enter value for 1: 15 Enter value for 2: 2 PROD1 / 2017-03-08 Time Statistic AvgActSess Activity% -------- -------------------------------------------------- ---------- --------- 16:53:55 DB time 1.4 28.0 .DB CPU 0.1 1.4 .sql execute elapsed time 1.3 27.4 background elapsed time 3.5 72.0 .background cpu time 0.1 1.2 PROD1 / 2017-03-08 Time Statistic AvgActSess Activity% -------- -------------------------------------------------- ---------- --------- 16:54:10 DB time 0.9 29.3 .DB CPU 0.1 2.1 .sql execute elapsed time 0.9 29.3 background elapsed time 2.1 70.7 .background cpu time 0.0 1.2 --实验3. SYS@PROD1> start active_sessions_setup.sql SYS@PROD1> SYS@PROD1> @@active_sessions_teardown.sql SYS@PROD1> SET ECHO OFF SYS@PROD1> SYS@PROD1> DROP TYPE t_active_session_tab; DROP TYPE t_active_session_tab * ERROR at line 1: ORA-04043: object T_ACTIVE_SESSION_TAB does not exist SYS@PROD1> DROP TYPE t_active_session; DROP TYPE t_active_session * ERROR at line 1: ORA-04043: object T_ACTIVE_SESSION does not exist SYS@PROD1> DROP TYPE t_sess_time_model_tab; DROP TYPE t_sess_time_model_tab * ERROR at line 1: ORA-04043: object T_SESS_TIME_MODEL_TAB does not exist SYS@PROD1> DROP TYPE t_sess_time_model; DROP TYPE t_sess_time_model * ERROR at line 1: ORA-04043: object T_SESS_TIME_MODEL does not exist SYS@PROD1> SYS@PROD1> DROP FUNCTION active_sessions; DROP FUNCTION active_sessions * ERROR at line 1: ORA-04043: object ACTIVE_SESSIONS does not exist SYS@PROD1> SYS@PROD1> DROP PUBLIC SYNONYM active_sessions; DROP PUBLIC SYNONYM active_sessions * ERROR at line 1: ORA-01432: public synonym to be dropped does not exist SYS@PROD1> SYS@PROD1> CREATE TYPE t_sess_time_model AS OBJECT (sid NUMBER, value NUMBER); 2 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_sess_time_model_tab IS TABLE OF t_sess_time_model; 2 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_active_session AS OBJECT (tstamp DATE, sessions NUMBER, logins NUMBER, sid VARCHAR2(30), username VARCHAR2(30), program VARCHAR2(48), activity NUMBER); 2 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_active_session_tab IS TABLE OF t_active_session; 2 / SYS@PROD1> SYS@PROD1> CREATE FUNCTION active_sessions(p_interval IN NUMBER DEFAULT 15, -- wait 15s between two snapshots 2 p_count IN NUMBER DEFAULT 1, -- take 1 snapshot 3 p_top IN NUMBER DEFAULT 10) -- show top-10 sessions only 4 RETURN t_active_session_tab 5 PIPELINED 6 AS 7 l_snap1 t_sess_time_model_tab; 8 l_snap2 t_sess_time_model_tab; 9 l_tstamp DATE; 10 l_tot_db_time1 NUMBER; 11 l_tot_db_time2 NUMBER; 12 l_tot_db_time NUMBER; 13 l_logons_cum1 NUMBER; 14 l_logons_cum2 NUMBER; 15 l_logons_cur NUMBER; 16 BEGIN 17 SELECT value 18 INTO l_logons_cum1 19 FROM v$sysstat 20 WHERE name = 'logons cumulative'; 21 22 SELECT t_sess_time_model(sid, sum(value)*1E-6) 23 BULK COLLECT INTO l_snap1 24 FROM v$sess_time_model 25 WHERE stat_name IN ('DB time','background elapsed time') 26 GROUP BY sid; 27 28 SELECT sum(value)*1E-6 29 INTO l_tot_db_time1 30 FROM v$sys_time_model 31 WHERE stat_name IN ('DB time','background elapsed time'); 32 33 FOR i IN 1..p_count 34 LOOP 35 dbms_lock.sleep(p_interval); 36 37 SELECT value 38 INTO l_logons_cur 39 FROM v$sysstat 40 WHERE name = 'logons current'; 41 42 SELECT value 43 INTO l_logons_cum2 44 FROM v$sysstat 45 WHERE name = 'logons cumulative'; 46 47 SELECT t_sess_time_model(sid, sum(value)*1E-6) 48 BULK COLLECT INTO l_snap2 49 FROM v$sess_time_model 50 WHERE stat_name IN ('DB time','background elapsed time') 51 GROUP BY sid; 52 53 SELECT sum(value)*1E-6, sysdate 54 INTO l_tot_db_time2, l_tstamp 55 FROM v$sys_time_model 56 WHERE stat_name IN ('DB time','background elapsed time'); 57 58 l_tot_db_time := l_tot_db_time2-l_tot_db_time1; 59 FOR r IN (SELECT stm.sid AS sid, 60 s.username AS username, 61 decode(s.type,'BACKGROUND',substr(program,instr(program,'(')+1,4),program) AS program, 62 sum(stm.db_time) AS db_time, 63 sum(stm.activity) AS activity 64 FROM (SELECT sid, 65 db_time, 66 db_time/nullif(l_tot_db_time,0)*100 AS activity 67 FROM (WITH 68 active_sessions AS ( 69 SELECT snap2.sid, 70 snap2.value-nvl(snap1.value,0) AS db_time 71 FROM table(l_snap1) snap1 72 RIGHT OUTER JOIN table(l_snap2) snap2 ON snap1.sid = snap2.sid 73 ) 74 /* active sessions */ 75 SELECT to_char(sid) AS sid, 76 db_time 77 FROM active_sessions 78 UNION ALL 79 /* closed sessions */ 80 SELECT 'Unknown' AS sid, 81 l_tot_db_time-sum(db_time) AS db_time 82 FROM active_sessions) 83 ORDER BY db_time DESC) stm 84 LEFT OUTER JOIN v$session s ON to_char(s.sid) = stm.sid 85 WHERE rownum <= p_top 86 GROUP BY rollup((stm.sid, s.type, s.program, s.username)) 87 ORDER BY grouping(sid), 88 db_time DESC) 89 LOOP 90 PIPE ROW(t_active_session(l_tstamp, 91 l_logons_cur, 92 l_logons_cum2-l_logons_cum1, 93 nvl(r.sid,'Top-'||p_top||' Total'), 94 r.username, 95 r.program, 96 r.activity)); 97 END LOOP; 98 l_logons_cum1 := l_logons_cum2; 99 l_snap1 := l_snap2; 100 l_tot_db_time1 := l_tot_db_time2; 101 END LOOP; 102 RETURN; 103 END active_sessions; 104 / SYS@PROD1> SYS@PROD1> SHOW ERROR No errors. SYS@PROD1> SYS@PROD1> CREATE PUBLIC SYNONYM active_sessions FOR active_sessions; SYS@PROD1> SYS@PROD1> GRANT EXECUTE ON active_sessions TO PUBLIC; SYS@PROD1> start active_sessions.sql 15 1 10 SYS@PROD1> SET ECHO OFF PROD1 / 2017-03-08 Time #Sessions #Logins SessionId Username Program Activity% -------- --------- ------- --------------- -------------------- ---------------- --------- 16:57:23 34 3 55 EODA sqlplus@ocm1 (TN 29.5 10 DBW0 22.9 31 W002 21.3 11 LGWR 20.1 12 CKPT 3.1 1 SYS sqlplus@ocm1 (TN 1.8 Unknown 0.8 18 RVWR 0.2 3 PSP0 0.2 37 CJQ0 0.1 Top-10 Total 100.0 --实验4. Snapper脚本较为复杂，此处简单介绍。 第一个参数指定需要取样的动态性能视图。 第二个参数指定取样周期，单位为秒。 第三个参数指定样本数量。 第四个参数指定取样的会话。 --详细说明参照作者Tanel Poder开发的Snapper的脚本头。 SYS@PROD1> start snapper.sql ash=sql_id 15 1 all Sampling SID all with interval 15 seconds, taking 1 snapshots... -- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) ------------------------------------ ActSes %Thread | SQL_ID ------------------------------------ 1.01 (101%) | 1.00 (100%) | aq8yqxyyb40nn 1.00 (100%) | 29wd81h7rja03 1.00 (100%) | 15rbgh4d2ku4u -- End of ASH snap 1, end=2017-03-08 17:01:25, seconds=15, samples_taken=93, AAS=4 SYS@PROD1> start snapper.sql ash=module+action 15 1 all Sampling SID all with interval 15 seconds, taking 1 snapshots... -- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) -------------------------------------------------------------------------- ActSes %Thread | MODULE | ACTION -------------------------------------------------------------------------- 2.01 (201%) | | 1.00 (100%) | MMON_SLAVE | Auto-Flush Slave Action 1.00 (100%) | SQL*Plus | -- End of ASH snap 1, end=2017-03-08 17:02:06, seconds=15, samples_taken=93, AAS=4
