Oracle 수행 시간 기준 SQL 추출 스크립트

Oracle 수행 시간 기준 SQL 추출 스크립트

---

SELECT CASE WHEN EXE_TIME BETWEEN 0 AND 0.9999 THEN '1초 미만' 

              WHEN EXE_TIME BETWEEN 1 AND 2.999 THEN  '1초이상 3초미만'

              WHEN EXE_TIME BETWEEN 3 AND 4.999 THEN  '3초이상 5초미만'

              WHEN EXE_TIME BETWEEN 5 AND 9.999 THEN  '5초이상 10초미만'

              WHEN EXE_TIME BETWEEN 10 AND 19.999 THEN '10초이상 20초미만'

              WHEN EXE_TIME >= 20 THEN '20초이상' END "GBN", 

              COUNT(DISTINCT SQL_ID), SUM(EXECUTIONS) EXE, SUM(ELapsed_time) elasped_time, sum(buffer_gets)

from

(select SQL_TEXT, MODULE,SQL_ID,PARSING_SCHEMA_NAME,

round(sum(Elapsed_time)/1000000/decode(sum(executions),0,1,sum(executions)),2) exe_time,

sum(parse_calls) parse_calls, sum(executions) executions,

sum(Disk_reads) Disk_reads, sum(Buffer_gets) Buffer_gets,

round((sum(Buffer_gets)-sum(Disk_reads))/decode(sum(Buffer_gets),0,1,sum(Buffer_gets)) *100,2) Hit_ratio,

round(sum(cpu_time)/1000000,2) cpu_time, round(sum(Elapsed_time)/1000000,2) Elapsed_time,

trunc(ratio_to_report(sum(Elapsed_time)) over (partition by null) * 100 ,2) "전체비중"

from v$sqlarea

Group by SQL_TEXT, MODULE, SQL_ID,PARSING_SCHEMA_NAME )

group by CASE WHEN EXE_TIME BETWEEN 0 AND 0.9999 THEN '1초 미만' 

              WHEN EXE_TIME BETWEEN 1 AND 2.999 THEN  '1초이상 3초미만'

              WHEN EXE_TIME BETWEEN 3 AND 4.999 THEN  '3초이상 5초미만'

              WHEN EXE_TIME BETWEEN 5 AND 9.999 THEN  '5초이상 10초미만'

              WHEN EXE_TIME BETWEEN 10 AND 19.999 THEN '10초이상 20초미만'

              WHEN EXE_TIME >= 20 THEN '20초이상' END

ORDER BY GBN