Cpu 유저,시스템,IDLE 별 사용률 조회 프로시져
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
CREATE OR REPLACE TYPE osstat_record IS OBJECT (
date_time TIMESTAMP,
idle_time NUMBER,
user_time NUMBER,
sys_time NUMBER,
iowait_time NUMBER,
nice_time NUMBER
);
/
CREATE OR REPLACE TYPE osstat_table AS TABLE OF osstat_record;
/
CREATE OR REPLACE FUNCTION osstat(p_interval IN NUMBER, p_count IN NUMBER)
RETURN osstat_table
PIPELINED
IS
l_t1 osstat_record;
l_t2 osstat_record;
l_out osstat_record;
l_num_cpus NUMBER;
l_total NUMBER;
BEGIN
l_t1 := osstat_record(NULL, NULL, NULL, NULL, NULL, NULL);
l_t2 := osstat_record(NULL, NULL, NULL, NULL, NULL, NULL);
SELECT value
INTO l_num_cpus
FROM v$osstat
WHERE stat_name = 'NUM_CPUS';
FOR i IN 1..p_count
LOOP
SELECT sum(decode(stat_name,'IDLE_TIME', value, NULL)) as idle_time,
sum(decode(stat_name,'USER_TIME', value, NULL)) as user_time,
sum(decode(stat_name,'SYS_TIME', value, NULL)) as sys_time,
sum(decode(stat_name,'IOWAIT_TIME', value, NULL)) as iowait_time,
sum(decode(stat_name,'NICE_TIME', value, NULL)) as nice_time
INTO l_t2.idle_time, l_t2.user_time, l_t2.sys_time, l_t2.iowait_time, l_t2.nice_time
FROM v$osstat
WHERE stat_name in ('IDLE_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME');
l_out := osstat_record(systimestamp,
(l_t2.idle_time-l_t1.idle_time)/l_num_cpus/p_interval,
(l_t2.user_time-l_t1.user_time)/l_num_cpus/p_interval,
(l_t2.sys_time-l_t1.sys_time)/l_num_cpus/p_interval,
(l_t2.iowait_time-l_t1.iowait_time)/l_num_cpus/p_interval,
(l_t2.nice_time-l_t1.nice_time)/l_num_cpus/p_interval);
l_total := l_out.idle_time+l_out.user_time+l_out.sys_time+l_out.iowait_time+nvl(l_out.nice_time,0);
PIPE ROW(osstat_record(systimestamp,
l_out.idle_time/l_total*100,
l_out.user_time/l_total*100,
l_out.sys_time/l_total*100,
l_out.iowait_time/l_total*100,
l_out.nice_time/l_total*100));
l_t1 := l_t2;
dbms_lock.sleep(p_interval);
END LOOP;
RETURN;
END;
/The statistics are displayed with a query like the following one (notice that I set ARRAYSIZE to keep as short as possible the delay between the generation and the display of the statistics):
SQL> SET ARRAYSIZE 1
SQL> COLUMN user_time FORMAT 990.00
SQL> COLUMN nice_time FORMAT 990.00
SQL> COLUMN sys_time FORMAT 990.00
SQL> COLUMN iowait_time FORMAT 990.00
SQL> COLUMN idle_time FORMAT 990.00
SQL> SELECT to_char(date_time,'HH:MI:SS') as date_time, user_time, nice_time, sys_time, iowait_time, idle_time
2 FROM table(osstat(5,100));
DATE_TIM USER_TIME NICE_TIME SYS_TIME IOWAIT_TIME IDLE_TIME
-------- --------- --------- -------- ----------- ---------
12:26:11
12:26:16 0.05 0.00 0.05 0.10 99.80
12:26:21 0.76 0.00 0.05 0.66 98.52
12:26:26 0.05 0.00 0.10 0.10 99.74
12:26:31 0.15 0.00 8.03 0.31 91.50
12:26:36 0.27 0.00 21.06 15.75 62.92
12:26:41 0.10 0.00 2.57 8.13 89.21
12:26:46 0.05 0.00 0.10 0.71 99.14
12:26:51 0.10 0.00 0.05 0.41 99.44
12:26:56 24.37 0.00 0.65 3.28 71.71
12:27:01 24.50 0.00 0.97 1.27 73.26
12:27:06 24.31 0.00 1.17 1.32 73.20
12:27:11 25.05 0.00 0.66 0.82 73.47
12:27:16 25.06 0.00 0.61 0.76 73.56
12:27:21 25.13 0.00 0.56 0.46 73.85
12:27:26 24.91 0.00 0.45 1.77 72.87
12:27:31 23.97 0.00 1.41 2.17 72.46
12:27:36 24.90 0.00 0.97 0.91 73.22
12:27:41 25.18 0.00 0.51 0.36 73.95
12:27:46 25.64 0.00 0.41 0.36 73.59
12:27:51 46.37 0.05 3.48 0.45 49.65
12:27:56 46.81 0.00 3.14 0.35 49.70
12:28:01 46.63 0.00 3.34 0.20 49.83
12:28:06 45.76 0.00 4.19 0.25 49.80
12:28:11 46.58 0.00 3.40 0.15 49.88
12:28:16 46.76 0.00 3.54 0.25 49.45
12:28:21 46.06 0.00 6.74 0.25 46.96
12:28:26 43.73 0.00 6.24 0.10 49.93
12:28:31 34.87 0.00 6.98 0.30 57.84
12:28:36 29.60 0.00 5.50 0.71 64.20
12:28:41 38.40 0.00 9.60 7.69 44.32
12:28:46 39.20 0.00 9.39 6.32 45.09
12:28:51 34.73 0.00 8.37 13.81 43.09
...And here is the sar(1) output for the very same period of time:
oracle@helicon:~/ [rdbms11107] sar 5 100
Linux 2.6.9-42.ELsmp (helicon.antognini.ch) 05/01/2009
12:26:11 AM CPU %user %nice %system %iowait %idle
12:26:16 AM all 0.05 0.00 0.05 0.10 99.80
12:26:21 AM all 0.77 0.00 0.10 0.67 98.46
12:26:26 AM all 0.05 0.00 0.10 0.10 99.74
12:26:31 AM all 0.15 0.00 8.31 0.31 91.23
12:26:36 AM all 0.31 0.00 26.54 18.63 54.52
12:26:41 AM all 0.10 0.00 3.74 8.41 87.75
12:26:46 AM all 0.05 0.00 0.20 0.72 99.03
12:26:51 AM all 0.26 0.00 0.05 0.41 99.28
12:26:56 AM all 25.15 0.00 0.72 3.39 70.74
12:27:01 AM all 24.87 0.00 0.98 1.29 72.86
12:27:06 AM all 24.64 0.00 1.23 1.34 72.79
12:27:11 AM all 25.23 0.00 0.67 0.82 73.27
12:27:16 AM all 25.26 0.00 0.72 0.77 73.25
12:27:21 AM all 25.19 0.00 0.62 0.46 73.73
12:27:26 AM all 25.40 0.00 0.51 1.80 72.29
12:27:31 AM all 24.46 0.00 1.44 2.21 71.88
12:27:36 AM all 25.13 0.00 1.03 0.92 72.92
12:27:41 AM all 25.26 0.00 0.56 0.36 73.82
12:27:46 AM all 25.73 0.00 0.46 0.36 73.44
12:27:51 AM all 46.58 0.05 3.50 0.45 49.43
12:27:56 AM all 46.95 0.00 3.15 0.35 49.55
12:28:01 AM all 46.70 0.00 3.45 0.20 49.65
12:28:06 AM all 45.85 0.00 4.25 0.25 49.65
12:28:11 AM all 46.65 0.00 3.45 0.15 49.75
12:28:16 AM all 46.80 0.00 3.55 0.25 49.40
12:28:21 AM all 46.20 0.00 6.80 0.25 46.75
12:28:26 AM all 43.80 0.00 6.25 0.10 49.85
12:28:31 AM all 35.05 0.00 7.01 0.30 57.64
12:28:36 AM all 29.70 0.00 5.58 0.71 64.01
12:28:41 AM all 41.18 0.00 11.29 8.20 39.33
12:28:46 AM all 41.57 0.00 10.66 6.75 41.02
12:28:51 AM all 39.96 0.00 10.62 15.87 33.55
...As you can verify, the values provided by the pipelined function are quite good! Also note that small difference are normal because the sampling interval is quite short (5 seconds) and the two gathering methods are not synchronized.