通过持续的调优工作和监测可以找到运行中应用程序的最佳值。此时，UNIX内核的内存不易减少，需要有效地调优数据库内存区域，使用更少内存发挥相同水平的性能。如果内核内存使用约100 MB，并发用户为30名，每个Oracle进程的内存为 10 MB，假定 sort_area_size为 1MB，按以下公式计算出430 MB。100 MB+30×=430 MB因此，物理内存为1 GB的系统，可分配大约600 MB的SGA使用。......
2023-11-01
基于成本的优化器：数据库性能管理与调优
【摘要】：统计信息中保存有列选择度、数据量等大量信息，因此基于规则的优化器可以更灵活地创建执行计划。extent是由相关联的数据块构成的逻辑单位。否则，会因表的random access造成表扫描的平均成本增高。排序操作成本并非用于访问对象数据的成本，而是用于排序已提取数据的成本。若使用以特定列为标准排序的索引提取数据时，可以减少额外的排序操作成本。要了解SQL语句实际应访问的对象范围程度才能计算实际成本，这是非常重要的概念。
优化器收集相应数据库的统计信息，并以该资料为基础创建SQL的执行计划。统计信息中保存有列选择度、数据量等大量信息，因此基于规则的优化器可以更灵活地创建执行计划。不过，统计信息只将无数信息中的一部分作为统计信息保存，因此有时会创建错误的执行计划。首先介绍CBO中使用的重要术语。
1）成本（cost）
Cost是为比较处理SQL语句的各个方法的成本而在CBO中使用的成本评价的单位。对于 index access和 table access， CBO将以各 自access method所需的 I/O次数为基础计算cost，在其中反映一部分CPU、network成本并校正。但DBA会将由CBO计算的cost与实际发生的I/O成本相关联，很难应用到SQL语句调优中。因此理解cost值的含义即可。
（1） table scan cost。
常用的表扫描成本的计算方法如下：
成本（cost）=ceil（HWM下的所有对象数据块数 /DB_ FILE_ MULTIBLOCK_ READ_COUNT）
作为参考，如果表分为多个extent且各个extent未指定为DB_FILE_MULTIBLOCK_READ_COUNT DB_BLOCK_SIZE的倍数，则会出现低效I/O。例如，一个extent由20个数据块组成，且DB_FILE_MULTIBLOCK_READ_COUNT=8，则每个extent的I/O次数为 2（8 * 2=16）+1（4），即为 3次，最后为了只读取 4个数据块出现执行一次 I/O的低效操作。
Ceil函数返还大于或等于自变量的最小整数值，返还值的类型与自变量的类型相同。
在Oracle中逻辑上最小的I/O单位为数据块（block）。 extent是由相关联的数据块构成的逻辑单位。segment以extent为单位分配（allocate）或取消分配（de-allocate）存储空间。使用 extent时，相关 block将保存到连续空间，有利于 I/O。即如果有内容要写入 data file， Oracle会以extent单位进行分配，使相关的Block保存至连续的空间。
（2） index scan cost。
索引扫描的成本将根据以下要素进行计算：
① B*树 level数。
②对象leaf block的数。
③ 结果上访问的table block数（clustering factor）。
INDEX FAST FULL SCAN时会受到DB_FILE_MULTIBLOCK_READ_COUNT的影响。仅使用索引的构成列就能得出满意结果时，可使用INDEX FAST FULL SCAN。否则，会因表的random access造成表扫描的平均成本增高。一般情况下，对象范围超过所有行的5%～10%，则判断表扫描优于索引扫描。
（3） SORT cost。
排序操作成本并非用于访问对象数据的成本，而是用于排序已提取数据的成本。若使用以特定列为标准排序的索引提取数据时，可以减少额外的排序操作成本。而且根据所有排序操作的对象与SORT_AREA_SIZE比较大小的差异，以确定排序操作的成本。
① SORT操作是非常CPU INTENSIVE（集中）的操作。
②如果无法在内存内执行SORT操作，则进行I/O INTENSIVE（集中）的操作。
③ SORT操作的cost将根据以下要素进行计算：
A.SORT对象行数和大小；
B.SORT_AREA_SIZE。
④发生SORT操作时，可用以下操作：
A.ORDER BY；
B.AGGREGATION；
C.JOIN OPERATIONS。
2）选择度（selectivity）
选择度指满足WHERE子句中所示条件的行数在整个表中所占的比率。要了解SQL语句实际应访问的对象范围程度才能计算实际成本，这是非常重要的概念。对于值未均等分布的列的选择度，可以使用直方图计算出更准确的结果。
选择度=（符合条件的行数/表的所有行数）×100=（1/列值种类）×100
3）选择度计算
（1）指定literal值时，根据High_value和Low_value值计算条件值的比率。literal值指编译时按进程内定义的方式准确解析的值。与此相比，变量在程序运行时可根据需要表示不同的值，常数在程序运行时则始终表示相同的值。但literal不是名称，而是值本身。例如，在公式 x=7中，“ x”为变量，“7”为 literal值。literal值可以是数字，也可以是文字或字符串。
（2）指定绑定变量时，分为以下两种情况：
①若指定的条件为绑定变量，则在绑定实际变量值之前制定执行计划，因此CBO无法计算该列的准确选择度。此时，对于各个运算符，CBO会将选择度（selectivity）作为default值进行计算。
② 多个条件组合时（多个条件子句通过AND、OR的组合时），(www.chuimin.cn)
作为参考，使用bind变量和使用literal value时所用的选择度（selectivity）不同，因此若要查看实际执行计划，执行时不能用特定literal替换bind变量，才能查看准确的执行计划。
4）基数（cardinality）
cardinality指通过特定表或子集指定操作返还后计算的行数。例如，CBO对某些index access以100来计算 cardinality时，会查找该索引，表示预期会返回100条索引。cardinality直接影响access method[1]和join method的选择，是非常重要的项目。cardinality非常大时，优化器作为access method可能会选择FULL-TABLE SCAN，作为join method则可能会选择HASH JOIN。 driving（外部）表的cardinality是NESTED LOOP JOIN成本评价中非常重要的要素。会根据该值确定执行Loop（重复）操作的次数。此外，排序操作的成本也依据cardinality值，因此对排序操作成本的计算同样有着重要意义。
5）传递性（transitivity ）
transitivity是通过推算出各个“=”条件语句的相关性，逻辑上等价地替换条件语句时应用的代数特点。这在离散数学中应用广泛。例如，A=B、B=C，则可以替换为A=C（当然，如果上述条件为连接条件，则不会如此简单地进行替换）。CBO利用这些transitivity以更加多样化的方式应用索引，则得出更为有效的执行计划的可能性增大。
6）优化器模式
（1） RULE：不考虑分析（analyze）信息，根据SQL语句信息创建最佳执行计划。
（2） CHOOSE：根据data dictionary信息中分析（analyze）信息的有无，基于成本或基于规则（rule-based）制定最优路径（统计资料不存在时与rule-based相同）。
（3） FIRST ROWS：为使用最少的资源导入第一个行而创建执行计划，无法优化需要排序（SORT）的SQL。使用基于成本（cost-based）的优化方式。
（4） ALL ROWS：为使用最少的资源导入所需的全部结果而创建执行计划。使用基于成本（cost-based）的优化方式。
（5） FIRST ROWS n：为使用最少的资源导入第n个行而创建执行计划。
7）选择优化器模式时的注意事项
选择优化器模式时注意事项如图2-6所示。
图2-6 选择优化器模式时的注意事项
8）影响基于成本的优化器（CBO）的参数
① OPTIMIZER_FEATURES_ENABLED：要直接使用特定版本的优化器时。
② OPTIMIZER_MODE：用于实例的优化器的模式设置。
③ OPTIMIZER_PERCENT_PARALLEL：指定对象的并行处理数（degree）反映到成本的程度（%）。
④ HASH_AREA_SIZE：影响 HASH JOIN的成本。
⑤ SORT_AREA_SIZE：影响SORT MERGE JOIN的成本。
⑥ DB_FILE_MULTIBLOCK_READ_COUNT：影响FULL SCAN的成本。
⑦ ALWAYS_ ANTI _JOIN: NOT IN SUBQUERY时将执行计划设置为ANTI HASH JOIN。
⑧ HASH_JOIN_ENABLED：决定是否使用 HASH JOIN。
⑨ HASH_MULTIBLOCK_IO_COUNT：影响 HASH JOIN的成本。
⑩ OPTIMIZER_INDEX_COST_ADJ：使用索引的影响度（0～100， default： 100）。
⑪ OPTIMIZER_INDEX_CACHING: NESTED LOOP JOIN时使用的索引leaf block的缓存 度（0～100， default： 0）。
9）优化器的限制
前文已对优化器存在限制的原因做过介绍。简单来说，在实际运行 SQL之前，优化器就需要利用统计资料制定执行计划。即优化器最大的限制就是在指定时间内仅用指定的统计信息预测成本。此外，通过预先指定的数学公式计算受多种要素影响的实际数据的统计选择度，无法避免受限情况。
①信息量是限制：无法预先拥有可用于指定条件的所有统计信息。
② 可用时间的限制：制定执行计划所花费的时间也是SQL的执行成本。
③数据选择度计算的限制：所用运算符各自组合引起的各种事件个数；多种列的组合引起的各种事件个数；实际情况下大多使用bind变量，使用bind变量时histogram也无法使用，最后还是取决于平均值。
有关数据库性能管理与调优的文章
- 详细阅读
-
数据库性能管理与调优-优化器提示方法详细阅读
在制定由优化器执行的SQL语句的执行计划过程中，提示为用户提供了可直接进行介入的方法。提示是用户对优化器下达的指示，但优化器可能会忽略不合理的提示。意味着除了RULE和DRIVING_SITE外的所有提示将选择CBO用作优化器。使用Orange Template Browser查看优化器提示如图2-8所示。图2-8Orange Template Browser查看优化器提示单击方框部分图标（）、单击最顶端菜单的［View］—［Template Browser］或按快捷键 Alt+F2则会运行［Template Browser］。......
2023-11-01
-
数据库性能管理与调优：HashJoin技术详细阅读
HASH JOIN是突出SORT MERGE JOIN优点并弥补其缺点的连接方法。该连接是对连接的对象表应用 HASH函数进行合并的方法。其特征是不仅只使用 HASH JOIN，而且提供通过并行处理大容量数据的最佳解决方案。但不建议在OLTP等状况下使用，因为HASH JOIN已为大容量处理进行优化，会占用大量系统资源。不会受JOIN条件（链路）索引有无的影响。2）使用标准由于链路使用HASH Key，因此仅用于EQUI-JOIN。在执行计划中成为HASHING对象的表始终在最前面，但如果是OUTER JOIN则完全相反。......
2023-11-01
-
数据库性能管理与调优-排序合并连接详细阅读
SORT MERGE JOIN指各自访问两侧表的处理范围后逐一扫描排序结果，同时使用连接条件进行合并的方式。通过SORT MERGE JOIN引导调优的情况几乎不存在，但可能对减少RANDOM ACCESS、大容量处理对象集有效。以下将介绍SORT MERGE JOIN的其他特征以及能保证良好性能的情形。通常NESTED LOOP JOIN对待处理数据量较少的在线应用程序更为有效，因此请勿随意使用SORT MERGE JOIN。......
2023-11-01
-
数据库性能管理与调优索引的使用与维护详细阅读
为根据不同情况使用适当的索引，要学习各种类型的索引。因为只有明确了解表和索引的概念才能轻松解决复杂的内容。创建索引抽屉时，应考虑偶有发生的损益分歧点，考虑是否创建。总体来说，索引是用户使用SQL语句查找时为提高查找速度而创建的对象。但若创建太多索引，购买新书或废弃时，会产生额外的维护费用。这是由于添加或删除书籍时应更新现有的索引抽屉。......
2023-11-01
-
数据库性能管理与调优：正确使用SQL技巧详细阅读
1）例子（一）正确使用SQL的例子（一）如图5-12所示。因此B.ORDD = : TrDay或B.ORDD = : WorkDay的扫描范围较窄，使用索引时响应速度非常快。在这种情况下，优化器使用OR从内部分为两个SQL语句并各自使用索引，再将执行结果合并为一个结果。图5-13正确使用SQL的例子（二）问题：连接的两个表的条件中决定扫描范围的条件是"AND SUBSTR LIKE : IN_org_cd AND SUBSTR ＜ 600000"。因此可按以下方法变更条件公式并使用相应索引。......
2023-11-01
-
数据库性能优化指南-数据库性能管理与调优详细阅读
实例已启动意味着数据库系统运行所需的后台进程已启动，并且处于“确保内存可用于数据处理”的状态，这种实例的调优将成为DBMS性能调优的重要基础。1） DBMS 内存 区域——SGASGA 是作为实例启动时确保控制器信息保存和数据处理空间的内存区域，存在于实例访问的所有进程共享的内存区域中。SGA相关调优的基准基本以命中率评价为主，通过适当地调整相关参数，将实际运行反映到评价结果中。......
2023-11-01
-
数据库性能管理与调优：连接种类与顺序详解详细阅读
对象表的连接顺序和方法不同是为了加快访问速度。因此即使变更连接方法和顺序，结果值也不能有任何变化。连接时表的驱动顺序是性能中的重要组成部分。4）优化器的连接选择对于参与连接条件语句的列，索引位置对优化器决定执行计划时的连接顺序和方法有很大影响。连接列的两侧均无索引时，将SORT MERGE JOIN或HASH JOIN用作连接方法。......
2023-11-01
相关推荐