[ORACLE] LITERAL SQL 추출 쿼리

SELECT MAX( parsing_schema_name ) parsing_schema_name ,

MAX( MODULE ) MODULE ,

MAX( s.sql_id ) sql_id ,

COUNT( s.exact_matching_signature ) literal_sql_cnt ,

ROUND( SUM( buffer_gets ) /sum( s.executions ) , 2 ) buffer_avg ,

ROUND( SUM( elapsed_time )/1000000 /sum( s.executions ) , 2 ) elapsed_avg ,

ROUND( SUM( rows_processed ) /sum( s.executions ) , 2 ) rows_processed ,

SUM( s.executions ) executions ,

ROUND( SUM( cpu_time ) /max( cpu_time_total ) *100 , 2 ) ratio_cpu ,

ROUND( SUM( elapsed_time ) /max( elapsed_time_total ) *100 , 2 ) ratio_elapsed ,

COUNT( DISTINCT s.plan_hash_value ) plan_cnt ,

substr_sqltext sql_text

FROM (

SELECT s.parsing_schema_name ,

s.module ,

s.sql_id ,

s.hash_value ,

s.plan_hash_value ,

s.address ,

s.sql_text substr_sqltext,

s.executions ,

s.buffer_gets ,

s.disk_reads ,

s.rows_processed ,

s.cpu_time ,

s.elapsed_time ,

s.force_matching_signature ,

s.exact_matching_signature ,

ROUND( (s.buffer_gets / s.executions) , 1 ) lio ,

ROUND( (s.elapsed_time / s.executions) /1000000 , 1 ) elapsed_sec ,

ROUND( (s.cpu_time / s.executions) /1000000 , 1 ) cpu_sec ,

SUM( s.cpu_time ) over( ) cpu_time_total ,

SUM( s.elapsed_time ) over( ) elapsed_time_total

FROM v$sqlarea s

where s.executions > 0

) s

WHERE s.executions > 0

AND s.force_matching_signature <> exact_matching_signature

AND s. parsing_schema_name NOT IN ( 'SYS' , 'SYSTEM' , 'SYSMAN' )

GROUP BY s.force_matching_signature, substr_sqltext

HAVING COUNT( s.exact_matching_signature ) >= 2

ORDER BY literal_sql_cnt DESC