\u5173\u4e8e\u53c2\u6570optimizer_index_cost_adj\u7684\u8bbe\u7f6e
\n\u6982\u8ff0:
\n\t\u53c2\u6570optimizer_index_cost_adj\u7684\u9ed8\u8ba4\u503c\u662f100\uff0c\u503c\u57df\u662f0-10000\u7684\u4efb\u610f\u503c\u3002\u53c2\u6570\u6570\u503c\u8d8a\u4f4e\uff0c\u8d8a\u53ef\u80fd\u8ba9\u4f18\u5316\u5668\u4f7f\u7528\u7d22\u5f15\u3002<\/p>\n
\u6ce8\u610f\uff1a
\n1\uff09\u4e00\u822c\u5efa\u8bae\u5728\u4f1a\u8bdd\u7ea7\u522b\u8bbe\u7f6e\u8be5\u53c2\u6570
\n2\uff09\u5728oltp\u7cfb\u7edf\u5efa\u8bae\u4f7f\u7528\u5f88\u4f4e\u7684\u53c2\u6570\u503c\uff0c\u59825\u6216\u800510\uff0c\u4ee5\u5f3a\u5236\u4f18\u5316\u5668\u4f7f\u7528\u7d22\u5f15<\/p>\n
\u6570\u636e\u5e93\u7248\u672c\u4fe1\u606f<\/p>\n
\r\nSQL> select * from gv$version;\r\n\r\n INST_ID BANNER\r\n---------- --------------------------------------------------------------------------------\r\n 1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\n 1 PL\/SQL Release 11.2.0.3.0 - Production\r\n 1 CORE 11.2.0.3.0 Production\r\n 1 TNS for Linux: Version 11.2.0.3.0 - Production\r\n 1 NLSRTL Version 11.2.0.3.0 - Production\r\n<\/pre>\n\u53c2\u6570\u9ed8\u8ba4\u503c<\/p>\n
\r\nSQL> show parameter optimizer_index_cost_adj\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\noptimizer_index_cost_adj integer 100\r\n<\/pre>\n\u5173\u4e8e\u8be5\u53c2\u6570\u503c\u7684\u8ba1\u7b97<\/p>\n
\r\nSQL> select event,average_wait from v$system_event where event like 'db file s%read';\r\n\r\nEVENT AVERAGE_WAIT\r\n---------------------------------------------------------------- ------------\r\ndb file sequential read 1.28\r\ndb file scattered read 2.2\r\n\r\nSQL> select (1.28\/2.2)*100 pct from dual;\r\n\r\n PCT\r\n----------\r\n58.1818182\r\n<\/pre>\n\u6545\u53c2\u6570\u503c\u5e94\u8be5\u8bbe\u7f6e\u4e3a58\u5de6\u53f3<\/p>\n
\u4e0b\u8fb9\u6d4b\u8bd5\u4e00\u4e0b\u8be5\u53c2\u6570\u7684\u7528\u9014<\/p>\n
\r\nSQL> create table yallonking as select rownum id,'yallonking' name from dual connect by rownum<101;\r\n\r\nTable created.\r\n\r\nSQL> create index idx_yallonking_id on yallonking(id);\r\n\r\nIndex created.\r\n\r\nSQL> set autot traceonly\r\nSQL> select * from yallonking where id<50;\r\n\r\n49 rows selected.\r\n\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2582482548\r\n\r\n--------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n--------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 49 | 1225 | 2 (0)| 00:00:01 |\r\n|* 1 | TABLE ACCESS FULL| YALLONKING | 49 | 1225 | 2 (0)| 00:00:01 |\r\n--------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 1 - filter("ID"<50)\r\n\r\nNote\r\n-----\r\n - dynamic sampling used for this statement (level=2)\r\n\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n 10 recursive calls\r\n 0 db block gets\r\n 15 consistent gets\r\n 0 physical reads\r\n 0 redo size\r\n 1587 bytes sent via SQL*Net to client\r\n 556 bytes received via SQL*Net from client\r\n 5 SQL*Net roundtrips to\/from client\r\n 0 sorts (memory)\r\n 0 sorts (disk)\r\n 49 rows processed\r\n<\/pre>\n\u4e0b\u8fb9\u4fee\u6539\u8be5\u53c2\u6570<\/p>\n
\r\nSQL> alter session set optimizer_index_cost_adj=58;\r\n\r\nSession altered.\r\n<\/pre>\n\u7ee7\u7eed\u6267\u884c\u4e0a\u8fb9\u7684sql\uff0c\u4f1a\u53d1\u73b0\u6b64\u65f6\u8be5sql\u5f00\u59cb\u8d70\u7d22\u5f15\uff0c\u6545\u8be5\u53c2\u6570\u5728\u8fd9\u65f6\u8d77\u4e86\u4f5c\u7528\u3002<\/p>\n
\r\nSQL> select * from yallonking where id<50;\r\n\r\n49 rows selected.\r\n\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2645553055\r\n\r\n-------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 49 | 1225 | 1 (0)| 00:00:01 |\r\n| 1 | TABLE ACCESS BY INDEX ROWID| YALLONKING | 49 | 1225 | 1 (0)| 00:00:01 |\r\n|* 2 | INDEX RANGE SCAN | IDX_YALLONKING_ID | 49 | | 1 (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 2 - access("ID"<50)\r\n\r\nNote\r\n-----\r\n - dynamic sampling used for this statement (level=2)\r\n\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n 9 recursive calls\r\n 0 db block gets\r\n 16 consistent gets\r\n 0 physical reads\r\n 0 redo size\r\n 2121 bytes sent via SQL*Net to client\r\n 556 bytes received via SQL*Net from client\r\n 5 SQL*Net roundtrips to\/from client\r\n 0 sorts (memory)\r\n 0 sorts (disk)\r\n 49 rows processed\r\n<\/pre>\n\u4ee5\u4e0b\u662f\u5728\u5185\u5b58\u4e2d\u7f13\u5b58\u6570\u636e\u540e\u7684\u4e00\u4e2a\u5bf9\u6bd4\u7684\u4f8b\u5b50<\/strong><\/p>\n
\r\nSQL> alter session set optimizer_index_cost_adj=90;\r\n\r\nSession altered.\r\n\r\nSQL> select * from yallonking where id<50;\r\n\r\n49 rows selected.\r\n\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2645553055\r\n\r\n-------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 49 | 1225 | 2 (0)| 00:00:01 |\r\n| 1 | TABLE ACCESS BY INDEX ROWID| YALLONKING | 49 | 1225 | 2 (0)| 00:00:01 |\r\n|* 2 | INDEX RANGE SCAN | IDX_YALLONKING_ID | 49 | | 1 (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 2 - access("ID"<50)\r\n\r\nNote\r\n-----\r\n - dynamic sampling used for this statement (level=2)\r\n\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n 9 recursive calls\r\n 0 db block gets\r\n 16 consistent gets\r\n 0 physical reads\r\n 0 redo size\r\n 2121 bytes sent via SQL*Net to client\r\n 556 bytes received via SQL*Net from client\r\n 5 SQL*Net roundtrips to\/from client\r\n 0 sorts (memory)\r\n 0 sorts (disk)\r\n 49 rows processed\r\n\r\nSQL> alter session set optimizer_index_cost_adj=100;\r\n\r\nSession altered.\r\n\r\nSQL> select * from yallonking where id<50;\r\n\r\n49 rows selected.\r\n\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2582482548\r\n\r\n--------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n--------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 49 | 1225 | 2 (0)| 00:00:01 |\r\n|* 1 | TABLE ACCESS FULL| YALLONKING | 49 | 1225 | 2 (0)| 00:00:01 |\r\n--------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 1 - filter("ID"<50)\r\n\r\nNote\r\n-----\r\n - dynamic sampling used for this statement (level=2)\r\n\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n 0 recursive calls\r\n 0 db block gets\r\n 7 consistent gets\r\n 0 physical reads\r\n 0 redo size\r\n 1587 bytes sent via SQL*Net to client\r\n 556 bytes received via SQL*Net from client\r\n 5 SQL*Net roundtrips to\/from client\r\n 0 sorts (memory)\r\n 0 sorts (disk)\r\n 49 rows processed\r\n<\/pre>\n\u8be5\u4f8b\u5b50\u8bf4\u660e\uff0c\u5728\u5168\u8868\u626b\u63cf\u7684\u65f6\u5019\u624d\u9700\u89817\u6b21\u4e00\u81f4\u6027\u8bfb\uff0c\u4f46\u662f\u8d70\u7d22\u5f15\u786e\u670916\u6b21\u4e00\u81f4\u6027\u8bfb\u4ee5\u53ca9\u6b21\u9012\u5f52\u8c03\u7528\u3002\u56e0\u4e3a\u6d4b\u8bd5\u67e5\u8be2\u7684\u5185\u5bb9\u4e0d\u5b8c\u5168\u5728\u7d22\u5f15\u4e2d\uff0c\u6240\u4ee5\u5728\u4f7f\u7528\u7d22\u5f15\u7684\u65f6\u5019\uff0coracle\u4e0d\u4f46\u8981\u8bfb\u53d6\u6570\u636e\u5757\uff0c\u800c\u4e14\u8fd8\u5f97\u8bfb\u53d6\u7d22\u5f15\u5757\uff0c\u7ee7\u800c\u589e\u52a0\u4e86\u5757\u7684\u8bfb\u7684\u6b21\u6570\u3002<\/p>\n
\u5c0f\u7ed3\uff1a\u53c2\u6570optimizer_index_cost_adj\u4f1a\u5f71\u54cd\u6570\u636e\u5e93\u5bf9\u7d22\u5f15\u7684\u4f7f\u7528\u7684\u9009\u62e9\u6027\u3002\u4f46\u662f\uff0c\u8be5\u53c2\u6570\u7684\u8c03\u6574\u9700\u8981\u5341\u5206\u8c28\u614e\uff0c\u56e0\u4e3a\u5728oracle\u66f4\u9ad8\u7684\u7248\u672c\u4e2d\u57fa\u4e8ecost\u7684\u4f18\u5316\u5668\u7684\u9009\u62e9\u5f80\u5f80\u6bd4\u4eba\u4e3a\u505a\u51fa\u7684\u51b3\u5b9a\u66f4\u52a0\u51c6\u786e\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u5173\u4e8e\u53c2\u6570optimizer_index_cost_adj\u7684\u8bbe\u7f6e \u6982\u8ff0: \u53c2\u6570optimizer_index_c […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[87,9],"tags":[162,161,160],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/779"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=779"}],"version-history":[{"count":4,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/779\/revisions"}],"predecessor-version":[{"id":782,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/779\/revisions\/782"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=779"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=779"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=779"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}