DBA의 정석

DB Admin Knowhow

DB Tunning(TABLE,SQL,...)

SQL 튜닝의 기본 지식

Byhaisins

9월 23, 2015 ,

☞How to Use Indexs

●  인덱스 생성 시기
●  인덱스 생성 대상 column 선택
●  결합 인덱스 사용 방법
●  인덱스 사용을 위한 SQL문 작성법

♣ When to Create Indexes

▶  질의어에서 select하는 row의 비율이 적을 경우, 인덱스를 사용하면 질의의 성능이 향상
▶  테이블에 인덱스를 생성하는 지침 : table의 전체 row중의 2%에서 4%이하를 검색하는 질의가 자주 발생할 경우
▶  이 지침은 다음 가정을 기반으로 함.

· 질의에서 선택된 column의 값이 동일한 Rows들은 그 Table에 할당된 data block에 균일하게 분산되어있음.
· Table의 row는 질의되는 column에 대하여 불규칙적으로 분포되어 있음.
· Table에 할당된 각 data block은 최소한 10개의 row를 포함
· Table은 상당히 작은 수의 column을 가진다.
· Table에 대한 대부분의 질의들은 비교적 단순한 WHERE절을 가진다.
· Cache hit ratio는 낮고 operating system cache는 없다.

 

♣ How to Choose Columns to Index

▶  인덱스를 생성할 column을 선택하기 위한 지침

·  Where절에서 자주 사용되는 column에 대한 인덱싱 고려
· SQL문에서 join을 위해 자주 사용되는 column에 대한 인덱싱 고려
· 좋은 분포도를 가지는 column에 대한 인덱싱

      ■ NOTE
         ·  Oracle은 intergrity constraints로 정의한 모든 unique key와 primary keys를 가진 column에 대해
            인덱스를 자동적으로 생성.
         ·  이러한 인덱스들은 최고의 분포도를 가지며, 성능 최적화 과정에서 가장 효과적이다.
         · 인덱스된 column에 대한 분포도 : 테이블의 전체 rows 수와 해당 column의 서로 다른 값의 수로 나눈 비율
         ·  ANALYZE 명령을 사용하여 분포도를 구함.

· 서로 다른 값이 거의 없는 column에 대해서는 인덱스를 생성하지 않는다.
· 자주 수정되는 column은 인덱스를 생성하지 않는다.
· WHERE절에서 function이나 operator내에서만 사용되는 column에 대해서는 인덱스를 생성할 필요가 없다.
· 다수의 INSERT, UPDATE, DELETE문이 parent와 child table을 동시에 access하는 경우에는 referential integrity constraints의 foreign key를 인덱싱하는 것을 고려해야 한다.
· column에 대한 인덱스 생성여부는, 질의에 대한 성능 이득, INSERT, UPDATE, DELETE문 대한 성능 손실, 그리고 인덱스에 대한 저장 공간을 고려하여야 한다.

♣ How to Choose Composite Indexes

■결합인덱스(Composite index)
· 결합 인덱스(Composite index) : 하나 이상의 칼럼으로 생성된 인덱스

■결합인덱스의 장점
· 좋은 분포도 : 나쁜 분포도를 가진 column을 결합한 결합 인덱스가 더 좋은 분포도를 가질 수 있다.
· 저장 공간의 효율성 : 한 질의에 의해 선택된 모든 칼럼이 결합인덱스에 있을 경우, table을 access하지 않고 결합 인덱스 만으로 원하는 값을 가져올 수 있다.


■결합인덱스의 사용
·  결합 인덱스의 선행 부분(leading portion)을 사용하는 SQL문인 경우, 결합 인덱스에 의한 access path 사용 가능
·  선행부분
: CREATE INDEX문에 의해 생성된 결합 인덱스를 구성하는 column list에 있는 첫 번째를 포함한 연속적인 칼럼의 집합.
·CREATE INDEX comp_ind
              ON tab1(x, y, z);
· 여기서 X, XY, XYZ는 결합 인덱스의 선행 부분이다. 그러나 YZ, Z는 선행부분이 아니다.


▶  결합 인덱스의 column선택을 위한 지침

· 각 column의 분포도 보다 결합 인덱스에서 결합된 분포도가 더 좋을 경우
· 여러 질의에서 하나이상의 칼럼값을 가진 칼럼의 동일한 집합을 질의할 경우, 이들 모든 칼럼을 포함하는 결합 인덱스 생성을 고려한다.

▶  결합 인덱스 구성시 column순서 배치를 위한 지침
· WHERE절에 사용된 칼럼을 선행부분으로 만들기 위한 결합 인덱스를 생성
· 칼럼의 일부가 WHERE절에서 자주 사용될 경우 => 자주 select되는 column을 선행부분올 만들어서 이 column만으로 인덱스를 사용할 수 있도록 한다.
· 모든 칼럼이 WHERE절에서 동일하게 사용되면 => 질의 성능을 개선하기 위하여 CREATE INDEX statement에서 분포도가 좋은 순서대로 배열
· 모든 칼럼이 WHERE절에서 동일하게 자주 사용되지만 데이터가 한 column에 대해 물리적으로 정렬되어 있으면 => 그 column을 결합 인덱스의 첫번째로 구성

 

♣ How to Write Statements That Use Indexes

▶  인덱스 생성 후, optimizer는 단순히 인덱스가 존재한다는 이유만으로 인덱스를 사용하는 access path를 사용하지 않음.
▶  Optimizer는 SQL문이 인덱스를 사용할 수 있는 형태를 가지는 경우에만 그 인덱스를 사용하는 access path를 선택할 수 있다.
▶  SQL문이 Index를 사용하는 access path의 사용을 위하여, 그 SQL문이 인덱스를 사용할 수 있는 형태를 포함 해야 함.
▶  Cost-based approach를 사용할 경우에는 해당 인덱스에 대한 statistics를 생성해야 함.
▶  SQL문이 특정 access path를 사용할 수 있도록 구성하였더라도 다른 종류의 access path에 대한 유용성에 따라 그 access path를 선택하거나 선택하지 않을 수 있음.

 

♣ How to Write Statements That Avoid Using Indexes

▶  경우에 따라서 SQL문에 대해서 기존 인덱스를 사용하는 access path의 사용을 강제적으로 억제 가능, (특정 인덱스의 분포도가 너무 나빠서 오히려 full table scan이 더 효과적인 경우에 인덱스의 사용을 억제 가능.)
▶  만일 SQL문이 인덱스 access path를 사용할 수 있는 형태로 주어진 경우에는 다음 방법중의 하나로 Optimizer가 강제로 full table scan하도록 유도할 수 있다.

· SQL문의 의미를 바꾸지 않고 SQL문을 수정
· FULL hint를 사용하여 Optimizer가 인덱스 scan 대신에 full table scan을 선택하게 함.
· INDEX나 AND_EQUAL hint를 사용하여 Optimizer가 다른 인덱스 대신에 하나의 인덱스나 인덱스의 집합을 사용하게 함

■ 1. SELECT * FROM tab1 WHERE col1=’A’
■ 2. SELECT * FROM tab1 WHERE col1=’B’;

·  tab1은 1000개의 row를 가지고 col1의 값은 A~Z까지의 문자.
·  col1의 75%는 ‘A’값을 가지고, 다른 문자들은 전체 row의 1%만큼씩 나타난다라고 가정
·  1번 질의에서는 ‘A’ 값이 전체 row의 75%를 차지하므로 col1에 대한 인덱스를 사용하는 인텍스 scan보다 full table scan을 사용하는 것이 더 빠르다.
·  2번 질의에서는 전체 row에서 ‘B’의 값은 1%이므로 full table scan보다 index scan이 더 빠르다.
·  2번 질의를 위해서는 index의 생성이 필요하지만 1번 질의에 대해서는 인덱스의 사용이 바람직하지않다.
·  질의 결과의 퍼센트가 다름에도 불구하고 Optimzer는 각 칼럼 값의 발생빈도를 알 수 없기 때문에 이들 두 질의들에 대해 동일한 access path를 선택할 것이다.

■해결방법
· CREATE INDEX col1_ind ON tab1(col1);
· 1번 질의에서는 COL1_IND 인덱스를 사용하는 access path를 유용하지 않게 만들기 위해 WHERE절을 다음과 같이 수정
·(SELECT * FROM tab1 WHERE col1 || ”=’A’; )
· 이것은 이 질의가 COL1_IND에 의해 제공되는 access path의 사용을 억제함.
· WHERE절에서 인덱스된 칼럼에 대하여 function이나 operation을 적용하면 인덱스 access path는 사용할 수 없게 된다. 따라서 Optimizer는 이 질의에 대해 full table scan을 최적의 access path로 선택한다.

      ■NOTE :
           ·  WHERE절에서의 이러한 변화는 조건에 의한 결과를 변하게 하지는 않으므로 질의 결과 역시 동일하다.
           ·  그리고 Number나 Date type의 칼럼에 대해서는 해당 칼럼 값에 0을 더하여 동일한 목표를 달성할 수 있다.

 

☞How to Use Clusters

 

♣ 테이블들을 클러스터링하기 위한 지침

■ 클러스터링이 유용한 경우
·  Join문에서 자주 access되는 테이블들을 클러스터링
·  master 레코드와 대응하는 detail 레코드를 자주 select하는 경우에는 master 테이블과 detail 테이블을 함께 클러스터링
·  동일한 master에 대한 대량의 detail 레코드를 자주 선택하는 경우에는 클러스터내에서 detail 테이블을 별도로 저장

■클러스터링이 유용하지 않은 경우
·  응용에서 매우 드물게 Join되거나 Join에 참여하는 공통 column값들이 빈번하게 수정될 경우
·  응용에서 클러스터된 테이블들중에서 한 테이블에 대해서 자주 full table scan을 하는 경우
·  동일한 클러스터 key값을 가진 모든 테이블의 데이타가 하나이상의 Oracle block보다 더 클 경우에는 클러스터링 하지않는 것이 좋다.

▶ 응용의 필요에 따라 요구되는 동일한 클러스터링의 장단정을 잘 고려해야 한다.
▶  Join 문에 의해 얻어지는 성능 이득이 cluster key값의 수정에 따른 성능 손실보다 큰지를 결정해야할 것이다.
▶  이를 위해서 클러스터된 테이블과 별도로 저장된 테이블의 처리시간을 비교하기 위한 실험이 필요하다.
▶  클러스터를 생성하려면 CREATE CLUSTER명령을 사용하면 된다.

 

☞How to Use Hashing

 

♣ Hash cluster를 사용하기 위한 지침

■  Hash cluster사용이 유용한 경우
·  WHERE절에서 동일한 column이나 column의 조합에 대한 동치 비교를 포함하는 질의가 빈번할 경우
·  주어진 클러스트 키 값을 가진 모든 row를 삽입하기 위해 필요한 저장 공간을 결정할 수 있으면 하나의 Hash cluster에 하나의 table에 저장
·  임의의 테이블이 다른 테이블들과 자주 조인되는 것과 관계없이 Hashing 조건에 적절하면, 그 테이블을 하나의 Hash cluster에 저장하는 것이 유리함.

■  Hash cluster사용이 유용하지 않은 경우
·  데이타베이스에 있는 공간이 부족하여 추가로 삽입할 row를 위해서 필요한 여유 공간이 없을 경우
·  항상 증가되는 테이블일 경우
·  응용에서 테이블의 full scan을 자주 실행하고 추후 테이블이 크게 증가될 것을 예상하여 해쉬 클러스터에 큰 여유 공간을 할당해야만할 경우
·  응용에서 클러스터 키 값을 자주 수정하는 경우

▶  응용의 필요에 따라 Hash cluster의 장단정을 잘 고려해야 함.
▶  Hash cluster에 저장된 테이블과 인덱스에 의해 별도로 저장된 테이블의 처리시간을 비교하기 위한 실험이 필요
▶  Hash cluster를 생성하려면 CREATE CLUSTER명령에서 HASH와 HASHKEYS 파라메터를 사용

 

♣ How to Determine How Many Hash Value to Use

▶  Hash cluster를 생성할 때 해쉬 값의 수를 정하기 위하여 CREATE CLUSTER문의 HASHKEYS 파라메타를 사용
▶  해쉬 scan의 최대 성능을 위해 최소한 cluster key값 수만큼 큰 hashkey값을 선택해야 함.
▶  Collision은 hash scan 수행후에 각 블럭에 있는 row에 대하여 cluster key 값이 올바른지 여부를 검사하게 되어 hash scans의 성능을 감소시킴
▶  Oracle에서는 실제 Hash 값의 수를 구하기 위하여 사용자가 지정한 HASHKEYS 값과 가장 근접한 prime number로 처리하여 Collisions을 감소시킴

  

☞How to Use Hints

●  응용설계자는 특정 데이터에 대하여 optimizer가 알 수 없는 정보를 가질 수 있음.
●  응용설계자는 optimizer보다 더 좋은 execution plan을 선택할 수 있음.
●  응용설계자는 hint를 사용하여 강제적으로 사용자가 선택한 execution plan을 생성하도록 optimizer에게 지시 가능.

 

♣ Hints의 사용

●  Hints의 사용범위

▶  SQL문을 위한 Optimization approach
▶  SQL문에 대한 Cost-based approach의 goal(best throughput, best response time)
▶  SQL문에 의해 접근되는 테이블에 대한 access path
▶  join문에 대한 join순서
▶  join문을 처리하기 위한 join operation


●  Statement Block

▶  간단한 SELECT, UPDATE, DELETE SQL문
▶  한 parent SQL문나 complex SQL문 내의 subquery
▶  한 compound query중의 한 부분
· UNION operator에 의해 조합된 두 개의 component query로 구성한 한 compound query는 두 개의 SQL문 block을 가짐. (각 component query를 위해 하나의 SQL문 block이 생김.)
· 첫 번째 component query에 있는 hint는 첫 번째 component의 optimizer에만 적용
· 두 번째 component query에 대한 optimizer에는 적용되지 않는다.


●  hint를 포함하는 Syntax


·DELETE, SELECT, UPDATE : SQL문 block을 시작하는 DELETE, SELECT, UPDATE 키워드이다. Hint를 포함하는 comments는 이 키워드들이 나타난 후에 써야한다.
·+ : 오라클이 그 comment를 해석할 수 있게 해주는 기능을 한다. 이 ‘ + ‘는 comment 표시 후에 빈칸(blank)이 없이 즉시 따라와야만 한다.
·hint : 이절에서 언급되는 hint중의 하나이다. 만약 comment가 여러 hint를 가진다면 그 hint들은 최소한 하나의 공간으로서 분리되어 있어야만 한다.
·text : hint에 대한 설명


●  hint를 무시하는 경우

▶  hint가 쓰인 comment가 DELETE, SELECT, UPDATE 키워드 뒤에 오지 않고, 다른 곳에 쓰여 있는 경우
▶  hint가 Syntax error를 가진 경우
     (동일한 comment내에 error를 가지지 않고 올바로 표현된 다른 hint들은 인정)
▶  충돌한 hint의 조합들은 무시(한 comment내에 두 개 이상의 hint가 있을 경우, Optimizer가 어떤 hint를 먼저 써야하는지 모르는 경우)
      (동일한 comment내에 충돌한 hint외에 다른 hint는 인정.)

· 오라클은 SQL*Forms Version 3 Trigger와 같은 PL/SQL Version1을 사용하는 환경에서는 모든 SQL 문에 있는 hint들을 무시.
· Optimizer는 cost-based approach를 사용하는 경우에 hint를 인식.
· 한 SQL문 block에 RULE hint를 제외한 어떤 hint들이 포함되어 있으면, optimizer 는 자동적으로 cost-based approach를 사용.

 

☞
Hint for Optimization Approaches and Goals

▶  hint는 cost-based approach와 rule-based optimization approach 중에 하나를 선택
▶  cost-based approach를 선택한 경우에는 best throughput와 best reponse time사이에 하나를 선택
▶  SQL문이 optimization approach 와 goal을 기술한 한개의 hint를 가진다면, optimizer는 statistics의 존재여부와 ALTER SESSSION명령에 있는 OPTIMIZER_GOAL와 OPIMIZER_MODE 초기 파라메타의 값과는 상관없이 기술된 approach를 사용.

 

♣ ALL_ROWS

▶  ALL_ROWS hint는 best throughput(minimum total resource consumption)을 목적으로 SQL문 block을 최적화하기 위해 cost-based approach를 선택
▶  optimizer는 best throughput을 목적으로 SQL문를 최적화    하려면
·SELECT /*+ ALL_ROWS */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

 

♣ FIRST_ROWS

▶  FIRST_ROWS hint는 best response time을 목적으로 SQL문 block을 최적화하기 위해 cost-based approach를 선택.(minimum resource usage to return first row)
▶  이 hint는 아래 내용을 이행할 수 있는 optimizer를 생성
· Index scan을 쓸수 있다면, optimizer는 full table scan보다는 Index scan을 사용.
· Index scan을 쓸수 있다면, optimizer는 연관된 table이 nested loop의 inner table일 때마다, sort-merge join보다는 nested loops join을 선택.
· Index scan이 ORDER BY절에 의해 쓰여지면, optimizer는 sort operation을 피하기 위해 index scan을 선택.

▶  optimizer는 best response time을 목적으로 아래 SQL문를 최적화하려면.
·SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

▶  Optimizer는 아래 Syntax를 가지는 DELETE와 UPDATE SQL문 blocks과 SELECT SQL문 blocls에 있는 hint는 무시.
· set operators(UNION, INTERSECT, MINUS, UNION ALL)
· GROUP BY절
·FOR UPDATE 절
·group functions
·DISTINCT operator
▶  이들 SQL문는 best response를 목적으로 최적화될 수 없다.
▶  위의 경우 첫 번째 row를 반환하기 전에 SQL문에 접근되는 모든 행들을 retrieve해야만 함.
▶  이런 SQL문에 대해 hint를 쓰면 optimizer는 cost-based approach를 사용하고, best throughput을 목적으로 최적화.

▶  SQL문에 ALL_ROWS나 FIRST_ROWS hint를 기술하고, data dictionary가 그 SQL문에 의해 접근되는 table에 대해 어떤 statistics도 포함하지 않는다면, optimizer는 내부적으로 execution plan을 선택하고, missing statistics를 추정할 default statistics 값을 사용.
▶  이 추정치(estimates)는 ANALYZE명령에 의해 생성되어진 것만큼 정확하지는 않음.
▶  ANALYZE명령을 사용하여 Cost-based optimization을 사용하는 SQL문에 의해 접근되는 모든 table에 대한 statistics를 생성해야 함.
▶  Access path를 위한 hint 또는 ALL_ROWS나 FIRST_ROWS hint를 가지는 join operation을 기술한다면, optimizer는 hint에 의해 기술된 join operation들과 access paths를 우선적으로 취함.

 

♣ CHOOSE

▶  CHOOSE hint는 statistics가 존재하고, SQL문에 의해 접근된 테이블에 대해 rule-based approach나 cost-based approach중 어떤 것을 쓸 것인지 optimizer가 선택.
▶  data dictionary가 이 table들 중 최소한 하나에 대한 statistics를 가진다면 optimizer는 cost-based approach를 사용.
▶  data dictionary가 이 table들 중 어떤 것에 대해서도 statistics를 가지지 않는다면 optimizer는 rule-based approach를 사용.
▶  아래 SQL문에서 만약 EMP 테이블에 대해 statistics가 있다면 optimizer는 cost-based approach를 사용.
▶  data dictionary에 EMP 테이블에 대해 어떤 statistics도 존재하지 않는다면 optimizer는 rule-based approach를 사용.
·SELECT /*+ CHOOSE */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

 

♣ RULE

▶  RULE hint는 한 SQL문 block에 대해 rule-based optimization을 선택.
▶  이 hint는 optimizer가 SQL문 block에 대해 기술한 다른 hint들을 무시.
▶  Optimizer는 아래 SQL문를 위해서 rule-based approach를 사용.
·SELECT –+ RULE

               empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;
▶  rule-based approach를 쓰는 RULE hint는 오라클의 다음 버전에서는 사용하지 않는다.

 

☞
Hint for Access Methods

▶  각 hint들은 table에 대한 access method를 제안
▶  hint중의 하나를 기술하는 것은 access path가 인덱스나 클러스터와 SQL문의 의미구조의 존재를 기본적으로 이용할수 있다면 기술된 access path를 선택
▶  힌트가 access path를 이용할수 없다면 optimizer는 그것을 무시.
▶  SQL문에서 정확하게 access되는 테이블을 기술
▶  SQL문이 table에 대한 alias를 사용하면, hint에서 table의 이름보다는 alias를 사용.
▶  테이블의 이름이나 alias 는 local database에 있는 한 테이블에 대한 하나의 synonym이나 하나의 table을 의미함.

 

♣ FULL

▶  FULL hint는 테이블에 대해 full table scan을 선택
▶  FULL hint의 문법은 FULL(table)
▶  (table)에는 full table scan을 수행하는 table의 alias나 name을 기술한다.
▶  예 : ACCOUNT 테이블에 WHERE절의 조건에 의해 사용가능한 ACCNO 칼럼에 대한 인덱스가 있음에도 불구하고, 오라클은 이 SQL 문을 실행할 ACCOUNTS 테이블에 full table scan을 수행.
·SELECT /*+ FULL(a) Don’t use the index on ACCNO */ accno, bal
              FROM accounts a
              WHERE accno = 7086854;

■ NOTE
      ·  ACCONTS테이블이 alias A를 가지기 때문에 hint는 테이블의 이름이 아닌 alias로 테이블을 표현.
      ·  FROM 절에 테이블의 이름이 기술되었음에도 불구하고, hint에서 는 schema names을 기술하지 않는다.

 

♣ ROWID

▶  ROWID hint는 테이블에 대해 ROWID에 의한 table scan을 선택
▶  ROWID hint의 문법은 ROWID(table)
▶  (table)에는 ROWID에 의한 table scan이 이행되어지는 table의 alias나 이름을 기술.

 

♣ CLUSTER

▶  CLUSTER hint는 테이블에 대해 cluster scan을 선택
▶  CLUSTER hint의 문법은 CLUSTER(table)
▶  (table)에는 cluster scan에 의해 접근되는 테이블의 이름이나 alias를 기술.

·SELECT –+ CLUSTER emp, ename, deptno
              FROM emp, dept
              WHERE deptno = 10 AND emp.deptno = dept.deptno;

 

♣ HASH

▶  HASH hint는 테이블에 대해 HASH scan을 선택
▶  HASH hint의 문법은 HASH(table)
▶  (table)에는 hash scan에 의해 접근되는 테이블의 이름이나 alias를 기술

 

♣ INDEX

▶  INDEX hint는 테이블에 대해 index scan을 선택
▶  INDEX hint의 문법은


table : scan될 index와 관련있는 테이블의 이름이나 alias를 기술
index : index scan이 수행될 index를 기술


▶  hint는 하나이상의 indexes들을 기술
· hint가 하나의 사용가능한 index를 기술한다면, optimizer는 index에서 한개의 scan을 수행.
· optimizer는 full table scan이나 테이블에 있는 다른 index에 대한 scan은 수행하지 않음.
· hint가 사용가능한 index의 리스트를 기술한다면, optimizer는 리스트에 있는 각각의 인덱스에 대한 scan을 하는데 드는 비용을 고려한 후에 가장 적은 비용이 드는 index scan을 이행
· 이 access path가 최저의 비용을 가진다면 optimizer는 이 리스트로부터 여러 인덱스를 scan하고 그 결과들을 merge.
· optimizer는 full table scan이나 hint에 있지 않은 index scan은 고려하지 않음.
· hint가 어떤 index도 기술하지 않았다면, optimizer는 테이블에 있는 사용가능한 index를 각각 scan한 비용을 고려한 후에 lowest cost를 가진 index scan을 수행.
· 이 access path가 최저비용을 가진다면 optimizer는 muliple index를 scan하고 그 결과값을 merge.
▶  SELECT name, height, weight
              FROM patients
              WHERE sex=’M’

·  sex의 열은 index되어 있고, 이 칼럼은 ‘M’과 ‘F’의 값을 가짐.
·  병원에 남자의 수와 여자의 수가 동일하다면, 이 질의는 연관된 테이블의 행의 최다 퍼센트를 반환하고, full table scan이 index scan보다는 더 빠르게 된다.
·  병원의 환자 중 남자의 비율이 매우 적다면, 질의는 관련된 테이블의 행에 대해 적은 비율을 반환하고, 이 경우에는 index scan이 full table scan보다 더 빠르다.
▶  각 disinct column value의 발생수는 optimizer에게 별로 유용하지 않다(도움이 되지 않는다.)
▶ cost-based approach는 각각의 값들이 각각의 행에서 나타나는 빈도수가 동일하다고 가정을 한다.
▶  한 칼럼이 단 2개의 다른 값들(distinct values)을 가진다면 optimizer는 그 두 값들이 각각 row의 50%로정도 나타난다고 가정한다 그래서 cost-based approcah는 index scan보다는 full table scan을 선택하곤 한다.
▶  WHERE절에 있는 값이 모든 row에 대해 매우 적은 퍼센트를 가진다면, hint에 index scan을 사용하여 optimizer가 강제로 index scan을 사용하게 할수 있다.
▶  아래 문장에서 INDEX hint는 SEX_INDEX에 대해 index scan을 선택한다.

· SELECT /*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few male patients */
              name, height, weight
              FROM patients
              WHERE sex = ‘M’;

 

♣ FULL hint와 INDEX hint의 비교 예제

▶  1. EMP테이블의 총건수는 10,000건, DEPT테이블의 총건수는 2400건.
▶  2. EMP테이블에서 empno, ename, sal을 select
▶  3. WHERE절의 조건은 JOB 필드의 ‘SALESMAN’값을 검사
▶  4. JOB_INDEX 존재.
▶  5. JOB필드에는 2개의 값이 존재(MANAGER-(9,751건/10,000건), SALESMAN-(249건/10,000건))
·  EMP, DEPT 테이블을 ANALYZE함.
·  sql_trace를 true로
·  OPTIMIZER_GOAL 은 ALL_ROWS로

▶  예제 1

analyze table emp estimate statistics;
analyze table dept estimate statistics;

alter session set sql_trace = true;
alter session set optimizer_goal = all_rows;

select empno, ename, sal
from emp
where job = ‘SALESMAN’;

select /*+ full(emp) */ empno, ename, sal
from emp
where job = ‘SALESMAN’;

select /*+ index(emp job_index) */ empno, ename, sal
from emp
where job = ‘SALESMAN’;
·  trace file 결과

·  OPTIMIZER_GOAL 은 FIRST_ROWS로

▶  예제 2

analyze table emp estimate statistics;
analyze table dept estimate statistics;

alter session set sql_trace = true;
alter session set optimizer_goal = first_rows;

select empno, ename, sal
from emp
where job = ‘SALESMAN’;

select /*+ full(emp) */ empno, ename, sal
from emp
where job = ‘SALESMAN’;

select /*+ index(emp job_index) */ empno, ename, sal
from emp
where job = ‘SALESMAN’;
·  trace file 결과

 

♣ INDEX_ASC

▶  INDEX_ASC hint는 테이블에 대한 index scan을 선택
▶  이 SQL문이 index range scan을 사용한다면, Oracle은 index된 값들을 오름차순으로 정렬한 index entry들을 scan.
▶  INDEX_ASC hint의 문법은



▶  각 파라메타는 INDEX hint에서와 같은 목적을 의미.
▶  range scan에 대해서 오라클의 default behavior는 index된 값에 대해 오름차순으로 정렬하고 그 index entry들을 scan하는 것이므로 이 hint는 일반적으로 index hint보다 더 나은 점은 없다.

 

♣ INDEX_DESC

▶  INDEX_DESC hint는 테이블에 대해 index scan을 선택
▶  만약 SQL 문이 index range scan을 사용한다면, 오라클은 index된 값들을 내림차순으로 정렬한 index entry들을 scan.
▶  INDEX_desc hint의 문법은



▶  각 파라메타는 INDEX hint에서와 같은 목적을 의미.
▶  이 hint는 테이블보다 INDEX를 더 많이 접근하므로 SQL문에 영향을 주지 않음.
▶  아래의 SQL문은 index된 값에 대해서 항상 오름차순으로 정렬된 index range scan을 수행
·CREATE TABLE tank_readings (
              time DATE CONSTAINT un_time UNIQUE,
               temperature NUMBER);

▶  테이블의 각 행들은 한 시점에서 시간과 온도를 저장.
▶  TIME칼럼에 대해 UNIQUE 제약을 주면 테이블이 동일한 시점에서 한번만 내용을 읽도록 한다.
▶  오라클은 TIME 칼럼에 강제로 인덱스를 수행.
▶  특별한 T시간에 대해서 읽은 가장 최근의 온도를 SELECT하는 complex query를 생각해보자.
▶  Subquery는 온도를 읽는 시점 T나 T이전의 가장 최근시간의 값을 반환.
▶  Parent query는 그 시간에 대한 온도를 찾는다.
·SELECT temperature
              FROM tank_readings
              WHERE time = (SELECT MAX(time)

              FROM tank_readings
              WEHRE time <= TO_DATE(:t) );


▶  위 SQL문에 대한 execution plan은 아래그림과 같다.



▶  위 SQL문장을 실행시키면, 오라클은 아래와 같은 operations를 실행.
· step 4와 3은 subquery를 실행.

– step 4는 시간 T와 같거나 더 적은 모든 TIME 값들을 반환할 UN_TIME 인덱스의 range scan을 수행.
– step 3는 step 4로부터 최대 TIME값을 선택하고 그 값을 반환.

·step 2 와 1은 parent query를 실행.

– step 2는 step 3에 의해 반환된 TIME 값에 맞는 UN_TIME index의 unique scan을 수행하고 관련된 ROWID를 반환.
– step 1은 step 2에 의해 반환된 ROWID를 사용하여 TANK_READING 테이블을 접근하고 TEMPERATURE 값을 반환.


▶ Step 4에서 오라클은 오름차순으로 정렬된 인덱스에 있는 TIME 값을 scan.
▶  오라클은 첫 번째 TIME값이 T보다 더 큰 경우에 scaning을 중지하고 그후에 step 3에서 T값과 같거나 더 적은 모든 값을 반환.
▶  INDEX_DESC hint를 사용하면 index로부터 단 하나의 TIME값을 읽어오는 질의 사용 가능.
·SELECT /*+ INDEX_DESC(tank_readings un_time) */ temperature
              FROM tank_readings
              WHERE time <= TO_DATE(:t)
              AND ROWNUM = 1;
              ORDER BY time DESC;

▶  이것의 execution plan을 아래 그림과 같다.


· step3 은 T와 동일하거나 더 적은 TIME값을 찾기위해 UN_TIME 인덱스를 range scan하고 그와 관련된 ROWID를 반환.
·step 2는 step 3에 의해 반환된 ROWID값들로서 TANK_READING 테이블에 접근.
· step 1은 step 2로부터 단 하나의 행을 요구함으로 ROWNUM=1이라는 조건을 수행.
▶  INDEX_DESC hint 때문에 step 3은 T값부터 시작하는 내림차순으로 정렬된 index에서 TIME값을 scan.
▶  scan된 첫 번째 TIME값은 T이거나 T값보다는 적은 최대 TIME값이다.
▶  step 1은 단 하나의 행을 요구한 후부터는 , step 3은 첫 번째 TIME값 이후의 index entry에 대해 더 이상 scan하지 않는다.
▶  default 행동이 오름차순 index scan이므로 INDEX_DESC hint없이 이 질의를 수행하면 오라클은 테이블에서 T와 같거나 그보자 적은 최대 시간을 처음 scaning하는 것보다 최초의 시간을 scaning함으로서 시작하게 됨. step1은 최초의 시간에대한 온도를 반환하게 된다.
▶  위 질의에서 좀 더 빨리 이 복합 질의에서 요구하는 온도를 반환하려면 INDEX_DESC hint를 사용해야만 한다.

 

♣ AND_EQUAL

▶  AND_EQUAL hint는 몇몇의 single-column index에대한 scan을 merge하는 access path를 사용하는 execution plan을 선택
▶  이 AND_EQUAL hint의 문법은


table : merge할 인덱스와 연관된 테이블의 이름이나 alias를 기술.
index : index scan을 수행하는 index를 기술

▶ 최소 2개 이상 최대 5개 이하의 index를 기술해야 한다.

 

♣ USE_CONCAT

▶  USE_CONCAT hint는 OR 조건을 UNION ALL set operator를 사용하는 compound query로 변환
▶  이 변환은 UNION ALL set operations을 사용하는 질의가 이를 사용하지 않을 때보다 비용이 더 적을 경우에만 발생

 

☞Hint for join Orders

ORDERED hint는 join order를 제안

 

♣ ORDERED

▶  ORDERED hint는 FROM절에 table이 나타나는 순서대로 테이블을 join시킨다.
▶  예를들어, 아래 SQL문은 테이블 TAB1과 테이블 TAB2를 조인한 후에 그 결과와 테이블 TAB3을 조인한다.

·SELECT /*+ ORDERED */ tab1.col1, tab2.col2, tab3.col3
              FROM tab1, tab2, tab3
              WHERE tab1.col1 = tab2.col1 AND tab2.col1 = tab3.col1;

▶  SQL문에서 ORDERED hint를 생략하고 join를 수행하면, optimizer가 table을 join할 순서를 선택
▶  각 테이블에서 select해 오는 행의 수에대해 알고 있다면 join 순서를 기술하는 ORDERED hint를 사용하는 것이 좋다.
▶  사용자가 inner 와 outer table을 선택하는 것이 optimizer가 할수 있는 것보다 나을 수도 있다.

 

☞
Hint for Join Operations

● 이절에서의 hint는 테이블을 위한 join operation을 언급
● SQL문에서 나타나는 조인된 table을 정확하게 기술해야만 한다.
● SQL문이 테이블의 alias를 사용한다면 hint에서도 테이블의 이름보다는 alias를 사용해야만 한다.
● 테이블의 이름이나 alias는 local database에 테이블의 synonym이나 테이블을 가지고 있어야만 한다.
● USE_NL과 USE_MERGE hint는 ORDERED hint를 사용해야만 한다.
● 오라클은 참조된 테이블이 조인에서 강제로 inner테이블이 될 때 이 hint를 사용해야만 한다.
● 참조된 테이블이 outer table이라면 이 hint들을 무시한다.

 

♣ USE_NL

▶  USE_NL hint는 테이블을 적는 부분에 테이블 기술된 table은 inner table로서 사용하여 nested loops로서 다른 테이블의 row source와 기술된 table을 join하게 한다.
▶  USE_NL hint의 문법은


table : nested loops join의 inner table로서 사용될 테이블의 이름과 alias이다.

▶  예를 들어서, ACCOUNTS와 CUSTOMERS 테이블을 조인하는 SQL문이 있다고 생각해보자. 이들 테이블들은 cluster에 함께 저장되지 않았다고 가정한다.

·SELECT accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custo;
▶  cost-based approach의 default 목적은 best throughput이므로 이 optimizer는 좀 더 빨리 질의에 의해 select된 모든 행들을 반환하기위해 nested loops operation이나 sort-merge operation중 하나를 선택한다.
▶  그러나 질의에 의해 선택된 첫 번째 행만 반환할 때 필요시간이 매우 적어야 할 경우에는 best throughput보다 best response time으로 SQL문을 최적화하는 것이 더 낫다.
▶  그렇게 하려면 USE_NL hint를 사용함으로서 optimizer가 nested loops join을 강제로 선택하게 할 수 있다.
▶  SQL문에서 USE_NL hint는 CUSTOMERS테이블을 inner table로 가지는 nested loop를 선택
·SELECT /*+ ORDERED USE_NL(customers) Use N-L to get first row faster */
              accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custno;

▶  많은 경우에 nested loops join은 sort-merge join보다는 더빨리 첫 번째 행을 반환한다.
▶  Nested loop join은 한 테이블로부터 첫 번빼 select한 행을 읽은 후에 첫 번째 행을 반환할 수 있고, 다른 테이블에서 첫 번째로 일치하는 행을 찾고 그것들을 결합한다.
▶  반면에 sort-merge join은 양 테이블에서 select한 모든 row들을 읽고 정렬한 후 각각의 저장된 row source의 첫 번째 행들을 결합할 때까지 첫 번째 행을 반환할 수 없다.

 

♣ USE_MERGE

▶  USE_MERGE hint는 오라클이 sort-merge join으로 각 테이블을 조인하게끔 하는 방법이다.
▶  USE_MERGE hint의 문법은

 

☞
Hints for Parallel Query Execution

 

♣ CACHE

▶  CACHE hint는 full table scan이 수행됐을 때 hint에 있는 테이블에 대해 retrieve된 블록들은 버퍼 캐시에 있는 LRU list의 가장최근(most recently)에 사용되어진 것의 끝에 위치
▶  이 option은 small lookup table에 유용하다. CACHE hint는 테이블의 default caching specification을 무시
·SELECT/*+ FULL (scoot_emp) CACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

 

♣ NOCACHE

▶  NOCACHE hint는 full table scan이 수행됐을 때 hint에 있는 테이블에 대해 retrieve된 블록들은 버퍼 캐시에 있는 LRU list의 가장 오래전(least recently)에 사용되어진 것의 끝에 위치
▶  버퍼캐수에 blocks의 일반적인 행동
·SELECT/*+ FULL (scoot_emp) NOCACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

 

☞
Considering Alternative Syntax

●  SQL이 융통성이 있는 언어이기 때문에 하나 이상의 SQL문이 Application을 필요로 할 것이다.
●  2개의 SQL문이 동일한 결과를 산출함에도 불구하고, 오라클은 2중의 하나가 더 처리속도가 빠르다.
●  execution plans을 비교하기 위해 EXPLAIN PLAN SQL문의 결과와 두 SQL문의 비용들을 사용할 수 있다.

 

♣ 두 SQL문에 대한 execution plan 비교

●  첫 번째 SQL문과 그것에 대한 execution plan
·SELECT dname, deptno
              FROM dept
              WHERE deptno NOT IN
              (SELECT deptno FROM emp);
▶ Execution Plan with Two Full Table Scans


▶ step3을 통해 오라클은 DEPTNO 칼럼에 인덱스가 있음에도 불구하고 EMP테이블을 full table scan함으로서 위 SQL문을 실행한다는 것을 알수 있다.
▶ full table scan은 time-consuming operation을 할 수 있다.
▶ EMP테이블을 찾는 subquery에 index사용이 가능한 WHERE절이 없기 때문에 오라클은 index를 사용할 수 없다.

▶  그러나, 아래의 SQL문은 인덱스에 접근함으로서 동일한 행을 SELECT한다.
·SELECT dname, deptno
              FROM dept
              WHERE NOT EXISTS
              (SELECT deptno
              FROM emp
              WHERE dept.deptno = emp.deptno);
▶ Execution Plan with a Full Table Scan and an Index Scan


▶ subquery의 WHERE절이 EMP테이블의 DEPTNO칼럼을 사용하므로 DEPTNO_INDEX를 사용할 수 있다.
▶ 인덱스의 사용은 execution plan의 step3에서 하게된다
▶  DEPTNO_INDEX의 index range scan은 첫 번째 문장에서 EMP테이블의 full scan하는 것보다 시간이 더 적게 걸린다.
▶  첫 번째 query는 DEPT 테이블에서 모든 DEPTNO를 가져오기 위해 EMP테이블을 한번 full scan한다.
▶ 이런 이유로 두 번째 SQL 문은 첫 번째보다는 더 빠르다.
▶ Application에 NOT IN operator를 사용하는 SQL문를 가진다면 NOT EXISTS operator를 사용해서 그것들을 다시 써라. 이것은 인덱스가 있다면 그 인덱스를 사용할 수 있게 해줄 것이다.

   

☞How to Tune Existing SQL Statements

●  존재하는 application에서 SQL 문을 튜닝하는 것은 새로운 문장을 기록하는 것과는 다른 작업(task)이다.
●  비록 요구된 지식이 같을지라도 프로세스는 다르다.
●  튜닝하기 위한 application에서 특별한 문장을 다음과 같이함으로써 분리해야 한다.
· application에 익숙하라.
· SQL trace를 이용하여 특별한 문제 문장을 분리하라.
● 이전 절에서 논의했던 새로운 SQL 문장을 기록하고, 인덱스, 클러스터, 해싱, 힌트를 하기 위한 지침을 이용하여 문장을 튜닝할수 있다.

 

♣ Know the Application

▶  application과 SQL 문장과 data에 익숙해져야 한다.
▶  만약 당신이 application을 설계하고 개발하지 않았다면 그것을 한 사람에게 의견을 물어보라.
▶  application이 하는 것을 기록하라.
· application을 이용하는 SQL 문은 무엇인가?
· application이 처리하는 데이타는 무엇인가?
· 데이타의 특징과 distribution은 무엇인가?
· applicatin의 무슨 operation이 data를 실행하는가?
▶  application 사용자들과 성능에 대해 이야기 해보라.
▶  application의 어떤 부분을 식별하기 위 해서 그들이 성능향상의 필요를 느끼는지를 물어보라.
▶  가능하면 개인적인 SQL 문장을 위해서 이 부분들이 적어질 때까지 좁혀라.

 

♣ Use the SQl Trace Facility

▶  오라클은 성능을 측정하기 위하여 진단에 도움이 되는 tool을 몇가지 제공한다
▶  appllicatin을 튜닝하는데 특별히 도움이 되는 tool중에 하나는 SQL trace 이다.
▶  SQL trace facililty는 오라클에 의해 처리된 각 SQL 문장을 위한 통계를 생성한다.
▶  통계는 다음을 나타낸다.
· SQL 문장이 파싱, 실행, 페치된 횟수
· 각 SQL 문장을 처리하는데 필요한 시간
· 각 SQL 문장과 관련된 메모리와 디스크 access
· 각 SQL 문장을 처리하는 row의 수
▶  SQL trace facility는 또한 EXPLAIN PLAN 명령을 이용하여 execution plan을 생성할수 있다.

 

♣ Tuning Individual SQL statements

▶  사용중인 사용자 응용을 수정하지 않고 SQL문의 변경된 문법을 탐색할 수 있음을 명심하라.
▶  사용자가 존재하는 SQL문의 비용과 실행계획을 비교하고 고려하는 문장을 변경하는 것은 단순히 EXPLIAN PLAN을 이용한다.
▶  만약 사용자가 SQL문의 tune을 위해 새로운 index를 생성하면 사용자는 optimizer가 응용이 실행될 때마다 index를 사용할 것인지를 결정하기 위해 EXPLAIN PLAN 명령을 사용할 수 있다.
▶  만약 사용자가 현재 parse된 문장을 tune하기 위해 새로운 index를 생성하면 Oracle은 SQL 문에서 이 index를 사용하지 않는다.
▶  SQL 문장이 다시 실행되면 optimizer는 자동적으로 새로 생성된 index를 사용하기 위해 새로운 실행계획을 선택한다.
▶  만약 사용자가 분산 SQL 문을 tune 하기 위해 원격 데이타베이스의 index를 생성하면 optimizer는 SQL문이 다시 실행될 때 이 index를 고려한다.
▶  또한 사용자는 SQL 문장에 대한 실행계획이 optimizer 보다 더 효과적으로 사용할 수 있도록 tune한다는 것을 명심하라.
▶  예를 들어 사용자가 하나의 SQL문을 사용하기 위해 index를 생성하면 optimizer는 사용자의 응용에서 다른 문장을 실행하기 위해 그 index를 선택한다.

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

Related Post

DB Tunning(TABLE,SQL,...)

DB LINK를 사용한 분산 쿼리의 튜닝

8월 1, 2018 haisins
DB Tunning(TABLE,SQL,...)

Oracle Advisor 관련 오라클 메모리 조회 SQL

4월 10, 2018 haisins
DB Tunning(TABLE,SQL,...)

로그 버퍼(Log Buffer) 영역의 튜닝

4월 10, 2018 haisins

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다

You missed

Oracle News

[NEWS] 오라클, ‘오라클 데이터베이스 23c 개발자용 무료 버전’ 공개

5월 8, 2023
Oracle DB Admin

Oracle Block change tracking 기능

5월 8, 2023
Oracle Up,Mig

DB Migration Using TTS

4월 20, 2023
DB 설치 & 구축

Windows 용 Oracle Database 19c 설치 필요 조건

4월 19, 2023