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)|
------------------------------------------------------------------------------------
관련 링크