《基于 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

继续阅读