오라클 AWR 에서 Top SQL 추출하는 SQL 스크립트 (dba_hist_sqlstat, dba_hist_sqltext)

Oracle DBMS 에서 튜닝 대상 SQL을 뽑기 위한 방법으로 AWR 딕셔너리를 이용할 수 있습니다.

AWR 리파지토리(저장소)에는 디폴트로 8일간의 SQL 실행정보가 기록되어 있습니다.

여기에서 실행시간이 오래 걸린 SQL들을 추출할 수 있습니다.

 

아래 SQL문을 sys 또는 system 유저에서 실행하면, AWR 보존기간(디폴트 8일) 동안 실행된 히스토리를 뒤져서 10초이상 실행된 SQL 내역을 뽑아줍니다.

 

-- AWR에서 10초 이상 실행된 SQL문 추출
set linesize 200
col username for a15
col sqltext for a100
col "elapsed_time(s)" for 999,999,999
col "buffer_get" for 999,999,999,999

select s.parsing_schema_name as username, s.sql_id, 
       round(elapsed_time_total/executions_total/1000000) "elapsed_time(s)", 
       round(buffer_gets_total/executions_total) "buffer_get", 
       executions_total "executions", 
       substr(t.sql_text,1,100) as sqltext
from dba_hist_sqlstat s, dba_hist_sqltext t
where s.sql_id = t.sql_id
  and parsing_schema_name not in ('SYS', 'SYSTEM')
  and round(elapsed_time_total/executions_total/1000000) > 10
  and executions_total > 0
order by 3;

 

AWR 관련 딕셔너리뷰인 dba_hist_sqlstat 과 dba_hist_sqltext 를 이용해서 조회합니다.

sqltext 는 전체 Full Text 가 다 들어있지만, 일단은 100 byte 만 잘라서(substr) 가져옵니다.

elapsed_time 은 백만분의 1초(microsecond) 단위 입니다. 초단위로 구하기 위해서 1000000 으로 나눴습니다.

elapsed_time 으로 오름차순 정렬하기 때문에 맨마지막에 나오는 것이 가장 오래 걸린 SQL이 됩니다.

elapsed_time 을 execution(실행횟수)로 나눴기 때문에 "한번 실행에 걸린시간" 을 가져옵니다.

 

 

buffer_get 은 한번 실행할때마다 가져온 버퍼의 수를 의미합니다.

elapsed_time, executions 뒤에 "_total" 이라고 붙어있는데 이건 AWR 전체 스냅샷기간동안 누적된 값을 의미합니다.

특정 스냅샷 기간동안의 값은 "_delta" 라고 붙어있는 컬럼들이 따로 있습니다.

 

 

위에서 Top SQL 목록을 조회했는데, SQL Text 컬럼이 100 byte 로 잘려서 나옵니다.

SQL Text 전체 문장을 보려면 dba_hist_sqltext 에서 sql_id 값을 넣어서 따로 조회합니다.

 

-- sql_id 값으로 SQL문 확인
set long 10000000

select sql_text
from dba_hist_sqltext
where sql_id = '&1';

 

sql_id 값으로 'c12q5b87432kw' 을 넣으면 해당 sql_id 대한 Full Text 를 출력합니다.

 

 

SQL문내에서 Bind 변수를 사용한 경우에는 해당 변수명과 변수값을 확인할 수 있습니다.

 

-- Bind 변수값 확인
col name for a20
col value_string for a50
col datatype_string for a20

select snap_id, position, name, value_string, datatype_string
from dba_hist_sqlbind
where sql_id = '&1'
order by 1,2;

 

dba_hist_sqlbind 딕셔너리뷰에서 정보를 조회하면 됩니다.

 

 

V_DEPTNO 변수에 5 라는 숫자가 할당되어 실행되었다는 의미입니다.

위에서는 결과가 달랑 1개만 나왔습니다.

하지만, 하나의 SQL문이 Bind 변수값을 바꿔가면서 여러번 실행될 수 있기 때문에, Bind 변수값이 여러개 나올 수 있습니다.

 

 

참고)  오라클 AWR 레포트 뜨는(출력하는) 방법 - awrrpt, awrrpti