Oracle物理设计及性能优化

Oracle物理设计及性能优化

设计资料整理

汤延涛

yantao929@163.com

2007-12-20

目录

1 2

前言 ........................................................................................................................................... 1 硬件体系构架 ............................................................................................................................ 1 2.1 2.2 2.3

SMP .......................................................................................................................................... 1 MPP .......................................................................................................................................... 2 PVFS(并行虚拟文件系统) ....................................................................................................... 4

概述 ................................................................................................................................. 4 2.3.2 PVFS的存取机制 ................................................................................................................ 5 2.3.3 PVFS的应用实例与性能 .................................................................................................... 7 2.4 OPS(ORACLE PARALLEL SERVER) ..................................................................................................... 8

2.4.1 2.4.2 2.4.3 3

2.3.1

体系结构 ......................................................................................................................... 8

并行处理 ......................................................................................................................... 9 分区技术 ....................................................................................................................... 12

ORACLE体系结构 .................................................................................................................... 12 3.1 3.2 3.3

内存结构和进程结构 ........................................................................................................... 12 ORACLE实例 ............................................................................................................................ 13 ORACLE 10G动态内存管理 ..................................................................................................... 14

3.3.1

系统全局区SGA（System Global Area） .................................................................... 14

3.3.2 Oracle实例的进程结构（Process Structure） ................................................................ 22

4

存储管理 ................................................................................................................................. 28 4.1 4.2 4.3 5

ASM（自动存储管理） ........................................................................................................ 28 DMT,LMT,ASSM ...................................................................................................................... 28 自动管理问题 ....................................................................................................................... 32

时间空间转换平衡 .................................................................................................................. 32 5.1 5.2

数据压缩 ............................................................................................................................... 32 索引机制分析 ....................................................................................................................... 32

5.2.1 Index full scan VS Index fast full scan ................................................................................. 32 5.2.2 Index range scan VS Index skip scan .................................................................................. 33 6

设计注意问题 .......................................................................................................................... 34 6.1 6.2 6.3 6.4 6.5 6.6 6.7 7

字符集问题 ........................................................................................................................... 34 代理主键 ............................................................................................................................... 34 程序监控 ............................................................................................................................... 34 临时表空间和回滚表空间释放 ............................................................................................ 34 并行操作 ............................................................................................................................... 34 关于APPEND提示 ................................................................................................................ 35 数据库统计信息收集 ........................................................................................................... 35

SQL语句优化 .......................................................................................................................... 35 7.1

选用适合的ORACLE优化器 ................................................................................................. 35

7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.9 7.10 7.11 7.12 7.13 7.14 7.15 7.16 7.17 7.18 7.19 7.20 7.21 7.22 7.23 7.24 7.25 7.26 7.27 7.28 7.29 7.30 7.31 7.32 7.33 7.34 7.35 7.36 7.37 7.38 7.39 7.40 7.41 7.42 7.43 7.44 7.45

访问TABLE的方式 ................................................................................................................. 35 共享SQL语句 ....................................................................................................................... 36 选择最有效率的表名顺序.................................................................................................... 37 WHERE子句中的连接顺序 .................................................................................................. 38 SELECT子句中避免使用 ‘ * ‘ ................................................................................................ 38 减少访问数据库的次数........................................................................................................ 38 使用DECODE函数来减少处理时间 .................................................................................... 39 整合简单,无关联的数据库访问 ........................................................................................... 40 删除重复记录 ....................................................................................................................... 41 用TRUNCATE替代DELETE ................................................................................................... 41 尽量多使用COMMIT ............................................................................................................ 41 计算记录条数 ....................................................................................................................... 41 用WHERE子句替换HAVING子句 ........................................................................................ 41 减少对表的查询 ................................................................................................................... 42 通过内部函数提高SQL效率 ............................................................................................... 43 使用表的别名(ALIAS) ............................................................................................................. 44 用EXISTS替代IN .................................................................................................................. 44 用NOT EXISTS替代NOT IN ................................................................................................... 44 用表连接替换EXISTS ............................................................................................................ 45 用EXISTS替换DISTINCT ....................................................................................................... 45 识别“低效执行”的SQL语句 ................................................................................................ 46 使用TKPROF 工具来查询SQL性能状态 ............................................................................ 46 用EXPLAIN PLAN 分析SQL语句 ......................................................................................... 47 用索引提高效率 ................................................................................................................... 48 索引的操作 ........................................................................................................................... 48 基础表的选择 ....................................................................................................................... 50 多个平等的索引 ................................................................................................................... 50 等式比较和范围比较 ........................................................................................................... 51 不明确的索引等级 ............................................................................................................... 51 强制索引失效 ....................................................................................................................... 52 避免在索引列上使用计算.................................................................................................... 53 自动选择索引 ....................................................................................................................... 54 避免在索引列上使用NOT ................................................................................................... 54 用>=替代> ............................................................................................................................. 55 用UNION替换OR (适用于索引列) ..................................................................................... 56 用IN来替换OR .................................................................................................................... 58 避免在索引列上使用IS NULL和IS NOT NULL ..................................................................... 59 总是使用索引的第一个列.................................................................................................... 59 ORACLE内部操作 .................................................................................................................. 60 用UNION-ALL 替换UNION ( 如果有可能的话) .................................................................. 60 使用提示(HINTS) ..................................................................................................................... 61 用WHERE替代ORDER BY .................................................................................................... 62 避免改变索引列的类型........................................................................................................ 63 需要当心的WHERE子句 ..................................................................................................... 64

7.46 7.47 7.48 7.49 7.50 7.51 7.52 7.53

连接多个扫描 ....................................................................................................................... 65 CBO下使用更具选择性的索引 ............................................................................................ 66 避免使用耗费资源的操作.................................................................................................... 66 优化GROUP BY ...................................................................................................................... 66 使用日期 ............................................................................................................................... 67 使用显式的游标(CURSORS) .................................................................................................. 67 优化EXPORT和IMPORT ...................................................................................................... 67 分离表和索引 ....................................................................................................................... 68

1 前言

要对数据库进行优化，首先要从数据库的设计方面入手，包含数据的概念模型，逻辑模型，物理模型（设计对以后的优化维护起决定性的作用，好的设计是优化维护的决定性条件）；所以：在设计阶段一定要确定好系统的模型构架，逻辑构架，硬件构架等，尽量做到软件和硬件的无缝结合；维护只是对程序的处理流程和相关的SQL进行调整，而不会太多涉及到对结构的调整；

本文主要在硬件体系结构，存储管理机制，数据压缩，SQL语句优化等几个方面进行数据库设计及程序开发方面的描述。 备注：（此文档仅用于内部交流，文档中引用从网上收集的图片等资料，为了保持格式及一致性，没有将引用来源加入文档中，如果侵犯个人或团体利益，请告知，我们将从文档中删除此部分内容或者添加参考资料来源）

2 硬件体系构架

2.1 SMP

SMP的全称是\对称多处理\（Symmetrical Multi-Processing）技术，是指在一个计算机上汇集了一组处理器(多CPU),各CPU之间共享内存子系统以及总线结构。它是相对非对称多处理技术而言的、应用十分广泛的并行技术。在这种架构中，一台电脑不再由单个CPU组成，而同时由多个处理器运行操作系统的单一复本，并共享内存和一台计算机的其他资源。虽然同时使用多个CPU，但是从管理的角度来看，它们的表现就像一台单机一样。系统将任务队列对称地分布于多个CPU之上，从而极大地提高了整个系统的数据处理能力。所有的处理器都可以平等地访问内存、I/O和外部中断。在对称多处理系统中，系统资源被系统中所有CPU共享，工作负载能够均匀地分配到所有可用处理器之上。 我们平时所说的双CPU系统，实际上是对称多处理系统中最常见的一种，通常称为\路对称多处理\，它在普通的商业、家庭应用之中并没有太多实际用途，但在专业制作，如3DMax Studio、Photoshop等软件应用中获得了非常良好的性能表现，是组建廉价工作站的良好伙伴。随着用户应用水平的提高，只使用单个的处理器确实已经很难满足实际应用的需求，因而各服务器厂商纷纷通过采用对称多处理系统来解决这一矛盾。在国内市场上这类机型的处理器一般以4个或8个为主，有少数是16个处理器。但是一般来讲，SMP结构的机器可扩展性较差，很难做到100个以上多处理器，常规的一般是8个到16个，不过这对于多数的用户来说已经够用了。这种机器的好处在于它的使用方式和微机或工作站的区别不大，编程的变化相对来说比较小，原来用微机工作站编写的程序如果要移植到SMP机器上使用，改动起来也相对比较容易。SMP结构的机型可用性比较差。因为4个或8个处理器共享一个操作系统和一个存储器，一旦操作系统出现了问题，整个机器就完全瘫痪掉了。而且由于这个机器的可扩展性较差，不容易保护

用户的投资。但是这类机型技术比较成熟，相应的软件也比较多，因此现在国内市场上推出的并行机大量都是这一种。PC服务器中最常见的对称多处理系统通常采用2路、4路、6路或8路处理器。目前UNIX服务器可支持最多64个CPU的系统，如Sun公司的产品Enterprise 10000。SMP系统中最关键的技术是如何更好地解决多个处理器的相互通讯和协调问题。

要组建SMP系统，首先最关键的一点就是需要合适的CPU相配合。我们平时看到的CPU都是单颗使用，所以看不出来它们有什么区别，但是，实际上，支持SMP功能并不是没有条件的，随意拿几块CPU来就可以建立多处理系统那简直是天方夜谈。要实现SMP功能，我们使用的CPU必须具备以下要求： 1、CPU内部必须内臵APIC（Advanced Programmable Interrupt Controllers）单元。Intel 多处理规范的核心就是高级可编程中断控制器（Advanced Programmable Interrupt Controllers--APICs）的使用。CPU通过彼此发送中断来完成它们之间的通信。通过给中断附加动作（actions），不同的CPU可以在某种程度上彼此进行控制。每个CPU有自己的APIC（成为那个CPU的本地APIC），并且还有一个I/O APIC来处理由I/O设备引起的中断，这个I/O APIC是安装在主板上的，但每个CPU上的APIC则不可或缺，否则将无法处理多CPU之间的中断协调。

2、相同的产品型号，同样类型的CPU核心。例如，虽然Athlon和Pentium III各自都内臵有APIC单元，想要让它们一起建立SMP系统是不可能的，当然，即使是Celeron和Pentium III，那样的可能性也为0，甚至Coppermine核心的Pentium III和Tualatin的Pentium III也不能建立SMP系统--这是因为他们的运行指令不完全相同，APIC中断协调差异也很大。

3、完全相同的运行频率。如果要建立双Pentium III系统，必须两颗866MHz或者两颗1000MHz处理器，不可以用一颗866MHz，另一颗1000MHz来组建，否则系统将无法正常点亮。

4、尽可能保持相同的产品序列编号。即使是同样核心的相同频率处理器，由于生产批次不同也会造成不可思议的问题。两个生产批次的CPU作为双处理器运行的时候，有可能会发生一颗CPU负担过高，而另一颗负担很少的情况，无法发挥最大性能，更糟糕的是可能导致死机，因此，应该尽可能选择同一批生产的处理器来组建SMP系统。

2.2 MPP

SMP系统与MPP系统比较（说明：SMP集群系统和MPP比较更有意义） SMP (Symmetric Multi Processing),对称多处理系统内有许多紧耦合多处理器，在这样的系统中，所有的CPU共享全部资源，如总线，内存和I/O系统等，操作系统或管理数据库的复本只有一个，这种系统有一个最大的特点就是共享所有资源。

MPP (Massively Parallel Processing)，大规模并行处理系统，这样的系统是由许多松耦合的处理单元组成的，要注意的是这里指的是处理单元而不是处理器。每个单元内的CPU都有自己私有的资源，如总线，内存，硬盘等。在每个单元内都有操作系统和管理数据库的实例复本。这种结构最大的特点在于不共享资源。

既然有两种结构，那它们各有什么特点呢？采用什么结构比较合适呢？通常情况下，MPP系统因为要在不同处理单元之间传送信息（请注意上图），所以它的效率要比SMP要差一点，但是这也不是绝对的，因为MPP系统不共享资源，因此对它而言，资源比SMP要多，当需要处理的事务达到一定规模时，MPP的效率要比SMP好。这就是看通信时间占用计算时间的比例而定，如果通信时间比较多，那MPP系统就不占优势了，相反，如果通信时间比较少，那MPP系统可以充分发挥资源的优势，达到高效率。当前使用的OTLP程序中，用户访问一个中心数据库，如果采用SMP系统结构，它的效率要比采用MPP结构要快得多。

而MPP系统在决策支持和数据挖掘方面显示了优势，可以这样说，如果操作相互之间没有什么关系，处理单元之间需要进行的通信比较少，那采用MPP系统就要好，相反就不合适了。

通过上面两个图我们可以看到，对于SMP来说，制约它速度的一个关键因素就是那个共享的总线，因此对于DSS程序来说，只能选择MPP，而不能选择SMP，当大型程序的处理要求大于共享总线时，总线就没有能力进行处理了，这时SMP系统就不行了。当然了，两个结构互有优缺点，如果能够将两种结合起来取长补短，当然最好了。

2.3 PVFS(并行虚拟文件系统)

2.3.1 概述

目前，I/O和处理器性能之间的不平衡，导致I/O在许多应用中成为了瓶颈。 特别是当使用到大规模数据的时候。解决这种问题的流行方法就是并行文件系统，很多商用的并行机上已经采用了这种技术。

现在集群技术已经很成熟了[18]，越来越多的集群成为流行的并行计算平台，而对系统软件的需求也越来越多。PVFS文件系统努力就是为集群提供一个高性能可调整的并行文件系统，它是一个开放源代码的GNU软件，不许要特别的硬件和针对内核的修改。PVFS在软件包里提供了４个重要的功能。

", a consistent file name space across the machine ", transparent access for existing utilities ", physical distribution of data across multiple disks in multiple cluster nodes ", high-performance user space access for applications

PVFS项目的目的就是去探索并行I/O的设计、实现和潜在的用途。Clemson大学的并行体系结构研究室，在NASA的支持下，和CESDIS Beowulf计划紧密关联，在不断的完善PVFS系统。PVFS项目现在重点关注并行I/O在工作站，Pile-of-PCs和特殊的Beowulf集群上的应用。目前的特点有：

", Compatibility with existing binaries

", Ease of installation

", User-controlled striping of files across nodes

", Multiple interfaces, including a MPI-IO interface via ROMIO

2.3.2 PVFS的存取机制

上图显示了PVFS下节点是如何分配的。

节点分为三种，计算节点，管理节点和I/O节点，计算节点是用来运行应用程序的，管理节点管理元操作，I/O节点存储PVFS文件系统的数据。同时管理节点和I/O节点也可以作为计算节点。在小的集群上可以这么做，来保证资源的高校利用，而大的集群最好分开。 PVFS系统主要有４部分组成：

", Metadata server (mgr)

前两个试运行在各个节点上的后台精灵。第一个是元数据是文件的描述信息，象文件 名，文件位臵，目录层次，属主以及在多个节点上的如何分布等。通过一个精灵来自动的对文件的元数据进行操作，我们避免了通过网络存储的缺点（当面对多个存储的时候，它必须执行一个复杂的锁定计划）。第二个精灵是I/O服务器，或者叫iod，它们用来存储和取回存在本地硬盘上的文件数据。这些服务器实际上是在本地的节点文件系统上创建文件，并且使用常用的read()， write()，和mmap()来对文件进行操作。这就是说你可以利用本地的任意文件系统来存储文件，比如ext2，ext3，或者reiser。此外，可以利用硬raid或者软raid来创建额外的大文件系统实现容错的功能。

", I/O server (iod)

", PVFS native API (libpvfs) ", PVFS Linux kernel support

PVFS内部的API来对数据进行分散/聚集的操作，这对将数据在用户的缓存和PVFS服务器之间移动是必需的，并且这些操作对用户是透明的。

上图显示了PVFS系统中元数据操作和数据存储的数据流。对于元数据操作来说，应用程序通过库来和元数据服务器联系；对数据存储来说，元数据服务器被跳过了，I/O服务器直接存储。这是提高性能的关键。

最后，对Linux内核的支持保证了可以将PVFS文件系统挂在Linux节点上。这使得现有的程序不经过修改可以直接利用PVFS文件系统。这虽然对PVFS不是必需的，但是提供了与系统互相作用的便利。对内核的支持包括了一个可安装的模块和一个可选择的内核补丁（消除内存中的拷贝），以及一个精灵，pvfsd。它利用libpvfs里的函数来实现操作。

上图显示的是当Linux内核支持应用时，数据流如何通过内核。这项技术和CODA文件系统的机制类似。当操作通过操作系统的VFS层时，在这里pvfsd将它们储存在队列中，通过内核的设备文件来进行操作。然后与PVFS服务器联系和将数据通过内核返给应用程序。为了使所有的文件系统都可用，必须建立方便的接口。特别是运行并行程序的时候尤其重要。为了满足多个组的需要，PVFS可能用到３个接口，分别是：

", PVFS native API ", Linux kernel interface

", ROMIO MPI-IO interface

PVFS内带的API提供了一个和UNIX操作系统类似的接口来存取PVFS系统

下的文件，它也允许用户详细说明如何将文件在PVFS系统上分布。使用函数格式为pvfs_open(), pvfs_read(), pvfs_write()……

Linux操作系统内核接口，允许应用程序通过正常的渠道来利用PVFS文件系统，它允许用户利用日常的工具来进行数据的日常操作和在PVFS文件系统上的存取。使用函数格式为open(), read(), write() ……

ROMIO实现的MPI-IO是一个库，它允许MPI程序通过MPI-IO接口来存取PVFS文件。使用函数格式为MPI_File_open(), MPI_File_read(),

MPI_File_write() …… 并且ROMIO附带了两个优化：数据筛和two-phase collective I/O。

PVFS的两个最重要的目标是高性能和可扩展性，随着集群的规模越来越大，并性文件系统也越来越重要了，而PVFS的不断发展能够符合新集群的要求

2.3.3 PVFS的应用实例与性能

上图是PVFS系统自带的API的性能的测试，环境是一个２５６节点的集群双

CPU的PIII500，512M内存，硬盘是９G的SCSI硬盘，试验共使用了６０个节点，一部分做计算节点，其余的做I/O节点。它有两套网络互连，快速以太网和Myrinet。运用并行文件系统，得到了读写带宽为：Myrinet下700 Mbytes/sec；快速以太网下 225 Mbytes/sec。

首先看快速以太网的整体性能分析，很明显，没有设计好当超过２４个I/O服务器的情况，当超过２４个时，性能反而有下降，这可能是由于快速以太网上的TCP协议和它的消耗，不过，网络的结构和I/O服务器位臵的选择也有一些影响。

第二套测试是在Myrinet上的性能，总体性能和TCP协议在Myrinet上的性能符合，而且峰值接近硬盘的性能（硬盘比Myrinet速度慢）

2.4 OPS(Oracle Parallel Server)

2.4.1 体系结构

多实例：

分布式系统：

多实例作为分布式系统的一部分：

2.4.2 并行处理

执行语句请求对比：

三种共享构架：

对称处理与非对称处理：

应用扩展性：

2.4.3 分区技术

垂直分区：

水平分区：

说明：更详细资料请详阅Oracle OPS详细资料

3 Oracle体系结构

3.1 内存结构和进程结构

Oracle数据库的总体结构如下图：

3.2 Oracle实例

在一个服务器中，每一个运行的Oracle数据库都与一个数据库实例相联系，实例是我们访问数据库的手段。

实例在操作系统中用ORACLE_SID来标识，在Oracle中用参数

INSTANCE_NAME来标识，它们两个的值是相同的。数据库启动时，系统首先在服务器内存中分配系统全局区（SGA），构成了Oracle的内存结构，然后启动若干个常驻内存的操作系统进程，即组成了Oracle的 进程结构，内存区域和后台进程合称为一个Oracle实例。

数据库与实例之间是1对1/n的关系，在非并行的数据库系统中每个Oracle数

据库与一个实例相对应；在并行的数据库系统中，一个数据库会对应多个实例，同一时间用户只与一个 实例相联系，当某一个实例出现故障时，其他实例自动服务，保证数据库正常运行。在任何情况下，每个实例都只可以对应一个数据库。

3.3 Oracle 10g动态内存管理

内存是影响数据库性能的重要因素，Oracle8i使用静态内存管理，Oracle 10g使用动态内存管理。所谓静态内存管理，就是在数据库系统中，无论是否有用户连接，也无论并发用量大小，只要数据库服务在运行，就会分配固定大小的内存；动态内存管理允许在数据库服务运行时对内存的大小进行修改，读取大数据块时使用大内存，小数据块时使用小内存，读取标准内存块时使用标准内存设臵。 按照系统对内存使用方法的不同，Oracle数据库的内存可以分为以下几个部分： ?系统全局区：SGA（System Global Area） ?程序全局区：PGA（Programe Global Area） ?排序池：（Sort Area） ?大池：（Large Pool） ?Java池：（Java Pool）

3.3.1 系统全局区SGA（System Global Area）

SGA是一组为系统分配的共享的内存结构，可以包含一个数据库实例的数据或控制信息。如果多个用户连接到同一个数据库实例，在实例的SGA中，数据可以被多个用户共享。当数据库实例启动时，SGA的内存被自动分配；当数据库实例关闭时，SGA内存被回收。SGA是占用内存最大的一个区域，同时也是影响数据库性能的重要因素。

SGA的有关信息可以通过下面的语句查询，sga_max_size的大小是不可以动态调整的。

=====================================

SQL> show parameter sga

NAME TYPE VALUE ------------------------------------ ----------- -------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 164M sga_target big integer 0

SQL> alter system set sga_max_size=100m; alter system set sga_max_size=100m * ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified ======================================

系统全局区按作用不同可以分为： ?数据缓冲区 ?日志缓冲区 ?共享池

3.3.1.1 数据缓冲区（Database Buffer Cache）

如果每次执行一个操作时，Oracle都必须从磁盘读取所有数据块并在改变它之后又必须把每一块写入磁盘，显然效率会非常低。数据缓冲区存放需要经常访问的数据， 供所有用户使用。修改数据时，首先从数据文件中取出数据，存储在数据缓冲区中，修改/插入数据也存储在缓冲区中，commit或DBWR（下面有详细介绍）进程的其他条件引发时，数据被写入数据文件。

数据缓冲区的大小是可以动态调整的，但是不能超过sga_max_size的限制。 ====================================== SQL> show parameter db_cache_size

NAME TYPE VALUE ------------------------------------ ----------- ----------------- db_cache_size big integer 24M

SQL> alter system set db_cache_size=128m;

alter system set db_cache_size=128m *

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid ORA-00384: Insufficient memory to grow cache SQL> alter system set db_cache_size=20m; System altered.

SQL> show parameter db_cache_size;

NAME TYPE VALUE ------------------------------------ ----------- ----------------- db_cache_size big integer 20M

#此处我仅增加了1M都不行？

SQL> alter system set db_cache_size=25m; alter system set db_cache_size=25m *

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid ORA-00384: Insufficient memory to grow cache #修改显示格式，方便查看。 SQL> column name format a40 wrap SQL> column value format a20 wrap

#下面语句可以用来查看内存空间分配情况，注意SGA各区大小总和。 SQL> select name,value from v$parameter where name like '%size' and value <> '0';

#先将java_pool_size调小，然后再修改db_cache_size SQL> show parameter java_pool_size; NAME TYPE VALUE ------------------------------------ ----------- ----- java_pool_size big integer 48M SQL> alter system set java_pool_size=20m; System altered.

SQL> alter system set java_pool_size=30m; System altered.

#上面说明SGA中各区大小总和不能超过sga_max_size。 =====================================

数据缓冲区的大小对数据库的存区速度有直接影响，多用户时尤为明显。有些应

用对速度要求很高，一般要求数据缓冲区的命中率在90%以上。 下面给出一种计算数据缓冲区命中率的方法： ?使用数据字典v$sysstat

===================================== SQL> select name, value from v$sysstat 2 where name in('session logical reads', 3 'physical reads', 4 'physical reads direct', 5 'physical reads direct (lob)') NAME VALUE ------------------------------- ---------- session logical reads 895243 physical reads 14992 physical reads direct 34 physical reads direct (lob) 0

====================================== 命中率=1-(14992-34-0)/895243

可以让Oracle给出数据缓冲区大小的建议： ====================================== SQL> alter system set db_cache_advice=on;#打开该功能 System altered.

SQL> alter system set db_cache_advice=off;#关闭该功能 System altered.

====================================== 日志缓冲区（Log Buffer Cache）

日志缓冲区用来存储数据库的修改信息。该区对数据库性能的影响很小，有关日

志后面还会有详细的介绍。

查询日志缓冲区大小： SQL> show parameter log_buffer NAME TYPE VALUE ---------- ----------- ------- log_buffer integer 262144

3.3.1.2 共享池（Share Pool）

共享池是对SQL，PL/SQL程序进行语法分析，编译，执行的内存区域。 它包含三个部分：（都不可单独定义大小，必须通过share pool间接定义）。 ?库缓冲区（Library Cache）包含SQL，PL/SQL语句的分析码，执行计划。 ?数据字典缓冲区（Data Dictionary Cache）表，列定义，权限。 ?用户全局区（Usr Global Area）用户MTS会话信息。 共享池的大小可以动态修改：

====================================== SQL> show parameter shared_pool_size

NAME TYPE VALUE ------------------------------------ ----------- ------ __shared_pool_size big integer 80M shared_pool_size big integer 80M

SQL> alter system set shared_pool_size=78m System altered.

======================================

#上面的__shared_pool_size一行奇怪？(是Oracle 的隐含参数)

3.3.1.3 程序全局区PGA（Programe Global Area）

程序全局区是包含单个用户或服务器数据和控制信息的内存区域，它是在用户进程连接到Oracle并创建一个会话时由Oracle自动分配的，不可共享，主要用于用户在编程存储变量和数组。

如上图:

?Stack Space是用来存储用户会话变量和数组的存储区域； ?User Session Data是为用户会话使用的附加存储区。 |--Session Information |--Sort Area

|--Cursor Information

注意Session information（用户会话信息）在独占服务器中与在共享服务器中所处的内存区域是不同的。

3.3.1.4 排序区，大池，Java池

排序区（Sort Area）为有排序要求的SQL语句提供内存空间。系统使用专用的内存区域进行数据排序，这部分空间就是排序区。在Oracle数据库中，用户数据的排序可使用两个区域，一个是内存排序区，一个是磁盘临时段，系统优先使用内存排序区进行排序。如果内存不够，Orcle自动使用磁盘临时表空间进行排序。为提高数据排序的速度，建议尽量使用内存排序区，而不要使用临时段。 参数sort_area_size用来设臵排序区大小。（好象不能动态修改？） 大池（Large Pool）用于数据库备份工具--恢复管理器（RMAN：Recovery Manager）。

Large Pool的大小由large_pool_size确定，可用下面语句查询和修改： ========================================= SQL> show parameter large_pool_size NAME TYPE VALUE

----------------- ----------- ------- large_pool_size big integer 8M SQL> alter system set large_pool_size=7m; System altered.

=========================================

Java池主要用于Java语言开发，一般来说不低于20M。其大小由java_pool_size来确定，可以动态调整。

3.3.1.5 Oracle自动共享内存管理（Automatic Shared

Memory(SGA) Management）

在Oracle 8i/9i中数据库管理员必须手动调整SGA各区的各个参数取值，每个区要根据

负荷轻重分别设臵，如果设臵不当，比如当某个区负荷增大时，没有调整该区内存大小，则

可能出现ORA-4031：unable to allocate ...bytes of shared memory错误。 在Oracle 10g中，将参数STATISTICS_LEVEL设臵为TYPICAL／ALL，使用SGA_TARGET指

定SGA区总大小，数据库会根据需要在各个组件之间自动分配内存大小。 下面是系统自动调整的区域：

?固定SGA区及其他?共享池?数据缓冲区? Java池?大池。

注意：如果不设臵SGA_TARGET，则自动共享内存管理功能被禁止。 ========================================== SQL> show parameter statistics_level NAME TYPE VALUE --------------------- ----------- ------------ statistics_level string TYPICAL

SQL> alter system set statistics_level=all; System altered.

#typical和all有什么区别？

SQL> alter system set statistics_level=typical; System altered.

SQL> show parameter sga_target NAME TYPE VALUE ------------- ----------- ---------- sga_target big integer 0

SQL> alter system set sga_target=170m; alter system set sga_target=170m *

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size SQL> alter system set sga_target=20m; System altered.

#不过后来又发现sga_target的值变成了140M? 下面是语句执行情况。 SQL> show parameter sga_target NAME TYPE VALUE ------------- ----------- ------- sga_target big integer 140M

SQL> alter system set sga_target=0; System altered.

SQL> show parameter sga_target

NAME TYPE VALUE ------------------------------------ ----------- ------ sga_target big integer 0 #改为20M

SQL> alter system set sga_target=20m; System altered. #显示的是140M

SQL> show parameter sga_target

NAME TYPE VALUE ------------------------------------ ----------- ------ sga_target big integer 140M #不可缩减？

SQL> alter system set sga_target=130m; alter system set sga_target=130m *

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid ORA-00827: could not shrink sga_target to specified value

#不可增加

SQL> alter system set sga_target=141m; alter system set sga_target=141m *

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size

3.3.2 Oracle实例的进程结构（Process Structure）

Oracle包含三类进程： ?用户进程（User Process） ?服务器进程（Server Process） ?后台进程（Background Process）

3.3.2.1 用户进程和服务器进程

当数据库用户请求连接到Oracle的服务时启动用户进程（比如启动SQLPlus时）。 ?用户进程首先必须建立一个连接。

?用户不能直接与Oracle服务器，必须通过服务器进程交互。

?服务器进程是用户进程与服务器交互的桥梁，它可以与Oracle Server直接交互。 ?服务器进程可以有共享和独占两种形式。

3.3.2.2 后台进程（Backgroung Process）

数据库的物理结构与内存结构之间的交互要通过后台进程来完成。数据库的后台进程包

含两类，一类是必须的，一类是可选的： ?Mandatory background processes |--DBWn（Database Writer）：数据写入 |--PMON（Process Moniter）：进程监控 |--LGWR（Log Writer）：日志写入 |--SMON（System Moniter）：系统监控 |--RECO（Recovery）：恢复 |--CKPT（Chekpoint）：检查点 ?Optional background processes |--ARCn（Archiver）：归档 |--LCKn（Lock）：锁 |--Dnnn（Dispatcher）：调度 |--......

可以用下面的语句查看正在运行的后台进程：

========================================= SQL> select * from v$bgprocess where paddr<>'00';

PADDR PSERIAL# NAME DESCRIPTION -------- ---------- ----- -------------------------------

6B0ED064 1 PMON process cleanup 6B0ED4E4 1 MMAN Memory Manager 6B0ED964 1 DBW0 db writer process 0 6B0EDDE4 1 LGWR Redo etc. 6B0EE264 1 CKPT checkpoint

6B0EE6E4 1 SMON System Monitor Process

6B0EEB64 1 RECO distributed recovery 6B0EEFE4 1 CJQ0 Job Queue Coordinator 6B0F01E4 1 QMNC AQ Coordinator

6B0F0664 1 MMON Manageability Monitor Process 6B0F0AE4 1 MMNL Manageability Monitor Process 2 ========================================

3.3.2.2.1 DBWR（Database Writer，数据写入进程）

将数据缓冲区的数据写入数据文件，是负责数据缓冲区管理的一个后台进程。当数据缓冲区中的一数据被修改后，就标记为dirty，DBWR进程将数据缓冲区中“脏”数据写入数据文件，保持数据缓冲区的”干净“。由于数据缓冲区的数据被用户修改并占用，空闲数据缓冲区会不断减少，当用户进程要从磁盘读取数据块到数据缓冲区却无法找到足够的空闲数据缓冲区时，DBWR将数据缓冲区内容写入磁盘，使用户进程总可以得到足够的空闲数据缓冲区。 DBWR的作用：

?管理数据缓冲区，以便用户进程总能够找到足够的空闲缓冲区。 ?将所有修改后的缓冲区数据写入数据文件。

?使用LRU（最近最少使用）算法保持缓冲区数据是最近经常使用的。 ?通过延迟写来优化磁盘I/0读写。

LGWR（Log Writer，日志写入进程）

将日志数据从日志缓冲区写入磁盘日志文件组。数据库在运行时，如果对数据库进行修改则产生日志信息，日志信息首先产生于日志缓冲区。当日志达到一定数量时，由LGWR将将日志数据写入到日志文件组，再经过日志切换，由归档进程（ARCH）将日志数据写入归档进程（前提是数据库运行在归档模式下）。数据库遵循写日志优先原则，即在写数据之前先写日志。

3.3.2.2.2 ARCH（Archiver，归档进程）

Oracle数据库有两种运行模式，归档（ARCHIVELOG），非归（NOARCHIVELOG）模式。

以非归档模式运行时日志在切换时被直接覆盖，不产生归档日志，这是数据库默认的运行模式。数据库运行在归档模式时，在日志切换之前，由ARCH进程将日志信息写入磁盘，也就是自动备份在线日志。

Oracle数据库的Redo文件数量是有限的，所以Oracle以循环的方式向它们中写入。它顺序写满每一个Redo文件，当达到最后一个时，再循环回去开始填写第一个Redo文件。如果为了能恢复数据库而想保存日志文件，那么在它们被重新使用之前需要对其进行备份，归档进程管理此工作。

3.3.2.2.3 CKPT（Check Point，检查点进程）

运行CKPT时，系统对全部数据文件及控制文件文件头的同步信号进行修改，以保证数据

库的同步。检查点出现在以下情况： |--在每个日志切换时产生。

|--上一个检验点之后又经过了指定时间。

|--从上一个检验点之后，当预定义数量的日志块被写入磁盘之后。 |--数据库关闭。 |--DBA强制产生。

|--当表空间设臵为OFFLINE时。

3.3.2.2.4 SMON（System Moniter，系统监控进程）

SMON在实例启动时执行实例恢复，并负责清理不再使用的临时段。

3.3.2.2.5 PMON（Process Monitor，进程监控）

PMON在用户进程出现故障时进行恢复，负责清理内存区域和释放该进程所使用的资源。

3.3.2.2.6 RECO（Recovery，恢复进程）

RECO用于分布式数据库维持在分布式环境中的数据的一致性。

3.3.2.2.7 LCKn（Lock，锁进程）

在并行服务器中用于多个实例间的封锁。

3.3.2.2.8 Dnnn（Dispatcher，调度进程）

Dnnn存在于多线程服务器体系结构中，负责将用户进程连接到服务器进程，再把结果返回给用户进程。

4 存储管理

4.1 ASM（自动存储管理）

Oracle 10g 的 ASM (自动存储管理) 真是一把双刃剑，对于存储的管理给 DBA 带来了不少便利，可也存在无穷多的问题。 ASM_POWER_LIMIT 参数

这个参数 ASM_POWER_LIMIT 参数控制 ASM 后台进程 ARBx 的数量ARBx 进程用来进行 ASM 磁盘数据重新分布打散。ASM_POWER_LIMIT 取值 从 0 到 11(据说从 10gR2 开始可以设臵为 0 ). 当新添加磁盘或者删除磁盘后，ASM 会启动 ARBx 进行 IO 分散操作，这是个非常消耗资源的动作，所以一定要选择系统空闲的时候进行。 关于 ASM 的条带与分配单元

ASM 默认的 Stripe Size 为 128K。 (一般操作系统的一个 IO 最大是 1M，对于 Block Size 为 8K 的系统，一般来说，db_file_multiblock_read_count 设臵为 16 即可)。分配单元（ Allocation Unit ） 是 1M，这个 AU 对应 extent 。在一些 DW 环境，随着数据量增大，AU 会非常的多，会产生性能影响。Stripe Size 和 AU 是可以通过 ASM 实例上的两个隐含参数调整的： _asm_ausize

_asm_stripesize(注意最大1M，否则会有负面影响)

4.2 DMT,LMT,ASSM

为了保持其最强大和最灵活数据库的地位，Oracle在最近发布的几个版本里一直都在创建新的机制来对表格和索引的存储进行简化和分块。从Oracle8i开始，Oracle开始在tablespace内部将对象管理进行自动化。第一个增强的地方原来叫做本地管理tablespace（或者简写作LMT）。在LMT里，Oracle将tablespace里的信息从数据字典的表格空间里移出去，而直接将其保存到tablespace自身里。这在Oracle9i里已经成为了一个事实的标准，因为它减轻了数据字典的负担。 表格空间的第二个主要增强的是自动分段空间管理（ASSM），它首次出现在Oracle9i里。有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质。

管理空间的两种方法 ，让我们从比较这两种空间管理开始：

本地管理tablespace（LMT）——LMT是通过把EXTENT MANAGEMENT LOCAL子句添加到tablespace的定义句法而实现的。和原来由字典管理的

tablespace（DMT）不同，LMT会将扩展管理自动化，并保持Oracle DBA不会被用来指定管理扩展大小的NEXT存储参数。这个原则唯一的例外是在NEXT和MINEXTENTS一起用在表格创建的时候。

自动区段空间管理（ASSM）——ASSM的tablespace是通过将SEGMENT SPACE MANAGEMENT AUTO子句添加到tablespace的定义句法里而实现的。通过使用位图freelist取代传统单向的链接列表freelist，ASSM的tablespace会将freelist的管理自动化，并取消为独立的表格和索引指定PCTUSED、FREELISTS和FREELIST GROUPS存储参数的能力。

Oracle值得赞扬的地方是，这两个空间管理的方法都是可选的特性，而且Oracle的老手可能仍会使用更加详细的方法，只要他们愿意的话。要注意，位图区段管理在Oracle9i里是可选的，而且只能在tablespace这一层实现，这一点是十分重要的。原有的系统还能够继续使用传统方法来管理freelist。

位图freelist挑战传统的空间管理

在我讨论位图freelist和传统的空间管理之前，让我们看看位图freelist是怎么样实现的。我会从使用区段空间管理自动参数创建tablespace开始： create tablespace asm_lmt_ts datafile 'c:\\oracle\\oradata\\diogenes\\asm_lmt.dbf' Size 5m EXTENT MANAGEMENT LOCAL -- Turn on LMT SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM; 一旦你定义好了tablespace，那么表格和索引就能够使用各种方法很容易地被移动到新的tablespace里。下面就是我进行创建的代码： create table new_cust tablespace assm_lmt_ts as select * from customer; alter index cust_name_idx rebuild tablespace assm_lmt_ts; 要注意，当表格或者索引被分配到这个tablespace以后，用于独立对象的PCTUSED的值会被忽略，而Oracle9i会使用位图数组来自动地管理tablespace里表格和索引的freelist。对于在LMT的tablespace内部创建的表格和索引而言，这个NEXT扩展子句是过时的，因为由本地管理的tablespace会管理它们。但是，INITIAL参数仍然是需要的，因为Oracle不可能提前知道初始表格加载的大小。对于ASSM而言，INITIAL最小的值是三个区块。

关于一个万能的方法对于Oracle来说是否是最好的方法还有一些争论。在大型数据库里，单独的对象设臵会带来性能和存储上的巨大不同。

PCTFREE的问题

PCTFREE参数是用来指定数据块剩余空间大小的，这一空间为将来数据行的扩展而保留。如果PCTFREE设臵得不得当，SQL的更新声明就可能导致大量的数据行碎片和断链。

数据行在刚保存的时候还很小，而在后来进行了扩展，在这种情况下，PCTFREE的设臵就显得尤其重要了。在这样的系统里，通常会把PCTFREE设臵成等于95，这就告诉Oracle要为数据行今后的扩展保留95％的数据区段空间。

PCTUSED的问题

对PCTUSED不正确的设臵（例如设得太小了）会导致SQL插入声明性能的急剧下降。如果数据区块剩余空间不是很多，那么在SQL插入操作的过程中就会产生过量的I/O，这是因为被重新使用的Oracle数据区块会被迅速地填满。从极端的角度来看，没有正确地设臵PCTUSED会导致数据区块的剩余空间要比表格数据行的平均长度小。在这样的情况下，Oracle会五次尝试从freelist链取回区块。在五次尝试以后，Oracle会提升表格的水位，并为插入操作腾出五个新的数据块。

有了Oracle9i的ASSM，PCTUSED就不再控制表格数据块的重新链接阙值了，但是你必须依靠Oracle的判断来确定区块在什么时候会有足够的剩余空间放臵到freelist里。

尽管有了本地管理的tablespace和ASSM之后Oracle9i会忽略PCTUSED、FREELISTS和FREELIST GROUPS等参数，但是当它们用于表格定义的时候，Oracle还是不会给出错误信息： SQL> create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 pctfree 20 pctused 30 7 storage 8 ( freelists 23 next 5m ) ; Table created.

如果你不记得带有ASSM的本地管理tablespace会略掉任何为PCTUSED、NEXT和FREELISTS所指定的值的话，这将是一个十分严重的问题。 使用ASSM的一个巨大优势是，位图freelist肯定能够减轻缓冲区忙等待（buffer busy wait）的负担，这个问题在Oracle9i以前的版本里曾是一个严重的问题。现在让我们来仔细看看这个特性。

在没有多个freelist的时候，每个Oracle表格和索引在表格的头部都曾有一个数据块，用来管理对象所使用的剩余区块，并为任何SQL插入声明所创建的新数据行提供数据块。当数据缓冲内的数据块由于被另一个DML事务处理锁定而无法使用的时候，缓冲区忙等待就会发生。当你需要将多个任务插入到同一个表格里的时候，这些任务就被强制等待，而同时Oracle会在同时分派剩余的区块，一次一个。

有了ASSM之后，Oracle宣称显著地提高了DML并发操作的性能，因为（同一个）位图的不同部分可以被同时使用，这样就消除了寻找剩余空间的串行化。根据Oracle的测试结果，使用位图freelist会消除所有分段头部（对资源）的争夺，还能获得超快的并发插入操作（图A）。

图A Oracle公司对使用位图freelist进行SQL插入操作的测试结果 ASSM的局限性

尽管ASSM显示出了令人激动的特性并能够简化Oracle DBA的工作，但是Oracle9i的位图分段管理还是有一些局限性的：

一旦DBA被分配之后，它就无法控制tablespace内部的独立表格和索引的存储行为。

大型对象不能够使用ASSM，而且必须为包含有LOB数据类型的表格创建分离的tablespace。

你不能够使用ASSM创建临时的tablespace。这是由排序时临时分段的短暂特性所决定的。

只有本地管理的tablespace才能够使用位图分段管理。

使用超高容量的DML（例如INSERT、UPDATE和DELETE等）的时候可能会出现性能上的问题。

4.3 自动管理问题

在实际中碰到的问题：数据库临时表空间不释放的问题：Oracle在使用临时表空间的时候，空闲空间优先分配原则，如果临时表空间有free空间，oracle会优先使用这些未分配的空间，等到空间不够的时候，才会去复用以前已经释放并且标志为unused的段，这种方式也是处于一种综合的考虑，直接使用未分配的空间肯定比去查找标志为unused的段要快，当空间不够的时候，再回去复用标志为unused的extend

5 时间空间转换平衡

5.1 数据压缩

考虑硬件平衡性的基础上进行数据压缩 适用环境：（CPU消耗较低，数据量较大，IO压力较大情况下）

原理：使用较高的CPU消耗换来IO的增加（压缩后CPU使用率上涨不是很大）

5.2 索引机制分析

5.2.1 Index full scan VS Index fast full scan

Fast Full Index Scans :

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

5.2.2 Index range scan VS Index skip scan

Oracle9i有的一个新的执行特性索引跳跃式扫描(index skip scan)，它适用于使用连接索引和访问多值索引的Oracle查询。让我们仔细看一下下面的例子。请注意到以下代码包含着连接索引： Create index sex_emp_id On emp (sex, emp_id); 在Oracle9i版本之前，当SQL查询中包含性别和emp_id时，或者查询指定性别行的时候才可以使用这一索引。下面的查询不能够使用连接索引： Select emp_id From emp Where emp_id = 123; Oracle9i的索引跳跃式扫描执行规则允许使用连接索引，即使SQL查询中不指定性别。这一特性使得无需在emp_id行中提供第二个索引。Oracle承认索引跳跃式扫描没有直接索引查询速度快，但可以这样说，相比于整个表扫描(table scan)，索引跳跃式扫描的速度要快得多。

当Oracle没有指明索引跳跃式扫描的内部内容时，我们可以从它的执行规则中判断出，Oracle在内部上生成了多个查询，这样就满足带有多个子查询的查询。

SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5) 0 SORT (AGGREGATE) 1 INDEX (SKIP SCAN) OF 'SEX_EMP_ID' (NON-UNIQUE) 在内部里，Oracle生成了两个查询，并连接结果的ROWID表。 selectemp_name from emp_where sex = 'F' and emp_id = 123 UNION select emp_name from emp_where sex = 'M' and emp_id = 123; 使用索引跳跃式扫描的内涵就变得很清晰。

对于高顺序键(high order key)中的独特值数目，Oracle的索引跳跃式扫描性能将会降低。如果主列有50个值，Oracle要发出50条查询才能找回结果。 注释：索引跳跃式扫描只适用于硬盘空间和存储空间相当紧缺的情况，如果索引的中的字段有单独用作查询条件的情况，存储空间又不是问题的情况下，对这些字段简历单独的索引

6 设计注意问题

列的比较简单，如果有什么问题，请找本人讨论。

6.1 字符集问题

在Oracle实例安装时应该统一字符集格式，字符集不要变动，这样会导致很多不可预知的问题。

6.2 代理主键

主要用在星型模型构架中，在事实表中使用代理主键，以减小事实表数据量，提高处理速度

注意：在ETL设计时要注意代理主键的生成及编码规则问题

6.3 程序监控

在可能会出错的地方，有优化空间的语句附近加上记录日志的语句，为以后错误调试和程序优化提供依据

6.4 临时表空间和回滚表空间释放

Oracle自动管理的临时表空间和回滚表空间在分配和释放的时候可能出于性能的考虑导致空间回收不是很好用，所以在设计时考虑设计两个临时表空间和两个回滚表空间，在系统回滚表空间或临时表空间出现空间问题的时候，可以切换临时表空间或者回滚表空间到备用的表空间，尽量减少对业务方面的影响。

6.5 并行操作

对于并发用户量较小的情况，可以设臵索引的并行查询，提高数据查询速度，对于并发用户数较多的情况，建议关闭在索引和表上的并行选项

6.6 关于APPEND提示

在向一个表中插入数据时，可以指定APPEND选项以加快插入速度

原理：加APPEND提示时，Oracle在插入数据时会直接在高水位标上插入数据，加快了插入数据的速度，但这个提示是对表加独占锁的，如果事务比较多，可能导致效率下降，所以要根据系统的实际情况来确定要不要加这个提示；

高水位标：Oracle在对存储管理时，会分配一批存储块，但是这些块也不是完全占满的；所以，在分配和没有分配的块中间会有一个界线，称为高水位标

6.7 数据库统计信息收集

对于采用CBO优化方式的Oracle数据库中，统计信息是很重要的，准确及时的统计信息能使Oracle产生更准确的执行计划，对优化能提供更可靠的依据，在实时交易系统中，这种方式不合适，因为收集统计信息要消耗系统资源和时间，这种优化模式更适合应用于数据仓库项目中。

7 SQL语句优化

7.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优化器,而直接采用基于规则或者基于成本的优化器.

7.2 访问Table的方式

ORACLE 采用两种访问表中记录的方式： a.全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

b.通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位臵信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位臵(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

7.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语句必须完全相同(包括空格,换行等).

共享的语句必须满足三个条件: A.字符级的比较:

当前被执行的语句和共享池中的语句必须完全相同. 例如:

SELECT * FROM EMP; 和下列每一个都不同

SELECT * from EMP; Select * From Emp;

SELECT * FROM EMP; B.两个语句所指的对象必须完全相同: 例如: 用户 对象名 如何访问 Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym Work_city public synonym Plant_detail table owner

考虑一下下列SQL语句能否在这两个用户之间共享.

SQL select max(sal_cap) from sal_limit; 能否共享 不能 select count(*0 from work_city where sdesc 能 like 'NEW%'; select a.sdesc,b.location from work_city a , 不能 plant_detail b where a.city_id = b.city_id 原因 每个用户都有一个private synonym - sal_limit , 它们是不同的对象 两个用户访问相同的对象public synonym - work_city 用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同. C.两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值) a.

select pin , name from people where pin = :blk1.pin; select pin , name from people where pin = :blk1.pin; b.

select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind;

7.4 选择最有效率的表名顺序

(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如：

表 TAB1 16,384 条记录 表 TAB2 1 条记录

选择TAB2作为基础表 (最好的方法)

select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间26.09秒

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT *

FROM LOCATION L ,

CATEGORY C,

EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将比下列SQL更有效率 SELECT * FROM EMP E ,

LOCATION L ,

CATEGORY C

WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

7.5 WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接

必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 例如:

(低效,执行时间156.3秒) SELECT … FROM EMP E

WHERE SAL > 50000

AND JOB = ?MANAGER?

AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效,执行时间10.6秒) SELECT … FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000

AND JOB = ?MANAGER?;

7.6 SELECT子句中避免使用 ‘ * ‘

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

7.7 减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算

索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量. 例如,

以下有三种方法可以检索出雇员号等于0342或0291的职员. 方法1 (最低效)

SELECT EMP_NAME , SALARY , GRADE FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME , SALARY , GRADE FROM EMP

WHERE EMP_NO = 291; 方法2 (次低效) DECLARE

CURSOR C1 (E_NO NUMBER) IS

SELECT EMP_NAME,SALARY,GRADE FROM EMP

WHERE EMP_NO = E_NO; BEGIN

OPEN C1(342);

FETCH C1 INTO …,..,.. ; OPEN C1(291); FETCH C1 INTO …,..,.. ; CLOSE C1; END; 方法3 (高效)

SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B

WHERE A.EMP_NO = 342 AND B.EMP_NO = 291; select emp_name,salary,grade from emp

where emp_no in(342,291) 注意:

在SQL*Plus , SQL*Forms和Pro*C中重新设臵ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200.

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

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

SELECT COUNT(*)，SUM(SAL) FROM EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE ?SMITH%?; SELECT COUNT(*)，SUM(SAL) FROM EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE ?SMITH%?;

你可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO,0020,?X?,NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,?X?,NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ?SMITH%?;

类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.

7.9 整合简单,无关联的数据库访问

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

SELECT NAME FROM EMP

WHERE EMP_NO = 1234;

SELECT NAME FROM DPT

WHERE DPT_NO = 10 ;

SELECT NAME FROM CAT

WHERE CAT_TYPE = ?RD?;

上面的3个查询可以被合并成一个: SELECT E.NAME , D.NAME , C.NAME FROM CAT C , DPT D , EMP E,DUAL X

WHERE NVL(?X?,X.DUMMY) = NVL(?X?,E.ROWID(+)) AND NVL(?X?,X.DUMMY) = NVL(?X?,D.ROWID(+)) AND NVL(?X?,X.DUMMY) = NVL(?X?,C.ROWID(+)) AND E.EMP_NO(+) = 1234 AND D.DEPT_NO(+) = 10 AND C.CAT_TYPE(+) = ?RD?;

(译者按: 虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以读者 还是要权衡之间的利弊)

7.10 删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID) DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

7.11 用TRUNCATE替代DELETE

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

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

(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

7.12 尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源:

a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间

d. ORACLE为管理上述3种资源中的内部花费

(译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)

7.13 计算记录条数

和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,

对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

(译者按: 在CSDN论坛中,曾经对此有过相当热烈的讨论, 作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)

7.14 用Where子句替换HAVING子句

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

例如:

低效:

SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION

HAVING REGION REGION != ?SYDNEY? AND REGION != ?PERTH? 高效

SELECT REGION，AVG(LOG_SIZE) FROM LOCATION

WHERE REGION REGION != ?SYDNEY? AND REGION != ?PERTH? GROUP BY REGION

(译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

7.15 减少对表的查询

在含有子查询的SQL语句中,要特别注意减少对表的查询. 例如: 低效

SELECT TAB_NAME FROM TABLES

WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER

FROM TAB_COLUMNS WHERE VERSION = 604) 高效

SELECT TAB_NAME FROM TABLES

WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER)

FROM TAB_COLUMNS WHERE VERSION = 604) Update 多个Column 例子: 低效:

UPDATE EMP

SET EMP_CAT = (SELECT MAX(CATEGORY) EMP_CATEGORIES),

SAL_RANGE = (SELECT MAX(SAL_RANGE) EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

FROM FROM

高效:

UPDATE EMP

SET (EMP_CAT, SAL_RANGE)

= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

7.16 通过内部函数提高SQL效率

SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*) FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H WHERE H.EMPNO = E.EMPNO AND H.HIST_TYPE = T.HIST_TYPE

GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC; 通过调用下面的函数可以提高效率.

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 AS

TDESC VARCHAR2(30); CURSOR C1 IS

SELECT TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = TYP; BEGIN

OPEN C1;

FETCH C1 INTO TDESC; CLOSE C1;

RETURN (NVL(TDESC,???)); END;

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 AS

ENAME VARCHAR2(30); CURSOR C1 IS SELECT ENAME FROM EMP

WHERE EMPNO=EMP; BEGIN

OPEN C1;

FETCH C1 INTO ENAME; CLOSE C1;

RETURN (NVL(ENAME,???)); END;

SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),

H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*) FROM EMP_HISTORY H

GROUP BY H.EMPNO , H.HIST_TYPE;

(译者按: 经常在论坛中看到如 ?能不能用一个SQL写出….? 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的)

7.17 使用表的别名(Alias)

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. (译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

7.18 用EXISTS替代IN

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

SELECT *

FROM EMP (基础表) WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO FROM DEPT

WHERE LOC = ?MELB?) 高效: SELECT *

FROM EMP (基础表) WHERE EMPNO > 0

AND EXISTS (SELECT ?X? FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ?MELB?)

(译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)

7.19 用NOT EXISTS替代NOT IN

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

SELECT … FROM EMP

WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT

WHERE DEPT_CAT=?A?); 为了提高效率.改写为: (方法一: 高效) SELECT ….

FROM EMP A,DEPT B

WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ?A? (方法二: 最高效) SELECT …. FROM EMP E

WHERE NOT EXISTS (SELECT ?X? FROM DEPT D

WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ?A?);

7.20 用表连接替换EXISTS

通常来说 , 采用表连接的方式比EXISTS更有效率 SELECT ENAME FROM EMP E

WHERE EXISTS (SELECT ?X? FROM DEPT

WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ?A?); (更高效)

SELECT ENAME

FROM DEPT D,EMP E

WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ?A? ;

(译者按: 在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)

7.21 用EXISTS替换DISTINCT

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

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO 高效:

SELECT DEPT_NO,DEPT_NAME FROM DEPT D

WHERE EXISTS ( SELECT ?X? FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

7.22 识别“低效执行”的SQL语句

用下列SQL工具找出低效SQL:

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;

(译者按: 虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)

7.23 使用TKPROF 工具来查询SQL性能状态

SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统. 设臵SQL TRACE在会话级别: 有效

ALTER SESSION SET SQL_TRACE TRUE

设臵SQL TRACE 在整个数据库有效仿, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录

(译者按: 这一节中,作者并没有提到TKPROF的用法, 对SQL TRACE的用法也不够准确, 设臵SQL TRACE首先要在init.ora中设定TIMED_STATISTICS, 这样才能得到那些重要的时间状态. 生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数. 大家可以参考ORACLE手册来了解具体的配臵. )

本文来源：https://www.bwwdw.com/article/qkcr.html