Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle

引言

Oracle 使用两种引擎来处理 PL/SQL 代码。所有存储过程的代码由 PL/SQL 引擎处理,所有的 SQL 由 SQL 语句执行器/SQL 引擎处理。

在两个引擎直间的上下文切换会带来开销。如果 PL/SQL 代码在一个集合上循环,为集合里的每个元素执行同样的 DML 操作,那么可以通过一次 bulk 绑定整个集合到 DML 语句上以减少上下文切换。

BULK COLLECT

Bulk 绑定可以在从查询里加载数据集时提升性能。BULK COLLECT INTO 把查询的数据结构化绑定到集合上。

CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name,
       object_id
FROM   all_objects;


--  使用
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();
  l_start  NUMBER;
BEGIN

  SELECT *
  BULK COLLECT INTO l_tab
  FROM   bulk_collect_test;

END;

集合是维护在内存里的,因此从一个大查询里做 bulk collect 会对性能有显著的影响。事实上,你不应该以这样的方式直接使用 bulk collect 。应当使用 LIMIT 子句限制返回的行数,这让你得到 bulk 方式的好处,又不会占用大量的服务器内存。

继续阅读

append hint, direct-path insert

一份笔记。

1. append hint

直接加载插入(direct load insert, direct-path insert)是运行 insert 语句的一种快速方法。对于加载大量数据行特别有用。

1.1 append hint 如何影响性能

  • 数据被追加到表的末尾,而不是尝试使用表里已存在的空闲空间。
  • 数据被直接写到数据文件,避开了 (写,buffer)缓冲、(读,cache)缓存。
  • 引用完整性约束将不会考虑。
  • 触发器的处理将不会执行。

后面两点可能导致数据逻辑损化,因此,如果表上允许引用完整性约束和触发器,Oracle 忽略 append hint 并以传统的 insert 方式加载数据。

1.1.1 append hint 对表大小的影响(高水位线, high water mark)

由于直接路径插入把数据追加到表的末尾,它们不断地增加表的高水位线,即使表里还有很多空闲空间。append hint 可能导致很大的表里包含了很多稀疏填充的块。这可以通过下面的收缩操作来管理:

  • 导出数据、 truncate 表然后导入数据。
  • 使用 create table ... as select(CTAS) 操作来构建新的表,让数据压缩,删除原始表,重命名新表来替代原始的。
  • 使用 online table redefinition 操作来重新创建表。
  • 使用 online segment shrink 操作压缩数据。

1.1.2 How the APPEND Hint Affects Redo Generation

If the database is running on NOARCHIVELOG mode, using just the APPEND hint will reduce redo generation. In reality, you will rarely run OLTP databases in NOARCHIVELOG mode, so what happens in ARCHIVELOG mode? In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation unless the table is set to NOLOGGING.

继续阅读

隐式类型转换导致全表扫描

最近系统改版了一部分(其实就是重做。。。),上线之后又开始出现数据库 IO 告警了。

DBA 抓出一个全表扫描的 SQL,是把范围在两个钟内的数据找出来做更新的,这个查询的时间字段是有索引的,结果却没有用上,
跟另一个表关联的时候,关联字段也是有索引的,但还是没有用上。

在两个钟内的数据量应该是很小的,相对于总量来说,时间字段的索引没理由不用啊;从主表找出来的数据量不大,再去关联表查询也应该走索引的。
感觉就是执行计划不对了,去找 DBA 做固化。

找 DBA 先用 hint 生成一个走索引的执行计划,然后做了固化。

这个 SQL 是两个钟才执行一次的,结果固化之后值班 DBA 说主表还是全表扫描。找 DBA 再做一次固化,还是主表全表扫描。最后开了 tunning ,提示说有隐式类型转换,主表没法走索引。

大致扫了一下代码,Java 里用的数据类型是 Date 的,而表的列的类型也是 Date,应该不需要做隐式类型转换啊。

问题出在 MyBatis 的参数类型指定为 timestamp 而不是 Date;由于查询需要时分秒,而 Date 会抹去这些值,所以被指定为 timestamp,
而这个类型刚好对应到 Oracle 的 timestamp 类型,所以就出现隐式类型转换。

细节吶。。。


欢迎关注我的微信公众号: coderbee笔记,可以更及时回复你的讨论。

《基于 Oracle 的 SQL 优化》笔记 第三章 Oracle 里的 Cursor 和变量绑定

系统性能随着并发数量的递增而显著下降的现象,往往是因为没有使用绑定变量而产生大量硬解析所致。

Cursor,游标

Cursor 是 Oracle 数据库里 SQL 解析和执行的载体。有两种类型的 Cursor:Shared Cursor,Session Cursor。

库缓存对象

库缓存(Libaray Cache)实际上是 SGA 中的一块内存(更确切说是 Shared Pool 中的一块内存区域),它的主要作用是缓存刚刚执行过的 SQL 语句和 PL/SQL 语句(如存储过程、函数、包、触发器)所对应的执行计划、解析树(Parse Tree)、Pcode、Mcode 等对象,当同样的 SQL 语句和 PL/SQL 语句再次被执行时,就可以利用缓存在 Libaray Cache 中的那些对象而无须再次从头开始解析,这就提高了这些 SQL 语句和 PL/SQL 语句在重复执行时的执行效率。
Oracle-arch

缓存在库缓存中的对象称为库缓存对象(Library Cache Object),所有的库缓存对象都是以一种名为库缓存对象句柄(Library Cache Object Handle)的结构存储在库缓存中的,Oracle 是通过访问相关的库缓存对象句柄来访问对应的库缓存对象的。

库缓存对象句柄是以哈希表(Hash Table)的方式存储在库缓存中的。

继续阅读

《基于 Oracle 的 SQL 优化》笔记 第五章 Oracle 里统计信息

CBO 是基于对各种不同执行路径成本的计算,比较并选取成本值最小的执行路径来作为目标 SQL 的执行计划的;而成本值的计算则是根据目标 SQL 所涉及的表、索引、列等相关对象的统计信息,运用 CBO 固有的成本值计算公式计算出来的。所以,对统计信息的了解成了理解 CBO、理解执行计划的关键。

Oracle 数据库里的统计信息分为六种类型,包含对应的对象的信息,维度不同:

  • 表的统计信息:典型的维度有 记录数、表块(表里的数据块)的数量、平均行长度等。
  • 索引的统计信息:典型的维度有 索引的层级、叶子块的数量、聚族因子等。
  • 列的统计信息:典型的维度有 列的 distinct 值的数量、列的 null 值的数量、列的最小值、列的最大值以及直方图等。
  • 系统统计信息:所在数据库服务器的系统处理能力,包含来 CPU 和 I/O 这两个维度,借助于系统统计信息,Oracle 可以知道目标数据库服务器的实际处理能力。
  • 数据字典统计信息:描述 Oracle 数据库里的数据字典基表(如 TAB$IND$ 等)数据字典基表上的索引,以及这些数据字典基表的列的详细信息,与普通表、索引、列的统计信息没有本质区别。
  • 内部对象统计信息:描述 Oracle 数据库里的内部表(如 X$ 系列表,其实只是 Oracle 自定义的内存结构)的信息信息,并不占用实际的物理存储空间。

可以用 ANALYZE 命令(只能收集前四种,且不能并行收集)或 DBMS_STATS 包来收集统计信息,

继续阅读

《基于 Oracle 的 SQL 优化》笔记 第四章 Oracle 里的查询转换

查询转换(Query Transformation)又称为查询改写(Query Rewrite),是指 Oracle 在解析目标 SQL 时可能会对其做等价改写,目的是为了生成更高效地执行目标 SQL,即 Orace 可能会将目标 SQL 改写成语义上完全相等但执行效率更高的形式。

子查询展开

子查询展开(subquery unnesting)是指 Oracle 不再将目标 SQL 中的子查询当作一个独立的处理单元来单独执行,而是将该子查询转换为它自身和外部查询之间等价的表连接。这种等价表连接转换要么是将子查询拆开(即将该子查询中的表、视图从子查询中拿出来,然后和外部查询中的表、视图做表连接),要么是不拆开但是会把子查询转换为一个内嵌视图(inline view),然后再和外部查询中的表、视图做表连接。

在 Oracle 10g 及其以后的版本中,对于那种不拆开子查询但是会把该子查询转换成一个内嵌视图的子查询展开,只有当经过子查询展开后的等价改写 SQL 的成本值小于原 SQL 的成本值时,Oracle 才会对原 SQL 执行子查询展开。

子查询展开通常都会提高原 SQL 的执行效率,因为如果原 SQL 不做子查询展开,那么通常情况下,该子查询就会在其执行计划的最后一步才被执行,并且会走 FILTER 类型的执行计划,这样就意味着对于外部查询所在结果集中的每一条记录,该子查询都会被当作一个独立的执行单元来执行一次,外部查询所在的结果集有多少条记录,该子查询就会被执行多少次(近似这样,实际上不完全是这样)。

Oracle 数据库里子查询前的 where 条件如果是如下这些条件之一,那么这种类型的目标 SQL 在满足了一定的条件后就可以做子查询展开:

  • SINGLE_ROW(即 =, <, >, <=, >=, <>),意味着子查询的返回结果至多只会有一条记录。
  • 可能返回多条记录的子查询:EXISTS, NOT EXISTS, IN, NOT IN, ANY, ALL

继续阅读

《基于 Oracle 的 SQL 优化》笔记 第一章 Oracle 里的优化器

CBO 相关基础

cardinality :指定结果集的行数,实际上表示对目标 SQL 的某个具体步骤的执行结果所包含记录数的估算。如果是针对整个目标 SQL,那么此时的 cardinality 就表示对该 SQL 最终执行结果所包含记录数的估算。cardinality 值越大,对应的成本就越高。

selectivity 选择度:是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。计算公式为 selectivity = 指定谓词条件后返回结果集的记录数 / 未施加任何谓词条件的原始结果集的记录数。这个取值处于 0 - 1 之间,值越小越好,说明选择性高(能过滤更多不符合条件的数据)。

对于每一种具体情况,selectivity 会有不同的计算公式。

最简单的情况是对目标列做等值查询时 selectivity 的计算公式,在目标列上没有直方图且没有 NULL 值的情况下,用目标列做等值查询时的 selectivity 的计算公式为 selectivity = 1 / NUM_DISTINCT,NUM_DISTINCT 表示目标列的 distinct 值的数量。

transitivity 可传递性:是 CBO 在查询转换中所做的第一件事,是指 CBO 可能会对原目标 SQL 做简单的等价改写,即在原目标 SQL 中加上根据该 SQL 现有的谓词条件推算出来的新的谓词条件,这么做的目的是提供更多的执行路径给 CBO 做选择,进而得到更高效执行计划的可能性。

Oracle 中可传递性分下列三种情形:

  • 简单的谓词传递:比如 t1.c1 = t2.c1 and t1.c1 = 10 ,额外添加 t2.c1 = 10
  • 连接谓词传递:比如 t1.c1 = t2.c1 and t2.c1 = t3.c1,额外添加 t1.c1 = t3.c1
  • 外连接谓词传递:比如 t1.c1 = t2.c1(+) and t1.c1 = 10,额外添加 t2.c1 (+)= 10

继续阅读

《基于 Oracle 的 SQL 优化》笔记 第二章 Oracle 里的执行计划

如何查看执行计划的顺序

简单的原则:根据往右的缩进,最右的先执行,即从右往左;缩进相同的,从上往下执行。

Oracle 里的常见的执行计划

与表访问相关的执行计划

全表扫描对应的执行计划关键词是“TABLE ACCESS FULL”;
rowid 扫描对应的关键词是“TABLE ACCESS BY USER ROWID”(目标 SQL 直接指定了 rowid)或 “TABLE ACCESS BY INDEX ROWID”(先检索索引,然后用 rowid 访问表)。

与 B-tree 相关的执行计划

连接类型          执行计划关键词
排序合并连接        SORT JOINMERGE JOIN
嵌套循环          NESTED LOOPS
哈希连接          HASH JOIN
反连接           ANTI(可能是 HASH JOIN ANTI, MERGE JOIN ANTI, NESTED LOOPS ANTI
半连接           SEMI(可能是 HASH JOIN SEMI, MERGE JOIN SEMI, NESTED JOIN SEMI

半连接对连接结果进行必要的去重。

其他执行计划

继续阅读

Oracle B-Tree 索引的结构、特点及索引扫描方式

《Oracle DBA 手记 — 数据库诊断案例与性能优化实践》摘录(P218)

Oracle 数据库中索引的存储结构使用的是 B Tree 的一种变体,称为 B*Tree (B Star Tree),在数据库中存储数据以块为单位。
Oracle B-Tree索引结构

一些术语:

  • 节点 M 的深度:从树根节点到节点 M 的最短路径长度。图中根节点 Root 的深度是 0,节点 L1-1 的深度是 1,节点 L0-1 的深度是 2。
  • 节点 M 的层数:节点 M 的层数与其深度是相同的。
  • 树的高度:树的深度值最大的那个节点,其深度 +1 即为树的高度。图中树的高度为 3。

继续阅读

《Oracle数据库性能优化》笔记 – 索引与 bitmap 索引

需要复杂技巧及高深知识来解决所遇到的问题的情形并不多,相对来说,在对基本概念有清晰认识的情形下,解决问题的切入口通常都会比较准确。

索引的作用

索引是以最快的路径找到所需要的数据;另一个功用是对数据完整性的强制控制。

在下面两种情形下,系统会自动为表的列建立索引:

  1. 当表的列被指定为 primary key 时;
  2. 当表的列有 unique constraint 时。

当一个表中有 foreign key 存在时,通常也建议这个列使用索引。

索引管理的常见问题

  1. 最近是否做过数据库的分析
  2. 索引的数量,过多或过少都是不好的

索引不被使用的情况

  • 当对同一个表的两个列进行比较的情形下,索引有时不会被使用。

  • null 值。如果 where 语句中出现 is null 或者 is not null 时,索引就不能被使用。

  • 当 where 语句中存在有 not function 时,比如 not in, not exist, column <> value, column > value, column2 < value 等情形下,索引不能被使用。

  • 当 select 语句使用了 single-row function时,如 nvl, to_char, lower 等,索引不能被使用。

  • 通配符 % 或者 _ 作为查询字符串的第一个字符时,索引页无法使用。如果查询字符串的第一个字确定,则可以使用索引。

继续阅读