통계 수집을 중단한 후 특정 기간이 경과하면 low_value와 high_value를 벗어난 값의 예상 카디널리티가 선형적으로 감소하기 때문에 비효율적인 실행계획이 수립될 수 있습니다.
테스트를 위해 아래와 같이 t1 테이블을 생성하겠습니다. 통계 수집시 히스토그램을 생성하지 않았습니다.
-- 1-1
DROP TABLE t1 PURGE;
CREATE TABLE t1
AS
SELECT ROWNUM AS c1
, DATE '2020-12-31' + CEIL (ROWNUM / 100) AS c2
FROM XMLTABLE ('1 to 36500');
-- 1-2
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'TUNA', tabname => 'T1', method_opt => 'FOR ALL COLUMNS SIZE 1');
아래는 t1 테이블의 통계와 c2 칼럼의 통계를 조회한 결과입니다. t1 테이블의 num_rows는 36,500이고, c2 칼럼의 num_distinct는 365, low_value는 2021-01-01 00:00:00, high_value는 2021-12-31 00:00:00입니다.
-- 2-1
SELECT num_rows FROM user_tables WHERE table_name = 'T1';
NUM_ROWS
--------
36500
1개의 행이 선택되었습니다.
-- 2-2
CREATE OR REPLACE FUNCTION fnc_raw_to_date (i_rawval IN RAW)
RETURN DATE
IS
v_resval DATE;
BEGIN
DBMS_STATS.CONVERT_RAW_VALUE (i_rawval, v_resval);
RETURN v_resval;
END;
/
-- 2-3
SELECT num_distinct
, fnc_raw_to_date (low_value) AS low_value
, fnc_raw_to_date (high_value) AS high_value
FROM user_tab_columns
WHERE table_name = 'T1'
AND column_name = 'C2';
NUM_DISTINCT LOW_VALUE HIGH_VALUE
------------ ------------------- -------------------
365 2021-01-01 00:00:00 2021-12-31 00:00:00
1개의 행이 선택되었습니다.
아래 쿼리는 c2가 2021-12-31인 행을 조회합니다. 예상 카디널리티가 100으로 계산되었습니다. 히스토그램이 없으므로 예상 카디널리티가 num_rows / num_distict 공식으로 계산됩니다.
-- 3
SELECT * FROM t1 WHERE c2 = DATE '2021-12-31';
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 100 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | -- 36500 ÷ 365 = 100
------------------------------------------
아래 코드로 c2가 2020-01-01부터 2022-12-31까지의 실행계획을 생성하겠습니다.
-- 4
DECLARE
v_sql_text VARCHAR2(256);
BEGIN
DELETE FROM plan_table;
FOR f1 IN (SELECT TO_CHAR (DATE '2019-12-31' + ROWNUM, 'YYYY-MM-DD') AS dt
FROM XMLTABLE ('1 to 1096'))
LOOP
v_sql_text := q'[EXPLAIN PLAN SET STATEMENT_ID = '{DT}' FOR SELECT * FROM t1 WHERE c2 = DATE '{DT}']';
v_sql_text := REPLACE (v_sql_text, '{DT}', f1.dt);
EXECUTE IMMEDIATE v_sql_text;
END LOOP;
END;
/
아래 쿼리로 일자별 예상 카디널리티를 조회할 수 있습니다.
-- 5
SELECT statement_id
, cardinality
FROM plan_table
WHERE id = 1
ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
2020-01-01 1
2020-01-02 1
2020-01-03 1
2020-01-04 1
2020-01-05 1
2020-01-06 1
2020-01-07 1
2020-01-08 1
2020-01-09 2
...
2022-12-24 2
2022-12-25 1
2022-12-26 1
2022-12-27 1
2022-12-28 1
2022-12-29 1
2022-12-30 1
2022-12-31 1
1096 행이 선택되었습니다.
아래는 앞선 쿼리의 결과로 생성한 차트입니다. low_value와 high_value를 벗어난 구간에서 예상 카디널리티가 선형적으로 감소하는 것을 확인할 수 있습니다.
DATE 타입 칼럼인 경우 low_value와 high_value를 벗어난 구간에서 예상 카디널리티는 아래의 공식으로 계산됩니다. low_value와 high_value의 구간이 짧고, 구간을 벗어난 일수가 많을 수록 예상 카디널리티가 크게 감소합니다. c2가 2022-07-01인 경우 예상 카디널리티는 50으로 계산됩니다.
cardinality = num_rows / num_distinct = 36500 / 365 = 100
range_days = high_value - low_value = 2021-12-31 - 2021-01-01 = 364
over_days = current_value - high_value = 2022-07-01 - 2021-12-31 = 182
over_days_cardinality = cardinality * ((range_days - over_days) / range_days) = 100 * ((364 - 182) / 364) = 50