PL\/SQL function result cache\ub294 SQL query result cache \uae30\ub2a5\uacfc \uba54\ucee4\ub2c8\uc998\uc744 \uacf5\uc720\ud55c\ub2e4.<\/p>\n
Cache \uc790\uccb4\ub3c4 shared pool \ub0b4\uc758 result cache memory \uc601\uc5ed\uc774 SQL query \uc6a9\uacfc PL\/SQL function \uc6a9\uc73c\ub85c \ub098\ub204\uc5b4\uc838 \uc788\uc744 \ubfd0\uc774\ub2e4.<\/p>\n
\ub530\ub77c\uc11c \ucc28\uc774\uc810 \ub0b4\uc9c0\ub294 \ud2b9\uc720\ud55c \uc810\ub4e4\uc744 \uc704\uc8fc\ub85c \uae30\uc220\ud558\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n
<\/p>\n
Cache-in\u00a0\u00a0<\/strong>\uc124\uc815 \ubc29\ubc95<\/strong><\/p>\n <\/p>\n Query result cache\uac00 \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\ub294 \ubc18\uba74, PL\/SQL function\uc740 \ub2e4\uc74c\uc758 \uc11c\uba85\uc744 \uc0ac\uc6a9\ud558\uc5ec \uc791\uc131\ud568\uc73c\ub85c\uc368 result caching\uc744 \uc9c0\uc815\ud558\uac8c \ub41c\ub2e4 (\uc2e4\uc81c \uc6a9\ubc95\uc740 \uc608\uc81c\uc5d0\uc11c \ub2e4\ub8e8\uae30\ub85c \ud55c\ub2e4).<\/p>\n RESULT_CACHE \uc808<\/p>\n RELIES_ON \uc808: dependent object \uba85\uc2dc<\/p>\n <\/p>\n Recursive function \uc5ed\uc2dc cache\ub420 \uc218 \uc788\ub2e4. \ub9cc\uc77c cache\ub41c recursive function\uc774 \ud638\ucd9c\ub41c\ub2e4\uba74 \uc77c\uccb4\uc758 \uc7ac\uadc0\uc801 \ud638\ucd9c\uc774 \uc0dd\ub7b5\ub420 \uac83\uc774\ub2e4.<\/p>\n <\/p>\n \uc81c\uc57d \uc0ac\ud56d<\/strong><\/p>\n <\/p>\n \ud55c\ud3b8 PL\/SQL function\uc758 caching\uc5d0\ub3c4 \uc5ed\uc2dc \uc81c\uc57d \uc0ac\ud56d\ub4e4\uc774 \uc788\ub2e4.<\/p>\n <\/p>\n \ub2e4\uc74c\uc740 result caching\uc774 \uc801\uc6a9\ub418\uc9c0 \uc54a\uc740 \uacbd\uc6b0\ub4e4\uc774\ub2e4:<\/strong><\/p>\n \ub370\uc774\ud130 \ud0c0\uc785 \uc0c1\uc758 \uc81c\ud55c: IN parameter \uc911 BLOB, CLOB, NCLOB, REF CURSOR, Collection, Object, Record \ud0c0\uc785\uc774 \uc788\ub294 \uacbd\uc6b0.<\/p>\n \uadf8\ub9ac\uace0 Return \ud0c0\uc785\uc774 BLOB, CLOB, NCLOB, REF CURSOR, Object \uc774\uac70\ub098<\/p>\n BLOB, CLOB, NCLOB, REF CURSOR, Object \ud0c0\uc785\uc744 \ud3ec\ud568\ud558\ub294 Record \ub610\ub294 Collection\uc778 \uacbd\uc6b0 OUT\/IN OUT parameter\ub97c \uac00\uc9c4 \uacbd\uc6b0.<\/p>\n <\/p>\n Invoker\u2019s right \uc73c\ub85c \uc815\uc758\ub41c \uacbd\uc6b0 \ub610\ub294 anonymous block \ub0b4\uc5d0\uc11c \uc815\uc758\ub41c \uacbd\uc6b0<\/p>\n <\/p>\n \uc704 \uacbd\uc6b0\uc5d0 \ud574\ub2f9\ud558\ub294 \ud568\uc218\ub97c RESULT_CACHE \uc808\uacfc \ud568\uc384 \uc0dd\uc131\ud558\uba74 \uc624\ub958\uac00 \ubc1c\uc0dd\ud55c\ub2e4. \ub2e4\uc74c\uc740 \uadf8 \uc624\ub958 \uc608\uc774\ub2e4.<\/p>\n <\/p>\n LINE\/COL ERROR<\/p>\n ——– —————————————————————–<\/p>\n 0\/0 PL\/SQL: Compilation unit analysis terminated<\/p>\n 1\/39 PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters<\/p>\n <\/p>\n <\/p>\n \uc8fc\uc758 \uc0ac\ud56d<\/strong><\/p>\n <\/p>\n \ub2e4\uc74c\uc758 \uacbd\uc6b0 result caching\uc744 \uc0ac\uc6a9\ud558\uc9c0 \ub9d0\ub358\uac00, \uc544\ub2c8\uba74 \ubcf4\ub2e4 \uc2e0\uc911\ud558\uac8c \ud568\uc218\ub97c \uc791\uc131\ud574\uc57c \ud55c\ub2e4:<\/p>\n Side-effect\ub97c \uac00\uc9c4 function. \uc774 \uacbd\uc6b0 \uadf8 \uacb0\uacfc\ub9cc\uc744 cache\ud55c\ub2e4\ub294 \uac83\uc740 \ubb34\uc758\ubbf8\ud55c \uc77c\uc77c \uc218 \uc788\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n \ud574\ub2f9 session\uc5d0 specific\ud55c \uc124\uc815\/application context\uc5d0 \uc758\uc874\ud558\ub294 \ud568\uc218. \uc608\ub97c \ub4e4\uc5b4 \ub0a0\uc9dc\ub97c \ubc18\ud658\ud558\ub294 \ud568\uc218\uac00<\/p>\n \uc788\ub294\ub370 \ub2e4\uc74c\uacfc \uac19\uc774 \ucf54\ub529\ub418\uc5c8\ub2e4\uace0 \ud558\uc790 – return to_char(some_date); \uc774 \uacbd\uc6b0 \ud574\ub2f9 \uc138\uc158\uc758<\/p>\n NLS_DATE_FORMAT\uc5d0 \ub530\ub77c \uadf8 \uacb0\uacfc\uac00 \uacc4\uc18d \ub2ec\ub77c\uc9c8 \uc218 \uc788\uc73c\ubbc0\ub85c cache\uc5d0 \uadf8\ub9ac \uc801\ud569\ud55c \uacbd\uc6b0\ub294 \uc544\ub2c8\ub2e4.<\/p>\n <\/p>\n \uc8fc\uc758 \uc0ac\ud56d\uacfc \uc81c\uc57d \uc0ac\ud56d\uc740 \ub2e4\ub8e8\uc5b4\uc9c0\ub294 \ubc29\ubc95\uc774 \ub2e4\ub974\uae30\ub294 \ud558\uc9c0\ub9cc \ub9e5\ub77d\uc740 \ube44\uc2b7\ud558\ub2e4\uace0 \ubcfc \uc218 \uc788\ub2e4. \uc608\ub97c \ub4e4\uc5b4 OUT parameter\ub97c \uac16\ub294 \ud568\uc218\ub294 \uc5b4\ub5a4 \uc758\ubbf8\uc5d0\uc11c side-effect\ub97c \uac16\uace0 \uc788\ub2e4\uace0 \ub9d0\ud560 \uc218 \uc788\ub2e4. \uadf8\ub807\ub2e4\uba74 \uc65c \uc774\uac83\uc740 \uc8fc\uc758 \uc0ac\ud56d\uc774 \uc544\ub2cc \uc81c\uc57d \uc0ac\ud56d\uc778\uac00? \uadf8 \uc774\uc720\ub294 \uadf8 \uacbd\uc6b0 \ud568\uc218 \uc11c\uba85\ub9cc\uc73c\ub85c \uac04\ub2e8\ud788 \uccb4\ud06c\ud560 \uc218 \uc788\uae30 \ub54c\ubb38\uc774\ub77c\uace0 \ubcfc \uc218 \uc788\uc744 \uac83\uc774\ub2e4.<\/p>\n \uc774\uc640 \uac19\uc740 \ub17c\uc758\ub294 query result cache\uc640 \ube44\uad50\ud574\ubcf4\uba74 \ub354\uc6b1 \ubd84\uba85\ud574\uc9c4\ub2e4. \uc55e\uc11c \uae30\uc220\ud588\ub4ef\uc774 query result cache\uc5d0\uc11c\ub294 \ub108\ubb34 \u201c\uc77c\uc2dc\uc801\uc778\u201d \uacb0\uacfc\ub294 cache\ub418\uc9c0 \uc54a\uc73c\uba70, \ub610 \uadf8\ub807\uac8c \ud558\ub294 \uac83\uc774 \uc5b4\ub835\uc9c0 \uc54a\ub2e4. \uc608\ub97c \ub4e4\uc5b4 SQL \ubb38\uc7a5 \ub0b4\uc5d0 sysdate \ud568\uc218\uc5d0 \ub300\ud55c \ud638\ucd9c\uc774 \uc788\ub290\ub0d0\ub9cc\uc744 \uc810\uac80\ud558\uba74 \ub41c\ub2e4. \ud558\uc9c0\ub9cc PL\/SQL \ucf54\ub4dc\uc758 symantics \uccb4\ud06c\ub294 SQL\uc5d0 \ube44\ud574 \ud6e8\uc52c \uc5b4\ub835\ub2e4.<\/p>\n \ub530\ub77c\uc11c PL\/SQL function\uc758 result caching\uc740 SQL query\uc758 \uacbd\uc6b0\uc5d0 \ube44\ud574 \ubcf4\ub2e4 \ubbf8\ubb18\ud55c \uce21\uba74\uc774 \uc788\ub2e4. \uc704\uc5d0 \uc608\ub85c \ub4e0 \ub0a0\uc9dc \uad6c\ud558\uae30 \ud568\uc218\uc758 \uacbd\uc6b0, \ub0a0\uc9dc\ub97c \uadf8\ub0e5 DATE\ub85c \ubc18\ud658\ud558\ub3c4\ub85d \ud558\ub358\uc9c0, \uc544\ub2c8\uba74 parameter\ub85c \ud3ec\ub9f7 \ubb38\uc790\uc5f4\uc744 \ubc1b\ub3c4\ub85d \ud558\ub294 \ub4f1\uc758 \ucf54\ub529\uc744 \uc2e0\uc911\ud558\uac8c \uad6c\uc0ac\ud574\uc57c \ud558\ub294 \uac83\uc774\ub2e4.<\/p>\n <\/p>\n Cache-hit<\/strong><\/p>\n \u201c\ub3d9\uc77c\ud55c \ud568\uc218, \ub3d9\uc77c\ud55c parameter\u201d\uac00 cache-hit\uc758 \uc870\uac74\uc774\ub2e4. \uc5ec\uae30\uc11c parameter\ub294 \uc9c1\uc811\uc801\uc73c\ub85c\ub294 function\uc758 parameter\ub97c \uc758\ubbf8\ud55c\ub2e4. \ud55c\uac00\uc9c0 \uc8fc\uc758\uc810\uc740 parameter\uc758 \ub3d9\uc77c\uc131\uc774 \u2018=\u2019 \uc5f0\uc0b0\uc790\uc5d0 \ube44\ud574 \ubcf4\ub2e4 \uc5c4\uaca9\ud558\ub2e4\ub294 \uac83\uc774\ub2e4. \uc608\ub97c \ub4e4\uc5b4 PL\/SQL \ucf54\ub4dc \ub0b4\uc5d0\uc11c\ub294 \u2018A\u2019 = \u2018A \u2018 \uc774\uc9c0\ub9cc \ub450 \uac12\uc774 parameter\ub85c \ub4e4\uc5b4\uc654\uc744 \ub54c\uc5d0\ub294 \ub3d9\uc77c\ud55c parameter\ub85c \ucde8\uae09\ub418\uc9c0 \uc54a\ub294\ub2e4. \uc989, \ub450 parameter\uac00 \ub3d9\uc77c\ud558\uae30 \uc704\ud574\uc11c\ub294 bit for bit\ub85c \ub3d9\uc77c\ud574\uc57c \ud55c\ub2e4.<\/p>\n <\/p>\n PL\/SQL Function Result Cache \uc0ac\uc6a9 \uc608<\/strong><\/p>\n \ud658\uacbd \uc810\uac80 \ubc0f \ucd08\uae30\ud654<\/p>\n <\/p>\n \ud568\uc218 \uc0dd\uc131<\/strong><\/p>\n <\/p>\n <\/p>\n \ud568\uc218 \ud638\ucd9c \u2013 1 \ucc28<\/strong><\/p>\n \uba3c\uc800 \ub2e4\uc74c\uacfc \uac19\uc774 \ud638\ucd9c\ud558\uace0,<\/p>\n DBMS_RESULT_CACHE. MEMORY_REPORT\ub97c \uc774\uc6a9\ud558\uc5ec \ud604\ud669\uc744 \ud30c\uc545\ud574\ubcf8\ub2e4.<\/p>\n <\/p>\n <\/p>\n \uc704 \uc2e4\ud589\uc5d0 \uc758\ud574 \ucd1d 3 block\uc758 cache\uac00 \ud560\ub2f9\ub418\uc5c8\uc74c\uc744 \ubcfc \uc218 \uc788\ub2e4. \uc65c cache entry\uac00 3\uac1c\uc77c\uae4c? \uc774\ub294 V$RESULT_CACHE_OBJECTS\ub85c\ubd80\ud130 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n <\/p>\n <\/p>\n \ud558\ub098\ub294 \ud568\uc218 \uc2e4\ud589 \uacb0\uacfc\uc774\uace0, \ub098\uba38\uc9c0 \ub450 \uac1c\ub294 dependent object\uc5d0 \ub300\ud55c \uc815\ubcf4\uc774\ub2e4. Dependency\ub294 RELIES_ON\uc73c\ub85c \uba85\uc2dc\ud55c HR.EMPLOYEES \ud14c\uc774\ube14\uc740 \ubb3c\ub860, \ud568\uc218\uc758 \uc815\uc758 \uc790\uccb4\ub3c4 \ud3ec\ud568\ud55c\ub2e4. SCAN_COUNT = 0 \uc784\uc740 \uc9c0\uae08 \ucc98\uc74c cache\ub418\uc5c8\uc73c\uba70 \uc544\uc9c1 \ucc38\uc870\ub418\uc9c0 \uc54a\uc558\uc74c\uc744 \uc758\ubbf8\ud55c\ub2e4.<\/p>\n \uc774\ubc88\uc5d0\ub294 V$RESULT_CACHE_STATISTICS\ub85c cross-check\ub97c \ud574\ubcf4\uc790.<\/p>\n <\/p>\n \ud568\uc218 \ud638\ucd9c \u2013 2 \ucc28<\/strong><\/p>\n <\/p>\n <\/p>\n \ub3d9\uc77c\ud55c \ud568\uc218\ub97c \ub2e4\uc2dc \uc2e4\ud589\uc2dc\ucf30\ub354\ub2c8 elapsed time\uc774 \ud06c\uac8c \uac10\uc18c\ub418\uc5c8\uc74c\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4. \uc774\uc81c \ub2e4\uc2dc \ub2e4\ub978 \uc815\ubcf4\ub97c \ud1b5\ud574 cross check\ub97c \ud574\ubcf4\uc790.<\/p>\n <\/p>\n <\/p>\n \uacb0\ub860\u00a0\ubc0f\u00a0\ud65c\uc6a9\u00a0\ubc29\uc548<\/b><\/p>\n <\/p>\n Result cache \uc5d0\u00a0\uc758\ud574\u00a0\uc131\ub2a5\u00a0\ud5a5\uc0c1\u00a0\ud6a8\uacfc\ub97c\u00a0\ub204\ub9ac\uae30\u00a0\uc704\ud574\uc11c\ub294\u00a0SQL query \ub610\ub294\u00a0PL\/SQL function\uc774\u00a0\ub2e4\uc74c\uc758 \uc131\uaca9\uc744\u00a0\ubaa8\ub450\u00a0\uac00\uc9c0\uace0\u00a0\uc788\uc5b4\uc57c\u00a0\ud55c\ub2e4.<\/p>\n \uacb0\uacfc\uac00\u00a0\ube44\uad50\uc801\u00a0\ubcc0\uce58\u00a0\uc54a\uc744\u00a0\uac83. <\/strong><\/p>\n \ub2e4\uc2dc\u00a0\ub9d0\ud574\u00a0dependent object \uac00\u00a0\uc8fc\ub85c\u00a0read-only\uc774\uac70\ub098, read-mostly\uc77c\u00a0\uac83.<\/strong><\/p>\n \uc790\uc8fc\u00a0\uc218\ud589\ub420\u00a0\uac83.<\/strong><\/p>\n <\/p>\n \ud55c\u00a0\uac00\uc9c0\ub97c\u00a0\ub367\ubd99\uc778\ub2e4\uba74\u00a0\ud574\ub2f9\u00a0query\/function\uc774\u00a0\ub9ce\uc740\u00a0row\ub4e4\uc744\u00a0access\ud558\ub418\u00a0\uacb0\uacfc\uc758\u00a0\ud06c\uae30\ub294\u00a0\uadf8\ub9ac\u00a0\ud06c\uc9c0\u00a0\uc54a\uc544\uc57c\u00a0\ud560\u00a0\uac83\uc774\ub2e4.<\/p>\n <\/p>\n \uc774\ub294\u00a0\uacb0\uad6d\u00a0result cache\uc758\u00a0\ud6a8\uacfc\ub294\u00a0application\uc5d0\u00a0\ub530\ub77c\u00a0\ud06c\uac8c\u00a0\ub2ec\ub77c\uc9c8\u00a0\uc218\u00a0\uc788\uc74c\uc744\u00a0\uc758\ubbf8\ud55c\ub2e4.<\/p>\n <\/p>\n \ub530\ub77c\uc11c\u00a0result cache\ub97c\u00a0\uc774\uc6a9\ud558\ub294\u00a0\ud29c\ub2dd\u00a0\uc5ed\uc2dc\u00a0\ubcf4\ud1b5\uc758\u00a0\ud29c\ub2dd\uacfc\u00a0\ub9c8\ucc2c\uac00\uc9c0\ub85c\u00a0application \ubd84\uc11d\uc73c\ub85c\ubd80\ud130\u00a0\uc2dc\uc791\ub418\uc5b4\uc57c\u00a0\ud560\u00a0\uac83\uc774\ub2e4.<\/strong><\/p>\n \ub610\u00a0\ud55c\uac00\uc9c0\u00a0\uc774\uc288\ub294\u00a0usability\uc774\ub2e4.<\/p>\n <\/p>\n RESULT_CACHE_MODE = MANUAL\uc778\u00a0\uacbd\uc6b0\uc5d0\ub294\u00a0\uac01\uac01\uc758\u00a0SQL\uc5d0\u00a0result_cache \ud78c\ud2b8\ub97c\u00a0\uc8fc\uc5b4\uc57c\u00a0\ud55c\ub2e4. <\/strong><\/span><\/p>\n \ub2e4\uc2dc\u00a0\ub9d0\ud574\u00a0application\uc758\u00a0\uc218\uc815\uc774\u00a0\ud544\uc694\ud55c\u00a0\uac83\uc774\ub2e4. \ud558\uc9c0\ub9cc\u00a0\uc774\ub294\u00a0BMT \ub4f1\uc758\u00a0\uc0c1\ud669\uc5d0\uc11c\ub294\u00a0\uc885\uc885\u00a0\ud5c8\uc6a9\ub418\uc9c0\u00a0\uc54a\ub294\ub2e4. <\/strong><\/span><\/p>\n <\/p>\n \ub530\ub77c\uc11c\u00a0\uc774\u00a0\uae30\ub2a5\uc758\u00a0\ud65c\uc6a9\uc744\u00a0\uc704\ud574\uc11c\ub294\u00a0\uc0ac\uc6a9\uc790\uc640\uc758\u00a0\ucda9\ubd84\ud55c\u00a0\ud611\uc758\uac00\u00a0\uc788\uc5b4\uc57c\u00a0\ud560\u00a0\uac83\uc774\ub2e4.<\/p>\n \ud55c\ud3b8\u00a0\uadf8\ub807\ub2e4\uace0\u00a0RESULT_CACHE_MODE = FORCE\ub85c\u00a0\uc8fc\ub294\u00a0\uac83\uc740\u00a0\ub610\u00a0\ub2e4\ub978\u00a0\uc758\ubbf8\uc5d0\uc11c\u00a0\ubd80\ub2f4\uc2a4\ub7fd\ub2e4.<\/p>\n <\/p>\n \ubaa8\ub4e0\u00a0query\/function\ub4e4\uc774\u00a0\ubb34\uc870\uac74, \uc989\u00a0dependent object\uc5d0\u00a0\ub300\ud55c\u00a0DML \ube48\ub3c4\ub97c\u00a0\uace0\ub824\ud558\uc9c0\u00a0\uc54a\uace0, \uc790\uae30\uc758\u00a0result\ub97c\u00a0cache\ud558\uace0\uc790\u00a0\ud55c\ub2e4\uace0\u00a0\uc0c1\uc0c1\ud574\ubcf4\uc790. \uc774\u00a0\uacbd\uc6b0\u00a0\uc131\ub2a5\uc774\u00a0\uc88b\uc544\uc9c0\ub294\u00a0\uac83\ubcf4\ub2e4\ub294\u00a0\uc624\ud788\ub824\u00a0\uc548\u00a0\uc88b\uc544\uc9c8\u00a0\uc18c\uc9c0\uac00\u00a0\ub9ce\uc744\u00a0\uac83\uc774\ub2e4.<\/p>\n \uacb0\ub860\uc744\u00a0\ub0b4\ub9ac\uba74\u00a0\uc798\u00a0\uc0ac\uc6a9\ud55c\ub2e4\uba74\u00a0\uc131\ub2a5\u00a0\ud5a5\uc0c1\uc5d0\u00a0\ud06c\uac8c\u00a0\uae30\uc5ec\ud560\u00a0\uc218\u00a0\uc788\ub294\u00a0\uae30\ub2a5\uc774\ub77c\ub294\u00a0\uac83\uc774\ub2e4.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" PL\/SQL function result cache\ub294 SQL query result cache \uae30\ub2a5\uacfc \uba54\ucee4\ub2c8\uc998\uc744 \uacf5\uc720\ud55c\ub2e4. Cache \uc790\uccb4\ub3c4 shared pool \ub0b4\uc758 result cache memory \uc601\uc5ed\uc774 SQL query \uc6a9\uacfc PL\/SQL function \uc6a9\uc73c\ub85c \ub098\ub204\uc5b4\uc838 \uc788\uc744 \ubfd0\uc774\ub2e4. \ub530\ub77c\uc11c \ucc28\uc774\uc810 \ub0b4\uc9c0\ub294 \ud2b9\uc720\ud55c \uc810\ub4e4\uc744 \uc704\uc8fc\ub85c \uae30\uc220\ud558\ub3c4\ub85d \ud55c\ub2e4. Cache-in\u00a0\u00a0\uc124\uc815 \ubc29\ubc95 Query result cache\uac00 \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\ub294 \ubc18\uba74, PL\/SQL function\uc740 \ub2e4\uc74c\uc758 \uc11c\uba85\uc744 \uc0ac\uc6a9\ud558\uc5ec \uc791\uc131\ud568\uc73c\ub85c\uc368 result caching\uc744 […]<\/p>\n","protected":false},"author":1,"featured_media":3403,"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":[976,975,973],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/Bild-Flyer-Know-how.jpg?fit=1000%2C563","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3390"}],"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=3390"}],"version-history":[{"count":4,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3390\/revisions"}],"predecessor-version":[{"id":3394,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3390\/revisions\/3394"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/3403"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3390"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}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 result_cache_max_size\r\nNAME TYPE VALUE\r\n------------------------------------ ---------------------- ------------------------------\r\nresult_cache_max_size big integer 1056K\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;\r\nPL\/SQL procedure successfully completed.\r\nElapsed: 00:00:00.02\r\nSQL> alter system flush shared_pool;\r\nSystem altered.\r\nElapsed: 00:00:00.67\r\nSQL> execute dbms_result_cache.memory_report;\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\r\nMaximum Cache Size = 1056K bytes (1056 blocks)\r\nMaximum Result Size = 52K bytes (52 blocks)\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] \u2013- \uc544\uc9c1 cache \ub41c \uac83\uc774 \uc5c6\uc74c\r\nPL\/SQL procedure successfully completed.\r\nElapsed: 00:00:00.05\r\nSQL><\/pre>\n
SQL> connect hr\/hr\r\nConnected.\r\nSQL>\r\nSQL> create or replace function EMP_COUNT(dept_no number)\r\n2 return number\r\n3 result_cache relies_on (employees) \u2013- result cache \ub97c \uc9c0\uc815\ud558\ub294 syntax\r\n4 is\r\n5 v_count number;\r\n6 begin\r\n7 select count(*) into v_count\r\n8 from employees\r\n9 where department_id = dept_no;\r\n10\r\n11 return v_count;\r\n12 end;\r\n13 \/\r\nFunction created.\r\nElapsed: 00:00:00.89\r\nSQL><\/pre>\n
SQL> connect hr\/hr\r\nConnected.\r\nSQL>\r\nSQL> select department_name, emp_count(department_id) no_of_emps\r\n2 from departments\r\n3 where department_name = 'Accounting'\r\n4 \/\r\nDEPARTMENT_NAME NO_OF_EMPS\r\n------------------------------------------------------------ ----------\r\nAccounting 2\r\n1 row selected.\r\nElapsed: 00:00:00.09\r\nSQL><\/pre>\n
SQL> SET ECHO ON\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>\r\nSQL> connect \/ as sysdba\r\nConnected.\r\nSQL>\r\nSQL> --- Establish the cache content\r\nSQL> set serveroutput on\r\nSQL> execute dbms_result_cache.memory_report\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\r\nMaximum Cache Size = 1056K bytes (1056 blocks)\r\nMaximum Result Size = 52K bytes (52 blocks)\r\n[Memory]\r\nTotal Memory = 103528 bytes [0.054% of the Shared Pool]\r\n... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]\r\n... Dynamic Memory = 98396 bytes [0.051% of the Shared Pool]\r\n....... Overhead = 65628 bytes\r\n....... Cache Memory = 32K bytes (32 blocks)\r\n........... Unused Memory = 29 blocks\r\n........... Used Memory = 3 blocks\r\n............... Dependencies = 2 blocks (2 count)\r\n............... Results = 1 blocks\r\n................... PLSQL = 1 blocks (1 count)\r\nPL\/SQL procedure successfully completed.\r\nElapsed: 00:00:00.06\r\nSQL><\/pre>\n
SQL> connect \/ as sysdba\r\nConnected.\r\nSQL>\r\nSQL> col name format a55\r\nSQL>\r\nSQL> select type, namespace,status, scan_count,name\r\n2 from v$result_cache_objects\r\n3 \/\r\nTYPE NAMESPACE STATUS SCAN_COUNT NAME\r\n-------------------- ---------- ------------------ ---------- ------------------------------------------\r\n-------------\r\nDependency Published 0 HR.EMP_COUNT\r\nDependency Published 0 HR.EMPLOYEES\r\nResult PLSQL Published 0\r\n\"HR\".\"EMP_COUNT\"::8.\"EMP_COUNT\"#fac892c7867b54c6 #1\r\n3 rows selected.\r\nElapsed: 00:00:00.01\r\nSQL><\/pre>\n
SQL> connect \/ as sysdba\r\nConnected.\r\nSQL>\r\nSQL> col name format a55\r\nSQL>\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 1 \u2013- Cache-in!\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.00\r\nSQL><\/pre>\n
SQL> connect hr\/hr\r\nConnected.\r\nSQL>\r\nSQL> select department_name, emp_count(department_id) no_of_emps\r\n2 from departments\r\n3 where department_name = 'Accounting'\r\n4 \/\r\nDEPARTMENT_NAME NO_OF_EMPS\r\n------------------------------------------------------------ ----------\r\nAccounting 2\r\n1 row selected.\r\nElapsed: 00:00:00.00\r\nSQL><\/pre>\n
SQL> connect \/ as sysdba\r\nConnected.\r\nSQL>\r\nSQL> col name format a55\r\nSQL>\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 1\r\n6 Create Count Failure 0\r\n7 Find Count 1 \u2013- 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>\r\nSQL> connect \/ as sysdba\r\nConnected.\r\nSQL>\r\nSQL> col name format a55\r\nSQL>\r\nSQL> select type, namespace,status, scan_count,name\r\n2 from v$result_cache_objects\r\n3 \/\r\nTYPE NAMESPACE STATUS SCAN_COUNT NAME\r\n-------------------- ---------- ------------------ ---------- ------------------------------------------ -------------\r\nDependency Published 0 HR.EMP_COUNT\r\nDependency Published 0 HR.EMPLOYEES\r\nResult PLSQL Published 1 \u2013- cache-hit!\r\n\"HR\".\"EMP_COUNT\"::8.\"EMP_COUNT\"#fac892c7867b54c6 #1\r\n3 rows selected.\r\nElapsed: 00:00:00.01\r\nSQL><\/pre>\n