因为系统执行请求太多,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