데이터베이스 튜닝이란?

책에서는 기타를 비유하여 말하고있지만, 저두 기타를 배우는 입장에서 기타를 치기위해서는 중요한 기타가 있다.
또한, 연주하는 방법 , 그리고 기타를 조율(튜닝) 을 한다.
위와, 같이 3가지 방법이 삼일일체하여야지 기타가 가질수있는 좋은 소리를 낼수있다.
데이터베이스도 마찬가지이다. 기본설계 및 DBMS 자체성능, 데이터베이스환경 설정,
SQL 문장작성 방법이 모두 데이터베이스 속도와 연관있다.

데이터베이스 튜닝을 하는 부분으로 데이터베이스 설계와 데이터베이스 환결설정 및 SQL 문장 작성으로 나눌 수 있다.

1.데이터베이스 설계 튜닝	데이터베이스 설계 단계에서 성능을 고려하여 설계
2.데이터베이스 환경	성능을 고려하여 메모리나 블록 크기 등을 지정
3.SQL 문장 튜닝	성능을 고려하여 SQl 문장을 작성

h2.① 데이터베이스 튜닝의 목적
(1)업무적인 환경과 시스템적 환경에 적합한 데이터베이스 파라미터를 설정한다
정렬 작업이 많이 발생하는 업무에서는 정렬 작업을 위한 메모리 공간을 충분히 확보해야 하지만,
읽어오는 순서대로 데이터를 조회하는 업무에서는 정렬 공간을 많이 두어 메모리를 낭비할 필요가 없다.

(2)데이터베이스에 접근하는 SQL 문장을 가능한 한 디스크 블록에 최소로 접근하도록 한다.
디스크를 적절하게 분산시켜 디스크 I/O가 집중되는 현상을 막으면 경합이 발생하지 않기 때문에 성능을 보장할 수 있다.

(3)디스크 블록에서 한번 읽은 데이터는 가능하면 메모리 영역에 보관한다.
메모리에서 데이터 가져오기와 디스크에서 메모리로 로그한 이후에 데이터를 가져오는 경우에 둘 사이의 성능 차이가 크다.
메모리에서 데이터를 읽어오는 편이 빠르므로 디스크 입출력을 최소화할 수있도록 SQL 작성한다.

(4)모든 사용자의 SQL 문장은 공유 가능하도록 명명 표준을 준수하여 작성한다
처음에 실행된 SQL 문장은 내부 파싱 절차(구문 검사, 실행 권한 체크)를 거쳐 DBMS 안에서 최적의 실행 계획을 수립한다.
동일한 SQL문장에 대해서는 파싱 작업을 다시 수행하지 않으므로 성능을 향상시킬 수 있다.

(5)잠금 발생이 최소가 되도록 한다.
데이터베이스에 여러 명의 사용자가 안정적으로 접근할 수 있도록 관리하는 잠금 기능이
전체적인 데이터베이스의 성능을 저하시키는 경우가 있다. 잠금이 최소한으로 발생하도록 트랜잭션의 분산과 같은 방법을 활용해야한다.
배치작업은 메모리나 CPU등 하드웨어 자원을 많이 사용하므로, 업무시간외에 수행될수있도록 해야된다.

h2.② 데이터베이스 설계 단계에서 튜닝하기

데이터 정합성을 유지할 수 있는 대책을 마련하고, 성능을 위해 필요하다면 테이블 , 컬럼, 관계에 대해 반정규화를 적용한다.

대용량 테이블의 경우 필요한 데이터에 대해서는 파티셔닝을 이용하여 테이블 분할을 검토한다.

이력을 관리해야 하는 테이블에 대해서는 필요하다면 시작과 종료나 현재 진행 상태등을 명확하게
명시하여 SQL 문장의 실행 성능을 보장한다.

테이블 접근 유형에 따라 전체 스캔 방식이나 B 트리 인덱스, 비트맵 인덱스, 클러스터링과 해싱적용 등을 고려한다.

테이블이 조회를 주로 하는지 입력, 수정, 삭제 작업이 주로 발생하는지를 고려하여 적당한 인덱스 개수를 지정해야 한다.
극단전으로 조회만 하는 경우에는 무방하지만,
DML 이 빈번한 테이블에서는 인덱스가 많으면 매번 인덱스를 수정하므로 성능을 저하시킨다.

분산 데이터베이스를 적용했을 경우 원격 데이터베이스를 이용할 때 성능 저하가 예상된다면
스냅샷을 이용한 복제 테이블 생성 등을 고려한다.

공통적으로 관리하는 코드 데이터에 대한 접근이 빈번하다면 애플리케이션의 메모리에 상주시키고
함수를 사용하여 코드 변환을 하도록 유도한다.

PK는 일반적으로 지정된 순서대로 인덱스를 생성하므로 키를 구성하는 컬럼 순서를 복합 컬럼 인덱스를 지정하는 규칙에 따라 나열한다.

FK에 대해서는 가급적 인덱스를 생성하여 전체 스캔이 발생하는 경우와 불필요하게 발생하는 잠금을 피하도록 한다.

복합 컬럼 인덱스를 지정하는 규칙은 첫째, 항상 조회 조건으로 사용하는 컬럼이 존재한다면 맨앞에 오게 한다.
기업의 업무에서는 일반적으로 사업소코드, 지사코드, 지점코드 또는 회사코드와 같이 지역을 나타내거나
데이터를 분리하는 단위를 첫째 컬럼으로 이용한다. 둘째 WHERE 조건절에서 '='로 비교되는 컬럼이 있으면 앞쪽에 오게 한다.
셋째는 데이터의 분포도가 낮은 컬럼이 앞쪽에 오게한다. 넷째는 정렬이 자주 발생하는 컬럼이 앞쪽에 오게한다.

SYSTEM 테이블 스페이스에는 데이터를 관리하는 딕셔너리 정보만 포함하고, 일반 오프젝트는 저장하지 않도록 한다.

테이블을 위한 테이블 스페이스와 인덱스를 위한 테이블 스페이스를 분리한다.

롤백 세그먼트에 대한 경합을 피하기 위해 롤백 세그먼트를 여러 개로 구성한다.

자주 수정되거나 변경 혹은 삭제되는 데이터는 별도의 테이블 스페이스를 만들어 생성한다.

h2.③ 데이터베이스 설계 단계에서 튜닝하기
공유 풀 튜닝
오라클의 공유 풀 영역은 라이브러리 캐시와 딕셔너리 캐시로 구분되며, 라이브러리 캐시에는 SQL문장과 PL/SQL 문장이 저장되고,
딕셔너리 캐시에는 데이터베이스에 관련된 각종 정보들이 딕셔너리로 저장된다. 오라클 데이터베이스 환경을 튜닝할 때
공유 풀(Shared Pool) 튜닝이 가장 중요하고 가장 먼저 고려해야한다.

■ 라이브러리 캐시 튜닝
라이브러리 캐시에서는 SQL 문이나 PL/SQL 문을 실행하면 실행된 문장을 파싱하여
메모리영역에 LRU 알고리즘에 따라 보관하였다가 동일한 문장이 다시 실행되면 다시 파싱하지 않고 실행 성능을 향상시킨다.


SQL> SELECT GETS, GETHITRATIO, PINS, GETHITRATIO,RELOADS, INVALIDATIONS
  2  FROM V$LIBRARYCACHE
  3  WHERE NAMESPACE= 'SQL AREA';

GETS GETHITRATIO       PINS GETHITRATIO    RELOADS INVALIDATIONS
---------- ----------- ---------- ----------- ---------- -------------
      1429  .020293912       8032  .020293912         20             0

SQL> select (sum(reloads)/sum(pins))*100 "miss"
  2  FROM V$LIBRARYCACHE;

      miss
----------
.204551266


alter sys set shared_pool_size = 1000000;

SQL> SELECT GETS, GETHITRATIO, PINS, GETHITRATIO,RELOADS, INVALIDATIONS
  2  FROM V$LIBRARYCACHE
  3  WHERE NAMESPACE= 'SQL AREA';

      GETS GETHITRATIO       PINS GETHITRATIO    RELOADS INVALIDATIONS
---------- ----------- ---------- ----------- ---------- -------------
       633  .009478673       6458  .009478673         16             0

SQL> select (sum(reloads)/sum(pins))*100 "miss"
  2  FROM V$LIBRARYCACHE;

      miss
----------
.146479905

위에서 보다시피 Miss 비율이 1% 보다 작게 나와야한다. 즉 한번 파싱 작업이 발생하여 메모리에 존재했다가
다른 문장이 메모리 영역을 다시 차지한 다른 문장과 캐시에 있는 문장을 실행하는 비율이 1%보다 작게 나와야 한다.
라이브러리 캐시의 HIT율을 향상시키기위해서
첫째, init.ora 파일에서 SHARED_POOL_SIZE를 크게한다.
둘째, SQL 문장에 대한 명명표준을 준수하여 동일한 문장에 대해서 재파싱 작업이 일어나지 않게한다.
셋째, 상수를 사용하기보다는 바인딩 변수를 사용한다.
참고로 오라클 8i 부터 상수를 사용해도 SQL문장을 공유할 수 있는 옵션을 지원.

■ 딕셔너리 캐시튜닝
딕셔너리 캐시에는 데이터베이스에 대한 각종 환경 정보와 오브젝트 생성 정보 등이 저장된 공간이다.
딕셔너리 캐시를 위해 별도로 메모리를 할당하지 않으며, SHARED_POOL_SIZE에 의해 할당된다.


alter sys set shared_pool_size = 0;

SQL> SELECT TO_CHAR(TRUNC(SUM(GETMISSES)/SUM(GETS)*100, 5),0999.99)||'%(LESS THAN 15%)'  "MISS"
  2  FROM V$ROWCACHE;

MISS
-----------------------
  24.00%(LESS THAN 15%)

TIMELESS 비율이 15%보다 작게나와야한다. 크게나옴 SHARED_POOL_SIZE를 크게하여서 15% 이하로 만든다.


alter sys set shared_pool_size = 100000;
show parameters shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
shared_pool_size                     big integer 4M

SQL> SELECT TO_CHAR(TRUNC(SUM(GETMISSES)/SUM(GETS)*100, 5),0999.99)||'%(LESS THAN 15%)'  "MISS"
  2  FROM V$ROWCACHE;

MISS
-----------------------
  23.04%(LESS THAN 15%)

별차이가 없어서 값을 더크게 주었다.




alter sys set shared_pool_size = 1234567;
show parameters shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
shared_pool_size                     big integer 12M

MISS
-----------------------
  23.35%(LESS THAN 15%)

위쪽테스트의 values값은 4M 였다. 책에서는 사이즈를 크게하면 좋아진다고했는데 더안좋아졌다.

데이터베이스 버퍼 캐시 튜닝
SQL 문장을 실행하면 오라클은 디스크에서 데이터를 읽어 들여 이를 메모리로, 즉 데이터베이스 버퍼 캐시에 저장한다.
데이터베이스에서 원하는 데이터를 빠르게 조회하기 위해서는 디스크보다는 메모리에서 읽는 편이
성능이 좋으므로 자주 사용되는 데이터를 버퍼 캐시에 저장할 수 있도록 환경을 설정한다.
버퍼 캐시에 영향을 주는 파라미터가 DB_BLOCK_BUFFER의 크기를 조절해야한다.

db block gets : DML 문의 처리과정에서 처럼 데이타 버퍼 캐시 영역에 있는 변경후 데이타를 읽는 블록수
consistent gets : 변경작업 후 ROLLBACK문 실행시 변경전 데이터로 복구하기 위해 이전 데이터를 저장해 주는 메모리 영역으로 부터 읽은 블록 수
physical reads : 사용자가 SQL문 실행 했을때 data files로 부터 table을 읽는 블록수


SQL> show parameters db_block_buffer;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
db_block_buffers                     integer     0

SQL> SELECT (1 -(PHY.VALUE /(CUR.VALUE+CON.VALUE)))  * 100 "CACHE HIT RATIO"
  2  FROM V$SYSSTAT CUR, V$SYSSTAT CON, V$SYSSTAT PHY
  3  WHERE CUR.NAME ='db block gets'
  4    AND CON.NAME ='consistent gets'
  5    AND PHY.NAME ='physical reads';

CACHE HIT RATIO
---------------
     96.4127823

SQL> alter system set db_cache_size =123456789;

SQL> show parameters db_cache_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 120M

SQL> SELECT (1 -(PHY.VALUE /(CUR.VALUE+CON.VALUE)))  * 100 "CACHE HIT RATIO"
  2  FROM V$SYSSTAT CUR, V$SYSSTAT CON, V$SYSSTAT PHY
  3  WHERE CUR.NAME ='db block gets'
  4    AND CON.NAME ='consistent gets'
  5    AND PHY.NAME ='physical reads';

CACHE HIT RATIO
---------------
     90.5298087

버퍼캐시 재사용율 공식
( HITS = 100 * (1-( PHYSICAL READS/ DB BLOCK GETS+ CONSISTENT GETS)))
시스템별로 다르겠지만 보통 80%이하로 값이 나오면 버퍼 캐시의 크기를 증가시키는 편을 고려해봐야한다.

리두로그 버퍼 튜닝
리두로그 버퍼는 모든 SQL 문장이 실행될 때 이용하는 메모리 영역이므로 작업하는 동안은 이 메모리 공간을 이용해야 한다.
데이터에 대해 수정이 발생하면 잠금을 설정하듯이 메모리 영역에서 다른 프로세스가 이용하지 못하도록 독점하는 방법으로
래치(Latch)를 할당받아 작업한다. 래치를 할당받지 못한 SQL 문장들은 래치를 할당받을 때까지 기다려야 한다.
그러므로 대기율을 분석하여 오랜 시간동안 대기하고 있거나 잦은 대기 상태가 되는 경우에는 리두로그 버퍼의 크기를 조정해야한다.



SQL> show parameters log_buffer;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_buffer                           integer     6984704

SQL> SELECT A.NAME, ( B.MISSES/B.GETS)*100 WAIT_RATIO
  2  FROM V$LATCHNAME A, V$LATCH B
  3  WHERE A.NAME IN('redo allocation')
  4    AND A.LATCH# = B.LATCH#;

NAME                                               WAIT_RATIO
-------------------------------------------------- ----------
redo allocation                                             0

SQL> SELECT NAME,VALUE
  2  FROM V$SYSSTAT
  3  WHERE NAME ='redo log space requests' ;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log space requests                                                   0

디스크 I/O 튜닝
특정 디스크에 있는 데이터 파일에 I/O가 집중되면 이는 성능 저하의 요인이 되므로 I/O 현황을 분석하여 집중된 I/O를 분산시켜야 한다.
특히 테이블 스페이스와 데이터 파일을 설계할 떄 가장 밀접한 관련이 있는 부분으로 보통 핵심 업무를 예로 들면,
인사 관리에서는 급여사항, 보험 업무에서는 사건이 고객과 같이 주로 발생하는 업무 프로세스가
특정 데이터 파일을 이용할 때 I/O 집중현상이 발생된다.


SQL> SELECT NAME,PHYRDS,PHYWRTS,PHYBLKRD,PHYBLKWRT
  2  FROM V$FILESTAT A, V$DATAFILE B
  3  WHERE A.FILE# = B.FILE#;

NAME                                                   PHYRDS    PHYWRTS   PHYBLKRD  PHYBLKWRT
--------------------------------------------------- --------- ---------- ---------- ----------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF      4763        105       5698        156
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF       30        155         30        326
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF       522        525        887        772


NAME                                                   PHYRDS    PHYWRTS   PHYBLKRD  PHYBLKWRT
--------------------------------------------------- --------- ---------- ---------- ----------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF         5          1          5          1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF       7          1          7          1

만약 물리적인 읽기 쓰기 작업이 특정 디스크에 집중적으로 발생한다면 해당 데이터 파일을 다른 디스크로
옮기거나 테이블 스페이스를 여유가 있는 데이터 파일에 생성해야한다.
위의 파일에는 SYSAUX01.DBF에 물리적인 I/O가 집중되었으므로 데이터파일을
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF에 대해서 I/O를 분석한다.


SQL> SELECT F.TABLESPACE_NAME T0, F.FILE_NAME T1, X.PHYRDS T2,
  2         TO_CHAR(TRUNC((PHYRDS/TOT_RDS)*100,2),99.99)||' %' T3,
  3         X.PHYWRTS T4,
  4         TO_CHAR(TRUNC((PHYWRTS/TOT_WRTS)*100,2),99.99)||' %' T5,
  5         ROUND((I.BYTES/1024)/1024) T6
  6  FROM V$FILESTAT X , DBA_DATA_FILES F, V$DATAFILE I,
  7       (SELECT SUM(PHYRDS) TOT_RDS,SUM(PHYWRTS) TOT_WRTS
  8       FROM V$FILESTAT) TOT_ID
  9  WHERE X.FILE# = F.FILE_ID
 10    AND F.FILE_ID = I.FILE#
 11  ORDER BY F.TABLESPACE_NAME;

T0------------------------------T1----------------------- T2------- T3-------T4-------T5-------T6
EXAMPLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF     7    .13 %          1    .09 %        100

SYSAUX
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF    522   9.79 %        702  67.69 %        250

T0------------------------------T1----------------------- T2------- T3-------T4-------T5-------T6
SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF      4763  89.41 %        133  12.82 %        480

UNDOTBS1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

T0------------------------------T1----------------------- T2------- T3-------T4-------T5-------T6
                                                           30    .56 %        200  19.28 %     60
USERS
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF         5    .09 %          1    .09 %          5

시스템 테이블 스페이스 별도로 생성한다.
임시 테이블 스페이스는 테이블과 인덱스의 테이블 스페이스와 분리하여 생성한다.
롤백 세그멈ㄴ트와 온라인 리두로그 파일은 분리하여 생성한다.
온라인 리두로그 파일과 아카이브 리두로그 파일은 분리하여 생성한다.
롤백 세그먼트와 테이블과 인덱스 테이블 스페이스와 분리하여 생성한다.
테이블의 테이블 스페이스와 인덱스의 테이블 스페이스는 분리하여 생성한다.
리두로그 파일은 지속적으로 I/O가 발생하므로 I/O가 가장 적은 디스크에 배치한다.
테이블과 인덱스 테이블 스페이스 중에 I/O가 많은 테이블 스페이스는 별도의 디스크에 배치한다.

로우 마이그레이션과 로우 체이닝
로우 마이그레이션은은 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고,
다른 블록의 빈 공간을 찾아 데이터를 저장하는 방식이 로우 마이그레이션이다. 로우 체이닝은 로우 길이가 너무 길어서
데이터 블록 하나에 데이터가 모두 저장되지 않고, 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태이다.
로우 마이그레이션과 로우 체이닝 모두 데이터를 입력하거나 수정할 때 추가적인 작업이 발생되어야 하므로 성능 저하의 원인이 된다.
로우 마이그레이션 현상은 보통 PCTFREE가 낮게 설정되어 있는 경우에 나타나고, 로우 체이닝 현상은 데이터베이스를 생성할 때 지정하는 DB_BLOCK_SIZE를 너무 작게 지정하거나 데이터 타입이 LOB 타입과 같이 대용량 타입일 경우에 발생한다.


SQL> CREATE TABLE BSH_TABLE AS
  2  SELECT * FROM HR.DEPARTMENTS;

SQL> SELECT NUM_ROWS,CHAIN_CNT FROM ALL_TABLES WHERE TABLE_NAME ='BSH_TABLE';

  NUM_ROWS  CHAIN_CNT
---------- ----------

SQL> ANALYZE TABLE BSH_TEST COMPUTE STATISTICS;

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

  NUM_ROWS  CHAIN_CNT
---------- ----------
       107          0

*로우 체이닝 확인*
SQL> @?/rdbms/admin/utlchain.sql

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

UPDATE BSH_TEST
SET FIRST_NAME ='ABCDEFAAAAAA';

COMMIT;

SQL> ANALYZE TABLE BSH_TEST LIST CHAINED ROWS INTO CHAINED_ROWS;

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

SELECT COUNT(*)
FROM CHAINED_ROWS
WHERE TABLE_NAME =UPPER('BSH_TEST');

  COUNT(*)
----------
        11

SQL> ANALYZE TABLE BSH_TEST COMPUTE STATISTICS;

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

SQL> SELECT NUM_ROWS, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME ='BSH_TEST';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       107         11

h2.④ 데이터베이스 설계 단계에서 튜닝하기

데이터베이스 성능에 절대적으로 영향을 주는 요소는 SQL 에 의해 결정된다.
초보자가 만드는 기타소리와 달리, 신대철 , 김도균 , 김태원 같은 3대 기타리스트들이 내는 기타소리가 다른거처럼,
SQL 자체도 초보자의 쿼리와 마농님과 같은 쿼리고수가 만드는건 확연한 차이가 존재한다.

SQL 문장을 작성할 때 꼭 알고 있어야 할 사항

업무에서 사용하는 SQL 문장은 단순히 결과만 조회하는 것이 아니라 일정한 성능을 보장해야 한다.
아무리 훌륭하게 만든 테이블을 생성하였지만 SQL 문장이 그 훌륭하게 만든 테이블설계를 무시하고 만든 SQL은 절대로 성능을 보장할 수 없다.

접근 경로에 대한 우선순위를 알고 있어야 한다.
옵티마이저 모드가 비용 기반 모드인지 규칙 기반 모드인지 알고 있어야한다.
숫자 형식은 숫자 형식의 타입으로 문자형식은 문자형식으로 타입으로 비교한다.
WHERE 절 안에 비교하는 조건에 인덱스를 이용해야 할 경우 인덱스 컬럼을 기술한다.
여러 개의 컬럼이 인덱스 하나로 지정된 경우 WHERE 절에 모두 기술한다.
여러 개의 컬럼이 인덱스 하나로 지정된 경우 앞쪽에 있는 컬럼을 모두 지정하여 인덱스 범위를 조회할 수 있게 한다.
인덱스를 이용하고자 하는 컬럼을 변현하지 않는다.
인덱스 컬럼에 NULL 값을 사용하지 않는다.
부정형으로 지정할 경우에는 인덱스를 사용하지 않는다. 가능하면 부정형을 사용하지 않는다.
OR 를 사용하면 인덱스를 이용하지 않는다.
불필요하게 DUAL 테이블을 이용하지 않는다.
WHERE 조건절에 걸린 인덱스가 데이터를 20% 이상 반환하리라 예상되면 인덱스 스캔을 피한다.
불필요하게 DBMS에서 제공하는 함수(SUM, SIGN, DECODE, NVL 등) 사용하지 않는다.
인덱스가 걸려있는 컬럼에 대해 LIKE 형식으로 비교하는 경우에는 반드시 뒤쪽에 비교자(%)가 위치해야 하며,
앞쪽에 위치할 경우에는 인덱스를 이용하지않는다.
힌트를 적절하게 사용한다.

■ 접근 경로에 대한 우선순위를 알고 있어야 한다.

순위	예	순위	예
1	ROWID에 의한 단일 로우	9	단일 컬럼 인덱스
2	클러스터 조인에 의한 단일 로우	10	인덱스기 구성된 컬럼에서 제한된 범위 검색
3	유일하거나 PK를 가진 해시 클러스터키에 의한 단일 로우	11	인덱스가 구성된 컬럼에서 무제한 범위 검색
4	유일하거나 PK에 의한 단일 로우	12	정렬-병합 조인
5	클러스터 조인	13	인덱스가 구성된 열에서 MAX 또는 MIN
6	해시 클러스터 키	14	인덱스가 구성된 열에서 ORDER BY
7	인덱스 클러스터 키	15	풀 테이블 스캔
8	복합 컬럼 인덱스	15

옵티마이저 모드가 비용 기반 모드인지 규칙 기반 모드인지 알고 있어야 한다.
오라클의 옵티마이저는 SQL 문장이 실행 될 때 DBMS에서 내부적으로 최적화하는 방법이다.
오라클의 옵티마이저는 비용 기반모드(CBO)와 규칙기반모드(RBM)를 제공한다.
규칙기반모드는 ALALYZE를 이용하여 생성한 통계정보를 활용하지 않고,
오라클에서 정한 접근 경로에 대한 우선순위에 근거하여 실행 계획을 선택한다.
비용기반모드는 ALALYZE를 이용하여 생성한 통계 정보를 활용하여 가장 빠른 접근 경로가 무엇인지 결정하여 실행계획을 수립한다.


SQL> DROP TABLE BSH_tEST2 PURGE;

테이블이 삭제되었습니다.

SQL> CREATE TABLE BSH_TEST2 AS
  2  SELECT LEVEL COL1
  3           , TRUNC(dbms_random.value( 1, 10000 ) ) COL2
  4           , TO_CHAR(TRUNC( SYSDATE ) - 100 + CEIL( LEVEL/1000),'YYYYMMDD') C
OL3
  5           , '1111' COL4
  6           , 'AAAA' COL5
  7    FROM DUAL
  8  CONNECT BY LEVEL <= 100000;

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

SQL>  UPDATE BSH_TEST2
  2   SET COL4 ='2222'
  3   WHERE ROWNUM <25000;

24999 행이 갱신되었습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> CREATE INDEX COL4_IDX01 ON BSH_TEST2(COL4 );

인덱스가 생성되었습니다.

SQL> EXPLAIN PLAN FOR
  2  SELECT COL1
  3  FROM BSH_TEST2
  4  WHERE COL4 ='2222'
  5  AND COL3 BETWEEN '20110701' AND '20110715'
  6  ORDER BY COL1;

해석되었습니다.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            | 12797 |   312K|       |   147   (3)| 00:00:02 |
|   1 |  SORT ORDER BY               |            | 12797 |   312K|   920K|   147   (3)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BSH_TEST2  | 12797 |   312K|       |    51   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | COL4_IDX01 |    51 |       |       |    50   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------------


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

   3 - access("COL3">='20110701' AND "COL4"='2222' AND "COL3"<='20110715')
       filter("COL4"='2222')

-- 통계정보수집
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'BSH_TEST2');

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

SQL> EXPLAIN PLAN FOR
  2  SELECT COL1
  3  FROM BSH_TEST2
  4  WHERE COL4 ='2222'
  5  AND COL3 BETWEEN '20110701' AND '20110715'
  6  ORDER BY COL1;

해석되었습니다.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  3743 | 71117 |   109   (8)| 00:00:02 |
|   1 |  SORT ORDER BY     |           |  3743 | 71117 |   109   (8)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| BSH_TEST2 |  3743 | 71117 |   107   (6)| 00:00:02 |
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - filter("COL3"<='20110715' AND "COL4"='2222' AND
              "COL3">='20110701')

숫자 형식은 숫자 형식의 타입으로, 문자는 문자 형식 타입으로 비교한다.
WHERE 절에 기술된 비교 형식을 일치 시키지 않으면 비록 변환에 문제가 없는 형식이라고 할지라도
DBMS 내부적으로 한 번 더 작업이 발생하게 되므로 성능에 영향을 준다.
예를 들어, 숫자 형식인 DEPTNO ='10'과 같이 작성하지 말고 DEPTNO=10과 같이 작성한다. 문자 형식은 DEPTNM =영업부 가 아니라 DEPTNM='영업부'와 같이 작성한다.



SQL> desc bsh_test2;
 이름                                      널?      유형
 ----------------------------------------- -------- ---------------------------

 COL1                                               NUMBER
 COL2                                               NUMBER
 COL3                                               VARCHAR2(8)
 COL4                                               CHAR(4)
 COL5                                               CHAR(4)

SQL> EXPLAIN PLAN FOR
  2  SELECT COL1
  3  FROM BSH_TEST2
  4  WHERE COL4 =2222  <-- char(4)  부분
  5  AND COL3 BETWEEN '20110701' AND '20110715'
  6  ORDER BY COL1;

해석되었습니다.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  7472 |   138K|       |   156   (6)| 00:00:02 |
|   1 |  SORT ORDER BY     |           |  7472 |   138K|   488K|   156   (6)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| BSH_TEST2 |  7472 |   138K|       |   107   (6)| 00:00:02 |
---------------------------------------------------------------------------------------

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

   2 - filter("COL3"<='20110715' AND TO_NUMBER("COL4")=2222 AND
              "COL3">='20110701')

WHERE 절의 비교 조건에 인덱스를 이용해야 할 경우 인덱스 컬럼을 기술한다.



SQL> select table_name, index_name, column_position , column_name
  2  from user_ind_columns
  3  where table_name ='BSH_TEST2';

TABLE_NAME                     INDEX_NAME                     COLUMN_POSITION            COLUMN_NAME
------------------------------------------------------------------------------------------------------
BSH_TEST2                      COL4_IDX01                                   1                 COL3

■ 여러 개의 컬럼이 하나의 인덱스로 지정된 복합 커럼 인덱스인 경우 WHERE 절에 모든 컬럼을 기술한다.



CREATE TABLE BSH_TEST2 AS
SELECT LEVEL COL1
         , TRUNC(dbms_random.value( 1, 10000 ) ) COL2
         , TO_CHAR(TRUNC( SYSDATE ) - 100 + CEIL( LEVEL/1000),'YYYYMMDD') COL3
         , '1111' COL4    
         , 'AAAA' COL5    
  FROM DUAL
CONNECT BY LEVEL <= 100000;

CREATE INDEX COL4_IDX01 ON BSH_TEST2( COL1, COL2,COL3 );

-- WHERE 절에 2개의 컬럼만 조건검색
SQL> explain plan for
  2  select *
  3  from bsh_test2
  4  where col3 ='20110625'
  5  and col2 ='57';

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    10 |   440 |   106   (5)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| BSH_TEST2 |    10 |   440 |   106   (5)| 00:00:02 |
-------------------------------------------------------------------------------


-- WHERE 절에 3개의 컬럼만 조건검색

SQL> explain plan for
  2  select *
  3  from bsh_test2
  4  where col1 ='89'
  5  and col3 ='20110625'
  6  and col2 ='57';
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    44 |     2   (0)|00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BSH_TEST2  |     1 |    44 |     2   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN          | COL4_IDX01 |     1 |       |     1   (0)|00:00:01 |
------------------------------------------------------------------------------------------

■ 여러 개의 컬럼이 인덱스 하나로 지정된 경우 앞쪽에 있는 컬럼을 모두 지정하여 인덱스 범위를 조회할 수 있게한다.

■ 인덱스를 이용하고자 하는 컬럼에 대해 변형을 하지 않는다.
WHERE 절부분에 함수를 사용하면 인덱스는 타지않는다.
EX) WHERE SUB(COL,1,2) = '12' , WHERE COL||'' = '1234' , WHERE 숫자+0 = 10000

■ 인덱스 컬럼에 NULL/NOT NULL 값을 사용하지 않는다.

■ 부정형으로 지정할 경우에는 인덱스를 사용하지 않는다. 가능하면 부정형을 사용하지 않는다.

■ OR를 사용하면 인덱스를 이용하지 않을 수 있다.

■ 불필요하게 DUAL 테이블을 이용하지 않는다.

■ WHERE 조건절에 걸린 인덱스가 데이터의 15% 이상을 반환하릴 예상되면 인덱스 스캔을 피한다.

■ 불필요하게 DBMS에서 제공하는 함수(SUM, SIGN, DECODE, NVL 등)을 사용하지 않는다.

*■ 인덱스가 걸려있는 컬럼에 대해 LIKE 형식으로 비교하는 경우에는 반드시 뒤쪽에 비교자(%)가 위치해야 하며,
앞쪽에 위치할 경우에는 인덱스를 이용하지 않는다.*

■ 힌트를 적절하게 사용한다
흰트는 오라클 옵티마이저에게 SQL문장에 개발자가 원하는 방법으로 실행계획을 수립하도록 내리는 명령이다.
사용법은 /*+ 힌트 */나 --힌트+로 사용한다.

(1) 최적화한 접근 목적을 위한 힌트

힌트	설명
ALL_ROWS	전체 리소스를 최소화하는 비용 기반 모드로 실행한다.
FIRST_ROWS	첫 번째 로우를 가장 빨리 반환하는 접근 방법으로 실행한다.
CHOOSE	옵티마이저가 SQL문장에 대해 실행 계획을 수립할 때 비용 기반으로 적용할 것인지 규칙 기반으로 적용할 것인지를 데이터 딕셔너리의 정보를 보고 결정한다.
RULE	옵티마이저가 규칙 기반 모드로 실행 계획을 수립하게 한다. 다른 힌트는 쓰지 않는다.

(2) 접근 방법을 위한 힌트

힌트	설명
FULL	지정한 테이블에 대한 전체 스캔 방식으로 실행된다.
ROWID	지정한 테이블에 대해 rowid를 이용하여 실행된다.
CLUSTER	클러스터가 된 오브젝트에 적용되며, 지정한 테이블에 대해 클러스터 스캔을 수행한다.
HASH	클러스터가 된 오브젝트에 적용되며, 지정한 테이블에 대해 해시 스캔을 수행한다.
INDEX	인덱스를 이용하여 실행한다.
INDEX_ASC	인덱스방향 인덱스를 이용하여 스캔한다.
INDEX_COMBINE	2개 이상의 인덱스를 지정한 테이블에 대해 비트맵 접근 경로를 선택한다. 비트맵 처리방법은 지정된 인덱스 조건에 따라 0과 1을 부여하여 접근한다.
INDEX_JOIN	인덱스 조인을 이용하여 실행한다.
INDEX_DESC	역방향 인덱스를 이용하여 스캔한다.
INDEX_FFS	전체 인덱스 스캔을 빠르게 수행한다.
NO_INDEX	지정된 테이블에 대해 인덱스를 무시한다.
AND_EQUAL	여러 개의 단일 컬럼 인덱스를 묶어서 실행한다.
USE_CONCAT	WHERE절에 'IN'이 'OR'를 사용할 때 이를 집합 연산 SQL 문장(UNION ALL)을 이용하여 실행한다.
NO_EXPAND	'OR'나 'IN'이 WHERE 절에 있을 때 비용 기반 모드의 옵티마이저는 'OR'나 'IN'에 기술된 비용을 참조하지 않고 실행된다.
REWRITE	뷰를 이용하여 실행된다.
NOREWRITE	QUERY_REWRITE_ENABLED를 비활성한다.

(3) 조인 순서를 위한 힌트

힌트	설명
ORDERED	FROM 절에 기술한 테이블 순서대로 실행된다.
STAR	STAR QUERY 방식으로 실행된다. 최소한 테이블이 세 개는 존재해야 한다.

(4) 조인 방법을 위한 힌트

힌트	설명
USE_NL	기준이 되는 테이블 로우를 이용하여 다른 테이블에서 기준에 맞는 로우에 접근할 때 Nested Loop Join 방법을 사용한다.
USE_MERGE	각각의 테이블을 먼저 정렬하여 정렬된 순서대로 비교하는 Sort Merge Join 방식으로 실행된다.
USE_HASH	기준이 되는 테이블 로우를 이용하여 다른 테이블에서 기준에 맞는 로우에 접근할때 Hash Join 방법으로 실행된다.
DRIVING_SITE	다른 지역에 존재하는 정보를 이용하여 실행 계획을 수립하게 한다. SELECT /* DRIVING_SITE(ITEM) FROM ORDER, ITEM@JISA WHERE ORDER.ORDERNO - ITEM.ORDERNO;
LEADING	조인 순서에서 지정된 테이블을 가장 먼저 사용된다.
HASH_AJ	부정형의 조인 방법(Anti Join)을 Hash Join으로 실행한다.
MERGE_AJ	부정형의 조인 방법(Hash Join)을 Sort Merge Join으로 실행한다.
HASH_SJ	Exists 서브쿼리 문장에서 Hash Semi-Join으로 실행된다. 만약 기준이 되는 테이블 로우에 다른 테이블에서 만족하는 로우가 하나 이상 존재한다면 로우는 한번만 반환된다.
MERGE_SJ	Exists 서브쿼리 문장에서 Sort Merge Semi-Join으로 실행된다. 만약 기준이 되는 테이블 로우에 대해 다른 테이블에서 만족하는 로우가 하나 이상 존재한다면 로우는 한번만 반환된다.

(5) 병행 수행을 위한 힌트

힌트	설명
PARALLEL	병렬 처리
NOPARALLEL	병렬 처리를 이용하지 않는다.

(6) 기타 부가적인 힌트

힌트	설명
CACHE	최근에 이용한 데이터를 데이터 버퍼 캐시에 보관하여 다시 동일한 데이터에 접근할 때 디스크 I/O를 줄인다.
NOCACHE	다른 테이블에서 데이터버퍼 캐시를 이용할 수 있도록 지정된 테이블은 캐시되지 않는다.

h2.⑤ 번번하게 성능에 영향을 주는 기타 요소

테이블에 일련번호(순번)를 증가시키는 방법
데이터 모델링을 진행할 때 업무적으로 PK로 선정할 컬럼이 있으면 다행이지만, 그렇지 않은 경우에는
일련번호(순번) 이용하여 P로 활용한다. 일련번호는 사용자 인터페이스를 통해 받아오는 데이터가 아니므로
데이터를 입력 할때 SQL 문장에서 채번을 수행해야 하는 특성이 있다.


-- 두개의 SQL 문장으로 처리
SELECT MAX(주문일련번호)+1
INTO :V_주문일련번호
FROM 주문;

INSERT INTO 주문(주문일련번호, COL2, COL3....)
VALUES (V_주문일련번호, 'XXX', 'XXX'.....)

두 번의 SQL 문장이 실행되었으므로 성능 저하가 예상된다.

-- MAX(컬럼)+1을 이용한 채번 방법

INSERT INTO 주문(주문일련번호, COL2, COL3.....)
SELECT DECODE(MAX(주문일련번호),NULL,0,MAX(주문일련번호))+1 주문일련번호, :COL2값 ...
FROM 주문

데이터 양이 적으면 단순히 처리되겠지만, 여러건일경우 여러번의 MAX값을 처리하기위해서 그룹핑하는
부하가 발생하므로 성능저하될것으로 예상.

다음과 같이 작성하면 성능 저하 현상도 발생하지 않는다.
INSERT INTO 주문(주문일련번호, CLO2, CLO3...)
SELECT DECODE(MAX(주문일련번호), NULL, 0, MAX(주문일련번호))+1 주문일련번호, :COL2값....
FROM 주문
WHERE 지점코드 ='XX'
AND 주문일자 = '2002-01-01' <== 조건검색으로 MAX의 부하를 줄인다.

-- 시퀀스 테이블을 이용한 채번 방법

1)시퀀스를 생성한다.
CREATE SEQUENCE 주문_SEQ
INCREMENT BY 1;

2)데이터를 입력할 때 시퀀스값을 이용한다.
INSERT INTO 주문(주문일련번호, COL2, COL3....)
VALUES (주문_SEQ.NEXTVAL, 'XXX','XXX'....);

시퀀스 테이블은 단순 조회만 해도되는 일련번호가 증가하면서 이빨이 빠질수 있다.
주문일련번호의 의미를 두고자 한다면 사용하지 말자.

-- 채번 테이블을 이용한 방법

1)신규 일련번호 채번
SELECT 최종일련번호 + 1
INTO :V_주문일련번호
FROM 주문;
WHERE 지점코드 ='01'
AND 주문구분 ='인터넷'

2)본 테이블에 데이터 입력 
INSERT INTO 주문(주문일련번호, COL2, COL3....)
VALUES (V_주문일련번호, 'XXX','XXX'....);

3) 채번 테이블 수정
UPDATE 주문채번테이블
SET 최종일련번호 = :V_주문일련번호 
WHERE 지점코드 ='01'
AND 주문구분 ='인터넷'

-- 인덱스 역방향 정렬을 이용한 채번 방법.
INSERT INTO 주문(주문일련번호, COL2, COL3 ....)
SELECT /*+ INDEX_DESC(B I_01) */ <--- 인덱스가 I_01일때 인덱스를 역정렬함
        DECODE(주문일려번호,NULL1,주문일련번호+1),'XXXX','XXX',....
FROM 주문 A,
     ( SELECT 0 SER FROM DUAL) B
WHERE A.주문일련번호 =B.SER(+)  <-- 데이터가 하나도 없을때 채번
AND ROWNUM =1;

갑작스런 대용량 테이블의 성능 저하 원인과 조치 방법

생성된 인덱스가 깨진 경우
(1) 인덱스에 대해 ANALYZE를 수행
ANALYZE INDEX 인덱스명 ESTIMATES STATISTICS;
대용량 데이터에 대해 COMPUTE로 실행하면 시간이 많이 소유되므로 ESTIMATES를 활용

인덱스 레벨이 깊어진 경우
(2) 인덱스 레벨을 검사한다.
SELECT BLEVEL,LEAF_BLOCKS FROM USER_INDEXS WHERE INDEX_NAME '인덱스명';
BLEVEL : 인덱스 깊이를 나타냄
LEAF_BLOCKS : 인덱스의 최종 단위인 리프 블록수
자세한내용은 7장 인덱스구조에서 확인.

인덱스 구조에 수정.삭제가 자주 발생한 결과 불필요한 인덱스로 인해 성능이 저하되는 경우
(3) 인덱스를 대체한다.
ALTER INDEX 인덱스명 REBUILD;
깨진 인덱스에 대해서 재생성처리.

11장. 데이터베이스 튜닝 (by bshman) [2011.10.01]

데이터베이스 튜닝이란?