#37_140805_TUNING_바인드 변수와 실행계획

# 바인드 변수와 실행계획

1.     바인드 변수를 사용할 때 주의할 사항

- SQL> select empno, ename, sal, from emp where empno = 7788;

- 위 쿼리 수행 단계

1)     parsing > SQL > 기계어

: 실행계획, SQL, Parse tree 3가지가 shared pool 캐쉬가 된다

: 다음번에 똑 같은 문장이 들어오면 parsing 과정을 생략하기 위해서 수행

2)     execute

3)     fetch

2.     바인드 변수 사용

SQL> select empno, ename, sal from emp where empno = 7788; SQL> select empno, ename, sal from emp where empno = 7902 - 위의 2문장은 다른 문장으로 인식한다 SQL> select empno, ename, sal from emp where empno = :v_1; - 2개의 문장을 같은문장으로 인식하도록 할려면 bind 변수를 이용해야 한다

3.     바인드 변수를 썼을 때 처리과정

1)     parsing : 실행계획 생성

SQL> select empno, ename, sal from emp where ename = :v_ename;

: 만약에 emp 테이블의 데이터가 ename에 ‘scott99’가 9999개이고 ‘scott1’ 한개라고 가정하면 중복데이터를 가진 테이블을 조회하는데 index scan을 하면 매우 느려지므로 bind peeking을 이용하여 해결한다

: bind peeking의 역할은 미리 binding 변수의 첫번째 내용만을 조회하고 조건절과 같은 값이라면 위 경우에 full table scan을 수행한다

: 이런 경우 단점이 다른 값을 대량의 데이터가 insert가 된다면 shared pool에는 쿼리의 내용과 full table scan 정보를 가지고 있는데 두번쨰 데이터에서 scott1로 binding 되면 느려질 수 있다

2)     binding

3)     execute

4)     fetch

l 바인드 변수 사용

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP100'); SQL> SELECT * FROM EMP100 WHERE ename='scott1'; - 실행계획 확인 : index range scan SQL> SELECT * FROM EMP100 WHERE ename='scott99'; - 실행계획 확인 : full table scan SQL> ALTER SYSTEM FLUSH SHARED_POOL; SQL> VARIABLE v_ename VARCHAR2(20); SQL> EXEC :v_ename :='scott1'; - 바인드 변수에 scott1을 입력해줌 SQL> SELECT COUNT(*) FROM EMP100 WHERE ename=:v_ename; SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS')); - 방금 수행했던 SQL의 실행계획을 확인 - index range scan 수행 SQL> EXEC :v_ename := 'scott99'; SQL> SELECT COUNT(*) FROM EMP100 WHERE ename=:v_ename; SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS')); - 수행해보면 full table scan을 하는 것이 맞지만 똑같이 index range scan 실행계획을 세우는 것을 확인할 수 있다

l cusrsor_sharing 파라미터

- cursor_sharing 파라미터를 force로 지정하면 오라클이 알아서 리터럴 SQL을 bind 변수를 변경한다

- 이럴경우 바인드 변수에 의해서 잘못된 실행계획을 세우는 문제가 발생한다

- 그 이후에도 리터럴 변수로 바꿔도 최초에 실행헀던 실행계획으로 계속해서 수행한다

- 결국 항상 같은 실행계획으로 수행한다

SQL> select count(*) from emp100 where ename='scott1';   COUNT(*) ----------          1 SQL> select sql_text from v$sql     where sql_text like '%emp100%'; SQL_TEXT --------------------------------------------------------------------------select count(*) from emp100 where ename=:"SYS_B_0" SQL> select count(*) from emp100 where ename='scott99';   COUNT(*) ----------       9999 SQL> select sql_text from v$sql     where sql_text like '%emp100%'; SQL_TEXT -------------------------------------------------------------------------- select count(*) from emp100 where ename=:"SYS_B_0" - scott99가 1개를 제외하고 9999개가 존재하는데 index scan을 수행하고 바인드변수를 이용하고 있다 - 결국 SQL이 공유되면서 잘못된 실행계획으로 수행된다 - 이럴경우 리터럴 SQL로 수행해도 자동으로 바인드 변수로 변경 되기 떄문에 실행계회깅 둘다 index scan이 된다

l 바인드 변수의 잘못된 실행계획을 방지하는 방법

- 힌트 /*+ cursor_sharing_exact */ 를 이용한다

- 자동으로 바인드 변수로 변경하지 않고 리터럴 SQL로 수행한다

SQL> select /*+ cursor_sharing_exact */ count(*) from emp100 where ename='scott1';      COUNT(*) ----------          1 SQL> select sql_text from v$sql                                                        where sql_text like '%emp100%';    SQL_TEXT -------------------------------------------------------------------------- select /*+ cursor_sharing_exact */ count(*) from emp100 where ename='scott1' - 위 SQL만 리터럴로 수행되는 것을 확인할 수 있다