리터럴(Literal) SQL

오라클 리터럴(Literal) SQL 및 성능정보 확인

 

SELECT *
FROM ( SELECT ( SELECT parsing_schema_name FROM v$sqlarea WHERE sql_id=a.max_sql_id ) as schema
            , ( SELECT module FROM v$sqlarea WHERE sql_id=a.max_sql_id ) as module
            , a.max_sql_id
            , a.plan_cnt
            , a.literal_sql_cnt
            , a.execution_cnt
            , buffer_gets
            , buffer_gets_per
            , cpu_time
            , cpu_time_per
            , elapsed
            , elapsed_per
            , SUBSTR((SELECT sql_fulltext FROM v$sqlarea WHERE sql_id = a.max_sql_id),1,300) sql_text
       FROM ( SELECT force_matching_signature
                   , COUNT(exact_matching_signature) literal_sql_cnt
                   , SUM(executions) execution_cnt
                   , MAX(sql_id) max_sql_id
                   , COUNT(DISTINCT plan_hash_value) plan_cnt
                   , SUM(buffer_gets) buffer_gets
                   , TRUNC(SUM(buffer_gets)/SUM(executions)) buffer_gets_per
                   , ROUND(SUM(cpu_time)/1000000, 2) cpu_time
                   , ROUND(SUM(cpu_time)/SUM(executions)/1000000, 2) cpu_time_per
                   , ROUND(SUM(elapsed_time)/1000000, 2) elapsed
                   , ROUND(SUM(elapsed_time)/SUM(executions)/1000000, 2) elapsed_per
              FROM v$sql
              WHERE parsing_schema_name NOT IN ('SYS','SYSTEM','SYSMAN')
              AND executions > 0
              GROUP BY force_matching_signature
              HAVING COUNT(exact_matching_signature) >= 2
       ) a
       ORDER BY literal_sql_cnt DESC 
) 
WHERE rownum <= 100
;