查询SQL执行次数、执行时间

--查询执行次数最多的100条SQL。

SELECT *
  FROM (SELECT S.SQL_TEXT,
               S.EXECUTIONS "执行次数",
               S.PARSING_USER_ID "用户名",
               RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK
          FROM V$SQL S
          LEFT JOIN ALL_USERS U
            ON U.USER_ID = S.PARSING_USER_ID) T
 WHERE EXEC_RANK <= 100;

 

--查询执行时间最久的50条SQL。

SELECT *
  FROM (SELECT SA.SQL_TEXT,
               SA.SQL_FULLTEXT,
               SA.EXECUTIONS "执行次数",
               ROUND(SA.ELAPSED_TIME / 1000000, 2) "总执行时间",
               ROUND(SA.ELAPSED_TIME / 1000000 / SA.EXECUTIONS, 2) "平均执行时间",
               SA.COMMAND_TYPE,
               SA.PARSING_USER_ID "用户ID",
               U.USERNAME "用户名",
               SA.HASH_VALUE
          FROM V$SQLAREA SA
          LEFT JOIN ALL_USERS U
            ON SA.PARSING_USER_ID = U.USER_ID
         WHERE SA.EXECUTIONS > 0
         ORDER BY (SA.ELAPSED_TIME / SA.EXECUTIONS) DESC)
 WHERE ROWNUM <= 10;