Oracle查看数据库执行SQL信息

查找某段时间执行过的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_numberfrom 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_idFROM dba_hist_active_sess_history GROUP BY sql_id,instance_number,user_id) c,dba_users uwhere a.sql_id=b.sql_idand b.sql_id=c.sql_idand c.user_id=u.user_idAND u.username NOT IN ('SYS','SYSTEM')--AND a.SQL_ID='3achj6g0wd53m'--AND REGEXP_LIKE(SQL_Text,'DEFECT_IMAGE','i') --AND a.COMMAND_TYPE = 6AND 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