SQL 튜닝의 기본 지식

☞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를 선택한다.