SQL 분석도구

sqltrace xplan autotrace 설명
https://myjamong.tistory.com/236

1. 예상 실행계획

실행계획 확인방법

실행계획 확인하는방법은 여러가지가있음
오라클이 기본으로 제공하는 쿼리툴 SQL*Plus에서 다음 명령을 수행하면 실행계획이 PLAN_TABLE에 저장된다.

explain plan for
쿼리

PLAN_TABLE에 저장된 정보를 읽어 분석하기 쉬운 형태로 포매팅하는 방법에 여러가지가 있지만, 가장 쉬운 방법은

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY(null,null,'typical'))

SQL SERVER에서 예상 실행계획 출력방법

set showplan_text on
쿼리

오라클 예상 실행계획 확인가능한정보

• 기본적으로(dbms_xplan.display 함수 세번째 인자에 typical 입력) 확인할 수 있는정보는

• Plan Hash Value

• 오브젝트 액세스방식(Operation)

• 오브젝트명

• 예상 Cardinality(=Rows)

• 예상 데이터 크기(Bytes)

• 예상 Cost

• 예상 CPU Time

• 조건절 정보(Predicate Information) 등이다.

  display함수 세번째 인자에 alias, outline, advanced등을 지정하면,
  Query Block및 Object Alias, Outline, Column Projection정보를 추가로 확인할 수 있다.

  AutoTrace 확인법 : https://estenpark.tistory.com/302

  Sql Trace, TKPROF 확인법 : https://estenpark.tistory.com/303?category=626579

2. SQL 트레이스

오라클 에서 SQL트레이스 확인 명령어

alter session set sql trace = true;
SELECT * FROM 테이블 WHERE 조건;

예상실행계획에서 볼수있는정보

예상 cardinality / 예상 Cost / 조건절 정보

오라클 Auto Trace에서 확인할수 있는정보

예상 실행계획 / 실제 disk에서 읽은 블록 수 / 실제기록한 Redo크기

오라클 SQL Trace에서 확인할 수 있는 정보

하드파싱 횟수 / 실제 사용한 CPU TIME / 실제 디스크에서 읽은 블록 수

오라클 Trace 확인 명령어

alter session set sql_trace = true

힌트를 활용한 실행계획보기

• gather_plan_statistics

• dbms_xplan.display_cursor(sql_id, child_number, 'allstats last')

  TKProf 유틸리티

  SQL트레이스 리포트 파일 생성
  트레이스파일명, 결과파일명(확장자 자유), sys=no(SQL파싱과정에서 내부적으로 수행되는 SQL문장을 제외)
  tkprof [트레이스 파일명][결과 파일명] sys=no

  오라클 SQL 트레이스에서 확인할 수 있는정보 ADD

  PARSE COUNT : 하드파싱횟수
  CPU : 실제사용한 CPU TIME
  DISK : 실제 디스크에서 읽은 블록수
  Recursive calls : 하드파싱 과정에서 딕셔너리 조회하거나 DB저장형 함수에 내장된 SQL을 수행할때 발생한 CALL 횟수

  오라클 SGA 메모리에 기록한 SQL 트레이스 정보 출력

• dbms_xplan.display_cursor - 첫번째인자 SQL커서 ID, 두번째인자 SQL 커서 CHILD_NUMBER
  SQL커서 ID와 CHILD_NUMBER는 SQL문장의 앞쪽 일부 문자열로 V$SQL 뷰의 SQL_TEXT컬럼을 조회해서 얻은 SQL_ID와 CHILD_NUMBER컬럼 값에서 얻는다.
  인자를 NULL,NULL입력하면 바로직전 수행한 SQL의 커서 ID와 CHILD_NUMBER를 내부에서 자동 선택해 준다.
  SELECT /+ gather_plan_statistics/ count() from table;
  SELECT from table (dbms_xplan.display_cursor(null, null, 'allstats last'))

  DBMS_XPLAN.DISPLAY_CURSOR 함수를 통해 추출한 SQL트레이스 정보

  select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))

  • Starts : 각오퍼레이션 단계별 실행 횟수
  • E-Rows : 옵티마이저가 예상한 Rows
  • A-Rows : 각 오퍼레이션 단계에서 읽거나 갱신한 로우 수
  • A-times : 각 오퍼레이션 단계별 소요시간
  • Buffers : 캐시에서 읽은 버퍼 블록 수
  • Reads : 디스크에서 읽은 블록수

3. 응답 시간 분석

대기이벤트(Wait Event)

• 대기이벤트 발생 예

• SGA공유 메모리에서 특정자원을 액세스하려고 래치를 획득하는 과정에서 다른 프로세스와 경합이 발생시
  - 디스크로부터 블록 I/O를 요청할때
  - 클라이언트로부터 다음 작업 요청이 오기를 기다리는 경우.

  대기이벤트 종류

  1) latch : shared pool
  Shared Pool에서 특정 오브젝트 정보 또는 SQL커서를 위한 Free Chunk를 할당받으려 할 때
  Shared Pool 래치를 할당받아야한다. 이 대기이벤트는 shared pool래치를 할당받는 과정에서 발생하는 경합과 관련있으며,
  하드파싱을 동시에 심하게 일으킬 때 주로 나타난다.
  2) library cach lock/library cache pin
  SQL수행 도중 DDL을 수행할 때 나타난다.
  3) free buffer waits
  서버프로세스가 버퍼 캐시에서 FREE BUFFER를 찾지 못해 DBWR에게 공간을 확보해 달라고 신호를 보낸후 대기할 때 나타난다.
  4) log file sync
  커밋 명령을 받은 서버 프로세스가 LGWR에게 로그버퍼를 로그파일에 기록해 달라고 신호를 보낸 후 대기할 때 나타난다.

  대기이벤트(Wait Event) / 응답시간 분석(Resoponse Time Analysis) 성능관리 방법론

• 대기 이벤트를 기반으로 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법/과정

• Response Time = Service Time + Wait Time
  = CPU Time + Queue Time