SQL 힌트의 사용 방법

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


 

힌트의 사용

 

  힌트의 사용범위

―  SQL
문을 위한 Optimization approach
―  SQL
문에 대한 Cost-based approach goal(best throughput, best response time)
―  SQL
문에 의해 접근되는 table 대한 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 있는 힌트는 번째 component optimizer에만 적용
·
번째 component query 대한 optimizer에는 적용되지 않는다.


● 
힌트를 포함하는 Syntax

 

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


 
힌트 무시하는 경우

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

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

 

옵티마이저를 위한 힌트와 사용 목표

―  힌트는 cost-based approach rule-based optimization approach 중에 하나를 선택
―  cost-based approach
선택한 경우에는 best throughput best reponse time사이에 하나를 선택
―  SQL
문이 optimization approach goal 기술한 한개의 힌트를 가진다면, optimizer statistics 존재여부와 ALTER SESSSION명령에 있는

OPTIMIZER_GOAL OPIMIZER_MODE 초기 파라메타의 값과는 상관없이 기술된 approach 사용.

 

 

ALL_ROWS

―  ALL_ROWS 힌트는 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 힌트는 best response time 목적으로 SQL block 최적화하기 위해 cost-based approach 선택.(minimum resource usage to return first row)
― 
힌트는 아래 내용을 이행할 있는 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 block 있는 힌트는 무시.
· set operators(UNION, INTERSECT, MINUS, UNION ALL)
· GROUP BY

·FOR UPDATE

·group functions
·DISTINCT operator
― 
이들 SQL문는 best response 목적으로 최적화될 없습니다.
― 
위의 경우 번째 row 반환하기 전에 SQL문에 접근되는 모든 행들을 retrieve해야만 .
― 
이런 SQL문에 대해 힌트를 쓰면 optimizer cost-based approach 사용하고, best throughput 목적으로 최적화.

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

 

CHOOSE

―  CHOOSE 힌트는 statistics 존재하고, SQL문에 의해 접근된 table 대해 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 table 대해 statistics 있다면 optimizer cost-based approach 사용.
―  data dictionary
EMP table 대해 어떤 statistics 존재하지 않는다면 optimizer rule-based approach 사용.


SELECT /*+ CHOOSE */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

 

RULE

―  RULE 힌트는 SQL block 대해 rule-based optimization 선택.
― 
힌트는 optimizer SQL block 대해 기술한 다른 힌트들을 무시.
―  Optimizer
아래 SQL문를 위해서 rule-based approach 사용.


SELECT --+ RULE
               empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;


―  rule-based approach
쓰는 RULE 힌트는 오라클의 다음 버전에서는 사용하지 않는다.

 

 

실행 순서 방법을 위한 SQL 힌트

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

 

FULL

―  FULL 힌트는 table 대해 full table scan 선택
―  FULL
힌트의 문법은 FULL(table)
―  (table)
에는 full table scan 수행하는 table alias name 기술합니다.
― 
: ACCOUNT table WHERE절의 조건에 의해 사용가능한 ACCNO 칼럼에 대한 index 있음에도 불구하고, 오라클은 SQL 문을 실행할 ACCOUNTS table full table scan 수행.


SELECT /*+ FULL(a) Don't use the index on ACCNO */ accno, bal
              FROM accounts a
              WHERE accno = 7086854;


NOTE
      ·  ACCONTStable
alias A 가지기 때문에 힌트는 table 이름이 아닌 alias table 표현.
      ·  FROM
절에 table 이름이 기술되었음에도 불구하고, 힌트에서 schema names 기술하지 않는다.

 

ROWID

―  ROWID 힌트는 table 대해 ROWID 의한 table scan 선택
―  ROWID
힌트의 문법은 ROWID(table)
―  (table)
에는 ROWID 의한 table scan 이행되어지는 table alias 이름을 기술.

 

CLUSTER

―  CLUSTER 힌트는 table 대해 cluster scan 선택
―  CLUSTER
힌트의 문법은 CLUSTER(table)
―  (table)
에는 cluster scan 의해 접근되는 table 이름이나 alias 기술.

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

 

HASH

―  HASH 힌트는 table 대해 HASH scan 선택
―  HASH
힌트의 문법은 HASH(table)
―  (table)
에는 hash scan 의해 접근되는 table 이름이나 alias 기술

 

INDEX

―  INDEX 힌트는 table 대해 index scan 선택
―  INDEX
힌트의 문법은

 


table : scan
index 관련있는 table 이름이나 alias 기술
index : index scan
수행될 index 기술


― 
힌트는 하나이상의 indexes들을 기술


·
힌트가 하나의 사용가능한 index 기술합니다면, optimizer index에서 한개의 scan 수행.
· optimizer
full table scan이나 table 있는 다른 index 대한 scan 수행하지 않음.
·
힌트가 사용가능한 index 리스트를 기술합니다면, optimizer 리스트에 있는 각각의 index 대한 scan 하는데 드는 비용을 고려한 후에 가장 적은 비용이 드는 index scan 이행
·
access path 최저의 비용을 가진다면 optimizer 리스트로부터 여러 index scan하고 결과들을 merge.
· optimizer
full table scan이나 힌트에 있지 않은 index scan 고려하지 않음.
·
힌트가 어떤 index 기술하지 않았다면, optimizer table 있는 사용가능한 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' 값을 가짐.
· 
병원에 남자의 수와 여자의 수가 동일하다면, 질의는 연관된 table 행의 최다 퍼센트를 반환하고, full table scan index scan보다는 빠르게 됩니다.
· 
병원의 환자 남자의 비율이 매우 적다면, 질의는 관련된 table 행에 대해 적은 비율을 반환하고, 경우에는 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 대해 매우 적은 퍼센트를 가진다면, 힌트에 index scan 사용하여 optimizer 강제로 index scan 사용하게 할수 있습니다.
― 
아래 문장에서 INDEX 힌트는 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 힌트와 INDEX 힌트의 비교 예제

―  1. EMPtable 총건수는 10,000, DEPTtable 총건수는 2400.
―  2. EMPtable
에서 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 table
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';

 

·  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';

 

INDEX_ASC

―  INDEX_ASC 힌트는 table 대한 index scan 선택
― 
SQL문이 index range scan 사용합니다면, Oracle index 값들을 오름차순으로 정렬한 index entry들을 scan.
―  INDEX_ASC
힌트의 문법은


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

 

INDEX_DESC

―  INDEX_DESC 힌트는 table 대해 index scan 선택
― 
만약 SQL 문이 index range scan 사용합니다면, 오라클은 index 값들을 내림차순으로 정렬한 index entry들을 scan.
―  INDEX_desc
힌트의 문법은


― 
파라메타는 INDEX 힌트에서와 같은 목적을 의미.
― 
힌트는 table보다 INDEX 많이 접근하므로 SQL문에 영향을 주지 않음.
― 
아래의 SQL문은 index 값에 대해서 항상 오름차순으로 정렬된 index range scan 수행


CREATE TABLE tank_readings (
              time DATE CONSTAINT un_time UNIQUE,
               temperature NUMBER);


―  table
행들은 시점에서 시간과 온도를 저장.
―  TIME
칼럼에 대해 UNIQUE 제약을 주면 table 동일한 시점에서 한번만 내용을 읽도록 합니다.
― 
오라클은 TIME 칼럼에 강제로 index 수행.
― 
특별한 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 index 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 table 접근하고 TEMPERATURE 값을 반환.


― Step 4
에서 오라클은 오름차순으로 정렬된 index 있는 TIME 값을 scan.
― 
오라클은 번째 TIME값이 T보다 경우에 scaning 중지하고 그후에 step 3에서 T값과 같거나 적은 모든 값을 반환.
―  INDEX_DESC
힌트를 사용하면 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 아래 그림과 같습니다.

 

 

· step 3 T 동일하거나 적은 TIME값을 찾기위해 UN_TIME index range scan하고 그와 관련된 ROWID 반환.
· step 2
step 3 의해 반환된 ROWID값들로서 TANK_READING table 접근.
· step 1
step 2로부터 하나의 행을 요구함으로 ROWNUM=1이라는 조건을 수행.


―  INDEX_DESC
힌트 때문에 step 3 T값부터 시작하는 내림차순으로 정렬된 index에서 TIME값을 scan.
―  scan
번째 TIME값은 T이거나 T값보다는 적은 최대 TIME 입니다.
―  step 1
하나의 행을 요구한 후부터는 , step 3 번째 TIME 이후의 index entry 대해 이상 scan하지 않는다.
―  default
행동이 오름차순 index scan이므로 INDEX_DESC 힌트없이 질의를 수행하면 오라클은 table에서 T 같거나 보다 적은 최대 시간을 처음 scaning하는 것보다

최초의 시간을 scaning함으로서 시작하게 . step1 최초의 시간에대한 온도를 반환하게 됩니다.
― 
질의에서 빨리 복합 질의에서 요구하는 온도를 반환하려면 INDEX_DESC 힌트를 사용해야만 합니다.

 

AND_EQUAL

―  AND_EQUAL 힌트는 몇몇의 single-column index에대한 scan merge하는 access path 사용하는 execution plan 선택
― 
AND_EQUAL 힌트의 문법은


table : merge
index 연관된 table 이름이나 alias 기술.
index : index scan
수행하는 index 기술

― 최소 2 이상 최대 5 이하의 index 기술해야 합니다.

 

USE_CONCAT

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

 

조인 순서를 위한 SQL 힌트

ORDERED 힌트는 join order 제안

 

ORDERED

―  ORDERED 힌트는 FROM절에 table 나타나는 순서대로 table join시킨다.
― 
예를들어, 아래 SQL문은 table TAB1 table TAB2 조인한 후에 결과와 table 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 힌트를 생략하고 join 수행하면, optimizer table join 순서를 선택
― 
table에서 select 오는 행의 수에대해 알고 있다면 join 순서를 기술하는 ORDERED 힌트를 사용하는 것이 좋다.
― 
사용자가 inner outer table 선택하는 것이 optimizer 할수 있는 것보다 나을 수도 있습니다.

 

Table 조인 방법 SQL 힌트

● SQL문에서 나타나는 조인된 table 정확하게 기술해야만 합니다.
● SQL
문이 table alias 사용합니다면 힌트에서도 table 이름보다는 alias 사용해야만 합니다.
● table
이름이나 alias local database table synonym이나 table 가지고 있어야만 합니다.
● USE_NL
USE_MERGE 힌트는 ORDERED 힌트를 사용해야만 합니다.
● 
오라클은 참조된 table 조인에서 강제로 innertable 힌트를 사용해야만 합니다.
● 
참조된 table outer table이라면 힌트들을 무시합니다.

 

USE_NL

―  USE_NL 힌트는 table 적는 부분에 table 기술된 table inner table로서 사용하여 nested loops로서 다른 table row source 기술된 table join하게 합니다.
―  USE_NL
힌트의 문법은


table : nested loops join
inner table로서 사용될 table 이름과 alias입니다.

―  예를 들어서, ACCOUNTS CUSTOMERS table 조인하는 SQL문이 있다고 생각해보자. 이들 table들은 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 힌트를 사용함으로서 optimizer nested loops join 강제로 선택하게 있습니다.
―  SQL
문에서 USE_NL 힌트는 CUSTOMERStable 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
table로부터 번빼 select 행을 읽은 후에 번째 행을 반환할 있고, 다른 table에서 번째로 일치하는 행을 찾고 그것들을 결합합니다.
― 
반면에 sort-merge join table에서 select 모든 row들을 읽고 정렬한 각각의 저장된 row source 번째 행들을 결합할 때까지 번째 행을 반환할 없습니다.

 

USE_MERGE

―  USE_MERGE 힌트는 오라클이 sort-merge join으로 table 조인하게끔 하는 방법입니다.
―  USE_MERGE
힌트의 문법은

 

병렬 쿼리 실행을 위한 SQL 힌트

 

CACHE

―  CACHE 힌트는 full table scan 수행됐을 힌트에 있는 table 대해 retrieve 블록들은 버퍼 캐시에 있는 LRU list 가장최근(most recently) 사용되어진 것의 끝에 위치
― 
option small lookup table 유용하다. CACHE 힌트는 table default caching specification 무시


SELECT/*+ FULL (scoot_emp) CACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

 

NOCACHE

―  NOCACHE 힌트는 full table scan 수행됐을 힌트에 있는 table 대해 retrieve 블록들은 버퍼 캐시에 있는 LRU list 가장 오래전(least recently) 사용되어진 것의 끝에 위치
― 
버퍼캐수에 blocks 일반적인 행동


SELECT/*+ FULL (scoot_emp) NOCACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

 

구문 연계를 고려하는 SQL 힌트

●  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 칼럼에 index 있음에도 불구하고 EMPtable full table scan함으로서 SQL문을 실행합니다는 것을 알수 있습니다.
― full table scan
time-consuming operation 있습니다.
― EMPtable
찾는 subquery index사용이 가능한 WHERE절이 없기 때문에 오라클은 index 사용할 없습니다.

― 
그러나, 아래의 SQL문은 index 접근함으로서 동일한 행을 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절이 EMPtable DEPTNO칼럼을 사용하므로 DEPTNO_INDEX 사용할 있습니다.
― index
사용은 execution plan step3에서 하게됩니다
―  DEPTNO_INDEX
index range scan 번째 문장에서 EMP table full scan하는 것보다 시간이 적게 걸린다.
― 
번째 query DEPT table에서 모든 DEPTNO 가져오기 위해 EMP table 한번 full scan합니다.
― 
이런 이유로 번째 SQL 문은 번째보다는 빠릅니다.
― Application
NOT IN operator 사용하는 SQL문를 가진다면 NOT EXISTS operator 사용해서 그것들을 다시 써라. 이것은 index 있다면 index 사용할 있게 해줄 것입니다.

 

기존 SQL문을 튜닝하는 방법

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

 

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 선택합니다.

사업자 정보 표시
(주)블루원 | 김홍태 | 서울특별시 용산구 원효로 4가 135 금홍 2빌딩 | 사업자 등록번호 : 106-86-76684 | TEL : 02-3272-7200 | Mail : support_ora@blueone.co.kr | 사이버몰의 이용약관 바로가기

+ Recent posts