바인드 변수 사용시 런타임때 실행계획 바뀌는것 테스트 1 3 3,137

by 강정식 [강정식] 바인드변수 [2009.05.25 18:35:38]


지난시간에 종원이 형이 얘기한 바인드 변수가 있는 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)
  


보시는 것처럼 실제 수행 시 데이터타입을 보고 플랜을 만들기 때문에 예측과 실제 플랜이 다를
수 있음을 증명할 수 있습니다.


보시고 이상한 점 있으면 리플 달아주세염

by 타락천사 [2009.05.25 22:25:04]
하나의 예고,
진짜 물음 왜 그럴까 ?
종원이의 진짜 물음 왜 그런 케이스가 생길까 ?
당신이 DBA 이고, S/W 설계자가 그렇게 묻는다면 어떻게 대답해야 하는가 ?

by 현 [2009.05.26 07:34:50]
내가 택시를 타고 우리집을 가는 도중, 올림픽대로로 갑시다 했지만,
막상 올림픽 대로에 진입하려하니 너무 막혀..
그래서 실제로 갈때는 강변북로를 타고 가는거야...
=> 이런 경우일텐데,
옵티마이져가 처음 계획 세운거랑 나중에 바꾸는 능력이 있다 이거지?

by 박민철 [2014.08.21 18:10:12]

데이터 타입이 문자열에 숫자형 값을 바인딩했으때 문제가 되는거죠??

1. 문자열컬럼 = :숫자형변수

  -> TO_NUMBER(문자열컬럼) = :숫자형변수

2. 숫자형컬럼 = :문자열변수

  -> 숫자형컬럼 = TO_NUMBER(:문자열변수)

이렇게 되는거죠???

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입