create or replace directory TEMP_DIR as '/tmp';
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
declare
v_cpu_count number := 16;
v_warn_value number := 10;
v_monitor_interval number := 5;
v_loop_count number :=10;
v_total_waits number;
b_total_waits number;
e_total_waits number;
total_waits number;
v_cpu_session number;
b_cpu_session number;
e_cpu_session number;
cpu_session number;
v_transaction number;
b_transaction number;
e_transaction number;
transaction number;
v_shijian date;
b_shijian date;
e_shijian date;
sid_monitor number;
filemonitor    UTL_FILE.file_type;
filelog    UTL_FILE.file_type;
cpu_response_time number;
begin

select sid into sid_monitor from v$mystat where rownum<2;
select sum(total_waits) into b_total_waits from v$system_event;
select value into b_cpu_session from v$sysstat where name='CPU used by this session';
select sum(value) into b_transaction from v$sysstat where name in('user commits','user rollbacks');

dbms_lock.sleep(v_monitor_interval);

select sum(total_waits) into e_total_waits from v$system_event;
select value into e_cpu_session from v$sysstat where name='CPU used by this session';
select sum(value) into e_transaction from v$sysstat where name in('user commits','user rollbacks');
select sysdate  into e_shijian from dual;

total_waits := e_total_waits - b_total_waits;
cpu_session := e_cpu_session - b_cpu_session;
transaction := e_transaction - b_transaction;

if transaction = 0 then
cpu_response_time := 0||'       ';
else
--- /100 to transform. csecond to second

cpu_response_time := (total_waits + cpu_session) / transaction / v_cpu_count /100 ;

end if;

--begin write 2 file,one is log and another is ues to be monitored

filemonitor := UTL_FILE.fopen ('TEMP_DIR', 'monitor', 'a');
filelog := UTL_FILE.fopen ('TEMP_DIR', 'log.csv', 'a');
      UTL_FILE.put_line (filelog,cpu_response_time||','||transaction||','||e_shijian||',sid_'||sid_monitor);

---define the rule of WARING

      if cpu_response_time <= v_warn_value then
      UTL_FILE.put_line (filemonitor,cpu_response_time||'  second   '||e_shijian);
      else
      UTL_FILE.put_line (filemonitor,cpu_response_time||'  second ,'||e_shijian||'  WARING!!!!!,please contact the DBA');
      UTL_FILE.put_line (filemonitor,cpu_response_time||'  second ,'||e_shijian||'  WARING!!!!!,please contact the DBA');
      end if;
      UTL_FILE.fclose (filemonitor);
      UTL_FILE.fclose (filelog);

---begin loop
for del_count in 1..v_loop_count loop
b_total_waits := e_total_waits;
b_cpu_session := e_cpu_session;
b_transaction := e_transaction;

dbms_lock.sleep(v_monitor_interval);

select sum(total_waits) into e_total_waits from v$system_event;
select value into e_cpu_session from v$sysstat where name='CPU used by this session';
select sum(value) into e_transaction from v$sysstat where name in('user commits','user rollbacks');
select sysdate  into e_shijian from dual;

total_waits := e_total_waits - b_total_waits;
cpu_session := e_cpu_session - b_cpu_session;
transaction := e_transaction - b_transaction;

--begin compute the cpu response time per transaction in this interval

if transaction = 0 then
cpu_response_time := 0;
else
cpu_response_time := (total_waits + cpu_session) / transaction / v_cpu_count /100;
end if;

--write log

filemonitor := UTL_FILE.fopen ('TEMP_DIR', 'monitor', 'a');
filelog := UTL_FILE.fopen ('TEMP_DIR', 'log.csv', 'a');
UTL_FILE.put_line (filelog,cpu_response_time||','||transaction||','||e_shijian||',sid_'||sid_monitor);     
      if cpu_response_time <= v_warn_value  then
      UTL_FILE.put_line (filemonitor,cpu_response_time||'  second '||e_shijian);
      else
      UTL_FILE.put_line (filemonitor,cpu_response_time||'  second ,'||e_shijian||'  WARING!!!!!,please contact the DBA');
      UTL_FILE.put_line (filemonitor,cpu_response_time||'  second ,'||e_shijian||'  WARING!!!!!,please contact the DBA');
      end if;
      UTL_FILE.fclose (filemonitor);
      UTL_FILE.fclose (filelog);
end loop;
end;
/