查找某段时间执行过的SQL

#gv$sql 是每个version的sql一条记录

#记得排除掉自己

SELECT module,SQL_FULLTEXT,elapsed_time,LAST_ACTIVE_TIME,EXECUTIONS  FROM gv$sql WHERE  sql_text LIKE '%select wafer_key WaferKey, UPDATE_USER UserName from defect_wafer_brm where reclassify_date%' 

AND LAST_ACTIVE_TIME > to_date('2025-01-15 00:00:01','yyyy-mm-dd hh24:mi:ss')

AND LAST_ACTIVE_TIME < to_date('2025-01-16 23:00:00','yyyy-mm-dd hh24:mi:ss')

AND module NOT LIKE '%DBeaver%'

ORDER BY LAST_ACTIVE_TIME

#alter system flush shared_pool

# alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

#elapsed_time 单位为微秒，是cputime和waittime的总和

#cpu_time就是包含解析、执行、获取数据的时间，如果cputime和elapsedtime相差较大则说明等待比较久

#gv$sql还需要除以executions以便获取平均时间

select module,sql_text,HASH_VALUE,elapsed_time/1000000 total_second,last_active_time,EXECUTIONS,ELAPSED_TIME/nvl(EXECUTIONS,1)/1000000 avg_second,cpu_time/1000/1000 from gv$sql where sql_id='0ysf4sb17gp92';

查找执行时间大于300s的SQL语句。

select a.sql_text,a.sql_id,

b.etime elapsed_time,

u.username,

c.user_id,c.sample_time,c.instance_number

from dba_hist_sqltext a,

(select sql_id,MAX(elapsed_time_delta/1000000) as etime 

from dba_hist_sqlstat 

where elapsed_time_delta/1000000 >=300 GROUP BY sql_id)b,

(SELECT SQL_ID,max(sample_time) sample_time,instance_number,user_id

FROM dba_hist_active_sess_history 

GROUP BY sql_id,instance_number,user_id) c,

dba_users u

where a.sql_id=b.sql_id

and b.sql_id=c.sql_id

and c.user_id=u.user_id

AND u.username NOT IN ('SYS','SYSTEM')

--AND a.SQL_ID='3achj6g0wd53m'

--AND REGEXP_LIKE(SQL_Text,'DEFECT_IMAGE','i') 

--AND a.COMMAND_TYPE = 6

AND SAMPLE_TIME >= to_date('2024-03-8 01:00:00','YYYY-MM-DD HH24:MI:SS')

AND SAMPLE_TIME <= TO_DATE('2024-03-8 23:00:00','YYYY-MM-DD HH24:MI:SS') 

order by sample_time desc,b.etime desc;

查看用户最近登录的时间

select MACHINE,s.logon_time,PROGRAM from gv$session s 

where MACHINE='ZHFA-CPJSLNK1\ZHFA-CPJSLNK1'

order by s.logon_time desc;

查看正在运行的SQL语句：

select a.sid,a.serial#,

a.paddr,a.machine,

nvl(a.sql_id,a.prev_sql_id) sql_id,

b.sql_text,b.sql_fulltext,

b.executions,

b.first_load_time,b.last_load_time,

b.last_active_time,

b.disk_reads,b.direct_writes,b.buffer_gets

from gv$session a,gv$sql b,gv$process c

where a.username='aaa'

--and a.status ='ACTIVE'

and c.addr=a.paddr

and a.sql_hash_value=b.hash_value