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为0ELAPSED_TIME会一直在增长。EXECUTIONS为0时计算sql平均执行时长这个除法运算时就会报错所以这里做了个判断当EXECUTIONS为0时将EXECUTIONS设为1再计算。5.LAST_LOAD_TIME,LAST_ACTIVE_TIME区别LAST_LOAD_TIME执行计划最近一次载入被library cache库缓存的时间。LAST_ACTIVE_TIMESQLsql最近一次执行的时间。当执行新的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,ACTION FROM 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 1 AND 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 改成 某个历史时间段不限制最大时间示例ANDLAST_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为0ELAPSED_TIME会一直在增长,LAST_ACTIVE_TIME会比较接近当前时间而非该sql最近一次开始执行的时间SQL_EXEC_START代表该sql最近一次开始执行的时间示例四 查看****当前正在执行的某类慢sql的统计信息#查询当前平均执行时长超过1秒的慢sql在上面sql的基础上加个平均执行时长的条件SELECT*FROM(SELECTb.USERNAME,a.INST_ID,b.SQL_ID,b.SID,b.SERIAL#,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS,CPU_TIME,ROUND(ELAPSED_TIME/1000,2)asTOTAL_ELAPSED_TIME_S,CASEWHENEXECUTIONS 0THENROUND(ELAPSED_TIME/1000/1,2)ELSEROUND(ELAPSED_TIME/1000/EXECUTIONS,2)ENDASAVG_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# ||‘’;ASKILL_SQLFROMGV$SQL aINNERJOINGV$SESSION b ONb.sql_hash_value a.hash_valueWHERESQL_TEXT NOTLIKE’%SELECT COMMAND_TYPE,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS%’ANDb.username isnotnullANDb.username notin(‘SYSMAN’))cWHERE c.AVG_ELAPSED_TIME_S 1ORDERBYc.AVG_ELAPSED_TIME_S DESC;
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为0ELAPSED_TIME会一直在增长。EXECUTIONS为0时计算sql平均执行时长这个除法运算时就会报错所以这里做了个判断当EXECUTIONS为0时将EXECUTIONS设为1再计算。5.LAST_LOAD_TIME,LAST_ACTIVE_TIME区别LAST_LOAD_TIME执行计划最近一次载入被library cache库缓存的时间。LAST_ACTIVE_TIMESQLsql最近一次执行的时间。当执行新的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,ACTION FROM 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 1 AND 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 改成 某个历史时间段不限制最大时间示例ANDLAST_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为0ELAPSED_TIME会一直在增长,LAST_ACTIVE_TIME会比较接近当前时间而非该sql最近一次开始执行的时间SQL_EXEC_START代表该sql最近一次开始执行的时间示例四 查看****当前正在执行的某类慢sql的统计信息#查询当前平均执行时长超过1秒的慢sql在上面sql的基础上加个平均执行时长的条件SELECT*FROM(SELECTb.USERNAME,a.INST_ID,b.SQL_ID,b.SID,b.SERIAL#,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS,CPU_TIME,ROUND(ELAPSED_TIME/1000,2)asTOTAL_ELAPSED_TIME_S,CASEWHENEXECUTIONS 0THENROUND(ELAPSED_TIME/1000/1,2)ELSEROUND(ELAPSED_TIME/1000/EXECUTIONS,2)ENDASAVG_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# ||‘’;ASKILL_SQLFROMGV$SQL aINNERJOINGV$SESSION b ONb.sql_hash_value a.hash_valueWHERESQL_TEXT NOTLIKE’%SELECT COMMAND_TYPE,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS%’ANDb.username isnotnullANDb.username notin(‘SYSMAN’))cWHERE c.AVG_ELAPSED_TIME_S 1ORDERBYc.AVG_ELAPSED_TIME_S DESC;
