一 : oracle内存结构详解

Oracle 内存结构

与 Oracle 实例关联的基本内存结构包括：

系统全局区 (SGA)：由所有服务器和后台进程共享。SGA 中存储的数据示例包括高速缓存的数据块和共享 SQL 区域。

程序全局区 (PGA)：各个服务器进程和后台进程专用，每个进程都有一个 PGA。

SGA 是共享内存区，包含实例的数据和控制信息，由以下各项组成：

数据库缓冲区高速缓存：用于缓存从磁盘检索到的数据块
重做日志缓冲区：用于缓存重做信息，直到其可以写入磁盘为止
共享池：用于缓存可在用户间共享的各种结构
大型池：用于缓冲大型 I/O 请求的可选区域，以便支持并行查询、共享服务器、Oracle XA 以及某些类型的备份操作

Java 池：用于存放 Java虚拟机(JVM) 中特定于会话的 Java 代码和数据
流池：由 Oracle Streams 使用
保留缓冲区高速缓存：用于存放会尽可能长地保留在缓冲区高速缓存中的数据

循环缓冲区高速缓存：用于存放缓冲区高速缓存中很快过期的数据

nK 块大小缓冲区高速缓存：用于缓存大小与默认数据库块大小不同的数据块，用来支持可传输的表空间
数据库缓冲区高速缓存、共享池、大型池、流池和Java池的大小可以按照当前需求自动调整。而且，这些内存缓冲区以及保留缓冲区高速缓存、循环缓冲区高速缓存和 nK 块大小缓冲区高速缓存可以在不关闭实例的情况下进行更改。

Oracle数据库会提供预警和指导，以标识内存大小问题，从而帮助确定相应的内存参数值。

程序全局区 (PGA) 是一个内存区，其中包含每个服务器进程的数据及控制信息。服务器进程是处理客户机请求的进程。每个服务器进程都有在服务器进程启动时创建的自己专用的 PGA。只有该服务器进程才能访问。

所用 PGA 内存量和 PGA 的内容取决于实例是否是在共享服务器模式下配置的。通常，PGA 包含下列内容：

专用 SQL 区：包含绑定信息和运行时内存结构等数据。发出 SQL 语句的每个会话都有一个专用 SQL 区。
会话内存：此处分配的内存用于存放会话变量以及与该会话相关的其它信息。

1.缓冲区高速缓存 data buffer cache

通过指定 DB_CACHE_SIZE 参数的值，可以配置缓冲区高速缓存。缓冲区高速缓存可存放数据文件中块大小为 DB_BLOCK_SIZE 的数据块的副本。缓冲区高速缓存是 SGA 的一部分；因此所有用户都可以共享这些块。服务器进程将数据文件中的数据读入缓冲区高速缓存。为了提高性能，服务器进程有时在一个读操作中会读取多个块。然后由 DBWn 进程将数据从缓冲区高速缓存写入数据文件。为提高性能，DBWn 在一个写操作中会写入多个块。

在任何给定时间，缓冲区高速缓存都可能会存放一个数据库块的多个副本。虽然该块只存在一个当前副本，但为了满足查询需要，服务器进程可能需要根据过去的映像信息构造读一致性副本。这称为读一致性 (CR) 块。

最近最少使用 (LRU) 列表可反映缓冲区的使用情况。缓冲区将依据其被引用时间的远近和引用频率进行排序。因此，最经常使用且最近使用过的缓冲区将列在最近最常使用一端。传入的块先被复制到最近最少使用一端的缓冲区中，然后该缓冲区将被指定到列表中央，作为起点。从这个起点开始，缓冲区根据使用情况在列表中上下移动。

缓冲区高速缓存中的缓冲区可以处于以下四种状态之一：
已连接：当前正将该块读入高速缓存或正在写入该块。其它会话正等待访问该块。

干净的：该缓冲区目前未连接，如果其当前内容（数据块）将不再被引用，则可以立即执行过期处理。这些内容与磁盘保持同步，或者缓冲区包含块的读一致性快照。

空闲/未使用：缓冲区因实例刚启动而处于空白状态。此状态与“干净的”状态非常相似，不同之处在于缓冲区未曾使用过。

灰：缓冲区不再处于连接状态，但内容（数据块）已更改，因此必须先通过 DBWn 将内容刷新到磁盘，然后才能执行过期处理。

服务器进程使用缓冲区高速缓存中的缓冲区；而 DBWn 进程通过将更改的缓冲区写回数据文件，使高速缓存中的缓冲区变为可用状态。检查点队列中列出将要写出到磁盘的缓冲区。

Oracle 数据库支持同一数据库中有多种块大小。标准块大小用于 SYSTEM 表空间。标准块大小可以通过设置初始化参数 DB_BLOCK_SIZE 来指定。其有效值介于 2 KB 到 32 KB 之间，默认值为 8 KB。非标准块大小的缓冲区的高速缓存大小通过以下参数指定：

DB_2K_CACHE_SIZE

DB_4K_CACHE_SIZE

DB_8K_CACHE_SIZE

DB_16K_CACHE_SIZE

DB_32K_CACHE_SIZE

DB_nK_CACHE_SIZE 参数不能用于调整标准块大小的高速缓存的大小。如果 DB_BLOCK_SIZE 的值为 nK，则设置 DB_nK_CACHE_SIZE 是非法的。标准块大小的高速缓存的大小始终由 DB_CACHE_SIZE 的值确定。

由于每个缓冲区高速缓存的大小都有限制，因此，通常并非磁盘上的所有数据都能放在高速缓存中。当高速缓存写满时，后续高速缓存未命中会导致 Oracle 数据库将高速缓存中已有的灰数据写入磁盘，以便为新数据腾出空间。（如果缓冲区中没有灰数据，则不需要写入磁盘即可将新块读入该缓冲区。）以后若对已写入磁盘的任何数据进行访问，则会导致再次出现高速缓存未命中现象。

数据请求导致高速缓存命中的几率会受到高速缓存大小的影响。高速缓存越大，包含所请求数据的几率也就越大。因此，增加高速缓存大小会提高引起高速缓存命中的数据请求的百分比。

数据库管理员 (DBA) 可以创建多个缓冲区池来提高数据库缓冲区高速缓存的性能。您可以根据对象的访问情况将其分配给某个缓冲区池。缓冲区池有三种：

保留：此池用于保留内存中可能要重用的对象。将这些对象保留在内存中可减少 I/O 操作。通过使池的大小大于分配给该池的各个段的总大小，可以将缓冲区保留在此池中。这意味着缓冲区不必执行过期处理。保留池可通过指定 DB_KEEP_CACHE_SIZE 参数的值来配置。

循环：此池用于内存中重用几率很小的块。循环池的大小要小于分配给该池的各个段的总大小。这意味着读入该池的块经常需要在缓冲区内执行过期处理。循环池可通过指定 DB_RECYCLE_CACHE_SIZE 参数的值来配置。

默认：此池始终存在。它相当于没有保留池和循环池的实例的缓冲区高速缓存，可通过 DB_CACHE_SIZE 参数进行配置。

注：保留池或循环池中的内存不是默认缓冲区池的子集。

CREATE INDEX cust_idx …
STORAGE (BUFFER_POOL KEEP …);

ALTER TABLE oe.customers
STORAGE (BUFFER_POOL RECYCLE);

ALTER INDEX oe.cust_lname_ix
STORAGE (BUFFER_POOL KEEP);

手工刷新内存：
alter system flush buffer cache;

BUFFER_POOL 子句用于定义对象的默认缓冲区池。它是 STORAGE 子句的一部分，对 CREATE 和 ALTER 表、簇和索引语句有效。未明确设置缓冲区池的对象中的块将进入默认缓冲区池。

语法为：BUFFER_POOL [KEEP | RECYCLE | DEFAULT]。

使用 ALTER 语句更改对象的默认缓冲区池时，已缓存的块会一直保留在其当前缓冲区中，直到正常缓冲区管理活动将它们清除为止。从磁盘读取的块将被放置在为该段新指定的缓冲区池中。

由于多个缓冲区池被分配给某一个段，所以有多个段的对象可以将块放置在多个缓冲区池中。例如，按索引组织的表在索引段和溢出段上可以有多个不同的池。

2.共享池 shared pool

大小通过 SHARED_POOL_SIZE 指定。
库高速缓存包含语句文本、已进行语法分析的代码和执行计划。
数据字典高速缓存包含数据字典表中各表、列和权限的定义。
用户全局区 (UGA) 包含会话信息（如果使用 Oracle 共享服务器）。

库高速缓存：库高速缓存包含共享 SQL 区和 PL/SQL 区 - 经过完全语法分析或编译的 PL/SQL 块和 SQL 语句的表示法。PL/SQL 块包括：

过程和函数

程序包

触发器

匿名
PL/SQL 块

数据字典高速缓存：数据字典高速缓存将字典对象的定义存放在内存中。

结果高速缓存：结果高速缓存包含 SQL 查询结果高速缓存和 PL/SQL 函数结果高速缓存。此高速缓存用于存储 SQL 查询或 PL/SQL 函数的结果，以加快其将来的执行速度。

用户全局区：UGA 包含 Oracle 共享服务器的会话信息。使用共享服务器会话时，如果尚未配置大型池，则 UGA 位于共享池中。

3.大型池large pool

可配置为 SGA 中一个单独的内存区

大小由 LARGE_POOL_SIZE 参数指定

用于在内存中为以下各项存储数据：
--UGA

--备份和还原操作

--并行查询消息传送

大型池必须显式配置。大型池的内存不是来自共享池，而是直接来自 SGA，这就增大了 Oracle 服务器在实例启动时需要的共享内存量。

大型池用于为以下各项分配大量会话内存：

--I/O 服务器进程

--备份和还原操作

--Oracle 共享服务器进程和 Oracle XA 接口（事务处理与多个数据库交互时使用）

由于从大型池为 Oracle 共享服务器分配会话内存，因此共享池中由于频繁分配和取消分配大对象而产生的碎片也就很少。将大对象从共享池中分离出来，可增加共享池内存的使用效率，这意味着，它可以将更多内存用于处理新的请求，以及在需要时用于保留现有数据。

4.Java pool

可配置为 SGA 中一个单独的内存区

大小由 JAVA_POOL_SIZE 参数指定

用于将 JVM 中特定于会话的所有 Java 代码和数据存储在内存中

5.重做日志缓冲区 redo buffer cache

Oracle 服务器进程将重做条目从用户的内存空间复制到每个 DML 或 DDL 语句的重做日志缓冲区。重做条目包含重建或重做 DML 和 DDL 操作对数据库的更改所必需的信息。它们用于数据库恢复，需要占用缓冲区中的连续空间。

重做日志缓冲区是一个循环缓冲区；服务器进程可以用新条目覆盖重做日志缓冲区中已写入磁盘的条目。LGWR 进程的写速度通常都很快，足以确保缓冲区中始终有存储新条目的空间。LGWR 进程将重做日志缓冲区写入磁盘上的活动联机重做日志文件（或活动组成员）中。LGWR 进程将 LGWR 上次写入磁盘以来进入缓冲区的所有重做条目复制到磁盘。

什么导致 LGWR 执行写操作？

用户进程提交事务处理时

每隔三秒，或每当重做日志缓冲区占满三分之一时

DBWn 进程将修改的缓冲区写入磁盘时（如果相应的重做日志数据尚未写入磁盘）

二 : Oracle之唯一性约束(UNIQUEConstraint)用法详解

Oracle| PL/SQL唯一索引(Unique Constraint)用法(www.61k.com)

1 目标

用示例演示如何创建、删除、禁用和使用唯一性约束。

2 什么是唯一性约束？

唯一性约束指表中一个字段或者多个字段联合起来能够唯一标识一条记录的约束。联合字段中，可以包含空值。

注：在Oracle中，唯一性约束最多可以有32列。

唯一性约束可以在创建表时或使用ALTER TABLE语句创建。

3 唯一性约束和主键的区别

4 创建表时定义唯一性约束

1）语法：

CREATE TABLE table_name( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name UNIQUE (column1, column2,...,column_n));

2）基于单列的唯一性约束示例：

create table tb_supplier( supplier_id number not null ,supplier_name varchar2(50) ,contact_name varchar2(50) ,CONSTRAINT tb_supplier_u1 UNIQUE (supplier_id)--创建表时创建唯一性约束);

3）基于多列的唯一性约束示例：

create table tb_products( product_id number not null, product_name number not null, product_type varchar2(50), supplier_id number, CONSTRAINT tb_products_u1 UNIQUE (product_id, product_name) --定义复合唯一性约束);

5 使用ALTER TABLE语法创建唯一性约束

1）语法

ALTER TABLE table_nameADD CONSTRAINT constraint_nameUNIQUE (column1, column2, ... , column_n);

2）示例准备，先创建表

drop table tb_supplier;drop table tb_products;create table tb_supplier( supplier_id number not null ,supplier_name varchar2(50) ,contact_name varchar2(50));create table tb_products( product_id number not null, product_name number not null, product_type varchar2(50), supplier_id number);

3）基于单列的唯一性约束

alter table tb_supplieradd constraint tb_supplier_u1unique (supplier_id);

4）基于多列的唯一性约束

alter table tb_productsadd constraint tb_products_u1unique (product_id,product_name);

6 禁用唯一性约束

1）语法：

ALTER TABLE table_nameDISABLE CONSTRAINT constraint_name;

2）示例：

ALTER TABLE tb_supplierDISABLE CONSTRAINT tb_supplier_u1;

7 使用唯一性约束

1）语法：

ALTER TABLE table_nameENABLE CONSTRAINT constraint_name;

2）示例：

ALTER TABLE tb_supplierENABLE CONSTRAINT tb_supplier_u1;

8 删除唯一性约束

1）语法：

ALTER TABLE table_nameDROP CONSTRAINT constraint_name;

2）示例：

ALTER TABLE tb_supplier DROP CONSTRAINT tb_supplier_u1;ALTER TABLE tb_products DROP CONSTRAINT tb_products_u1;

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

扩展：unique constraint / uniqueconstraint用法 / jpa uniqueconstraint

三 : ORACLE的HINT详解

ORACLE的HINT详解

hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：

1) 使用的优化器的类型

2) 基于代价的优化器的优化目标，是all_rows还是first_rows。

3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。

4) 表之间的连接类型

5) 表之间的连接顺序

6) 语句的并行程度

2、HINT可以基于以下规则产生作用

表连接的顺序、表连接的方法、访问路径、并行度

3、HINT应用范围

dml语句

查询语句

4、语法

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

如果语（句）法不对，则ORACLE会自动忽略所写的HINT，不报错

1. /*+ALL_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.

例如:

SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

2. /*+FIRST_ROWS*/

表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

例如:

SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

3. /*+CHOOSE*/

表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;

表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;

例如:

SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

4. /*+RULE*/

表明对语句块选择基于规则的优化方法.

例如:

SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+FULL(TABLE)*/

表明对表选择全局扫描的方法.

例如:

SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

6. /*+ROWID(TABLE)*/

提示明确表明对指定表根据ROWID进行访问.

例如:

SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'

AND EMP_NO='SCOTT';

7. /*+CLUSTER(TABLE)*/

提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.

例如:

SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS

WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/

表明对表选择索引的扫描方法.

例如:

SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/

表明对表选择索引升序的扫描方法.

例如:

SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

10. /*+INDEX_COMBINE*/

为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.

例如:

SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS

WHERE SAL<5000000 AND HIREDATE

11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/

提示明确命令优化器使用索引作为访问路径.

例如:

SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE

FROM BSEMPMS WHERE SAL<60000;

12. /*+INDEX_DESC(TABLE INDEX_NAME)*/

表明对表选择索引降序的扫描方法.

例如:

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=&#39;SCOTT&#39;;

13. /*+INDEX_FFS(TABLE INDEX_NAME)*/

对指定的表执行快速全索引扫描,而不是全表扫描的办法.

例如:

SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO=&#39;TEC305&#39;;

14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/

提示明确进行执行规划的选择,将几个单列索引的扫描合起来.

例如:

SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO=&#39;SCOTT&#39; AND DPT_NO=&#39;TDC306&#39;;

15. /*+USE_CONCAT*/

对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.

例如:

SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO=&#39;TDC506&#39; AND SEX=&#39;M&#39;;

16. /*+NO_EXPAND*/

对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.

例如:

SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO=&#39;TDC506&#39; AND SEX=&#39;M&#39;;

17. /*+NOWRITE*/

禁止对查询块的查询重写操作.

18. /*+REWRITE*/

可以将视图作为参数.

19. /*+MERGE(TABLE)*/

能够对视图的各个查询进行相应的合并.

例如:

SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO

,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO

AND A.SAL>V.AVG_SAL;

20. /*+NO_MERGE(TABLE)*/

对于有可合并的视图不再合并.

例如:

SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

21. /*+ORDERED*/

根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.

扩展：oracle的hint / oracle hint / oracle hint用法

例如:

SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

22. /*+USE_NL(TABLE)*/

将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

例如:

SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/

将指定的表与其他行源通过合并排序连接方式连接起来.

例如:

SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

24. /*+USE_HASH(TABLE)*/

将指定的表与其他行源通过哈希连接方式连接起来.

例如:

SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

25. /*+DRIVING_SITE(TABLE)*/

强制与ORACLE所选择的位置不同的表进行查询执行.

例如:

SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

26. /*+LEADING(TABLE)*/

将指定的表作为连接次序中的首表.

27. /*+CACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

例如:

SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

28. /*+NOCACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端

例如:

SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

29. /*+APPEND*/

直接插入到表的最后,可以提高速度.

insert /*+append*/ into test1 select * from test4 ;

30. /*+NOAPPEND*/

通过在插入语句生存期内停止并行模式来启动常规插入.

insert /*+noappend*/ into test1 select * from test4 ;

31. NO_INDEX: 指定不使用哪些索引

/*+ NO_INDEX ( table [index [index]...] ) */

select /*+ no_index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal>300;

32. parallel

select /*+ parallel(emp,4)*/ * from emp where deptno=200 and sal>300;

另：每个SELECT/INSERT/UPDATE/DELETE命令后只能有一个/*+ */，但提示内容可以有多个，可以用逗号分开，空格也可以。

如：/*+ ordered index() use_nl() */

---------

类似如下的一条语句：insert into xxxx select /*+parallel(a) */ * from xxx a;数据量大约在75G左右，这位兄弟从上午跑到下午还没跑完，过来问我咋回事，说平常2hrs能跑完的东西跑了好几个小时还撒动静。查看系统性能也比较 正常，cpu，io都不繁忙，平均READ速度在80M/s左右（勉强凑合），但平均写速度只有10M不到。等待事件里面大量的&lsquo; &lsquo;PX Deq Credit: send blkd&rsquo;，这里能看出并行出了问题，从而最后得知是并行用法有问题，修改之后20分钟完成了该操作。正确的做法应该是：

alter session enable dml parallel；

insert /*+parallel(xxxx,4) */ into xxxx select /*+parallel(a) */ * from xxx a;

因为oracle默认并不会打开PDML，对DML语句必须手工启用。 另外不得不说的是，并行不是一个可扩展的特性，只有在数据仓库或作为DBA等少数人的工具在批量数据操作时利于充分利用资源，而在OLTP环境下使用并行 需要非常谨慎。事实上PDML还是有比较多的限制的，例如不支持触发器，引用约束，高级复制和分布式事务等特性，同时也会带来额外的空间占用，PDDL同 样是如此。

---------

select count(*)

From wid_serv_prod_mon_1100 a

where a.acct_month = 201010

and a.partition_id = 10

and serv_state not in (&#39;2HB&#39;, &#39;2HL&#39;, &#39;2HJ&#39;, &#39;2HP&#39;, &#39;2HF&#39;)

and online_flag in (0)

and incr_product_id in (2000020)

and product_id in (2020966, 2020972, 2100297, 2021116)

and billing_mode_id = 1

and exp_date > to_date(&#39;201010&#39;, &#39;yyyymm&#39;)

and not exists (select /*+no_index (b IDX_W_CDR_MON_SERV_ID_1100)*/

1

from wid_cdr_mon_1100 b

where b.acct_month = 201010

and b.ANA_EVENT_TYPE_4 in

(&#39;10201010201&#39;, &#39;10202010201&#39;, &#39;10203010201&#39;, &#39;10203010202&#39;, &#39;10203030201&#39;, &#39;10203030202&#39;, &#39;10204010201&#39;, &#39;10204010202&#39;, &#39;10204030201&#39;)

and a.serv_id = b.serv_id)

扩展：oracle的hint / oracle hint / oracle hint用法

四 : 详解Oracle的几种分页查询语句

分页查询格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21