Oracle 工具類 Sql

標籤：ext   username   use   row   lap   exec   hash   select   執行   

一、查詢執行最慢的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 <= 50;

二、查詢次數最多的 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;

總結

Oracle 工具類 Sql