本文主要是介绍系统级别分析数据库实例负载，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
--系统级别分析数据库实例负载--1.通过system_activity_sql脚本对v$system_wait_class进行取样，获得平均活动会话数和花费在每个等待级别的时间。
--2.通过time_model.sql脚本对v$sys_time_model进行取样，来了解哪个引擎处理数据最多等详细信息。
--3.通过active_session.sql脚本对v$sess_time_model进行取样，来显示在给定的时间段里，top session花费了多少DB time。
--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 existSYS@PROD1> DROP TYPE t_system_activity;
DROP TYPE t_system_activity
*
ERROR at line 1:
ORA-04043: object T_SYSTEM_ACTIVITY does not existSYS@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 existSYS@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 existSYS@PROD1>
SYS@PROD1> DROP FUNCTION system_activity;
DROP FUNCTION system_activity
*
ERROR at line 1:
ORA-04043: object SYSTEM_ACTIVITY does not existSYS@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 existSYS@PROD1>
SYS@PROD1> CREATE TYPE t_system_wait_class AS OBJECT (2 wait_class# NUMBER,3 wait_class VARCHAR2(64),4 time_waited NUMBER5 );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 NUMBER17 );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 snapshots2 p_count IN NUMBER DEFAULT 1) -- take 1 snapshot3 RETURN t_system_activity_tab4 PIPELINED5 AS6 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 BEGIN25 SELECT t_system_wait_class(wait_class#, wait_class, time_waited/1E2)26 BULK COLLECT INTO l_snap127 FROM v$system_wait_class28 WHERE wait_class <> 'Idle';29 30 SELECT sum(value)/1E631 INTO l_cpu132 FROM v$sys_time_model33 WHERE stat_name IN ('DB CPU','background cpu time');34 35 FOR i IN 1..p_count36 LOOP37 dbms_lock.sleep(p_interval);38 39 SELECT t_system_wait_class(wait_class#, wait_class, time_waited/1E2)40 BULK COLLECT INTO l_snap241 FROM v$system_wait_class42 WHERE wait_class <> 'Idle';43 44 SELECT sum(value)/1E6, sysdate45 INTO l_cpu2, l_tstamp46 FROM v$sys_time_model47 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_waited65 FROM table(l_snap1) snap1,66 table(l_snap2) snap267 WHERE snap1.wait_class# = snap2.wait_class#)68 LOOP69 CASE r.wait_class70 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 zero87 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: 20PROD1 / 2017-03-08Time 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 existSYS@PROD1> DROP TYPE t_time_model;
DROP TYPE t_time_model
*
ERROR at line 1:
ORA-04043: object T_TIME_MODEL does not existSYS@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 existSYS@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 existSYS@PROD1>
SYS@PROD1> DROP FUNCTION time_model;
DROP FUNCTION time_model
*
ERROR at line 1:
ORA-04043: object TIME_MODEL does not existSYS@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 existSYS@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 snapshots2 p_count IN NUMBER DEFAULT 1) -- take 1 snapshot3 RETURN t_time_model_tab4 PIPELINED5 AS6 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 BEGIN11 SELECT t_sys_time_model(stat_name, value*1E-6) BULK COLLECT12 INTO l_snap113 FROM v$sys_time_model;14 15 FOR i IN 1..p_count16 LOOP17 dbms_lock.sleep(p_interval);18 19 SELECT t_sys_time_model(stat_name, value*1E-6) BULK COLLECT20 INTO l_snap221 FROM v$sys_time_model;22 23 SELECT sum(snap2.value-snap1.value), sysdate24 INTO l_tot_db_time, l_tstamp25 FROM table(l_snap1) snap126 JOIN table(l_snap2) snap2 ON snap1.stat_name = snap2.stat_name27 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 activity32 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_id75 FROM table(l_snap1) snap1,76 table(l_snap2) snap277 WHERE snap1.stat_name = snap2.stat_name)78 CONNECT BY parent_id = PRIOR id79 START WITH parent_id IS NULL80 ORDER SIBLINGS BY id)81 LOOP82 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: 2PROD1 / 2017-03-08Time 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.4background elapsed time 3.5 72.0.background cpu time 0.1 1.2PROD1 / 2017-03-08Time 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.3background 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 existSYS@PROD1> DROP TYPE t_active_session;
DROP TYPE t_active_session
*
ERROR at line 1:
ORA-04043: object T_ACTIVE_SESSION does not existSYS@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 existSYS@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 existSYS@PROD1>
SYS@PROD1> DROP FUNCTION active_sessions;
DROP FUNCTION active_sessions
*
ERROR at line 1:
ORA-04043: object ACTIVE_SESSIONS does not existSYS@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 existSYS@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 snapshots2 p_count IN NUMBER DEFAULT 1, -- take 1 snapshot3 p_top IN NUMBER DEFAULT 10) -- show top-10 sessions only4 RETURN t_active_session_tab5 PIPELINED6 AS7 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 BEGIN17 SELECT value18 INTO l_logons_cum119 FROM v$sysstat20 WHERE name = 'logons cumulative';21 22 SELECT t_sess_time_model(sid, sum(value)*1E-6)23 BULK COLLECT INTO l_snap124 FROM v$sess_time_model25 WHERE stat_name IN ('DB time','background elapsed time')26 GROUP BY sid;27 28 SELECT sum(value)*1E-629 INTO l_tot_db_time130 FROM v$sys_time_model31 WHERE stat_name IN ('DB time','background elapsed time');32 33 FOR i IN 1..p_count34 LOOP35 dbms_lock.sleep(p_interval);36 37 SELECT value38 INTO l_logons_cur39 FROM v$sysstat40 WHERE name = 'logons current';41 42 SELECT value43 INTO l_logons_cum244 FROM v$sysstat45 WHERE name = 'logons cumulative';46 47 SELECT t_sess_time_model(sid, sum(value)*1E-6)48 BULK COLLECT INTO l_snap249 FROM v$sess_time_model50 WHERE stat_name IN ('DB time','background elapsed time')51 GROUP BY sid;52 53 SELECT sum(value)*1E-6, sysdate54 INTO l_tot_db_time2, l_tstamp55 FROM v$sys_time_model56 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 activity64 FROM (SELECT sid,65 db_time,66 db_time/nullif(l_tot_db_time,0)*100 AS activity67 FROM (WITH68 active_sessions AS (69 SELECT snap2.sid,70 snap2.value-nvl(snap1.value,0) AS db_time71 FROM table(l_snap1) snap172 RIGHT OUTER JOIN table(l_snap2) snap2 ON snap1.sid = snap2.sid73 )74 /* active sessions */75 SELECT to_char(sid) AS sid,76 db_time77 FROM active_sessions78 UNION ALL79 /* closed sessions */80 SELECT 'Unknown' AS sid,81 l_tot_db_time-sum(db_time) AS db_time82 FROM active_sessions)83 ORDER BY db_time DESC) stm84 LEFT OUTER JOIN v$session s ON to_char(s.sid) = stm.sid85 WHERE rownum <= p_top86 GROUP BY rollup((stm.sid, s.type, s.program, s.username))87 ORDER BY grouping(sid),88 db_time DESC)89 LOOP90 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 OFFPROD1 / 2017-03-08Time #Sessions #Logins SessionId Username Program Activity%
-------- --------- ------- --------------- -------------------- ---------------- ---------
16:57:23 34 3 55 EODA sqlplus@ocm1 (TN 29.510 DBW0 22.931 W002 21.311 LGWR 20.112 CKPT 3.11 SYS sqlplus@ocm1 (TN 1.8Unknown 0.818 RVWR 0.23 PSP0 0.237 CJQ0 0.1Top-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%) | aq8yqxyyb40nn1.00 (100%) | 29wd81h7rja031.00 (100%) | 15rbgh4d2ku4u-- End of ASH snap 1, end=2017-03-08 17:01:25, seconds=15, samples_taken=93, AAS=4SYS@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 Action1.00 (100%) | SQL*Plus |-- End of ASH snap 1, end=2017-03-08 17:02:06, seconds=15, samples_taken=93, AAS=4 这篇关于系统级别分析数据库实例负载的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
