1.   emp 테이블을 새로 만듬

SQL> create table emp
  2  as
  3  select * from scott.emp;

SQL> create index emp_idx01 on emp(empno);

인덱스가 생성되었습니다.

2 .   통계하는 히스토그램이 생성 되지 않게끔 수집

SQL> analyze table emp compute statistics
  2  for table for all indexes for columns empno size 1;

>>   SQL문이 100% 같을 대만 커서를 공유함

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

>>  다른 문자열은 모두 같고 일부  Literal 값만 다를때 그 값들을 시스템이 자동 생성한

      바인드 변수로 대체함으로써 공유가능한 SQL 커서로 만듬

SQL> alter session set cursor_sharing = FORCE;

세션이 변경되었습니다.

SQL> alter system flush shared_pool;

SQL> declare
  2 
  3     l_condition varchar2(20);
  4 
  5     l_ename emp.ename%type;
  6 
  7    begin
  8 
  9     for c in ( select empno from emp )
 10 
 11     loop
 12 
 13       l_condition := 'empno = '|| c.empno;
 14 
 15       execute immediate 'select ename ' || 'from emp where ' || l_condition
 16 
 17            into l_ename;
 18 
 19       dbms_output.put_line( l_condition ||' : ' || l_ename );
 20 
 21     end loop;
 22 
 23  end;
 24 
 25  /

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
7v2nh2sv9hf80     select ename from emp where empno = :"SYS_B_0"

  PL/SQL 에서 루프를 돌면서 Dynamic SQL을 이용해  Literal 상수를 제공하며 조회했는데

 V$SQL을 조회하면 바인드 변수를 사용했을때처럼 공유커서가 반복 사용 됨

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_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
7v2nh2sv9hf80          0 3956160932          1          14         14         14

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> alter session set cursor_sharing = 'SIMILAR';

세션이 변경되었습니다.

SQL> declare
  2 
  3     l_condition varchar2(20);
  4 
  5     l_ename emp.ename%type;
  6 
  7    begin
  8 
  9     for c in ( select empno from emp )
 10 
 11     loop
 12 
 13       l_condition := 'empno = '|| c.empno;
 14 
 15       execute immediate 'select ename ' || 'from emp where ' || l_condition
 16 
 17            into l_ename;
 18 
 19       dbms_output.put_line( l_condition ||' : ' || l_ename );
 20 
 21     end loop;
 22 
 23  end;
 24 
 25  /

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
7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"

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_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
7v2nh2sv9hf80          0 3956160932          1          14         14         14

>> SIMILAR로 설정했는데도 Literal 값에 따라 CHILD 커서를 생성하지 않았고 FORCE일때와 똑같이 하나의

     공유 커서를 사용함, empno 칼럼에 히스토그램이 없기 때문이며 , SIMILAR로 설정해도 FORCE와 같은 방식으로 작동

SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
7v2nh2sv9hf80          0 3956160932          1          14         14         14

SQL> insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  select empno, ename, job, mgr, hiredate, sal, comm, deptno
  3  from emp, (select * from dual connect by level <=  9999)
  4  where emp.empno = '7788';

9999 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> analyze table emp compute statistics
  2  for table for all indexes for columns empno size 8;

테이블이 분석되었습니다.

SQL> set autotrace traceonly exp
SQL> select ename from emp where empno = '7788';

>> TABLE을 FULL SCAN함

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  8761 | 87610 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |  8761 | 87610 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

>> SIMILAR 옵션을 줌

 SQL> delete from emp where empno = '7788' and rownum <= 9999;

9999 행이 삭제되었습니다.

SQL>   declare
  2      l_condition varchar2(20);
  3      l_ename emp.ename%type;
  4      begin
  5       for c in ( select empno from emp )
  6      loop
  7         l_condition := 'empno = '|| c.empno;
  8         execute immediate 'select ename ' || 'from emp where ' || l_condition
  9             into l_ename;
 10        dbms_output.put_line( l_condition ||' : ' || l_ename );
 11      end loop;
 12   end;
 13 
 14 
 15 
 16  /

PL/SQL 처리가 정상적으로 완료되었습니다.

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
-------------
7v2nh2sv9hf80
select ename from emp where empno = :"SYS_B_0"
           14

7v2nh2sv9hf80
select ename from emp where empno = :"SYS_B_0"
           14

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------

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_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
7v2nh2sv9hf80          0 3295026711          1           1          1          1
7v2nh2sv9hf80          1 3295026711          1           1          1          1
7v2nh2sv9hf80          2 3295026711          1           1          1          1
7v2nh2sv9hf80          3 3295026711          1           1          1          1
7v2nh2sv9hf80          4 3295026711          1           1          1          1
7v2nh2sv9hf80          5 3295026711          1           1          1          1
7v2nh2sv9hf80          6 3295026711          1           1          1          1
7v2nh2sv9hf80          7 3295026711          1           1          1          1
7v2nh2sv9hf80          8 3295026711          1           1          1          1
7v2nh2sv9hf80          9 3295026711          1           1          1          1
7v2nh2sv9hf80         10 3295026711          1           1          1          1

SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES
------------- ---------- ---------- ---------- ----------- ---------- ----------
7v2nh2sv9hf80         11 3295026711          1           1          1          1
7v2nh2sv9hf80         12 3295026711          1           1          1          1
7v2nh2sv9hf80         13 3956160932          1           1          1          1

>> 입력된 Literal 값별로 Child 커서를 생성했고, empno = '7788' 일때는 실행계획까지 달라짐

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 system  flush shared_pool;

SQL> alter session set cursor_sharing = 'SIMILAR';

세션이 변경되었습니다.

SQL> set timing on
SQL> declare
  2  l_cnt number;
  3  begin
  4  for i in 1..1000
  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:00.04

--> 아주 빠르게 처리함, V&SQL을 조회해 보지 않더라고 하나의 공유 커서가 사용됐음을 짐작

>> 히스토그램 생성

 
PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.04
SQL> analyze table t compute statistics
  2  for table for all indexes for all columns size 100;

테이블이 분석되었습니다.

경   과: 00:00:00.10
SQL> alteer system flussh shared_pool;

경   과: 00:00:00.00
SQL> declare
  2  l_cnt number;
  3  begin
  4  for i in 1 .. 10000
  5  loop
  6  execute immediate
  7 
  8  'select /*similar */ count(*) from t where no = '||i
  9  into l_cnt;
 10  end loop;
 11  end;
 12  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:09.71

--> 수행시간이 급격히 저하, 히스토그램을 생성했으므로 각 입려값별로 child 커서를 만들어야 하고

아래처럼 하드파싱 할 때보다 더 나쁜 결과를 초래함

 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.92

>> 바인드 변수를 사용함

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.18