차례
1. 튜닝의 기본
2. INDEX
3. JOIN
4. 함수
Oracle SID FATE 예로 설정
Oracle 제공하는 OPTIMIZER 라는 프로세스를 사용하여 튜닝
튜닝의 기본
영향요소, 목적, 주체, 시기, 목표 설정 하고 시스템계획, 분석/설계(Table간 자동화 규칙, 적합한 인덱스 정의), 개발(옵티마이저, 최적화, 재사용 정립,표준 준수,테이블, 행단위 잠금설정, 유틸리티), 검수, 운영
1. Explain Plan
SQL 문을 분석하고 해석하여 실행계획(execution, SQL이 요구한 테이터를 추출하기 위해 오라클이 차례로 수행하는 작업 방법)을 수립, 실행계획 테이블(PLAN_TABLE)에 저장해주는 명령
1) 실행계획 테이블(PLAN_TABLE) 생성
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on 화 Apr 29 17:05:10 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> conn scott/tiger@FATE
연결되었습니다.
SQL> show user
USER은 "SCOTT"입니다
SQL> @D:\ORACLE\ora81\RDBMS\admin\utlxplan.sql테이블이 생성되었습니다.
SQL> DESC PLAN_TABLE 이름 널 유형
----------------------------------------- -------- ---------------------------
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
D:\ORACLE\ora81\RDBMS\admin\utlxplan.sql rem
rem $Header: utlxplan.sql big_dev/0 11-aug-95.14:02:45 achaudhr Exp $ xplainpl.sql
rem
Rem Copyright (c) 1988, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM UTLXPLAN.SQL
Rem FUNCTION
Rem NOTES
Rem MODIFIED
Rem mzait 02/19/98 - add distribution method column
Rem ddas 05/17/96 - change search_columns to number
Rem achaudhr 07/23/95 - PTI: Add columns partition_{start, stop, id}
Rem glumpkin 08/25/94 - new optimizer fields
Rem jcohen 11/05/93 - merge changes from branch 1.1.710.1 - 9/24
Rem jcohen 09/24/93 - #163783 add optimizer column
Rem glumpkin 10/25/92 - Renamed from XPLAINPL.SQL
Rem jcohen 05/22/92 - #79645 - set node width to 128 (M_XDBI in gendef)
Rem rlim 04/29/91 - change char to varchar2
Rem Peeler 10/19/88 - Creation
Rem
Rem This is the format for the table that is used by the EXPLAIN PLAN
Rem statement. The explain statement requires the presence of this
Rem table in order to store the descriptions of the row sources.
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30));
* 실행계획(Execution Plan)
SQL 문이 요구한 데이타를 추출하기위해 DMBS가 차례로 수행하는 방법을 말하는데, 오라클 옵티마이저에 의해서 SELECT, UPDATE, INSERT 그리고 DELETE문이 테이블 인덱스등에 대해서 ACCESS 하는 경로를 보여줌
옵티마이저가 DMBS를 Access 하고 경로를 작성한 계획으로서 어떤 Table 또는 Index 를 제일 먼저 Access 하고 다음으로 어떤것을 Access 하고 하는 등등의 계획, 반드시 PLAN_TABLE 이 있어야 가능하고 분석은 가장 우측의 것이 최 우선, 우측의 Depth 같은 경우 상위것이 우선함
2) EXPLAIN PLAN 실행
EXPLAIN PLAN [SET STATEMENT_ID = 'Description']
[INTO TABLE_NAME]
FOR SQL_Statement
EXPLAIN PLAN SET STATEMENT_ID = 'TEMP_1'
INTO PLAN_TABLE FOR
SELECT A.EMPNO, A.ENAME, NVL(A.SAL,0), B.DNAME
FROM EMP A, DEPT B
WHERE A.EMPNO = 10
AND A.DEPTNO = B.DEPTNO
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TEMP_1'
2 INTO PLAN_TABLE FOR
3 SELECT A.EMPNO, A.ENAME, NVL(A.SAL,0), B.DNAME
4 FROM EMP A, DEPT B
5 WHERE A.EMPNO = 10
6 AND A.DEPTNO = B.DEPTNO
7 /
해석되었습니다.
3) PLAN_TABLE 확인
SELECT ID, PARENT_ID P_ID,
LPAD('',2*(LEVEL-1)) || OPERATION OPERATION,
OPTIONS, OBJECT_NAME
FROM PLAN_TABLE
START WITH ID = 0 AND STATEMENT_ID = 'TEMP_1'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEMP_1'
출력 결과 한줄에 보기좋게 설정
COLUMN OPERATION FORMAT A25
COLUMN OPTIONS FORMAT A20
COLUMN OBJECT_NAME FORMAT A20
COLUMN ID FORMAT 999
COLUMN P_ID FORMAT 999
SQL> COLUMN OPERATION FORMAT A25
SQL> COLUMN OPERATION FORMAT A25
SQL> COLUMN OPTIONS FORMAT A20
SQL> COLUMN OBJECT_NAME FORMAT A20
SQL> COLUMN ID FORMAT 999
SQL> COLUMN P_ID FORMAT 999
SQL> SELECT ID, PARENT_ID P_ID,
2 LPAD('',2*(LEVEL-1)) || OPERATION OPERATION,
3 OPTIONS, OBJECT_NAME
4 FROM PLAN_TABLE
5 START WITH ID = 0 AND STATEMENT_ID = 'TEMP_1'
6 CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEMP_1'
7 /
ID P_ID OPERATION OPTIONS OBJECT_NAME
---- ---- ------------------------- -------------------- --------------------
0 SELECT STATEMENT
1 0 NESTED LOOPS
2 1 TABLE ACCESS BY INDEX ROWID EMP
3 2 INDEX UNIQUE SCAN PK_EMP
4 1 TABLE ACCESS BY INDEX ROWID DEPT
5 4 INDEX UNIQUE SCAN PK_DEPT
6 개의 행이 선택되었습니다.
4) TRACE FILE
위와 같이 하나의 SQL 문장에 대한것이 아닌 시스템 전체 혹은 하나의 세션에 발생하는 모든 SQL 문에 대한 실행 계획을 알고자 할때 Trace File 을 만들어 분석하고 tkprof 라는 툴을 사용해서 분석
시스템 전체 적용
D:\Oracle\ora81\database\initFATE.ora (initSID.ora)
SQL_TRACE = TRUE
TIMED_STATISTICS = TRUE
USER_DUMP_DEST = Directory
MAX_DUMP_DEST = number
종료후 TRUE 를 FALSE 수정후 DB Restart
세션별 적용
전체 적용 불가능시 해당 세션만 적용
ALTER SESSION SET SQL_TRACE = TRUE
ALTER SESSION SET TIMED_STATISTICS = TRUE
trace 파일은 보통 $ORACLE_HOME/admin/$ORACLE_SID/udump 에 생성
5) TRACE FILE 분석
tkprof tracefile outfile [explain= ] [table= ] [print= ] [sys= ] [sort= ]
explain : explain=userid/passwd 로 connect 시 사용된 user 및 password
sys : sys=no system에 관련자료 미포함
6) SQLPLUS 를 이용한 Trace 분석
SET AUTOTRACE TRACE EXPLAIN : 실행하지는 않고 통계정보만 보여줌
SET AUTOTRACE ON : 실행하여 결과물과 통계 정보 다보여줌
SET AUTOTRACE OFF
2. OPTIMIZER
SQL 문에 대하여 이를 해석하고 실행계획(Execution Plan)을 수립하는 오라클 프로세스로 최적의 경로를 이용하지 않았을 경우 최적의 경로로 Access 할수 있도록 하는것
Rule Based Optimizer(RBO, 규칙 기반 최적화)
DB 에 접근하는 경로를 찾는데 사전 정의된 규칙을 사용, 예측, 통제 가능하고 가장 낮은 순위의 실행 계획을 항상 사용하고, 통계정보는 사용하지 않고 비용 계산도 고려 하지 않고 규칙 우선
Rank | Access Path |
1 | Single row by ROWID |
2 | Single row by cluster join |
3 | Single row by hash cluster with unique or primary key |
4 | Single row by unique or primary key |
5 | Cluster join |
6 | Hash cluster key |
7 | Indexed cluster key |
8 | Composite key |
9 | Single column indexes |
10 | Bounded range search on indexed columns |
11 | Unbounded range search on indexed columns |
12 | Sort merge join |
13 | MAX or MIN of indexed columns |
14 | ORDER BY on indexed columns |
15 | Full table scan |
Cost Based Optimizer(CBO, 비용 기반 최적화)
DB 정보를 활용, 생성된 테이블을 ANALYZER 명령으로 분석, 과부하 요소를 찾고 cost가 적은 실행 계획을 선택 하는데, Cost 란 각각의 소요시간(Elapsed time)을 말한다
Optimizer Modes 설정 방법
시스템 전체 (InitSID.ora)
Optimizer Mode = CHOOSE, ALL_ROWS, FIRST_ROWS, RULE
세션에서 지정
ALTER SESSION SET OPTIMIZE_MODE = CHOOSE, ALL_ROWS, FIRST_ROWS, RULE
SQL 문장에서 지정(Hint 사용)
SELECT /* CHOOSE, ALL_ROWS, FIRST_ROWS, RULE */
통계정보 만들기(ANALYZER)
ANALYZER 란 테이블, 인덱스에 대한 통게정보를 DBMS가 저장함으로써 Optimizer가 CBO Mode 에서 참고할수 있는 정보 제공하고 이를 이용하여 각 Object 의 구조를 확인 하고 Chain 생성 여부를 확인 할수 있으므로 시스템의 저장공간 관리를 효줄적으로 함
ANALYZE TABLE(or INDEX) COMPUTE,ESTIMATE,DELETE STATISTICS
SQL_Trace
SQL_TRACE 설정 -> 세션별 실행통계->TRACE 파일 생성-> TKPROF->출력
Init.ora 의 파라미터를 이용하여 설정, 세션단위도 가능
TKPROF
TRACE 파일을 분석 가능한 파일 형식으로 출력, SQL 문장분석, 시스템 이상 분석
Access Path
SQL문에서 WHERE 조건문의 형태를 분석하는 자료
주요 컬럼 조사 기록, 인덱스 검정및 클러스트링 검토 중요 자료
댓글 없음:
댓글 쓰기