<\/p>\n
Oracle Cursors \ub294 library cache (shared SQL area) \uc5d0 \ud560\ub2f9\ub41c memory \uacf5\uac04\uc73c\ub85c LRU \uc54c\uace0\ub9ac\uc998\uc5d0 \uc758\ud574\uc11c \uad00\ub9ac\ub41c\ub2e4.<\/p>\n
<\/p>\n
< Cursor \uc815\ubcf4 ><\/p>\n
<\/p>\n
– \uad6c\ubb38 \ubd84\uc11d\ub41c \uba85\ub839\ubb38 ( \uc815\uc801 , \ub3d9\uc801 \ubc0f \uc21c\ud658 SQL, \ud504\ub85c\uc2dc\uc800, \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ud2b8\ub9ac\uac70 \ub4f1 \uc758 \ud504\ub85c\uadf8\ub7a8 \ub2e8\uc704) : P-Code<\/p>\n
– Execution-Plan<\/p>\n
– \ucc38\uc870 \uac1d\uccb4 \ubaa9\ub85d (\uc6d0\ubcf8 TEXT)<\/p>\n
<\/p>\n
<\/p>\n
————– Session cached cursors Hit Ratio ————<\/p>\n
<\/p>\n
select round((hit.value\/tot.value)*100, 5) session_cache_hit_ratio<\/p>\n
from v$sysstat tot<\/p>\n
, v$sysstat hit<\/p>\n
, v$sysstat cnt<\/p>\n
where tot.name = ‘parse count (total)’<\/p>\n
and hit.name = ‘session cursor cache hits’<\/p>\n
and cnt.name = ‘session cursor cache count’ ;<\/p>\n
<\/p>\n
session_cache_hit_ratio \ub294 \ucd5c\uc18c 50% \uc774\uc0c1 \uc774\uc5b4\uc57c \ud569\ub2c8\ub2e4.<\/span><\/strong><\/p>\n <\/p>\n select gethitratio, pinhitratio<\/p>\n from v$librarycache<\/p>\n where namespace=’SQL AREA’ ;<\/p>\n <\/p>\n gethitratio, pinhitratio > 90% \uc774\uc5b4\uc57c \ud558\uace0, sum(pins) \/ sum(reloads) <= 1% \uc774\uc5b4\uc57c \ud55c\ub2e4.<\/span><\/strong><\/p>\n \u00a0<\/span><\/strong><\/p>\n ————– Session cached cursors Hit Ratio ————<\/p>\n <\/p>\n 1. open_cursors<\/span><\/strong><\/p>\n :\u00a0 \ud55c \uc138\uc158\uc774 \uc5f4 \uc218 \uc788\ub294 \ucd5c\ub300 cursor \uac1c\uc218<\/p>\n <\/p>\n 2. session_cached_cursors<\/span><\/strong><\/p>\n : \uc5f4\ub824\uc788\ub294 \uc138\uc158\uc774 \uac00\uc9c8 \uc218 \uc788\ub294 \ucd5c\ub300 Cursors \uac1c\uc218<\/p>\n SESSION_CACHED_CURSORS \ud30c\ub77c\ubbf8\ud130\ub294 \ub3d9\uc77c\ud55c SQL\uc744 \ubc18\ubcf5\uc218\ud589(3\ud68c\uc774\uc0c1) \ud558\ub294 \uacbd\uc6b0\uc5d0 \uc720\ub9ac\ud558\uba70, \ubcf4\ud1b5 softer parse \ub77c\uace0 \ud55c\ub2e4.<\/p>\n \ubaa8\ub4c8\ubcc4\ub85c \ud2b9\uc815 SQL \ub4e4\uc744 \ubc18\ubcf5 \uc218\ud589\ud558\ub294 \uc138\uc158\ub4e4\uc5d0 \uc124\uc815\uc2dc SOFT \ud30c\uc2f1\ubd80\ud558\ub97c \uac10\uc18c \uc2dc\ucf1c \uc900\ub2e4.<\/p>\n \uc2dc\uc2a4\ud15c \uc774 \ub0b4\ubd80 \uc218\ud589\ud558\ub294 ReCursive SQL \ub3c4 \ud3ec\ud568\ub418\ubbc0\ub85c \ucd5c\uc18c 30 \uc774\ud558\ub85c \uc124\uc815\ud558\ub294\uac83\uc740 \ud6a8\uacfc\uac00 \uc5c6\uc73c\uba70 \ubcf4 \ud1b5 50 \uc774\uc0c1\uc744 \uad8c\uc7a5\ud55c\ub2e4.<\/p>\n \ub3d9\uc77c\ud55c SQL\uc774 \ub3d9\uc77c\uc138\uc158\uc5d0\uc11c 3\ud68c \uc774\uc0c1 \uc218\ud589 \uc2dc PGA \uc5d0 \ud574\ub2f9 SQL \uc758 Handle Address \ub97c Caching \ud558\uac8c \ub418\uba70, Caching \uc815\ubcf4\ub97c \ud1a0\ub300\ub85c \ud574\ub2f9 Bucket\uc758 \ubaa8\ub4e0 Handle\uc744 \ubaa8\ub450 \uac80\uc0c9\ud558\uc9c0 \uc54a\uace0,<\/p>\n Caching \ub418\uc5b4 \uc788\ub294 Handle Address\ub97c \uac00\uc9c0\uace0 \ud574\ub2f9 Handle \uc758 LCO\uc5d0 Direct \ud558\uac8c \ud0d0\uc0c9\uc744 \ud558\uac8c \ub418\uc5b4 \uc77c\ubc18\uc801\uc778 Soft Parsing \ubcf4\ub2e4 \uac1c\uc120\uc758 \ud6a8\uacfc\uac00 \ub354 \ud06c\ub2e4.<\/p>\n \ub2e8, parse count \uac00 \uc790\uccb4\uac00 \uc904\uc5b4\ub4dc\ub294 \uac83\uc740 \uc544\ub2c8\uba70, \ud0d0\uc0c9 \ud558\ub294 \uc2dc\uac04 \uc989 parsing time \uc774 \uc808\uac10\ud558\ub294 \ud6a8\uacfc\uac00 \uc788\ub2e4.<\/p>\n Session_cached_cursors \uc758 \uc124\uc815\uc740 PGA \uc5d0 \ud574\ub2f9 \uc138\uc158\uc758 SQL(3\ud68c \uc774\uc0c1 \uc218\ud589)\uc744 Caching \ud558\ub294 \uac83\uc73c\ub85c<\/strong><\/span>, Library cache object \ub97c pinned \ud558\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc5d0 Soft Parsing \uc774 \ubc1c\uc0dd\ud558\uac8c \ub418\ub294 \uac83\uc774\ub2e4.<\/p>\n \ubc18\uba74\uc5d0 PL\/SQL\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 Hold Cursor(Static SQL) \uc758 \uacbd\uc6b0\uc5d0\ub294 library cache object \ub97c Pinned \ud55c \uc0c1\ud0dc\uc5d0\uc11c \ubc18\ubcf5 \uc218\ud589\ub418\ubbc0\ub85c, soft parsing \uc774 \ubc1c\uc0dd \ud558\uc9c0 \uc54a\uac8c \ub418\ub294 \uac83\uc774\ub2e4.<\/p>\n <\/p>\n 3. cursor_space_for_time = true[false]<\/span><\/strong><\/p>\n : \uc138\uc158\uc5d0\uc11c \uc0ac\uc6a9\ub41c Cursor\ub97c \uc138\uc158\uc774 \ub2eb\ud790 \ub54c\uae4c\uc9c0 SGA\uc5d0 \ub0a8\uaca8\ub193\ub294\ub2e4.<\/p>\n <\/p>\n 4. cursor_shaing = [ EXACT, FORCE, SIMILAR ]<\/span><\/strong><\/p>\n : cursor_sharing(FORCE, SIMILAR) \uc744 \uc124\uc815 \uc2dc \ucc98\uc74c \uc218\ud589\ub418\ub294 literal value\ub97c bind value\ub85c \ub300\uccb4\ub97c \ud558\uac8c \ub418\ub294\ub370, \ud574\ub2f9 cursor\uac00 Memory \uc5d0\uc11c Aging out \ub418\uc9c0 \uc54a\uc744 \uacbd\uc6b0 \uc774\ud6c4 \uc218\ud589\ub418\ub294 literal value \uc5d0 \ub300\ud574\uc11c\ub294 peek at the bind \ub85c \uc218\ud589\ub418\uac8c \ub41c\ub2e4.<\/p>\n peek at the bind\ub85c \ud574\uc11d\ub418\ub294 \uac83\uc740 “_optim_peek_user_bind”=TRUE<\/strong>\ub85c \ud574\uc11d\ub418\ub294 \uac83\uacfc \ub3d9\uc77c\ud558\uac8c \ud574 \uc11d\ub418\ub294\uac83\uc774\ub2e4.<\/p>\n \uc704\uc640 \uac19\uc774 \ud574\uc11d\ub420 \uacbd\uc6b0 system level \uc5d0\uc11c cursor_sharing \ub97c \uc124\uc815\ud558\ub294 \uac83\uc740 \uc0c1\ub2f9\ud788 \uc704\ud5d8<\/span><\/strong>\ud574 \uc9c8 \uc218 \uc788\uc73c\ubbc0\ub85c, \ud574\ub2f9 \ud30c\ub77c\ubbf8\ud130 \uc138\ud305 (FORCE,SIMILAR) \uc2dc\uc5d0\ub294 \ud544\ud788 SESSION LEVEL OR SQL LEVEL \uc5d0\uc11c \uc81c\uc5b4\ub97c \ud560 \ud544\uc694\uac00 \uc788\uc74c.<\/p>\n \ubd80\uac00\uc801\uc73c\ub85c, cursor_sharing = force \ub85c \uc124\uc815\ud560 \uacbd\uc6b0\uc5d0\ub294 rownum \uc0ac\uc6a9\uc5d0 \uc8fc\uc758\ub97c \ud558\uc5ec\uc57c \ud55c\ub2e4.<\/p>\n rownum = 1 \uacfc \uac19\uc774 \ud504\ub85c\uadf8\ub7a8 \uc791\uc131\uc2dc cursor_sharing=force\ub97c \uc124\uc815\ud560 \uacbd\uc6b0 rownum = :b1 \uacfc \uac19\uc774 Oracle \ub0b4\ubd80\uc801\uc73c\ub85c \ubcc0\uacbd\ub418\ubbc0\ub85c \uc804\uccb4\ubc94\uc704 \ucc98\ub9ac \ud6c4\uc5d0 \ud574\ub2f9 1\uac74\uc758 row \ub97c \ucd94\ucd9c\ud558\uac8c \ub41c\ub2e4.<\/p>\n \uc774 \ub54c \uc5d0\ub294 rownum <= 1 \uacfc \uac19\uc774 \ubcc0\uacbd \ud6c4 cursor_sharing=force \ub97c \uc124\uc815\ud558\uc5ec\uc57c \ud55c\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":" Oracle Cursors \ub294 library cache (shared SQL area) \uc5d0 \ud560\ub2f9\ub41c memory \uacf5\uac04\uc73c\ub85c LRU \uc54c\uace0\ub9ac\uc998\uc5d0 \uc758\ud574\uc11c \uad00\ub9ac\ub41c\ub2e4. < Cursor \uc815\ubcf4 > – \uad6c\ubb38 \ubd84\uc11d\ub41c \uba85\ub839\ubb38 ( \uc815\uc801 , \ub3d9\uc801 \ubc0f \uc21c\ud658 SQL, \ud504\ub85c\uc2dc\uc800, \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ud2b8\ub9ac\uac70 \ub4f1 \uc758 \ud504\ub85c\uadf8\ub7a8 \ub2e8\uc704) : P-Code – Execution-Plan – \ucc38\uc870 \uac1d\uccb4 \ubaa9\ub85d (\uc6d0\ubcf8 TEXT) ————– Session […]<\/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":[9],"tags":[1236,1235,209,1232,1233,1234],"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\/4658"}],"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=4658"}],"version-history":[{"count":3,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4658\/revisions"}],"predecessor-version":[{"id":4661,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4658\/revisions\/4661"}],"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=4658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4658"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}