oracle 12c查看执行过的sql及当前正在执行的sql

V$SQL 提供了已经执行过及正在执行的SQL语句的信息。

一 查看共享池中所有sql的统计信息

#统计共享池中某类sql执行次数，总体执行时长，平均执行时长等信息，并按总体执行时长降序排序

SELECT INST_ID,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS,CPU_TIME,ROUND(ELAPSED_TIME/1000,2) as TOTAL_ELAPSED_TIME_S,
CASE  WHEN EXECUTIONS = 0 THEN ROUND(ELAPSED_TIME/1000/1,2) ELSE  ROUND(ELAPSED_TIME/1000/EXECUTIONS,2) END AS AVG_ELAPSED_TIME_S,
FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME,DISK_READS,DIRECT_WRITES,DIRECT_READS,BUFFER_GETS,PARSING_USER_ID,PARSING_SCHEMA_NAME,ADDRESS,HASH_VALUE,PLAN_HASH_VALUE,MODULE,ACTION
FROM GV$SQL 
WHERE  SQL_TEXT  NOT  LIKE '%SELECT COMMAND_TYPE,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS%'AND PARSING_SCHEMA_NAME NOT LIKE '%SYS%' AND PARSING_SCHEMA_NAME NOT IN ('DBSNMP')
ORDER BY TOTAL_ELAPSED_TIME_S DESC;

查询结果示例：

/*

备注：

1.在oracle 12c里，ELAPSED_TIME单位是毫秒，在11g里ELAPSED_TIME单位是微秒。

2.PARSING_SCHEMA_NAME显示的是哪个用户执行的该sql，而不是这个sql操作的表属于哪个schema。比如如果是SYS执行的select count(*) from scott.t3,则PARSING_SCHEMA_NAME结果为SYS,而不是SCOTT。

3.假如用N个不同用户执行了相同sql，则该sql会生成N条记录，一个用户对应一条sql记录。

4.如果一个sql被阻塞了，它的EXECUTIONS为0，ELAPSED_TIME会一直在增长。EXECUTIONS为0时，计算sql平均执行时长这个除法运算时就会报错，所以这里做了个判断，当EXECUTIONS为0时，将EXECUTIONS设为1，再计算。

5.LAST_LOAD_TIME,LAST_ACTIVE_TIME区别：

LAST_LOAD_TIME 执行计划最近一次载入被library cache库缓存的时间。

LAST_ACTIVE_TIME SQL sql最近一次执行的时间。

当执行新的SQL语句时，如果这条SQL语句不在共享池中，数据库会进行硬解析，并将解析后的执行计划加载到共享池中。此时，LAST_LOAD_TIME会被更新为当前时间。如果执行的是共享池内已经存在的SQL语句，则只会进行软解析，LAST_LOAD_TIME的值不会改变。

LAST_LOAD_TIME在SQL语句首次硬解析时被设置，之后除非SQL语句被重新硬解析（如由于SQL文本改变或共享池空间不足导致被逐出后重新加载），否则其值不会改变。而LAST_ACTIVE_TIME则会在每次SQL语句执行时被更新。

*/

二 查看某个时间段内执行了哪些慢sql

假如同事让排查某个接口都有哪些慢sql，可以使用这个方法（先调用下这个慢接口，然后统计下这个时间段内的慢sql），只是会导出非这个接口的多余慢sql，再结合业务进行筛选吧。

接口慢也可以看看是否发生了阻塞，详细参考：

oracle排查长时间没提交的事务造成的阻塞案例-CSDN博客。

#在“一 查看所有sql的统计信息”基础上新增一个时间范围及执行时长超过1秒的条件，示例：

SELECT  *
FROM 
(SELECT INST_ID,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS,CPU_TIME,ROUND(ELAPSED_TIME/1000,2) as TOTAL_ELAPSED_TIME_S,CASE  WHEN EXECUTIONS = 0 THEN ROUND(ELAPSED_TIME/1000/1,2) ELSE  ROUND(ELAPSED_TIME/1000/EXECUTIONS,2) END AS AVG_ELAPSED_TIME_S,  FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME,DISK_READS,DIRECT_WRITES,DIRECT_READS,BUFFER_GETS,PARSING_USER_ID,PARSING_SCHEMA_NAME,ADDRESS,HASH_VALUE,PLAN_HASH_VALUE,MODULE,ACTIONFROM GV$SQL WHERE  SQL_TEXT  NOT  LIKE '%SELECT COMMAND_TYPE,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS%'AND LAST_ACTIVE_TIME BETWEEN TO_DATE('2024/11/24 09:00:00','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2024/11/24 15:00:00','YYYY-MM-DD HH24:MI:SS')
) c
WHERE AVG_ELAPSED_TIME_S > 1AND PARSING_SCHEMA_NAME NOT LIKE '%SYS%' AND PARSING_SCHEMA_NAME NOT IN ('DBSNMP')
ORDER BY AVG_ELAPSED_TIME_S DESC

--假如一个sql被阻塞了，那LAST_ACTIVE_TIME会一直更新，比较接近当前时间，那么如果限定了 LAST_ACTIVE_TIME为一个历史时间段，该可能会查不到这条被阻塞的sql，要想查到这个阻塞sql，就把BETWEEN AND 改成 > 某个历史时间段，不限制最大时间，示例：

  AND LAST_ACTIVE_TIME > TO_DATE('2024/11/24 09:00:00','YYYY-MM-DD HH24:MI:SS') 

三 查看当前正在执行的sql的统计信息

在“一 查看所有sql的统计信息”的sql基础上和gv$session进行关联：

SELECT b.USERNAME,a.INST_ID,b.SQL_ID,b.SID,b.SERIAL#,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS,CPU_TIME,ROUND(ELAPSED_TIME/1000,2) as TOTAL_ELAPSED_TIME_S,
CASE  WHEN EXECUTIONS = 0 THEN ROUND(ELAPSED_TIME/1000/1,2) ELSE  ROUND(ELAPSED_TIME/1000/EXECUTIONS,2) END AS AVG_ELAPSED_TIME_S,
FIRST_LOAD_TIME,LAST_LOAD_TIME,SQL_EXEC_START,LAST_ACTIVE_TIME,DISK_READS,DIRECT_WRITES,DIRECT_READS,BUFFER_GETS,PARSING_USER_ID,PARSING_SCHEMA_NAME,ADDRESS,HASH_VALUE,PLAN_HASH_VALUE,
b.MACHINE,b.MODULE,b.PROGRAM,b.SERVER,a.ACTION,b.EVENT,'alter system kill session ''' || b.sid || ',' || b.serial# ||''';' AS KILL_SQL
FROM GV$SQL  a
INNER JOIN GV$SESSION b ON b.sql_hash_value = a.hash_value
WHERE   SQL_TEXT NOT  LIKE '%SELECT COMMAND_TYPE,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS%'
AND b.username is not null
AND b.username not in('SYSMAN')
ORDER BY TOTAL_ELAPSED_TIME_S DESC;

如果一个sql被阻塞了，它的EXECUTIONS为0，ELAPSED_TIME会一直在增长,LAST_ACTIVE_TIME会比较接近当前时间，而非该sql最近一次开始执行的时间，SQL_EXEC_START代表该sql最近一次开始执行的时间，示例：

四 查看当前正在执行的某类慢sql的统计信息

#查询当前平均执行时长超过1秒的慢sql

在上面sql的基础上加个平均执行时长的条件：

SELECT * 

FROM 

(SELECT b.USERNAME,a.INST_ID,b.SQL_ID,b.SID,b.SERIAL#,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS,CPU_TIME,ROUND(ELAPSED_TIME/1000,2) as TOTAL_ELAPSED_TIME_S,

CASE  WHEN EXECUTIONS = 0 THEN ROUND(ELAPSED_TIME/1000/1,2) ELSE  ROUND(ELAPSED_TIME/1000/EXECUTIONS,2) END AS AVG_ELAPSED_TIME_S,

FIRST_LOAD_TIME,LAST_LOAD_TIME,SQL_EXEC_START,LAST_ACTIVE_TIME,DISK_READS,DIRECT_WRITES,DIRECT_READS,BUFFER_GETS,PARSING_USER_ID,PARSING_SCHEMA_NAME,ADDRESS,HASH_VALUE,PLAN_HASH_VALUE,

b.MACHINE,b.MODULE,b.PROGRAM,b.SERVER,a.ACTION,b.EVENT,'alter system kill session ''' || b.sid || ',' || b.serial# ||''';' AS KILL_SQL

FROM GV$SQL  a

INNER JOIN GV$SESSION b ON b.sql_hash_value = a.hash_value

WHERE   SQL_TEXT NOT  LIKE '%SELECT COMMAND_TYPE,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS%'

AND b.username is not null

AND b.username not in('SYSMAN')

) c

WHERE c.AVG_ELAPSED_TIME_S > 1

ORDER BY c.AVG_ELAPSED_TIME_S DESC;