{"id":290,"date":"2012-01-14T21:06:54","date_gmt":"2012-01-14T13:06:54","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=290"},"modified":"2014-01-14T21:12:12","modified_gmt":"2014-01-14T13:12:12","slug":"sql%e8%b0%83%e4%bc%9802-cbo%e4%bc%98%e5%8c%96%e5%99%a8%e7%9a%84%e5%8e%9f%e7%90%86","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=290","title":{"rendered":"SQL\u8c03\u4f1802–CBO\u4f18\u5316\u5668\u7684\u539f\u7406"},"content":{"rendered":"

Intorduction to the CBO Optimizer<\/p>\n

\n
    \n
  1. \u4ecb\u7ecdCBO\u4f18\u5316\u5668\u7684\u4e66:Jonathan Lewis\u5199\u7684<<Cost-Based Oracle Fundamentals>>;<\/li>\n
  2. \u7ed3\u6784\u5316\u67e5\u8be2\u8bed\u8a00\u7684\u7c7b\u578b:\n
      \n
    1. DML(Data Manipulation Language):INSERT, UPDATE, DELETE, MERGE, SELECT;<\/li>\n
    2. DDL(Data Definition Language):CREATE, DROP, ALTER, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT;<\/li>\n
    3. ESS(Environmental Sustainability Statement):DECLARE, CONNECT, OPEN, CLOSE, DESCRIBLE, WHENEVER, PREPARE, EXECUTE, FETCH;<\/li>\n
    4. TCS(Transactoin Control Statement):COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION;<\/li>\n
    5. SystemCS(System\u00a0Control Statement):ALTER SYSTEM;<\/li>\n
    6. SessionCS(Session\u00a0\u00a0Control Statement):ALTER SESSSION, SET ROLE;<\/li>\n<\/ol>\n<\/li>\n
    7. SQL\u8bed\u53e5\u7684\u5b9e\u73b0\u8fc7\u7a0b:\u76f8\u540c\u7684SQL\u8bed\u53e5\u53ef\u4ee5\u4f7f\u7528\u76f8\u540c\u7684\u6267\u884c\u8ba1\u5212; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
    8. Cursor\n
        \n
      1. PL\/SQL\u4e2d\u7684cursor:\u8bb0\u5f55\u884c\u7684rowid,\u7528\u6765\u8868\u793a\u7ed3\u679c\u96c6;<\/li>\n
      2. sql\u89e3\u6790\u8fc7\u7a0b\u4e2d\u7684cursor:\u662fSHARED POOL\u4e2d\u7684\u5185\u5b58\u5757;<\/li>\n<\/ol>\n<\/li>\n
      3. SQL\u8bed\u53e5\u7684\u5904\u7406\u8fc7\u7a0b:\n
          \n
        1. Create a cursor:\n
            \n
          1. Cursor\u662fprivate SQL area\u7684\u53e5\u67c4\u6216\u8005\u540d\u79f0;<\/li>\n
          2. \u5176\u4e2d\u5305\u542b\u4e86\u8bed\u53e5\u6267\u884c\u7684\u4fe1\u606f;<\/li>\n
          3. Cursor\u7684\u7ed3\u6784\u4e0e\u5b83\u5305\u542bSQL\u8bed\u53e5\u662f\u4e92\u76f8\u72ec\u7acb\u7684;<\/li>\n<\/ol>\n<\/li>\n
          4. Parse the statement:\n
              \n
            1. SQL\u8bed\u53e5\u901a\u8fc7\u7528\u6237\u8fdb\u7a0b\u53d1\u9001\u5230Oracle\u5b9e\u4f8b;<\/li>\n
            2. \u5728PGA\u4e2d\u68c0\u67e5\u8bed\u6cd5\u548c\u8bed\u4e49,\u7136\u540e\u68c0\u67e5\u6743\u9650,\u5206\u914dprivate SQL area,\u7136\u540e\u68c0\u67e5\u662f\u5426\u5df2\u7ecf\u5728Library Cache\u4e2d\u5b58\u5728,\u5982\u679c\u6ca1\u6709\u7684\u8bdd,\u4f1a\u628asql\u653e\u5165\u5230Shared SQL area\u4e2d,\u4ea7\u751f\u786c\u89e3\u6790;<\/li>\n
            3. \u5982\u679cSQL\u7684\u6267\u884c\u8ba1\u5212\u5df2\u7ecf\u5b58\u5728\u7684\u8bdd\u53ef\u4ee5\u88ab\u91cd\u7528;<\/li>\n<\/ol>\n<\/li>\n
            4. Describe query results:\n
                \n
              1. \u63d0\u4f9b\u4e86SELECT\u540e\u9762\u7684\u8981\u67e5\u627e\u7684\u5217\u7684\u4fe1\u606f,\u4e0e\u52a8\u6001\u6267\u884cSQL\u6709\u5173;<\/li>\n
              2. 9i\u65f6\u4f7f\u7528DBMS_SQL\u5305,\u4e4b\u540e\u4f7f\u7528EXECUTE IMMEDIATE\u6765\u52a8\u6001\u6267\u884cSQL;<\/li>\n<\/ol>\n<\/li>\n
              3. Define query output:\u5b9a\u4e49\u8981\u67e5\u627e\u7684\u5217\u7684\u4f4d\u7f6e,\u5927\u5c0f,\u6570\u636e\u7c7b\u578b\u4fe1\u606f;<\/li>\n
              4. Bind variables:\n
                  \n
                1. \u5f00\u542f\u5185\u5b58\u5730\u5740\u6765\u4fdd\u5b58\u6570\u636e\u7684\u503c;<\/li>\n
                2. \u5141\u8bb8\u5171\u4eabsql;<\/li>\n<\/ol>\n<\/li>\n
                3. Parallelize the statement:\n
                    \n
                  1. \u53ef\u4ee5\u5e76\u884c\u7684\u8bed\u53e5:SELECT, INSERT, UPDATE, MERGE, DELETE, CREATE, ALTER;<\/li>\n
                  2. 11g\u4e2d\u81ea\u52a8\u5224\u65ad\u662f\u5426\u9700\u8981\u5e76\u884c;<\/li>\n<\/ol>\n<\/li>\n
                  3. Execute the statement:\u6267\u884cSQL\u8bed\u53e5,\u4ea7\u751f\u60f3\u8981\u7684\u7ed3\u679c;<\/li>\n
                  4. Fetch rows of a query:\u4ee5\u8868\u683c\u7684\u5f62\u5f0f\u8fd4\u56de\u6570\u636e;<\/li>\n
                  5. Close the cursor:\u5173\u95ed\u6e38\u6807,PGA\u4e2d\u6307\u5411cursor\u7684\u6307\u9488\u5173\u95ed,\u4f46\u662f\u5185\u5b58\u533a\u57df\u8fd8\u662f\u88ab\u5360\u7528,\u4e4b\u540e\u53ef\u4ee5\u88ab\u8986\u76d6;<\/li>\n
                  6. \u4f7f\u7528DBMS_SQL\u5305\u53ef\u4ee5\u770b\u5230\u5b8c\u6574\u7684SQL\u6267\u884c\u7684\u8fc7\u7a0b;<\/li>\n<\/ol>\n<\/li>\n
                  7. SQL\u8bed\u53e5\u89e3\u6790\u7684\u8fc7\u7a0b; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                  8. \u9700\u8981\u4f18\u5316\u5668\u7684\u7406\u7531:\u5b83\u53ef\u4ee5\u9009\u62e9\u4e00\u79cd\u8d44\u6e90\u6d88\u8017\u6700\u5c0f\u7684\u65b9\u5f0f;<\/li>\n
                  9. \u786c\u89e3\u6790\u7684\u64cd\u4f5c\u6b65\u9aa4: \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                  10. Transformer\n
                      \n
                    1. \u4f18\u5316\u5668\u9996\u5148\u4f1a\u628a\u8bed\u53e5\u5206\u6210\u4e00\u4e2a\u4e00\u4e2a\u7684\u67e5\u8be2\u5757,\u7136\u540e\u8fdb\u884c\u8f6c\u6362;<\/li>\n
                    2. \u867d\u7136\u8fdb\u884c\u4e86\u8f6c\u6362\u64cd\u4f5c\u4ee5\u63d0\u9ad8\u6548\u7387,\u4f46\u662f\u5728\u5185\u5b58\u4e2d\u4fdd\u5b58\u7684\u8fd8\u662f\u539f\u6765\u7684SQL\u8bed\u53e5;<\/li>\n
                    3. OR\u8f6c\u6362\u4e3aUNION ALL\u64cd\u4f5c; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                    4. IN\u8f6c\u6362\u4e3a\u5185\u8fde\u63a5\u64cd\u4f5c(11g); \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                    5. IN\u6539\u5199\u4e3aexists;<\/span><\/li>\n
                    6. NOT IN\u6539\u5199\u4e3a\u5916\u8fde\u63a5+IS NULL,11g\u4e2d\u81ea\u52a8\u8f6c\u6362,10g\u4e2d\u9700\u8981\u4fee\u6539;<\/span><\/li>\n
                    7. IN\u6539\u5199\u4e3a\u5916\u8fde\u63a5+IS NOT NULL;<\/span><\/li>\n
                    8. \u89c6\u56fe\u5408\u5e76:\u67e5\u8be2\u7684\u65f6\u5019\u76f4\u63a5\u67e5\u8be2\u89c6\u56fe\u4e2d\u7684\u57fa\u8868,\u975e\u5e38\u9002\u5408\u4e8e\u89c6\u56fe\u7684\u8bb0\u5f55\u6570\u5f88\u5927,\u67e5\u8be2\u89c6\u56fe\u7684\u8bb0\u5f55\u6570\u5c0f\u7684\u60c5\u51b5; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                    9. \u89c6\u56fe\u4e0d\u5408\u5e76:\u67e5\u8be2\u65f6\u628a\u5236\u56fe\u5f53\u6210\u57fa\u8868,\u8fd9\u6837\u6548\u7387\u6bd4\u8f83\u4f4e.CBO\u4f1a\u81ea\u52a8\u5408\u5e76\u7b2c\u4e00\u5c42\u7684\u89c6\u56fe,\u6240\u4ee5\u4e0d\u8981\u4f7f\u7528\u5d4c\u5957\u89c6\u56fe;<\/li>\n
                    10. Predicate Pushing:\u628a\u6761\u4ef6\u63a8\u5230\u6700\u67e5\u8be2\u7684\u6700\u4f4e\u7aef; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                    11. \u6761\u4ef6\u7684\u4f20\u9012\u6027:employees\u7684department_id\u5217\u6ca1\u6709\u7d22\u5f15,department\u8868department_id\u5217\u662f\u4e3b\u952e,\u8f6c\u6362\u8fc7\u4e4b\u540e\u5c31\u4f1a\u5148\u8d70\u4e3b\u952e\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                    12. Cost-Based Optimizer\n
                        \n
                      1. \u7531Estimator\u548cPlan Generator\u7ec4\u6210;<\/li>\n
                      2. Estimator\u51b3\u5b9a\u6267\u884c\u8ba1\u5212\u7684\u6210\u672c\u6d88\u8017\u7684\u5efa\u8bae;\n
                          \n
                        1. \u5b83\u662f\u57fa\u4e8e\u6982\u7387\u8bba\u7684,\u7406\u8bba\u4f9d\u636e\u662f\u6570\u636e\u662f\u5747\u5300\u5206\u5e03\u7684;<\/li>\n
                        2. \u5b83\u7684\u57fa\u7840\u6570\u636e\u662f\u5b9a\u671f\u6536\u96c6\u5e76\u5b58\u653e\u5728\u6570\u636e\u5b57\u5178\u7684\u7edf\u8ba1\u4fe1\u606f;<\/li>\n<\/ol>\n<\/li>\n
                        3. Plan Generator:\n
                            \n
                          1. \u4ea7\u751f\u5404\u79cd\u4e0d\u540c\u7684\u6267\u884c\u8ba1\u5212;<\/li>\n
                          2. \u4f7f\u7528Estimator\u8ba1\u7b97\u5404\u4e2a\u6267\u884c\u8ba1\u5212\u7684\u6210\u672c;<\/li>\n
                          3. \u57fa\u4e8e\u6210\u672c\u9009\u62e9\u6700\u597d\u7684\u4f18\u5316\u5efa\u8bae;<\/li>\n
                          4. \u751f\u6210\u6700\u4f18\u7684\u6267\u884c\u8ba1\u5212;<\/li>\n<\/ol>\n<\/li>\n
                          5. OPTIMIZER_MODE\u7684\u4e24\u4e2a\u53c2\u6570:ALL_ROWS, FIRST_ROWS_n:\n
                              \n
                            1. FIRST_ROWS_n:\n
                                \n
                              1. CBO\u4f18\u5148\u8003\u8651\u5c06\u7ed3\u679c\u96c6\u4e2d\u7684\u524dN\u6761\u8bb0\u5f55\u4ee5\u6700\u5feb\u7684\u901f\u5ea6\u8fd4\u56de,\u800c\u5176\u5b83\u7684\u7ed3\u679c\u96c6\u5e76\u4e0d\u9700\u8981\u540c\u65f6\u8fd4\u56de;<\/li>\n
                              2. \u53ef\u4ee5\u4f7f\u7528\u5728BBS\u7684\u5206\u9875\u4e0a:SELECT \/*+ first_rows(10) *\/ FROM tbname;<\/li>\n
                              3. \u8fd9\u79cd\u6267\u884c\u8ba1\u5212\u5bf9\u4e8eSQL\u6574\u4f53\u7684\u6267\u884c\u65f6\u95f4\u4e0d\u662f\u6700\u5feb\u7684,\u4f46\u662f\u5728\u8fd4\u56de\u524dN\u6761\u8bb0\u5f55\u7684\u5904\u7406\u4e0a\u7edd\u5bf9\u662f\u6700\u5feb\u7684;<\/li>\n
                              4. \u4f7f\u7528\u7684\u6392\u5e8f\u5b57\u6bb5\u5fc5\u987b\u6709\u7d22\u5f15,\u5426\u5219CBO\u4f1a\u5ffd\u7565FIRST_ROWS(n),\u800c\u4f7f\u7528ALL_ROWS;<\/li>\n<\/ol>\n<\/li>\n
                              5. ALL_ROWS:\n
                                  \n
                                1. CBO\u8003\u8651\u6700\u5feb\u7684\u901f\u5ea6\u8fd4\u56de\u6240\u6709\u7684\u7ed3\u679c\u96c6,\u548cFIRST_ROWS_n\u76f8\u53cd;<\/li>\n
                                2. \u5728OLAP\u7cfb\u7edf\u4e2d\u4f7f\u7528\u8f83\u591a,\u603b\u4f53\u6548\u7387\u9ad8;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                3. Estimator\n
                                    \n
                                  1. Selectivity:\u9009\u62e9\u5ea6;\n
                                      \n
                                    1. Selectivity is the estimated proportion of a row set retrieved by a particular predicate or combination of predicates;\u9009\u62e9\u5ea6\u662f\u7531\u4e00\u4e2a\u7279\u5b9a\u7684\u8c13\u8bcd\u6216\u8005\u7ec4\u5408\u8c13\u8bcd\u68c0\u7d22\u884c\u96c6\u7684\u4f30\u8ba1\u6bd4\u4f8b;<\/li>\n
                                    2. \u8ba1\u7b97\u516c\u5f0f:Selectivity=\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\u6570\/\u603b\u8bb0\u5f55\u6570;<\/li>\n
                                    3. \u5b83\u7684\u53d6\u503c\u57280.0-1.0\u4e4b\u95f4:\n
                                        \n
                                      1. High Selectivity:\u5f97\u5230\u5927\u6bd4\u4f8b\u7684\u8bb0\u5f55\u6570;<\/li>\n
                                      2. Low Selectivity:\u5f97\u5230\u5c0f\u6bd4\u4f8b\u7684\u8bb0\u5f55\u6570;<\/li>\n<\/ol>\n<\/li>\n
                                      3. \u5982\u4f55\u83b7\u5f97Selectivity:\n
                                          \n
                                        1. \u5982\u679c\u6ca1\u6709\u7edf\u8ba1\u4fe1\u606f\u5219\u91c7\u7528\u52a8\u6001\u91c7\u6837(Dynamic Sampling);<\/li>\n
                                        2. \u5982\u679c\u6ca1\u6709\u76f4\u65b9\u56fe\u4fe1\u606f\u5219\u91c7\u7528\u884c\u5206\u5e03;<\/li>\n<\/ol>\n<\/li>\n
                                        3. \u5b58\u653e\u7edf\u8ba1\u4fe1\u606f\u7684\u89c6\u56fe:\n
                                            \n
                                          1. dba_tables;<\/li>\n
                                          2. dba_tab_statistics(NUM_ROWS, BLOCKS, AVG_ROW_LEN);<\/li>\n
                                          3. dba_tab_col_statistics(NUM_DISTINCT, DENSITY, HIGH\/LOW_VALUE);<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                          4. Cardinality:\u57fa\u6570;\n
                                              \n
                                            1. \u901a\u8fc7\u6267\u884c\u8ba1\u5212\u671f\u671b\u80fd\u68c0\u7d22\u51fa\u6765\u7684\u8bb0\u5f55\u6570;<\/li>\n
                                            2. \u8ba1\u7b97\u516c\u5f0f:Cardinality=Selectivity*\u603b\u8bb0\u5f55\u6570;<\/li>\n
                                            3. \u5bf9\u4e8ejoin, filters\u548csort\u7684\u6210\u672c\u662f\u91cd\u8981\u7684\u6307\u6807;<\/li>\n<\/ol>\n<\/li>\n
                                            4. \u4e3e\u4f8b:SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;\n
                                                \n
                                              1. employees\u8868\u7684job_id\u5217\u7684\u53d6\u503c\u4e2a\u6570\u4e3a:19; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                              2. employees\u8868\u4e2d\u7684\u8bb0\u5f55\u6570:107; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                              3. Selectivity=1\/19=0.0526315789473684,\u5373DENSITY\u7684\u503c;<\/li>\n
                                              4. Cardinality=(1\/19)*107=5.63,\u5411\u4e0a\u53d6\u6574\u4e3a6;<\/li>\n<\/ol>\n<\/li>\n
                                              5. Cost:\n
                                                  \n
                                                1. Cost\u662f\u6267\u884c\u7279\u5b9a\u7684\u8bed\u53e5\u6240\u82b1\u8d39\u7684\u6807\u51c6I\/Os\u6570\u91cf\u7684\u6700\u4f18\u8bc4\u4f30;<\/li>\n
                                                2. Cost\u7684\u5355\u4f4d\u662f\u4e00\u4e2a\u6807\u51c6\u5757\u7684\u968f\u673a\u8bfb\u53d6:1 cost unit = 1 SRds(Standardized Random Reads);<\/li>\n
                                                3. \u6267\u884c\u8ba1\u5212\u4e2dCost(%CPU):\u4e00\u6b21IO\u8bfb\u53d6\u4e00\u4e2aIO\u5757\u9700\u8981\u7684\u65f6\u95f4;<\/li>\n
                                                4. Cost\u7684\u503c\u7531\u4e09\u90e8\u5206\u7ec4\u6210; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                                                5. \u63a7\u5236\u4f18\u5316\u5668\u7684\u521d\u59cb\u5316\u53c2\u6570:\n
                                                    \n
                                                  1. CURSOR_SHARING<\/span>:SIMILAR|EXACT(default)|FORCE,\u63a7\u5236\u53ef\u4ee5\u5171\u4eabCursor\u7684SQL\u8bed\u53e5\u7c7b\u578b;<\/li>\n
                                                  2. DB_FILE_MULTIBLOCK_READ_COUNT<\/span>:\u5b83\u662f\u4e00\u4e2a\u53ef\u4ee5\u5728\u8868\u626b\u63cf\u65f6\u6700\u5c0f\u5316IO\u7684\u53c2\u6570,\u6307\u5b9a\u4e86\u5728\u987a\u5e8f\u626b\u63cf\u65f6\u4e00\u6b21IO\u64cd\u4f5c\u53ef\u4ee5\u8bfb\u53d6\u7684\u6700\u5927\u7684\u5757\u6570;(\u5728OLTP\u7cfb\u7edf\u4e2d\u4e00\u822c\u6307\u5b9a4-16,\u5728DW\u7cfb\u7edf\u4e2d\u53ef\u4ee5\u5c3d\u91cf\u8bbe\u7f6e\u7684\u5927\u4e00\u70b9);<\/li>\n
                                                  3. PGA_AGGREGATE_TARGET<\/span>:PGA\u81ea\u52a8\u7ba1\u7406\u65f6\u6307\u5b9aserver processes\u53ef\u4ee5\u4f7f\u7528\u7684PGA\u5185\u5b58\u7684\u603b\u548c;<\/li>\n
                                                  4. STAR_TRANSFORMATION_ENABLED:\u53c2\u6570\u8bbe\u7f6e\u4e3aTRUE\u65f6\u4f7f\u7528CBO\u53ef\u4ee5\u4f7f\u7528\u4f4d\u56fe\u7d22\u5f15\u7684\u7279\u6027,\u4e0d\u8fc7\u8c8c\u4f3c\u73b0\u5728\u8fd9\u4e2a\u53c2\u6570\u4e0d\u91cd\u8981;<\/li>\n
                                                  5. RESULT_CACHE_MODE:MANUAL,FORCE,11g<\/li>\n
                                                  6. RESULT_CACHE_MAX_SIZE:11g;<\/li>\n
                                                  7. RESULT_CACHE_MAX_RESULT:11g;<\/li>\n
                                                  8. RESULT_CACHE_REMOTE_EXPIRATION:11g;<\/li>\n
                                                  9. OPTIMIZER_INDEX_CACHING:\u5728Buffer Cache\u4e2d\u7f13\u5b58\u7d22\u5f15\u7684\u6bd4\u4f8b,\u9ed8\u8ba4\u4e3a0;<\/li>\n
                                                  10. OPTIMIZER_INDEX_COST_ADJ<\/span>:\u7d22\u5f15\u626b\u63cf\/\u5168\u8868\u626b\u63cf\u7684\u6210\u672c,\u9ed8\u8ba4\u4e3a100%,\u5373\u7d22\u5f15\u626b\u63cf\u6210\u672c\u7b49\u4e8e\u5168\u8868\u626b\u63cf\u6210\u672c;\u4f18\u5148\u4f1a\u9009\u62e9\u5168\u8868\u626b\u63cf;\u6bd4\u8f83\u60b2\u89c2\u7684\u914d\u7f6e;<\/li>\n
                                                  11. OPTIMIZER_FEATURES_ENABLE:\u5e0c\u671b\u542f\u7528\u54ea\u4e2a\u7248\u672c\u7684CBO;<\/li>\n
                                                  12. OPTIMIZER_MODE<\/span>:ALL_ROWS|FIRST_ROWS|FIRST_ROWS_n\n
                                                      \n
                                                    1. \u9ed8\u8ba4\u662fall_rows:\u8d44\u6e90\u6d88\u8017\u6bd4\u8f83\u5c0f;<\/li>\n
                                                    2. first_rows_n:n\u7684\u53d6\u503c\u4e3a1|10|100|1000,\u901f\u5ea6\u4f18\u5148,\u4f46\u662f\u6d88\u8017\u5f88\u5927\u7684\u8d44\u6e90;<\/li>\n<\/ol>\n<\/li>\n
                                                    3. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;<\/li>\n
                                                    4. OPTIMIZER_USE_SQL_PLAN_BASELINES;<\/li>\n
                                                    5. OPTIMIZER_DYNAMIC_SAMPLING:\u52a8\u6001\u91c7\u6837\u7684\u7279\u6027,10g\u540e\u9ed8\u8ba4\u4e3a2;<\/li>\n
                                                    6. OPTIMIZER_USE_INVISIBLE_INDEXES;<\/li>\n
                                                    7. OPTIMIZER_USE_PENDING_STATISTICS;<\/li>\n<\/ol>\n<\/li>\n
                                                    8. OPTIMIZER_INDEX_COST_ADJ\u53c2\u6570\u8bbe\u7f6e\u7684\u4f8b\u5b50:\n
                                                        \n
                                                      1. \u9996\u5148\u521b\u5efa\u8868,\u7d22\u5f15,\u5e76\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                      2. \u6253\u5f00\u6267\u884c\u8ba1\u5212,\u6267\u884c\u67e5\u8be2\u8bed\u53e5,\u9ed8\u8ba4\u7684\u503c\u4e3a100%,\u5373\u7d22\u5f15\u626b\u63cf\u6210\u672c\u7b49\u4e8e\u5168\u8868\u626b\u63cf\u6210\u672c,\u5219\u6267\u884c\u8ba1\u5212\u8d70\u5168\u8868\u626b\u63cf; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                                      3. \u4fee\u6539\u53c2\u6570\u503c\u4e3a50,\u5373\u7d22\u5f15\u626b\u63cf\u6210\u672c\u662f\u5168\u8868\u626b\u63cf\u6210\u672c\u76841\/2,\u67e5\u770b\u6267\u884c\u8ba1\u5212; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                                                      4. Selectivity\u503c\u7684\u4f8b\u5b50:\n
                                                          \n
                                                        1. \u56e0\u4e3aCBO\u662f\u57fa\u4e8e\u6570\u636e\u5747\u5300\u7684\u6982\u7387\u5206\u5e03\u7684,\u6240\u4ee5\u5b83\u4f30\u8ba1\u7684Selectivity\u662f\u4e00\u4e2a\u7406\u8bba\u503c;<\/li>\n
                                                        2. \u521b\u5efa\u4e00\u4e2a1200\u6761\u8bb0\u5f55\u7684\u8868,\u91cc\u9762\u7684\u503c\u5206\u5e03\u662f1-12,\u4ee3\u88681-12\u6708\u51fa\u751f\u7684\u4eba; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                        3. \u5982\u679c\u8981\u67e5\u627e\u67d0\u4e2a\u6708\u4efd\u51fa\u751f\u7684\u4eba,\u90a3\u4e48\u5728\u4e0d\u660e\u767d\u4efb\u4f55\u60c5\u51b5\u4e0b,\u6bcf\u4e2a\u6708\u4efd\u51fa\u751f\u7684\u4eba\u7684\u6982\u7387\u90fd\u662f1\/12,\u5373\u6709100\u4e2a\u4eba,CBO\u4e5f\u662f\u8fd9\u4e48\u601d\u8003\u95ee\u9898\u7684; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                                        4. \u5b9e\u9645\u503c\u5f80\u5f80\u8ddf\u7406\u8bba\u503c\u4e0d\u76f8\u7b26,\u4f46\u662f\u6570\u636e\u91cf\u8d8a\u5927,\u8d8a\u63a5\u8fd1; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                                                        5. 10053\u4e8b\u4ef6\u6d4b\u8bd5:\n
                                                            \n
                                                          1. \u5f00\u542f10053\u4e8b\u4ef6,\u5e76\u6267\u884c\u4e00\u6761sql\u8bed\u53e5; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                          2. \u67e5\u770b\u5f53\u524d\u7684session_id\u548cprocess_id\u6765\u786e\u5b9a\u751f\u6210\u7684\u5185\u5bb9\u662f\u653e\u5728\u54ea\u4e2audump\u6587\u4ef6\u4e2d; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                                                          3. \u67e5\u770budump\u6587\u4ef6,\u91cc\u9762\u5305\u542b\u4e86\u4e00\u4e9b\u7f29\u5199\u7684\u542b\u4e49\u548c\u5f53\u524d\u751f\u6548\u7684\u4f18\u5316\u53c2\u6570\u7684\u503c; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                                                          4. \u5173\u95ed10053\u4e8b\u4ef6; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
                                                            <\/div>\n<\/div>\n
                                                            —\u00a0Estimator\u4f8b\u5b50\u7684\u811a\u672c;<\/div>\n
                                                            SELECT * FROM hr.employees WHERE job_id = ‘SH_CLERK’;<\/div>\n
                                                            SELECT COUNT(DISTINCT job_id) FROM hr.employees;<\/div>\n
                                                            SELECT owner, table_name, column_name, num_distinct, density
                                                            \nFROM dba_tab_col_statistics
                                                            \nWHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’ AND column_name = ‘JOB_ID’;<\/div>\n
                                                            SELECT owner, table_name, num_rows, blocks, avg_row_len
                                                            \nFROM dba_tab_statistics
                                                            \nWHERE owner = ‘HR’ AND table_name = ‘EMPLOYEES’;<\/div>\n
                                                            <\/div>\n
                                                            —\u00a0OPTIMIZER_INDEX_COST_ADJ\u53c2\u6570\u7684\u4f8b\u5b50;<\/div>\n
                                                            CREATE TABLE t1 AS
                                                            \nSELECT MOD(ROWNUM, 200) n1, MOD(ROWNUM, 200) n2
                                                            \nFROM dba_objects WHERE ROWNUM <= 3000;
                                                            \nCREATE INDEX t_i1 ON t1(n1);
                                                            \nEXEC dbms_stats.gather_table_stats(USER, ‘t1’, CASCADE=>TRUE);
                                                            \nSET autotrace traceonly exp;
                                                            \nSELECT * FROM t1 WHERE n1 = 50;
                                                            \nALTER SESSION SET optimizer_index_cost_adj = 50;
                                                            \nSELECT * FROM t1 WHERE n1 = 50;<\/div>\n
                                                            <\/div>\n
                                                            — Selectivity\u7684\u4f8b\u5b50;<\/div>\n
                                                            CREATE TABLE t2(ID, month_id) AS
                                                            \nSELECT ROWNUM, trunc(dbms_random.value(1, 13))
                                                            \nFROM dba_objects WHERE ROWNUM <= 1200;
                                                            \nEXEC dbms_stats.gather_table_stats(USER, ‘t2’, CASCADE => TRUE);
                                                            \nSET autotrace traceonly exp;
                                                            \nSELECT * FROM t2 WHERE month_id = 5;
                                                            \nSELECT COUNT(*) FROM t2 WHERE month_id = 5;<\/div>\n
                                                            <\/div>\n
                                                            — 10053\u65f6\u95f4\u6d4b\u8bd5\u4f8b\u5b50;<\/div>\n
                                                            ALTER SYSTEM\/SESSION SET EVENTS ‘10053 trace name context forever, level 8’;
                                                            \nSELECT * FROM employees WHERE employee_id = 100;
                                                            \nALTER SYSTEM\/SESSION SET EVENTS ‘10053 trace name context off’;
                                                            \nSELECT s.sid, p.spid FROM v$session s
                                                            \nINNER JOIN v$process p ON s.paddr = p.addr AND
                                                            \ns.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1);<\/div>\n","protected":false},"excerpt":{"rendered":"

                                                            Intorduction to the CBO Optimizer \u4ecb\u7ecdCBO\u4f18\u5316\u5668\u7684\u4e66:Jonathan L […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[8],"class_list":["post-290","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-performance"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=290"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/290\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}