![\"imageb\"](\"https:\/\/i0.wp.com\/oracledba.zapto.org\/wordpress\/wp-content\/uploads\/2018\/02\/imageb_thumb.png?resize=244%2C5\" "\\"imageb\\"")
<\/a><\/p>\n\ub9cc\uc77c result cache\uc758 \ud06c\uae30\ub97c \uba85\uc2dc\uc801\uc73c\ub85c \uc9c0\uc815\ud558\uace0\uc790 \ud55c\ub2e4\uba74 \uc774 parameter\ub97c \uc0ac\uc6a9\ud55c\ub2e4:<\/p>\n
\ucd5c\uc18c\uac12\uc740 0\uc774\uba70, \uc774 \uacbd\uc6b0 result caching\uc774 disable\ub41c\ub2e4.<\/p>\n
\ucd5c\ub300\uac12\uc740 shared pool \ud06c\uae30\uc758 75%\uc774\ub2e4.<\/p>\n
<\/p>\n
\uc774 parameter\ub294 \uc6d0\ub798 \ub3d9\uc801\uc73c\ub85c \ubcc0\uacbd\uc774 \uac00\ub2a5\ud558\uc9c0\ub9cc, \uc77c\ub2e8 0\uc73c\ub85c \uc124\uc815\ud558\uc5ec instance\ub97c \uc2dc\uc791\ud55c \ud6c4\uc5d0\ub294 alter system \uba85\ub839\uc5d0 \uc758\ud574 \uc218\uc815\ud560 \uc218 \uc5c6\ub2e4. \uc989, result caching\uc744 \ub2e4\uc2dc enable\ud558\uae30 \uc704\ud574\uc11c\ub294 instance restart\uac00 \ud544\uc694\ud558\ub2e4.<\/p>\n
<\/p>\n
\ud55c\ud3b8 \uc544\uc27d\uc9c0\ub9cc \ud604 \ubc84\uc804\uc5d0\uc11c result cache \ud06c\uae30\uc5d0 \ub300\ud55c advisory \uae30\ub2a5\uc740 \uad6c\ud604\ub418\uc9c0 \uc54a\uc558\uc73c\uba70, shared pool advisory \uae30\ub2a5\uc774 result cache \uc0ac\uc774\uc988\uc5d0 \ub300\ud55c \uad8c\uace0\ub97c \uc81c\uc2dc\ud558\uc9c0\ub3c4 \uc54a\ub294\ub2e4.<\/p>\n
<\/p>\n
RESULT_CACHE_MAX_RESULT<\/p>\n
\uc774 parameter\ub294 \ube44\ub85d result cache \uc790\uccb4\uc758 sizing\uc744 \uc815\ud558\ub294 \uac83\uc740 \uc544\ub2c8\uc9c0\ub9cc, \uc804\uccb4 result cache memory \ub0b4\uc5d0\uc11c \ud558\ub098\uc758 result\uac00 \ucc28\uc9c0\ud560 \uc218 \uc788\ub294 \uba54\ubaa8\ub9ac\uc758 \ucd5c\ub300 \ud06c\uae30\ub97c result cache memory \uc804\uccb4 \ud06c\uae30\uc5d0 \ub300\ud55c %\ub85c \ub098\ud0c0\ub0b8\ub2e4. \ub514\ud3f4\ud2b8 \uac12\uc740 5%\uc774\ub2e4.<\/p>\n
<\/p>\n
SQL Query Result Cache\uc758 \ub3d9\uc791<\/p>\n
\ub2e4\uc74c\uacfc \uac19\uc774 \ud06c\uac8c \uc138\uac00\uc9c0\ub85c \ub098\ub204\uc5b4 \ubcfc \uc218 \uc788\ub2e4:<\/p>\n
<\/p>\n
Cache-in: query \uacb0\uacfc\ub294 \uc5b4\ub5bb\uac8c cache\ub418\ub294\uac00?<\/span><\/strong><\/p>\nCache-hit: \uc6d0\ud558\ub294 query \uacb0\uacfc\ub97c cache\ub85c\ubd80\ud130 \uc5b4\ub5bb\uac8c \ucc3e\ub294\uac00?<\/span><\/strong><\/p>\nCache-out: \ud55c\ubc88 cache\ub41c \uacb0\uacfc\ub294 \uc5b4\ub5bb\uac8c cache\ub85c\ubd80\ud130 \u201cout\u201d \ub418\ub294\uac00?<\/span><\/strong><\/p>\n <\/p>\n
Cache-in<\/strong><\/p>\n\uc774\ub97c \uacb0\uc815\ud558\ub294 1\ucc28\uc801\uc778 \uc694\uc18c\ub294 \uc2dc\uc2a4\ud15c\/\uc138\uc158 \ub808\ubca8 parameter\uc778 RESULT_CACHE_MODE\uc758 \uc124\uc815\uc774\ub2e4.<\/p>\n
\ub2e4\uc74c\uacfc \uac19\uc740 \ub450 \uac00\uc9c0 \uc124\uc815\uc774 \uc81c\uacf5\ub41c\ub2e4.<\/p>\n
<\/p>\n
MANUAL<\/strong>: \uc774 \uacbd\uc6b0 result caching\ub97c \uc6d0\ud558\ub294 SQL \ub9c8\ub2e4 \uac1c\ubcc4\uc801\uc73c\ub85c \/*+ result_cache *\/ \ud78c\ud2b8\ub97c \uc8fc\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n\uc774\uac83\uc774 default \uc774\ub2e4.<\/p>\n
FORCE<\/strong>: \ubaa8\ub4e0 SQL\uc774 \uc77c\uad04\uc801\uc73c\ub85c result caching\uc758 \ub300\uc0c1\uc774 \ub41c\ub2e4. \uc774 \uacbd\uc6b0 \uc5ed\uc73c\ub85c \/*+ no_result_cache *\/ \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud2b9\uc815 SQL\uc5d0 \ub300\ud574\uc11c\ub294 result caching\uc744 \ud558\uc9c0 \uc54a\ub3c4\ub85d \uc124\uc815\ud560 \uc218 \uc788\ub2e4.<\/p>\n\uc774\uc640 \uac19\uc774 result caching\uc758 \ub300\uc0c1\uc774 \ub418\ub294 SQL\uc774 \uc2e4\ud589\ub41c\ub2e4\uba74, cache-in\uc740 \ub2e4\uc74c \ub450 \uc870\uac74\ub4e4\uc744 \ubaa8\ub450 \ub9cc\uc871\ud558\ub294 \uacbd\uc6b0\uc5d0 \ubc1c\uc0dd\ud55c\ub2e4:<\/p>\n
\ud604\uc7ac \uadf8 \uacb0\uacfc\uac00 cache\ub418\uc5b4 \uc788\uc9c0 \uc54a\ub2e4\uba74<\/p>\n
\uadf8\ub9ac\uace0 \uacb0\uacfc\uc758 \ud06c\uae30\uac00 RESULT_CACHE_MAX_RESULT \uc774\ud558\ub77c\uba74<\/p>\n
\uadf8\ub7ec\ub098 \uc5ec\uae30\uc5d0\ub294 \uc81c\uc57d \uc0ac\ud56d\uc774 \uc788\ub2e4. \ub2e4\uc74c query\ub4e4\uc740 result caching\uc774 \uc801\uc6a9\ub418\uc9c0 \uc54a\ub294\ub2e4. (\/*+ result_cache *\/ \ud78c\ud2b8\ub97c \uc8fc\ub354\ub77c\ub3c4 \uadf8\ub0e5 \ubb34\uc2dc\ub41c\ub2e4.) :<\/p>\n
Dictionary \ubc0f temporary \ud14c\uc774\ube14\uc5d0 \ub300\ud55c query<\/p>\n
\uc2dc\ud000\uc2a4\uc758 CURRVAL\/NEXTVAL\uc5d0 \ub300\ud55c query<\/p>\n
current_date, current_timestamp, local_timestamp, userenv\/sys_context (with non-constant variables), sys_guid, sysdate, sys_timestamp \ub4f1\uc758 \ud568\uc218 \ud638\ucd9c\uc774 \ud3ec\ud568\ub41c query Non-deterministic PL\/SQL \ud568\uc218\ub97c \ud638\ucd9c\ud558\ub294 query<\/p>\n
<\/p>\n
\uc774\ub4e4\uc744 \uc0b4\ud3b4\ubcf4\uba74 \uadf8 \uacb0\uacfc\ub97c cache\ud558\uae30\uc5d0 \uc801\uc808\ud558\uc9c0 \uc54a\uc740 \uacbd\uc6b0\uc784\uc744 \uc54c \uc218 \uc788\ub2e4. \uc608\ub97c \ub4e4\uc5b4 \uc2dc\ud000\uc2a4\uc5d0 \ub300\ud55c query \uacb0\uacfc\ub294 \uc9c0\uadf9\ud788 \u201c\uc77c\uc2dc\uc801\u201d\uc774\ub2e4; \uc989, \ub9e4\ubc88 query\ub97c \ud560 \ub54c\ub9c8\ub2e4 \uacb0\uacfc\uac12\uc774 \ub2ec\ub77c\uc9c4\ub2e4. \uc774 \uacbd\uc6b0 cache\uc758 \uc2e4\uc775\uc774 \uc788\ub2e4\uace0 \ubcf4\uae30 \uc5b4\ub824\uc6b8 \ubfd0\ub354\ub7ec, \uad1c\ud788 result cache memory\ub9cc \ucc28\uc9c0\ud568\uc73c\ub85c\uc368, \uc815\uc791 \ud544\uc694\ud55c query \uacb0\uacfc\uc758 caching\uc5d0 \ubc29\ud574\uac00 \ub420 \uc218 \uc788\ub2e4.<\/p>\n
<\/p>\n
Cache-hit<\/strong><\/p>\nCache-hit\uc774\ub780 \uc2e4\uc81c\ub85c \uc2e4\ud589\ud558\uc600\uc744 \ub54c\uc640 \ub3d9\uc77c\ud55c \uacb0\uacfc\ub97c result cache\ub85c\ubd80\ud130 \uc5bb\uc5c8\uc74c\uc744 \uc758\ubbf8\ud55c\ub2e4. \ub530\ub77c\uc11c \uc6b0\uc120\uc740 \ub3d9\uc77c\ud55c SQL\uc774 \uc218\ud589\ub418\uc5b4\uc57c \ud558\uba70, \ub3d9\uc2dc\uc5d0 parameter\ub3c4 \ub3d9\uc77c\ud574\uc57c \ud55c\ub2e4. \uadf8\ub807\ub2e4\uba74 parameter\ub780 \ubb34\uc5c7\uc778\uac00? Query result\ub294 SQL \ubb38\uc7a5 \ub0b4\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \uc694\uc18c\ub4e4\uc5d0 \uc758\ud574 parameter \ud654 \ub41c\ub2e4:<\/p>\n
Bind \ubcc0\uc218<\/p>\n
dbtimezone, sessiontimezone,userenv\/sys_context (with constant variables), uid, user \ub4f1\uc758 \ubcf4\ub2e4 \uc815\uc801\uc778 \ud568\uc218 \ud638\ucd9c \uacb0\uacfc NLS \ub4f1\uc758 \ud658\uacbd parameter \ub4e4 \ub2e4\uc2dc \ub9d0\ud574 SQL \ubb38\uc7a5 \ubfd0\ub9cc \uc544\ub2c8\ub77c \uc704\uc640 \uac19\uc740 parameter\ub4e4\uc774 \ubaa8\ub450 \uc77c\uce58\ud560 \ub54c cache-hit\uc774 \ubc1c\uc0dd\ud558\ub294 \uac83\uc774\ub2e4.<\/p>\n
<\/p>\n
RAC \ud658\uacbd\uc5d0\uc11c result cache\uc758 cache-hit\uc740 \uc5b4\ub514\uae4c\uc9c0\ub098 local event\uc774\ub2e4. \ub2e4\uc2dc \ub9d0\ud574 \uac01 instance\uc758 result cache memory\ub294 \uac01\uc790\uc758 \ub0b4\uc6a9\uc744 \ub2f4\uace0 \uc788\uc744 \ubfd0\uc774\ub2e4. Result cache\uc5d0 \ub300\ud574\uc11c\ub294 \ud1b5\uc0c1\uc758 cache fusion\uacfc \uac19\uc740 \uae30\ub2a5\uc774 \uc81c\uacf5\ub418\uc9c0 \uc54a\ub294\ub2e4\uace0\ub3c4 \ubcfc \uc218 \uc788\uc744 \uac83\uc774\ub2e4.<\/p>\n
<\/p>\n
Cache-out<\/strong><\/p>\nCache\ub41c result\ub294 \ub2e4\uc74c\uc758 \uacbd\uc6b0\uc5d0 result cache\ub85c\ubd80\ud130 \u201cout\u201d\ub41c\ub2e4:<\/p>\n
Age-out. Result cache \uc5ed\uc2dc LRU cache\uc774\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n
Invalidation. \ud574\ub2f9 query\uac00 \ucc38\uc870\ud558\ub294 object\uc5d0 DML \ub4f1\uc758 \ubcc0\uacbd\uc774 \uc77c\uc5b4\ub098\ub294 \uacbd\uc6b0\uc774\ub2e4.<\/p>\n
<\/p>\n
<\/p>\n
RAC \ud658\uacbd\uc5d0\uc11c result cache\uc758 cache-hit\uc740 local event\uc774\uc9c0\ub9cc, invalidation\uc740 global event\uc774\ub2e4. \uc774\ub294 read consistency\uc758 \ubb38\uc81c\uc774\uae30 \ub54c\ubb38\uc774\ub2e4. \uc5b4\ub5a4 \uc758\ubbf8\uc5d0\uc11c \ubd80\ubd84\uc801\uc778 cache fusion\uc774 \uc81c\uacf5\ub41c\ub2e4\uace0\ub3c4 \ubcfc \uc218 \uc788\uaca0\ub2e4.<\/strong><\/span><\/p>\n <\/p>\n
<\/p>\n
SQL Query Result Cache\uc758 \ubaa8\ub2c8\ud130\ub9c1<\/strong><\/p>\n <\/p>\n
DBMS_RESULT_CACHE<\/p>\n
<\/p>\n
DBMS_RESULT_CACHE \ud328\ud0a4\uc9c0\ub294 result cache\uc5d0 \ub300\ud55c \uc815\ubcf4\ub098 \ud1b5\uacc4\ub294 \ubb3c\ub860 \uac01\uc885 \uad00\ub9ac \uc791\uc5c5\ub3c4 \uc218\ud589\ud560 \uc218 \uc788\ub3c4\ub85d \ud558\ub294 \ub2e4\uc591\ud55c \ud504\ub85c\uc2dc\uc800\ub4e4\uc744 \uc81c\uacf5\ud55c\ub2e4. \uc0ac\uc6a9 \uc608\ub294 \uc608\uc81c\uc5d0\uc11c \ub2e4\ub8e8\uae30\ub85c \ud55c\ub2e4.<\/p>\n
RESULT_CACHE Views<\/p>\n
\ub2e4\uc74c\uc758 View\ub4e4\uc774 \uc81c\uacf5\ub41c\ub2e4. \uc5ed\uc2dc \uc0ac\uc6a9 \uc608\ub294 \uc608\uc81c\uc5d0\uc11c \ub2e4\ub8e8\uae30\ub85c \ud55c\ub2e4.<\/p>\n
(G)V$RESULT_CACHE_STATISTICS<\/p>\n
(G)V$RESULT_CACHE_MEMORY<\/p>\n
(G)V$RESULT_CACHE_OBJECTS<\/p>\n
(G)V$RESULT_CACHE_DEPENDENCY<\/p>\n
<\/p>\n
<\/p>\n
SQL Query Result Cache \uc0ac\uc6a9 \uc608<\/strong><\/p>\n <\/p>\n
\ud658\uacbd \uc810\uac80 \ubc0f \ucd08\uae30\ud654<\/p>\n
\uba3c\uc800 \uad00\ub828 parameter \uac12\ub4e4\uc744 \uc810\uac80\ud574 \ubcf4\uace0, result cache\ub97c \ucd08\uae30\ud654\ud55c \ud6c4 \uadf8 \ub0b4\uc5ed\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. Result cache\uc758 \ucd08\uae30\ud654\uc640 \uadf8 \ud604\ud669 \ud30c\uc545\uc740 DBMS_RESULT_CACHE \ud328\ud0a4\uc9c0\ub97c \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n
<\/p>\n
SQL> connect \/ as sysdba\r\nConnected.\r\nSQL> SET FEEDBACK 1\r\nSQL> SET NUMWIDTH 10\r\nSQL> SET LINESIZE 150\r\nSQL> SET TRIMSPOOL ON\r\nSQL> SET TAB OFF\r\nSQL> SET PAGESIZE 1000\r\nSQL> SET SERVEROUTPUT ON\r\nSQL>\r\nSQL> show parameter result_cache_mode\r\nNAME TYPE VALUE\r\n------------------------------------ ---------------------- ------------------------------\r\nresult_cache_mode string MANUAL\r\nSQL> show parameter memory_target\r\nNAME TYPE VALUE\r\n------------------------------------ ---------------------- ------------------------------\r\nmemory_target big integer 412M\r\nSQL> show parameter result_cache_max_size\r\nNAME TYPE VALUE\r\n------------------------------------ ---------------------- ------------------------------\r\nresult_cache_max_size big integer 1056K \u2013 default; memory_target \uc758 \uc57d0.25%\r\nSQL> show parameter result_cache_max_result\r\nNAME TYPE VALUE\r\n------------------------------------ ---------------------- ------------------------------\r\nresult_cache_max_result integer 5\r\nSQL>\r\nSQL> execute dbms_result_cache.flush; -- result cache \ub97c flush\r\nPL\/SQL procedure successfully completed.\r\nElapsed: 00:00:00.01\r\nSQL> alter system flush shared_pool;\r\nSystem altered.\r\nElapsed: 00:00:00.34\r\nSQL> execute dbms_result_cache.memory_report; -- result cache \ud604\ud669 \ud30c\uc545\r\nR e s u l t C a c h e M e m o r y R e p o r t\r\n[Parameters]\r\nBlock Size = 1K bytes -- \ub0b4\ubd80\uc801\uc73c\ub85c 1KB \ub2e8\uc704\ub85c \uad00\ub9ac (not configurable)\r\nMaximum Cache Size = 1056K bytes (1056 blocks) -- result_cache_max_size \uc5d0 \ub300\uc751\r\nMaximum Result Size = 52K bytes (52 blocks) -- result_cache_max_result \uc5d0 \ub300\uc751\r\n[Memory]\r\nTotal Memory = 5132 bytes [0.003% of the Shared Pool]\r\n... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]\r\n... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] -- 0; \ud604\uc7ac cache \ub41c result \uac00 \uc5c6\uc74c\r\nPL\/SQL procedure successfully completed.\r\nElapsed: 00:00:00.05\r\nSQL><\/pre>\n <\/p>\n
\uc0ac\uc804 \ud655\uc778<\/p>\n
Result caching\uc774 \uc9c0\uc815\ub41c SQL\uc774 \uc5b4\ub5bb\uac8c \uc2e4\ud589\ub420 \uac83\uc778\uc9c0\ub97c execution plan\uc744 \ud1b5\ud574 \ubbf8\ub9ac \uc54c\uc544\ubcf4\uc790. \ud604\uc7ac\u00a0 RESULT_CACHE_MODE = MANUAL\uc774\ubbc0\ub85c \ud574\ub2f9 SQL\uc5d0 result_cache \ud78c\ud2b8\ub97c \uc8fc\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n
<\/p>\n
SQL> connect hr\/hr\r\nConnected.\r\nSQL>\r\nSQL> explain plan for\r\n2 select \/*+ result_cache *\/ * from departments;\r\nExplained.\r\nElapsed: 00:00:00.30\r\nSQL>\r\nSQL> set echo off\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------\r\nPlan hash value: 4167016233\r\n-------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 27 | 540 | 3 (0)| 00:00:01 |\r\n| 1 | RESULT CACHE | ggq8ztnxqw8ft4ucg3art21sjm | | | | |\r\n| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------------------------\r\nResult Cache Information (identified by operation id):\r\n------------------------------------------------------\r\n1 - column-count=4; dependencies=(HR.DEPARTMENTS); name=\"select \/*+ result_cache *\/ * from\r\ndepartments\"\r\n14 rows selected.\r\nElapsed: 00:00:02.17\r\nSQL><\/pre>\n <\/p>\n
\u201cRESULT CACHE\u201d\ub77c\ub294 \uc0c8\ub85c\uc6b4 row source operation\uc774 \uc2e4\uc81c operation\uc758 \uc0c1\uc704\uc5d0 position\ub428\uc744 \ubcfc \uc218 \uc788\ub2e4. \ub610\ud55c explain plan \uba85\ub839\uc774 result cache\uc5d0 \ub300\ud55c \ucd94\uac00\uc801\uc778 \uc815\ubcf4\ub97c \uc81c\uacf5\ud568\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4. \uc544\ub798\ub294 \ub610 \ub2e4\ub978 query\uc774\ub2e4. \uc774\ubc88\uc5d0\ub294 \ucd5c\uc0c1\uc704 \ub808\ubca8\uc774 \uc544\ub2cc inline view query block\uc5d0 result caching\uc744 \uc9c0\uc815\ud574 \ubcf8\ub2e4.<\/p>\n
<\/p>\n
SQL> connect hr\/hr\r\nConnected.\r\nSQL>\r\nSQL> explain plan for\r\n2 select department_name, emp_count\r\n3 from (select \/*+ result_cache *\/ department_id, count(*) emp_count from employees group by department_id) e,\r\n4 departments d\r\n5 where e.department_id = d.department_id;\r\nExplained.\r\nElapsed: 00:00:00.12\r\n\r\nSQL>\r\nSQL> set echo off\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------\r\nPlan hash value: 523547400\r\n--------------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n--------------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 11 | 462 | 7 (29)| 00:00:01 |\r\n| 1 | MERGE JOIN | | 11 | 462 | 7 (29)| 00:00:01 |\r\n| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |\r\n| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |\r\n|* 4 | SORT JOIN | | 11 | 286 | 5 (40)| 00:00:01 |\r\n| 5 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |\r\n| 6 | RESULT CACHE | 7n7dsf9ukwqcv7cwbupmmdntaj | | | ||\r\n| 7 | HASH GROUP BY | | 11 | 33 | 4 (25)| 00:00:01 |\r\n| 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)|00:00:01 |\r\n--------------------------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n4 - access(\"E\".\"DEPARTMENT_ID\"=\"D\".\"DEPARTMENT_ID\") filter(\"E\".\"DEPARTMENT_ID\"=\"D\".\"DEPARTMENT_ID\")\r\n\r\nResult Cache Information (identified by operation id):\r\n------------------------------------------------------\r\n6 - column-count=2; dependencies=(HR.EMPLOYEES); name=\"select \/*+ result_cache *\/ department_id, count(*) emp_count from employees group by departm ent_id\"\r\n26 rows selected.\r\nElapsed: 00:00:00.09\r\nSQL><\/pre>\nInline view \ub808\ubca8\ub85c\ub3c4 result caching\uc774 \uc801\uc6a9\ub428\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n
\uc2e4\uc81c \uc218\ud589<\/p>\n
\uc774\uc81c\ub294 \uc704\uc758 \ub450 query\ub97c \uc2e4\uc81c\ub85c \uc2e4\ud589\ud574 \ubcf4\uc790.<\/p>\n
<\/p>\n
SQL> connect hr\/hr\r\nConnected.\r\nSQL>\r\nSQL> select \/*+ result_cache *\/ * from departments;\r\nDEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID\r\n------------- ------------------------------------------------------------ ---------- -----------\r\n10 Administration 200 1700\r\n20 Marketing 201 1800\r\n30 Purchasing 114 1700\r\n40 Human Resources 203 2400\r\n50 Shipping 121 1500\r\n60 IT 103 1400\r\n70 Public Relations 204 2700\r\n80 Sales 145 2500\r\n90 Executive 100 1700\r\n100 Finance 108 1700\r\n110 Accounting 205 1700\r\n120 Treasury 1700\r\n130 Corporate Tax 1700\r\n140 Control And Credit 1700\r\n150 Shareholder Services 1700\r\n160 Benefits 1700\r\n170 Manufacturing 1700\r\n180 Construction 1700\r\n190 Contracting 1700\r\n200 Operations 1700\r\n210 IT Support 1700\r\n220 NOC 1700\r\n230 IT Helpdesk 1700\r\n240 Government Sales 1700\r\n250 Retail Sales 1700\r\n260 Recruiting 1700\r\n270 Payroll 1700\r\n27 rows selected.\r\nElapsed: 00:00:00.05\r\nSQL>\r\nSQL> select department_name, emp_count\r\n2 from (select \/*+ result_cache *\/ department_id, count(*) emp_count\r\n3 from employees\r\n4 group by department_id) e, departments d\r\n5 where e.department_id = d.department_id;\r\nDEPARTMENT_NAME EMP_COUNT\r\n------------------------------------------------------------ ----------\r\nAdministration 1\r\nMarketing 2\r\nPurchasing 6\r\nHuman Resources 1\r\nShipping 45\r\nIT 5\r\nPublic Relations 1\r\nSales 34\r\nExecutive 3\r\nFinance 6\r\nAccounting 2\r\n11 rows selected.\r\nElapsed: 00:00:00.05\r\nSQL>\r\nSQL> set echo off\r\nSQL><\/pre>\n <\/p>\n
\uc704\uc758 \uc2e4\ud589\uc5d0 \uad00\ub828\ud558\uc5ec \uae30\ub85d\ub418\ub294 result cache \uad00\ub828 \ud1b5\uacc4 \uc815\ubcf4\ub294 V$RESULT_CACHE_STATISTICS\uc73c\ub85c\ubd80\ud130 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n
<\/p>\n
SQL> connect \/ as sysdba\r\nConnected.\r\nSQL> col name format a55\r\nSQL> select * from v$result_cache_statistics;\r\nID NAME VALUE\r\n---------- ------------------------------------------------------- ----------\r\n1 Block Size (Bytes) 1024\r\n2 Block Count Maximum 1056\r\n3 Block Count Current 32\r\n4 Result Size Maximum (Blocks) 52\r\n5 Create Count Success 2 \r\n6 Create Count Failure 0\r\n7 Find Count 0\r\n8 Invalidation Count 0\r\n9 Delete Count Invalid 0\r\n10 Delete Count Valid 0\r\n10 rows selected.\r\nElapsed: 00:00:00.02\r\nSQL><\/pre>\n\uc704 \ub450 SQL\uc740 \ucc98\uc74c \uc218\ud589\ub418\ub294 \uac83\uc774\uc5c8\uc73c\ubbc0\ub85c \uc2e4\ud589\uacfc \ub3d9\uc2dc\uc5d0 \uc0c8\ub86d\uac8c result cache\uc5d0 \uae30\ub85d\ub418\uc5c8\uc744 \uac83\uc774\ub2e4. \uc774 \uacb0\uacfc\ub294 \uc704\uc758 \uc870\ud68c\uc640 \uac19\uc774 \u201cCreate Count Success = 2\u201d\ub85c \ub098\ud0c0\ub09c\ub2e4. \uc774\ubc88\uc5d0\ub294 \ub3d9\uc77c\ud55c \ub450 SQL\uc744 \ub2e4\uc2dc \ud55c\ubc88 \uc2e4\ud589\uc2dc\ucf1c \ubcf8\ub2e4.<\/p>\n
<\/p>\n
SQL> connect hr\/hr\r\nConnected.\r\nSQL>\r\nSQL> select \/*+ result_cache *\/ * from departments;\r\nDEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID\r\n------------- ------------------------------------------------------------ ---------- -----------\r\n10 Administration 200 1700\r\n20 Marketing 201 1800\r\n30 Purchasing 114 1700\r\n40 Human Resources 203 2400\r\n50 Shipping 121 1500\r\n60 IT 103 1400\r\n70 Public Relations 204 2700\r\n80 Sales 145 2500\r\n90 Executive 100 1700\r\n100 Finance 108 1700\r\n110 Accounting 205 1700\r\n120 Treasury 1700\r\n130 Corporate Tax 1700\r\n140 Control And Credit 1700\r\n150 Shareholder Services 1700\r\n160 Benefits 1700\r\n170 Manufacturing 1700\r\n180 Construction 1700\r\n190 Contracting 1700\r\n200 Operations 1700\r\n210 IT Support 1700\r\n220 NOC 1700\r\n230 IT Helpdesk 1700\r\n240 Government Sales 1700\r\n250 Retail Sales 1700\r\n260 Recruiting 1700\r\n270 Payroll 1700\r\n27 rows selected.\r\nElapsed: 00:00:00.01\r\nSQL>\r\nSQL> select department_name, emp_count\r\n2 from (select \/*+ result_cache *\/ department_id, count(*) emp_count\r\n3 from employees\r\n4 group by department_id) e, departments d\r\n5 where e.department_id = d.department_id;\r\nDEPARTMENT_NAME EMP_COUNT\r\n------------------------------------------------------------ ----------\r\nAdministration 1\r\nMarketing 2\r\nPurchasing 6\r\nHuman Resources 1\r\nShipping 45\r\nIT 5\r\nPublic Relations 1\r\nSales 34\r\nExecutive 3\r\nFinance 6\r\nAccounting 2\r\n11 rows selected.\r\nElapsed: 00:00:00.01\r\nSQL>\r\nSQL> set echo off\r\nSQL><\/pre>\n <\/p>\n
\ub2f9\uc5f0\ud788 \ub3d9\uc77c\ud55c \uacb0\uacfc\ub97c \uc5bb\uc9c0\ub9cc \uadf8 elapsed time\uc744 \ubcf4\uba74, \ucc98\uc74c \uc2e4\ud589\ud558\uc600\uc744 \ub54c\ubcf4\ub2e4 \uc57d 5 \ubc30\uac00 \uc88b\uc544\uc84c\uc74c\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4. \uc774\ub294 query\ub4e4\uc774 \ub2e4\uc2dc \uc2e4\ud589\ub41c \uac83\uc774 \uc544\ub2c8\ub77c \uc55e\uc11c cache\ub41c \uacb0\uacfc\ub97c result cache\ub85c\ubd80\ud130 \ubc14\ub85c \uac00\uc838\uc62c \uc218 \uc788\uc5c8\uc74c\uc744 \uc758\ubbf8\ud55c\ub2e4. \uc774\ub294 V$RESULT_CACHE_STATISTICS\ub85c\ubd80\ud130\ub3c4 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n
<\/p>\n
SQL> connect \/ as sysdba\r\nConnected.\r\nSQL> col name format a55\r\nSQL> select * from v$result_cache_statistics;\r\nID NAME VALUE\r\n---------- ------------------------------------------------------- ----------\r\n1 Block Size (Bytes) 1024\r\n2 Block Count Maximum 1056\r\n3 Block Count Current 32\r\n4 Result Size Maximum (Blocks) 52\r\n5 Create Count Success 2\r\n6 Create Count Failure 0\r\n7 Find Count 2 -- cache-hit!\r\n8 Invalidation Count 0\r\n9 Delete Count Invalid 0\r\n10 Delete Count Valid 0\r\n10 rows selected.\r\nElapsed: 00:00:00.00\r\nSQL><\/pre>\n\uc608\uc0c1\ub300\ub85c 2 \ubc88\uc758 cache-hit\uac00 \uae30\ub85d\ub418\uc5c8\uc74c\uc744 \uc54c \uc218 \uc788\ub2e4. \uc77c\ubc18\uc801\uc73c\ub85c result cache\uc758 \ud6a8\uc6a9\uc774 \uc88b\ub2e4\ub294 \uac83\uc740 \u201cFind Count\u201d\uac00 \uc0c1\ub300\uc801\uc73c\ub85c \ub192\uc740 \uac12\uc744 \ubcf4\uc774\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4. \ub3d9\uc2dc\uc5d0 result cache\uc758 sizing\uc774 \uc801\uc808\ud558\uc9c0 \ubabb\ud560 \ub54c \ubc1c\uc0dd\ud558\ub294 \u201cCreate Count Failure\u201d\uc640 \u201cDelete Count Valid\u201d\uc758 \uac12\uc740 \uc0c1\ub300\uc801\uc73c\ub85c \ub0ae\uc544\uc57c \ud560 \uac83\uc774\ub2e4.
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Result cache \uae30\ub2a5\uc740 Oracle11g\uc758 \uc131\ub2a5 \ubd84\uc57c\uc758 \uc2e0 \uae30\ub2a5\uc73c\ub85c\uc11c SQL query \ub610\ub294 PL\/SQL function\uc758 \uacb0\uacfc\ub97c \uba54\ubaa8\ub9ac \ub0b4\uc5d0 cache\ud560 \uc218 \uc788\ub294 \uae30\ub2a5\uc774\ub2e4. \uacb0\uacfc\uac00 cache \ub418\uc5b4 \uc788\ub294 query\ub97c \uc218\ud589\ud558\uac70\ub098 PL\/SQL function\uc744 \ud638\ucd9c\ud558\uba74 \ud574\ub2f9 query \ub610\ub294 function\uc744 \uc2e4\ud589\ud558\ub294 \ub300\uc2e0 cache\ub85c\ubd80\ud130 \ubc14\ub85c \uacb0\uacfc\ub97c \uac00\uc838\uc62c \uc218 \uc788\ub2e4. \ub2e4\uc2dc \ub9d0\ud574 result cache \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\uba74 \uba54\ubaa8\ub9ac\ub97c \uc870\uae08 \ub354 \uc0ac\uc6a9\ud558\ub294 \ub300\uc2e0 \uc751\ub2f5 \uc2dc\uac04\uc758 \ud604\uc800\ud55c […]<\/p>\n","protected":false},"author":1,"featured_media":2859,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[973,974],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/OracleCloudSolutions.jpg?fit=749%2C525","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3355"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3355"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3355\/revisions"}],"predecessor-version":[{"id":3357,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3355\/revisions\/3357"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2859"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3355"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"image4\"](\"https:\/\/i0.wp.com\/oracledba.zapto.org\/wordpress\/wp-content\/uploads\/2018\/02\/image4-1.png\" "\\"image4\\"")
<\/a><\/p>\n