{"id":4648,"date":"2021-11-04T13:38:48","date_gmt":"2021-11-04T04:38:48","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=4648"},"modified":"2021-11-04T13:42:39","modified_gmt":"2021-11-04T04:42:39","slug":"oracle-query-optimizer-parameter","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4648","title":{"rendered":"Oracle Query Optimizer Parameter"},"content":{"rendered":"

\u00a0<\/strong><\/p>\n

Oracle Query Optimizer\u00a0 Parameter<\/strong><\/span><\/p>\n

 <\/p>\n

1. optimizer_max_permutations<\/strong><\/p>\n

optimizer_max_permutations\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 2000<\/p>\n

: \uc635\ud2f0\ub9c8\uc774\uc800\uac00 \uc2e4\ud589\uacc4\ud68d\uc744 \uc218\ub9bd\ud560 \ub54c \ud50c\ub79c\uc758 \uacbd\uc6b0\uc758 \uc218 \ub0b4\uc5d0\uc11c \uc2e4\ud589\uacc4\ud68d\uc744 \uc218\ub9bd\ud558\uac8c \ub41c\ub2e4.<\/p>\n

\uc774\ub7ec \ud55c \uacbd\uc6b0 \ud50c\ub79c\uc774 \ud6e8\uc52c \uc88b\uc740 \uacbd\uc6b0\uac00 \uc788\ub354\ub77c\ub3c4, \uadf8 \ud50c\ub79c\uc740 \uc801\uc6a9\uc774 \uc548\ub420 \uc218 \uc788\ub2e4.<\/p>\n

8i\uae4c\uc9c0\ub294 Default\uac00 80000 , 9i\ubd80\ud130 Default\uac00 2000\u00a0 .<\/p>\n

alter session set optimizer_max_permutations = 80000 ;<\/p>\n

 <\/p>\n

2. optimizer_index_cost_adj <\/strong><\/p>\n

default : 100<\/strong><\/p>\n

\uc774 \ud30c\ub77c\uba54\ud130\ub294 1~10000\uc0ac\uc774\uc758 \uac12\uc744 \uc124\uc815\ud560\uc218 \uc788\uc73c\uba70 Index access \uc640 Full Table Scan \uc5d0\uc11c \uc0ac\uc6a9\ub418 \ub294 Physical I\/O \uc758 Cost\uc758 \uc0c1\ub300\uc801\uc778 \ube44\uc728\uc744 \uc124\uc815\ud558\ub294 \ud30c\ub77c\uba54\ud130\uc774\ub2e4.<\/p>\n

100\uc778\uacbd\uc6b0\uc5d0\ub294 \ub450\uac1c\uc758 access \ubaa8\ub450 \ub3d9\uc77c\ud55c \ube44\uc728\ub85c Cost\ub97c \uacc4\uc0b0\ud558\uac8c\ub418\uba70 50\uc73c\ub85c \uc124\uc815\ud558\uc5ec Index access\ub97c \ud558\ub294 \uacbd\uc6b0 \uae30\uc874\uc758 cost\uc758 1\/2\ub85c cost\ub97c \uacc4\uc0b0\ud558\uac8c \ub418\uc5b4, Index access \ubc29\uc2dd\uc73c\ub85c execution plan\uc774 \uc218\ub9bd\ub418\uc5b4\uc9c8 \ud655\ub960\uc774 \ub192\uc544\uc9c0\uac8c \ub41c\ub2e4.<\/p>\n

Optimizer_mode\ub97c first_rows\ub85c \uc124\uc815\ud558\ub294 \uacbd\uc6b0\uc5d0\ub294 \ub0b4 \ubd80\uc801\uc73c\ub85c optimizer_index_cost_adj \ub294 10\uc73c\ub85c \uacc4\uc0b0\ub418\uc5b4\uc9c4\ub2e4.<\/p>\n

\ub530\ub77c\uc11c Index access\ub97c \uc774\uc6a9\ud558\uc5ec execution plan\uc744 \uc218\ub9bd\ud560 \ud655\ub960\uc774 \ub192\uc544\uc9c0\uac8c \ub418\uba74 Join\uc778 \uacbd\uc6b0\uc5d0\ub294 Nested Loops Join\uc73c\ub85c execution plan\uc774 \uc218\ub9bd\ub420 \uac00\ub2a5\uc131\uc774 \ub192\uc544\uc9c0\uac8c \ub41c\ub2e4.<\/p>\n

 <\/p>\n

3. optimizer_index_caching <\/strong><\/p>\n

default : 0<\/p>\n

\uc774 \ud30c\ub77c\uba54\ud130\ub294 0 ~ 99 \uc0ac\uc774\uc758 \uac12\uc744 \uc124\uc815 \ud560 \uc218 \uc788\uc73c\uba70, 0\uc77c \uacbd\uc6b0\uc5d0\ub294 Index\ub97c \uc774\uc6a9\ud574\uc11c Access\ub418\ub294 block\ub4e4\uc774 SGA\uc758 Buffer Cache \uc601\uc5ed\uc5d0\uc11c \ucc3e\uc744 \uc218 \uc788\ub294 \ube44\uc728\uc774 0% \ub780 \uc758\ubbf8\uc774\ub2e4.<\/p>\n

\uc989 \ubaa8\ub4e0 index access \ub294 DISK I\/O \ub97c \ubc1c\uc0dd\ud558\uc5ec Physical Reads \ub97c \uc218\ud589 \ud55c \ud6c4\uc5d0 Buffer cache\ub85c\ubd80\ud130 Logical reads\ub97c \uc218\ud589\ud55c\ub2e4\ub294 \uc758\ubbf8\uac00 \ub41c\ub2e4.<\/p>\n

\uc774 \ud30c\ub77c\uba54\ud130\ub294 CBO \uc5d0\uc11c Index block \uc744 access \ud560 \ub54c \uc18c \uc694\ub418\ub294 Cost\ub97c \uacc4\uc0b0\ud558\uae30 \uc704\ud55c \ube44\uc728\ub85c\uc11c \uc0ac\uc6a9 \ub418\uc5b4\uc9c4\ub2e4.<\/p>\n

 <\/p>\n

4. optimizer_dynamic_sampling<\/strong><\/p>\n

< 10g\uc5d0\uc11c\uc758 RBO Optimizer Mode\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub294\uac00? ><\/p>\n

— Database Level\uc5d0\uc11c RBO \uc9c0\uc6d0\ud558\uc9c0 \uc54a\uc73c\uba70, Session Level\uc5d0\uc11c \/*+ Rule *\/ \ud78c\ud2b8\uc5d0 \uc758\ud574\uc11c \uc77c\ubd80\uc758 RBO Path\uac00 \uc9c0\uc6d0\uc774 \ub418\uace0 \uc788\uc74c.<\/p>\n

\uc774\ud6c4 \ubc84\uc802\uc5d0\uc11c\ub294 RBO\ub97c \uc9c0\uc6d0\ud558\uc9c0 \uc54a\uc744 \uac83\uc784.<\/p>\n

RBO\ub294 \ud558\uc704 \ubc84\uc802(v7.3\uc774\uc802)\uacfc\uc758 \ud638\ud656\uc131\uc744 \uc704\ud558\uc5ec \uc9c0\uc6d0\ud558\ub294 \uac83\uc774\uba70, 10g\uc758 init Parameter\uc5d0 \uc758\ud574\uc11c \uae30\ubcf8\uc801\uc73c\ub85c \ud1b5\uacc4\uc815\ubcf4\uac00 \uc5c6\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c Plan \ud574\uc11d\ub3c4 RBO\ub85c \ub418\uc9c0 \uc54a\uc73c\uba70, CBO \ub85c \ud574\uc11d\uc774 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

< Init Parameter ><\/p>\n

– optimizer_dynamic_sampling = 2 (default)<\/p>\n

\uc774 \ud30c\ub77c\ubbf8\ud130\uac00\uc758 \uac12\uc774 2\ub85c \uc124\uc815\ub418\uc5b4 \uc788\uc744 \uacbd\uc6b0 Query parsing time\uc5d0 64 blocks\uac00 Query Optimization\uc744 \uc704\ud574\uc11c Sampling \ub418\uc5b4 \uc9c4\ub2e4.<\/p>\n

\uae30\ubcf8\uc801\uc73c\ub85c \ud65c\uc131\ud654 \ub418\uc5b4 \uc788\uc73c\uba70, \uc544\ub798\uc640 \uac19\uc774 \uc124\uc815\ud560 \uacbd\uc6b0\uc5d0\ub294 \ube44\ud65c\uc131\ud654 \uc2dc\ud0ac \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

< \ubcc0\uacbd\ubc29\ubc95 ><\/p>\n

– optimizer_dynamic_sampling = 0<\/p>\n

– optimizer_features_enable = (9.0.1 or \uc774\ud558 \ubc84\uc804)<\/p>\n

 <\/p>\n

5. _optim_peek_user_binds=FALSE <\/strong><\/p>\n

: _optim_peek_user_binds=TRUE\uc77c \uacbd\uc6b0 Bind Value\uac00 \uc788\ub294 SQL\uc758 \ud574\uc11d\uacfc FALSE\uc77c \uacbd\uc6b0\uc5d0 \ud574\uc11d\ub418 \ub294 \uac83\uc5d0 \ub9ce\uc740 \ucc28\uc774\uac00 \uc788\ub2e4.<\/p>\n

TRUE\uc77c \uacbd\uc6b0 Bind Value\uc5d0 \uc778\ub371\uc2a4\uac00 \uc788\uace0, \ud574\ub2f9 SQL\uc774 Bind Value\ub97c \uc774\uc6a9\ud574\uc11c \ud574\uc11d\uc774 \ub418\uc5b4\uc57c \ud558\ub294 \uacbd\uc6b0 Query Optimizer\uac00 Bind Value\uc758 Column Histogram\uc744 \ucc38\uc870\ud558 \uc5ec SQL\ubb38\uc744 \ud574\uc11d\ud558\uace0 \uc2e4\ud589\ud558\uac8c \ub428.<\/p>\n

\uc774\ub54c, \ud3c9\uc0c1\uc2dc \uc870\ud68c\uac00 \ub418\uc9c0 \uc54a\ub294 \uc870\uac74\uc73c\ub85c Binding \uc774 \ub420 \uacbd\uc6b0 \ud3c9\uc0c1\uc2dc\uc758 PLAN \uacfc \uc0c1\uc774\ud558\uac8c \ud574\uc11d\ub420 \uc218 \uc788\uc73c\uba70, \ud574\ub2f9 SQL\uc758 Cursor\uac00 Aging Out \ub418\uc9c0 \uc54a\ub294 \ub3d9\uc548 \uc5d0\ub294 PLAN\uc774 \uc720\uc9c0\ub418\uac8c \ub41c\ub2e4.<\/p>\n

Aging Out \ub41c \uc774\ud6c4\uc5d0\ub294 \ub610 \ub2e4\uc2dc Column Histogram\uc744 \ucc38\uc870\ud558\uc5ec PLAN\uc744 \uc138\uc6b0\uac8c \ub418\uc5b4 \uc774\uc804\uacfc\ub294 \uc0c1\uc774\ud55c \uc2e4\ud589\uacc4\ud68d\uc774 \uc218\ub9bd\ub418\uc5b4 SQL\uc774 \uc2e4\ud589\ub420 \uc218 \uc788\ub2e4. False\uc77c \uacbd\uc6b0 \uc5d0\ub294 \uc77c\ubc18\uc801\uc778 Query Optimizing\uc744 \ud558\uac8c \ub41c\ub2e4.<\/p>\n

(\ud544\uc218\uc801\uc6a9\uc0ac\ud56d)<\/p>\n

alter session set “_optim_peek_user_binds”=false ;<\/p>\n

 <\/p>\n

6. _b_tree_bitmap_plans = FALSE<\/strong><\/p>\n

: Query Optimizer\uac00 SQL \ud574\uc11d\ud560 \ub54c Where\uc803\uc5d0 \uc5ec\ub7ec \uc870\uac74\uc774 \uc788\uace0, \ud574\ub2f9 \uc870\uac74 \uceec\ub7fc\ub4e4\uc5d0 Index \uac00 \uac01\uac01 \uc0dd\uc131\ub418\uc5b4 \uc788\uc744 \uacbd\uc6b0 B*tree Index \ub97c Bitmap\uc73c\ub85c conversion \ud558\uc5ec PLAN\uc744 \uc218\ub9bd\ud558\uc5ec \uc2e4\ud589\ud568.<\/p>\n

\uc774\ub7f4 \uacbd\uc6b0 TYPE \uc774\ub098 Code\uc131 \uceec\ub7fc\uc758 \uacbd\uc6b0 B*tree Index range scan\uc73c\ub85c \ud574\uc11d\ub418\ub294 \uacbd\uc6b0\ubcf4\ub2e4 \uc131\ub2a5\uc774 \ub098\uc744 \uc218 \uc788\uc73c\ub098 \uc77c\ubc18\uc801\uc73c\ub85c \uc131\ub2a5\uc774 \uc800\ud558\ub418\ub294 \uacbd\uc6b0\uac00 \ub354 \ub9ce\uc74c.(\ud544\uc218\uc801\uc6a9\uc0ac\ud56d)<\/p>\n

 <\/p>\n

7. optimizer_mode = ‘FIRST_ROWS_100’<\/strong><\/p>\n

: 10g R1\uc5d0\uc11c\ub294 optimizer_mode\ub97c first_rows\ub85c \uc124\uc815\uacfc \uad00\ub826\ub41c Bug\uc774 \uc788\uc5c8\uc74c.<\/p>\n

\uadf8\ub9ac\uace0, \uc628\ub77c\uc778 (OLTP) \ud658\uacbd\uc5d0\uc11c\ub294 First_rows \uc124\uc815\uc774 \uc544\ub2cc First_rows_100\uc815\ub3c4 \uc138\ud305\ud558\ub294 \uac83\uc774 \uc720\ub9ac\ud568.<\/p>\n

 <\/p>\n

8. _optimizer_sortmerge_join_enabled = FALSE<\/strong><\/p>\n

: Merge Join Cartesian(Merge Join)\uc744 \uc5c6\uc560\uae30 \uc704\ud55c \ud30c\ub77c\ubbf8\ud130 \uc138\ud305\uc73c\ub85c, Cartesian Product\ub294 Join Ker\uac00 \uc5c6\uc774 Join\uc774 \ubc1c\uc0dd\ud560 \uacbd\uc6b0 \ubc1c\uc0dd\ub418\ub294 \uac83\uc774 \uc815\uc0c1\uc774\ub098 \ube44\uc815\uc0c1\uc801\uc73c\ub85c Merge Join Cartesian\uc774 \ubc1c\uc0dd\ub418\uc5b4 SQL\ub4e4\uc758 \uc2e4\ud589\uacc4\ud68d\uc774 \ube44\uc815\uc0c1\uc801\uc73c\ub85c \uc218\ub9bd \ubc0f \uc2e4\ud589\ub418\uc5b4 \uc131\ub2a5\uc800\ud558\uac00 \ub9ce\uc774 \ubc1c\uc0dd\ud558\uc5ec \uc801\uc6a9\uc744 \ud568.<\/p>\n

\uc8fc\uc758\ud560 \uc810\uc740 cartesian Product\uac00 \ubc1c\uc0dd\ud560 \uacbd\uc6b0 Nested Loop\ub85c PLAN\uc5d0 \ud574\uc11d\ub418\ubbc0\ub85c, PLAN \ud574\uc11d \ud560 \ub54c\uc5d0 \uc720\uc758\ud558\uc5ec\uc57c \ud568. (\ud544\uc218\uc801\uc6a9\uc0ac\ud56d)<\/p>\n

 <\/p>\n

9. _optimizer_skip_scan_enabled = FALSE<\/strong><\/p>\n

: Index Skip Scan\uc774 \ub418\uc9c0 \uc54a\uac8c \ud558\uae30\uc704\ud55c \ud30c\ub77c\ubbf8\ud130\uc784.<\/p>\n

-> Index Skip Scan\uc774 \ud544\uc694\ud55c \uacbd\uc6b0\uc5d0\ub294 10g \ubd80\ud130 \uc9c0\uc6d0\ub418\ub294 opt_param() \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\uc5ec SQL Level\uc5d0\uc11c Index Skip Scan\uc774 \ub418\uac8c\ub054 \ubc14\uafd4\uc8fc\uba74 \ub428.<\/p>\n

( \/*+ opt_param(‘_OPTIMIZER_SKIP_SCAN_ENABLED’,’FALSE’) *\/ )<\/p>\n

-> Index Skip Scan\uc774 False\uc77c \uacbd\uc6b0 Skip Scan\uc774 \ub418\uc9c0 \uc54a\uc9c0\ub9cc, Index Column\uc778 \uacbd\uc6b0\uc5d0 Index Filter \uac00 \ub418\uae30 \ub54c\ubb38\uc5d0 \uc131\ub2a5\uc0c1 \ub9ce\uc740 \uc601\ud5a5\uc740 \uc5c6\uc74c.<\/p>\n","protected":false},"excerpt":{"rendered":"

\u00a0 Oracle Query Optimizer\u00a0 Parameter   1. optimizer_max_permutations optimizer_max_permutations\u00a0\u00a0\u00a0\u00a0\u00a0 integer\u00a0\u00a0\u00a0\u00a0 2000 : \uc635\ud2f0\ub9c8\uc774\uc800\uac00 \uc2e4\ud589\uacc4\ud68d\uc744 \uc218\ub9bd\ud560 \ub54c \ud50c\ub79c\uc758 \uacbd\uc6b0\uc758 \uc218 \ub0b4\uc5d0\uc11c \uc2e4\ud589\uacc4\ud68d\uc744 \uc218\ub9bd\ud558\uac8c \ub41c\ub2e4. \uc774\ub7ec \ud55c \uacbd\uc6b0 \ud50c\ub79c\uc774 \ud6e8\uc52c \uc88b\uc740 \uacbd\uc6b0\uac00 \uc788\ub354\ub77c\ub3c4, \uadf8 \ud50c\ub79c\uc740 \uc801\uc6a9\uc774 \uc548\ub420 \uc218 \uc788\ub2e4. 8i\uae4c\uc9c0\ub294 Default\uac00 80000 , 9i\ubd80\ud130 Default\uac00 2000\u00a0 . alter session set optimizer_max_permutations = 80000 ;   2. optimizer_index_cost_adj […]<\/p>\n","protected":false},"author":1,"featured_media":3402,"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":[1222,1223,1221,1220,1218,1181,1219],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/1-know-how-1.jpg?fit=937%2C450","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4648"}],"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=4648"}],"version-history":[{"count":3,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4648\/revisions"}],"predecessor-version":[{"id":4651,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4648\/revisions\/4651"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/3402"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4648"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4648"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4648"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}