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)| ---------------------------------------------------------------