Oracle SQL 性能优化技巧整理

    1.选用适合的ORACLE优化器

     ORACLE的优化器共有3种

     A 、 RULE ( 基于 规则 ) b 、 COST ( 基于成本 ) c 、 CHOOSE ( 选择 性 )

      设 置缺省的 优 化器，可以通 过对 init.ora 文件中 OPTIMIZER_MODE 参数的各 种 声明，如 RULE ， COST ， CHOOSE ， ALL_ROWS ， FIRST_ROWS 。 你当然也在 SQL 句 级 或是会 话 (session) 级对 其 进 行覆盖。

      为 了使用基于成本的 优 化器 (CBO ， Cost-Based Optimizer) ，你必 须经 常运行 analyze 命令，以增加数据 库 中的 对 象 统计 信息 (object statistics) 的准确性。

      如果数据 库 的 优 化器模式 设 置 为选择 性 (CHOOSE) ，那 么实际 的 优 化器模式将和是否运行 过 analyze 命令有 关 。 如果 table 已 经 被 analyze 过 ， 优 化器模式将自 动 成 为 CBO ， 反之，数据 库 将采用 RULE 形式的 优 化器。

在缺省情况下， ORACLE 采用 CHOOSE 优 化器， 为 了避免那些不必要的全表 扫 描 (full table scan) ， 你必 须 尽量避免 使用 CHOOSE 优 化器，而直接采用基于 规则 或者基于成本的 优 化器。

   2. 访问 Table 的方式

      ORACLE 采用两 种访问 表中 记录 的方式：
     A 、 全表 扫 描
           全表 扫 描就是 顺 序地 访问 表中 每 条 记录 。 ORACLE 采用一次 读 入多个数据 块 (database block) 的方式 优 化全表 扫 描。
     B 、 通 过 ROWID 访问 表
           你可以采用基于 ROWID 的 访问 方式情况，提高 访问 表的效率， ROWID 包含了表中 记录 的物理位置信息。 ORACLE 采用索引 (INDEX) 实现 了数据和存放数据的物理位置 (ROWID) 之 间 的 联 系。通常索引提供了快速 访问 ROWID 的方法，因此那些基于索引列的 查询 就可以得到性能上的提高。

3. 共享 SQL 语 句

      为 了不重 复 解析相同的 SQL 语 句，在第一次解析之后， ORACLE 将 SQL 语 句存放在内存中。 这块 位于系 统 全局区域 SGA(system global area) 的共享池 (shared buffer pool) 中的内存可以被所有的数据 库 用 户 共享。 因此，当你 执 行一个 SQL 语 句 ( 有 时 被称 为 一个游 标 ) 时 ，如果它和之前的 执 行 过 的 语 句 完全相同， ORACLE 就能很快 获 得已 经 被解析的 语 句以及最好的 执 行路径。 ORACLE 的 这 个功能大大地提高了 SQL 的 执 行性能并 节 省了内存的使用。
      可惜的是 ORACLE 只 对简单 的表提供高速 缓 冲 (cache buffering) ， 这 个功能并不适用于多表 连 接 查询 。
      数据 库 管理 员 必 须 在 init.ora 中 为这 个区域 设 置合适的参数，当 这 个内存区域越大，就可以保留更多的 语 句，当然被共享的可能性也就越大了。
      当你向 ORACLE 提交一个 SQL 语 句， ORACLE 会首先在 这块 内存中 查 找相同的 语 句 。 这 里需要注明的是， ORACLE 对 两者采取的是一 种严 格匹配，要达成共享， SQL 语 句必 须 完全相同 ( 包括空格， 换 行等 ) 。
      数据 库 管理 员 必 须 在 init.ora 中 为这 个区域 设 置合适的参数，当 这 个内存区域越大，就可以保留更多的 语 句，当然被共享的可能性也就越大了。
      共享的 语 句必 须满 足三个条件：
     A 、 字符 级 的比 较 ： 当前被 执 行的 语 句和共享池中的 语 句必 须 完全相同。
      B 、 两个 语 句所指的 对 象必 须 完全相同：
     C 、 两个 SQL 语 句中必 须 使用相同的名字的 绑 定 变 量 (bind variables) 。

4. 选择 最有效率的表名 顺 序 ( 只在基于 规则 的 优 化器中有效 )

      ORACLE 的解析器按照从右到左的 顺 序 处 理 FROM 子句中的表名，因此 FROM 子句中写在最后的表 ( 基 础 表 driving table) 将被最先 处 理。在 FROM 子句中包含多个表的情况下，你必 须选择记录 条数最少的表作 为 基 础 表。当 ORACLE 处 理多个表 时 ， 会运用排序及合并的方式 连 接它 们 。首先， 扫 描第一个表 (FROM 子句中最后的那个表 ) 并 对记录进 行派序，然后 扫 描第二个表 (FROM 子句中最后第二个表 ) ，最后将所有从第二个表中 检 索出的 记录 与第一个表中合适 记录进 行合并。
      如果有 3 个以上的表 连 接 查询 ， 那就需要 选择 交叉表 (intersection table) 作 为 基 础 表， 交叉表是指那个被其他表所引用的表。

5.WHERE 子句中的 连 接 顺 序

      ORACLE 采用自下而上的 顺 序解析 WHERE 子句，根据 这 个原理，表之 间 的 连 接必 须 写在其他 WHERE 条件之前， 那些可以 过滤 掉最大数量 记录 的条件必 须 写在 WHERE 子句的末尾。

6.SELECT 子句中避免使用 ' * '

      当你想在 SELECT 子句中列出所有的 COLUMN 时 ，使用 动态 SQL 列引用 '*' 是一个方便的方法。不幸的是， 这 是一个非常低效的方法。 实际 上， ORACLE 在解析的 过 程中， 会将 '*' 依次 转换 成所有的列名， 这 个工作是通 过查询 数据字典完成的， 这 意味着将耗 费 更多的 时间 。

7. 减少 访问 数据 库 的次数

      当 执 行 每 条 SQL 语 句 时 ， ORACLE 在内部 执 行了 许 多工作：解析 SQL 语 句，估算索引的利用率， 绑 定 变 量， 读 数据 块 等等。由此可 见 ，减少 访问 数据 库 的次数，就能 实际 上减少 ORACLE 的工作量。

8. 使用 DECODE 函数来减少 处 理 时间

      使用 DECODE 函数可以避免重 复扫 描相同 记录 或重 复连 接相同的表。

9. 整合 简单 ，无 关联 的数据 库访问

      如果你有几个 简单 的数据 库查询语 句，你可以把它 们 整合到一个 查询 中 ( 即使它 们 之 间 没有 关 系 )

10. 删 除重 复记录

11. 用 TRUNCATE 替代 DELETE

      当 删 除表中的 记录时 ，在通常情况下， 回 滚 段 (rollback segments ) 用来存放可以被恢 复 的信息。 如果你没有 COMMIT 事 务 ， ORACLE 会将数据恢 复 到 删 除之前的状 态 ( 准确地 说 是恢 复 到 执 行 删 除命令之前的状况 ) 。

      而当运用 TRUNCATE 时 ， 回 滚 段不再存放任何可被恢 复 的信息。当命令运行后，数据不能被恢 复 。因此很少的 资 源被 调 用， 执 行 时间 也会很短。

 

12. 尽量多使用 COMMIT

      只要有可能，在程序中尽量多使用 COMMIT ， 这样 程序的性能得到提高，需求也会因 为 COMMIT 所 释 放的 资 源而减少
     COMMIT 所 释 放的 资 源：
     A 、 回 滚 段上用于恢 复 数据的信息。
　   B 、被程序 语 句 获 得的 锁 。
     C 、 redo log buffer 中的空 间 。
     D 、 ORACLE 为 管理上述 3 种资 源中的内部花 费 。

13. 计 算 记录 条数

      和一般的 观 点相反， count(*) 比 count(1) 稍快，当然如果可以通 过 索引 检 索， 对 索引列的 计 数仍旧是最快的。例如 COUNT(EMPNO)

14. 用 Where 子句替 换 HAVING 子句

      避免使用 HAVING 子句， HAVING 只会在 检 索出所有 记录 之后才 对结 果集 进 行 过滤 。 这 个 处 理需要排序， 总计 等操作。如果能通 过 WHERE 子句限制 记录 的数目，那就能减少 这 方面的 开销 。

 

15. 减少 对 表的 查询

      在含有子 查询 的 SQL 语 句中，要特 别 注意减少 对 表的 查询 。

16. 通 过 内部函数提高 SQL 效率。

17. 使用表的 别 名 (Alias)

      当在 SQL 语 句中 连 接多个表 时 ， 请 使用表的 别 名并把 别 名前 缀 于 每 个 Column 上。 这样 一来，就可以减少解析的 时间 并减少那些由 Column 歧 义 引起的 语 法 错误 。

18. 用 EXISTS 替代 IN

      在 许 多基于基 础 表的 查询 中， 为 了 满 足一个条件，往往需要 对 另一个表 进 行 联 接。在 这种 情况下，使用 EXISTS( 或 NOT EXISTS) 通常将提高 查询 的效率。

19. 用 NOT EXISTS 替代 NOT IN

      在子 查询 中， NOT IN 子句将 执 行一个内部的排序和合并。 无 论 在哪 种 情况下， NOT IN 都是最低效的 ( 因 为 它 对 子 查询 中的表 执 行了一个全表遍 历 ) 。 为 了避免使用 NOT IN ，我 们 可以把它改写成外 连 接 (Outer Joins) 或 NOT EXISTS 。

20. 用表 连 接替 换 EXISTS

      通常来 说 ， 采用表 连 接的方式比 EXISTS 更有效率

21. 用 EXISTS 替 换 DISTINCT

      当提交一个包含一 对 多表信息 ( 比如部 门 表和雇 员 表 ) 的 查询时 ，避免在 SELECT 子句中使用 DISTINCT 。 一般可以考 虑 用 EXIST 替 换

Oracle 内存 结 构 ( 三 )----Process Memory 的 详细 信息

The Process Memory :
  除了 SGA(System Global Area) 之外 ,Oracle 进 程 还 使用下面三个全局区 :
  The Process Global Area ( PGA )
  The User Global Area (UGA)
  The Call Global Area (CGA)

  很多人都搞不清楚 PGA 和 UGA 两者之 间 的区 别 , 实际 上两者之 间 的区 别 跟一个 进 程和一个会 话 之 间 的区 别 是 类 似的 . 尽管 说进 程和会 话 之 间 一般都是一 对 一的 关 系 , 但 实际 上比 这 个更 复杂 . 一个很明 显 的情况是 MTS 配置 , 会 话 往往会比 进 程多得多 . 在 这种 配置下 , 每 一个 进 程会有一个 PGA , 每 一个会 话 会有一个 UGA. PGA 所包含的信息跟会 话 是无任何 关联 的 , 而 UGA 包含的信息是以特定的会 话为 基 础 的 .

  The PGA :

  进 程全局区 ( PGA 即可以理解 ) 为 Process Global Area, 也可以理解 为 Program Global Area. 它的内存段是在 进 程私有区 (Process Private Memory 而不是在共享区 (Shared Memory 它是个全局区意味着它包含了所有代 ). ) 码 有可能 进 入的全局 变 量和数据 结 构 , 但是它是不被所有 进 程共享的 . 每 个 Oracle 的服 务 器 进 程都包含有属于自己的 PGA , 它只包含了本 进 程的相 关 特定信息 . PGA 中的 结 构不需要由 latches 来保 护 , 因 为 其它的 进 程是不能 进 入到 这 里面来 访问 的 .

  PGA 包含的是有 关进 程正在使用的操作系 统资 源信息以及 进 程的状 态 信息 , 而其它的 进 程所使用的 Oracle 的共享 资 源是在 SGA 中 . PGA 是私有的而不是共享的 , 这 个机制是有必要的 , 因 为 当 进 程死掉后可以把 这 些 资 源清除和 释 放掉 .

  PGA 包含两个主要区域 :Fixed PGA 和 Variable PGA 或称 为 PGA Heap. Fixed PGA 的作用跟 Fixed SGA 是 类 似的 , 都包含原子 变 量 ( 不可分的 ), 小的数据 结 构和指向 Variable PGA 的指 针 .

  Variable PGA 是一个堆 . 它的 Chunks 可以从 Fixed Table X$KSMPP 查 看得到 , 这 个表的 结 构跟前面有提到的 X$KSMSP 是相同的 . PGA 包含了一些有 HEAP 关 Fixed Table 的永久性内存 , 它跟某些参数的 设 置有依 赖关 系 . 这 些参数包含 DB_FILES,LOG_FILES,CONTROL_FILES.

  The UGA:

  UGA(User Global Area) 包含的是特定会 话 的信息 , 有如下一些 :

  所打 开 游 标 的持 续 和运行 时间 内的区域

  包的状 态 信息 , 特定的 变 量

  Java 会 话 状 态

  可以用的 ROLES

  被 ENABLE 的跟踪事件

  起作用的 NLS 参数 设 置

  打 开 的 DBLINK

  会 话 的入口控制

  跟 PGA 一 样 ,UGA 也由两区 组 成 :Fixed UGA 和 Variable UGA, 也称 为 UGA HEAP. Fixed UGA 包含了大 约 70 个原子 变 量 , 小的数据 结 构和指向 Variable UGA 的指 针 .

  UGA HEAP 中的 Chunks 可以从它 们 自己的会 话 中通 过查 看表 X$KSMUP 获 得相 关 信息 , 这 个表的 结 构跟 X$KSMSP 是一 样 的 .UGA HEAP 包含了一些有 关 fixed tables 的永久性内存段 , 跟一些参数的 设 置有依 赖关 系 . 这 些参数有 OPEN_CURSORS,OPEN_LINKS, 和 MAX _ENABLE_ROLES.

  UGA 在内存中的位置依 赖 于会 话 的配置方式 . 如果会 话连 接的配置方式是 专 用服 务 器模式 (DDS) 即是一个会 话对应 一个 进 程 , 则 UGA 是放在 PGA 中的 . 在 PGA 中 ,Fixed UGA 是其中的一个 Chunk, 而 UGA HEAP 是 PGA 的一个子堆 (Subheap). 如果会 话连 接是配置 为 共享服 务 器模式 (MTS), Fixed UGA 是 SHARED POOL 中的一个 Chunk, 而 UGA HEAP 则 是 SHARED POOL 中的子堆 (Subheap)

  The CGA:

  跟其它的全局区不同 ,Call Global Area 是短 暂 性存在的 . 它只有在 调 用数据期 间 存在 , 一般是在 对实 例的最低 级别 的 调 用 时 才需要 CGA, 如下 :

  分析一个 SQL 语 句

  执 行一个 SQL 语 句

  取出一个 SELECT 语 句的 输 出

  一个 单 独的 CGA 在 递归调 用 时 是需要的 . 在 SQL 语 句的分析 过 程中 , 对 数据字典信息的 递归调 用是需要的 , 因 为 要 对 SQL 语 句 进 行 语 法分析 , 还 有在 语 句的 优 化期 间 要 计 算 执 行 计 划 . 执 行 PL/SQL 块时 在 处 理 SQL 语 句的 执 行 时 也是需要 递归调 用的 , 在 DML 语 句的 执 行 时 要 处 理触 发 器 执 行也是需要 递归调 用的 .

  不管 UGA 是放在 PGA 中 还 是在 SGA 中 ,CGA 都是 PGA 的一个子堆 (Subheap). 这 个事 实 的一个重要推 论 是在一个 调 用的期 间 会 话 必 须 是一个 进 程 . 对 于在一个 MTS 的 Oracle 数据 库进 程 应 用 开发时关 于 这 一点的理解是很重要的 . 如果相 应 的 调 用 较 多 , 就得增加 processes 的数量以适 应调 用的增加 .

  没有 CGA 中的数据 结 构 ,CALLS 是没法工作的 . 而 实际 上跟一次 CALL 相 关 的数据 结 构一般都是放在 UGA 中 , 如 SQL AREA,PL/SQL AREA 和 SORT AREA 它 们 都必 须 在 UGA 中 , 因 为 它 们 要在各 CALLS 之 间 要一直存在并且可用 . 而 CGA 中所包含的数据 结 构是要在一次 CALL 结 束后能 够释 放的 . 例如 CGA 包含了 关 于 递归调 用的信息 , 直接 I/O BUFFER 等 还 有其它的一些 临时 性的数据 结 构 .

  Java Call Memory 也是在 CGA 中 . 这 一段内存比 Oracle 的其它内存段管理得更密集 . 它分成三个 Space: Stack Space, New Space, Old Space. 在 New Space 和 Old Space 中不再被参考使用的 Chunks, 根据它 们 在使用期 间 的 长 度及 SIZE 的不同 , 在 调 用的 执 行 过 程中将被当成不用的 Chunks 收集起来 .New Space Chunks 很多次的不用的 Chunks 的反 复 收集 过 程中没有被收集的 Chunks 将会被放到 Old Space Chunks 中 . 这 是在 Oracle 内存管理中唯一的一个 废 物收集 (garbage collection), 其它的 Oracle 内存段都是 释 放 Dead Chunks.

  Process Memory Allocation

  跟 SGA 不一 样 的是 ,SGA 在 实 例启 动 之后 SIZE 就已 经 是定下来的 , 而 PGA 的 SIZE 是会增 长 的 . 通 过 使用 malloc() 或者 sbrk() 系 统调 用来 为进 程增加堆数据段大小而使得 PGA 的 SIZE 的增 长 .OS 的新虚 拟 内存会被做 为 PGA HEAP 中的一个新的区被加到 PGA 中来 . 这 些区一般只几 KB 大 , 如果有需要 ,Oracle 将会 给 分配上千个区 .

  操作系 统对每 个 进 程的堆数据段的增 长 是有限制的 . 大部分的情况是操作系 统 的内存参数 进 行限制 (kernel parameter: MAXDSIZ), 有一些情况它的缺省 值 是可以以 每 个 进 程 为 基准 进 行修改的 . 对 于所有的 进 程 , 操作系 统对 整个虚 拟 内存也有一个系 统 全局性的限制 , 这 个限制跟系 统 的 SWAP SPACE 相 关 . 一旦超 过 了 这 两个限制 ,Oracle 的 进 程在 执 行中会遇到 ORA-4030 错误 .

  ORA-4030 这 个 错误 的 产 生一般不是因 为每 个 进 程的 资 源限制而是因 为 SWAP SPACE 空 间 不足造成 . 为 了 诊 断 这 个 问题 可以使用操作系 统 的一些 选项 来 查 看 SWAP SPACE 的使用情况 . 另外 , 在一些操作系 统 中 ,Oracle 包含了一个工具叫 maxmem, 它可以用来 查 看 每 个 进 程可以被分配的堆数据段的最大 SIZE 以及哪一个限制是第一次超 过 的 .

  如果 这 个 问题 的出 现 是因 为 SWAP SPACE 空 间 不足 , 而且 换页 的 动 作非常 频 繁而且 较 多 , 则 需要减少系 统 一 级 的虚 拟 内存的使用 , 这 个可以通 过 减少 进 程数也可以通 过 减少 每 个 进 程的内存限制 . 如果 换页动 作不 频 繁而且比 较 少 , 则 需要 调 大 SWAP SPACE SIZE.

 

Process Memory Deallocation:

  Oracle 堆的增 长 比它 们 的收 缩 要来得容易 , 当然它 们 的 SIZE 也是可以收 缩 的 . 在 V$MYSTAT 和 V$SESSTAT 视图 中 , session 的 统计 信息 session uga memory 和 session pga 分 memory 别显 示了当前 session 的 UGA 和 PGA 的内存大小 , 包含内部的空 闲 空 间 . 相 应 的 统计 信息 session uga memory 和 session pga 分 memory max max 别显 示了在 session 的生存期 间 所使用 过 得最大的 UGA 和最大的 PGA .

  UGA 和 PGA 只有在特定的操作后才会收 缩 , 这 些操作如一次磁 盘 排序的合并操作 , 或者用程序 DBMS_ SESSION .FREE_UNUSED_USER_ MEMORY 显 示 释 放内存 . 只有整个 free heap extent 会被 释 放 给 父堆或者是 进 程堆数据段 , 所以有一部分的内部 free space 在内存 释 放后仍然存在于 subheap 中 .

  在大多的操作系 统环 境下 ,Oracle 是不会减少 进 程堆数据段也不会 释 放虚 拟 内存并将其返 还给 操作系 统 的 . 所以从一个操作系 统 的 查 看中 , 一个 Oracle 的 进 程将会把虚 拟 内存 SIZE 作 为 HWM 而保留着 . 如果有必要 时 ,Oracle 是会将一些没用的虚 拟 内存 页换页 出去的 . 因 为这 个原因 , 有 关 Oracle 进 程的虚 拟 内存 页 的操作系 统统计 信息都是很 难 理解的 . 所以一般用的是 Oracle 内部 统计 信息来代替使用操作系 统 的 统计 信息 .

  程序 DBMS_ SESSION 只能在 .FREE_UNUSED_USER_ MEMORY 连 接是配置 为 MTS 模式的 应 用才能使用 . 这 个最好是少点使用 , 因 为 它只 释 放大的包的 array 变 量所占用的内存返 还给 Large Pool 或者是 Shared Pool. 一般地 ,UGA HEAP 的内存 应该 首先被 释 放 , 可以通 过 指派新的空 array 给 array 变 量使用 , 也可以通 过调 用程序 DBMS_ SESSION .RESET_PACKAGE.