Ask Maclean

Oracle Database 11gR2中的Cost Base Optimizer使用说明会

本文永久地址是：https://www.askmac.cn/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

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *