2._Cursor_Sharing

조회 수 5067 추천 수 0 2014.09.11 15:57:33
운영자 *.73.93.2

 바인드 변수 사용 원칙을 잘 지키지 않아 시스템 정상 가동이 어려운 경우가 있음

--> 라이브러리 캐시 경합을 일시적으로 해결하기 위해  cursor_sharing  파라미터를 변경하는것을 고려할수 있음

 


 

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

 

 

  

 .  FORCE로 설정시

        - 어떤 값으로 싫행하든 항상 같은 실행계획을 사용

        - EXACT 일 때보다 라이브러리 캐시 부하는 상당히 줄겠지만 컬럼 히스토그램을 사용하지 못하는 문제 때문에

         성능이 더 나빠질 가능성

        -> 어떤 값은 분포가 90% 이고 다른 값들은 대개 1% 정도 분포를 보일때 FORCE를 사용하면 항상

            고정된  실행계획을 사용함으로써 EXACT일 때보다 개별 쿼리 성능은 더 나빠질수 있음

 

 

>>  위의 단점을 회피할 목적으로 SIMILAR를 설정, 실행되는 Literal 값에 따라 별도의 커서를 생성함으로써

      다른 실행계획을 사용할수 있게 함

 

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

 

 

 

>> 입력되는 Literal값에 따라 다른 실행계획이 생성되는지 확인하기 위해 많이 입력함

 

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' 일때는 실행계획까지 달라짐

 

 

 

 

>> SIMILAR 일때, 히스토그램을 생성해 둔 컬럼에 값의 종류가 아주 많으면 어떤일이 발생하는지 테스트

 

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


 

 

 

 

--> 과도한 shared pool과 라이브러리 캐시 래치 경합 을 줄이는데 효과는 있지만, 기존 실행계획이 틀어져

이전보다 더 느리게 수행되는 쿼리들이 속출하게 될 가능성이 높음