OPT_ESTIMATE 힌트

OPT_ESTIMATE 힌트는 SQL Tuning Advisor(STA)가 내부적으로 사용하는 문서화되지 않은 힌트입니다.

SELECT name
     , RTRIM (CASE WHEN BITAND (target_level, 1) != 0 THEN 'STATEMENT + '   END
           || CASE WHEN BITAND (target_level, 2) != 0 THEN 'QUERY BLOCK + ' END
           || CASE WHEN BITAND (target_level, 4) != 0 THEN 'OBJECT + '      END
           || CASE WHEN BITAND (target_level, 8) != 0 THEN 'JOIN + '        END, '+ ') AS target_level
     , version
  FROM v$sql_hint
 WHERE name = 'OPT_ESTIMATE';

NAME         TARGET_LEVEL                VERSION
------------ --------------------------- --------
OPT_ESTIMATE QUERY BLOCK + OBJECT + JOIN 10.1.0.3

1개의 행이 선택되었습니다.

 

OPT_ESTIMATE 힌트의 구문은 아래와 같습니다.

/*+ OPT_ESTIMATE([@queryblock] operation identifier adjustment) */

 

operation에 아래의 항목을 사용할 수 있습니다.

{ TABLE | INDEX_SCAN | INDEX_FILTER | INDEX_SKIP_SCAN | QUERY_BLOCK | GROUP_BY | HAVING | JOIN | NLJ_INDEX_SCAN | NLJ_INDEX_FILTER }

 

operation별로 아래의 identifier를 지정해야 합니다. QUERY_BLOCK, GROUP_BY, HAVING 오퍼레이션은 identifier를 지정하지 않습니다.

+-------------------------------------------+---------------------------------------------------+
| OPERATION                                 | IDENTIFIER                                        |
+-------------------------------------------+---------------------------------------------------+
| TABLE                                     | tablespec                                         |
| INDEX_SCAN, INDEX_FILTER, INDEX_SKIP_SCAN | tablespec indexspec                               |
| QUERY_BLOCK, GROUP_BY, HAVING             |                                                   |
| JOIN                                      | (tablespec tablespec)                             |
| NLJ_INDEX_SCAN, NLJ_INDEX_FILTER          | inner_tablespec (outer_tablespec) inner_indexspec |
+-------------------------------------------+---------------------------------------------------+

 

adjustment는 아래와 같이 지정할 수 있습니다. ROWS는 로우 수, SCALE_ROWS는 로우 비율, MIN은 최소 로우 수, MAX는 최대 로우 수를 나타냅니다.

{ ROWS | SCALE_ROWS | MIN | MAX } = number

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 (c1, c2, c3) AS SELECT ROWNUM, CEIL (ROWNUM / 1000), LPAD ('X', 1000, 'X') FROM XMLTABLE ('1 to 100000');
CREATE TABLE t2 (c1, c2, c3) AS SELECT ROWNUM, CEIL (ROWNUM / 1000), LPAD ('X', 1000, 'X') FROM XMLTABLE ('1 to 100000');

CREATE INDEX t1_x1 ON t1 (c2, c1);
CREATE INDEX t2_x1 ON t2 (c2);

 

아래 예제는 TABLE 오퍼레이션에 ROWS, SCALE_ROWS, MIN, MAX adjustment를 사용합니다. 예상 카디널리티(Rows) 변화로 동작 방식을 확인할 수 있습니다.

-- 2-1: TABLE
SELECT * FROM t1;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    96M|  2513   (1)|
|   1 |  TABLE ACCESS FULL| T1   |   100K|    96M|  2513   (1)|
---------------------------------------------------------------

-- 2-2
SELECT /*+ OPT_ESTIMATE(TABLE T1 ROWS=20000) */ * FROM t1;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 20000 |    19M|  2513   (1)|
|   1 |  TABLE ACCESS FULL| T1   | 20000 |    19M|  2513   (1)|
---------------------------------------------------------------

-- 2-3
SELECT /*+ OPT_ESTIMATE(TABLE T1 SCALE_ROWS=0.5) */ * FROM t1;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50000 |    48M|  2513   (1)|
|   1 |  TABLE ACCESS FULL| T1   | 50000 |    48M|  2513   (1)|
---------------------------------------------------------------

-- 2-4
SELECT /*+ OPT_ESTIMATE(TABLE T1 MIN=200000) */ * FROM t1;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   200K|   192M|  2514   (1)|
|   1 |  TABLE ACCESS FULL| T1   |   200K|   192M|  2514   (1)|
---------------------------------------------------------------

-- 2-5
SELECT /*+ OPT_ESTIMATE(TABLE T1 MAX=50000) */ * FROM t1;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50000 |    48M|  2513   (1)|
|   1 |  TABLE ACCESS FULL| T1   | 50000 |    48M|  2513   (1)|
---------------------------------------------------------------

 

아래 예제는 INDEX_SCAN과 INDEX_FILTER 오퍼레이션을 사용합니다. 두 오퍼레이션은 유사하게 동작하지만 INDEX_FILTER 오퍼레이션은 필터링에 대한 추가 비용이 발생하며, 원본 예상 카디널리티보다 큰 값으로 설정되지 않습니다.

-- 3-1: INDEX_SCAN, INDEX_FILTER
SELECT * FROM t1 WHERE c2 = 1;

----------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  1000 |   985K|   147   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|   147   (0)|
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |  1000 |       |     4   (0)|
----------------------------------------------------------------------------------

-- 3-2
SELECT /*+ OPT_ESTIMATE(INDEX_SCAN T1 T1_X1 SCALE_ROWS=0.5) */ * FROM t1 WHERE c2 = 1;

----------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  1000 |   985K|    75   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|    75   (0)|
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |   500 |       |     3   (0)|
----------------------------------------------------------------------------------

-- 3-3
SELECT /*+ OPT_ESTIMATE(INDEX_FILTER T1 T1_X1 SCALE_ROWS=0.5) */ * FROM t1 WHERE c2 = 1;

----------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  1000 |   985K|    76   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|    76   (0)|
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |   500 |       |     4   (0)|
----------------------------------------------------------------------------------

-- 3-4
SELECT /*+ OPT_ESTIMATE(INDEX_SCAN T1 T1_X1 SCALE_ROWS=2) */ * FROM t1 WHERE c2 = 1;

----------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  1000 |   985K|   293   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|   293   (0)|
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |  2000 |       |     7   (0)|
----------------------------------------------------------------------------------

-- 3-5
SELECT /*+ OPT_ESTIMATE(INDEX_FILTER T1 T1_X1 SCALE_ROWS=2) */ * FROM t1 WHERE c2 = 1;

----------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  1000 |   985K|   147   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|   147   (0)|
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |  1000 |       |     4   (0)|
----------------------------------------------------------------------------------

 

아래 예제는 INDEX_SKIP_SCAN 오퍼레이션을 사용합니다. INDEX_SKIP_SCAN 오퍼레이션은 INDEX_SCAN 오퍼레이션과 동일하게 동작합니다.

-- 4-1: INDEX_SKIP_SCAN
SELECT /*+ INDEX_SS(T1) */ * FROM t1 WHERE c1 <= 50;

----------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |    50 | 50450 |   109   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    50 | 50450 |   109   (0)|
|*  2 |   INDEX SKIP SCAN                   | T1_X1 |    50 |       |   101   (0)|
----------------------------------------------------------------------------------

-- 4-2
SELECT /*+ INDEX_SS(T1) OPT_ESTIMATE(INDEX_SKIP_SCAN T1 T1_X1 SCALE_ROWS=2) */ * FROM t1 WHERE c1 <= 50;

----------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |    50 | 50450 |   116   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    50 | 50450 |   116   (0)|
|*  2 |   INDEX SKIP SCAN                   | T1_X1 |   100 |       |   101   (0)|
----------------------------------------------------------------------------------

 

아래 예제는 QUERY_BLOCK 오퍼레이션을 사용합니다.

-- 5-1: QUERY_BLOCK
SELECT * FROM (SELECT /*+ NO_MERGE */ * FROM t1);

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|    50M|  2513   (1)|
|   1 |  VIEW              |      |   100K|    50M|  2513   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|    96M|  2513   (1)|
----------------------------------------------------------------

-- 5-2
SELECT * FROM (SELECT /*+ NO_MERGE OPT_ESTIMATE(QUERY_BLOCK SCALE_ROWS=0.5) */ * FROM t1);

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50000 |    25M|  2513   (1)|
|   1 |  VIEW              |      | 50000 |    25M|  2513   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|    96M|  2513   (1)|
----------------------------------------------------------------

 

아래 예제는 GROUP_BY 오퍼레이션을 사용합니다.

-- 6-1: GROUP_BY
SELECT c2 FROM t1 GROUP BY c2;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |   300 |  2520   (1)|
|   1 |  HASH GROUP BY     |      |   100 |   300 |  2520   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|   292K|  2513   (1)|
----------------------------------------------------------------

-- 6-2
SELECT /*+ OPT_ESTIMATE(GROUP_BY SCALE_ROWS=0.5) */ c2 FROM t1 GROUP BY c2;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    50 |   150 |  2520   (1)|
|   1 |  HASH GROUP BY     |      |    50 |   150 |  2520   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|   292K|  2513   (1)|
----------------------------------------------------------------

 

아래 예제는 HAVING 오퍼레이션을 사용합니다. HAVING 절을 사용한 쿼리는 GROUP_BY 오퍼레이션이 동작하지 않습니다.

-- 7-1: HAVING
SELECT c2 FROM t1 GROUP BY c2 HAVING COUNT (*) >= 1;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    15 |  2520   (1)|
|*  1 |  HASH GROUP BY     |      |     5 |    15 |  2520   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|   292K|  2513   (1)|
----------------------------------------------------------------

-- 7-2
SELECT /*+ OPT_ESTIMATE(HAVING SCALE_ROWS=2) */ c2 FROM t1 GROUP BY c2 HAVING COUNT (*) >= 1;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |    30 |  2520   (1)|
|*  1 |  HASH GROUP BY     |      |    10 |    30 |  2520   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|   292K|  2513   (1)|
----------------------------------------------------------------

 

아래 예제는 JOIN 오퍼레이션을 사용합니다.

-- 8-1: JOIN
SELECT * FROM t1 a, t2 b WHERE b.c1 = a.c1;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|   192M| 14687   (1)|
|*  1 |  HASH JOIN         |      |   100K|   192M| 14687   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|    96M|  2513   (1)|
|   3 |   TABLE ACCESS FULL| T2   |   100K|    96M|  2513   (1)|
----------------------------------------------------------------

-- 8-2
SELECT /*+ OPT_ESTIMATE(JOIN (A B) SCALE_ROWS=0.5) */ * FROM t1 a, t2 b WHERE b.c1 = a.c1;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50000 |    96M| 14687   (1)|
|*  1 |  HASH JOIN         |      | 50000 |    96M| 14687   (1)|
|   2 |   TABLE ACCESS FULL| T1   |   100K|    96M|  2513   (1)|
|   3 |   TABLE ACCESS FULL| T2   |   100K|    96M|  2513   (1)|
----------------------------------------------------------------

 

아래 예제는 NLJ_INDEX_SCAN, NLJ_INDEX_FILTER 오퍼레이션을 사용합니다. 두 오퍼레이션은 NL 조인의 이너 인덱스에 대한 카디널리티를 변경하며, INDEX_SCAN, INDEX_FILTER 오퍼레이션과 동일하게 동작합니다. NL 조인의 아우터 인덱스에 대한 카디널리티는 INDEX_SCAN, INDEX_FILTER 오퍼레이션으로 변경할 수 있습니다.

-- 9-1: NLJ_INDEX_SCAN, NLJ_INDEX_FILTER
SELECT * FROM t1 a, t2 b WHERE a.c2 = 1 AND b.c2 = a.c1;

------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |   100K|   192M|  1306   (2)|
|   1 |  NESTED LOOPS                         |       |   100K|   192M|  1306   (2)|
|   2 |   NESTED LOOPS                        |       |  1000K|   192M|  1306   (2)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|   147   (0)|
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |  1000 |       |     4   (0)|
|*  5 |    INDEX RANGE SCAN                   | T2_X1 |  1000 |       |     1   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2    |   100 |    98K|   144   (0)|
------------------------------------------------------------------------------------

-- 9-2
SELECT /*+ OPT_ESTIMATE(NLJ_INDEX_SCAN B (A) T2_X1 SCALE_ROWS=0.5) */ * FROM t1 a, t2 b WHERE a.c2 = 1 AND b.c2 = a.c1;

------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |   100K|   192M|  1227   (1)|
|   1 |  NESTED LOOPS                         |       |   100K|   192M|  1227   (1)|
|   2 |   NESTED LOOPS                        |       |   500K|   192M|  1227   (1)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|   147   (0)|
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |  1000 |       |     4   (0)|
|*  5 |    INDEX RANGE SCAN                   | T2_X1 |   500 |       |     1   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2    |   100 |    98K|    73   (0)|
------------------------------------------------------------------------------------

-- 9-3
SELECT /*+ OPT_ESTIMATE(NLJ_INDEX_FILTER B (A) T2_X1 SCALE_ROWS=0.5) */ * FROM t1 a, t2 b WHERE a.c2 = 1 AND b.c2 = a.c1;

------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |   100K|   192M|  1235   (2)|
|   1 |  NESTED LOOPS                         |       |   100K|   192M|  1235   (2)|
|   2 |   NESTED LOOPS                        |       |   500K|   192M|  1235   (2)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   985K|   147   (0)|
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |  1000 |       |     4   (0)|
|*  5 |    INDEX RANGE SCAN                   | T2_X1 |   500 |       |     1   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2    |   100 |    98K|    73   (0)|
------------------------------------------------------------------------------------

 

관련 링크

• Jonathan Lewis - opt_estimate catalogue
• Christo Kutrovsky - Oracle's OPT_ESTIMATE hint: Usage Guide