oracle sql执行历史,Oracle 历史SQL执行耗时查询

这个查询首先得理解dba_hist_sqlstat视图，也就是并不是所有SQL都在dba_hist_sqlstat里都能查询：

DBA_HIST_SQLSTAT displays historical information about SQL statistics. This view captures the top SQL statements based on a set of criteria and captures the statistics information from V$SQL. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

ELAPSED_TIME_DELTA：

Delta value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching

select a.sql_text SQL语句,

b.etime 执行耗时,

c.user_id 用户ID,

c.SAMPLE_TIME 执行时间,

c.INSTANCE_NUMBER 实例数,

u.username 用户名, a.sql_id SQL编号

from dba_hist_sqltext a,

(select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime

from dba_hist_sqlstat

where ELAPSED_TIME_DELTA / 1000000 >= 1) b,

dba_hist_active_sess_history c,

dba_users u

where a.sql_id = b.sql_id

and u.username = 'MYDB'

and c.user_id = u.user_id

and b.sql_id = c.sql_id

and a.sql_text like '%XXX%'

order by SAMPLE_TIME desc,

b.etime desc;