tianzhizy

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;

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;