2. Cursor_Sharing

조회 수 6332 추천 수 0 2013.08.21 12:34:19
balto *.252.219.183

- 바인드 변수 사용 원칙을 지키지 않아 라이브러리 캐시 경합이 일어날 경우 경합을 일시적으로 해결하기 위하여 cursor_sharing 파라메터를 변경할 수 있다.


예제

- Dynamic SQL을 만들고 Literal 상수를 제공했다. -> 커서 공유를 하지 않는다.

alter session set cursor_sharing = exact; (DEFAULT)

- alter session set cursor_sharing = force;를 사용하면 커서를 공유한다.

- force 이면 컬럼 히스토그램을 사용하지 못하므로 고정된 실행계획을 사용한다.

- force 단점을 해결하기 위해 similar 옵션을 사용할 수 있다. 히스토그램이 없으면 force와 같고, 있으면 exact와 같다.

  값 분포가 일정하지 않는 컬럼은 히스토그램을 만든다.

  히스토그램의 값의 경우 수가 많을 경우 EXACT 보다 더 느릴 수 있다.

create table emp_t

as select * from emp;

create index emp_empno_idx on emp_t(empno);


analyze table emp_t compute statistics

for table for all indexes for columns empno size 1;

show parameters cursor_sharing;

alter session set cursor_sharing = force;

alter system flush shared_pool;

set serveroutput on;


declare

   l_condition varchar2(20);

   l_ename emp_t.ename%type;

  begin

   for c in ( select empno from emp_t )

   loop

     l_condition := 'empno = '|| c.empno;

     execute immediate 'select ename ' || 'from emp_t where ' || l_condition

          into l_ename;

     dbms_output.put_line( l_condition ||' : ' || l_ename );

   end loop;

end;

/

select sql_id, sql_text, version_count

from v$sqlarea

where sql_text like 'select ename%';


select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches

from v$sql

where sql_text like 'select ename%';

SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES

------------- ---------- ---------- ---------- ----------- ---------- ----------

6y7xpur0d4jty 0 3220957464 1 12 12 12


(alter session set cursor_sharing = force;를 사용하지 않으면 아래 결과가 나온다.)

SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, pars

fetches

2 from v$sql

3 where sql_text like 'select ename%';

SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES

------------- ---------- ---------- ---------- ----------- ---------- ----------

7btg75m1un6mh 0 3220957464 1 1 1 1

4z85t036cstxu 0 3220957464 1 1 1 1

8t1hstds8svrg 0 3220957464 1 1 1 1

1p0dw95jhp6f1 0 3220957464 1 1 1 1

4k5uyn3kq1jkf 0 3220957464 1 1 1 1

1qsw3fn3j5pqr 0 3220957464 1 1 1 1

2kwq0tcdfa9b4 0 3220957464 1 1 1 1

9a3p6t37v2q3d 0 3220957464 1 1 1 1

2xauzardzur0f 0 3220957464 1 1 1 1

72127hc1t6zdw 0 3220957464 1 1 1 1

d4sqtxgazkzns 0 3220957464 1 1 1 1

9mjc6sbjubgsc 0 3220957464 1 1 1 1


- cursor_sharing은 일시적으로 바인드변수에 대한 하드파싱 문제를 해결할 수 있지만 완전한 해결책은 아니다.