7._Result_캐시

조회 수 1433 추천 수 0 2014.09.11 15:55:53
운영자 *.73.93.2
제목 작성자 날짜
7._Result_캐시 (current) 운영자 2014.09.11 15:55:53
복원
<p></p><ul><li>Result ìºìë Shared Pool ì ìì¹</li><li>ìì¤í I/O ë°ìëì ìµìí</li><li>íë² ìíí Query ëë PL/SQL í¨ìì ê²°ê³¼ ê°ì ì ì¥ (11g ì´ì)</li><li>Result ìºì¬ ìì­<ul><li>SQL Query Result ìºì</li><li>PL/SQL  í¨ì Result ìºì</li></ul></li></ul><ul><li>ê´ë ¨ Parameter </li></ul><p></p><p></p><table style="background:#cccccc;" cellspacing="1"><tbody><tr style="background:#ffffff;"><td><p style="text-align:center;">구붠</p></td><td><p style="text-align:center;">기본갠</p></td><td><p style="text-align:center;">ì¤ëª </p></td></tr><tr style="background:#ffffff;"><td><p>Result_cache_mode </p></td><td><p style="text-align:center;">manual </p></td><td><p>Result Cache  ë±ë¡ë°©ìì ê²°ì </p><p></p><ul><li>manual : result_cache íí¸ë¥¼ ëªìí SQLë§ ë±ë¡</li><li>force : no_result_cache  íí¸ë¥¼ ëªìíì§ ìì ëª¨ë  SQLì ë±ë¡ </li></ul></td></tr><tr style="background:#ffffff;"><td><p> result_cache_max_size</p></td><td><p style="text-align:center;"> N/A</p></td><td><p> SGA ë´ Result_cacheê° ì¬ì©í  ì´ ë(Bytes)</p><p></p><ul><li>0 =&gt; reslt ê¸°ë¥ ìëìí¨</li><li>N/A =&gt; ì¤ë¼í´ì´ ê´ë¦¬ (ìµë Shared pool ì 75% ê° ëì§ ìê² í¨)<ul><li>memory_target * 0.25%</li><li>sga_target * 0.5%</li><li>shared_pool_sizeìë ê´ë¦¬ì =&gt; shared_pool_size * 1%  </li></ul></li></ul></td></tr><tr style="background:#ffffff;"><td><p> result_cache_max_result</p></td><td><p style="text-align:center;">5</p></td><td><p> íëì SQLê²°ê³¼ ì§í©ì´ ì ì²´ìºì ìì­ì ì°¨ì§í ì ìë ìµë í¬ê¸° ë¹ì¨</p></td></tr><tr style="background:#ffffff;"><td><p> result_cache_remote_expiration</p></td><td><p style="text-align:center;">0</p></td><td><p>remote ê°ì²´ì 결과를 ì¼ë§ ëì ë³´ê´í ì§ (ë¶)</p><p>0 =&gt; remote ê°ì²´ ì ì¥íì§ ìì </p></td></tr></tbody></table><div style="text-align:center;"><br /></div><p></p><p></p><ul><li>ê´ë ¨ Latch<ul><li>Result Cache: Latch</li><li>Result Cache:SO Latch</li></ul></li></ul><p><br /></p><p></p><ul><li>ì¬ì©ë°©ë²</li></ul><p></p><blockquote class="q7" style="line-height:normal;text-align:justify;"><p><font face="Dotum"><span><br /></span></font></p><p></p><ul><li><span style="font-family:Dotum;">Query ìºì±</span></li></ul><p style="font-family:Tahoma, sans-serif;font-size:15px;"><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;">select </span><span style="background-color:transparent;vertical-align:baseline;font-size:12px;font-family:Gulim;"><b><span style="font-family:Dotum;">/*+ RESULT_CACHE */ </span></b></span><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;">COL,COUNT(*)</span></p><p><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;">from R_CACHE_TEST</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;">where GUBUN=7</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;">group by COL</span></p><p><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;">;</span></p><p><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;"><br /></span></p><p></p><ul><li><span style="font-family:Dotum;">PL/SQL í¨ì ìºì±</span></li></ul><p><span style="font-family:Dotum;"><br /></span></p><p><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;">create or replace function get_team_name (p_team_cd number)</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;">return varchar2</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;"><b>RESULT_CACHE RELIES_ON (r_cache_function)</b></span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;">is</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;"><span class="Apple-tab-span"> </span>l_team_name r_cache_function.team_name%type;</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;">begin</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;"><span class="Apple-tab-span"> </span>select team_name into l_team_name</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;"><span class="Apple-tab-span"> </span>from r_cache_function</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;"><span class="Apple-tab-span"> </span>where team_cd=p_team_cd;</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;"><span class="Apple-tab-span"> </span>return l_team_name;</span><br /><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;">end;</span>
</p><p><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;">/</span></p><p><span style="background-color:transparent;vertical-align:baseline;font-family:Dotum;font-size:12px;"><br /></span></p></blockquote><p><br /></p><p></p><ul><li><ul><li>v$result_cache_objects ìì ìºì±ë ìë£ë¥¼ íì¸ê°ë¥</li><li>bind  ë³ì ì¬ì©ììë bind  ë³ìì ê°ì ì¢ë¥ ë³ë¡ ìºì±ë¨</li><li>LRU ì고리ì¦ì ìí´ ê´ë¦¬ë¨</li><li>ìºì±ë ì¿¼ë¦¬ê° ì°¸ì¡°íë  TABLE ì ë³ê²½ ë°ìì í´ë¹ ìºì ìí¸ë¦¬ë 무í¨í ë¨</li><li>íí°ì íì´ë¸ì DMLì´ ë°ìí´ë 무ê´í íí°ì ê¹ì§ 무í¨íë¨</li><li>PL/SQL í¨ì ìºì±ììë relies_on  ì ëªìë íì´ë¸ì´ ë³ê²½ëë©´ 무í¨íë¨</li></ul></li></ul><p></p><p><br /></p><p><br /></p><p><br /></p><p></p><ul><li>쿼리 ê²°ê³¼ ì§í©ì ìºì±íì§ ëª»íë ê²½ì°<ul><li>Dictionary Object 참조</li><li>Temporary íì´ë¸ 참조</li><li>Sequence ì¬ì© (Currval, nextval pseudo ì»¬ë¼ í¸ì¶)</li><li>ë¤ì í¨ì ì¬ì©ì<ul><li>CURRENT_DATE</li><li>CURRENT_TIMESTAMP</li><li>LOCAL_TIMESTAMP</li><li>SYS_CONTEXT(with non-constant vriables)</li><li>SYS_GUID</li><li>SYSDATE</li><li>SYSTIMESTAMP</li><li>USERENV(with non-constant variables)</li></ul></li></ul></li></ul><div><br /></div><ul><li>Result ìºìì ê·¹ëí<ul><li>ìì ê²°ê³¼ ì§í©ì ì»ì¼ë ¤ê³  ëì©ë ë°ì´í°ë¥¼ ì½ì´ì¼ í ë</li><li>ì½ê¸° ì ì©ì ìì íì´ë¸ì ë°ë³µì ì¼ë¡ ì½ì´ì¼ í  ë</li><li>ì½ê¸° ì ì© ì½ë íì´ë¸ì ì½ì´ ì½ë ëªì¹­ì ë°ííë í¨ì</li></ul></li></ul><p><br /></p><p></p><ul><li>Result ìºì ì¬ì©ì ìì <ul><li>DML ì´ ë§ì´ ë°ìíë TABLE</li><li>í¨ì ëë ë°ì¸ë ë³ì를 ê°ì§ 쿼리ìì ìë ¥ëë ê°ì ì¢ë¥ê° ë§ê³  ê·¸ ê°ë¤ì´ 골고루 ìë ¥ ë ë</li></ul></li></ul><p><br /></p>
assczbmyi 2014.09.08 21:51:00
복원
<p></p><ul><li>Result 캐시는 Shared Pool 에 위치</li><li>시스템 I/O 발생량을 최소화</li><li>한번 수행한 Query 또는 PL/SQL 함수의 결과 값을 저장 (11g 이상)</li><li>Result 캐쉬 영역</li><ul><li>SQL Query Result 캐시</li><li>PL/SQL &nbsp;함수 Result 캐시</li></ul></ul><ul><li>관련 Parameter&nbsp;</li></ul><p></p><p></p><table style="background:#CCCCCC" cellspacing="1"><tbody><tr style="background:#FFFFFF"><td class=""><p style="text-align: center;">구분&nbsp;</p></td><td class=""><p style="text-align: center;">기본값&nbsp;</p></td><td class=""><p style="text-align: center;">설명&nbsp;</p></td></tr>
<tr style="background:#FFFFFF"><td class=""><p>Result_cache_mode&nbsp;</p></td><td class=""><p style="text-align: center;">manual&nbsp;</p></td><td class=""><p>Result Cache &nbsp;등록방식을 결정</p><p><ul><li>manual : result_cache 힌트를 명시한 SQL만 등록</li><li>force : no_result_cache &nbsp;힌트를 명시하지 않은 모든 SQL을 등록&nbsp;</li></ul></p></td></tr>
<tr style="background:#FFFFFF"><td><p>&nbsp;result_cache_max_size</p></td><td class=""><p style="text-align: center;">&nbsp;N/A</p></td><td><p>&nbsp;SGA 내 Result_cache가 사용할 총 량(Bytes)</p><p><ul><li>0 =&gt; reslt 기능 작동안함</li><li>N/A =&gt; 오라클이 관리 (최대 Shared pool 의 75% 가 넘지 않게 함)</li><ul><li>memory_target * 0.25%</li><li>sga_target * 0.5%</li><li>shared_pool_size수동 관리시 =&gt; shared_pool_size * 1% &nbsp;</li></ul></ul></p></td></tr>
<tr style="background:#FFFFFF"><td><p>&nbsp;result_cache_max_result</p></td><td><p style="text-align: center;">5</p></td><td><p>&nbsp;하나의 SQL결과 집합이 전체캐시 영역에 차지할수 있는 최대 크기 비율</p></td></tr>
<tr style="background:#FFFFFF"><td><p>&nbsp;result_cache_remote_expiration</p></td><td><p style="text-align: center;">0</p></td><td><p>remote 객체의 결과를 얼마 동안 보관할지 (분)</p><p>0 =&gt; remote 개체 저장하지 않음&nbsp;</p></td></tr>
</tbody></table><div style="text-align: center;"><br /></div><p></p><p><ul><li>관련 Latch</li><ul><li>Result Cache: Latch</li><li>Result Cache:SO Latch</li></ul></ul></p><p><br /></p><p><ul><li>사용방법</li></ul></p><p><blockquote class="q7" style="line-height: normal; text-align: justify; "><p><font face="Dotum"><span style="white-space: pre-wrap;"><br /></span></font></p><p><ul><li><span style="white-space: pre-wrap; font-family: Dotum; ">Query 캐싱</span></li></ul></p><p style="font-family: Tahoma, sans-serif; font-size: 15px; "><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; ">select </span><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-size: 12px; font-family: Gulim; "><b><span style="font-family: Dotum; ">/*+ RESULT_CACHE */ </span></b></span><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; ">COL,COUNT(*)</span></p><p><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; ">from R_CACHE_TEST</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; ">where GUBUN=7</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; ">group by COL</span></p><p><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; ">;</span></p><p><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; "><br /></span></p><p><ul><li><span style="font-family: Dotum; white-space: pre-wrap; ">PL/SQL 함수 캐싱</span></li></ul></p><p><span style="white-space: pre-wrap; font-family: Dotum; "><br /></span></p><p><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; ">create or replace function get_team_name (p_team_cd number)</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; ">return varchar2</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; "><b>RESULT_CACHE RELIES_ON (r_cache_function)</b></span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; ">is</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; "><span class="Apple-tab-span" style="white-space: pre; "> </span>l_team_name r_cache_function.team_name%type;</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; ">begin</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; "><span class="Apple-tab-span" style="white-space: pre; "> </span>select team_name into l_team_name</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; "><span class="Apple-tab-span" style="white-space: pre; "> </span>from r_cache_function</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; "><span class="Apple-tab-span" style="white-space: pre; "> </span>where team_cd=p_team_cd;</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; "><span class="Apple-tab-span" style="white-space: pre; "> </span>return l_team_name;</span><br /><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; ">end;</span>
</p><p><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; ">/</span></p><p><span style="background-color: transparent; vertical-align: baseline; white-space: pre-wrap; font-family: Dotum; font-size: 12px; "><br /></span></p></blockquote></p><p><br /></p><p><ul><ul><li>v$result_cache_objects 에서 캐싱된 자료를 확인가능</li><li>bind &nbsp;변수 사용시에는 bind &nbsp;변수의 값의 종류 별로 캐싱됨</li><li>LRU 알고리즘에 의해 관리됨</li><li>캐싱된 쿼리가 참조하는 &nbsp;TABLE 에 변경 발생시 해당 캐시 엔트리는 무효화 됨</li><li>파티션 테이블에 DML이 발생해도 무관한 파티션 까지 무효화됨</li><li>PL/SQL 함수 캐싱시에는 relies_on &nbsp;에 명시된 테이블이 변경되면 무효화됨</li></ul></ul></p><p></p><p><br /></p><p><br /></p><p><br /></p><p><ul><li>쿼리 결과 집합을 캐싱하지 못하는 경우</li><ul><li>Dictionary Object 참조</li><li>Temporary 테이블 참조</li><li>Sequence 사용 (Currval, nextval pseudo 컬럼 호출)</li><li>다음 함수 사용시</li><ul><li>CURRENT_DATE</li><li>CURRENT_TIMESTAMP</li><li>LOCAL_TIMESTAMP</li><li>SYS_CONTEXT(with non-constant vriables)</li><li>SYS_GUID</li><li>SYSDATE</li><li>SYSTIMESTAMP</li><li>USERENV(with non-constant variables)</li></ul></ul></ul><div><br /></div><ul><li>Result 캐시의 극대화</li><ul><li>작은 결과 집합을 얻으려고 대용량 데이터를 읽어야 할때</li><li>읽기 전용의 작은 테이블을 반복적으로 읽어야 할 때</li><li>읽기 전용 코드 테이블을 읽어 코드 명칭을 반환하는 함수</li></ul></ul></p><p><br /></p><p><ul><li>Result 캐시 사용의 자제</li><ul><li>DML 이 많이 발생하는 TABLE</li><li>함수 또는 바인드 변수를 가진 쿼리에서 입력되는 값의 종류가 많고 그 값들이 골고루 입력 될때</li></ul></ul></p><p><br /></p>
시와처 2013.07.07 10:08:50
복원
<p>- Result 캐시는 Shared Pool 에 위치함</p><p>- 시스템 I/O ㅂㄹ생량을 최소화 하는데 도움이 된다.</p>
시와처 2012.05.27 11:14:21