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

Hint


Hint란?
1. 옵티마이저는 데이타 분포도가 좋고 최신 통계가 있어야 좋은 실행계획을 세운다.
2. HINT절은 옵타마이저의 실행계획을 개발자가 지정 (e.g. INDEX SCAN을 강제로 FULL TABLE SCAN으로 변경)

문법
1. {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]… */
2. {DELETE|INSERT|SELECT|UPDATE} –+ hint [text] [hint[text]]…

 

종류
RULE : RBO 옵티마이저 사용
FIRST_ROWS : 첫번째 레코드 추출을 최소화할 목적으로 사용시
ALL_ROWS : 모든 레코드의 처리시간을 최소화할 목적으로 사용시
FULL : 테이블의 전체 스캔
ROWID : 테이블의 ROWID 스캔
CLUSTER : 테이블의 클러스터 스캔
HASH : 테이블의 해쉬 스캔

정렬방법
INDEX_ASC : 오름차순 INDEX 스캔
INDEX_DESC : 내림차순 INDEX 스캔
AND_EQUALS : 여러 인덱스를 MERGE하여 사용 (2~5개)
ORDERED : FROM절에 기술된 TABLE 순으로 Join
USE_NL : Nested Loop Join
USE_MERGE : Sort Merge Join

예제

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

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


SQL> analyze table big_emp compute statistics;
테이블이 분석되었습니다.

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

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

 

SQL> alter session set optimizer_mode = choose;
세션이 변경되었습니다.

SQL> set autotrace trace;

 

SQL> select ename from big_emp where deptno = 20 and empno between 100 and 200 order by ename;
30 개의 행이 선택되었습니다.


Execution Plan
———————————————————-
Plan hash value: 3671819838

————————————————————————————————
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————
|   0 | SELECT STATEMENT             |                 |     1 |    12 |     5(20)| 00:00:01 |
|   1 |  SORT ORDER BY               |                 |     1 |    12 |     5(20)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP         |     1 |    12 |     4 (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_BIG_EMP_EMPNO |    99 |       |     2 (0)| 00:00:01 |
————————————————————————————————


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


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
396  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
30  rows processed



SQL>  select /*+RULE*/ ename from big_emp where deptno = 20 and empno between 100 and 200 order by ename;

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


Execution Plan
———————————————————-
Plan hash value: 65844923

———————————————————
| Id  | Operation                    | Name             |
———————————————————
|   0 | SELECT STATEMENT             |                  |
|   1 |  SORT ORDER BY               |                  |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP          |
|*  3 |    INDEX RANGE SCAN          | I_BIG_EMP_DEPTNO |
———————————————————

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

Note
—–
– rule based optimizer used (consider using cbo)


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



SQL> select /*+FIRST_ROWS*/ ename from big_emp where deptno = 20 and empno between 100 and 200 order by ename;

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


Execution Plan
———————————————————-
Plan hash value: 3671819838

————————————————————————————————
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————————
|   0 | SELECT STATEMENT             |                 |     1 |    12 |     5(20)| 00:00:01 |
|   1 |  SORT ORDER BY               |                 |     1 |    12 |     5(20)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP         |     1 |    12 |     4 (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_BIG_EMP_EMPNO |    99 |       |     2 (0)| 00:00:01 |
————————————————————————————————



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


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
396  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
30  rows processed

 


SQL> select /*+FULL(big_emp)*/ ename from big_emp where deptno = 20 and empno between 100 and 200 order by ename;

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


Execution Plan
———————————————————-
Plan hash value: 4283132093

——————————————————————————
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT   |         |     1 |    12 |    45   (7)| 00:00:01 |
|   1 |  SORT ORDER BY     |         |     1 |    12 |    45   (7)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| BIG_EMP |     1 |    12 |    44   (5)| 00:00:01 |
——————————————————————————

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


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

 

 

병렬옵션


개요
full table scan 의 성능향상을 가져옴
여러개의 CPU에 parallel query process가 떠서 병렬로 I/O 작업을 함

사용자 삽입 이미지사용자 삽입 이미지
옵션 확인

C:\Documents and Settings\edu>sqlplus “as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on 화 6월 3 12:29:20 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> col parameter format a30
SQL> col value format a20
SQL> select * from V$OPTION where parameter like ‘%Parallel%’;

PARAMETER                      VALUE
—————————— ——————–
Parallel backup and recovery   TRUE  –> 병렬 복구에 관한 옵션
Parallel execution             TRUE  –> 병렬 질의와 병렬 인덱스 관련 옵션
Parallel load                  TRUE  –> 병렬 로드에 관한 옵션

SQL> select table_name, degree from user_tables;

TABLE_NAME                     DEGREE
—————————— ——————–
KIMSTAR_STATS                           1   ==> 병렬처리 1 (기본값임)
ACCOUNT                                 1
BIG_DEPT                                1

 

SQL> show parameter parallel_automatic_tuning  –> 결과 merge를 위해 메모리 필요함, LARGE_POOL 사이즈를 자동으로 설정함
NAME                                 TYPE        VALUE
———————————— ———– ———
parallel_automatic_tuning            boolean     FALSE

SQL> show parameter parallel_adaptive_multi_user –> CPU의 load-balancing을 하도록 설정
NAME                                 TYPE        VALUE
———————————— ———– ———
parallel_adaptive_multi_user         boolean     TRUE

 

Parallel Query 환경
1. 테이블 생성시 : create table ~~~ parallel 5;
2. 테이블 생성후 : alter table name parallel 5;
3. SQL 에서 : select /*+ Parallel(테이블명 5) */ ~~~;

Parallel Qury 예제

SQL> create table big_emp1 parallel 2 as select * from big_emp;
테이블이 생성되었습니다.

SQL> create table big_dept1 as select * from big_dept;
테이블이 생성되었습니다.

SQL> alter table big_dept1 parallel 2;
테이블이 변경되었습니다.

SQL> set autotrace trace

SQL> select /*+PARALLE(A 2)*/ empno, ename from big_emp1 A;
28955 개의 행이 선택되었습니다.


Execution Plan
———————————————————-
Plan hash value: 1065910406

————————————————————————————————————–
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time|    TQ  |IN-OUT| PQ Distrib |
————————————————————————————————————–
|   0 | SELECT STATEMENT     |          | 27053 |   528K|    27   (4)| 00:00:01|        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            ||        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 27053 |   528K|    27   (4)| 00:00:01|  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 27053 |   528K|    27   (4)| 00:00:01|  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| BIG_EMP1 | 27053 |   528K|    27   (4)| 00:00:01|  Q1,00 | PCWP |            |
————————————————————————————————————–


Note
—–
– dynamic sampling used for this statement


Statistics
———————————————————-
94  recursive calls
3  db block gets
397  consistent gets
182  physical reads
672  redo size
589154  bytes sent via SQL*Net to client
21615  bytes received via SQL*Net from client
1932  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
28955  rows processed


SQL> set autotrace off
SQL> select program from v$process;  –> 위의 쿼리를 실행후 다른 창에서 빨리 실행해야 됨

PROGRAM
—————————————————————-
PSEUDO
ORACLE.EXE (PMON)
ORACLE.EXE (PSP0)
ORACLE.EXE (MMAN)
ORACLE.EXE (DBW0)
ORACLE.EXE (LGWR)
ORACLE.EXE (CKPT)
ORACLE.EXE (SMON)
ORACLE.EXE (RECO)
ORACLE.EXE (CJQ0)
ORACLE.EXE (MMON)
ORACLE.EXE (MMNL)
ORACLE.EXE (D000)
ORACLE.EXE (S000)
ORACLE.EXE (J000)
ORACLE.EXE (P000) –> 병렬실행 #0
ORACLE.EXE (SHAD)
ORACLE.EXE (QMNC)
ORACLE.EXE (P001) –> 병렬실행 #1
ORACLE.EXE (q000)
ORACLE.EXE (SHAD)
ORACLE.EXE (SHAD)
ORACLE.EXE (SHAD)
ORACLE.EXE (q001)
ORACLE.EXE (SHAD)
ORACLE.EXE (SHAD)

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

tip :
UNIX라면 SQL> ! ps -ef | grep DBA101 로 확인하면 ‘ora_p000_DBA101’, ‘ora_p001_DBA101’ 확인가능함
DBA101은 데이테베이스명

 

Parallel DML
update /* Parallel (테이블명, degree) / ~~
delete /
Parallel (테이블명, degree) / ~~
insert /
Parallel (테이블명, degree) */ ~~

Parallel Data Load
병렬로 데이타 로드

Parallel Recovery
병렬로 데이타베이스 복구

사용자 삽입 이미지

 

테이블 검색 방법

 

데이터 검색 방법

  1. Full Table Scan : 테이블을 다 읽음, 가장 느림
  2. Index Scan : 테이블의 특정컬럼에 대해 목차(index)를 만든후 데이타 검색시 사용
  3. Bitmap Index Scan : 오라클 7.3부터 제공 0/1로 인덱스 구성
  4. Index Cluster : index를 통해 빠른검색 못할때 사용, 2개 이상의 테이블을 같은 블록구조에 저장
  5. Hash Cluster : 해쉬 알고리즘으로 대용량 테이블에서 하나의 행을 검색시

데이터 검색 로드맵

사용자 삽입 이미지

테이블 설계시 주의사항

  1. 테이블 생성 전에 정규화 및 컬럼의 데이터 유형을 계획하라
  2. 블록 영역을 위해 Initial, Next, Extents, PCTFREE, PCTUSED를 충분히 고려하라

– 단편화(구멍) / 행 이주(블럭이 모자라서 통채로 옮김) / 행 연결(여러개 블럭에) 현상 방지
3. 저장될 테이블스페이스를 반드시 지정하라 (사용자 데이타, 롤백 데이타, 분류 데이타)
– 기본 tablaspace에 넣지마라
– tablesapce를 명시 안하면 사용자의 default tablespace에 생성됨
– dba_segments 테이블에서 생성된 table의 tablespace 확인 가능
– tablespace 변경 : alter table big_emp move tablespace test
4. 필요한 경우 NoLogging 키워드를 사용하라
– 데이터 유실시 다시 복구할 수 있는 테이블은 백업 저장안해도 된다.
– create table ~~~ NOLOGGING;  (기본값은 LOGGING)
– alter table test1 LOGGING;
5. 필요한 경우 CACHE 키워드를 사용하라
– 자주 사용하는 테이블은 데이터 버퍼 캐시에서 지워지지 않도록 설정
– create table ~~~ CACHE;  (기본값은 NOCACHE)
6. 테이블의 파티션 여부를 결정하라
– 파티셔닝 테이블 : 하나의 테이블이 여러개의 테이블스페이스에 저장될 수도 있다.
– 오라클 8부터 추가된 기능

테이블 분석 및 재구성

(1) 분석 : anayze table

SQL> analyze table big_emp compute statistics;

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

SQL> select num_rows, blocks, avg_row_len, avg_space, chain_cnt
2  from dba_tables
3  where table_name = ‘BIG_EMP’;

  NUM_ROWS     BLOCKS AVG_ROW_LEN  AVG_SPACE  CHAIN_CNT
———- ———- ———– ———- ———-
28955        182          43        913          0 –> 행이주, 행연결이 없음

 

(2) 재구성

  • export -> drop table -> import
  • create table emp1 from select * from emp; -> drop table emp; -> rename emp1 to emp;

 

인덱스

 

인덱스 설계시 주의사항

  1. 6블럭 이상의 테이블에 적용 (DB_BLOCK_SIZE)
  2. 컬럼의 분포도가 10~15% 이내일때 적용
    : 분포도 = (1/컬럼값의 종류)*100 = (컬럼값의 평균 로우수 / 테이블의 총 로우수) * 100
  3. 분포도가 범위 이상이라도, 부분 범위 처리를 목적으로 하는 경우
  4. 인덱스만을 사용하여 요구를 해결하고자 하는 경우 (손익 분기점)

인덱스 종류

  1. 물리적 인덱스

– Balance*Tree
– Reverse
– Bitmap
– IOT
– Descending

  1. 논리적 인덱스

– Single Index : create index aaa on emp(a);
– Concanated Index : create index aaa on emp(a,b);
– Unique Index : create unique index aaa on emp(사번);
– NonUnique Index : create index aaa on emp(a);  –> 기본값임
– Function-Based Index : create index aaa on emp(substr(ename,1,2));
–> 8i부터, where절에서 substr(ename,1,2)=’김’ 과 같이 사용시

 

물리적 인덱스

 

Balance*Tree Index

  • 대부분의 index가 이걸 사용한다.
    사용자 삽입 이미지– 초기 인덱스를 만들면 균형잡힌 B-Tree 가 되지만 추가/삭제가 실행될 수록 맨밑의 leaf 아래에 자식 생김
    사용자 삽입 이미지사용자 삽입 이미지

SQL> select index_name, leaf_blocks, blevel, avg_leaf_blocks_per_key from user_indexes;

INDEX_NAME                     LEAF_BLOCKS     BLEVEL AVG_LEAF_BLOCKS_PER_KEY
—————————— ———– ———- ———————–
KIMSTAR_STATS
SYS_C005393                             85          1                       1
I_BIG_EMP_EMPNO                         60          1                       1
I_BIG_EMP_DEPTNO                        57          1                       1
SYS_C005399                           1093          2                       1
PK_T_CHANGGO                             1          0                       1


– 발랜스에 대하여

SQL> drop table big_emp1;
테이블이 삭제되었습니다.

SQL> create table big_emp1 as select * from big_emp;
테이블이 생성되었습니다.

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

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

SQL> select (del_lf_rows_len/lf_rows_len)*100 as Balancing from index_stats;

 BALANCING
———-
0 ==> 최초 만들었으므로 밸런스 100% 이다..

 

SQL> delete from big_emp1 where empno > 1 and empno < 25000;
23958 행이 삭제되었습니다.

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

SQL>  select (del_lf_rows_len/lf_rows_len)*100 as Balancing from index_stats;

 BALANCING
———-
82.3819436 ==> 삭제를 수행하면서 밸런스가 깨졌다..
==> 오라클 가이드에서 20% 이상이면 rebuild 권장.

 

SQL> alter index i_emp_empno rebuild nologging parallel 2;
인덱스가 변경되었습니다.

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

SQL> select (del_lf_rows_len/lf_rows_len)*100 as Balancing from index_stats;

 BALANCING
———-
0 ==> rebuild 하여 밸런스 좋아짐


– unique scan
사용자 삽입 이미지
– range scan
사용자 삽입 이미지

 

ROWID를 통한 검색

사용자 삽입 이미지

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

SQL> set autotrace traceonly
SQL> select empno, ename from big_emp
2  where deptno = 1102;

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

————————————————————————————-
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)|
————————————————————————————-
|   0 | SELECT STATEMENT            |                  |     1 |    12 |     2 (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_EMP          |     1 |    12 |     2 (0)|
|*  2 |   INDEX RANGE SCAN          | I_BIG_EMP_DEPTNO |     1 |       |     1 (0)|
————————————————————————————-

 

Reverse Index
– 연속적인 Data의 변경이 많을 경우 B-Tree의 균형이 깨진다.
– index를 뒤집어서 사용하므로 연속적인 데이타를 변경해도 분산됨
– create table ~~~ REVERSE;

사용자 삽입 이미지
Decending Index
– 일반적으로는 ascending 으로 index 쌓인다.
– 날짜 컬럼으로 최근것 검색시
– 걍.. Hint를 쓰면 됨

Function Based Index
– 8i 이상 버전
– 인덱스로 함수를 사용할 수 있음
– 조건
1) QUERY_REWRITE_INTEGRITY = TRUSTED (9i 이상에서는 그냥됨)
2) QUERY_REWRITE_ENABLED = TRUE
3) Init.ora 파일에 COMPATIBLE = 8.1.0.0.0 이상
4) CREATE INDEX, QUERY REWRITE 권한이 필요
5) 통계정보가 있고, CBO 일때

IOT(Index Organized Table) Index
– 인덱스와 테이블을 같은 저장구조에 생성
– 한번의 액세스로 인덱스와 테이블을 검색 가능하므로 디스크 I/O 가 많은 테이블 구조일때 적합

사용자 삽입 이미지
BitMap Index
– 오라클 7.3.2 이상 버전
– 0과 1로 인덱스 생성
– and, or 연산 가능
– create bitmap index ~~~~;
– 효과를 보기 위한 조건
1) 큰 테이블 검색시 (데이타웨어하우징)
2) 데이터 분포도 나쁠때(e.g. 남/여, 서울/경기)
3) DML이 자주 실행되지 않아야
– 효과
1) 인덱스가 너무 크거나 생성시간이 오래 걸릴때 효과적
2) 저장공간이 줄어들음

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

 

논리적 인덱스


결합인덱스

사용자 삽입 이미지

– 싱글인덱스 사용시

select *
from
big_emp where deptno=:”SYS_B_0″ and job=:”SYS_B_1″


call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       78      0.01       0.03          0      10934          0        1154
——- ——  ——– ———- ———- ———- ———-  ———-
total       80      0.04       0.05          0      10934          0        1154

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

Rows     Row Source Operation
——-  —————————————————
1154  TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=10934 pr=0 pw=0 time=65252 us)
1154   AND-EQUAL  (cr=10728 pr=0 pw=0 time=59468 us)
4367    INDEX RANGE SCAN I_EMP_DEPTNO (cr=6501 pr=0 pw=0 time=23658 us)(object id 52612)
3223    INDEX RANGE SCAN I_EMP_JOB (cr=4227 pr=0 pw=0 time=12931 us)(object id 52613)

Rows     Execution Plan
——-  —————————————————
      0  SELECT STATEMENT   MODE: RULE
1154   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘BIG_EMP’ (TABLE)
1154    AND-EQUAL
4367     INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_EMP_DEPTNO’ (INDEX)
3223     INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_EMP_JOB’ (INDEX)


– 결합인덱스 사용시


select *
from
big_emp where deptno=:”SYS_B_0″ and job=:”SYS_B_1″


call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       78      0.04       0.00          0        288          0        1154
——- ——  ——– ———- ———- ———- ———-  ———-
total       80      0.04       0.00          0        288          0        1154

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

Rows     Row Source Operation
——-  —————————————————
1154  TABLE ACCESS BY INDEX ROWID BIG_EMP (cr=288 pr=0 pw=0 time=10458 us)
1154   INDEX RANGE SCAN I_EMP_DEPTNO_JOB (cr=82 pr=0 pw=0 time=4659 us)(object id 52614)

Rows     Execution Plan
——-  —————————————————
      0  SELECT STATEMENT   MODE: ALL_ROWS
1154   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘BIG_EMP’ (TABLE)
1154    INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_EMP_DEPTNO_JOB’ (INDEX)

– sort 대신 index 사용


1) 단일 인덱스
– select * from emp where hiredate = ’86/06/14′ and job like ‘CL%’ order by hiredate, job
– 인덱스(hiredate) (’86/06/14’에 대한 스캔범위 넓다) –> table –> sort


2) 복합 인덱스
– select * from emp where hiredate = ’86/06/14′ and job like ‘CL%’
– 인덱스(hiredate + job) (’86/06/14′ & ‘CL%’ 스캔범위 좁다) –> table

 

인덱스 활용 기법


Index Type Roadmap

사용자 삽입 이미지
Fast Index Scan 사용
– 관련 인덱스만 검색하면 원하는 모든 정보를 얻을 수 있는 기법

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

SQL> select max(empno) + 1
2  from big_emp
3  where deptno = 10;

———————————————————————————-
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)|
———————————————————————————-
|   0 | SELECT STATEMENT             |              |     1 |     6 |    53   (0)|
|   1 |  SORT AGGREGATE              |              |     1 |     6 |            | –> max 때문
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_EMP      |   294 |  1764 |    53   (0)|
|*  3 |    INDEX RANGE SCAN          | I_EMP_DEPTNO |   294 |       |     1   (0)|
———————————————————————————-


==> 튜닝하자 !!

 

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

SQL> select /*+ index_desc(a i_emp_deptno_empno) */ empno+1
2  from big_emp
3  where deptno=10 and rownum=1;

—————————————————————————–
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)|
—————————————————————————–
|   0 | SELECT STATEMENT  |                    |     1 |     6 |     2   (0)|
|*  1 |  COUNT STOPKEY    |                    |       |       |            |
|*  2 |   INDEX RANGE SCAN| I_EMP_DEPTNO_EMPNO |     2 |    12 |     2   (0)|
—————————————————————————–

 

선행 컬럼의 결정

1) where 절에 자주 사용되는 컬럼
2) data 분포도가 좋은 컬럼
3) data 양이 적은 것
4) between, like, <, > 등으로 제한되어 검색이 되지 않는 컬럼

a) 분포도와 결합 순서의 상관 관계

사용자 삽입 이미지b) 이퀄(=)이 결합순서에 미치는 영향
사용자 삽입 이미지c) IN 연산자를 이용한 징검다리 효과
사용자 삽입 이미지4) 처리 범위에 직접적인 영향을 주지 못하는 컬럼의 추가 기준
사용자 삽입 이미지

 

create inde i_emp_date_deptno on big_emp(hiredate, deptno);
선행컬럼을 잘못 선택된 경우

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.03         87        219          0           3
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.01       0.03         87        219          0           3

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: ALL_ROWS
3   SORT (ORDER BY)
3    HASH (GROUP BY)
131     FILTER
131      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘BIG_EMP’ (TABLE)
131       INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_EMP_DATE_DEPTNO’ (INDEX)



create inde i_emp_deptno_date on big_emp(deptno, hiredate);
선행컬럼을 잘 선택된 경우

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01         11        138          0           3
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.01       0.01         11        138          0           3

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: ALL_ROWS
3   SORT (ORDER BY)
3    SORT (GROUP BY NOSORT)
131     FILTER
131      INLIST ITERATOR
131       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF ‘BIG_EMP’ (TABLE)
131        INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_EMP_DEPTNO_DATE’ (INDEX)



create inde i_emp_deptno_date_sal on big_emp(deptno, hiredate, sal);
select절에 있는 sal을 인덱스로 잡아서 table scan을 줄임 –> 빠른 인덱스 스캔

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01         10          6          0           3
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.00       0.01         10          6          0           3

Rows     Execution Plan
——-  —————————————————
0  SELECT STATEMENT   MODE: ALL_ROWS
3   SORT (ORDER BY)
3    SORT (GROUP BY NOSORT)
131     FILTER
131      INLIST ITERATOR
131       INDEX   MODE: ANALYZED (RANGE SCAN) OF ‘I_EMP_DEPTNO_DATE_SAL’ (INDEX)

 

 

인덱스를 사용못하는 경우

 

(1) 인덱스가 있는 컬럼을 표현식 또는 함수로 변형시킬경우

select * from dept where substr(dname, 1, 3) = ‘ABC’;
==>
select * from dept where dname like ‘ABC%’;

 

SQL> select * from emp where nvl(comm, 0) < 1000;
==>
1) function based index 생성
create index i_emp_comm_nvl on emp (NVL(COMM, 0))
2) Parameter  Requirement
– QUERY_REWRITE_INTEGRITY = TRUSTED
– QUERY_REWRITE_ENABLED  = TRUE
– COMPATIBLE = 8.1.0.0.0 이상

   SQL> alter session set QUERY_REWRITE_ENABLE = TRUE;
SQL> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
SQL> grant query rewrite to 오라클사용자명;
3) query 작성 (인덱스 생성조건과 똑같이 쿼리 작성)
SELECT EMPNO, ENAME FROM EMP X WHERE NVL(COMM, 0) < 100

 

(2) 부정연산자 (!=, <>) 를 사용할경우
– 인덱스는 검색하고자 하는 컬럼의 데이터가 전체 데이터의 약 10% 범위에 있을때 가장 빠르게 검색
– 인덱스에 부정(!=, <>)을 사용하면 4~10% 의 범위를 벗어난 범위를 의미하기때문에
인덱스가 있다하더라도 오라클 서버는 인덱스를 사용하지 않고 대신 전체 테이블 스캔을 한다.

select * from emp where like ‘F%’ and job <> ‘SALES’;
==>
select * from emp a where a.ename like ‘F%’
and not exists (select ” from emp b where (a.ename = b.ename) and (b.job = ‘SALESMAN’));

 

(3) IS NULL 을 사용할 경우
– 인덱스는 NULL 값이 포함되지 않으므로
IS NULL 의 의미는 인덱스가 없음을 의미하고 전체 테이블 스캔으로 데이터를 검색한다.

select * from emp where ename is not null;
==>
select * from emp where ename > ”;

 

(4) 와일드 카드로 시작하는 LIKE 문은 인덱스를 사용하는 경우
– 검색해야 할 범위를 알수 없기 때문에
전체 테이블 스캔이 더 빠른 검색을 해줄수 있다고 판단하여 인덱스를 사용하지 않는다.
– 단 와일드카드(%) 로 끝나는 검색조건에는 인덱스가 사용된다.  (e.g. select ~~~ name like ‘김%’)

select * from emp where job like ‘%AB’;
==>
index 사용하지 않고 full table scan을 빨리 수행하도록 DB_FILE_MULTIBLOCK_READ_COUNT 값을 키운다.

 

(5) 같은 테이블에 있는 다른 컬럼과 비교할 경우

SQL> select * from emp where sal < comm;

 

 

인덱스 튜닝 실습


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

SQL> set autotrace trace;

 

SQL> create index i_dept_dname on dept(dname);
인덱스가 생성되었습니다.

SQL> select * from dept where substr(dname,1,3) = ‘ABC’;  ==> 인덱스를 함수로 감쌀때
선택된 레코드가 없습니다.

Execution Plan
———————————————————-
Plan hash value: 3383998547

———————————-
| Id  | Operation         | Name |
———————————-
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| DEPT |
———————————-

 

SQL> select * from dept where dname like ‘ABC%’; ==> 인덱스를 함수로 감싸지 않도록 튜닝
선택된 레코드가 없습니다.

Execution Plan
———————————————————-
Plan hash value: 49243191

—————————————————-
| Id  | Operation                   | Name         |
—————————————————-
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT         |
|*  2 |   INDEX RANGE SCAN          | I_DEPT_DNAME |
—————————————————-

 

SQL> drop index i_dept_dname;
인덱스가 삭제되었습니다.

SQL> create index i_dept_job on emp(job);
인덱스가 생성되었습니다.

SQL> select * from emp where ename like ‘F%’ and job <> ‘SALES’; ==> 인덱스에 부정문 사용

Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id  | Operation         | Name |
———————————-
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| EMP  |
———————————-

SQL> select * from emp a where a.ename like ‘F%’ and
not exists(select ” from emp b where (a.ename = b.ename) and (b.job = ‘SALESMAN’));
==> 인덱스에 부정문 사용을 변경하여 튜닝


Execution Plan
———————————————————-
Plan hash value: 1945559707

—————————————————
| Id  | Operation                    | Name       |
—————————————————
|   0 | SELECT STATEMENT             |            |
|*  1 |  FILTER                      |            |
|*  2 |   TABLE ACCESS FULL          | EMP        |
|*  3 |   TABLE ACCESS BY INDEX ROWID| EMP        |
|*  4 |    INDEX RANGE SCAN          | I_DEPT_JOB |
—————————————————

 

SQL> drop index i_emp_job;
인덱스가 삭제되었습니다.

SQL> create index i_emp_ename on emp(ename);
인덱스가 생성되었습니다.

SQL> select * from emp where ename is not null==> 인덱스에 null 비교
14 개의 행이 선택되었습니다.


Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id  | Operation         | Name |
———————————-
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| EMP  |
———————————-

SQL> select * from emp where ename > ”==> 인덱스 null 비교를 제거하여 튜닝
선택된 레코드가 없습니다.


Execution Plan
———————————————————-
Plan hash value: 1237151973

—————————————————
| Id  | Operation                   | Name        |
—————————————————
|   0 | SELECT STATEMENT            |             |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |
|*  2 |   INDEX RANGE SCAN          | I_EMP_ENAME |
—————————————————

 

 

SQL> drop index i_emp_ename;
인덱스가 삭제되었습니다.

SQL> create index i_emp_job on emp(job);
인덱스가 생성되었습니다.

SQL> set autotrace trace
SQL> select * from emp where job like ‘%AB’; ==> %로 시작하는 like 문
선택된 레코드가 없습니다.


Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id  | Operation         | Name |
———————————-
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| EMP  |
———————————-

SQL> alter session set db_file_multiblock_read_count = 32; ==> 어차피 full table scan해야 하므로 한번에 읽어오는 갯수를 늘려서 튜닝
세션이 변경되었습니다.

 

SQL> drop index i_emp_job;
인덱스가 삭제되었습니다.

SQL> alter session set optimizer_mode = choose;
세션이 변경되었습니다.

SQL> create index i_emp_sal on emp(sal);
인덱스가 생성되었습니다.

SQL> select * from emp where nvl(sal, 0) < 4000;  ==> 인덱스에 산술공식 또는 함수 사용됨
13 개의 행이 선택되었습니다.


Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id  | Operation         | Name |
———————————-
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| EMP  |
———————————-

SQL> create index i_emp_sal_nvl on emp(nvl(sal, 0));  => function based index 를 사용
인덱스가 생성되었습니다.

SQL> alter session set QUERY_REWRITE_ENABLED = true;
세션이 변경되었습니다.

SQL> alter session set QUERY_REWRITE_INTEGRITY = trusted;
세션이 변경되었습니다.

SQL> select * from emp where nvl(sal, 0) < 4000;
13 개의 행이 선택되었습니다.

 

**** 실행계획 이상하다.. 다시 실습해 보자.. 아마도 grant를 안해서 인듯.. ****

 

 

 

인덱스 분석

 

인덱스 Balance 및 재구성
– 아까 설명 했음

불필요한 인덱스 삭제
– DML 수행시 성능 저하, 저장공간 낭비

SQL> create index i_emp_ename on emp(ename);
인덱스가 생성되었습니다.

SQL> alter index i_emp_ename monitoring usage;  ==> 모니터링 시작
인덱스가 변경되었습니다.

SQL> select index_name, used from v$object_usage;
INDEX_NAME                     USE
—————————— —
I_EMP_ENAME                    NO  ==> 현재 인덱스를 참조한 적이 없다.

SQL> select /*+index(emp i_emp_ename)*/ * from emp where ename=’KIM’;  ==> 강제로 인덱스 사용
선택된 레코드가 없습니다.

SQL> select index_name, used from v$object_usage;
INDEX_NAME                     USE
—————————— —
I_EMP_ENAME                    YES  ==> 사용했다

SQL> alter index i_emp_ename monitoring usage; ==> 모니터링 긑
인덱스가 변경되었습니다.

SQL> drop index i_emp_ename;  ==> 인덱스 삭제
인덱스가 삭제되었습니다.

 

 

인덱스 선정

 

액세스 유형의 조사 (설계단계)

– 반복 수행되는 액세스 형태를 찾는다.
– 분포도가 아주 양호한 컬럼을 찾아 액세스 유형을 찾는다.
– 자주 넓은 범위의 조건이 부여되는 경우를 찾는다.
– 자주 조건절에 사용되는 컬럼들의 액세스 유형을 찾는다.
– 자주 결합되어 사용되는 경우를 찾는다.
– SORT 의 유형을 조사한다.
– 일련번호를 부여하는 경우를 찾는다.
– 통계자료 추출을 위한 액세스 유형을 조사한다.

INDEX의 활용 (선정기준)

– 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상
– 자주 조합되어 사용되는 경우는 결합인덱스 생성
– 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담
– 가능한 수정이 빈번하지 않는 컬럼
– 기본키 및 외부키 (조인의 연결고리가 되는 컬럼)
– 결합 인덱스의 컬럼순서 선정에 주의
– 반복수행(loop 내) 되는 조건은 가장 빠른 수행속도를 내게 할 것
– 실제 조사된 액세스 종류를 토대로 선정 및 검증

INDEX의 활용 (고려사항)

– 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음
– 지나치게 많은 인덱스는 오버헤드를 발생
– 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생
– 옵티마이져를 위한 통계데이타를 주기적으로 갱신
– 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
– 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음
– 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐
– 조인(join)시에 인덱스가 사용여부에 주의

FULL TABLE SCAN 을 빠르게 하는 방법

– PARARELL QUERY 사용
– DB_FILE_MULTIBLOCK_READ_COUNT 파라메터를 크게 설정 (16 정도 잡으면 시스템에 따라 8~10 정도 읽음..)

사용자 삽입 이미지

 

분석을 위한 실습용 파일

실습 : C:\oracle\product\10.2.0\admin\orcl\udump 에 tkprof.sql 복사해 넣을것 :
sqlplus를 요 위치에서 실행할것
기존 trace 파일들은 지울것

tkprof.sql


ALTER SESSION SET SQL_TRACE=TRUE;   이라는 설정을
C:\oracle\product\10.2.0\db_1\sqlplus\admin\glogin.sql 에 저장하여 기본설정을 바꾼다.


상태 보기용 SQL

 

사용자 삽입 이미지

CC BY-NC-ND 2.0 KR

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

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

댓글 남기기