OPT_ESTIMATE 힌트처럼 아래의 세 가지 힌트도 SQL Tuning Advisor(STA)가 내부적으로 사용하는 문서화되지 않은 힌트입니다. 이번 글에서 TABLE_STATS 힌트의 동작을 살펴보고, 다음 글에서 COLUMN_STATS, INDEX_STATS 힌트를 살펴보겠습니다.
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 IN ('TABLE_STATS', 'COLUMN_STATS', 'INDEX_STATS');
NAME TARGET_LEVEL VERSION
------------ ------------ --------
TABLE_STATS STATEMENT 10.1.0.3
COLUMN_STATS STATEMENT 10.1.0.3
INDEX_STATS STATEMENT 10.1.0.3
3 행이 선택되었습니다.
TABLE_STATS 힌트의 구문은 아래와 같습니다.
/*+ TABLE_STATS(tablespec method adjustment) */
method에 아래의 항목을 사용할 수 있습니다.
{ SET | SCALE | SAMPLE | DEFAULT }
method는 힌트에 따라 동작 방식이 다르며 명칭에 따라 동작하지 않는 것으로 보입니다.
+--------------+---------+-------------+-------------+------------------+
| | SET | SCALE | SAMPLE | DEFAULT |
+--------------+---------+-------------+-------------+------------------+
| TABLE_STATS | working | not working | working | strange behavior |
| COLUMN_STATS | working | working | not working | working |
| INDEX_STATS | working | working | not working | strange behavior |
+--------------+---------+-------------+-------------+------------------+
adjustment는 아래의 형식으로 사용하며 다수의 항목을 함께 사용할 수 있습니다.
{ ROWS | BLOCKS | ROW_LENGTH } = value
테스트를 위해 아래과 같이 테이블을 생성하고 통계정보를 수집하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 VARCHAR2(4000));
INSERT INTO t1 SELECT ROWNUM, ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');
아래는 t1 테이블의 통계정보입니다. ROWS, BLOCKS, ROW_LENGTH 항목은 각각 num_rows, blocks, avg_row_len 값을 나타냅니다.
-- 2
SELECT num_rows -- ROWS
, blocks -- BLOCKS
, avg_row_len -- ROW_LENGTH
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------ -----------
10000 244 109
1개의 행이 선택되었습니다.
아래는 t1 테이블을 조회하는 실행계획입니다.
-- 3
SELECT * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1064K| 44 (0)|
| 1 | TABLE ACCESS FULL| T1 | 10000 | 1064K| 44 (0)|
---------------------------------------------------------------
비교를 위해 아래와 같이 데이터를 입력하고 통계정보를 수집하겠습니다.
-- 4
INSERT INTO t1 SELECT ROWNUM + 10000, ROWNUM + 10000, LPAD ('X', 300, 'X') FROM XMLTABLE ('1 to 10000');
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');
통계정보가 아래와 같이 변경되었습니다.
-- 5
SELECT num_rows -- ROWS
, blocks -- BLOCKS
, avg_row_len -- ROW_LENGTH
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------ -----------
20000 622 209 -- before: 10000, 244, 109
1개의 행이 선택되었습니다.
통계정보 변경으로 인해 실행계획의 Rows, Bytes, Cost 값이 아래와 같이 변경됩니다.
-- 6
SELECT * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 4082K| 110 (1)|
| 1 | TABLE ACCESS FULL| T1 | 20000 | 4082K| 110 (1)| -- before: 10000, 1064K, 44 (0)
---------------------------------------------------------------
변경 전 통계정보 값을 지정하고 4개의 method로 실행계획을 생성하면 SET과 SCALE 방식만 정상적으로 동작하며 명칭과 달리 두 방식의 동작에 차이가 없는 것을 확인할 수 있습니다.
-- 7-1: working
SELECT /*+ TABLE_STATS(T1 SET ROWS=10000 BLOCKS=244 ROW_LENGTH=109) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1064K| 44 (0)|
| 1 | TABLE ACCESS FULL| T1 | 10000 | 1064K| 44 (0)|
---------------------------------------------------------------
-- 7-2: not working
SELECT /*+ TABLE_STATS(T1 SCALE ROWS=10000 BLOCKS=244 ROW_LENGTH=109) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2128K| 110 (1)|
| 1 | TABLE ACCESS FULL| T1 | 20000 | 2128K| 110 (1)|
---------------------------------------------------------------
-- 7-3: working
SELECT /*+ TABLE_STATS(T1 SAMPLE ROWS=10000 BLOCKS=244 ROW_LENGTH=109) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1064K| 44 (0)|
| 1 | TABLE ACCESS FULL| T1 | 10000 | 1064K| 44 (0)|
---------------------------------------------------------------
-- 7-4: strange behavior
SELECT /*+ TABLE_STATS(T1 DEFAULT ROWS=10000 BLOCKS=244 ROW_LENGTH=109) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 797K| 19 (0)|
| 1 | TABLE ACCESS FULL| T1 | 8168 | 797K| 19 (0)|
---------------------------------------------------------------
adjustment 항목의 개별 동작을 살펴보면 ROWS는 카디널리티(Rows)를 변경하는 것을 알 수 있습니다.
-- 8-1
SELECT /*+ TABLE_STATS(T1 SET ROWS=10000) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 2041K| 50 (0)|
| 1 | TABLE ACCESS FULL| T1 | 10000 | 2041K| 50 (0)|
---------------------------------------------------------------
-- 8-2
SELECT /*+ TABLE_STATS(T1 SET ROWS=20000) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 4082K| 100 (1)|
| 1 | TABLE ACCESS FULL| T1 | 20000 | 4082K| 100 (1)|
---------------------------------------------------------------
BLOCKS는 내부 계산식에 의해 카디널리티를 변경하는 것으로 보입니다.
-- 9-1
SELECT /*+ TABLE_STATS(T1 SET BLOCKS=244) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8608 | 1756K| 44 (0)|
| 1 | TABLE ACCESS FULL| T1 | 8608 | 1756K| 44 (0)|
---------------------------------------------------------------
-- 9-2
SELECT /*+ TABLE_STATS(T1 SET BLOCKS=622) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21943 | 4478K| 110 (1)|
| 1 | TABLE ACCESS FULL| T1 | 21943 | 4478K| 110 (1)|
---------------------------------------------------------------
ROW_LENGTH는 카디널리티를 변경하지 않고 Bytes만 변경합니다.
-- 10-1
SELECT /*+ TABLE_STATS(T1 SET ROW_LENGTH=109) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2128K| 110 (1)|
| 1 | TABLE ACCESS FULL| T1 | 20000 | 2128K| 110 (1)|
---------------------------------------------------------------
-- 10-2
SELECT /*+ TABLE_STATS(T1 SET ROW_LENGTH=209) */ * FROM t1;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 4082K| 110 (1)|
| 1 | TABLE ACCESS FULL| T1 | 20000 | 4082K| 110 (1)|
---------------------------------------------------------------