本文永久地址是：https://www.askmaclean.com/archives/oracle-database-11gr2%E4%B8%AD%E7%9A%84cost-base-optimizer%E4%BD%BF%E7%94%A8%E8%AF%B4%E6%98%8E%E4%BC%9A.html     SQL的执行计划是指什么？ Execution Plan SQL语句中没有记录访问路径（使用索引等）。Database内部判断做出决定。这个访问路径我们称为执行计划。 SQL例子： select * from tab2 where c2 > 999;
  • 执行计划例子：
    • 使用索引的执行计划
  
----------------------------------------------------------
Plan hash value: 2200541503
---------------------------------------------------
| Id  | Operation                   | Name | Rows  
---------------------------------------------------
|   0 | SELECT STATEMENT            |      |    10 
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB2 |    10 
|*  2 |   INDEX RANGE SCAN          | IND2 |    10 
---------------------------------------------------

CBO的输入信息与输出信息

 
  • Cost Base Optimizer (CBO)
    • 决定生成查询结果的最高效的方法，制成执行计划的功能
  • 统计信息
    • CBO的输入之一
    • 表示使用表、索引、使用的区域、基数、数据分布等数据特性的信息
  • 执行计划
    • CBO的输出信息
    • 通过Cost Base Optimizer (CBO)生成的SQL执行算法（访问路径）
11gR2CBO1   統計信息与SQL响应的关系
  • 统计信息以外的输入经常是固定的
  • 执行计划的变动原因主要是统计信息。统计信息是重要的因素。
11gR2CBO2       CBO (Cost Base Optimizer) 优点与缺点 11gR2CBO3  
  • 决定通过执行计划执行SQL的访问路径
  • 执行计划通过优化制成（CBO）
  • 优化注意通过观察统计信息来制成执行计划
  • 每次获得统计信息时，都会反映数据状态，并产生相应变化
  • 因此，根据数据状态的变化来生成执行计划
通过访问行数变更执行计划的效果
  • 搜索中取出行的方法有索引扫描以及全表扫描
 
  • 索引扫描
    • 从字典搜索单词时，从标记中横向寻找的方法
    • 通过精确定位想寻找特定单词时有效
 
  • 全表扫描
    • 从表的最开始到最末搜索对应行的方法
    • 用字典的例子来说的话，例如，想查找词语最后以a结尾的单词是，只有在提示数较多的情况下才有效
    通过访问行数变更执行计划的效果   一般访问1个SQL表的10%-20%以上的行数的话，全表扫描速度更快 ※与严格的CBO判断逻辑不同，可以作为参考指标来使用 自动执行切换执行计划，是CBO的使命   11gR2CBO4  
  • 一方面，不仅是访问的行数是表的百分之几，决定其是否成为对象，还会更加表整体的行数来变化
11gR2CBO5  
  • 在此，从一下两种红色对话框的信息中可以获得统计信息
  • 因此，统计信息中，重要的是正确反映实际数据的信息
11gR2CBO6   統計信息收集时机的决定方法
  • 对于执行所有SQL，想执行正确反映数据状态的执行计划的话，需要每次执行之前都需要获得统计信息。但是，考虑这样的统计信息产生的负荷，这个想法不太现实。
  • 尽可能真实地反映实际数据，掌握实际数据与统计信息分离时，产生的风险。采用的方法是避开这个时点，获得统计方法。
  产生实际数据与统计数据分离的风险的原因
  • 实际尺寸比统计信息估计的表尺寸要大得多时，由于执行计划不合适，所以可能发生性能恶化的风险。
11gR2CBO7     統計信息的获得时机   11gR2CBO8 掌握各个表的数据变更，在最大值集中的时点，将获得schema单位的统计信息job化。最大值的时机，不同的话，就会从其他方法获得统计信息，进行锁定。 如此，通过其他方法获得统计信息进行锁定的表，比较有代表性的有，在补丁表中使用的工作表。     11gR2CBO9  
  • 重要的是统计信息与实际数据没有分离
  • 为了减少分离的统计信息，请采取风险较低的方法。
  • 在统计信息的表尺寸比实际的表尺寸要小的时候就会有风险
  • 为了规避风险，请选择在表尺寸为最大值时收集统计信息
控制、管理重要SQL的执行计划
  • 提供认真选择获得統計信息的时机，可以根据数据变动，选择最合适的执行计划。但是无法防止执行计划发生改变的风险。
  • 提供应用最合适的执行计划，判断为需要安定的响应比较重要的SQL，需要考虑控制CBO的执行计划。
  11gR2CBO10   使用提示的执行计划控制方法
  • 提供在SQL语句中对CBO加入控制命令来控制执行计划的方法
  • Package 应用中，为了不在SQL语句中直接加入字符列，所以不能直接使用
  • 还有通过使用SPM，来控制package应用的SQL的方法 →[参考]SPM baseline的编辑
  • 例）搜索emp表的employee_id时，控制emp_id_pk这样的索引扫描的提示语句
    
SELECT /*+ INDEX(e1 emp_id_pk)　*/ 
e1.first_name, e1.last_name, j.job_id, 
sum(e2.salary) total_sal 
FROM employees e1, employees e2, job_history j 
WHERE e1.employee_id = e2.manager_id 
AND e1.employee_id = j.employee_id 
AND e1.hire_date = j.start_date 
GROUP BY e1.first_name, e1.last_name, j.job_id 
ORDER BY total_sal;
SQL Plan Management(SPM)是指什么
  • 将执行计划作为历史记录，进行评价、管理的机制
  • 从记录完成的执行计划中构造SQL计划baseline，从其中选择最优的计划
SPM的执行层面   11gR2CBO11   获得SQL计划baseline   参考有效的话，优化就可以每次新建执行计划时都可以获得计划，作为计划历史来管理 重新解析、重新执行的SQL，可以获得SQL计划历史 (在日志中储存、查看被Parse1次的SQL的SQL_ID) 11gR2CBO12   选择SQL计划Baseline 基于储存SQL计划历史，检测出计划的变更，选择可以回避降低SQL语句性能可能性的计划 重新解析、重新执行的SQL，可以获得SQL计划历史   11gR2CBO13     SQL计划baseline的改良 评价新的计划性能，在SQL计划baseline中加入更加优秀的性能计划 可以通过以下方法改良计划 -使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE功能 -手动认证执行计划(DBMS_SPM.ALTER_SQL_PLAN_BASELINE) -SQL调优建议 -人工加载计划(cursor cache, SQL tuning set) -使用固定SQL计划baseline -   固定SQL计划baseline 可以使用SQL计划baseline的FIX属性变成YES时、CBO会优先使用这个计划。 另外，对于固定SQL计划baseline不会追加新的计划，所以追加时需要人工加载计划。     DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE   11gR2CBO14   SPM的优点
  1. 可以处理执行计划发生变化的风险
  2. 可以保存多个执行计划进行比较，从中选错最佳的执行计划
  SPM的缺点
  1. 由于使用SPM会导致Hard Parse时负荷增加
  2. 需要一定知识水平才能从多个执行计划中选出最佳的执行计划
  project事例：选择SPM对象SQL例   11gR2CBO15 ※重要业务 SQL 执行性能较低的情况下，选择对业务影响较大的功能。 成为夜间补丁的关键路径的功能以及补丁窗口中必需收集的功能等 执行次数较多的SQL SQL执行次数越多，对单个SQL的性能的影响程度就越大   这次案例中，采用案例2 ※SPM的对象，SQL的执行计划，管理，使用的对象增加太多的话，使用起来可能就变得非常麻烦。  
  • 对于重要SQL，SPM可以不通过统计信息，从而控制执行计划
    • Package应用中，因为无法记录到SQL语句中，所以可以控制执行计划的只有SPM
  • 使用SPM时，推荐将重要的SQL作为对象来进行控制、管理
   
  • 执行计划是指执行SQL时内部使用的访问路径
  • 执行计划通过CBO将统计信息作为重要因素来计算
  • 为了获得合适的执行计划，需要在风险最小的时候收集信息
  • 为了将风险最小化，推荐在表尺寸到达最大值时收集统计信息
  • 伴随着数据变动，通过将执行计划最优化，可以使得重要的SQL稳定下来，通过SPM进行控制、管理。
  • 基本上，定期获得统计信息，就可以将整体的SQL执行计划最优化，推荐用其他SPM管理控制重要SQL
※另外，由于执行计划的变化导致性能恶化的统计信息的备份无法定期获得     [参考]执行计划的查看方法 1.Explain plan for <SQL>
  • 实际上不会执行SQL
  • 需要plan_table
– 2.SQL*PLUS的AUTOTRACE命令
  • 除set autotrace traceonly explain以外都会执行SQL
  • 需要plan_table
3.SQL追踪/Statspack以及AWR的SQL报告 4.V$SQL以及V$SQL_PLAN(9i～)
  • 使用共享表的SQL语句的执行计划以及V$SQL_PLAN视图来搜索
5.Enterprise Manager （10g～）   [参考]統計信息是指什么?? 統計信息是指表示表、索引、以及正在使用的区域， Cardinality数据分布等信息。CBO以这些信息为基础来计算成本，生成执行计划。     統計信息的内容  
  • 表統計
    • 行数、数据・块数、平均行長
  • 列統計
    • 列内的個別値数(NDV : Number of Distinct Values)
    • 列内的NULL数
    • 数据分布(最大値 / 最小値 /柱状图)
  • 索引統計
    • Leaf block数
    • 水平 (树的高度)
    • Cluster化系数
  • 系统统计
    • I/O性能
    • CPU性能
11gR2CBO16                   [参考]Oracle Database自动收集统计信息
  • Oracle Database 会遵从以下规则定期自动收集统计信息。
[时机] 周一到周五每天22-2之间4小时 周末每天20小时 [条件] 統計信息是没有收集的表 表内的行数的10%以上被变更，统计信息失效的表 ※通过DBMS_STATS 获得统计信息时，之前的统计信息就会保存在SYSAUX表区域中， DBMS_STATS 会内部自动收集统计信息
  • 默认保存31天
  • 可以在性能恶化时进行重新存储
  [参考]SPM baseline的编辑   11gR2CBO17