--查询执行最慢的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;