--系统级别分析数据库实例负载
--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。
--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 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 
91
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
