ORACLE数据库SQL执行语句分析

主要用到三张表 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这张表中了，具体用到了那些还需要再调整。