ORACLE _optim_peek_user_bind
from
1. _optim_peek_user_binds
_optim_peek_user_binds (이하 ‘파라미터’ 라고 부르겠음) 는 바인드변수를 사용했을 때
실값을 가지고 실행플랜을 만들기 위해 사용된다.
다시말해 바인드변수를 사용한 SQL 이 들어왔을 때 해당 변수에 들어온 실값을 들여다
보고 실행계획을 만들겠다는 것이며 이를 통해 이와 비슷한 유형의 실값이 후에도
들어올 것이라 기대한다.
2. _optim_peek_user_binds 조회
A. <<9i>>
SQL> /
NAME SESMOD SYSMOD DESCRIPTION
------------------------------ ---------- ------------------ -------------------------------
_optim_peek_user_binds TRUE FALSE enable peeking of user binds
/*+ 9i 의 default 값 조회하기 */
B. <<10g>>
SQL> @f_par
parameter_name의 값을 입력하십시오: _optim_peek
PL/SQL 처리가 정상적으로 완료되었습니다.
NAME SESMO SYSMOD DEF VALUE DESCRIPTION
------------------------------ ----- --------- ---------- ---------- -------------------------------
_optim_peek_user_binds TRUE IMMEDIATE TRUE TRUE enable peeking of user binds
SESMO, SYSMOD 는 SYS.X$KSPPI 의 KSPPIFLG 를 가공한 값으로 각기 SESSION LEVEL 에서 SYSTEM LEVEL 에서 수정이 가능한지를 의미한다.
해당 파라미터는 SESSION LEVEL 에서 수정이 가능하고 SYSTEM LEVEL 에서도 즉시 수정이 가능하다.
DEFAULT 값은 TRUE 로 되어있고 현재 VALUE 도 TRUE 로 되어있다.
3. _optim_peek_user_binds 수정
10g 에서 진행했고 9i나 10g 나 수정방법은 동일하다.
A. <<SYSTEM LEVEL>>
SQL> alter system set "_optim_peek_user_binds"=false scope=memory;
시스템이 변경되었습니다.
SQL> alter system set "_optim_peek_user_binds"=true scope=memory;
시스템이 변경되었습니다.
scope = spfile, scope = both 모두 가능하다.
B. <<SESSION LEVEL>>
SQL> alter session set "_optim_peek_user_binds"=false;
세션이 변경되었습니다.
4. _optim_peek_user_binds 의 한계
_optim_peek_user_binds 는 bind 변수의 실값을 가지고 보다 나은 실행플랜을 만들기
위한 방책이다. 실값을 사용하는데 참고하는 정보는 이 예제의 경우 histogram 이다.
histogram 은 값의 분포를 알기 위한 정보이다.
이를 테면 JOB_ID 가 ‘SA_REP’가 90%이고 ‘PU_MAN’ 값이 2% 이라고 했을 때 optimizer는 조건이 SA_REP 일 때 FULL SCAN 을 선택하고 PU_MAN 일 때 INDEX RANGE SCAN 을 선택할 것이다. (일반적으로 생각했을 때의 이야기다.)
_optim_peek_user_binds 로 생성된 플랜은 aging out 되기 전까지 사용되며 이 후 다시 파싱하게 되는데 이때 들어오는 첫 바인드 변수가 문제가 될 수 있다. 이를테면 들어오는 조건들이 full scan 으로 유리한 조건들이 대다수였는데 한번 PU_MAN 같이 INDEX SCAN 이 유리한 조건이 들어왔다고 하면 이를 가지고 실행플랜을 생성하며 이 후 FULL SCAN 이 유리한 조건에도 INDEX SCAN 을 하는 실행플랜을 사용하게 된다.
대용량 테이블이라 생각했을 때 이로 인해 불필요하게 발생할 인덱스 스캔과 랜덤엑세스는 적은 비용이 아니다.
5. _optim_peek_user_binds 예제
HR.EMPLOYEES 의 JOB_ID, FIRST_NAME, LAST_NAME 을 CREATE SELECT 하여 새로운
테이블 생성 후 INSERT SELECT 로 ROW를 6848 개로 불렸고 마지막으로 JOB_ID 에
인덱스를 생성한 후 DBMS_STATS 로 통계정보를 수집하였다.
각 실행플랜은 다음의 경우 사용되며
FULL SCAN : JOB_ID = ‘SA_REP’
INDEX RANGE SCAN JOB_ID = ‘PU_MAN’
조건은 바인드변수를 사용해 처리하였다.
<<FLUSH 후 기존 FULL SCAN 으로 풀리는 쿼리 수행>>
JOB_ID = ‘SA_REP’ 조건으로 수행하였다.
SQL> @DISP
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID 75wj271bpv3c4, child number 0
-------------------------------------
select job_id,FIRST_NAME,LAST_NAME from employees where job_id = :job_id
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1920 | 63360 | 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"=:JOB_ID)
Note
-----
- dynamic sampling used for this statement
22 개의 행이 선택되었습니다.
<<FLUSH 후 기존 INDEX RANGE SCAN으로 풀리는 쿼리 수행>>
JOB_ID = ‘PU_MAN’ 로 수행하였다.
SQL> @DISP
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID 75wj271bpv3c4, child number 0
-------------------------------------
select job_id,FIRST_NAME,LAST_NAME from employees where job_id = :job_id
Plan hash value: 1460121040
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 64 | 2112 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPLOYEES_JOB_ID_IDX | 64 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"=:JOB_ID)
Note
-----
- dynamic sampling used for this statement
23 개의 행이 선택되었습니다.
<<기존 FULL SCAN 으로 풀리는 쿼리>>
위의 결과에서 SHARED_POOL 을 FLUSH 하지 않고 JOB_ID = ‘SA_REP’ 로 수행하였다.
SQL> @DISP
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID 75wj271bpv3c4, child number 0
-------------------------------------
select job_id,FIRST_NAME,LAST_NAME from employees where job_id = :job_id
Plan hash value: 1460121040
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 64 | 2112 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPLOYEES_JOB_ID_IDX | 64 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"=:JOB_ID)
Note
-----
- dynamic sampling used for this statement
23 개의 행이 선택되었습니다.
위의 plan hash value 를 보면 종전 PU_MAN 이었을 때와 동일하다. 다시 말해 PLAN 을 그대로 가져다 사용했다는 의미이다.
이 결과는 처음 PU_MAN 을 바인드변수의 실값으로 주었을 때 생성되었던 실행플랜과 다르다. 이것이 의미하는 바는 비효율적인 실행플랜으로 재사용되었음을 말한다.