실행계획

K·2022년 7월 21일
친절한 SQL튜닝
0

친절한SQL튜닝

목록 보기
16/16

1. 실행계획 확인

PLAN TABLE 생성 (예상실행계획)

  • 실행계획을 확인하려면 우선 plan_table을 생성해야 하는데, 아래 스크립트를 실행하면 된다.
  • '?'는 $ORACLE_HOME디렉토리를 대체하는 기호다.

    SQL @?/rdbms/admin/utlxplan.sql

  • 10g 버전부터는 기본적으로 오라클이 sys.plan_table$ 테이블을 만들고, 아래와 같이 'PLAN_TABLE'로 명명한 public synonym도 생성하므로 사용자가 별도로 plan_table을 만들 필요가 없다

    select *
    from all_synonyms
    where synonym_name ='PLAN_TABLE';

SQL*Plus에서 실행계획 확인

  • plan_table을 생성했으면 이제 실행계획을 생성할 차례다. SQL*Plus에서 아래와 같이 explain plan명령어를 수행하면된다. 그러면 SQL실행계획이 plan table에 저장된다.

    explain plan for
    select * from emp where empno = 7900;

  • plan table저장된 실행계획확인

    set linesize 200
    @?/rdbms/admin/utlxpls

상용 쿼리 툴에서 실행계획 확인.

  • 토드/오랜지 : Ctrl + E
  • SQLGate : F7
  • SQL Developer : F10

더많은 정보 확인하기

  • explain plan 실행후 아래 쿼리를 수행하면 더많은 정보를 확인할 수 있다.

    select * from table(dbms_xplan.display(null,null,'advanced'));

  • 세번째 인자에는 serial, parallel, outline, alias, projection, all 같은 옵션사용가능

2. AUTO Trace

  • 튜너들이 가장 즐겨사용하는 도구 중 하나.

  • 사용법 (SQL DEVELOPER에선 f5로 실행하면됨)

    set autotrace on
    select * from

  • Execution Plan : 예상실행계획

  • Statistics : 실행통계


  • Auto Trace 옵션

    • set autotrace on : SQL실행, 결과집합과 예상 실행계획 및 실행 통계 출력
    • set autotrace on explain : SQL실행, 결과집합과 함께 예상실행계획 출력
    • set autotrace on statistics : SQL실행, 결과집합과 실행통계 출력
    • set autotrace on traceonly : SQL실행, 예상실행계획, 실행통계 출력
    • set autotrace traceonly explain : SQL미실행, 예상 실행계획만 출력
    • set autotrace traceonly statistics : SQL실행, 실행통계 출력
    • 1~3은 실행결과를 출력해야하므로 쿼리실제수행, 4,6은 실행통계보여줘야하므로 쿼리 실제수행

  • 필요권한
    실행계획 확인용도만 사용하면 plan table만 생성돼 있으면된다
    실행통계까지 확인하려면 v_$sesstat, v_$statname, v_$mystat뷰에 대한 읽기 권한 필요
    dba, select_catalog_role등이나, 최소 plustrace 롤부여 필요.

    grant plustrace to 계정명;

3. SQL 트레이스

  • SQL튜닝할때 가장 많이 사용하는 강력한 도구
  • AutoTrace결과만으로 문제점을 찾을 수 없을 때, SQL트레이스를 이용하면 문제점을 쉽게 찾을수있다.
  • 접속한 세션에 트레이스를 설정하는 방법

    alter session set sql_trace = true;
    select * from t_ord where ord_seq = 517;
    alter session set sql_trace =false;

  • 트레이스 파일 생성되는 경로조회

    select value
    from v$diag_info
    where name ='Diag Trace';

  • 트레이스 파일명 조회

    select value
    from v$diag_info
    where name ='Default Trace File';

리포트 생성

  • 도스프롬프트나 유닉스 쉘에서 tkprof명령어를 사용할 수 있다.

    --sys=no 옵션은 SQL을 파신하는 과저에서 내부적으로 수행되는 SQL문을 제외
    tkprof ora11g_ora_22827.trc report.prf sys=no

  • 수행한 결과파일을 텍스트도구에서 확인가능

  • Call통계 - 트레이스 결과 분석

    • call : 커서의 진행상태에따라 Parse, Execute, Fetch3개단계로 보여준다
      • Parse : SQL을파싱하고 실행계획을 생성하는 단계
      • Execute : SQL 커서를 실행하는 단계
      • Fetch : 레코드를 실제로 Fetch하는 단계
    • count : Parse, Execute, Fetch각 단계가 수행된 횟수
    • cpu : 현재 커서가 각 단계에서 사용한 cpu time
    • elaspsed : 현재 커서가 각 단계를 수행하는데 소요된 시간
    • disk : 디스크로부터 읽은 블록수
    • query : Consistent모드로 읽은 블록수 (6장 1절 1항 MVCC모델 참고)
    • current : Current모드로 읽은 블록수 (6장 1절 1항 MVCC모델 참고)
    • rows : 각 단계에서 읽거나 갱신한 건수

  • AutoTrace와 일치하는 항목 매핑

    • current = db block gets : Current모드로 읽은 블록 수
    • query = consistent gets : Consistent 모드로 읽은 블록 수
    • disk = physical reads : 디스크로부터 읽은 블록 수
    • fetch count = SQL*Net roundtrips to/from client : 조회결과를 전송을 위해 클라이언트가 발행한 Fetch Call횟수
    • fetch rows = rows processed : 조회 건수

  • 하단 실행계획 통계 각 항목 의미

    • Rows : 각 수행단계에서 출력된 로우 수
    • cr, pr등은 누적

4. DBMS_XPLAN 패키지

  • 10g부터 이패키지로 라이브러리 캐시에 캐싱된 SQL실행계획도 확인가능
  • SQL트레이스처럼 오퍼레이션 단계(ROW-Source)별 수행통계도 확인가능

4.1 예상 실행계획 출력

explain plan for
select * from t_ord where ord_seq = 517;
select plan_table_output
from table(dbms_xplan.display('plan_table', null,'serial'));

4.2 캐싱된 커서의 실제 실행계획 출력

  • 'SQL 커서'란 하드파싱 과정을 거쳐 메모리에 적재된 SQL과 파싱트리, 실행계획, 그리고 SQL을 실행하는데 필요한 정보를 담은 SQL Area를 말한다.

  • 오라클은 라이브러리 캐시에 캐싱된 각 커서에 대한 수행통계를 볼수있도록 v$sql뷰를 제공

  • 실행계획은 v$sql_plan뷰에서 확인가능.

  • v$sql_plan을 조회하려면 SQL에 대한 sql_id와 child_number를 알아야한다

  • 직전에 수행한 sql에대한 sql_id와 child_number조회

    select prev_sql_id as sql_id, prev_child_number aschild_no
    from v$session
    where sid = userenv('sid')
    and username is not null
    and prev_hash_value <> 0;

  • 이전에 수행한 SQL의 sql_id, child_number조회(SQL TEXT로 검색)

    select sql_id, child_number, sql_fulltext, last_active_time
    from v$sql
    where sql_text like '%t_ord%'
    order by last_active_time desc;

  • sql_id와 child_number로 실행계획조회

    select from table(dbms_xplan.display_cursor('sql_id', child_no, 'format'));
    select     from table(dbms_xplan.display_cursor('sql_id', child_no, 'BASIC ROWS BYTES COST PREDICATE'));
    select * from table(dbms_xplan.display_cursor('c1b58wxhtvqw4', 0, 'ALLSTATS LAST'));

  • 항목 설명(괄호안은 SQL 트레이스와 매칭되는 속성)

    • A-ROWS (rows) : 각단계에서 읽거나 갱신한 건수
    • A-TIME (time) : 각 단계별 소요 시간
    • Buffers (cr(=query), current) : 캐시에서 읽은 버퍼 블록 수
    • Reads (pr) : 디스크로부터 읽은 블록 수
    • 각 항목은 기본적으로 누적값을 보여주며 format에 LAST추가시 마지막 수행했을때의 일량을 보여준다.

  • 권한이 필요한 VIEW
    grant select on v$session to '계정명';
    grant select on v    sql to '계정명'; grant select on v_sql_plan to '계정명';

5. 실시간 SQL모니터링

  • 11g부터 실시간 SQL모니터링 제공하며 모니터링 대상은 다음과 같다
    • CPU time또는 I/O time을 5초이상 소비한 SQL
      (_sqlmon_threshold파라미터로 조절가능)
    • 병렬 SQL
    • monitor힌트를 지정한 SQL
  • 위조건을 만족해도 실행계획이 500라인넘으면 대상에서 제외
    (_sqlmon_max_planlines파라미터를 올리면 그이상도 모니터링 가능)
  • 수집한 정보는 VSQLMONITOR,VSQL_MONITOR, VSQL_PLAN_MONITOR뷰를 통해 확인할수있다
  • SQL을 실행하는동안 1초마다 자동 갱신된다. SQL수행후 적어도 1분간은 유지하지만 이후 캐시공간이 부족하면 언제든캐시에서 밀려난다.
  • 조회방법

    select dbms_sqltune.report_sql_monitor(sql_id=>'sql_id') from dual;
    select dbms_sqltune.report_sql_monitor(sql_id=>'sql_id', type=>'html') from dual;

6. V$SQL

  • 라이브러리 캐시에 캐싱돼 있는 각 SQL에 대한 수행통계를 보여준다
  • 쿼리가 수행을 마칠때마다 갱신되며, 오랫동안 수행되는 쿼리는 5초마다 갱신이 이루어진다
    select sql_id, child_number, sql_text, sql_fulltext, parsing_schema_name     --1
       ,loads, invalidations, parse_calls, executions, fetches, rows_processed --2
      , cpu_time, elapsed_time           --3
      , buffer_gets, disk_reads, sorts   --4
      , first_load_time, last_active_time --5
   from v$sql;
      1. 라이브러리 캐시에 적재된 SQL커서에 대한 정보
      1. 하드파싱 및 무효화 발생횟수, Parse, Execute, Fetch Call발생횟수, Execute또는 Fetch Call시점에 처리한 로우 건수 등
      1. CPU사용 시간과 DB구간 소요시간(microsecond)
      1. 논리적 블록 읽기와 디스크 읽기, 그리고 소트 발생 횟수
      1. 커서가 라이브러리 캐시에 처음 적재된 시점, 가장마지막에 수행된 시점
  • 이 뷰에서 제공하는 수치는 모두 누적값
  • SQL수행횟수로 나눈 평균값, 즉 SQL한번 수행당 일량과 시간을 계산해야 의미있는 분석이 가능(LAST)
  • 집중튜닝이 필요한 스키마를 선정하기위해 스키마별 쿼리 수행 통계를 집계하는 SQL
     select parsing_schema_name "업무"
       , count(*) "SQL개수"
       ,sum(executions) "수행횟수"
       ,round(avg(buffer_gets/executions)) "논리적I/O"
       ,round(avg(disk_reads/executions)) "물리적I/O"
       ,round(avg(rows_processed/executions)) "처리건수"
       ,round(avg(elapsed_time/executions/1000000),2) "평균소요시간"
       ,count(case when elapsed_time/executions/1000000 >= 10 then 1 end) "악성SQL"
       ,round(max(elapsed_time/executions/1000000),2) "최대소요시간"
  from v$sql
 where parsing_schema_name in ('스키마명')
   and last_active_time >= to_date('날짜', 'yyyymmdd')
   and executions > 0
  group by parsing_schema_name;
profile
K
늙어가면서 기억을 남기는 개발자
이전 포스트

SQL 옵티마이저

0개의 댓글