시간 오래 걸리는 쿼리는?

------------------------------------------------
-- sys 계정으로 들어가서 느린 쿼리를 골라 낸다.
------------------------------------------------
select
    sql_text, elapsed_time, executions, 
    -- ((elapsed_time / executions ) || '(ms)') as AVG_ELA_TIME, last_load_time
    ((ROUND(ELAPSED_TIME/EXECUTIONS/1000000,4)) || '(sec)') as AVG_ELA_TIME, last_load_time    
from v$sqlarea
   where (
    sql_text like 'select%' or
    sql_text like 'SELECT%'
    )
    and (module = 'JDBC Thin Client')
    and parsing_schema_name = 'C##TEST'
    
    order by last_load_time desc

------------------------------------------------
-- 전체적으로 통계를 보고 느린 쿼리만 집중 튜닝한다. 
------------------------------------------------
SELECT 
           COUNT(*)  AS SQL개수
          , COUNT(DISTINCT SUBSTR(SQL_TEXT, 1 , 100)) AS SQL유니크개수
          , SUM(EXECUTIONS)  AS 수행횟수
          , ROUND(AVG(ELAPSED_TIME/EXECUTIONS/ 1000000), 2) AS 평균수행시간
          , COUNT(CASE WHEN ELAPSED_TIME/EXECUTIONS/1000000 >= 10 THEN 1 END) AS 악성10초이상쿼리
          , ROUND(MAX(ELAPSED_TIME/EXECUTIONS/1000000), 2) AS 최대소요시간
  FROM V$SQL
  WHERE PARSING_SCHEMA_NAME = 'SWING'
  AND LAST_ACTIVE_TIME >= TO_DATE('20110706', 'YYYYMMDD')

------------------------------------------------
-- v$sql 초기화
------------------------------------------------
alter system flush shared_pool;