지난시간에 종원이 형이 얘기한 바인드 변수가 있는 SQL이 예측 실행계획과 실제 수행할 때
실행계획이 바뀌는 것에 대해 테스트 한 결과를 올려드립니다.
실제 수행할 때 플랜이 바뀌는 것은 여러 변수들이 있겠지만 대표적으로 데이터타입을 잘 못
넣은것으로 테스트를 했습니다.
우선 샘플 데이터를 생성하겠습니다.
1. 샘플 데이터 생성
-- create table
DROP TABLE EMP PURGE;
CREATE TABLE EMP AS
SELECT TO_CHAR(LEVEL) EMPNO
FROM DUAL
CONNECT BY LEVEL <= 1000000
;
-- create index
CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_U1 NOPARALLEL;
-- gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);
2. 통계정보 확인
@stat
COLUMN_NAME DATA_TYPE DATA_LEN Null DISTINCT_KEY DENSITY NUM_NULLS
----------- ---------- -------- ---- -------------- ---------- --------------
EMPNO VARCHAR2 40 Y 969,689 1.0313E-06 0
AVG USED_BLOCKS PCTFREE/ INITRAN
NUMBER_OF_ROWS ROWLEN EMPTY_BLOCKS DEG CHAIN USED/INCR MAXTRAN
-------------- ------- ------------- ---- -------- --------- -------
969,689 6 1,682 1 0 10//0 1/255
0
NUMBER_ROWS CLUSTERING BLEVEL
OWNER.INDEX_NAME TABLESPACE DISTINCT_KEY FACTOR LEAF_BLOKCS
---------------- --------------- --------------- -------------- ------------
USER.EMP_U1 DATA 1,000,000 191,290 [2]
1,000,000 2,354
OWNER.INDEX_NAME Uniq Type Status Drop COLUMN LIST
---------------- ---- ------ ------ ---- -----------
APPS.EMP_U1 UNIQ NORMAL VALID NO EMPNO
테스트는 CHAR 타입으로 컬럼을 생성하고 이 테이블을 바인드 변수로 예측 실행계획을 뜬거랑
실제 수행시 CHAR 타입으로 선언하지 않고 NUMBER 타입을 주어서 수행한 결과를 비교해 보았습니다.
3. 테스트
-- DISPLAY
EXPLAIN PLAN FOR
SELECT *
FROM EMP
WHERE EMPNO = :B1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| EMP_U1 | 1 | 6 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=:B1)
-- DISPLAY_CURSOR
VAR B1 NUMBER;
EXEC :B1 := 999999;
SET LINESIZE 2000;
SET SERVEROUTPUT OFF;
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT *
FROM EMP
WHERE EMPNO = :B1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS COST LAST'));
------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 |00:00:00.60 | 1662 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("EMPNO")=:B1)
보시는 것처럼 실제 수행 시 데이터타입을 보고 플랜을 만들기 때문에 예측과 실제 플랜이 다를
수 있음을 증명할 수 있습니다.
보시고 이상한 점 있으면 리플 달아주세염