oracle开发分享Word文档格式.docx

1、2.7. 分布式查询 242.8. 用 exist 代替 distinct 252.9. 避免在索引列上使用计算 262.10. 用=替代 262.11. 用TRUNCATE替代DELETE删除全表 262.12. 大事务多使用COMMIT 262.13. 避免在索引列上使用IS NULL和IS NOT NULL 262.14. 用Where子句替换HAVING子句： 272.15. 使用WITH AS 语句减少访问基表的次数 272.16. hint的使用 282.17. 基于规则优化器的默认选择 323. SQL 调试 323.1. 通过V$SQLAREA查找有问题的查询 333.2. 实

2、时查询最耗CPU资源的SQL语句 333.3. 怎样看懂Oracle的执行计划 333.4. Oracle优化器介绍 334. SQL性能监控 344.1. 常用视图介绍 344.1.1. v$sqlarea 344.1.2. v$sql 364.1.3. v$sqltext 364.1.4. v$session 364.1.5. v$Lock 414.1.6. v$locked_object 414.2. 锁查询 424.2.1. Oracle的TM锁类型 424.2.2. 锁查询 424.2.3. 解锁 434.3. 查看表空间大小 434.4. 查看表大小 43order by 3 de

3、sc 435 oracle SQL开发工具介绍 43PL/SQL developer 431 Oracle 概念1.1. Oracle 服务器概述数据库是一个数据的集合，不仅是指物理上的数据，也指物理、存储及进程对象的一个集合。下面主要从应用结构，进程结构，内存结构，逻辑结构等几个方面对其进行描述：1.1.1. 应用体系结构 在一个 Oracle 数据库系统中，数据库应用程序与数据库被分为两部分：前端（front-end）或称为客户端（client）；后台（back-end）或称为服务端（server）这就是客户端/服务器体系结构（client/server architecture）名字的由

4、来。客户端运行数据库应用程序，此程序能够从数据库获取数据，并通过键盘，鼠标，显示器之类的设备和用户交互。服务端运行 Oracle 数据库程序，这些程序能实现多用户并发、共享地访问数据库。目前的应用结构有：客户端/服务器体系结构 和多层体系结构。图1-1 客户端/服务器体系结构及分布式处理图1-2 多层体系结构示例1.1.2. 进程体系结构 1.1.2.1. 进程的种类 执行应用程序或 Oracle 工具代码的用户进程（user process） 执行 Oracle 数据库服务器代码的 Oracle 进程（Oracle process）。其中包括服务进程（server process）和后台进程

5、（background process）图1-3 显示了一个运行在专用服务器模式下的 Oracle 实例。每个连接到系统的用户拥有一个独立的用户进程，同时系统中还存在多个运行 Oracle 模块的后台进程图1-3 中间为 SGA。其上为用户进程，及表示各进程与 SGA 交换数据的双向箭头。其下为 Oracle 进程（后台进程），例如 RECO，PMON，SMON，DBWn，LGWR 和 ARC0。在各 Oracle 进程与 SGA 间也有表示数据交换的双向箭头。1.1.2.2. 连接与会话连接（connection）与会话（session）这两个概念均与用户进程（user process）紧密

6、相关，但二者又具有不同的含义。连接是用户进程和 Oracle 实例间的通信通道（communication pathway）。这个通信通道是通过进程间的通信机制（interprocess communication mechanisms）或网络软件（network software）建立的。会话是用户通过用户进程与 Oracle 实例建立的连接此处连接与上文中的连接含义不同 ，主要指用户和数据库间的联系。例如，当用户启动 SQL*Plus 时必须提供有效的用户名和密码，之后 Oracle 为此用户建立一个会话。从用户开始连接到用户断开连接（或退出数据库应用程序）期间，会话一直持续。1.1.2.

7、3. 服务进程 Oracle 需要创建服务进程（server process）来处理连接到实例的用户进程（user process）提交的请求。当应用程序与 Oracle 服务器运行在同一计算机时，某些用户进程可以和其服务进程合并为一个进程，以便减小系统开销。但是，当应用程序与 Oracle 服务器运行在不同计算机时，用户进程必须通过一个服务进程与 Oracle 通信。 为用户应用程序创建的服务进程（或用户/服务进程合并后的服务进程部分）可以完成以下工作： 解析、运行应用程序提交的 SQL 语句 如果数据在 SGA 中不存在，则将所需的数据块从磁盘上的数据文件读入 SGA 的数据缓存区（sha

8、red database buffer） 以应用程序能理解的形式返回 SQL 语句的执行结果1.1.2.4. 后台进程一个 Oracle 实例中可以包含多种后台进程，这些进程不一定全部出现在实例中。系统中运行的后台进程数量众多，用户可以通过 V$BGPROCESS 视图查询关于后台进程的信息。Oracle 实例中可能运行的后台进程有： 数据写入进程（DBWn）: 数据写入进程（database writer process，DBWn）的功能是将数据缓冲区的内容写入数据文件。DBWn 进程负责将数据缓存区（database buffer cache）内修改过的缓冲区（即 dirty buffer

9、）写入磁盘. 日志写入进程（LGWR）: 日志写入进程（log writer process，LGWR）负责对重做日志进行管理将重做日志缓冲区（redo log buffer）内的数据写入磁盘上的重做日志文件中。LGWR 进程将上次写入之后进入缓冲区的所有重做条目（redo entry）写入磁盘中。 检查点进程（CKPT）: 当一个检查点（checkpoint）事件发生时，Oracle 需要更新所有数据文件的文件头来记录检查点事件的详细信息。这个工作是由 CKPT 进程完成的。但是将数据块写入数据文件的不是 CKPT 进程，而是 DBWn 进程。 系统监控进程（SMON）: 系统监控进程（sy

10、stem monitor process，SMON）将负责进行恢复（recovery）工作。此外，SMON 还负责清除系统中不再使用的临时段（temporary segment），以及为数据字典管理的表空间（dictionary managed tablespace）合并相邻的可用数据扩展（extent） 进程监控进程（PMON）: 进程监控进程（process monitor，PMON）将对其进行恢复。PMON 进程将清除相关的数据缓存区（database buffer cache）并释放被此用户进程使用的资源。例如，PMON 进程将重置活动事务表（active transaction ta

11、ble），释放锁，并从活动进程列表（list of active process）中删除出错进程的 ID。 恢复进程（RECO）: 恢复进程（recoverer process，RECO）是在分布式数据库环境（distributed database configuration）中自动地解决分布式事务（distributed transaction）错误的后台进程。一个节点（node）上的 RECO 进程能够连接到出现了不可信的分布式事务（in-doubt distributed transaction）的数据库。当 RECO 进程重新连接到与不可信的事务相关的数据库后，她将负责对此事务进行处

12、理，并从相关数据库的活动事务表（pending transaction table）中移除和此事务有关的数据 作业队列进程: 作业队列进程（job queue process）的功能是进行批处理（batch processing）。这种进程用于运行用户的作业（job）. 归档进程（ARCn）: 归档进程（archiver process，ARCn）在发生日志切换（log switch）时将重做日志文件复制到指定的存储设备中。只有当数据库运行在 ARCHIVELOG 模式下，且自动归档功能被开启时，系统才会启动 ARCn 进程。 队列监控进程（QMNn）: 队列监控进程（queue monito

13、r process）是供 Oracle 工作流高级队列（Oracle Streams Advanced Queuing）使用的可选的进程，用于监控消息队列 其他后台进程图1-4 中间为 SGA。上部为 RECO，PMON，及 SMON 进程，其间的双向箭头表示各进程与实例间的通信。下部左侧为 DBW0 和 LGWR 进程，这两个进程分别和数据缓存区与重做日志缓冲区进行通信，同时还分别访问数据文件和重做日志文件。本图中还展示了一些其他进程，例如 ARC0，需要访问脱机存储设备和控制文件；以及 CKPT，需要访问数据文件和控制文件。1.1.3. 内存体系结构 Oracle 中的基本内存结构包括：

14、系统全局区（System Global Area，SGA），此区域由所有的服务进程（server process）和后台进程（background process）共享。 程序全局区（Program Global Areas，PGA），此区域是每个服务进程和后台进程所私有的；即每个进程都有一个 属于自己的 PGA。其中包括 Java 池，数据缓存区，重做日志缓冲区，共享池，数据流池，及大型池。在 SGA 之外，存在服务进程，后台进程，及 Oracle 进程，她们能够和 SGA 交换信息。同时每个进程还需与其 PGA 通信。SGA 中还包含了数据库及实例的状态信息，供后台进程使用。这部分内容被称

15、为固定 SGA（fixed SGA）。用户数据不会存储在这个区域内。SGA 中还包含需要在 Oracle 进程间进行交换的信息（例如，锁信息）。1.1.3.1. 数据缓存区（database buffer cache）用于存储从数据文件（datafile）读出的数据块（data block）副本。所有并发地连接到实例上的用户进程（user process）都将共享同一个数据缓存区。1.1.3.2. 重做日志缓冲区（redo log buffer）是 SGA 内一块被循环使用的缓冲区，用于记录数据库内的数据变化信息。这些信息以重做条目（redo entry）的形式进行存储。Oracle 利用重做

16、条目内的信息就可以重做由 INSERT，UPDATE，DELETE，CREATE，ALTER，及 DROP 等操作对数据库进行的修改。重做条目可以被用于进行数据库恢复（database recovery）。1.1.3.3. 共享池（shared pool）SGA 的共享池（shared pool）内包含了库缓存（library cache），数据字典缓存区（dictionary cache），并行执行消息缓冲区（buffers for parallel execution messages），以及用于系统控制的各种内存结构。库缓存（library cache）中包含共享 SQL 区（share

17、d SQL area），私有 SQL 区（private SQL area）（当系统运行在共享服务器模式下时），PL/SQL 过程和包，以及用于系统控制的各种内存结构，例如锁（lock）及库缓存句柄（library cache handle）等。Oracle 为其所执行的每个 SQL 提供一个共享 SQL 区（shared SQL area）及一个私有 SQL 区（private SQL area）, 共享 SQL 区（shared SQL area）中存储了此 SQL 的解析树（parse tree）及执行计划（execution plan）。令多次运行的 SQL 语句使用同一个共享 SQL

18、 区可以为 Oracle 节约大量的内存开销，这在大量用户运行相同应用的环境里尤为明显。当一个新的 SQL 语句被解析后，Oracle 会从共享池（shared pool）中分配一块内存创建共享 SQL 区，以保存解析结果。所分配内存的容量大小取决于语句的复杂程度。如果共享池内没有可用的内存，Oracle 将使用改进的 LRU（modified LRU）算法清除共享池内已有的共享 SQL 区，直到其中有足够的空间容纳新语句的共享 SQL 区。一个共享 SQL 区被 Oracle 清除出共享池后，相应的 SQL 语句再次执行时需要重新解析并分配新的共享 SQL 区。数据字典缓存区（data di

19、ctionary cache）是一系列保存了数据库参考信息（例如数据库结构，数据库用户等）的表和视图。Oracle 需要频繁地使用经过解析的 SQL 语句访问数据字典。数据字典信息对 Oracle 能否正常运行至关重要。由于 Oracle 对数据字典的访问极为频繁，因此内存中有两个特殊区域用于存储数据字典信息。一个区域是数据字典缓存区（data dictionary cache），因为数据在其中是以数据行的形式存储的（通常缓冲区内保存的是完整的数据块），所以此区域也被称为行缓存（row cache）。另一个区域为库缓存（library cache）。所有 Oracle 数据库进程在访问数据字典

20、信息时都能够共享这两个缓存区.1.1.3.4. 大型池（large pool）数据库管理员可以配置一个称为大型池（large pool）的可选内存区域，供一次性大量的内存分配使用，例如： 共享服务器（shared server）及 Oracle XA 接口（当一个事务与多个数据库交互时使用的接口）使用的会话内存（session memory） I/O 服务进程 Oracle 备份与恢复操作1.1.3.5. Java 池（Java pool）供各会话内运行的 Java 代码及 JVM 内的数据使用的。Java 池的内存使用方式与 Oracle 服务器的运行模式有关。1.1.3.6. 数据流池（S

21、treams pool）是从10g开始才增加的一个新的SGA结构。流池会用于缓存流进程在数据库间移动/复制数据时使用的队列消息。比如expdb，impdb等。1.1.3.7. PGA 中内容PGA 内存中存储的内容依据实例服务进程（server process）的模式而有所不同。但是通常来说，PGA 中含有以下内容 私有 SQL 区（private SQL area）中包含绑定信息（bind information）及运行时内存结构（runtime memory structure）等数据。每个提交了 SQL 语句的会话都有一个私有 SQL 区。每个提交了相同 SQL 语句的用户都有自己的私有

22、 SQL 区，但她们使用同一个共享 SQL 区（shared SQL area）。即多个私有 SQL 区可以和同一个共享 SQL 区相联系 Oracle 预编译程序（precompiler program）及 OCI 程序的开发者可以显式地打开游标（cursor）（游标即私有 SQL 区的句柄（handle），并在程序运行过程中依据游标名称使用相关资源。当 Oracle 执行某些 SQL 语句时隐式提交的递归游标（recursive cursor）还需使用共享 SQL 区（shared SQL area）。 会话内存（session memory）用于存储会话的变量（登录信息）及其他与会话有关

23、的信息。对于共享服务器（shared server）而言，会话内存是共享的而非为某个会话所私有。1.1.4. 逻辑结构数据库逻辑结构包含表空间、段、范围（extent）、数据块和模式对象。1.1.4.1. 表空间概述 数据库是由一个或多个被称为表空间（tablespace）的逻辑存储单位构成。表空间内的逻辑存储单位为段（segment），段又可以继续划分为数据扩展（extent）。而数据扩展是由一组连续的数据块（data block）构成。 Oracle中的数据逻辑上存储于表空间（tablespace）中，而物理上则存储于属于表空间的数据文件（datafile）中。1.1.4.2. 数据块，数

24、据扩展，段简介 Oracle数据库负责为所有数据分配逻辑存储空间。数据库空间的分配单位有数据块（Data Block），数据扩展（Extent），和段（Segment）段，数据扩展，数据块之间的关系Oracle存储数据的最小粒度（finest level of granularity）被称为数据块（data block）（也叫做逻辑块（logical block），Oracle块（Oracle block）或页（page）。一个数据块对应于磁盘上数个字节（byte）的物理数据库空间。处于数据块之上的逻辑数据库空间是数据扩展（extent）。数据扩展是为存储数据而分配的一组连续的数据块位于数据扩

25、展之上的逻辑数据库存储结构是段（segment）。段由一组数据扩展 （extent）构成，这些数据扩展位于同一表空间（tablespace）中，用于存储各种逻辑数据结构。例如每个表（table）的数据都存储在其自身的数据段（data segment）中，每个索引（index）的数据都存储在其自身的索引段（index segment）中。如果表或索引是分区存储（partitioned）的，则每个分区拥有自己的段。Oracle为段（segment）分配空间时以数据扩展（extent）为单位。当段内已有的数据扩展没有可用空间时，Oracle为此段分配一个新的数据扩展。因为数据扩展是随需分配的，所以一

26、个段内的数据扩展在磁盘上未必是连续的。一个段（segment）以及属于她的所有数据扩展（extent）必须包含在同一表空间（tablespace）中。但在一个表空间内，属于同一个段的数据扩展可以分布 在多个数据文件（datafile）上，即段可以跨文件存储。但是每个数据扩展只能包含于同一个数据文件中。在用户分配一个新的数据扩展（extent）时，其中的数据块（data block）未必被同时分配。如果用户是为某个数据库对象分配数据扩展 ，那么数据块也同时被立即分配并加入可用块列表（free list）中。如果数据扩展并非专为某数据库对象分配，那么数据块只在高水位线（high water mar

27、k）移动时才被分配。高水位线是段（segment）中已用和未用空间的边界。1.1.4.3. 数据块结构本图显示了数据块的各个组成部分，包括：数据块头（包括标准内容和可变内容）（common and variable header），表目录区（table directory），行目录区（row directory），可用空间区（free space），行数据区（row data）。以下各节将分别讲解各个组成部分。图中两个箭头表示一个数据块中的可用空间区的容量是可变的 数据块头（header）中包含了此数据块的概要信息，例如块地址（block address）及此数据块所属的段（segment）的

28、类型（例如，表或索引）。 如果一个数据表在此数据块中储存了数据行，那么数据表的信息将被记录在数据块的表目录区（table directory）中。 此区域包含数据块中存储的数据行的信息（每个数据行片断（row piece） 在行数据区（row data area）中的地址）。一个数据块中可能保存一个完整的数据行，也可能只保存数据行的一部分 ，所以文中使用row piece 当一个数据块（data block）的行目录区（row directory）空间被使用后，即使数据行被删除（delete），行目录区空间也不会被回收。举例来说，当一个曾经包含50条记录的数据块被清空 后，其块头（header

29、）的行目录区仍然占用100字节（byte）的空间。只有在数据块中插入（insert）新数据时，行目录区空间才会被 重新利用。1.2. 基本的索引原理选择性Oracle根据查询和数据，提供了多种方法来判断使用索引的价值。第一个方法就是判断索引中的唯一键或不同键的数量。可以对表或索引进行分析来确定不同 键的数量。可以查询USER_INDEXES视图的Distinct_Keys列来研究分析的结果。比较一下唯一键的数量和表中的行数（如 USER_INDEXES视图的Num_Rows列所示），就可以判断索引的选择性。选择性越高，索引返回的行数就越少，该索引就越好。索引的选择性可以帮助基于成本的优化器来判

30、断执行路径。索引的选择性越高，针对每个不同值返回的行数也越少。对于组合索引在索引中添加额外的列不会显著改善选择性，并且使用额外列的成本会超出收益。集群因子集群因子是索引与它所基于的表相比较而得出的有序性度量，它用于检查在索引访问之后执行的表查找的成本（将集群因子与选择性相乘即可得到该操作的成 本）。集群因子记录在扫描索引时将读取的块数量。如果使用的索引具有较大的集群因子，则必须访问更多的表数据块才可以获得每个索引块中的行（因为邻近行位 于不同的块中）。如果集群因子接近于表中的块数量，则表示索引适当排序；但是，如果集群因子接近于表中的行数量，则表示索引没有适当排序。集群因子的计算 简要介绍如下：（1） 按顺序扫描索引。（2） 将当前索引值指向的ROWID的块部分与以前的索引值进行比较（比较索引中的邻近行）。（3） 如果ROWID指向不同的TABLE块，则增加集群因子（对整个索引执行该操作）。Clustering_Factor列位于USER_INDEXES视图中，该列反映了数据相对于已索引的列是否显得有序。如果 Clustering_Fac