主要用到三张表 DBA_HIST_SQLSTAT,DBA_HIST_SNAPSHOT,DBA_HIST_SQLTEXT
select sql_id,sql_text from DBA_HIST_SQLTEXT where upper(sql_text) like '%INSERT%INTO%T_TABLE%'
此语句可以用来查询对应sql语句的id,然后根据id可以查到其他信息
SELECT ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta, 0) exe_num, --执行次数 trunc(elapsed_time_delta / 1000000) exe_time, --执行时间 trunc((elapsed_time_delta / decode(nvl(executions_delta, 0), 0, 1, executions_delta)) / 1000000) avg_exe_time, --平均执行时间 buffer_gets_delta lg_read, --逻辑读 trunc((buffer_gets_delta / decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta))) avg_lg_read --平均逻辑读 FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS WHERE --sql_id = '4dwcab9fvm7d3' --问题sql语句的sql_id值 ss.snap_id = S.snap_id AND ss.instance_number = S.instance_number AND s.instance_number = 1 AND executions_delta > 0 AND to_char(ss.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') >= '2023-04-18 10:00:00' AND to_char(ss.BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') <= '2023-04-18 11:05:05' ORDER BY NODE, BEGIN_INTERVAL_TIME;
如上所示,问题sql语句的相关信息都在DBA_HIST_SQLSTAT这张表中了,具体用到了那些还需要再调整。