본문 바로가기

oracle

[ORACLE] oracle sql elapsed_time check query

728x90

AWR 이용한 sql query 별 elapsed_time 구하는 쿼리 

select
    sql_id "SQL_ID",    
    min(snap_id) "MIN_SNAP_ID",
    max(snap_id) "MAX_SNAP_ID",
    case when sum(executions_delta) = 0 then 0
    else round(sum(elapsed_time_delta)/sum(executions_delta), 3)/1000000
    end "ElapsedPerExec(s)",
    sum(elapsed_time_delta) "ElapsedTime (ms)",
    sum(executions_delta) "Executions"            
from
    dba_hist_sqlstat
where
    sql_id = 'SQL_ID 입력'
group by
    sql_id

728x90