h1.Cursor_Sharing
SQL문이 재파싱되지 않기 위해서는 바인드 변수 사용 원칙을 잘지켜 옵티마이저가 동일한 SQL로 인식하여 SHARE하게 사용하여야 하지만
바인드 변수 사용 원칙을 잘 지키지 않아 쿼리문이 실행될 때마다 변하게 되는 상수값으로 인해 재파싱이 되어 시스템 정상 가동이 어려운 경우 라이브러리 캐시 경합을 일시적으로 해결하기 위해
Cursor_sharing 파라미터를 변경하여 내부적으로 리터럴값을 바인드변수화하여 처리되게하는 기능을 한다.
1.CURSOR_SHARING = EXACT *DEFAULT 값*
2개의 문장에 사용된 SQL문이 모두 동일한 조건을 가져야만 파싱 정보를 공유할 수 있습니다.
WHERE 조건에 정의된 상수가 동일해야 합니다.
가. select * from dept where deptno = 30;
나. select * from dept where deptno = 40;
==> 실행된 2개의 문장에 대한 파싱 정보가 각각 생성됩니다.
2.CURSOR_SHARING = SIMILAR
2개의 문장에 사용된 SQL문이 모두 동일해야 파싱 정보를 공유할 수 있습니다.
리터럴 값을 Bind로 치환하며, 리터럴 값에 따라 컬럼 히스토그램을 사용하여 실행계획을 세우게 되어 동일한 SQL이라고 하여도
여러개의 실행계획을 세울수 있다.
가.select * from dept where deptno = 10;
나.select * from dept where deptno = 12;
- 실행된 2개의 문장에 대한 파싱 정보가 리터럴값에 따라 한개 또는 두개의 실행계획을 세울수 있음.
3.CURSOR_SHARING = FORCE
2개의 문장에 사용된 SQL문이 모두 동일해야 파싱 정보를 공유할 수 있습니다.
리터럴 값을 Bind로 치환하여 , 단일 실행계획만을 세우게된다.
이때 해당 쿼리문이 첫 수행되는 쿼리문의 실행계획을 세우게 된다.
가.select * from dept where deptno = 10;
나.select * from dept where deptno = 12;
- 실행된 2개의 문장에 대한 파싱 정보가 하나만 생성됩니다.
h3.1) Cursor_Sharing 수행 예제 - Similar
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> alter session set cursor_sharing = similar;
세션이 변경되었습니다.
SQL> declare
2 l_condition varchar2(20);
3 l_ename emp_t.ename%type;
4 begin
5 for c in ( select empno from emp_t )
6 loop
7 l_condition := 'empno = '|| c.empno;
8 execute immediate 'select ename ' || 'from emp_t where ' || l_condition
9 into l_ename;
10 dbms_output.put_line( l_condition ||' : ' || l_ename );
11 end loop;
12 end;
13 /
empno = 7329 : SMITH
- 중 략 -
empno = 7934 : MILLER
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select ename%'
SQL> /
SQL_ID SQL_TEXT VERSION_COUNT
------------- -------------------------------------------------- -------------
6y7xpur0d4jty select ename from emp_t where empno = :"SYS_B_0" 1
SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches
2 from v$sql
3 where sql_text like 'select ename%';
4 /
SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
6y7xpur0d4jty 0 2443308290 1 14 14 14
h3.2) Cursor_Sharing 수행 예제 - Force
SQL> create table emp_t
2 as
3 select * from emp
SQL> /
테이블이 생성되었습니다.
SQL> create index emp_empno_idx on emp_t(empno)
SQL> /
인덱스가 생성되었습니다.
SQL> analyze table emp_t compute statistics
2 for table for all indexes for columns empno size 1
통계정보 : 히스토그램이 생성되지 않게 수집
SQL> /
테이블이 분석되었습니다.
SQL> alter session set cursor_sharing = force;
세션이 변경되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> set serveroutput on;
SQL> declare
2 l_condition varchar2(20);
3 l_ename emp_t.ename%type;
4 begin
5 for c in ( select empno from emp_t )
6 loop
7 l_condition := 'empno = '|| c.empno;
8 execute immediate 'select ename ' || 'from emp_t where ' || l_condition
9 into l_ename;
10 dbms_output.put_line( l_condition ||' : ' || l_ename );
11 end loop;
12 end;
13 /
empno = 7329 : SMITH
empno = 7499 : ALLEN
- 중 략 -
empno = 7902 : FORD
empno = 7934 : MILLER
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select ename%'
SQL> /
SQL_ID SQL_TEXT VERSION_COUNT
------------- -------------------------------------------------- -------------
6y7xpur0d4jty select ename from emp_t where empno = :"SYS_B_0" 1
SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches
2 from v$sql
3 where sql_text like 'select ename%'
SQL> /
SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
6y7xpur0d4jty 0 2443308290 1 14 14 14
- 이때 Simlar로 설정하였는데도 Literal 값에 따라 Child 커서를 생성하지 않고 Force와 똑같이 하나의 방식으로 작동하였는데
이는 empno컬럼에 히스토그램이 없기 때문이다. 다르게 작동하도록 히스토그램을 만들어 수행해보자
- 리터럴값에따라 다르게 작독하도록 특정값을 많이 입력(7788)
SQL> insert into emp_t( empno, ename, job, mgr, hiredate, sal, comm, deptno )
2 select empno, ename, job, mgr, hiredate, sal, comm, deptno
3 from emp_t, ( select * from dual connect by level <= 9999 )
4 where emp_t.empno = 7788
5 /
9999 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> analyze table emp_t compute statistics
2 for table for all indexes for columns empno size 8
통계정보 : 히스토그램 생성
SQL> set autotrace traceonly exp;
SQL> select ename from emp_t where empno = 7788;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=9)
1 0 TABLE ACCESS (FULL) OF 'EMP_T' (TABLE) (Cost=4 Card=1 Bytes= 9)
- 히스토그램 생성으로 인해 옵티마이저가 정보를 얻어 7788의값으로 조회시 Full Table을 타는것을 볼수 있다.
- 수행속도와 Fetch에러를 방지하기 위해 원래 건수대로 1건만 남기고 모두 del
어차피 통계정보는 10000건이 있는 걸로 인식하고 있기때문에 지워도 원하는 실행계획을 뽑을수 있음.
SQL> delete from emp_t where empno = 7788 and rownum <= 9999;
9999 행이 삭제되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 l_condition varchar2(20);
3 l_ename emp_t.ename%type;
4 begin
5 for c in ( select empno from emp_t )
6 loop
7 l_condition := 'empno = '|| c.empno;
8 execute immediate 'select ename ' || 'from emp_t where ' || l_condition
9 into l_ename;
10 dbms_output.put_line( l_condition ||' : ' || l_ename );
11 end loop;
12 end;
13 /
empno = 7329 : SMITH
- 중 략 -
empno = 7788 : SCOTT
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select ename%'
SQL_ID SQL_TEXT VERSION_COUNT
------------- ----------------------------------------------------- -------------
6y7xpur0d4jty select ename from emp_t where empno = :"SYS_B_0" 14
SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches
2 from v$sql
3 where sql_text like 'select ename%'
SQL> /
SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
6y7xpur0d4jty 0 2617457611 1 1 1 1
6y7xpur0d4jty 1 2617457611 1 1 1 1
6y7xpur0d4jty 2 2617457611 1 1 1 1
6y7xpur0d4jty 3 2617457611 1 1 1 1
6y7xpur0d4jty 4 2617457611 1 1 1 1
6y7xpur0d4jty 5 2617457611 1 1 1 1
6y7xpur0d4jty 6 2617457611 1 1 1 1
6y7xpur0d4jty 7 2617457611 1 1 1 1
6y7xpur0d4jty 8 2617457611 1 1 1 1
6y7xpur0d4jty 9 2617457611 1 1 1 1
6y7xpur0d4jty 10 2617457611 1 1 1 1
6y7xpur0d4jty 11 2617457611 1 1 1 1
6y7xpur0d4jty 12 2617457611 1 1 1 1
6y7xpur0d4jty 13 2443308290 1 1 1 1
14 개의 행이 선택되었습니다.
*히스토그램을 생성하지 않았을 때*
SQL> drop table t purge;
테이블이 삭제되었습니다.
경 과: 00:00:01.95
SQL> create table t
2 nologging
3 as
4 select rownum no from all_objects
5 where rownum < 10000;
테이블이 생성되었습니다.
SQL> create index t_idx on t(no);
인덱스가 생성되었습니다.
SQL> analyze table t compute statistics
2 for table for all indexes for all columns size 1;
테이블이 분석되었습니다.
SQL> alter session set cursor_sharing = similar;
세션이 변경되었습니다.
SQL> set timing on
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..10000
5 loop
6 execute immediate
7 'select /* similar */ count(*) from t where no = ' || i
8 into l_cnt;
9 end loop;
10 end;
11 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:01.03
-히스토그램을 많드지 않았으므로 매우 빨리 처리가 되는것을 볼수있다.
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select /* similar%'
4 /
SQL_ID SQL_TEXT VERSION_COUNT
-------------- ------------------------------------------------------------- -------------
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1
*히스토그램을 생성하였을 때*
SQL> analyze table t compute statistics
2 for table for all indexes for all columns size 100;
통계수집 : 히스토그램 생성
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..10000
5 loop
6 execute immediate
7 'select /* similar */ count(*) from t where no = ' || i
8 into l_cnt;
9 end loop;
10* end;
SQL> /
PL/SQL 처리가 정상적으로 완료되었습니다.
히스토 그램을 생성했으므로 각 입력값별로 CHILD커서를 만들어야 했고, 이것은 아래 처럼 매번 하드파싱할 때 보다 더 나쁜 결과를 초래한다.
경 과: 00:00:09.09
SQL>
SQL> select sql_id, sql_text, version_count
2 from v$sqlarea
3 where sql_text like 'select /* similar%'
4 /
SQL_ID SQL_TEXT VERSION_COUNT
------------- -------------------------------------------------------------------------------- -------------
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 766
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1026
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1026
9a5hvy0j9y82q select /* similar */ count(*) from t where no = :"SYS_B_0" 1026
SQL> alter session set cursor_sharing = exact;
세션이 변경되었습니다.
##하드파싱
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'select /* exact */ count(*) from t where no = ' || i
8 into l_cnt;
9 end loop;
10 end;
11 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:06.89
SQL>
SQL> select count(*)
2 from v$sqlarea
3 where sql_text like 'select /* exact%'
SQL> /
COUNT(*)
----------
4852
* 정상적으로 BIND변수를 사용하였을때 *
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
경 과: 00:00:00.34
SQL> declare
2 l_cnt number;
3 begin
4 for i in 1..10000
5 loop
6 execute immediate
7 'select /* bind */ count(*) from t where no = :no '
8 into l_cnt
9 using i;
10 end loop;
11 end;
12 /
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.43
SQL> ed
file afiedt.buf(이)가 기록되었습니다
1 select sql_id, sql_text, version_count
2 from v$sqlarea
3* where sql_text like 'select /* bind%'
SQL> /
SQL_ID SQL_TEXT VERSION_COUNT
------------- ------------------------------------------------------------------------ -------------
ctu0jcy938aqg select /* bind */ count(*) from t where no = :no 1