Cpu 유저,시스템,IDLE 별 사용률 조회 프로시져 문서 원본 보기

<source lang=sql>
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
);
/
</source>


<source lang=sql>
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;
/
</source>

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):
<source lang=sql>
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
...
</source>

And here is the sar(1) output for the very same period of time:
<source lang=sql>
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
...
</source>

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.