2._Cursor_Sharing

조회 수 5210 추천 수 0 2014.09.11 15:57:33
운영자 *.73.93.2
제목 작성자 날짜
2._Cursor_Sharing (current) 운영자 2014.09.11 15:57:33
복원
<p> ë°ì¸ë ë³ì ì¬ì© ìì¹ì ì ì§í¤ì§ ìì ìì¤í ì ì ê°ëì´ ì´ë ¤ì´ ê²½ì°ê° ìì</p>
<p>--&gt; ë¼ì´ë¸ë¬ë¦¬ ìºì ê²½í©ì ì¼ìì ì¼ë¡ í´ê²°í기 ìí´  cursor_sharing  íë¼ë¯¸í°ë¥¼ ë³ê²½íëê²ì ê³ ë ¤í ì ìì</p>
<p> </p>
<p><br />  </p>
<table style="width:673px;background:#cccccc;height:1334px;" cellspacing="1"><tbody><tr style="background:#ffffff;"><td>
<p>1.   emp íì´ë¸ì ìë¡ ë§ë¬</p>
<p><br />SQL&gt; create table emp<br />  2  as<br />  3  select * from scott.emp;</p>
<p> </p>
<p>SQL&gt; create index emp_idx01 on emp(empno);</p>
<p>ì¸ë±ì¤ê° ìì±ëììµëë¤.</p>
<p> </p>
<p><br />2 .   íµê³íë íì¤í ê·¸ë¨ì´ ìì± ëì§ ìê²ë ìì§</p>
<p> </p>
<p>SQL&gt; analyze table emp compute statistics<br />  2  for table for all indexes for columns empno size 1;</p>
<p> </p>
<p>&gt;&gt;   SQLë¬¸ì´ 100% ê°ì ëë§ ì»¤ì를 ê³µì í¨</p>
<p> </p>
<p>SQL&gt; show parameter cursor_sharing</p>
<p>NAME                                 TYPE        VALUE<br />------------------------------------ ----------- ------------------------------<br />cursor_sharing                       string      EXACT</p>
<p> </p>
<p> </p>
<p>&gt;&gt;  ë¤ë¥¸ 문ìì´ì 모ë ê°ê³  ì¼ë¶  Literal ê°ë§ ë¤ë¥¼ë ê·¸ ê°ë¤ì ìì¤íì´ ìë ìì±í</p>
<p>      <strong>ë°ì¸ë ë³ìë¡ ëì²´í¨ì¼ë¡ì¨ ê³µì ê°ë¥í SQL 커ìë¡</strong> ë§ë¬</p>
<p><br />SQL&gt; alter session set cursor_sharing = FORCE;</p>
<p>ì¸ìì´ ë³ê²½ëììµëë¤.</p>
<p>SQL&gt; alter system flush shared_pool;</p>
<p><br />SQL&gt; declare<br />  2  <br />  3     l_condition varchar2(20);<br />  4  <br />  5     l_ename emp.ename%type;<br />  6  <br />  7    begin<br />  8  <br />  9     for c in ( select empno from emp )<br /> 10  <br /> 11     loop<br /> 12  <br /> 13       l_condition := 'empno = '|| c.empno;<br /> 14  <br /> 15       execute immediate 'select ename ' || 'from emp where ' || l_condition<br /> 16  <br /> 17            into l_ename;<br /> 18  <br /> 19       dbms_output.put_line( l_condition ||' : ' || l_ename );<br /> 20  <br /> 21     end loop;<br /> 22  <br /> 23  end;<br /> 24  <br /> 25  /</p>
<p> </p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />  2  from v$sqlarea<br />  3  where sql_text like 'select ename%';</p>
<p> </p>
<p>SQL_ID                 SQL_TEXT                                                                        VERSION_COUNT<br />7v2nh2sv9hf80     select ename from emp where empno = :"SYS_B_0"</p>
<p> </p>
<p>  </p>
<p>  PL/SQL ìì 루í를 ëë©´ì Dynamic SQLì ì´ì©í´  Literal ìì를 ì ê³µíë©° ì¡°ííëë°</p>
<p> V$SQLì ì¡°ííë©´ ë°ì¸ë ë³ì를 ì¬ì©íìëì²ë¼<strong> ê³µì ì»¤ìê° ë°ë³µ ì¬ì© ë¨</strong></p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br /> fetches<br />  2  from v$sql<br />  3  where sql_text like 'select ename%';</p>
<p>SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80          0 3956160932          1          14         14         14</p></td></tr></tbody></table><p></p>
<p> </p>
<p> </p>
<p>  </p>
<p> .  FORCEë¡ ì¤ì ì</p>
<p>        - ì´ë¤ ê°ì¼ë¡ ì«ííë  í­ì ê°ì ì¤íê³íì ì¬ì©</p>
<p>        - EXACT ì¼ ëë³´ë¤ ë¼ì´ë¸ë¬ë¦¬ ìºì ë¶íë ìë¹í ì¤ê² ì§ë§ ì»¬ë¼ íì¤í ê·¸ë¨ì ì¬ì©íì§ ëª»íë ë¬¸ì  ë문ì</p>
<p>         ì±ë¥ì´ ë ëë¹ ì§ ê°ë¥ì±</p>
<p>        -&gt; ì´ë¤ ê°ì ë¶í¬ê° 90% ì´ê³  ë¤ë¥¸ ê°ë¤ì ëê° 1% ì ë ë¶í¬ë¥¼ ë³´ì¼ë FORCE를 ì¬ì©íë©´ í­ì</p>
<p>            ê³ ì ë  ì¤íê³íì ì¬ì©í¨ì¼ë¡ì¨ EXACTì¼ ëë³´ë¤ ê°ë³ 쿼리 ì±ë¥ì ë ëë¹ ì§ì ìì</p>
<p> </p>
<p> </p>
<p>&gt;&gt;  ìì ë¨ì ì íí¼í  목ì ì¼ë¡ SIMILAR를 ì¤ì , ì¤íëë Literal ê°ì ë°ë¼ ë³ëì 커ì를 ìì±í¨ì¼ë¡ì¨</p>
<p>      ë¤ë¥¸ ì¤íê³íì ì¬ì©í ì ìê² í¨</p>
<p>  </p>
<table style="width:671px;background:#cccccc;height:1029px;" cellspacing="1"><tbody><tr style="background:#ffffff;"><td>
<p>SQL&gt; alter system flush shared_pool;</p>
<p>ìì¤íì´ ë³ê²½ëììµëë¤.</p>
<p>SQL&gt; alter session set cursor_sharing = 'SIMILAR';</p>
<p>ì¸ìì´ ë³ê²½ëììµëë¤.</p>
<p> </p>
<p><br />SQL&gt; declare<br />  2  <br />  3     l_condition varchar2(20);<br />  4  <br />  5     l_ename emp.ename%type;<br />  6  <br />  7    begin<br />  8  <br />  9     for c in ( select empno from emp )<br /> 10  <br /> 11     loop<br /> 12  <br /> 13       l_condition := 'empno = '|| c.empno;<br /> 14  <br /> 15       execute immediate 'select ename ' || 'from emp where ' || l_condition<br /> 16  <br /> 17            into l_ename;<br /> 18  <br /> 19       dbms_output.put_line( l_condition ||' : ' || l_ename );<br /> 20  <br /> 21     end loop;<br /> 22  <br /> 23  end;<br /> 24  <br /> 25  /</p>
<p> </p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />  2  from v$sqlarea<br />  3  where sql_text like 'select ename%';</p>
<p> </p>
<p>SQL_ID  SQL_TEXT   VERSION_COUNT<br />7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"</p>
<p> </p>
<p><br /> </p>
<p>SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br /> fetches<br />  2  from v$sql<br />  3  where sql_text like 'select ename%';</p>
<p>SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80          0 3956160932          1          14         14         14<br /></p>
<p>&gt;&gt; SIMILARë¡ ì¤ì íëë°ë Literal ê°ì ë°ë¼ CHILD 커ì를 ìì±íì§ ììê³  <strong>FORCEì¼ëì ëê°ì´ íëì</strong></p>
<p><strong>     ê³µì  커ì를 ì¬ì©í¨</strong>, empno ì¹¼ë¼ì íì¤í ê·¸ë¨ì´ ì기 ë문ì´ë©° , SIMILARë¡ ì¤ì í´ë FORCEì ê°ì ë°©ìì¼ë¡ ìë</p>
<p> </p>
<p> </p>
<p><br />SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80          0 3956160932          1          14         14         14</p></td></tr></tbody></table><p></p>
<p> </p>
<p> </p>
<p> </p>
<p>&gt;&gt; ìë ¥ëë Literalê°ì ë°ë¼ ë¤ë¥¸ ì¤íê³íì´ ìì±ëëì§ íì¸í기 ìí´ ë§ì´ ìë ¥í¨</p>
<p>  </p>
<table style="width:695px;background:#cccccc;height:1794px;" cellspacing="1"><tbody><tr style="background:#ffffff;"><td>
<p>SQL&gt; insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)<br />  2  select empno, ename, job, mgr, hiredate, sal, comm, deptno<br />  3  from emp, (select * from dual connect by level &lt;=  9999)<br />  4  where emp.empno = '7788';</p>
<p>9999 ê°ì íì´ ë§ë¤ì´ì¡ìµëë¤.</p>
<p>SQL&gt; commit;</p>
<p>커ë°ì´ ìë£ëììµëë¤.</p>
<p> </p>
<p>SQL&gt; analyze table emp compute statistics<br /><strong>  2  for table for all indexes for columns empno size 8;</strong></p>
<p>íì´ë¸ì´ ë¶ìëììµëë¤.</p>
<p> </p>
<p>SQL&gt; set autotrace traceonly exp<br />SQL&gt; select ename from emp where empno = '7788';</p>
<p> </p>
<p>&gt;&gt; TABLEì FULL SCANí¨</p>
<p>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3956160932</p>
<p>--------------------------------------------------------------------------<br />| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |<br />--------------------------------------------------------------------------<br />|   0 | SELECT STATEMENT  |      |  8761 | 87610 |    15   (0)| 00:00:01 |<br />|*  1 |  TABLE ACCESS FULL| EMP  |  8761 | 87610 |    15   (0)| 00:00:01 |<br />--------------------------------------------------------------------------</p>
<p> </p>
<p> </p>
<p>&gt;&gt; SIMILAR ìµìì ì¤</p>
<p> </p>
<p> SQL&gt; delete from emp where empno = '7788' and rownum &lt;= 9999;</p>
<p>9999 íì´ ì­ì ëììµëë¤.</p>
<p> </p>
<p>SQL&gt;   declare<br />  2      l_condition varchar2(20);<br />  3      l_ename emp.ename%type;<br />  4      begin<br />  5       for c in ( select empno from emp )<br />  6      loop<br />  7         l_condition := 'empno = '|| c.empno;<br />  8         execute immediate 'select ename ' || 'from emp where ' || l_condition<br />  9             into l_ename;<br /> 10        dbms_output.put_line( l_condition ||' : ' || l_ename );<br /> 11      end loop;<br /> 12   end;<br /> 13  <br /> 14  <br /> 15  <br /> 16  /</p>
<p>PL/SQL ì²ë¦¬ê° ì ìì ì¼ë¡ ìë£ëììµëë¤.</p>
<p>SQL&gt; <br />SQL&gt; select sql_id, sql_text, version_count<br />  2  from v$sqlarea<br />  3  where sql_text like 'select ename%';</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br />7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />           14</p>
<p>7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />           14</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br /></p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br /> fetches<br />  2  from v$sql<br />  3  where sql_text like 'select ename%';</p>
<p> </p>
<p>SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80          0 3295026711          1           1          1          1<br />7v2nh2sv9hf80          1 3295026711          1           1          1          1<br />7v2nh2sv9hf80          2 3295026711          1           1          1          1<br />7v2nh2sv9hf80          3 3295026711          1           1          1          1<br />7v2nh2sv9hf80          4 3295026711          1           1          1          1<br />7v2nh2sv9hf80          5 3295026711          1           1          1          1<br />7v2nh2sv9hf80          6 3295026711          1           1          1          1<br />7v2nh2sv9hf80          7 3295026711          1           1          1          1<br />7v2nh2sv9hf80          8 3295026711          1           1          1          1<br />7v2nh2sv9hf80          9 3295026711          1           1          1          1<br />7v2nh2sv9hf80         10 3295026711          1           1          1          1</p>
<p>SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80         11 3295026711          1           1          1          1<br />7v2nh2sv9hf80         12 3295026711          1           1          1          1<br />7v2nh2sv9hf80         13 3956160932          1           1          1          1</p>
<p><br />&gt;&gt; ìë ¥ë Literal ê°ë³ë¡ Child 커ì를 ìì±íê³ , empno = '7788' ì¼ëë ì¤íê³íê¹ì§ ë¬ë¼ì§</p></td></tr></tbody></table><p></p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p>&gt;&gt; SIMILAR ì¼ë, íì¤í ê·¸ë¨ì ìì±í´ ë 컬ë¼ì ê°ì ì¢ë¥ê° ì주 ë§ì¼ë©´ ì´ë¤ì¼ì´ ë°ìíëì§ íì¤í¸</p>
<p> 
</p><table style="width:686px;background:#cccccc;height:559px;" cellspacing="1"><tbody><tr style="background:#ffffff;"><td>
<p>SQL&gt;  create table t<br />  2   nologging<br />  3   as<br />  4  select rownum no from all_objects<br />  5  where rownum &lt;= 10000;</p>
<p> </p>
<p>SQL&gt; create index t_idx on t( no);</p>
<p> </p>
<p>SQL&gt; analyze table t compute statistics<br /><strong>  2  for table for all indexes for all columns size 1;</strong></p>
<p> </p>
<p>SQL&gt; alter system  flush shared_pool;</p>
<p><strong></strong> </p>
<p><strong>SQL&gt; alter session set cursor_sharing = 'SIMILAR';</strong></p>
<p>ì¸ìì´ ë³ê²½ëììµëë¤.</p>
<p>SQL&gt; set timing on<br />SQL&gt; declare<br />  2  l_cnt number;<br />  3  begin<br />  4  for i in 1..1000<br />  5  loop<br />  6  execute immediate<br />  7  'select /*similar*/ count(*) from t where no = '|| i<br />  8  into l_cnt;<br />  9  end loop;<br /> 10  end;<br /> 11  /</p>
<p>PL/SQL ì²ë¦¬ê° ì ìì ì¼ë¡ ìë£ëììµëë¤.</p>
<p>ê²½   ê³¼: 00:00:00.04</p>
<p> </p>
<p>--&gt; ì주 ë¹ ë¥´ê² ì²ë¦¬í¨, V&amp;SQLì ì¡°íí´ ë³´ì§ ìëë¼ê³  íëì ê³µì  ì»¤ìê° ì¬ì©ëìì ì§ì</p>
<p> </p>
<p> </p>
<p>&gt;&gt; íì¤í ê·¸ë¨ ìì±</p>
<p> <br />PL/SQL ì²ë¦¬ê° ì ìì ì¼ë¡ ìë£ëììµëë¤.</p>
<p>ê²½   ê³¼: 00:00:00.04<br />SQL&gt; analyze table t compute statistics<br /><strong>  2  for table for all indexes for all columns size 100;</strong></p>
<p>íì´ë¸ì´ ë¶ìëììµëë¤.</p>
<p>ê²½   ê³¼: 00:00:00.10<br />SQL&gt; alteer system flussh shared_pool;<br /><br />ê²½   ê³¼: 00:00:00.00<br />SQL&gt; declare<br />  2  l_cnt number;<br />  3  begin<br />  4  for i in 1 .. 10000<br />  5  loop<br />  6  execute immediate<br />  7  <br />  8  'select /*similar */ count(*) from t where no = '||i<br />  9  into l_cnt;<br /> 10  end loop;<br /> 11  end;<br /> 12  /</p>
<p>PL/SQL ì²ë¦¬ê° ì ìì ì¼ë¡ ìë£ëììµëë¤.</p>
<p>ê²½   ê³¼: 00:00:09.71</p>
<p>--&gt; ìíìê°ì´ ê¸ê²©í ì í, íì¤í ê·¸ë¨ì ìì±íì¼ë¯ë¡ ê° ìë ¤ê°ë³ë¡ child 커ì를 ë§ë¤ì´ì¼ íê³ </p>
<p>ìëì²ë¼ íëíì± í  ëë³´ë¤ ë ëì 결과를 ì´ëí¨</p>
<p> </p>
<p> </p>
<p> </p>
<p> SQL&gt;   declare<br />  2  l_cnt number;<br />  3    begin<br />  4    for i in 1..10000<br />  5    loop<br />  6    execute immediate<br />  7    'select /*exact*/ count(*) from t where no = '||i<br />  8    into l_cnt;<br />  9    end loop;<br /> 10    end;<br /> 11  /</p>
<p>PL/SQL ì²ë¦¬ê° ì ìì ì¼ë¡ ìë£ëììµëë¤.</p>
<p>ê²½   ê³¼: 00:00:06.92</p>
<p> </p>
<p>&gt;&gt; ë°ì¸ë ë³ì를 ì¬ì©í¨</p>
<p> </p>
<p>SQL&gt;   declare<br />  2  l_cnt number;<br />  3    begin<br />  4    for i in 1..10000<br />  5    loop<br />  6    execute immediate<br />  7    'select /*bind*/ count(*) from t where no =  :no'<br />  8    into l_cnt<br />  9  using i;<br /> 10    end loop;<br /> 11    end;<br /> 12  /</p>
<p>PL/SQL ì²ë¦¬ê° ì ìì ì¼ë¡ ìë£ëììµëë¤.</p>
<p>ê²½   ê³¼: 00:00:00.18</p></td></tr></tbody></table><p><br /> </p>
<p> </p>
<p> </p>
<p> </p>
<p>--&gt; ê³¼ëí shared poolê³¼ ë¼ì´ë¸ë¬ë¦¬ ìºì ëì¹ ê²½í© ì ì¤ì´ëë° í¨ê³¼ë ìì§ë§, 기존 ì¤íê³íì´ íì´ì ¸</p>
<p>ì´ì ë³´ë¤ ë ëë¦¬ê² ìíëë 쿼리ë¤ì´ ìì¶íê² ë  ê°ë¥ì±ì´ ëì</p>
<p> </p>
<p> </p>
<p> </p>
ikzblwtf 2014.09.10 22:15:02
복원
<p>&nbsp;바인드 변수 사용 원칙을 잘 지키지 않아 시스템 정상 가동이 어려운 경우가 있음</p>
<p>--&gt; 라이브러리 캐시 경합을 일시적으로 해결하기 위해&nbsp; cursor_sharing&nbsp; 파라미터를 변경하는것을 고려할수 있음</p>
<p>&nbsp;</p>
<p><br />&nbsp; </p>
<table style="WIDTH: 673px; BACKGROUND: #cccccc; HEIGHT: 1334px" cellspacing="1">
<tbody>
<tr style="BACKGROUND: #ffffff">
<td>
<p>1.&nbsp;&nbsp; emp 테이블을 새로 만듬</p>
<p><br />SQL&gt; create table emp<br />&nbsp; 2&nbsp; as<br />&nbsp; 3&nbsp; select * from scott.emp;</p>
<p>&nbsp;</p>
<p>SQL&gt; create index emp_idx01 on emp(empno);</p>
<p>인덱스가 생성되었습니다.</p>
<p>&nbsp;</p>
<p><br />2 . &nbsp;&nbsp;통계하는 히스토그램이 생성 되지 않게끔 수집</p>
<p>&nbsp;</p>
<p>SQL&gt; analyze table emp compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for columns empno size 1;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp;&nbsp; SQL문이 100% 같을 대만 커서를 공유함</p>
<p>&nbsp;</p>
<p>SQL&gt; show parameter cursor_sharing</p>
<p>NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUE<br />------------------------------------ ----------- ------------------------------<br />cursor_sharing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXACT</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp; 다른 문자열은 모두 같고 일부&nbsp; Literal 값만 다를때 그 값들을 시스템이 자동 생성한</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <strong>바인드 변수로 대체함으로써 공유가능한 SQL 커서로</strong> 만듬</p>
<p><br />SQL&gt; alter session set cursor_sharing = FORCE;</p>
<p>세션이 변경되었습니다.</p>
<p>SQL&gt; alter system flush shared_pool;</p>
<p><br />SQL&gt; declare<br />&nbsp; 2&nbsp; <br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 4&nbsp; <br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 6&nbsp; <br />&nbsp; 7&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 8&nbsp; <br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp;10&nbsp; <br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp;12&nbsp; <br />&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp;16&nbsp; <br />&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;18&nbsp; <br />&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;20&nbsp; <br />&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;22&nbsp; <br />&nbsp;23&nbsp; end;<br />&nbsp;24&nbsp; <br />&nbsp;25&nbsp; /</p>
<p>&nbsp;</p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL_TEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; VERSION_COUNT<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp; select ename from emp where empno = :"SYS_B_0"</p>
<p>&nbsp;</p>
<p>&nbsp;&nbsp;</p>
<p>&nbsp; PL/SQL 에서 루프를 돌면서 Dynamic SQL을 이용해&nbsp; Literal 상수를 제공하며 조회했는데</p>
<p>&nbsp;V$SQL을 조회하면 바인드 변수를 사용했을때처럼<strong> 공유커서가 반복 사용 됨</strong></p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p></td></tr></tbody></table>
<p></p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;&nbsp;</p>
<p>&nbsp;. &nbsp;FORCE로 설정시</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 어떤 값으로 싫행하든 항상 같은 실행계획을 사용</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;- EXACT 일 때보다 라이브러리 캐시 부하는 상당히 줄겠지만 컬럼 히스토그램을 사용하지 못하는 문제 때문에</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 성능이 더 나빠질 가능성</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt; 어떤 값은 분포가 90% 이고 다른 값들은 대개 1% 정도 분포를 보일때 FORCE를 사용하면 항상</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 고정된&nbsp; 실행계획을 사용함으로써 EXACT일 때보다 개별 쿼리 성능은 더 나빠질수 있음</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp; 위의 단점을 회피할 목적으로 SIMILAR를 설정, 실행되는 Literal 값에 따라 별도의 커서를 생성함으로써</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 다른 실행계획을 사용할수 있게 함</p>
<p>&nbsp; </p>
<table style="WIDTH: 671px; BACKGROUND: #cccccc; HEIGHT: 1029px" cellspacing="1">
<tbody>
<tr style="BACKGROUND: #ffffff">
<td>
<p>SQL&gt; alter system flush shared_pool;</p>
<p>시스템이 변경되었습니다.</p>
<p>SQL&gt; alter session set cursor_sharing = 'SIMILAR';</p>
<p>세션이 변경되었습니다.</p>
<p>&nbsp;</p>
<p><br />SQL&gt; declare<br />&nbsp; 2&nbsp; <br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 4&nbsp; <br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 6&nbsp; <br />&nbsp; 7&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 8&nbsp; <br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp;10&nbsp; <br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp;12&nbsp; <br />&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp;16&nbsp; <br />&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;18&nbsp; <br />&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;20&nbsp; <br />&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;22&nbsp; <br />&nbsp;23&nbsp; end;<br />&nbsp;24&nbsp; <br />&nbsp;25&nbsp; /</p>
<p>&nbsp;</p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp; SQL_TEXT&nbsp;&nbsp; VERSION_COUNT<br />7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14<br /></p>
<p>&gt;&gt;&nbsp;SIMILAR로 설정했는데도 Literal 값에 따라 CHILD 커서를 생성하지 않았고 <strong>FORCE일때와 똑같이 하나의</strong></p>
<p><strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;공유 커서를 사용함</strong>,&nbsp;empno 칼럼에 히스토그램이 없기 때문이며 ,&nbsp;SIMILAR로 설정해도 FORCE와 같은 방식으로 작동</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><br />SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p></td></tr></tbody></table>
<p></p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; 입력되는 Literal값에 따라 다른 실행계획이 생성되는지 확인하기 위해 많이 입력함</p>
<p>&nbsp; </p>
<table style="WIDTH: 695px; BACKGROUND: #cccccc; HEIGHT: 1794px" cellspacing="1">
<tbody>
<tr style="BACKGROUND: #ffffff">
<td>
<p>SQL&gt; insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)<br />&nbsp; 2&nbsp; select empno, ename, job, mgr, hiredate, sal, comm, deptno<br />&nbsp; 3&nbsp; from emp, (select * from dual connect by level &lt;=&nbsp; 9999)<br />&nbsp; 4&nbsp; where emp.empno = '7788';</p>
<p>9999 개의 행이 만들어졌습니다.</p>
<p>SQL&gt; commit;</p>
<p>커밋이 완료되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt; analyze table emp compute statistics<br /><strong>&nbsp; 2&nbsp; for table for all indexes for columns empno size 8;</strong></p>
<p>테이블이 분석되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt; set autotrace traceonly exp<br />SQL&gt; select ename from emp where empno = '7788';</p>
<p>&nbsp;</p>
<p>&gt;&gt; TABLE을 FULL SCAN함</p>
<p>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3956160932</p>
<p>--------------------------------------------------------------------------<br />| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br />--------------------------------------------------------------------------<br />|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 8761 | 87610 |&nbsp;&nbsp;&nbsp; 15&nbsp;&nbsp; (0)| 00:00:01 |<br />|*&nbsp; 1 |&nbsp; TABLE ACCESS FULL| EMP&nbsp; |&nbsp; 8761 | 87610 |&nbsp;&nbsp;&nbsp; 15&nbsp;&nbsp; (0)| 00:00:01 |<br />--------------------------------------------------------------------------</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; SIMILAR 옵션을 줌</p>
<p>&nbsp;</p>
<p>&nbsp;SQL&gt; delete from emp where empno = '7788' and rownum &lt;= 9999;</p>
<p>9999 행이 삭제되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt;&nbsp;&nbsp; declare<br />&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;12&nbsp;&nbsp; end;<br />&nbsp;13&nbsp; <br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp; <br />&nbsp;16&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>SQL&gt; <br />SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br />7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br /></p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</p>
<p><br />&gt;&gt; 입력된 Literal 값별로 Child 커서를 생성했고, empno = '7788' 일때는 실행계획까지 달라짐</p></td></tr></tbody></table>
<p></p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; SIMILAR 일때, 히스토그램을 생성해 둔 컬럼에 값의 종류가 아주 많으면 어떤일이 발생하는지 테스트</p>
<p>&nbsp;
<table style="WIDTH: 686px; BACKGROUND: #cccccc; HEIGHT: 559px" cellspacing="1">
<tbody>
<tr style="BACKGROUND: #ffffff">
<td>
<p>SQL&gt;&nbsp; create table t<br />&nbsp; 2&nbsp;&nbsp; nologging<br />&nbsp; 3&nbsp;&nbsp; as<br />&nbsp; 4&nbsp; select rownum no from all_objects<br />&nbsp; 5&nbsp; where rownum &lt;= 10000;</p>
<p>&nbsp;</p>
<p>SQL&gt; create index t_idx on t( no);</p>
<p>&nbsp;</p>
<p>SQL&gt; analyze table t compute statistics<br /><strong>&nbsp; 2&nbsp; for table for all indexes for all columns size 1;</strong></p>
<p>&nbsp;</p>
<p>SQL&gt; alter system&nbsp; flush shared_pool;</p>
<p><strong></strong>&nbsp;</p>
<p><strong>SQL&gt; alter session set cursor_sharing = 'SIMILAR';</strong></p>
<p>세션이 변경되었습니다.</p>
<p>SQL&gt; set timing on<br />SQL&gt; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp; begin<br />&nbsp; 4&nbsp; for i in 1..1000<br />&nbsp; 5&nbsp; loop<br />&nbsp; 6&nbsp; execute immediate<br />&nbsp; 7&nbsp; 'select /*similar*/ count(*) from t where no = '|| i<br />&nbsp; 8&nbsp; into l_cnt;<br />&nbsp; 9&nbsp; end loop;<br />&nbsp;10&nbsp; end;<br />&nbsp;11&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.04</p>
<p>&nbsp;</p>
<p>--&gt; 아주 빠르게 처리함, V&amp;SQL을 조회해 보지 않더라고 하나의 공유 커서가 사용됐음을 짐작</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp;히스토그램 생성</p>
<p>&nbsp;<br />PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.04<br />SQL&gt; analyze table t compute statistics<br /><strong>&nbsp; 2&nbsp; for table for all indexes for all columns size 100;</strong></p>
<p>테이블이 분석되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.10<br />SQL&gt; alteer system flussh shared_pool;<br /><br />경&nbsp;&nbsp; 과: 00:00:00.00<br />SQL&gt; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp; begin<br />&nbsp; 4&nbsp; for i in 1 .. 10000<br />&nbsp; 5&nbsp; loop<br />&nbsp; 6&nbsp; execute immediate<br />&nbsp; 7&nbsp; <br />&nbsp; 8&nbsp; 'select /*similar */ count(*) from t where no = '||i<br />&nbsp; 9&nbsp; into l_cnt;<br />&nbsp;10&nbsp; end loop;<br />&nbsp;11&nbsp; end;<br />&nbsp;12&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:09.71</p>
<p>--&gt; 수행시간이 급격히 저하, 히스토그램을 생성했으므로 각 입려값별로 child 커서를 만들어야 하고</p>
<p>아래처럼 하드파싱 할 때보다 더 나쁜 결과를 초래함</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;SQL&gt;&nbsp;&nbsp; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 4&nbsp;&nbsp;&nbsp; for i in 1..10000<br />&nbsp; 5&nbsp;&nbsp;&nbsp; loop<br />&nbsp; 6&nbsp;&nbsp;&nbsp; execute immediate<br />&nbsp; 7&nbsp;&nbsp;&nbsp; 'select /*exact*/ count(*) from t where no = '||i<br />&nbsp; 8&nbsp;&nbsp;&nbsp; into l_cnt;<br />&nbsp; 9&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;10&nbsp;&nbsp;&nbsp; end;<br />&nbsp;11&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:06.92</p>
<p>&nbsp;</p>
<p>&gt;&gt; 바인드 변수를 사용함</p>
<p>&nbsp;</p>
<p>SQL&gt;&nbsp;&nbsp; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 4&nbsp;&nbsp;&nbsp; for i in 1..10000<br />&nbsp; 5&nbsp;&nbsp;&nbsp; loop<br />&nbsp; 6&nbsp;&nbsp;&nbsp; execute immediate<br />&nbsp; 7&nbsp;&nbsp;&nbsp; 'select /*bind*/ count(*) from t where no =&nbsp; :no'<br />&nbsp; 8&nbsp;&nbsp;&nbsp; into l_cnt<br />&nbsp; 9&nbsp; using i;<br />&nbsp;10&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;11&nbsp;&nbsp;&nbsp; end;<br />&nbsp;12&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.18</p></td></tr></tbody></table></p>
<p><br />&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>--&gt; 과도한 shared pool과 라이브러리 캐시 래치 경합 을 줄이는데 효과는 있지만, 기존 실행계획이 틀어져</p>
<p>이전보다 더 느리게 수행되는 쿼리들이 속출하게 될 가능성이 높음</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
dasini 2013.08.21 12:33:43
복원
<p>&nbsp;바인드 변수 사용 원칙을 잘 지키지 않아 시스템 정상 가동이 어려운 경우가 있음</p>
<p>--&gt; 라이브러리 캐시 경합을 일시적으로 해결하기 위해&nbsp; cursor_sharing&nbsp; 파라미터를 변경하는것을 고려할수 있음</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp; emp 테이블을 새로 만들고 통계하는 히스토그램이 생성 되지 않게끔 수집</p>
<p><br />SQL&gt; create table emp<br />&nbsp; 2&nbsp; as<br />&nbsp; 3&nbsp; select * from scott.emp;</p>
<p>테이블이 생성되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt; create index emp_idx01 on emp(empno);</p>
<p>인덱스가 생성되었습니다.</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>SQL&gt; analyze table emp compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for columns empno size 1;</p>
<p>테이블이 분석되었습니다.</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp;&nbsp; SQL문이 100% 같을 대만 커서를 공유함</p>
<p>SQL&gt; show parameter cursor_sharing</p>
<p>NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUE<br />------------------------------------ ----------- ------------------------------<br />cursor_sharing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXACT</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp; 다른 문자열은 모두 같고 일부&nbsp; Literal 값만 다를때 그 값들을 시스템이 자동 생성한</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 바인드 변ㄴ수로 대체함으로써 공유가능한 SQL 커서로 만듬<br />SQL&gt; alter session set cursor_sharing = FORCE;</p>
<p>세션이 변경되었습니다.</p>
<p>SQL&gt; alter system flush shared_pool;</p>
<p><br />SQL&gt; declare<br />&nbsp; 2&nbsp; <br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 4&nbsp; <br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 6&nbsp; <br />&nbsp; 7&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 8&nbsp; <br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp;10&nbsp; <br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp;12&nbsp; <br />&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp;16&nbsp; <br />&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;18&nbsp; <br />&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;20&nbsp; <br />&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;22&nbsp; <br />&nbsp;23&nbsp; end;<br />&nbsp;24&nbsp; <br />&nbsp;25&nbsp; /</p>
<p>&nbsp;</p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL_TEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; VERSION_COUNT<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp; select ename from emp where empno = :"SYS_B_0"</p>
<p>&nbsp;</p>
<p>&nbsp;&nbsp;</p>
<p>&nbsp; PL/SQL 에서 루프를 돌면서 Dynamic SQL을 이용해&nbsp; Literal 상수를 제공하며 조회했는데</p>
<p>&nbsp;V$SQL을 조회하면 바인드 변수를 사용했을때처럼 공유커서가 반복 사용 됨</p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;. &nbsp;FORCE로 설정시</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 어떤 값으로 싫행하든 항상 같은 실행계획을 사용</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;- EXACT 일 때보다 라이브러리 캐시 부하는 상당히 줄겠지만 컬럼 히스토그램을 사용하지 못하는 문제 때문에</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 성능이 더 나빠질 가능성</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt; 어떤 값은 분포가 90% 이고 다른 값들은 대개 1% 정도 분포를 보일때 FORCE를 사용하면 항상</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 고정된&nbsp; 실행계획을 사용함으로써 EXACT일 때보다 개별 쿼리 성능은 더 나빠질수 있음</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp; 위의 단점을 회피할 목적으로 SIMILAR를 설정, 실행되는 Literal 값에 따라 별도의 커서를 생성함으로써</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 다른 실행계획을 사용할수 있게 함</p>
<p>&nbsp;</p>
<p>SQL&gt; alter system flush shared_pool;</p>
<p>시스템이 변경되었습니다.</p>
<p>SQL&gt; alter session set cursor_sharing = 'SIMILAR';</p>
<p>세션이 변경되었습니다.</p>
<p>&nbsp;</p>
<p><br />SQL&gt; declare<br />&nbsp; 2&nbsp; <br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 4&nbsp; <br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 6&nbsp; <br />&nbsp; 7&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 8&nbsp; <br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp;10&nbsp; <br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp;12&nbsp; <br />&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp;16&nbsp; <br />&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;18&nbsp; <br />&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;20&nbsp; <br />&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;22&nbsp; <br />&nbsp;23&nbsp; end;<br />&nbsp;24&nbsp; <br />&nbsp;25&nbsp; /</p>
<p>&nbsp;</p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp; SQL_TEXT&nbsp;&nbsp; VERSION_COUNT<br />7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14<br /></p>
<p>&gt;&gt;&nbsp;SIMILAR로 설정했는데도 Literal 값에 따라 CHILD 커서를 생성하지 않았고 FORCE일때와 똑같이 하나의</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;공유 커서를 사용함,&nbsp;empno 칼럼에 히스토그램이 없기 때문이며 ,&nbsp;SIMILAR로 설정해도 FORCE와 같은 방식으로 작동</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><br />SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; 입력되는 Literal값에 따라 다른 실행계획이 생성되는지 확인하기 위해 많이 입력함</p>
<p>&nbsp;</p>
<p>SQL&gt; insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)<br />&nbsp; 2&nbsp; select empno, ename, job, mgr, hiredate, sal, comm, deptno<br />&nbsp; 3&nbsp; from emp, (select * from dual connect by level &lt;=&nbsp; 9999)<br />&nbsp; 4&nbsp; where emp.empno = '7788';</p>
<p>9999 개의 행이 만들어졌습니다.</p>
<p>SQL&gt; commit;</p>
<p>커밋이 완료되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt; analyze table emp compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for columns empno size 8;</p>
<p>테이블이 분석되었습니다.</p>
<p>SQL&gt; set autotrace traceonly exp<br />SQL&gt; select ename from emp where empno = '7788';</p>
<p>&nbsp;</p>
<p>&gt;&gt; TABLE을 FULL SCAN함</p>
<p>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3956160932</p>
<p>--------------------------------------------------------------------------<br />| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br />--------------------------------------------------------------------------<br />|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 8761 | 87610 |&nbsp;&nbsp;&nbsp; 15&nbsp;&nbsp; (0)| 00:00:01 |<br />|*&nbsp; 1 |&nbsp; TABLE ACCESS FULL| EMP&nbsp; |&nbsp; 8761 | 87610 |&nbsp;&nbsp;&nbsp; 15&nbsp;&nbsp; (0)| 00:00:01 |<br />--------------------------------------------------------------------------</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; SIMILAR 옵션을 줌</p>
<p>&nbsp;</p>
<p>&nbsp;SQL&gt; delete from emp where empno = '7788' and rownum &lt;= 9999;</p>
<p>9999 행이 삭제되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt;&nbsp;&nbsp; declare<br />&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;12&nbsp;&nbsp; end;<br />&nbsp;13&nbsp; <br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp; <br />&nbsp;16&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>SQL&gt; <br />SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br />7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br /></p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />&gt;&gt; 입력된 Literal 값별로 Child 커서를 생성했고, empno = '7788' 일때는 실행계획까지 달라짐</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; SIMILAR 일때, 히스토그램을 생성해 둔 컬럼에 값의 종류가 아주 많으면 어떤일이 발생하는지 테스트</p>
<p>&nbsp;</p>
<p><br />SQL&gt;&nbsp; create table t<br />&nbsp; 2&nbsp;&nbsp; nologging<br />&nbsp; 3&nbsp;&nbsp; as<br />&nbsp; 4&nbsp; select rownum no from all_objects<br />&nbsp; 5&nbsp; where rownum &lt;= 10000;</p>
<p>테이블이 생성되었습니다.</p>
<p>SQL&gt; create index t_idx on t( no);</p>
<p>인덱스가 생성되었습니다.</p>
<p>SQL&gt; analyze table t compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for all columns size 1;</p>
<p>테이블이 분석되었습니다.</p>
<p>SQL&gt; alter system&nbsp; flush shared_pool;</p>
<p>시스템이 변경되었습니다.</p>
<p>SQL&gt; alter session set cursor_sharing = 'SIMILAR';</p>
<p>세션이 변경되었습니다.</p>
<p>SQL&gt; set timing on<br />SQL&gt; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp; begin<br />&nbsp; 4&nbsp; for i in 1..1000<br />&nbsp; 5&nbsp; loop<br />&nbsp; 6&nbsp; execute immediate<br />&nbsp; 7&nbsp; 'select /*similar*/ count(*) from t where no = '|| i<br />&nbsp; 8&nbsp; into l_cnt;<br />&nbsp; 9&nbsp; end loop;<br />&nbsp;10&nbsp; end;<br />&nbsp;11&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.04</p>
<p>&nbsp;</p>
<p>--&gt; 아주 빠르게 처리함, V&amp;SQL을 조회해 보지 않더라고 하나의 공유 커서가 사용됐음을 짐작</p>
<p>&nbsp;</p>
<p>. 히스토그램 생성</p>
<p>&nbsp;</p>
<p><br />PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.04<br />SQL&gt; analyze table t compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for all columns size 100;</p>
<p>테이블이 분석되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.10<br />SQL&gt; alteer system flussh shared_pool;<br />SP2-0734: "alteer sys..."(으)로 시작되는 알 수 없는 명령 - 나머지 줄은 무시되었습니다.<br />SQL&gt; alter&nbsp; system flush shared_poo;<br />alter&nbsp; system flush shared_poo<br />*<br />1행에 오류:<br />ORA-02000: 누락된 SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT 키워드</p>
<p><br />경&nbsp;&nbsp; 과: 00:00:00.00<br />SQL&gt; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp; begin<br />&nbsp; 4&nbsp; for i in 1 .. 10000<br />&nbsp; 5&nbsp; loop<br />&nbsp; 6&nbsp; execute immediate<br />&nbsp; 7&nbsp; <br />&nbsp; 8&nbsp; 'select /*similar */ count(*) from t where no = '||i<br />&nbsp; 9&nbsp; into l_cnt;<br />&nbsp;10&nbsp; end loop;<br />&nbsp;11&nbsp; end;<br />&nbsp;12&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:09.71</p>
<p>--&gt; 수행시간이 급격히 저하, 히스토그램을 생성했으므로 각 입려값별로 child 커서를 만들어야 하고</p>
<p>아래처럼 하드파싱 할 때보다 더 나쁜 결과를 초래함</p>
<p>&nbsp;</p>
<p>&nbsp;SQL&gt;&nbsp;&nbsp; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 4&nbsp;&nbsp;&nbsp; for i in 1..10000<br />&nbsp; 5&nbsp;&nbsp;&nbsp; loop<br />&nbsp; 6&nbsp;&nbsp;&nbsp; execute immediate<br />&nbsp; 7&nbsp;&nbsp;&nbsp; 'select /*exact*/ count(*) from t where no = '||i<br />&nbsp; 8&nbsp;&nbsp;&nbsp; into l_cnt;<br />&nbsp; 9&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;10&nbsp;&nbsp;&nbsp; end;<br />&nbsp;11&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:06.92</p>
<p>&nbsp;</p>
<p>&gt;&gt; 바인드 변수를 사용함</p>
<p>&nbsp;</p>
<p>SQL&gt;&nbsp;&nbsp; declare<br />&nbsp; 2&nbsp; l_cnt number;<br />&nbsp; 3&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 4&nbsp;&nbsp;&nbsp; for i in 1..10000<br />&nbsp; 5&nbsp;&nbsp;&nbsp; loop<br />&nbsp; 6&nbsp;&nbsp;&nbsp; execute immediate<br />&nbsp; 7&nbsp;&nbsp;&nbsp; 'select /*bind*/ count(*) from t where no =&nbsp; :no'<br />&nbsp; 8&nbsp;&nbsp;&nbsp; into l_cnt<br />&nbsp; 9&nbsp; using i;<br />&nbsp;10&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;11&nbsp;&nbsp;&nbsp; end;<br />&nbsp;12&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>경&nbsp;&nbsp; 과: 00:00:00.18</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>--&gt; 과도한 shared pool과 라이브러리 캐시 래치 경합 을 줄이는데 효과는 있지만, 기존 실행계획이 틀어져</p>
<p>이전보다 더 느리게 수행되는 쿼리들이 속출하게 될 가능성이 높음</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
dasini 2012.05.28 21:42:57
복원
<p>&nbsp;바인드 변수 사용 원칙을 잘 지키지 않아 시스템 정상 가동이 어려운 경우가 있음</p>
<p>--&gt; 라이브러리 캐시 경합을 일시적으로 해결하기 위해&nbsp; cursor_sharing&nbsp; 파라미터를 변경하는것을 고려할수 있음</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp; emp 테이블을 새로 만들고 통계하는 히스토그램이 생성 되지 않게끔 수집</p>
<p><br />SQL&gt; create table emp<br />&nbsp; 2&nbsp; as<br />&nbsp; 3&nbsp; select * from scott.emp;</p>
<p>테이블이 생성되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt; create index emp_idx01 on emp(empno);</p>
<p>인덱스가 생성되었습니다.</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>SQL&gt; analyze table emp compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for columns empno size 1;</p>
<p>테이블이 분석되었습니다.</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp;&nbsp; SQL문이 100% 같을 대만 커서를 공유함</p>
<p>SQL&gt; show parameter cursor_sharing</p>
<p>NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUE<br />------------------------------------ ----------- ------------------------------<br />cursor_sharing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXACT</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp; 다른 문자열은 모두 같고 일부&nbsp; Literal 값만 다를때 그 값들을 시스템이 자동 생성한</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 바인드 변ㄴ수로 대체함으로써 공유가능한 SQL 커서로 만듬<br />SQL&gt; alter session set cursor_sharing = FORCE;</p>
<p>세션이 변경되었습니다.</p>
<p>SQL&gt; alter system flush shared_pool;</p>
<p><br />SQL&gt; declare<br />&nbsp; 2&nbsp; <br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 4&nbsp; <br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 6&nbsp; <br />&nbsp; 7&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 8&nbsp; <br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp;10&nbsp; <br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp;12&nbsp; <br />&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp;16&nbsp; <br />&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;18&nbsp; <br />&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;20&nbsp; <br />&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;22&nbsp; <br />&nbsp;23&nbsp; end;<br />&nbsp;24&nbsp; <br />&nbsp;25&nbsp; /</p>
<p>&nbsp;</p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL_TEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; VERSION_COUNT<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp; select ename from emp where empno = :"SYS_B_0"</p>
<p>&nbsp;</p>
<p>&nbsp;&nbsp;</p>
<p>&nbsp; PL/SQL 에서 루프를 돌면서 Dynamic SQL을 이용해&nbsp; Literal 상수를 제공하며 조회했는데</p>
<p>&nbsp;V$SQL을 조회하면 바인드 변수를 사용했을때처럼 공유커서가 반복 사용 됨</p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;. &nbsp;FORCE로 설정시</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 어떤 값으로 싫행하든 항상 같은 실행계획을 사용</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;- EXACT 일 때보다 라이브러리 캐시 부하는 상당히 줄겠지만 컬럼 히스토그램을 사용하지 못하는 문제 때문에</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 성능이 더 나빠질 가능성</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt; 어떤 값은 분포가 90% 이고 다른 값들은 대개 1% 정도 분포를 보일때 FORCE를 사용하면 항상</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 고정된&nbsp; 실행계획을 사용함으로써 EXACT일 때보다 개별 쿼리 성능은 더 나빠질수 있음</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt;&nbsp; 위의 단점을 회피할 목적으로 SIMILAR를 설정, 실행되는 Literal 값에 따라 별도의 커서를 생성함으로써</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 다른 실행계획을 사용할수 있게 함</p>
<p>&nbsp;</p>
<p>SQL&gt; alter system flush shared_pool;</p>
<p>시스템이 변경되었습니다.</p>
<p>SQL&gt; alter session set cursor_sharing = 'SIMILAR';</p>
<p>세션이 변경되었습니다.</p>
<p>&nbsp;</p>
<p><br />SQL&gt; declare<br />&nbsp; 2&nbsp; <br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 4&nbsp; <br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 6&nbsp; <br />&nbsp; 7&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 8&nbsp; <br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp;10&nbsp; <br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp;12&nbsp; <br />&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp;16&nbsp; <br />&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;18&nbsp; <br />&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;20&nbsp; <br />&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;22&nbsp; <br />&nbsp;23&nbsp; end;<br />&nbsp;24&nbsp; <br />&nbsp;25&nbsp; /</p>
<p>&nbsp;</p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp; SQL_TEXT&nbsp;&nbsp; VERSION_COUNT<br />7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14<br /></p>
<p>&gt;&gt;&nbsp;SIMILAR로 설정했는데도 Literal 값에 따라 CHILD 커서를 생성하지 않았고 FORCE일때와 똑같이 하나의</p>
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;공유 커서를 사용함,&nbsp;empno 칼럼에 히스토그램이 없기 때문이며 ,&nbsp;SIMILAR로 설정해도 FORCE와 같은 방식으로 작동</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><br />SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; 입력되는 Literal값에 따라 다른 실행계획이 생성되는지 확인하기 위해 많이 입력함</p>
<p>&nbsp;</p>
<p>SQL&gt; insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)<br />&nbsp; 2&nbsp; select empno, ename, job, mgr, hiredate, sal, comm, deptno<br />&nbsp; 3&nbsp; from emp, (select * from dual connect by level &lt;=&nbsp; 9999)<br />&nbsp; 4&nbsp; where emp.empno = '7788';</p>
<p>9999 개의 행이 만들어졌습니다.</p>
<p>SQL&gt; commit;</p>
<p>커밋이 완료되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt; analyze table emp compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for columns empno size 8;</p>
<p>테이블이 분석되었습니다.</p>
<p>SQL&gt; set autotrace traceonly exp<br />SQL&gt; select ename from emp where empno = '7788';</p>
<p>&nbsp;</p>
<p>&gt;&gt; TABLE을 FULL SCAN함</p>
<p>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3956160932</p>
<p>--------------------------------------------------------------------------<br />| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br />--------------------------------------------------------------------------<br />|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 8761 | 87610 |&nbsp;&nbsp;&nbsp; 15&nbsp;&nbsp; (0)| 00:00:01 |<br />|*&nbsp; 1 |&nbsp; TABLE ACCESS FULL| EMP&nbsp; |&nbsp; 8761 | 87610 |&nbsp;&nbsp;&nbsp; 15&nbsp;&nbsp; (0)| 00:00:01 |<br />--------------------------------------------------------------------------</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&gt;&gt; SIMILAR 옵션을 줌</p>
<p>&nbsp;</p>
<p>&nbsp;SQL&gt; delete from emp where empno = '7788' and rownum &lt;= 9999;</p>
<p>9999 행이 삭제되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt;&nbsp;&nbsp; declare<br />&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;12&nbsp;&nbsp; end;<br />&nbsp;13&nbsp; <br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp; <br />&nbsp;16&nbsp; /</p>
<p>PL/SQL 처리가 정상적으로 완료되었습니다.</p>
<p>SQL&gt; <br />SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br />7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>7v2nh2sv9hf80<br />select ename from emp where empno = :"SYS_B_0"<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>SQL_ID<br />-------------<br />SQL_TEXT<br />--------------------------------------------------------------------------------<br />VERSION_COUNT<br />-------------<br /></p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12 3295026711&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br />&gt;&gt; 입력된 Literal 값별로 Child 커서를 생성했고, empno = '7788' 일때는 실행계획까지 달라짐</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
dasini 2012.05.28 20:14:45
복원
<p>&nbsp;바인드 변수 사용 원칙을 잘 지키지 않아 시스템 정상 가동이 어려운 경우가 있음</p>
<p>--&gt; 라이브러리 캐시 경합을 일시적으로 해결하기 위해&nbsp; cursor_sharing&nbsp; 파라미터를 변경하는것을 고려할수 있음</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp; emp 테이블을 새로 만들고 통계하는 히스토그램이 생성 되지 않게끔 수집</p>
<p><br />SQL&gt; create table emp<br />&nbsp; 2&nbsp; as<br />&nbsp; 3&nbsp; select * from scott.emp;</p>
<p>테이블이 생성되었습니다.</p>
<p>&nbsp;</p>
<p>SQL&gt; create index emp_idx01 on emp(empno);</p>
<p>인덱스가 생성되었습니다.</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>SQL&gt; analyze table emp compute statistics<br />&nbsp; 2&nbsp; for table for all indexes for columns empno size 1;</p>
<p>테이블이 분석되었습니다.</p>
<p>&nbsp;</p>
<p>&gt;&gt;</p>
<p>SQL&gt; show parameter cursor_sharing</p>
<p>NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUE<br />------------------------------------ ----------- ------------------------------<br />cursor_sharing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXACT</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><br />SQL&gt; alter session set cursor_sharing = FORCE;</p>
<p>세션이 변경되었습니다.</p>
<p>SQL&gt; alter system flush shared_pool;</p>
<p><br />SQL&gt; declare<br />&nbsp; 2&nbsp; <br />&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp; l_condition varchar2(20);<br />&nbsp; 4&nbsp; <br />&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp; l_ename emp.ename%type;<br />&nbsp; 6&nbsp; <br />&nbsp; 7&nbsp;&nbsp;&nbsp; begin<br />&nbsp; 8&nbsp; <br />&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; for c in ( select empno from emp )<br />&nbsp;10&nbsp; <br />&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp; loop<br />&nbsp;12&nbsp; <br />&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_condition := 'empno = '|| c.empno;<br />&nbsp;14&nbsp; <br />&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute immediate 'select ename ' || 'from emp where ' || l_condition<br />&nbsp;16&nbsp; <br />&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; into l_ename;<br />&nbsp;18&nbsp; <br />&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line( l_condition ||' : ' || l_ename );<br />&nbsp;20&nbsp; <br />&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br />&nbsp;22&nbsp; <br />&nbsp;23&nbsp; end;<br />&nbsp;24&nbsp; <br />&nbsp;25&nbsp; /</p>
<p>&nbsp;</p>
<p>SQL&gt; select sql_id, sql_text, version_count<br />&nbsp; 2&nbsp; from v$sqlarea<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>&nbsp;</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL_TEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; VERSION_COUNT<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp; select ename from emp where empno = :"SYS_B_0"</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><br />&nbsp;</p>
<p>&nbsp; PL/SQL 에서 루프를 돌면서 Dynamic SQL을 이용해&nbsp; Literal 상수를 제공하며 조회했는데</p>
<p>&nbsp;V$SQL을 조회하면 바인드 변수를 사용했을때처럼 공유커서가 반복 사용 됨</p>
<p><br />SQL&gt; select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions,<br />&nbsp;fetches<br />&nbsp; 2&nbsp; from v$sql<br />&nbsp; 3&nbsp; where sql_text like 'select ename%';</p>
<p>SQL_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHLD_NO&nbsp; PLAN_HASH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOADS PARSE_CALLS EXECUTIONS&nbsp;&nbsp;&nbsp; FETCHES<br />------------- ---------- ---------- ---------- ----------- ---------- ----------<br />7v2nh2sv9hf80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 3956160932&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14</p>
<p>&nbsp;&nbsp;</p>
dasini 2012.05.28 08:29:11