[SQLP] 예상 및 실제 실행계획 확인

예상 실행계획 확인

1. PLAN_TABLE 생성

~ 9i : PLAN_TABLE을 사용자가 직접 생성
( '?'는 $ORACLE_HOME 디렉토리 기호 )

dbms_xplan 패키지
위 스크립트를 열어보면 dbms_xplan 패키지를 사용하는 것을 볼 수 있다. dbms_xplan는 오라클에서 제공하는 패키지 중 하나로, SQL 실행 계획을 조회하고 분석할 수 있는 다양한 함수를 제공한다.

select plan_table_output
from table(dbms_xplan.display('plan_table', null, 'serial'))

• 첫번째 인자 : 실행 계획이 저장된 plan table명 입력
• 두번째 인자 : statment_id 입력. null일 경우 마지막 explain plan 명령에 사용된 쿼리 실행계획 출력됨
• 세번째 인자 : 포맷 옵션 지정
  • BASIC : ID, Operation, Name 출력. 가장 기본적인 정보
  • BASIC ROWS BYTES COST : ID, Operation, Name, Rows, bytes, Cost 출력
  • TYPICAL : Default. ID, Operation, Name, Rows, bytes, Cost, 쿼리에서 사용하는 조인, 인덱스, 테이블 접근 방식 등 포함.
  • SERIAL : 단일 프로세스(Serial) 실행 계획, 병렬 처리나 파티셔닝이 되지 않은 실행 계획
  • PARTITION : 파티셔닝된 테이블에 대한 실행 계획
  • PARALLEL : 병렬 실행 계획
  • PREDICATE : 쿼리에서 사용된 조건(Predicate)조건문에 사용된 필터 등
  • PROJECTION : 쿼리에서 선택된 컬럼(Projection)에 대한 정보, 어떤 컬럼들이 select에 사용되었는지 세부 정보
  • ALIAS : 테이블 별칭(Alias)에 대한 정보, 테이블의 실제 이름과 별칭 대응
  • REMOTE : 분산 쿼리 실행 계획. 원격 데이터베이스에서 수행된 작업이나 연결된 DBMS에 대한 정보 포함
  • NOTE : 실행 계획과 관련된 주석 표시 (예를들어 옵티마이저가 실행 계획을 선택하는데 사용한 특정 조건이나 고려 사항을 추가적으로 표시)
  • ALL : 모든 옵션 표시, 실행 계획에 포함된 모든 세부 정보를 제공하며 각 연산자의 설명과 함께 다양한 추가적인 메타데이터 표시
  • OUTLINE : 쿼리 최적화에 사용된 아웃라인 정보
  • ADVANCED : ALL + OUTLINE

**~10g ~ : 오라클이 `sys.plan_table$` 테이블과 `PLAN_TABLE`로 명명한 `public synonym`을 생성** ( `synonym` : 객체(Table, View, Procedure ..)의 별칭 )

2. SQL*Plus에서 실행계획 확인

explain plan 명령어 수행 시 SQL 실행계획이 plan_table에 저장

오라클이 제공하는 utlxpls.sql 스크립트를 이용하여 실행계획 확인

예상 실행 계획이므로, 실제 다른 방식으로 실행될 수도 있다.

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

SQL Cursor : SQL문을 처리한 후 반환되는 결과 집합 ( 하드 파싱 과정을 거쳐 메모리에 적재된 SQL / 파싱트리 / 실행계획 / SQL을 실행하는데 필요한 정보를 담은 SQL Area )


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

실행계획은 V$sql_plan veiw에서 확인할 수 있다. V$sql_plan을 조회하려면 SQL에 대한 sql_id와 child_number 값 필요.

1. sql_id와 child_number 조회

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

2. V$sql_plan veiw에서 실행계획 확인

방법1 : sql_id와 child_number로 v$sql_plan view를 직접 조회
방법2 : dbms_xplan, display_cursor함수 이용

select *
from table(dbms_xplan.display_cursor('[sql_id]',[child_no],'[format]'));

sql_id와 child_number을 null로 입력 시, 직전 수행한 SQL에 대한 정보를 출력한다.

select *
from table(dbms_xplan.display_cursor(null,null,'BASIC ROWS BYTES COST'));

캐싱된 커서의 Row Source별 수행 통계 출력

1. 오퍼레이션 단계별 수행통계 수집

• 세션 레벨에서 statistics_level 파라미터를 all로 설정
• 분석 대상 SQL문에 gather_plan_statistics 힌트 사용

2. dbms_xplan.display_cursor 함수로 확인

DBMS_XPLAN	SQL 트레이스	설명
Starts	-	각 오퍼레이션 단계 실행 횟수
E-Rows	-	SQL을 수행하기 전 옵티마이저가 각 실행단계별로 예상했던 로우 수, 예상 실행 계획의 Rows 또는 Card와 일치
A-Rows	rows	각 단계에서 읽거나 갱신한 건 수
A-Time	time	각 단계별 소요시간
Buffers	cr(=query), current	SQL 수행 과정에서 읽은 총 블록 수
Reads	pr	SQL 수행 과정에 디스크에서 읽은 총 블록 수