一、创建用户并赋权
create user dbtj identified by oracle default tablespace OGGTBS;
grant connect,resource to dbtj;
grant select any dictionary to dbtj;
grant create job to dbtj;
grant manage scheduler to dbtj;二、创建存储表
1、连接到新建用户
conn dbtj/oracle
2、按大表2千万为维度，统计出大表，再根据大表找到最近一天的sql，统计其执行效率（毫秒），创建数据存储表
create table dbtj.dbtj_tab as
select to_char(b.end_interval_time, 'yyyy-mm-dd-hh24') as datet,
SQL_ID,
sum(a.executions_delta) exec_d,
sum(a.buffer_gets_delta) buffer_d,
sum(a.disk_reads_delta) disk_d,
round(sum(a.elapsed_time_delta / 1000000), 3) et_d,
round(decode(sum(a.executions_delta),
0,
sum(a.elapsed_time_delta),
sum(a.elapsed_time_delta / 1000) /
sum(a.executions_delta)),
2) et_onetime
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and a.sql_id IN
(SELECT DISTINCT (SQL_ID)
FROM GV$SQL_PLAN SP
WHERE (sp.OBJECT_OWNER, sp.OBJECT_NAME) in
(select owner, table_name
from dba_tables
where num_rows >= 20000000
and owner not in ('SYS', 'SYSTEM')))
AND B.end_interval_time >= SYSDATE - 6/24
group by to_char(b.end_interval_time, 'yyyy-mm-dd-hh24'), SQL_ID
having(sum(a.executions_delta)) > 0
order by 1;三、创建存储过程，将第二步的查询结果插入到数据表dbtj_tab
create or replace procedure dbtj_proc
as
begin
insert into dbtj.dbtj_tab
select to_char(b.end_interval_time, 'yyyy-mm-dd-hh24') as datet,
SQL_ID,
sum(a.executions_delta) exec_d,
sum(a.buffer_gets_delta) buffer_d,
sum(a.disk_reads_delta) disk_d,
round(sum(a.elapsed_time_delta / 1000000), 3) et_d,
round(decode(sum(a.executions_delta),
0,
sum(a.elapsed_time_delta),
sum(a.elapsed_time_delta / 1000) /
sum(a.executions_delta)),
2) et_onetime
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and a.sql_id IN
(SELECT DISTINCT (SQL_ID)
FROM GV$SQL_PLAN SP
WHERE (sp.OBJECT_OWNER, sp.OBJECT_NAME) in
(select owner, table_name
from dba_tables
where num_rows >= 20000000
and owner not in ('SYS', 'SYSTEM')))
AND B.end_interval_time >= SYSDATE - 6/24
group by to_char(b.end_interval_time, 'yyyy-mm-dd-hh24'), SQL_ID
having(sum(a.executions_delta)) > 0
order by 1;
commit;
end;四、创建定时任务，每天1、7、13、19 统计
begin
dbms_scheduler.create_job(
job_name =>'dbtj_job',
job_type =>'STORED_PROCEDURE',
JOB_ACTION =>'dbtj_proc',
START_DATE =>TO_TIMESTAMP_TZ('2022-07-11 22:10:00 +8:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
REPEAT_INTERVAL =>'FREQ=DAILY;BYHOUR=01,07,13,19',
ENABLED =>TRUE);
END;五、测试
begin
dbtj_proc;
end;
begin
dbms_scheduler.enable('dbtj_job');
end;
begin
dbms_scheduler.run_job('dbtj_job');
end;
BEGIN
DBMS_SCHEDULER.DROP_JOB('dbtj_job');
END;
select * from dbtj.dbtj_tab;六、结果展示
dbtj.dbtj_tab
select datet as "时间",sql_id,exec_d as "执行次数",et_onetime as "每次耗时（毫秒）" from dbtj.dbtj_tab where datet >= to_char(sysdate-1,'yyyy-mm-dd-hh24') order by 1;
