튜닝과 치료(157p)

• 문제 발생
• 문제점 파악
  1) Explain Plan으로 실행계획 확인(X-Ray 촬영)
  2) AutoTrace로 실제 일량 측정(CT 촬영)
  3) SQL 트레이스로 내부 수행 절차의 부하 발생 단계 확인(MRI촬영)

• 문제점 해결
  1) 쿼리 변환(외용약 처방), 옵티마이져 힌트사용(내복약 처방)
  2) 인덱스 조정(시술)
  3) 반정규화, 집계 테이블 생성(수술)

Explain plan(158p~160p)

(1) plan_table 생성하기

• explain plan : SQL을 수행하기 전에 실행계획을 확인 할 때 사용하는 명령어
• explain plan 을 사용하기 위해서는 plan_table을 생성해야한다.

SQL> @?/rdbms/admin/utlxplan.sql   -- ? 는 $ORACLE HOME 디렉토리 대체하는 기호

 
SQL> SELECT owner, synonym_name, table_owner, table_name
  2    FROM all_synonyms
  3   WHERE synonym_name = 'PLAN_TABLE'
  4  ;

OWNER    SYNONYM_NAME    TABLE_OWNER    TABLE_NAME
------- -------------- ---------------- -----------------
PUBLIC   PLAN_TABLE      SYS            PLAN_TABLE$               

• explain plan for 명령을 수행하고 나면 해당 SQL에 대한 실행계획이 plan_table 에 저장된다.

 
SQL> explain plan set statement id = 'queryl' for  --  statement id = 'queryl'  생략가능
   2 select * from emp where empno = 7900;

해석되었습니다.

h5.(2) plan_table 포맷팅

• plan_table에 저장된 정보를 포맷팅 SQL
• AutoTrace 또는 TOAD에서 실행 계획 출력시 명령 수행 후 plan_table의 데이터를 포맷팅해서 출력.

 
SQL> SELECT lpad(id, 4, ' ') || NVL(LPAD(parent_id, 6, ' '), '       ')
  2      || ' ' || lpad(' ', (LEVEL - 1) * 2, ' ')
  3      || operation || NVL2(options, ' ( ' || options || ' ) ', '')
  4      || NVL2(object_name, ' OF '''
  5      || object_owner || '.' || object_name, NULL)
  6      || NVL2(object_name, '''', '')
  7      || decode(parent_id, NULL, ' Optimizer=' || optimizer)
  8      || (CASE
  9            WHEN cost IS NULL AND cardinality IS NULL AND bytes IS NULL
 10            THEN ''
 11            ELSE '(' || NVL2(cost, 'Cost=' || cost, '')
 12                     || NVL2(cardinality, 'Card=' || cardinality, '')
 13                     || NVL2(bytes, 'Bytes=' || bytes, '')
 14                     || ')' END) "Execution Plan"
 15  FROM   plan_table p
 16  START WITH statement_id = 'query1' AND id = 0
 17  CONNECT BY PRIOR id = parent_id AND PRIOR statement_id = statement_id
 18  ORDER BY id;


Execution Plan
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=32)
1  0  TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=32)
2  1   INDEX (UNIQUE SCAN) OF 'EMP_PK' (Cost=0 Card=1)

• utlxpls.sql / utlxplp.sql 사용 (9i 부터 사용가능)

SQL> SET LINESIZE 200
SQL> @?/rdbms/admin/utlxpls

---------------------------------------------------------------------------------------
| Id | Operation                    | Name   | Rows | Bytes | Cost ( %CPU) |
|  0 | SELECT STATEMENT             |        |    1 |    32 |    1     (0) |
|  1 |  TABLE ACCESS BY INDEX ROWID | EMP    |    1 |    32 |    1     (0) |
|* 2 |   INDEX UNIQUE SCAN          | EMP_PK |    1 |       |    0     (0) |
---------------------------------------------------------------------------------------

PLAN TABLE OUTPUT
------------------------------
Plan hash value : 4024650034

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

(3) SQL Repository 실행계획 모니터링

• SQL Repository의 SQL에 대해 매일 explain plan 명령 수행하여 별도 테이블에 실행계획을 저장해 두면 안정적인 시스템 운영 및 성능관리에 활용 가능함.

예를 들어，인텍스 구조를 바꾸고자 할 때 해당 인텍스를 시용하는 쿼리 목록을 뽑아 사 전점검을 실시할 수 있다. 또는 오브젝트 통계정보가 바뀌어 어느 날 갑자기 성능이 나빠진 쿼리가 생겼을 때 이전 실행계획을 빨리 확인하고 예전과 같은 방식으로 수행되도록 빠르게 튜닝할 수 있다.

• sql_repository에 저장된 SQL을 sql_plan_repository라고 명명된 plan table에 실행계획을 저장하는 스크립트 예시.
  {warning}
  sql_repository 테이블은 쉽게 예시하려고 만든 것일 뿐이며, 실제 운영 환경에서 SQL 문장을 테이블에 담아두고 수행할때마다 "매번 실시간으로" 읽어오는 방식을 사용하는 것은 금물.
  {warning}

  CREATE TABLE SQL repository(SQL id VARCHAR2(30), SQL text VARCHAR2(4000));
  BEGIN
    FOR c IN (SELECT sql_id, sql_text frαn sql_repository) 
    LOOP
      EXECUTE IMMEDIATE 'explain plan set statement_id = ''' || c.sql_id 
              || ''' into sql_plan_repository' 
              || ' for ' || c.sql_text;
      COMMIT;
    END LOOP;
  END;
  /