\u4e3a\u4e86\u89e3\u51b3\u4e0a\u8ff0\u95ee\u9898\uff0cOracle\u572811g\u4e2d\u63a8\u51fa\u4e86SPM(SQL Plan Management)\u3002SPM\u662f\u4e00\u79cd\u4e3b\u52a8\u7684\u7a33\u5b9a\u6267\u884c\u8ba1\u5212\u7684\u624b\u6bb5\uff0c\u80fd\u591f\u4fdd\u8bc1\u53ea\u6709\u88ab\u9a8c\u8bc1\u8fc7\u7684\u6267\u884c\u8ba1\u5212\u624d\u4f1a\u88ab\u542f\u7528\uff0c\u5f53\u7531\u4e8e\u79cd\u79cd\u539f\u56e0(\u5982\u7edf\u8ba1\u4fe1\u606f\u7684\u53d8\u66f4)\u800c\u5bfc\u81f4\u76ee\u6807SQL\u4ea7\u751f\u4e86\u65b0\u7684\u6267\u884c\u8ba1\u5212\u540e\uff0c\u8fd9\u4e2a\u65b0\u7684\u6267\u884c\u8ba1\u5212\u5e76\u4e0d\u4f1a\u88ab\u9a6c\u4e0a\u542f\u7528\uff0c\u76f4\u5230\u5b83\u5df2\u7ecf\u88ab\u6211\u4eec\u9a8c\u8bc1\u8fc7\u5176\u6267\u884c\u6548\u7387\u4f1a\u6bd4\u539f\u5148\u6267\u884c\u8ba1\u5212\u9ad8\u624d\u4f1a\u88ab\u542f\u7528\u3002<\/p>
\u968f\u7740Oracle\u6570\u636e\u5e93\u7248\u672c\u7684\u4e0d\u6bb5\u63a8\u8fdb\uff0c\u5176CBO\u7684\u7b97\u6cd5\u3001\u529f\u80fd\u4e5f\u5728\u4e00\u76f4\u4e0d\u65ad\u8fdb\u5316\u548c\u589e\u52a0\uff0c\u6240\u4ee5\u540c\u6837\u7684SQL\u6709\u53ef\u80fd\u5728\u65b0\u7248\u672c\u7684Oralce\u6570\u636e\u5e93\u4e2d\u6267\u884c\u6548\u7387\u66f4\u9ad8\uff0c\u5982\u679c\u6211\u4eec\u4f7f\u7528\u4e86SQL Profile(\u7279\u522b\u662f\u4f7f\u7528\u4e86Manual\u7c7b\u578b\u7684SQL Profile)\u6765\u7a33\u5b9a\u76ee\u6807SQL\u7684\u6267\u884c\u8ba1\u5212\uff0c\u90a3\u5c31\u610f\u5473\u7740\u53ef\u80fd\u5931\u53bb\u4e86\u7ee7\u7eed\u4f18\u5316\u4e0a\u8ff0SQL\u7684\u6267\u884c\u6548\u7387\u7684\u673a\u4f1a\u3002\u800cSPM\u7684\u63a8\u51fa\u53ef\u4ee5\u8bf4\u5f7b\u5e95\u89e3\u51b3\u4e86\u6267\u884c\u8ba1\u5212\u7a33\u5b9a\u6027\u7684\u95ee\u9898\uff0c\u5b83\u65e2\u80fd\u4e3b\u52a8\u5730\u7a33\u5b9a\u6267\u884c\u8ba1\u5212\uff0c\u53c8\u4fdd\u7559\u4e86\u7ee7\u7eed\u4f7f\u7528\u65b0\u7684\u6267\u884c\u6548\u7387\u53ef\u80fd\u66f4\u9ad8\u7684\u6267\u884c\u8ba1\u5212\u7684\u673a\u4f1a\u3002<\/p>
\u5f53\u542f\u7528\u4e86SPM\u540e\uff0c\u6bcf\u4e00\u4e2aSQL\u90fd\u4f1a\u5b58\u5728\u5bf9\u5e94\u7684SQL Plan Baseline\uff0c\u8fd9\u4e2aSQL Plan Baseline\u91cc\u5b58\u50a8\u7684\u5c31\u662f\u8be5SQL\u7684\u6267\u884c\u8ba1\u5212\uff0c\u5982\u679c\u4e00\u4e2aSQL\u6709\u591a\u4e2a\u6267\u884c\u8ba1\u5212\uff0c\u90a3\u4e48\u8be5SQL\u5c31\u53ef\u80fd\u4f1a\u6709\u591a\u4e2aSQL Plan Baseline\uff0c\u53ef\u4ee5\u4eceDBA_SQL_PLAN_BASELINES\u4e2d\u67e5\u770b\u76ee\u6807SQL\u6240\u6709\u7684SQL Plan Baseline\u3002<\/p>
DBA_SQL_PLAN_BASELINES\u4e2d\u7684\u5217ENABLED\u548cACCEPTED\u7528\u6765\u63cf\u8ff0\u4e00\u4e2aSQL Plan Baseline\u6240\u5bf9\u5e94\u7684\u6267\u884c\u8ba1\u5212\u662f\u5426\u80fd\u88abOracle\u542f\u7528\uff0c\u53ea\u6709ENABLED\u548cACCEPTED\u7684\u503c\u5747\u4e3a\u201cYES\u201d\u7684SQL Plan Baseline\u6240\u5bf9\u5e94\u7684\u6267\u884c\u8ba1\u5212\u624d\u4f1a\u88abOracle\u542f\u7528\uff0c\u5982\u679c\u4e00\u5177SQL\u6709\u8d85\u8fc71\u4e2a\u4ee5\u4e0a\u7684SQL Plan Baseline\u7684ENABLED\u548cACCEPTED\u7684\u503c\u5747\u4e3aYES\uff0c\u5219Oracle\u4f1a\u4ece\u4e2d\u9009\u62e9\u6210\u672c\u503c\u6700\u5c0f\u7684\u4e00\u4e2a\u6240\u5bf9\u5e94\u7684\u6267\u884c\u575f\u5893\u6765\u4f5c\u4e3a\u8be5SQL\u7684\u6267\u884c\u8ba1\u5212\u3002<\/p>
\u5728Oracle 11g\u53ca\u5176\u4ee5\u4e0a\u7684\u7248\u672c\u4e2d\uff0c\u6709\u5982\u4e0b\u4e24\u79cd\u65b9\u6cd5\u53ef\u4ee5\u4ea7\u751f\u76ee\u6807SQL\u7684SQL Plan Baseline\u3002<\/p>
\u4e0b\u9762\u5206\u522b\u4ecb\u7ecd\u5982\u4f55\u81ea\u52a8\u6355\u83b7\u548c\u624b\u5de5\u7684\u65b9\u5f0f\u6765\u4ea7\u751fSQL Plan Baseline\u3002<\/p>
1 \u81ea\u52a8\u6355\u83b7SQL Plan Baseline<\/p>
\u53c2\u6570OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES\u7528\u4e8e\u63a7\u5236\u662f\u5426\u5f00\u542f\u81ea\u52a8\u6355\u83b7SQL Plan Baseline\uff0c\u5176\u9ed8\u8ba4\u503c\u4e3aFALSE\uff0c\u8868\u793a\u5728\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0cOracle\u5e76\u4e0d\u4f1a\u81ea\u52a8\u6355\u83b7SQL Plan Baseline\u3002\u8fd9\u4e2a\u53c2\u6570\u53ef\u4ee5\u5728session\u6216\u7cfb\u7edf\u7ea7\u522b\u52a8\u6001\u4fee\u6539\u3002\u5f53\u4fee\u6539\u4e3aTRUE\u540e\uff0c\u5219Oracle\u4f1a\u5bf9\u4e0a\u8ff0\u53c2\u6570\u5f71\u54cd\u8303\u56f4\u5185\u6240\u6709\u91cd\u590d\u6267\u884c\u7684SQL\u81ea\u52a8\u6355\u83b7\u5176SQL Plan Baseline\uff0c\u5e76\u4e14\u9488\u5bf9\u76ee\u6807SQL\u7b2c\u4e00\u6b21\u6355\u83b7\u7684SQL Plan Baseline\u7684ENABLED\u548cACCEPTED\u7684\u503c\u5747\u4e3a\u201cYES\u201d\u3002\u968f\u540e\u5982\u679c\u8be5SQL\u7684\u6267\u884c\u8ba1\u5212\u53d1\u751f\u4e86\u53d8\u66f4\uff0c\u5219\u518d\u6b21\u6355\u83b7\u5230\u7684SQL Plan Baseline\u7684ENABLED\u7684\u503c\u4f9d\u7136\u4e3aYES\uff0c\u4f46ACCEPTED\u7684\u503c\u53d8\u4e3a\u4e86NO\uff0c\u8fd9\u8868\u793a\u540e\u7eed\u53d8\u66f4\u7684\u6267\u884c\u8ba1\u5212\u867d\u7136\u88ab\u6355\u83b7\u4e86\uff0c\u4f46Oracle\u4e0d\u4f1a\u5c06\u5176\u4f5c\u4e3a\u8be5SQL\u7684\u6267\u884c\u8ba1\u5212\u6765\u6267\u884c\uff0c\u5373\u6b64\u65f6Oracle\u4f1a\u6c38\u8fdc\u6cbf\u7528\u8be5SQL\u7b2c\u4e00\u6b21\u88ab\u6355\u83b7\u7684SQL Plan Baseline\u6240\u5bf9\u5e94\u7684\u6267\u884c\u8ba1\u5212(\u9664\u975e\u540e\u7eed\u505a\u4e86\u624b\u5de5\u8c03\u6574)\u3002<\/p>
\u53c2\u6570OPTIMIZER_USE_SQL_PLAN_BASELINES\u7528\u4e8e\u63a7\u5236\u662f\u5426\u542f\u7528SQL Plan Baseline\uff0c\u5176\u9ed8\u8ba4\u503c\u4e3aTRUE\uff0c\u8868\u793a\u5728\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0cOracle\u5728\u751f\u6210\u6267\u884c\u8ba1\u5212\u65f6\u5c31\u4f1a\u542f\u7528SPM\uff0c\u4f7f\u7528\u5df2\u6709\u7684SQL Plan Baseline\uff0c\u8fd9\u4e2a\u53c2\u6570\u4e5f\u53ef\u4ee5\u5728session\u6216\u7cfb\u7edf\u7ea7\u522b\u52a8\u6001\u4fee\u6539\u3002<\/p>
\u4e0b\u9762\u770b\u4e00\u4e0b\u5b9e\u4f8b\uff1a<\/p>
\u67e5\u770b\u4e0a\u8ff0\u4e24\u4e2a\u53c2\u6570\u7684\u9ed8\u8ba4\u503c<\/p>
\u5728\u5f53\u524dsession\u4e2d\u7981\u6389SPM\u5e76\u540c\u65f6\u5f00\u542f\u81ea\u52a8\u6355\u83b7SQL Plan Baseline\uff1a<\/p>
\u521b\u5efa\u6d4b\u8bd5\u8868T2<\/p>
\u4ece\u6267\u884c\u8ba1\u5212\u4e0a\u770b\uff0c\u8d70\u7684\u662f\u7d22\u5f15IDX_T2\u4e0a\u7684\u7d22\u5f15\u8303\u56f4\u626b\u63cf\uff0c\u56e0\u4e3aSQL\u53ea\u6267\u884c\u4e86\u4e00\u6b21\uff0c\u6240\u4ee5Oracle\u4e0d\u4f1a\u81ea\u52a8\u6355\u83b7SQL Plan Baseline\uff0cDBA_SQL_PLAN_BASELINES\u4e2d\u6ca1\u6709\u8bb0\u5f55<\/p>
\u518d\u6b21\u6267\u884c\u4e0a\u8ff0SQL\uff0c\u56e0\u4e3a\u91cd\u590d\u6267\u884c\u8be5SQL\uff0cOracle\u81ea\u52a8\u6355\u83b7\u4e86\u8fd9\u4e2aSQL\u7684SQL Plan Baseline?<\/p>
\u4ece\u6267\u884c\u8ba1\u5212\u4e2d\u53ef\u4ee5\u770b\u51fa\u8be5SQL\u7684\u6267\u884c\u8ba1\u5212\u5df2\u7ecf\u53d8\u4e3a\u5168\u8868\u626b\u63cf\u3002\u56e0\u4e3a\u76ee\u6807SQL\u5df2\u7ecf\u91cd\u590d\u6267\u884c\u4e14\u540c\u65f6\u53c8\u4ea7\u751f\u4e86\u4e00\u4e2a\u65b0\u7684\u6267\u884c\u8ba1\u5212\uff0c\u6240\u4ee5\u73b0\u5728Oracle\u5c31\u4f1a\u81ea\u52a8\u6355\u83b7\u5e76\u521b\u5efa\u8fd9\u4e2a\u65b0\u7684\u6267\u884c\u8ba1\u5212\u6240\u5bf9\u5e94\u7684SQL Plan Baseline\u4e86\u3002\u4ece\u5982\u4e0b\u67e5\u8be2\u53ef\u4ee5\u770b\u51faOracle\u5bf9\u65b0\u7684\u6267\u884c\u8ba1\u5212\u4ea7\u751f\u4e86\u4e00\u4e2a\u65b0\u7684SQL Plan Baseline\uff0c\u5176ENABLED\u7684\u503c\u4f9d\u7136\u4e3aYES\uff0c\u4f46ACCEPTED\u7684\u503c\u53d8\u4e3a\u4e86NO\uff1a<\/p>
\u4ece\u4e0a\u9762\u7684\u663e\u793a\u5185\u5bb9\u53ef\u4ee5\u770b\u51fa\uff0c\u73b0\u5728\u76ee\u6807SQL\u7684\u6267\u884c\u53c8\u4ece\u5168\u8868\u626b\u63cf\u6062\u590d\u4e3a\u4e86\u7d22\u5f15\u8303\u56f4\u626b\u63cf\uff0c\u5e76\u4e14\u6267\u884c\u8ba1\u5212\u4e2d\u7684Note\u90e8\u5206\u6709\u201cSQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement\u201d\u5185\u5bb9\uff0c\u8bf4\u660eSPM\u5f00\u542f\u7684\u60c5\u51b5\u4e0b\uff0c\u5373\u4fbf\u76ee\u6807SQL\u4ea7\u751f\u4e86\u65b0\u7684\u6267\u884c\u8ba1\u5212\uff0cOracle\u4f9d\u7136\u53ea\u4f1a\u5e94\u7528\u8be5SQL\u7684ENABLED\u548cACCEPTED\u7684\u503c\u5747\u4e3aYES\u7684SQL Plan Baselline\u3002<\/p>
\u5982\u679c\u60f3\u542f\u7528\u76ee\u6807SQL\u65b0\u7684\u6267\u884c\u8ba1\u5212(\u5373\u5168\u8868\u626b\u63cf)\uff0c\u5e94\u8be5\u5982\u4f55\u505a\u5462\uff1f<\/p>
\u9488\u5bf9\u4e0d\u540c\u7684Oracle\u7248\u672c\uff0c\u4f1a\u6709\u4e0d\u540c\u7684\u5904\u7406\u65b9\u6cd5\u3002\u6bd4\u5982\u8fd9\u91cc\u60f3\u542f\u7528\u76ee\u6807SQL\u7684\u65b0\u7684\u6267\u884c\u8ba1\u5212\uff0c\u5982\u679c\u662f11gR1\u7684\u73af\u5883\uff0c\u5219\u53ea\u9700\u8981\u5c06\u76ee\u6807SQL\u6240\u91c7\u7528\u7684\u540d\u4e3aSQL_PLAN_asnmb3t5yfk4024c6dbb6\u7684SQL Plan Baseline(\u5373\u7d22\u5f15\u8303\u56f4\u626b\u63cf)\u7684ACCEPTED\u7684\u503c\u8bbe\u4e3aNO\u5c31\u53ef\u4ee5\u4e86\u3002\u4f46\u5bf9\u4e8e11gR2\u73af\u5883\uff0c\u4e0a\u8ff0\u65b9\u6cd5\u4f1a\u62a5\u9519\uff0c\u56e0\u4e3a\u572811gR2\u4e2d\uff0c\u6240\u6709\u5df2\u7ecf\u88abACCEPTED\u7684SQL Plan Baseline\u7684ACCEPTED\u7684\u503c\u5c06\u4e0d\u518d\u80fd\u591f\u88ab\u8bbe\u4e3aNO\uff1a<\/p>
\u572811gR2\u4e2d\uff0c\u6211\u4eec\u53ef\u4ee5\u8054\u5408\u4f7f\u7528DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE\u548cDBMS_SPM.ALTER_SQL_PLAN_BASELINE\u8fbe\u5230\u542f\u7528\u76ee\u6807SQL\u65b0\u7684\u6267\u884c\u8ba1\u5212\u7684\u76ee\u7684\u3002<\/p>
\u5148\u7528DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE\u5c06\u65b0\u7684\u6267\u884c\u8ba1\u5212(\u5168\u8868\u626b\u63cf)\u6240\u5bf9\u5e94\u7684SQL Plan Baseline\u7684ACCEPTED\u503c\u8bbe\u4e3a\u201cYES\u201d\uff1a<\/p>
\u4ece\u4e0a\u9762\u663e\u793a\u7684\u5185\u5bb9\u770b\u5230\u5982\u4e0b\u4fe1\u606f\uff1a\u201cPlan: SQL_PLAN_asnmb3t5yfk40b860bcf2----Plan was changed to an accepted plan.\u201d\uff0c\u8fd9\u8868\u660e\u5df2\u7ecf\u5c06\u65b0\u7684\u6267\u884c\u8ba1\u5212(\u5168\u8868\u626b\u63cf)\u6240\u5bf9\u5e94\u7684SQL Plan Baseline\u7684ACCEPTED\u503c\u8bbe\u4e3aYES<\/p>
\u4ece\u4e0b\u9762\u7684\u67e5\u8be2\u7ed3\u679c\u4e5f\u53ef\u4ee5\u8bc1\u660e\uff1a<\/p>
\u7136\u540e\u518d\u4f7f\u7528DBMS_SPM.ALTER_SQL_PLAN_BASELINE\u5c06\u539f\u5148\u7684\u6267\u884c\u8ba1\u5212(\u7d22\u5f15\u8303\u56f4\u626b\u63cf)\u5bf9\u5e94\u7684SQL Plan Baseline\u7684ENABLED\u7684\u503c\u8bbe\u4e3aNO\uff1a<\/p>
\u518d\u6b21\u6267\u884c\u76ee\u6807SQL<\/p>
\u4ece\u4e0a\u8ff0\u663e\u793a\u53ef\u4ee5\u770b\u51fa\uff0c\u73b0\u5728SQL\u7684\u6267\u884c\u8ba1\u5212\u5df2\u7ecf\u53d8\u4e3a\u4e86\u5168\u8868\u626b\u63cf\uff0c\u6211\u4eec\u8981\u542f\u7528\u65b0\u7684\u6267\u884c\u8ba1\u5212(\u5168\u8868\u626b\u63cf)\u7684\u76ee\u7684\u5df2\u7ecf\u5b9e\u73b0\uff0cNote\u90e8\u5206\u4e5f\u6709\u4e86\u63d0\u793a\u3002<\/p>
\u4ece\u4e0a\u8ff0\u6d4b\u8bd5\u7ed3\u679c\u53ef\u4ee5\u770b\u51fa\uff0c\u5b9e\u9645\u4e0a\u6211\u4eec\u53ef\u4ee5\u8f7b\u6613\u5730\u5728\u76ee\u6807SQL\u7684\u591a\u4e2a\u6267\u884c\u8ba1\u5212\u4e2d\u5207\u6362\uff0c\u6240\u4ee5SPM\u786e\u5b9e\u662f\u65e2\u80fd\u591f\u4e3b\u52a8\u5730\u7a33\u5b9a\u6267\u884c\u8ba1\u5212\uff0c\u53c8\u4fdd\u7559\u4e86\u7ee7\u7eed\u4f7f\u7528\u65b0\u7684\u6267\u884c\u8ba1\u5212\u7684\u673a\u4f1a\uff0c\u5e76\u4e14\u6211\u4eec\u5f88\u5bb9\u6613\u5c31\u80fd\u542f\u7528\u65b0\u7684\u6267\u884c\u8ba1\u5212\u3002<\/p>
\u4e0b\u9762\u4ecb\u7ecd\u624b\u5de5\u751f\u6210SQL Plan Baseline\uff1a<\/p>
\u624b\u5de5\u751f\u6210\u76ee\u6807SQL\u7684","orderid":"0","title":"Oracle\u56fa\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212(\u4e8c)---SPM(\u4e00)","smalltitle":"","mid":"0","fname":"\u6570\u636e\u5e93\u7f16\u7a0b","special_id":"0","bak_id":"0","info":"0","hits":"485","pages":"2","comments":"0","posttime":"2017-03-03 08:15:35","list":"1488500135","username":"admin","author":"","copyfrom":"","copyfromurl":"","titlecolor":"","fonttype":"0","titleicon":"0","picurl":"https:\/\/www.cppentry.com\/upload_files\/","ispic":"0","yz":"1","yzer":"","yztime":"0","levels":"0","levelstime":"0","keywords":"Oracle<\/A> \u56fa\u5b9a<\/A> SQL<\/A> \u6267\u884c<\/A> \u8ba1\u5212<\/A> ---SPM<\/A>","jumpurl":"","iframeurl":"","style":"","template":"a:3:{s:4:\"head\";s:0:\"\";s:4:\"foot\";s:0:\"\";s:8:\"bencandy\";s:0:\"\";}","target":"0","ip":"14.17.22.31","lastfid":"0","money":"0","buyuser":"","passwd":"","allowdown":"","allowview":"","editer":"","edittime":"0","begintime":"0","endtime":"0","description":"Oracle\u56fa\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212(\u4e8c)---SPM","lastview":"1705116188","digg_num":"4023","digg_time":"0","forbidcomment":"0","ifvote":"0","heart":"","htmlname":"","city_id":"0"},"page":"1"}