오라클 SQL 튜닝 & 서버 튜닝 #1

Orientation

(1) 이곳에 작성하는 내용은 http://dbguide.net/ 에서의 오라클 튜닝 교육에 대한 정리입니다.
본문 내용은 강의자료와 “알기 쉽게 해설한 오라클 SQL 튜닝 & 서버 튜닝” 책자의 내용이 함께 들어 있으므로
저작권 문제 발생시 비공개가 될 수도 있습니다.

(2) 첨부된 이미지들은 구글링하여 얻은 자료이므로 문제가 된다면 삭제합니다.

(3) 실습환경 설치

(4) 실습파일 설치 : NEW_SCOTT_10.DMP (최대 압축해도 138MB.. 첨부 불가.. T.T)

Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\edu>sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on 월 6월 2 12:35:02 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

사용자명 입력: system
암호 입력:

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> drop user scott cascade;

사용자가 삭제되었습니다.

SQL> create user scott identified by tiger;

사용자가 생성되었습니다.

SQL> grant connect, resource, dba to scott;

권한이 부여되었습니다.

SQL> exit
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options에서 분리되었습니다.

C:\Documents and Settings\edu>cd C:\oracle\product\10.2.0\db_1\BIN

C:\oracle\product\10.2.0\db_1\BIN>imp system/manager full=y file=/NEW_SCOTT_10.DMP

Import: Release 10.2.0.1.0 – Production on 월 6월 2 13:38:37 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

엑스포트 파일은 규정 경로를 거쳐 EXPORT:V10.02.01 에 의해 생성되었습니다
KO16MSWIN949 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다
. SYSTEM 객체를 SYSTEM(으)로 임포트하는 중입니다
. SCOTT 객체를 SCOTT(으)로 임포트하는 중입니다
IMP-00008: 엑스포트 파일에 인식할 수 없는 명령이 있습니다:

. . 테이블                      “ACCOUNT”(를)을 임포트 중      28969 행이 임포트되었습니다
. . 테이블                     “BIG_DEPT”(를)을 임포트 중        289 행이 임포트되었습니다
. . 테이블                      “BIG_EMP”(를)을 임포트 중      28955 행이 임포트되었습니다
. . 테이블                         “DEPT”(를)을 임포트 중          4 행이 임포트되었습니다
. . 테이블                          “EMP”(를)을 임포트 중         14 행이 임포트되었습니다
. . 테이블                “LARGE_ACCOUNT”(를)을 임포트 중     460938 행이 임포트되었습니다
. . 테이블                   “LARGE_DEPT”(를)을 임포트 중        289 행이 임포트되었습니다
. . 테이블                    “LARGE_EMP”(를)을 임포트 중    1091410 행이 임포트되었습니다
. . 테이블                   “PLAN_TABLE”(를)을 임포트 중          0 행이 임포트되었습니다
. . 테이블                    “S_CHANGGO”(를)을 임포트 중          3 행이 임포트되었습니다
. . 테이블                 “S_CHULHAJISI”(를)을 임포트 중     802095 행이 임포트되었습니다
. . 테이블                     “S_GOGAEK”(를)을 임포트 중     378789 행이 임포트되었습니다
. . 테이블                      “S_JAEGO”(를)을 임포트 중     274276 행이 임포트되었습니다
. . 테이블                     “S_MAECHE”(를)을 임포트 중        140 행이 임포트되었습니다
. . 테이블                  “S_PROMOTION”(를)을 임포트 중       1063 행이 임포트되었습니다
. . 테이블                    “S_SANGPUM”(를)을 임포트 중        592 행이 임포트되었습니다
. . 테이블                    “S_SPINOUT”(를)을 임포트 중     154673 행이 임포트되었습니다
. . 테이블                       “S_SUJU”(를)을 임포트 중     827447 행이 임포트되었습니다
. . 테이블                   “S_SUJU_DTL”(를)을 임포트 중    4136970 행이 임포트되었습니다
. . 테이블                    “S_ZIPCODE”(를)을 임포트 중      49033 행이 임포트되었습니다
C:\oracle\product\10.2.0\db_1\BIN>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 – Production on 월 6월 2 13:43:48 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
ACCOUNT                        TABLE
BIG_DEPT                       TABLE
BIG_EMP                        TABLE
DEPT                           TABLE
EMP                            TABLE
LARGE_ACCOUNT                  TABLE
LARGE_DEPT                     TABLE
LARGE_EMP                      TABLE
PLAN_TABLE                     TABLE
S_CHANGGO                      TABLE
S_CHULHAJISI                   TABLE

S_GOGAEK                       TABLE
S_JAEGO                        TABLE
S_MAECHE                       TABLE
S_PROMOTION                    TABLE
S_SANGPUM                      TABLE
S_SPINOUT                      TABLE
S_SUJU                         TABLE
S_SUJU_DTL                     TABLE
S_ZIPCODE                      TABLE

20 개의 행이 선택되었습니다.

 

튜닝의 개요

(1) 성능을 저하시키는 기업의 문제점

  • 결과물 위주의 개발기법과 개발기간 : 충분치 못한 분석&설계 > 막바지에 업무 몰림
  • 충분하지 못한 비용 : 기간, 인력
  • 개발자의 RDBMS에 대한 기술력의 한계 : 대부분의 개발자가 초급 기술자 (결과물 위주)
  • 적절하지 못한 H/W, DBMS 도입 : 예산낭비
  • 실패에 대한 두려움 : 자신감 & 도전정신 필요

(2) 좋은 성능을 위한 요소

  • 관계형 데이터베이스에 맞는 시스템 설계 : 데이터베이스 관리 시스템 종류 많음(네트워크형, 계층형, 관계형…)
  • 시행착오를 줄이고 생산성을 향상시킬 수 있는 분석/설계자 : 리더쉽
  • SQL에 대한 명확한 기술을 확보한 개발자 : 대부분 개발자들이 결과에만 관심두고 실행원리에 무관심

 

튜닝의 개념

(1) 튜닝의 개념

  • 좋은 차라도 제대로 운전해야
  • 좋은 악기라도 조율(튜닝)되어 있어야
  • 좋은 오라클이라도 튜닝을 통해 성능을 향상해야

(2) 튜닝 방법론

  • Step-1 : 준비 단계 (고객인터뷰 > 자료 수집/분석 > 튜닝계획 수립 > 고객 인터뷰)
  • Step-2 : 튜닝분석 단계 (디자인튜닝 > App튜닝 > 서버튜닝 > 문제점 분석 > 튜닝대상 적용 > 고객인터뷰)
  • Step-3 : 결과 단계 (튜닝후 자료 수집/분석 > 튜닝 결과 평가 > 산출물 작성 > 고객인터뷰)

(3) 튜닝 절차

1) 비지니스 튜닝 : 비지니스 룰 튜닝(e.g. 10개 공정을 7개로)
2) 디자인 튜닝 : 데이타베이스의 논리적 튜닝 (테이블 구조/크기, 인덱스 여부/종류)
3) Application 튜닝 : SQL문을 분석하여 성능을 개선
4) 서버 튜닝 : 데이터베이스의 메모리 영역과 물리적 구조 튜닝
5) System 튜닝 : OS 튜닝

  • 개발시에는 1 –> 5 순으로, 개발후에는 5 –>1 순으로
  • 어떤 단계의 튜닝은 다음 단계에 영향 미침
  • 튜닝 전/후의 기록을 반드시 남길것 (반복적으로 튜닝하다 보면 이전 결과를 기억하기 어려움)
  • 데이터베이스 관리자는 서버튜닝을, 개발자는 Application 튜닝을, 분석/설계자는 디자인 튜닝을 담당

(4) 성능을 위해 고려할 사항

  • 인덱스 활용
  • 액세스 경로의 최적화
  • JOIN의 최적화
  • 클러스터 기법의 활용
  • ARRAY PROCESSING
  • 뷰의 효과적 활용
  • 적절한 데이터 타입의 사용
  • PL/SQL 활용
  • 반 정규화
  • Parallel_Query
  • 메모리의 최적 활용
  • 멀티블럭 처리
  • 경합의 감소
  • 공유 서버 프로세스의 활용
  • 디스크 I/O의 분산
  • LOCKING의 해결

 

오라클 데이터베이스의 구조

(1) 오라클 데이터베이스의 구조

사용자 삽입 이미지사용자 삽입 이미지

  • 프로세스 영역 : Background Process, User Process, Server Process
  • 메모리 영역 : Shared Pool, Data Buffer, Log Buffer, Large Pool
    • SGA : SQL에 의 해 검색 또는 변경되는 테이블의 데이터를 임시 저장
    • Shared Pool Area : “문법 확인 > 테이블 확인 > 실행계획 수립” 내용을 저장
      • Library Cache : 실행코드, 실행계획
      • Data Dictionary Cache : 자료사전 데이블, 뷰 정보
    • Data Buffer Cache Area : SQL의 테이블을 데이터 파일로 부터 읽어서 저장
    • Log Buffer Area : DML 실행시 Commit한 내용, Redo-Log File에 저장됨
    • Large Pool Area : 8.x 이후 버전, 복구관리자 or 공유서버 프로세스 사용시
  • 파일 영역 : Control Files, Parameter Files, Data Files, Redo-Log Files
  • PGA (Program Global Area) : 유저 프로세스를 처리하는 서버 프로세스에서 생성하여 사용하는 메모리 영역
  • SGA (System Global Area) : 디스크에서 읽혀진 데이터가 저장되고 그 데이터들이 읽혀지거나 변경되어지는 작업에 사용되는 공용 메모리 영역
  • UGA (User Global Area) : PGA의 부분 집합(정렬 공간, 세션 정보, 커서 상태 정보)

 

(2) SELECT문의 처리 과정

  • 구문 분석 : syntax check(문법이 맞는지), symantics check(table 및 컬럼들이 존재하는지 Data Dictonary table에서 검색 –> Recursive-SQL), 권한 check, 실행계획 수립, shared pool 저장 (인터프리트 방식의 단점 극복위해 파싱전 같은 SQL 있는지 검사)
  • 실행 단계 : Data Buffer Cache 조사해서 없으면 file에서 해당 테이블 읽어옴, DML 실행시 Data Buffer Cache에서 변경/삭제/입력 됨
  • 인출 단계 : SELECT
  • 참조 : http://www.dbguide.net/dbqa/oracle_tune/structure_point/commit.jsp?catenum=6

사용자 삽입 이미지

 

(3) DML 문의 처리과정

  • 구문분석
  • file에서 해당 테이블 읽고, 복구를 위해 Undo Segment의 빈 블록을 읽음
  • 테이블Undo SegmentData Buffer Cache에 넣고 변경하려는 행을 Lock
  • Log Buffer Area에 변경 전/후의 정보 저장됨 (이는 실시간으로 Redo Log Files에 저장됨)

사용자 삽입 이미지
(4) COMMIT 문의 처리과정
사용자 삽입 이미지

 

(5) 저장 구조

  • The Logical Structures
    • Data Blocks
    • Extents
    • Segments
    • Tablespaces

사용자 삽입 이미지

  • The Physical Structures
    • Data Files
    • Control Files : initSID.ora
    • Redo Log Files
    • Initialization Files : INIT.ora (db_block_size, control_files, db_name 등 정보 포함)
    • Password File
    • Alert Log File
  • oracle 설치시 기본 tablespace 확인
    • C:\oracle\product\10.2.0\oradata\orcl\SYSTEM01.DBF : table dictonary tablespace
    • C:\oracle\product\10.2.0\oradata\orcl\TEMP01.DBF : temp segment tablespace
    • C:\oracle\product\10.2.0\oradata\orcl\UNDOTBS01.DBF : undo segment tablespace
  • 용어 정리
    • $ORACLE_HOME : 홈디렉토리
    • $ORACLE_SID :  System Identifier (데이타베이스 이름)
  • Tablespace 설계
    • MIS 테이블 스페이스 하나 사용 : 중소규모 시스템 설계시
    • INSA, ACCOUNT, INVENTORY 등 테이블스페이스 여러개 사용 : 대규모 시스템
  • 물리적 디자인과 성능
    • 사용빈도 많은 것과 적은것을 하나의 디스크에 배치하여 부하분산
  • Tablespace 단편화
    • 분석하기 : percent_extents_coalesced 값이 100(%)가 되는지
    • 빈공간 합병하기 : percent_extents_coalesced 값이 100(%)가 될때까지 아래를 실행

사용자 삽입 이미지

 

 

튜닝도구 : SET AUTOTRACE

(1) set autotrace

  • 7.3 이후 추가됨
  • 수행절차 : plan 테이블 생성 > 실행계획 분석 > select하여 결과 참조
  • 반드시 plan 테이블이 최초에 한번은 생성되어야 함
  • 문법 : set autotrace [on|off|trace|traceonly]

(2) plan_table 생성

C:\Users\user>sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.3.0 – Production on 화 6월 10 20:58:20 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> @d:/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql
SQL> drop role plustrace;

롤이 삭제되었습니다.

SQL> create role plustrace;

롤이 생성되었습니다.

SQL>
SQL> grant select on v_$sesstat to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$mystat to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL> set echo off
SQL> grant plustrace to scott;

권한이 부여되었습니다.

SQL> connect scott/tiger
연결되었습니다.
SQL> @d:/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql

테이블이 생성되었습니다.

 

(3) set autotrace 실행결과

SQL> set autotrace trace
SQL> select * from big_emp where deptno=10;

5336 개의 행이 선택되었습니다.

Execution Plan
———————————————————-
Plan hash value: 3814977537

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         |   294 | 11760 |    43   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIG_EMP |   294 | 11760 |    43   (5)| 00:00:01 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“DEPTNO”=10)

Statistics
———————————————————-
0  recursive calls –> symentics 수행시 자동으로 실행되는 내부 SQL
0  db block gets
–> physical read, 0 이면 이전의 같은실행결과를 buffer cache에서 가져옴
        539  consistent gets
–> DML의 경우 “변경후” 값이 저장되는 크기
          0  physical reads
–> 물리적 read, 위의 2개보다 보통은 작다.(메모리가 작다면 더 크다)
          0  redo size
256694  bytes sent via SQL*Net to client –> SQL + 기타정보들이 서버로…
       4290  bytes received via SQL*Net from client –> 클라이언트로 보낸 양
357  SQL*Net roundtrips to/from client –> 357번 왔다갔다..
0  sorts (memory) –> sort시 사용된 메모리 공간 (PGA)
0  sorts (disk) –> Temp Segment Table에 저장된 내용, 여기가 크다면 PGA가 부족하다는 뜻
–> 튜닝방법 : PGA 공간 크게 : alter session set sort_area_size=10M;
5336  rows processed

  • sqlplus : 오라클서버와 클라이언트 입출력 환경 제공
  • sql*net : 오라클서버와 클라이언트간의 통신담당 (API)
  • 사용자 접속마다 PGA 생성됨, 500k 정도 메모리 크기, sort된 결과를 저장 –> temp segment table (file) 에 임시 저장함

 

(4) 실행계획 읽는 법 #1

SQL> set autotrace on
SQL> select ename, job, sal, dname
2  from emp, dept
3  where emp.deptno = dept.deptno;

ENAME      JOB              SAL DNAME
———- ——— ———- ————–
SMITH      CLERK            800 RESEARCH
ALLEN      SALESMAN        1600 SALES

14 개의 행이 선택되었습니다.

Execution Plan
———————————————————-
Plan hash value: 615168685

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |    14 |   854 |     5  (20)| 00:00:01 |  –> (4)
|*  1 |  HASH JOIN         |      |    14 |   854 |     5  (20)| 00:00:01 |  –> (3)
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     2   (0)| 00:00:01 |  –> (1)
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     2   (0)| 00:00:01 |  –> (2)
—————————————————————————

동일레벨이면 먼저 나온 operation 부터
상/하위 레벨이면 하위 operation 부터
Join 종류 ; hash, sort-merge, nested-loop

 

Predicate Information (identified by operation id):
—————————————————

   1 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
456  recursive calls
0  db block gets
69  consistent gets
6  physical reads
0  redo size
996  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
10  sorts (memory)
0  sorts (disk)
14  rows processed

 

(5) 실행계획 읽는 법 #2

ID    OPERATION           OPTIONS        OBJECT_NAME
————————————————————
0     SELECT STATEMENT
1       FILTER
2         NESTED LOOPS
3           TABLE ACCESS  FULL           EMPLOYEES
4           TABLE ACCESS  BY ROWID       DEPARTMENTS
5             INDEX       UNIQUE SCAN    PK_DEPARTMENT_ID
6         TABLE ACCESS    FULL           JOBS

사용자 삽입 이미지

 

(6) 실행계획상의 SQL연산

1. AND-EQUAL
– row operation
– 인덱스들에 의해 반환되는 rowid를 병합해서 공통값만을 반환2. CONCATENATION
– row operation
– 반환된 로우들의 합산하는 역할3. connect by
– 계층형 쿼리 실행되는 경우
– 성능을 위해 start with와 connect by절이 인덱스를 사용하여 풀리게 해야 함4. count
– row operation
– rownum pseudo column이 where이 아닌 select문에 위치할때 나타남
– currval, nextval, level, rowid, rownum5. count stopkey
– row operation
– where 절 안에서 조건자 구실을 할 때 나타남

6. filter
– where 절 조건절에서 인덱스 사용못할 때
– 해석은  nested loop와 동일

7. for update
– set operation
– select row에 대한 row-level lock걸게하는 연산
– transaction 종료시 종료

8. hash join
– row or set operation
– 바로 아래 테이블을 비트맵형식으로 메로리 로드 후, 해시 기법으로 조건 로우 추출
– 조인시에 두 테이블 중 한 테이블이 나머지 테이블에 비해 크기가 상당히 작고 메모리에 로드될 공간이 있을때
– 머지조인보다 성능이 나음
– 상위에 위치한 테이블의 사이즈가 작을때 좋은 성능을 낼 수 있다.
– use_hash 힌트는 ordered 힌트 구문과 같이 사용된다.

9. hash anti-join
– 한쪽 테이블의 로우를 추출할 때, 다른 나머지 테이블의 로우와는 매칭되지 않는 로우만 골라내는 조인
– not in/not exists/minus

10. hash semi-join
– exists 절을 이용하는 서브쿼리
– 지원 인덱스가 없는 exists 절을 사용하는 쿼리에서 hash_sj 또는 merge_sj 힌트

11. index range scan
– row operation
– 인덱스를 일정 범위의 값들로 제한을 두고 스캔
– <, >, between, like ‘a%’

12. index range scan descending
– 역순으로 인덱스블록 스캔
– 순서로 내림차순

13. index unique scan
– 유니크한 인덱스로부터 유니크한 값을 뽑아내는 로우 연산

14. inlist iterator
– in-list 절에 나오는 인수 갯수만큼 반복연산 수행

15. itersection
– set operation
– 여러 쿼리의 반환 결과셋을 병합하여 공통부분만을 추출
– intersect/union/minus sql연산자는 실행계획시에 항상 sort연산 동반
– union all 의 경우 sort 없음

16. merge join
– 테이블을 각각 따로 정렬한 후에 그 결과를 병합하는 집합연산
– 배치에서 효율적일 수 있음
– 조인 컬럼의 인덱스를 사용할 수 없을 때는 수립
– 실행계획상에 나타나는 테이블의 순서는 중요하지 않음

17. merge anti join

18. merge semi join

19. minus
– set operation
– 첫번째 쿼리 결과에서 두번째 쿼리 결과 제거

20. nested loops
– row operation
– 조인되는 컬럼 중에 적어도 하나에 인덱스가 존재할 때
– 상단 테이블이 driving테이블 아래 테이블이 probed table이 된다
– from절에 나오는 테이블의 순서에 따라 실행계획이 달라진다

 

튜닝도구 : SQL*TRACE

(1) 개요 : 실행되는 SQL의 통계자료를 Trace 파일로 생성

  • parsing, execute, fetch별로 구분
  • 사용CPU시간, 추출 소요시간
  • 물리적으로 읽은 블럭수 (파일)
  • 논리적으로 읽은 블럭수 (메모리)
  • 추출된 로우 수

(2) 사용하기 위한 parameter

SQL> connect as sysdba
사용자명 입력: system
암호 입력:
연결되었습니다.
SQL> show parameter timed_statistics

NAME                                 TYPE        VALUE
———————————— ———– ——————————
timed_statistics                     boolean     TRUE ==> true가 default, true 여야 sql*trace 사용가능
SQL> show parameter max_dump_file_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
max_dump_file_size                   string      UNLIMITED ==> trace dump 파일 최대 크기
SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
———————————— ———– ——————————
user_dump_dest                       string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP
==> 여기에 trace dump 파일이 생성됨

SQL> show parameter sql_trace

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sql_trace                            boolean     FALSE
==> false 가 아니면 모든 세션의 trace가 됨

 

(3) parameter 설정후 환경설정

SQL> connect as sysdba
사용자명 입력: system
암호 입력:
연결되었습니다.

SQL> startup force
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  281018368 bytes
Fixed Size                  1248528 bytes
Variable Size              88081136 bytes
Database Buffers          184549376 bytes
Redo Buffers                7139328 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

SQL> connect scott/tiger
연결되었습니다.
SQL> alter session set sql_trace = true;

세션이 변경되었습니다.

SQL> select * from big_emp where deptno = 01;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO G
———- ———- ——— ———- ——– ———- ———- ———- –
29709 SMITH      CLERK           7902 83/06/13     1654.5          1
     28709 SMITH      CLERK           7902 83/06/13     1654.5

ORA_xxxxx.trc 파일이 생성됨 ==> C:\oracle\product\10.2.0\db_1\BIN\tkprof.exe 로 분석이 가능함

 

(4) tkprof 로 분석하기 #1


tkprof 실행
C:\oracle\product\10.2.0\admin\orcl\udump>C:\oracle\product\10.2.0\db_1\BIN\tkprof orcl_ora_3160.trc kimstar.tkf sys=no explain=scott/tiger
(input file, output file Recursive-SQL 표시여부 실행계획 표시)

output file
TKPROF: Release 10.2.0.1.0 – Production on 월 6월 2 15:21:12 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.Trace file: orcl_ora_3160.trc
Sort options: default
********************************************************************************
count    = number of times OCI procedure was executed : 프로시저 실행 수
cpu      = cpu time in seconds executing : 순수한 cpu 사용시간
elapsed  = elapsed time in seconds executing : 다른놈도 cpu 사용하므로 총 걸린시간 (환경에 따라 elapsed 시간이 틀리므로 여러번 실행후 평균을 잡아야 할것임)
disk     = number of physical reads of buffers from disk : 디스크에서 읽은 버퍼수
query    = number of buffers gotten for consistent read : 메모리에서 가져온 버퍼수
current  = number of buffers gotten in current mode (usually for update) : DML이면 변경전 데이터를 읽은 수 (update, delete, insert 사용후 select 했을때)
rows     = number of rows processed by the fetch or execute call : 필터링되어 리턴된 row갯수
********************************************************************************

alter session set sql_trace = true

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  (SCOTT)

********************************************************************************

select *
from
big_emp where deptno = 01

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.03       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.01       0.03        183        188          0          35
——- ——  ——– ———- ———- ———- ———-  ———-
total        6      0.04       0.04        183        188          0          35

Misses in library cache during parse: 1 –> 0이면 소프트 파싱, 1이면 SGA Cache에 없어서 하드 파싱
Optimizer mode: ALL_ROWS –> 옵티마이저
Parsing user id: 61  (SCOTT) –> 사용자

Rows     Row Source Operation
——-  —————————————————
35  TABLE ACCESS FULL BIG_EMP (cr=188 pr=183 pw=0 time=11044 us)

Rows     Execution Plan –> 실행계획
——-  —————————————————
0  SELECT STATEMENT   MODE: ALL_ROWS
35   TABLE ACCESS   MODE: ANALYZED (FULL) OF ‘BIG_EMP’ (TABLE)

 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

parse count * 0.01 < parse cpu –> 라이브러리 캐쉬 영역이 작음을 의미 –> SHARED_POOL_SIZE 조절
parse count * 0.03 < parse disk –> 데이터 딕셔네리 영역이 작음을 의미 –> SHARED_POOL_SIZE 조절
(execute disk * fetch disk) < ((execute query + fetch query) + (execute current + fetch current) * 0.1) –> 데이터버퍼캐시 영역 작음 –> DB_CHACHE_SIZE 조절

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.03       0.01          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.01       0.03        183        188          0          35
——- ——  ——– ———- ———- ———- ———-  ———-
total        7      0.04       0.04        183        188          0          35

Misses in library cache during parse: 1
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse       12      0.01       0.00          0          0          0           0
Execute    123      0.09       0.07          0          0          0           0
Fetch      136      0.00       0.08         20        352          0         321
——- ——  ——– ———- ———- ———- ———-  ———-
total      271      0.10       0.16         20        352          0         321

Misses in library cache during parse: 12
Misses in library cache during execute: 12

    2  user  SQL statements in session.
123  internal SQL statements in session.
125  SQL statements in session.
1  statement EXPLAINed in this session.
********************************************************************************

아래는 trace 파일 생성에 대한 정보임

Trace file: orcl_ora_3160.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
2  user  SQL statements in trace file.
123  internal SQL statements in trace file.
125  SQL statements in trace file.
14  unique SQL statements in trace file.
1  SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
1080  lines in trace file.
12  elapsed seconds in trace file.

 

(5) tkprof 로 분석하기 #2

  • 전체 시간 분석 : 위와 같이 다양한 방법으로 쿼리를 통해 cpu 시간을 측정한다. (e.g. full table scan과 index-scan 비교)
  • Count 분석 : parse=execution=fetch 카운트 값이 같다면 루프문에서 반복실행됨을 의미 –> parsing을 재사용을 위해 커서사용
  • CPU 분석 : excute cpu time 많을때는 where 없거나 인덱스를 안타서 full scan 할때임, 거기다 order by / group by를 쓰면 분류작업으로 더 느려짐. 또는 테이블설계가 잘못되어 LONG이 들어가면 2GB까지 읽어야 되므로 성능저하됨 –> ??
  • CPU 분석 : fetch cpu time 이 많을때는 full-scan 또는 인덱스 없이 count,max,min,sum 함수 수행시 한번 더 분류작업하기 때문임 –> ??

 

V$SQLAREA 자료사전

(1) V$SQLAREA

(2) 파싱 정보 보기

  • shared_pool 는 기존 실행한 sql을 저장한다. (interpreter 방식이므로 다시 파싱하지 않기 위해)
    • 공간이 없으면 일정기간 기다렸다 공간이 안나오면 오래된놈을 지운다..
    • 공간 부족시 전반적인 성능이 나빠질 수 있음
    • alter system flush shared_pool;  (임시 방편임)
  • 파라메터

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
shared_pool_size                     big integer 0  ==> 0 이면 SGA target 파라메터 값을 참조

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 268M

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
———————————— ———– ——————————
cursor_sharing                       string      EXACT  ==> 9i부터 생긴 파라메터, 바인더 변수 사용시 동일한 파싱결과로 생각한다… (selec * from xxx where deptno=:a)

 

(3) cursor_sharing 을 통한 성능 튜닝 (9i 이후)

  • PARSE_CALL 이 높으면 parsing을 재사용했다는 뜻
  • 재사용을 높이려면 완전히 동일한 SQL을 사용해야 함 : 공백, 대소문자, 띄어쓰기 등등 확인해야

SQL> alter system set cursor_sharing=similar; –> 비슷하면 재사용하겠다는 뜻, exact는 완전동일

시스템이 변경되었습니다.

SQL> select * from dept where deptno = 10;

    DEPTNO DNAME          LOC
———- ————– ————–
10 ACCOUNTING     NEW YORK

SQL> select * from dept where deptno = 20;

    DEPTNO DNAME          LOC
———- ————– ————–
20 RESEARCH       DALLAS

SQL> @/sql_area.sql

SQL_TEXT
————————————————————-
VERSION_COUNT      LOADS INVALIDATIONS PARSE_CALLS      SORTS
————- ———- ————- ———– ———-
select * from dept where deptno = 10
1          1             0           1          0

select * from dept where deptno = :”SYS_B_0″
            1          1             0           1          0

SQL> select * from dept where deptno = 30;

    DEPTNO DNAME          LOC
———- ————– ————–
30 SALES          CHICAGO

SQL> @/sql_area.sql

SQL_TEXT
————————————————————-
VERSION_COUNT      LOADS INVALIDATIONS PARSE_CALLS      SORTS
————- ———- ————- ———– ———-
select * from dept where deptno = 10
1          1             0           1          0

select * from dept where deptno = :”SYS_B_0″
            2          2             0           2          0

 

(4) I/O 분석

  • V@SQLAREA 에서의 DISK_READS, BUFFER_GETS 를 통해 디스크, 메모리 I/O를 분석

 

(5) 동일한 SQL문 작성 지침

  • SQL문은 대문자 또는 소문자로 통일
  • 변수명은 SQL문, 객체명, 컬럼명과 구분하기 위해 소문자로 작성
  • 다른 스키마의 테이블을 호출할때는 SCHEMA명.TABLE명으로 작성
  • SQL문의 각 단어의 여백은 한칸
  • SQL문 내의 변수명은 변수 선언 기준 안에 따르며, 해당 컬럼명을 접두어와 결합하여 사용
  • SQL문의 SELECT , FROM, WHERE절은 라인의 선두에 기술

사용자 삽입 이미지

 

옵티마이저

(1) 옵티마이저란

사용자 삽입 이미지(2) 종류

  • Rule-Based Optimizer (RBO) : 15단계 실행방법 기반. Oracle Database 10g에서도 명맥은 남아 있음
  • Cost-Based Optimizer (CBO) : 인덱스, 조건 만족하는 데이터 양과 분포 등에 따라 판단. Oracl 7에서부터 도입

 

Rule Based Optimizer

(1) 비용 기반 15 단계

① rowid에  의해 단일행 실행
– 가장 빠름
– ROWID 구성 : 6(객체번호:emp) – 3 – 6(블럭주소) – 3(블럭에서 순번)

② cluster-join에 의한 단일해 실행
③ unique-key, primary-key를 사용한 hash-cluster key에 의한 단일행 실행
④ unique-key, primary-key에 의한 단일행 실행
– create unique index pk_emp on big_emp (deptno);
⑤ cluster 조인
⑥ hash-cluster key
⑦ 인덱스화된 cluster-key

⑧ 복합 인덱스
– create index deptno_job_idx on big_emp (job, deptno);
⑨ 단일 컬럼 인덱스(equal)
– create index sal_idx on big_emp (sal);
⑩ 인덱스가 구축된 컬럼에 대한 제한된 범위 검색
– between, like, < and >, = 표현식
– crete index ename_idx on big_emp (ename);
select ename, deptno from big_emp where ename between ‘B%’ and ‘S%’;
⑪ 인덱스가 구축된 커럼에 대한 무제한 범위의 검색
– >=,=< 표현식
– crete index ename_idx on big_emp (sal);
select ename from big_emp where sal > 3000;
⑫ 정렬-병합 조인
⑬ 인덱스가 구축된 컬럼에 대한 MAX, MIN
⑭ 인덱스가 구축된 컬럼에 대한 ORDER BY
⑮ Full-Table Scan    – 가장 느림


1) select * from emp where empno = 7369;  –>
2) create inex i_emp_empno on emp(empno);
select * from emp where empno = 7369;  –>
3) select * from emp where rowid = ‘AAAAfBAACAAAAEqAAA’; –>
  • Ranking에 의한 우선 순위 원칙이 적용
  • WHERE 조건절에 정의된 컬럼에 같은 동등 조건의 인덱스가 여러 개 생성되어 있다면 모든 인덱스를 적용하여 실행 계획을 결정
  • WHERE 절에 정의된 컬럼들이 동등 조건이 아닌 여러 개의 부분 범위의 조건으로 검색된다면 가장 나중에 만들어진 인덱스를 사용
  • ROWID 구성 : 6(객체번호 : dept)-3-6(블럭주소)-3(블럭에서 순번)

 

(2) 설정 변경

세션에서 룰 변경 : Alter session set optimizer_mode = rule;
설정 변경 : init<SID>.ora 파일에서 OPTIMIZER_MODE = RULE; 로 변경

 

(3) 예제

SQL> create index i_emp_deptno on big_emp (deptno);
인덱스가 생성되었습니다.

SQL> create unique index i_emp_empno on big_emp (empno);
인덱스가 생성되었습니다.

SQL> alter session set optimizer_mode = rule;  –> rule based optimizer 설정
세션이 변경되었습니다.

SQL> set autotrace traceonly –> 실행계획 확인

SQL> select ename
2  from big_emp
3  where deptno = 20  –> 9 순위
4        and empno between 100 and 200  –> 원래는 4순위인데 범위 때문에 10순위
5  order by ename;

30 개의 행이 선택되었습니다.

Execution Plan
———————————————————-
—————————————————–
| Id  | Operation                    | Name         |
—————————————————–
|   0 | SELECT STATEMENT             |              |
|   1 |  SORT ORDER BY               |              |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP      |
|*  3 |    INDEX RANGE SCAN          | I_EMP_DEPTNO–> 9순위
—————————————————–

Predicate Information (identified by operation id):
—————————————————
2 – filter(“EMPNO”<=200 AND “EMPNO”>=100)
3 – access(“DEPTNO”=20)

Note
—–
– ‘PLAN_TABLE’ is old version
– rule based optimizer used (consider using cbo)

Statistics
———————————————————-
1  recursive calls
0  db block gets
196  consistent gets
15  physical reads
0  redo size
750  bytes sent via SQL*Net to client
411  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
30  rows processed

 

Cost-Based Optimizer

(1) 부산에서 남대문 시장 가는 방법

  • 부산 > 부산공항 > 서울공항 > 남대문시장 : 1시간20분, 비용 많음
  • 부산 > 부산역 > 서울역 > 남대문시장 : 4시간35분, 비용 적음

(2) CBO 사용방법

  • Session 레벨 : alter session set optimizer_mode = choose;
  • Instance 레벨 : OPTIMIZER_MODE = CHOOSE | ALL_ROWS | FIRST_ROWS | FIRST_ROWS_1
    –  CHOOSE, ALL_ROWS : WHERE 조건중 모든행을 가장 빠르게 검색하는 실행계획
    –  FIRST_ROWS, FIRST_ROWS_1 : WHERE 조건중 1번째 행을 가장 빠르게 검색하는 실행계획
    –  FIRST_ROWS_100 : WHERE 조건중 100개의 행을 가장 빠르게 검색하는 실행계획
  • Statement 레벨 : Hint 사용 ( select /*+ Rule */  ~~~)
    – Hint를 줘도 CBO 실행 안될 수 있다 : 오타 (주석으로 인식), CBO 인데 RBO 관련 Hint 쓸때

(3) table/index/cluster의 통계 정보 생성

  • analyze table emp compute statistics;  –> emp 테이블 통계 생성
  • analyze table emp estimate statistics 50 percent; –> 전체중 50% 통계 생성, 100 rows -> 100개만
  • analyze table emp delete statistics; –> 통계 삭제

(4) 실습

사용자 삽입 이미지

SQL> create index i_emp_deptno on big_emp(deptno);
인덱스가 생성되었습니다.

SQL> create unique index i_emp_empno on big_emp(empno);
인덱스가 생성되었습니다.

 

SQL> analyze table big_emp compute statistics;  –> 테이블 통계 정보 생성 -> 비용 계산됨 -> Data Dictionary (user_table, user_indexes)에 저장됨, select table_name, last_analyzed from user_tables; 로 확인 가능
테이블이 분석되었습니다.

SQL> analyze index i_emp_deptno compute statistics; –> 실은 위의 테이블에 index가 들어 있어서 별도로 생성하지 않아도 된다. 그냥 한번 해봤음
인덱스가 분석되었습니다.

SQL> analyze index i_emp_empno compute statistics;
인덱스가 분석되었습니다.

 

SQL> alter session set optimizer_mode = choose–> rule-based optimizer 선택
세션이 변경되었습니다.

SQL> set autotrace traceonly

 

SQL> select ename
2  from big_emp
3  where deptno = 20
4  and empno between 100 and 200
5  order by ename;

30 개의 행이 선택되었습니다.

Execution Plan
———————————————————-

———————————————————————————
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)|
———————————————————————————
|   0 | SELECT STATEMENT             |             |     1 |    12 |     5  (20)|
|   1 |  SORT ORDER BY               |             |     1 |    12 |     5  (20)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP     |     1 |    12 |     4   (0)|
|*  3 |    INDEX RANGE SCAN          | I_EMP_EMPNO |    99 |       |     2   (0)| ->RBO와 다른결과
———————————————————————————

Predicate Information (identified by operation id):
—————————————————
   2 – filter(“DEPTNO”=20)
3 – access(“EMPNO”>=100 AND “EMPNO”<=200)

Note
—–
– ‘PLAN_TABLE’ is old version

Statistics
———————————————————-
1  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
750  bytes sent via SQL*Net to client
411  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
30  rows processed

(5) CBO 문제점

  • 통계 정보가 있어야 된다.
  • 모든 index에 대한 통계가 있어야 정확하다
  • 최근의 통계정보가 있어야 정확하다 (자주 바뀌는 테이블은 주기적으로 통계 생성해야 함)

(6) CBO 구조

사용자 삽입 이미지

(7) 비용 계산

  • full table scan : 테이블의 전체 블럭수 / DB_FILE_MULTIBLOCK_READ_COUNT

SQL> select blocks from user_tables
2  where table_name = ‘BIG_DEPT’;

BLOCKS
———-
1

SQL> connect system/inticube
연결되었습니다.
SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_multiblock_read_count        integer     16

 

  • index table scan : 선택도 * cluster_factor
    • 선택도 : 전체행에 대해 조건절에서 검색되는 행의 비율, 계산 공식은 다양함..
    • cluster_factor : index가 몇개의 block에 저장되었는지 비율

SQL> select index_name, clustering_factor
2  from user_indexes
3  where index_name = ‘I_EMP_EMPNO’;

INDEX_NAME                     CLUSTERING_FACTOR
—————————— —————–
I_EMP_EMPNO                                  533

 

(8) 쿼리 변형

잘못된 데이터 타입으로 조건 값을 검색하면 변형된다. (S_DATE 컬럼은 날짜 컬럼인데 문자 값을 검색할 때 사용하는 인용부호를 사용한 경우)
SQL> SELECT * FROM emp WHERE s_date = ‘1999-01-01’;
–> SQL> SELECT * FROM emp WHERE s_date = TO_DATE(‘1999-01-01’);

LIKE 연산자는 %(와일드 카드)와 함께 검색하는 경우 사용되지만, 그렇지 않은 경우 =(동등) 조건으로 변형되어 검색된다.
SQL> SELECT * FROM emp WHERE ename LIKE ‘주종면‘;
–> SQL> SELECT * FROM emp WHERE ename = ‘주종면’;

BETWEEN ~ AND 조건은 > AND < 조건으로 변형되어 검색된다.
SQL> SELECT * FROM emp WHERE salary BETWEEN 100000 AND 200000;
–> SQL> SELECT * FROM emp WHERE salary >= 100000 and salary <= 200000;

인덱스가 생성되어 있는 컬럼의 IN 연산자의 조건은 OR 연산자의 조건으로 변형된다.
SQL> SELECT * FROM emp WHERE ename IN (‘SMITH’, ‘KING’);
–> SQL> SELECT * FROM emp WHERE ename = ‘SMITH’ or ename = ‘KING’;

인덱스가 생성되어 있는 컬럼의 OR 연산자의 조건은 UNION ALL로 변형된다.
SQL> SELECT * FROM emp WHERE ename = ‘SMITH’ or sal = 1000;
–> SELECT * FROM emp WHERE ename = ‘SMITH’
UNION ALL
SELECT * FROM emp WHERE sal = 1000;

 

(9) 적정 플랜 선택

사용자 삽입 이미지
(10) Histogram

  • 분포도를 계산하여 적당한 선택도(Selectivity) 값을 찾음
  • e.g. 1 이 7개, 2,3,5,8,10,100 이 1개씩 있을때
    • Histogram 없을때 column = 1 로 검색시 선택도 : 1/100 (최소값 / 최대값) = 0.01
    • Histogram 있을때 column = 1 로 검색시 선택도 : 0.5
      • analyze table emp compute statistics for fomlumn size 4
      • 버켓수 : 1~255 값을 가짐 , default 75, distinct 한 key 갯수를 넣는다.
      • 1은 4개의 버켓중 2개의 버켓에 들어감
      • 선택도 : 2/4
  • 실습예제

SQL> set autotrace off
SQL> select deptno, count(*) from big_emp
2  group by deptno
3  order by count(*) desc;

    DEPTNO   COUNT(*)
———- ———-
        30       9362
20       7231
10       5336

60       1263
50        660
4        127
21        126
11        104
102
84        102
35        102

 

deptno = 30 은 데이타가 많지만, deptno = 38 은 데이타가 적다 (분포가 다르다)
따라서 histogram을 적용하면…

 

SQL> analyze table big_emp compute statistics for columns deptno size 99; ===> historgram 적용

테이블이 분석되었습니다.

SQL> select * from big_emp where deptno=38;


Execution Plan
———————————————————-
Plan hash value: 1169875452

————————————————————————————————
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————
|   0 | SELECT STATEMENT            |                  |     5 |   180 |     2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_EMP          |     5 |   180 |     2 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_BIG_EMP_DEPTNO |     5 |       |     1 (0)| 00:00:01 |
————————————————————————————————


Predicate Information (identified by operation id):
—————————————————

   2 – access(“DEPTNO”=38)


Statistics
———————————————————-
1  recursive calls
0  db block gets
8  consistent gets
0  physical reads
0  redo size
1121  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
5  rows processed


SQL> select * from big_emp where deptno=30;

9362 개의 행이 선택되었습니다.


Execution Plan
———————————————————-
Plan hash value: 3814977537

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         |  9362 |   329K|    44   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIG_EMP |  9362 |   329K|    44   (5)| 00:00:01 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“DEPTNO”=30)


Statistics
———————————————————-
1  recursive calls
0  db block gets
808  consistent gets
0  physical reads
0  redo size
434370  bytes sent via SQL*Net to client
7249  bytes received via SQL*Net from client
626  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
9362  rows processed

 

histogram 값을 주기 위해 아래와 같은 쿼리를 참조

SQL> set autotrace off
SQL> select table_name, column_name, num_distinct, num_buckets, sample_size
2  from user_tab_columns
3  where table_name=’BIG_EMP’ and column_name=’DEPTNO’;

TABLE_NAME         COLUMN_NAME        NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
—————— —————— ———— ———– ———–
BIG_EMP            DEPTNO                       98          98       28955

 

(11) DBMS_STATS 패키지

1) 통계정보 새로 만들고 나서 실행계획이 바뀌면서 성능 떨어질 수도 있음. 기존 통계정보를 백업/복원 가능
2) 특정 통계 정보를 일괄수집 가능 : TABLE 단위, USER 단위, DB단위, 선별적으로도 가능
3) 자동실행 가능 : 야간에 특정 부분의 통계정보 생성 가능  –> DBMS_JOB (스케쥴 관련)과 연동하여
4) 8i 부터 사용됨

excute dbms_stat.gather_table_stats(‘소유자’, ‘테이블’);  –> 테이블 통계 생성
excute dbms_stat.create_table_stats(‘소유자’, ‘통계테이블’);  –> 백업할 통계테이블 생성
excute dbms_stat.export_schema_stats(‘소유자’, ‘통계테이블’);  –> 백업 실행
excute dbms_stat.import_table_stats(‘소유자’, ‘테이블’, null, ‘통계테이블’);  –> 복원 실행

 

C:\Documents and Settings\edu>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 – Production on 화 6월 3 11:29:12 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL>  analyze table big_emp delete statistics;  ===> 실습위해 기존 통계 정보 지우고 시작하자
테이블이 분석되었습니다.

SQL> set serveroutput on  ===> 화면출력
SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS(‘SCOTT’, ‘BIG_EMP’, null, NULL, null, num_rows, num_blocks, avg_row_len); ===> 통계정보가 없는데 읽으라고 하니 오류가 난다.
7     DBMS_OUTPUT.PUT_LINE(‘num_rows=’||num_rows||’,num_blocks=’||num_blocks||’,avg_row_len=’||avg_row_len);
8  END;
9  /
DECLARE
*
1행에 오류:
ORA-20000: Unable to get values for table BIG_EMP
ORA-06512: “SYS.DBMS_STATS”, 줄 4314에서
ORA-06512: “SYS.DBMS_STATS”, 줄 4331에서
ORA-06512: 줄 6에서

SQL> execute dbms_stats.gather_table_stats(‘SCOTT’, ‘BIG_EMP’);  ===> 통계정보 생성

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS(‘SCOTT’, ‘BIG_EMP’, null, NULL, null, num_rows, num_blocks, avg_row_len);  ===> 정상 실행됨
7     DBMS_OUTPUT.PUT_LINE(‘num_rows=’||num_rows||’,num_blocks=’||num_blocks||’,avg_row_len=’||avg_row_len);
8  END;
9  /
num_rows=29239,num_blocks=182,avg_row_len=40

PL/SQL 처리가 정상적으로 완료되었습니다.


SQL> execute dbms_stats.create_stat_table(‘SCOTT’, ‘KIMSTAR_STATS’);  ===> 백업테이블 생성

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> execute dbms_stats.export_schema_stats(‘SCOTT’, ‘KIMSTAR_STATS’); ===> 통계를 테이블에 백업

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> execute dbms_stats.delete_table_stats(‘SCOTT’, ‘BIG_EMP’);  ===> 통계 삭제

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS(‘SCOTT’, ‘BIG_EMP’, null, NULL, null, num_rows, num_blocks, avg_row_len);  ===> 또 에러
7     DBMS_OUTPUT.PUT_LINE(‘num_rows=’||num_rows||’,num_blocks=’||num_blocks||’,avg_row_len=’||avg_row_len);
8  END;
9  /
DECLARE
*
1행에 오류:
ORA-20000: Unable to get values for table BIG_EMP
ORA-06512: “SYS.DBMS_STATS”, 줄 4314에서
ORA-06512: “SYS.DBMS_STATS”, 줄 4331에서
ORA-06512: 줄 6에서

SQL> execute dbms_stats.import_table_stats(‘SCOTT’, ‘BIG_EMP’, null, ‘KIMSTAR_STATS’); ===> 복원

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> DECLARE
2     num_rows NUMBER;
3     num_blocks NUMBER;
4     avg_row_len NUMBER;
5  BEGIN
6     DBMS_STATS.GET_TABLE_STATS(‘SCOTT’, ‘BIG_EMP’, null, NULL, null, num_rows, num_blocks, avg_row_len); ===> 정상처리
7     DBMS_OUTPUT.PUT_LINE(‘num_rows=’||num_rows||’,num_blocks=’||num_blocks||’,avg_row_len=’||avg_row_len);
8  END;
9  /
num_rows=29239,num_blocks=182,avg_row_len=40

PL/SQL 처리가 정상적으로 완료되었습니다.

 

(12) Stored Outline : 실행계획을 백업시 사용

 

튜닝 로드맵

사용자 삽입 이미지

 

사용자 삽입 이미지

 

실습 결과 trace 파일

첨부파일에서 ==> 로 검색하면 optimizer가 선택한 실행계획에 대한 사항을 볼 수 있음

orcl_ora_1412.trc.txt

 

 

사용자 삽입 이미지

CC BY-NC-ND 2.0 KR

이 저작물은 크리에이티브 커먼즈 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용할 수 있습니다. 크리에이티브 커먼즈 라이선스

저작권과 관련된 파일요청 및 작업요청을 받지 않습니다.

14 댓글

  1. 리치타이거 응답

    안녕하세요!
    구글 검색하다가 왔습니다.
    혹시 NEW_SCOTT_10.DMP 파일 좀 email로 받을 수 있을까요?
    테스트를 좀 해보고 싶어서 무리하게 부탁하게 되었습니다.

  2. 김용찬 응답

    안녕하세요.
    관련 교재를 보다가 DMP 파일이 필요하여 검색하다보니 찾고 왔습니다.
    실습공부에 필요하여 email로 부탁드립니다.

    • kimstar 글쓴이응답

      답장이 늦어서 죄송합니다. paran.com 이메일로 파일 보내드렸습니다.

  3. 오라과객 응답

    저도 책보다말고 여기와서 DMP파일이 필요한거 같습니다.
    내용 정리가 잘 되 있어요
    실습파일 좀 부탁드리겠습니다.

    • kimstar 글쓴이응답

      답변이 많이 늦었습니다.
      이메일로 전송해 드렸습니다. ^^

  4. 땅을 응답

    안녕하세요.. 최근에 DBguide.net 통해서 오라클 튜닝 강좌 들었어서 관련 자료 찾던 중에 들어오게 됐는데요…
    우와.. 대단하신 것 같아요… 이렇게 잘 정리된 자료 처음 봤어요…

    앞으로 자주 들어올 듯한 ㅎㅎㅎ

    감사합니다. ^^

    • kimstar 글쓴이응답

      도움이 되는 글이였으면 좋겠네요. 자주 놀러오세요. ^^

    • kimstar 글쓴이응답

      답변이 늦었습니다. 최근에 제가 NAS 하드를 날리는 바람에.. 해당 파일을 유실하여 보내드릴 수가 없네요. ㅠㅠ

    • kimstar 글쓴이응답

      답변이 늦었습니다. 최근에 제가 NAS 하드를 날리는 바람에.. 해당 파일을 유실하여 보내드릴 수가 없네요. ㅠㅠ

  5. 이서윤 응답

    안녕하세요. 구글링을 하다가 우연히 발견하여 기쁘고 감사의 마음으로 실습파일을 요청드립니다.

    • kimstar 글쓴이응답

      해당 파일을 유실하여 현재로서는 전달해 드릴 수가 없네요.

댓글 남기기