定期自动统计大表执行情况

一、创建用户并赋权

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;