DEFAULT 값 : EXACT
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
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
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)
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
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