Chaptr 03. 오라클 성능 관리

Intro.

Explain plan

SQL문을 분석하고 해석하여, Execution Plan(실행계획)을 수립하여 Plan_Table(실행계획테이블)에 저장해주는 명령어

옵티마이저는 SQL문이 실행될 때마다 우선 Execution Plan을 작성하여 Plan_Table에 저장
사용자는 Plan_Table에 저장된 Execution Plan을 통하여 SQL문에 대한 옵티마이저의 Access하는 경로를 확인

• Execution Plan
  SQL문장이 요구한 데이터를 추출하기 위해 오라클(DBMS)이 차례로 수행하는 작업방법
  즉, 오라클 옵티마이저에 의해 DML이 테이블 인덱스 등에 대해서 Access하는 경로를 보여줌.

• Plan_Table
  oracle 10g 부터 설치 시 기본적으로 생성 - sys.plan_table$
  이전 버전은 계정별로 별도로 Plan_Table을 생성해야 했다.
  SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql //Plan_Table 생성

'$ORACLE_HOME'을 '?'로 사용할 수 있다.

• explain plan 사용

SQL > set linesize 200 //보여질 넓이 값 조정
SQL > Explain plan set statement_id ='query1' for //explain plan 명령 사용
2     select * from emp where empno = 7900; 

해석되었습니다.

SQL > @?/rdbms/admin/utlxpls  //9i부터 plan_table에서 읽은 데이터를 포맷팅해서 출력

plan_table_output
 -----------------------------------------------------------------------------  
Plan hash value: 4024650034
 
 -----------------------------------------------------------------------------  
|ID | Operation                         | Name | Rows | Bytes | Cost (%CPU)  |
 ----------------------------------------------------------------------------- 
|  0| SELECT STATEMENT                  |      |      |     32|     1     (0)|
|  1|  TABLE ACCESS BY INDEX ROWID      |EMP   |     1|     32|     1     (0)|
|* 2|   INDEX UNIQUE SCAN               |EMP_PK|     1|       |     0     (0)|
 -----------------------------------------------------------------------------

 -----------------------------------------------------------------------------
Predicate Information (identified by operation id):
 -----------------------------------------------------------------------------
  2- access("EMPNO"=7900)

1. 과거
   plan_table 의 정보를 포맷팅하기 위해선 별도 SQL 요구(p.159참조) ☞ 스크립트로 저장 후 호출

1. 현재
   1. 내부적으로 명령 수행 처리
   2. plan_table의 정보 포맷팅
   3. Autotrace 및 쿼리 툴에서 실행계획 출력

1. 시스템 운영 및 성능관리
   1. SQL을 하드코딩으로 하지 않고 XML 포맷으로 별도 Repository에 저장
   2. SQL Repository 저장 SQL에 대해 매일 explain plan 명령을 수행
   3. 실행계획을 별도 테이블로 저장 및 활용

참조문서

서적(오라클 성능 고도화 원리와해법 I) : http://book.daum.net/detail/book.do?bookid=KOR9788996246015