--查詢執行次數最多的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;
浙公網安備 33010602011771號