Oracle 실행 계획

Oracle에서 SQL 튜닝을 위해 사용하는 실행계획 명령어를 간단하게 4가지로 정리해보았다.

* 주요출력정보에서 파란색 텍스트는 다른 실행계획 출력 방법과 대응되는 의미가 있을시 표기하였고 마지막에 정리하였다.

​

1. Explain Plan For

	실행계획
문법	Explain plan [SET STATEMENT_ID = '계획ID' INTO PLAN_TABLE] FOR <SQL>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', '계획ID', 'ALL'));
비고	sql을 실행하지않고 예상 계획을 수립
	예상 카디널리티, 코스트, cpu 시간, 바이트크기, 조건절 정보
	예상계획으로 실제 실행시간은 불가
주요 출력 정보	predicate information(조건절 정보)
	ROWS
	BYTES
	COST(CPU)
	TIME

- 가장 기본적인 실행계획으로 예상계획을 수립한다 .

고로 행개수를 나타내는 ROWS는 예상 로우수로 후에 기술된 Shared Pool SQL Trace의 E-ROWS와 대응된다.

- SET 옵션절을 기술하지 않았다면 ~DISPLAY('PLAN_TABLE', NULL, 'ALL') 로 조회하며 가장최신의 플랜테이블을 보여줄것같다.

3번째 인자는 출력 포맷으로 serial, basic, typical 등 여러 방식으로 출력한다.

---

2. AutoTrace

	AUTOTRACE
문법	SET AUTOTRACE ON; --SQL을 실제 수행하고 실행계획, 실행통계 출력 SET AUTOTRACE ON EXPLAIN; --SQL을 실제 수행하고 실행계획 출력 SET AUTOTRACE ON STATISTICS; --SQL을 실제 수행하고 실행통계 출력 SET AUTOTRACE TRACEONLY; --SQL을 실제 수행하지 않고 실행계획, 실행통계 출력 SET AUTOTRACE TRACEONLY EXPLAIN; --SQL을 실제 수행하지 않고 실행계획 출력 SET AUTOTRACE TRACEONLY STATISTICS; --SQL을 실제 수행하지 않고 실행통계 출력 <SQL>; SET AUTOTRACE ON OFF;
비고	-
주요 출력 정보	predicate information(조건절 정보)
	recursive calls
	physical read
	consistent gets
	db block gets
	SQL*NET roundrtips to/from client
	row processed
	sort정보 인메모리/디스크 / sorts(memory) sorts(disk)
	redo 사이즈
	ROWS
	BYTES
	COST(CPU)
	TIME

- 다른 방법과는 다르게 redo사이즈와 sorts 정보가 있다.

---

3. SQL TRACE(10046)

	SQL TRACE(10046?)
문법	ALTER SESSION SET sql_trace = true; <SQL>; ALTER SESSION SET sql_trace = false;
비고	TKProf이용하여 본다
주요 출력 정보	disk : 물리 io 수 row 소스 오퍼레이션 : pr
	query : 논리 io consistent 모드로 읽기 수 row 소스 오퍼레이션 : cr
	current : 논리 io current 모드 읽기 수
	fetch count
	fetch rows
	Misses in library cache ~ : 하드파싱 수

- 가장 디테일한 정보를 얻을수 있는 방법

- 주의할점은 논리적 I/O 수를 계산할때 query + current는 disk 수까지 포함된 값이다.

BCHR (버퍼캐시 히트율) 계산시 주의!

- TKProf 는 Oracle에 생기는 .trc 트레이스 파일을 보기쉽게 포매팅해주는 유틸리티라고 한다 Trace Kernal Profile?

---

4. Shared Pool SQL Trace

	Shared pool SQL TRACE
문법	SELECT /*+ GATHER_PLAN_STATISTICS */ ~; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADAVNCED ALLSTATS LAST'));
비고	라이브러리 캐시에 캐싱되어있는 정보를 조회
주요 출력 정보	predicate information(조건절 정보)
	E-Rows 예상로우
	A-Rows 실제로우 A-Time 실제시간

---

% 출력정보 비교

실행계획	AUTOTRACE	SQL TRACE(10046?)	Shared pool SQL TRACE
predicate information (조건절 정보)	predicate information (조건절 정보)		predicate information (조건절 정보)
	recursive calls
	physical read	disk : 물리io수 row 소스 오퍼레이션 : pr
	consistent gets	query : cons 모드 io수 row 소스 오퍼레이션 : cr
	db block gets	current : current 모드 읽기수
	SQL*NET roundrtips to/from client	fetch count
	row processed	fetch rows
		Misses in library cache ~ : 하드파싱 수
	sort정보 인메모리/디스크
	redo 사이즈
ROWS	ROWS		E-Rows 예상로우
			A-Rows 실제로우 A-Time 실제시간
BYTES	BYTES
COST(CPU)	COST(CPU)
TIME	TIME

autotrace 의 ROWS는 실제 로우수인지 예상로우수인지는 불확실하다.

ON 이면 A-ROWS와 대응되고 TRACEONLY면 E-ROW와 대응될것 같다...