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

一、创建用户并赋权

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 asselect 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_onetimefrom dba_hist_sqlstat a, dba_hist_snapshot bwhere a.snap_id = b.snap_idand a.instance_number = b.instance_numberand a.sql_id IN(SELECT DISTINCT (SQL_ID)FROM GV$SQL_PLAN SPWHERE (sp.OBJECT_OWNER, sp.OBJECT_NAME) in(select owner, table_namefrom dba_tableswhere num_rows >= 20000000and owner not in ('SYS', 'SYSTEM')))AND B.end_interval_time >= SYSDATE - 6/24group by to_char(b.end_interval_time, 'yyyy-mm-dd-hh24'), SQL_IDhaving(sum(a.executions_delta)) > 0order by 1;

三、创建存储过程，将第二步的查询结果插入到数据表dbtj_tab

create or replace procedure dbtj_procasbegininsert into dbtj.dbtj_tabselect 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_onetimefrom dba_hist_sqlstat a, dba_hist_snapshot bwhere a.snap_id = b.snap_idand a.instance_number = b.instance_numberand a.sql_id IN(SELECT DISTINCT (SQL_ID)FROM GV$SQL_PLAN SPWHERE (sp.OBJECT_OWNER, sp.OBJECT_NAME) in(select owner, table_namefrom dba_tableswhere num_rows >= 20000000and owner not in ('SYS', 'SYSTEM')))AND B.end_interval_time >= SYSDATE - 6/24group by to_char(b.end_interval_time, 'yyyy-mm-dd-hh24'), SQL_IDhaving(sum(a.executions_delta)) > 0order by 1;commit;end;

四、创建定时任务，每天1、7、13、19 统计

begindbms_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;

五、测试

begindbtj_proc;end;begindbms_scheduler.enable('dbtj_job');end;begindbms_scheduler.run_job('dbtj_job');end;BEGINDBMS_SCHEDULER.DROP_JOB('dbtj_job');END;select * from dbtj.dbtj_tab;

六、结果展示
 

dbtj.dbtj_tabselect 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;