--通过查询v$metric视图来获取当前度量值。
SYS@PROD1> start host_load_setup.sql SYS@PROD1> SYS@PROD1> SYS@PROD1> CREATE TYPE t_host_load AS OBJECT ( 2 begin_time DATE, 3 duration NUMBER, 4 db_fg_cpu NUMBER, 5 db_bg_cpu NUMBER, 6 non_db_cpu NUMBER, 7 os_load NUMBER, 8 num_cpu NUMBER 9 ); 10 / SYS@PROD1> SYS@PROD1> CREATE TYPE t_host_load_tab IS TABLE OF t_host_load; 2 / SYS@PROD1> SYS@PROD1> CREATE OR REPLACE FUNCTION host_load(p_count IN NUMBER DEFAULT 1) -- number of samples (1 sample per minute) 2 RETURN t_host_load_tab -- set p_count to 0 to loop forever 3 PIPELINED 4 AS 5 l_begin_time DATE; 6 l_duration NUMBER; 7 l_db_fg_cpu NUMBER; 8 l_db_bg_cpu NUMBER; 9 l_non_db_cpu NUMBER; 10 l_os_load NUMBER; 11 l_num_cpu NUMBER; 12 l_samples INTEGER := 0; 13 l_previous_time DATE := sysdate-1; 14 BEGIN 15 BEGIN 16 SELECT value INTO l_num_cpu 17 FROM v$osstat 18 WHERE stat_name = 'NUM_CPU_CORES'; 19 EXCEPTION 20 WHEN no_data_found THEN 21 SELECT value INTO l_num_cpu 22 FROM v$osstat 23 WHERE stat_name = 'NUM_CPUS'; 24 END; 25 LOOP 26 SELECT begin_time, 27 duration, 28 db_fg, 29 db_bg, 30 host - db_fg - db_bg AS non_db, 31 os_load 32 INTO l_begin_time, 33 l_duration, 34 l_db_fg_cpu, 35 l_db_bg_cpu, 36 l_non_db_cpu, 37 l_os_load 38 FROM ( 39 SELECT begin_time, 40 intsize_csec/100 AS duration, 41 sum(case when metric_name = 'Host CPU Usage Per Sec' then value/100 else 0 end) AS host, 42 sum(case when metric_name = 'CPU Usage Per Sec' then value/100 else 0 end) AS db_fg, 43 sum(case when metric_name = 'Background CPU Usage Per Sec' then value/100 else 0 end) AS db_bg, 44 sum(case when metric_name = 'Current OS Load' then value else 0 end) AS os_load 45 FROM v$metric 46 WHERE group_id = (SELECT group_id FROM v$metricgroup WHERE name = 'System Metrics Long Duration') 47 AND metric_name IN ('CPU Usage Per Sec', 48 'Background CPU Usage Per Sec', 49 'Host CPU Usage Per Sec', 50 'Current OS Load') 51 GROUP BY begin_time, intsize_csec 52 ) 53 ORDER BY begin_time; 54 IF l_previous_time < l_begin_time 55 THEN 56 PIPE ROW(t_host_load(l_begin_time, 57 l_duration, 58 l_db_fg_cpu, 59 l_db_bg_cpu, 60 l_non_db_cpu, 61 l_os_load, 62 l_num_cpu)); 63 l_samples := l_samples + 1; 64 END IF; 65 EXIT WHEN l_samples = p_count; 66 l_previous_time := l_begin_time; 67 dbms_lock.sleep(5); 68 END LOOP; 69 RETURN; 70 END host_load; 71 / SYS@PROD1> SYS@PROD1> SHOW ERROR No errors. SYS@PROD1> SYS@PROD1> CREATE PUBLIC SYNONYM host_load FOR host_load; SYS@PROD1> SYS@PROD1> GRANT EXECUTE ON host_load TO PUBLIC; SYS@PROD1> start host_load.sql 15 SYS@PROD1> SET ECHO OFF PROD1 / 2017-03-08 BEGIN_TIME DURATION DB_FG_CPU DB_BG_CPU NON_DB_CPU OS_LOAD NUM_CPU ---------- -------- --------- --------- ---------- ------- ------- 16:00:04 60.08 0.01 0.00 0.06 0.10 1 16:01:04 60.08 0.01 0.00 0.06 0.04 1 16:02:05 60.09 0.01 0.01 0.05 0.01 1 16:03:06 60.08 0.03 0.00 0.06 0.13 1 16:04:06 60.07 0.03 0.01 0.08 1.17 1 16:05:07 60.32 0.09 0.06 0.08 2.52 1 16:06:34 59.34 0.08 0.06 0.07 3.49 1 16:07:53 60.53 0.07 0.05 0.08 3.57 1 16:09:06 59.99 0.08 0.06 0.08 4.14 1 16:10:17 59.87 0.08 0.05 0.08 4.01 1 16:11:28 60.15 0.02 0.01 0.05 2.00 1 16:12:32 60.34 0.01 0.05 0.06 1.18 1 16:13:33 59.09 0.00 0.07 0.03 0.58 1 16:14:38 60.27 0.01 0.00 0.05 0.29 1 16:15:38 60.43 0.01 0.00 0.05 0.11 1
