'오라클 힌트'에 해당되는 글 1건

Oracle HINT(기초)

1. IT Story/DB 2012. 2. 29. 21:47

오라클 HINT

  • 쿼리문이 실행될 때 기본적으로 DB내에 존재하는 옵티마이저가 쿼리를 분석한 뒤 가장 좋은 계획을 세워서 쿼리를 실행하고 결과를 반환한다.

    모든 사항을 고려하여 자동으로 결정하므로 편하지만, 100% 믿을 수는 없다.

    그래서 옵티마이저에게 맡기지 않고 쿼리를 작성한 사람이 직접, 원하는 대로 실행방법을 지정하여 실행하는 방법이 오라클 HINT이다.

   

-프로그램이 완벽하지 않기 때문에 세부적이고, 중요한 처리를 사람이 직접 수정하기 위해 사용.

   

  • 힌트를 사용하려면 두가지 방법이 있다.

   

1.   /*+   힌트    */

2.   --+   힌트

   

  • 두 가지의 방식은 실제 힌트문장에는 차이가 없고, 서술하는 방식의 차이이다, 1번은 여러줄에 걸쳐쓸 수 있는 반면 2번은 한 줄에만 적용이 된다.  개발할때 주석도 한줄주석 여러줄 주석 그런거 처럼…

   

Hints from Optimization Approaches and Goals

  

  

HINT

내용

사용법

ALL_ROWS

cost-based optimizer에서 전체 응답시간이 가장 적은 plan 선택

/*+ ALL_ROWS */

FIRST_ROWS(n)

: cost-based optimizer에서 첫번째 row가 가장 빨리 나오는 plan으로 선택

/*+ FIRST_ROWS(10) */

/*+ FIRST_ROWS(n) */

CHOOSE

통계정보의 유무에 따라 RBO/CBO 가 선택됨

/*+ CHOOSE */

RULE

rule-based optimization로 plan 작성

/*+ RULE */

   

<TEST>

   

select distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:09

   

  1. /*+ ALL_ROWS */

       

select /*+ All_ROWS(10) */ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:09

   

   

   

  1. /*+ FIRST_ROWS(10) */

       

select /*+ FiRST_ROWS(10) */ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:10

   

   

   

  1. /*+ CHOOSE */

       

select /*+ CHOOSE */ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:10

   

   

  1. /*+ RULE */

       

select /*+ RULE */ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:12

   

Hints for Query Transformations

  

  

HINT

내용

사용법

USE_CONCAT

: IN 또는 OR 연산자가 있을 경우 CONCATENATION 을 사용하도록 한다

in절을 concatenation access operation으로 수행

/*+ USE_CONCAT */

USE_EXPAND

in절을 concatenation access operation으로 수행 못하게 함

  

NO_EXPAND

  

  

REWRITE

query rewrite 수행

  

EXPAND_GSET_TO_UNION

  

  

NOREWRITE

query rewrite 를  수행 못함

  

MERGE

view merging 수행

  

NO_MERGE

view merging 수행 못하게 함

  

STAR_TRANSFORMATION

  

  

FACT

  

  

NO_FACT

  

  

   

Hints from Access Paths

  

  

HINT

내용

사용법

FULL

TABLE FULL SCAN 을 하도록 유도

FULL SCAN

  

/*+ FULL(A) don't use the index on A.table */

FULL(TALBE명)

ROWID

  

  

CLUSTER

Cluster Scan을 선택하도록 지정한다. 따라서 clustered object들에만 적용 됩니다.

/*+CLUSTER(table name)*/

HASH

  

  

INDEX

( = INDEX_ASC) : 지정한 인덱스를 사용하여 ACCESS PLAN을 작성하도록 유도

INDEX를 순차적으로 스캔

  

/*+ INDEX(patients name_index) */,

INDEX(TABLE명, INDEX명)

INDEX_ASC

지정된 index를 오름차순으로 쓰게끔 지정 합니다. Default로 Index Scan은 오름차순.

   

  

  

INDEX_JOIN

  

  

INDEX_DESC

INDEX_DESC : 지정한 인덱스를 사용하여 ACCESS PLAN을 작성하도록 유도

(인덱스를 맨뒤에서부터 SCAN 하도록 한다)

INDEX를 역순으로 스캔

/*+ INDEX_DESC(patients name_index) */

INDEX_DESC(TABLE명, INDEX명)

  

INDEX_FFS

INDEX FAST FULL SCAN

INDEX_FFS(TABLE명, INDEX명)

AND_EQUAL

NDEX MERGE 수행

AND_EQUALS(INDEX_NAME, INDEX_NAME)

   

<Test1>

   

select d.department_id, d.department_name, COUNT(*) from departments d, employees e where d.department_id = e.department_id group by d.department_id, d.department_name having count(*) <3;

Full(A)

   

   

   

화면 캡처: 2011-12-27 오전 11:16

   

   

  • /*+ FULL(A) don't use the index on A.table */

       

select /*+ FULL(e) don't use the index on e*/ distinct d.department_id, d.department_name, COUNT(*) from departments d, employees e where d.department_id = e.department_id group by d.department_id, d.department_name having count(*) <3;

  

   

   

화면 캡처: 2011-12-27 오전 11:19

   

   

<Test2>

   

select distinct job_id from employees where department_id in (10,20);

  

   

   

   

화면 캡처: 2011-12-27 오전 11:26

  

  • /*+ INDEX(patients name_index) */

       

select /*+ INDEX(employees EMP_JOB_IX)*/ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:28

   

select /*+ INDEX(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:28

   

   

  • /*+ INDEX_ASC(patients name_index) */

       

    선수되어있던 인덱스를 이용해서 순차적으로 검색?

       

select /*+ INDEX_ASC(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:31

   

  • /*+ INDEXJOIN(patients name_index) */

       

select /*+ INDEX_JOIN(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:32

   

   

  • /*+ INDEX_DESC(patients name_index) */

       

select /*+ INDEX_DESC(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:33

   

   

   

  • /*+ INDEX_FFS(patients name_index) */

       

select /*+ INDEX_FFS(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

  

   

   

화면 캡처: 2011-12-27 오전 11:33

   

Hints from join orders

  

  

HINT

내용

사용법

ORDERED

FROM 절에 있는 테이블들의 JOIN 순서를 지정함

/*+ ORDERED */

STAR

  

  

DRIVING

해당 테이블을 먼저 DRIVING- driving(table)

  

   

Hints for join Operations

  

  

HINT

내용

사용법

USE_NL

지정한 테이블에 대해 NESTED LOOP 조인이 일어나도록 지정함

NESTED LOOP JOIN

/*+ ORDERED USE_NL() to get first row faster */

USE_NL(TABLE1, TABLE2)

  

USE_MERGE

지정한 테이블에 대해 SORT MERGE 조인이 일어나도록 지정함

SORT MERGE JOIN

  

/*+USE_MERGE(emp dept)*/

USE_MERGE(TABBLE1, TABLE2)

USE_HASH

지정한 테이블에 대해 HASH 조인이 일어나도록 지정함

HASH JOIN

  

/*+USE_HASH(emp dept)*/

USE_HASH(TABLE1, TABLE2)

  

DRIVING_SITE

  

  

LEADING

  

  

HASH_AJ, HASH_SJ

HASH ANTIJOIN

HASH SEMIJOIN

HASH_AJ(TABLE1, TABLE2)

HASH_SJ(TABLE1, TABLE2)

MERGE_AJ, MERGE_SJ

SORT MERGE ANTIJOIN

SORT MERGE SEMIJOIN

MERGE_AJ(TABLE1, TABLE2)

MERGE_SJ(TABLE1, TABLE2)

NL_AJ, NL_SJ

NESTED LOOP ANTI JOIN

NESTED LOOP SEMIJOIN

NL_AJ(TABLE1, TABLE2)

NL_SJ(TABLE1, TABLE2)

   

<Test>

   

select * from employees, departments where employees.department_id = departments.department_id;

  

   

   

   

화면 캡처: 2011-12-27 오전 11:40

   

   

  1. /*+ USE_NL(TABBLE1, TABLE2) */

       

select /*+ USE_NL(employees, departments) */ * from employees, departments where employees.department_id = departments.department_id;

  

   

   

화면 캡처: 2011-12-27 오전 11:40

   

   

  1. /*+ USE_MERGE(TABBLE1, TABLE2) */

       

select /*+ USE_MERGE(employees, departments) */ * from employees, departments where employees.department_id = departments.department_id;

  

   

   

화면 캡처: 2011-12-27 오전 11:40

   

   

  1. /*+ USE_HASH(TABBLE1, TABLE2) */

       

select /*+ USE_HASH(employees, departments) */ * from employees, departments where employees.department_id = departments.department_id;

  

   

   

화면 캡처: 2011-12-27 오전 11:40

   

Hints for Parallel Execution

  

  

HINT

내용

사용법

Parallel        

select, insert 시 여러 개의 프로세스로 수행- parallel(table, 개수)

  

NOPARALLEL

  

  

PQ_DISTRIBUTE

  

  

PARALLEL_INDEX

NDEX PARALLEL SCAN

PARALLEL_INDEX(TABLE명,INDEX명)

NOPARALLEL_INDEX

NDEX PARALLEL SCAN 제한

NOPARALLEL_INDEX(TABLE명,INDEX명)

   

DEGREE의 의미 및 결정

  •   Parallel Query에서 degree란 하나의 operation 수행에 대한 server process의 개수 입니다. 이러한 degree 결정에 영향을 주는 요인들에는 다음과 같은 것들이 있습니다.

    (1) system의 CPU 갯수

    (2) system의 maximum process 갯수

    (3) table이 striping되어 있는 경우, 그 table이 걸쳐있는 disk의 갯수

    (4) data의 위치 (즉, memory에 cache되어 있는지, disk에 있는지)

    (5) query의 형태 (예를 들어 sorts 혹은 full table scan)

  •   한 사용자만이 parallel query를 사용하는 경우, sorting이 많이 필요한 작업과 같은 CPU-bound 작업의 경우는 CPU 갯수의 1 ~ 2배의 degree가 적당하며, sorting보다는 table scan과 같은 I/O bound 작업의 경우는 disk drive 갯수의 1 ~ 2배가 적당합니다.
  •   동시에 수행되는 parallel query가 많은 경우에는 위의 각 사용자의 degree를 줄이거나 동시에 사용하는 사용자 수를 줄여야 합니다.

   

Additional Hints

  

  

HINT

내용

사용법

APPEND

insert 시 direct loading

/*+ APPEND */

NOAPPEND

  

  

CACHE

데이터를 메모리에 caching

  

NOCACHE

데이터를 메모리에 caching하지 않음

  

UNNEST

  

  

NO_UNNEST

  

  

PUSH_PRED

  

  

NO_PUSH_PRED

  

  

PUSH_SUBQ

subquery를 먼저 수행

  

NO_PUSH_SUBQ

  

  

ORDERED_PREDICATES

  

  

CURSOR_SHARING_EXACT

  

  

DYNAMIC_SAMPLING

  

  

   

   

조인 순서 조정을 위한 힌트

힌트는 다수의 테이블을 조인하는 경우에 조인 순서에 혼선이 있을 적용하는 것이 가장 일반적이다.

Sort Merge 조인 이나 해쉬조인에서는 적은 테이블을 먼저 처리하도록 유도할 사용하는 것이 좋다.

ORDERED

FROM절에 기술된 테이블 순서대로 조인을 수행하도록 유도한다.

만약 LEADING 힌트와 함께 사용하면 LEADING 힌트는 무시된다.

) SELECT /*+ ORDERED */ ...
FROM TAB1, TAB2, TAB3
WHERE ....

ORDERED 힌트는 액세스 순서만 제시할 뿐이고, 조인 방법 과는 무관하기 때문에 조인방법을 유도하기 위한 USE_NL, USE_MERGE등의 힌트와 함께 사용하는 것이 일반적이다

) SELECT /*+ ORDERED USE_NL(A B C) */.........
FROM TAB1 a, TAB2 b, TAB3 c
WHERE ........

LEADING

FROM절에 기술한 테이블의 순서와 상관없이 조인 순서를 제어하는 힌트로서 ORDERED 힌트와는 달리 FROM절을 변경 필요 없이 사용할 있다.

ORDERED 힌트와 함께 사용되면 무시된다.

) SELECT /*+ LEADING(b c) */......
FROM CUST a, ORDER_DETAIL b, ITEM c
WHERE a.cust_no = b.cust_no
         AND b.item_no = c.item_no
         AND ....

    

   

   

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 10g Documentation Library - councepts

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.oracleclub.com/lecture/1260>

원본 위치 <http://blog.naver.com/PostView.nhn?blogId=comele&logNo=70069313846&beginTime=0&jumpingVid=&from=search&redirect=Log&widgetTypeCall=true&topReferer=http%3A%2F%2Fsearch.naver.com%2Fsearch.naver%3Fsm%3Dtab_hty.top%26where%3Dnexearch%26ie%3Dutf8%26query%3D%25EC%2598%25A4%25EB%259D%25BC%25ED%2581%25B4%2B%25ED%259E%258C%25ED%258A%25B8%26x%3D0%26y%3D0>

원본 위치 <http://blog.naver.com/PostView.nhn?blogId=myshyz&logNo=50125858009&beginTime=0&jumpingVid=&from=search&redirect=Log&widgetTypeCall=true&topReferer=http%3A%2F%2Fsearch.naver.com%2Fsearch.naver%3Fwhere%3Dnexearch%26query%3DOracle%2BHint%26x%3D21%26y%3D23%26sm%3Dtop_hty%26fbm%3D1%26ie%3Dutf8>


블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,