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