[SQLP] Hint 실습

1. Table access 예제

1) 인덱스 없이 사용

explain plan for select * from orders where row_id=50000;

select * from table(DBMS_XPLAN.DISPLAY);

Plan hash value: 1275100350
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |   227 |   479   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDERS |     1 |   227 |   479   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ROW_ID"=50000)

  - 인덱스가 없기 때문에 TABLE ACCESS FULL가 발생

 

2) 인덱스 추가

create index orders_rowid on orders(row_id)
explain plan for select * from orders where row_id=50000;

select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3050922610
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   227 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS       |     1 |   227 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORDERS_ROWID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

  - 인덱스를 생성했기 때문에 INDEX RANGE SCAN  발생 

 

3) 강제로 full scan 하도록 Hint 사용

explain plan for select /*+full(orders)*/ * from orders where row_id=50000;
select * from table(DBMS_XPLAN.DISPLAY);

Plan hash value: 1275100350
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |   227 |   479   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDERS |     1 |   227 |   479   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ROW_ID"=50000)

 

4) 별칭을 지정했으나, 테이블 명을 기입했을 때, full scan 하도록 Hint 사용할 경우

explain plan for select /*+full(orders)*/ * from  orders A  where row_id=5000
select * from table(DBMS_XPLAN.DISPLAY);

Plan hash value: 3050922610
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   227 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS       |     1 |   227 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORDERS_ROWID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ROW_ID"=50000)
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
 
   1 -  SEL$1
         N -  full(orders)

 

-별칭을 사용하지 않아, Hint가 무시된 것을 확인할 수 있다.

 

2. Parallel 예제

데이터베이스의 정보 출력 시 프로세스가 사용된다. 이 때, 여러 개의 프로세스를 병렬로 처리해서 더 빠른 SQL 실행을 유도할 수 있다. 이를 parallel 힌트라고 한다.

explain plan for 
select R.market, sum(O.profit)
from orders O, returns R
where O.order_ID = R.order_Id
group by R.market;

United States	23136.19749999999386889
APAC	46675.64969999999954339
EU	31617.94049999999967726
LATAM	16569.03587999999968433


select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3485348817
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     4 |   188 |   487   (2)| 00:00:01 |
|   1 |  HASH GROUP BY      |         |     4 |   188 |   487   (2)| 00:00:01 |
|*  2 |   HASH JOIN         |         |  2406 |   110K|   486   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| RETURNS |  1173 | 26979 |     5   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| ORDERS  | 51290 |  1202K|   480   (1)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("O"."ORDER_ID"="R"."ORDER_ID")
   
   -- 병렬처리를 유도하여 실행계획 써보기
select /*+parallel(4)*/ R.market, sum(O.profit)
from orders O, returns R
where O.order_ID = R.order_Id
group by R.market;


Plan hash value: 753833677
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     4 |   188 |   136   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10001 |     4 |   188 |   136   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY           |          |     4 |   188 |   136   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE             |          |     4 |   188 |   136   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH          | :TQ10000 |     4 |   188 |   136   (1)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY        |          |     4 |   188 |   136   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |        HASH JOIN           |          |  2406 |   110K|   135   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |         JOIN FILTER CREATE | :BF0000  |  1173 | 26979 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   9 |          TABLE ACCESS FULL | RETURNS  |  1173 | 26979 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |         JOIN FILTER USE    | :BF0000  | 51290 |  1202K|   133   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |          PX BLOCK ITERATOR |          | 51290 |  1202K|   133   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 12 |           TABLE ACCESS FULL| ORDERS   | 51290 |  1202K|   133   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("O"."ORDER_ID"="R"."ORDER_ID")
  12 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"O"."ORDER_ID"))
 
Note
-----
   - Degree of Parallelism is 4 because of hint

 - parallel 사용함으로 PX 가 생긴걸 볼 수 있다.

 

 

3. Append 힌트  ★★

append 힌트를 사용하면 insert 시에 데이터베이스 버퍼 캐시를 거치지 않고 디스크에 직접 데이터를 입력한다..

append 힌트는 데이터베이스가 no archive mode 혹은 table 을 nologging  모드 같이 사용한다.

- 사용할 경우 대용량데이터를 디스크에 빠르게, 효율적으로 넣을 수 있다.
- 그러나 인덱스가 있는 경우 오히려 삽입 성능이 안좋아 질 수 있다. 실제로는 인덱스를 비활성화하고 정보를 삽입한다.