因为系统执行请求太多，cpu经常卡，但是不知道哪个请求卡了cpu。

所以想做个界面展示所有请求执行情况，包括执行时间和占用cpu。

执行时间可以用当前时间减去提交时间获得，但是cpu的准确数据在服务器上面，EBS本身没有。

所有考虑从数据库读取cpu情况进行追溯。

主要思路如下：

        1. linux定时执行脚本，将cpu使用率写入到txt文件；

2. 数据库程序读取txt文件，写入到table中进行记录；

具体如下：

1. DBA在linux建立sh文件如下

#!/bin/bash
.  /etc/profile
. /home/oraprod/.bash_profile
/usr/bin/top -bn  1  -i -c |grep oracle|awk '{ if ($9 > 70) print $1,$9}' |awk '{print  > "/data/prod/temp/topcpupid.txt"}'

2. DBA使用corntab建立定时任务（每分钟执行，每次执行60次，这样可以达到秒级记录）

#!/bin/bash
.  /etc/profile
. /home/oraprod/.bash_profile

step=1
for (( i = 0; i < 60; i = (i+step) )); do
     /home/oraprod/topcpupid.sh
    sleep $step
done

exit 0

3. 数据库代码获取cpu使用率

Utl_File.Fopen和Utl_File.Get_Line得到txt内容

--建立数据库object
CREATE OR REPLACE TYPE xxtopcpu AS OBJECT (pid NUMBER,cpuusage NUMBER);
CREATE OR REPLACE TYPE xxtopcpu_table AS TABLE OF xxtopcpu;

--建立路径--注意：不同环境服务器路径会不同，需要DBA去人
CREATE OR REPLACE DIRECTORY XXCPUREAD AS '/data/dev/temp/';

--在程序包读取文件返回object
--该程序会按照路径读取指定txt文档，整理数据插入到object，返回对象列表
  FUNCTION Get_Cpuusage RETURN Xxtopcpu_Table IS
    Handle       Utl_File.File_Type;
    v_Text       VARCHAR2(4000);
    Lv_Fiel_Name VARCHAR2(2000) := 'topcpupid.txt'; --'topcpupid.txt';
    Lv_Dir_Name  VARCHAR2(100) := 'XXCPUREAD'; --'/data/dev/temp/'
    Xxtopcpu_Rec Xxtopcpu := Xxtopcpu(NULL, NULL);
    Xxtopcpu_Tbl Xxtopcpu_Table := Xxtopcpu_Table();
    Ln_Index     NUMBER := 0;
  BEGIN
    --Write_Cpuusage;
    Handle := Utl_File.Fopen(Lv_Dir_Name, Lv_Fiel_Name, 'r');
    LOOP
      BEGIN
        Utl_File.Get_Line(Handle, v_Text);
        Dbms_Output.Put_Line(v_Text);
        SELECT Substr(v_Text, 1, Instr(v_Text, ' ') - 1)
              ,Substr(v_Text, Instr(v_Text, ' ') + 1, 10)
          INTO Xxtopcpu_Rec.Pid
              ,Xxtopcpu_Rec.Cpuusage
          FROM Dual;
        Ln_Index := Ln_Index + 1;
        Xxtopcpu_Tbl.Extend;
        Xxtopcpu_Tbl(Ln_Index) := Xxtopcpu_Rec;
      EXCEPTION
        WHEN No_Data_Found THEN
          EXIT;
        
      END;
    END LOOP;
  
    FOR i IN 1 .. Xxtopcpu_Tbl.Count LOOP
      Dbms_Output.Put_Line('pid:' || '---' || Xxtopcpu_Tbl(i).Pid);
    END LOOP;
    Utl_File.Fclose(Handle);
    RETURN Xxtopcpu_Tbl;
  
  END Get_Cpuusage;

4. 定时获取cpu使用情况，做历史记录分析

a. 建立table表

create table it_erp_r.XXCUS_CPUINFO_T
(
  group_id   NUMBER,
  query_date DATE,
  pid        NUMBER,
  cpuusage   NUMBER,
  session_id NUMBER,
  ser_id     NUMBER,
  request_id NUMBER,
  sql_id     VARCHAR2(100)
);
-- Add/modify columns 
alter table IT_ERP_R.XXCUS_CPUINFO_T add request_code varchar2(200);

-- Create sequence 
create sequence IT_ERP_R.XXCUS_CPUINFO_S
minvalue 1
maxvalue 999999999999
start with 1
increment by 1;

b. 建立job定时执行

CREATE OR REPLACE PROCEDURE it_erp_r.Xxcus_Cpuinfo_Prc IS
  Ld_Date       DATE := SYSDATE;
  Ln_Group_Id   NUMBER := It_Erp_r.Xxcus_Cpuinfo_s.Nextval;
  Ln_Request_Id NUMBER;
  lv_request_code VARCHAR2(100);
  CURSOR Cur_Loop IS
    SELECT Cputop.Pid
          ,Cputop.Cpuusage
          ,Ses.Sid
          ,Ses.Serial# Ser_Id
          ,Ses.Sql_Id
      FROM TABLE(Apps.Xxcus_Error_Operation_Pkg.Get_Cpuusage) Cputop
          ,V$process Pro
          ,V$session Ses
     WHERE Pro.Spid = Cputop.Pid
       AND Ses.Paddr = Pro.Addr;
BEGIN
  FOR i IN Cur_Loop LOOP
    BEGIN
      SELECT Fcr.Request_Id,fcr.Concurrent_Program_Name
        INTO Ln_Request_Id,lv_request_code
        FROM Fnd_Concurrent_Worker_Requests Fcr
       WHERE Fcr.Oracle_Process_Id = i.Pid;
    EXCEPTION
      WHEN OTHERS THEN
        Ln_Request_Id := NULL;
        lv_request_code := NULL;
    END;
    INSERT INTO It_Erp_r.Xxcus_Cpuinfo_t
      (Group_Id
      ,Query_Date
      ,Pid
      ,Cpuusage
      ,Session_Id
      ,Ser_Id
      ,Request_Id
      ,Sql_Id
      ,request_code)
    VALUES
      (Ln_Group_Id
      ,Ld_Date
      ,i.Pid
      ,i.Cpuusage
      ,i.Sid
      ,i.Ser_Id
      ,Ln_Request_Id
      ,i.Sql_Id
      ,lv_request_code);
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
  
END Xxcus_Cpuinfo_Prc;

创建定时job

begin
  sys.dbms_scheduler.create_job(job_name            => 'XXCUPINFO',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'it_erp_r.Xxcus_Cpuinfo_Prc',
                                start_date          => to_date('09-03-2022 17:55:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=SECONDLY;Interval=30',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '获取cpu使用率信息');
end;
/

创建job的说明这里说的比较清楚：

sys.dbms_scheduler.create_job创建定时任务_傲骨雄风的博客-CSDN博客_sys.dbms_scheduler