監控使用高cpu的sql語句指令碼

1，在linux 加入如下指令碼
[oraprod@ebsdb ~]$ crontab -l
#*/5 * * * * sh /home/oraprod/os_status.sh > /home/oraprod/os_status.log
[oraprod@ebsdb ~]$ cat /home/oraprod/os_status.sh
#!/bin/sh
#add date:2013/07/02
. /ebs10/oracle/prod/db/tech_st/11.1.0/prod_ebsdb.env;
path="/home/oraprod/os_status";
rm -rf /home/oraprod/os_status/os_*;
ps -e -o pcpu,pmem,pid,time,user,tty,args|sort -n -k 1 -r|grep oracle |grep LOCAL|head -20 > $path/os_s00.txt;
cat $path/os_s00.txt |awk '{print $1}'|sed s/^/,\'\/g >  $path/os_s11.txt;
cat $path/os_s00.txt |awk '{print $2}'|sed s/^/\',\'\/g >  $path/os_s12.txt;
cat $path/os_s00.txt |awk '{print $3}'|sed s/^/\',\'\/g > $path/os_s13.txt;
cat $path/os_s00.txt |awk '{print $4}'|sed s/^/\',\'\/g > $path/os_s14.txt;
cat $path/os_s00.txt |awk '{print $5}'|sed s/^/\',\'\/g > $path/os_s15.txt;

cat $path/os_s11.txt|sed s/^/insert\ into\ system.CHECK_OSSESSION\ values\(\'192\.168\.0\.6\'/g > $path/os_s21.txt;
paste  $path/os_s21.txt  $path/os_s12.txt > $path/os_s22.txt;
paste  $path/os_s22.txt  $path/os_s13.txt > $path/os_s23.txt;
paste  $path/os_s23.txt  $path/os_s14.txt  > $path/os_s24.txt;
paste  $path/os_s24.txt  $path/os_s15.txt |sed s/$/\'\)\;\/g > $path/os_s25.txt;
cat    $path/os_s25.txt      >> $path/os_checksession.sql
echo 'exit' >> $path/os_checksession.sql;
#sqlplus oscheck/checkdb@checkdb <sqlplus / as sysdba  <@/home/oraprod/os_status/os_checksession.sql;
2,在資料庫中加入job
begin
  sys.dbms_job.submit(job => :job,
                      what => 'INSERT INTO system.CHECK_os_status
  (HOST,
  v_spid,
    program,
   process,
   sql_text,
   OSCPU,
   os_time,
   OSMEM,
   v_sid,
   v_serial,
   createtime)
  select c.host,
           v$process.spid,
       v$session.program,
          process,
        substr(sql_text,1,100),
         c.oscpu,
         c.os_time,
         c.osmem,
         v$session.sid,
         v$session.serial#,sysdate
    from v$sqlarea,
         v$session,
         v$process,
         (select to_number(decode(length(rtrim(a.os_pid)),
                                  6,
                                  substr(trim(a.os_pid), 1, 5),
                                  5,
                                  substr(trim(a.os_pid), 1, 4))) ospid,
                 a.host,
                 a.oscpu,
                 a.os_time,
                 a.osmem
            from system.CHECK_OSSESSION a) c
   where v$sqlarea.address = v$session.sql_address
     and v$sqlarea.hash_value = v$session.sql_hash_value
     and v$session.paddr = v$process.addr
     and v$process.spid = c.ospid;
delete system.CHECK_OSSESSION ;',
                      next_date => to_date('03-07-2013 16:09:10', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'SYSDATE + (10/(24*60))');
  commit;
end;
/

表：

create table CHECK_OS_STATUS
(
  HOST       VARCHAR2(20),
  V_SPID     NUMBER,
  PROGRAM    VARCHAR2(50),
  PROCESS    VARCHAR2(30),
  SQL_TEXT   VARCHAR2(1000),
  OSCPU      VARCHAR2(30),
  OS_TIME    VARCHAR2(30),
  OSMEM      VARCHAR2(30),
  V_SID      NUMBER,
  V_SERIAL   NUMBER,
  CREATETIME DATE
)

create table CHECK_OSSESSION
(
  HOST    VARCHAR2(20),
  OSCPU   VARCHAR2(30),
  OSMEM   VARCHAR2(30),
  OS_PID  VARCHAR2(8),
  OS_TIME VARCHAR2(30),
  OSUSER  VARCHAR2(30)
)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15187685/viewspace-765427/，如需轉載，請註明出處，否則將追究法律責任。