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