Oracle查看耗资源SQL

最新推荐文章于 2021-04-02 20:33:47 发布

东方-phantom

最新推荐文章于 2021-04-02 20:33:47 发布

阅读量928

点赞数

分类专栏： cronjob 监控脚本

cronjob 监控脚本专栏收录该内容

8 篇文章 0 订阅

订阅专栏

SELECT S.SQL_TEXT, 
       S.SQL_FULLTEXT, 
       S.SQL_ID, 
       ROUND(ELAPSED_TIME / 1000000 / (CASE 
               WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 
                1 
               ELSE 
                EXECUTIONS 
             END), 
             2) "执行时间'S'", 
       S.EXECUTIONS "执行次数", 
       S.OPTIMIZER_COST "COST", 
       S.SORTS, 
       S.MODULE, --连接模式（JDBC THIN CLIENT：程序） 
       -- S.LOCKED_TOTAL, 
       S.PHYSICAL_READ_BYTES "物理读", 
       -- S.PHYSICAL_READ_REQUESTS "物理读请求", 
       S.PHYSICAL_WRITE_REQUESTS "物理写", 
       -- S.PHYSICAL_WRITE_BYTES "物理写请求", 
       S.ROWS_PROCESSED      "返回行数", 
       S.DISK_READS          "磁盘读", 
       S.DIRECT_WRITES       "直接路径写", 
       S.PARSING_SCHEMA_NAME, 
       S.LAST_ACTIVE_TIME 
  FROM GV$SQLAREA S 
 WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE 
               WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 
                1 
               ELSE 
                EXECUTIONS 
             END), 
             2) > 5 --100 0000微秒=1S 
   AND S.PARSING_SCHEMA_NAME = USER 
   AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') = 
       TO_CHAR( SYSDATE, 'YYYY-MM-DD' ) 
   AND S.COMMAND_TYPE IN (2 , 3, 5, 6 , 189) 
 ORDER BY "执行时间'S'" DESC;