作者：YongJing(敬勇)

分区
分区（partitioning）最早在Oracle 8.0中引入，这个过程是将一个表或索引物理地分解为多个更小、更可管理的部分。就访问 数据库的应用而言，逻辑上讲只有一个表或一个索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个 独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。考虑使用分区原因是多方面的，可能是分区能提高数 据的可用性，或者是可以减少管理员的负担，另外在某些情况下，还可能提高性能。

分区只是一个工具，对索引或表进行分区时可能发生3种情况：使用这些分区表的应用可能运行得更慢；可能运行得更快；有可 能没有任何变化。

分区概述
分区有利于管理非常大的表和索引，它使用了一种“分而治之”的逻辑。分区引入了一种分区键（partition key）的概念，分 区键用于根据某个区间值（或范围值）、特定值列表或散列函数值执行数据的聚集。分区有以下好处：
（1） 提高数据的可用性：这个特点对任何类型的系统都适用，而不论系统本质上是OLTP还是仓库系统。
（2） 由于从数据库中去除了大段，相应地减轻了管理的负担。
（3） 改善某些查询的性能：主要在大型仓库环境中有这个好处，通过使用分区，可以消除很大的数据区间，从而不必考虑它们 ，相应地根本不用访问这些数据。但这在事务性系统中并不适用，因为这种系统本身就只是访问少量的数据。
（4） 可以把修改分布到多个单独的分区上，从而减少大容量OLTP系统上的竞争：如果一个段遭遇激烈的竞争，可以把它分为多 个段，这就可以成比例地减少竞争。

下面分别讨论使用分区可能带来的这些好处。

可用性的提高源自于每个分区的独立性。对象中一个分区的可用性（或不可用）并不意味着对象本身是不可用的。优化器知道有 这种分区机制，会相应地从查询计划中去除未引用的分区。在一个大对象中如果一个分区不可用，你的查询可以消除这个分区而 不予考虑，这样Oracle就能成功地处理这个查询。

为了展示这种可用性的提高，我们将建立一个散列分区表，其中有两个分区，分别在单独的表空间中。这里将创建一个EMP表， 它在EMPNO列上指定了一个分区键（EMPNO就是我们的分区键）。在这种情况下，这个结构意味着：对于插入到这个表中的每一行 ，会对EMPNO列的值计算散列，来确定这一行将置于哪个分区（及相应的表空间）中
先创建分区对应的表空间:

sys@JINGYONG> create tablespace p1 datafile '/u01/app/oracle/product/10.2.0/orad
ata/jingyong/jingyong/p101.dbf' size 50M;

表空间已创建。

sys@JINGYONG> create tablespace p2 datafile '/u01/app/oracle/product/10.2.0/orad
ata/jingyong/jingyong/p201.dbf' size 50M;

表空间已创建。

sys@JINGYONG> create table emp
  2  (empno int,
  3  ename varchar2(20)
  4  )
  5  partition by hash(empno)
  6  (partition part_1 tablespace p1,
  7  partition part_2 tablespace p2
  8  )
  9  ;

表已创建。

接下来，我们向表中插入一些数据，然后使用带分区的扩展表名检查各个分区的内容：

sys@JINGYONG> insert into emp select empno,ename from scott.emp;

已创建14行。

sys@JINGYONG> select * from emp partition(part_1);

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7876 ADAMS
      7934 MILLER

已选择8行。

sys@JINGYONG> select * from emp partition(part_2);

     EMPNO ENAME
---------- --------------------
      7521 WARD
      7566 JONES
      7788 SCOTT
      7844 TURNER
      7900 JAMES
      7902 FORD

已选择6行。

应该能注意到，数据的“摆放”有些随机。通过使用散列分区，我们让Oracle随机地（很可能均匀地）将数据分布到多个
分区上。我们无法控制数据要分布到哪个分区上；Oracle会根据生成的散列键值来确定。

下面将其中一个表空间脱机（例如，模拟一种磁盘出故障的情况），使这个分区中的数据不可用：

sys@JINGYONG> alter tablespace p1 offline;

表空间已更改。

接下来，运行一个查询，这个查询将命中每一个分区，可以看到这个查询失败了：

sys@JINGYONG> select * from emp;
select * from emp
              *
第 1 行出现错误:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon
g/p101.dbf'

不过，如果查询不访问脱机的表空间，这个查询就能正常工作；Oracle会消除脱机的分区而不予考虑。

sys@JINGYONG> select * from emp where empno=7844;

     EMPNO ENAME
---------- --------------------
      7844 TURNER

总之，只要优化器能从查询计划消除分区，它就会这么做。基于这一点，如果应用在查询中使用了分区键，就能提高这些应用的 可用性。

分区还可以通过减少停机时间来提高可用性。例如，如果有一个200GB的表，它划分为100个2GB的分区，这样就能更快地从错误 中恢复。如果某个2GB的分区遭到破坏，现在恢复的时间就只是恢复一个2GB分区所需的时间，而不是恢复一个200GB表的时间。 所以从两个方面提高了可用性：
(1) 优化器能够消除分区，这意味着许多用户可能甚至从未注意到某些数据是不可用的。
(2) 出现错误时的停机时间会减少，因为恢复所需的工作量大幅减少。

减少管理负担
之所以能减少管理负担，这是因为与在一个大对象上执行操作相比，在小对象上执行同样的操作从本质上讲更为容易、速度更快 ，而且占用的资源也更少。

例如，假设数据库中有一个100GB的索引。如果需要重建这个索引，而该索引未分区，你就必须将整个100GB的索引作为一个工作 单元来重建。尽管可以在线地重建索引，但是要完全重建完整的100GB索引，还是需要占用大量的资源。至少需要在某处有100GB 的空闲存储空间来存放索引的副本，还需要一个临时事务日志表来记录重建索引期间对基表所做的修改。另一方面，如果将索引 本身划分为100个1GB的分区，就可以一个接一个地单独重建各个索引分区。现在只需要原先所需空闲空间的100%。另外，各个索 引的重建也更快（可能是原来的100倍），需要向新索引合并的事务修改也更少（到此为止，在线索引重建期间发生的事务修改 会更少）。

另外请考虑以下情况：100GB索引的重建即将完成之前，如果出现系统或软件故障会发生什么。我们所做的全部努力都会付诸东 流。如果把问题分解，将索引划分为1GB的分区，你最多只会丢掉重建工作的1%。或者，你可能只需要重建全部聚集索引的1%， 例如，只是“最新”的数据（活动数据）需要重组，而所有“较旧”的数据（相当静态）不受影响。

最后，请考虑这样一种情况：你发现表中50%的行都是“移植”行（行链接或行迁移），可能想进修正。建立一个分区表将有利 于这个操作。为了“修正”移植行，你往往必须重建对象，在这种情况下，就是要重建一个表。如果有一个100GB的表，就需要 在一个非常大的“块”（chunk）上连续地使用ALTER TABLE MOVE来执行这个操作。另一方面，如果你有25个分区，每个分区的 大小为4GB，就可以一个接一个地重建各个分区。或者，如果你在空余时间做这个工作，而且有充足的资源，甚至可以在单独的 会话中并行地执行ALTER TABLE MOVE语句，这就很可能会减少整个操作所需的时间。对于一个未分区对象所能做的工作，分区对 象中的单个分区几乎都能做到。你甚至可能发现，移植行都集中在一个很小的分区子集中，因此，可以只重建一两个分区，而不 是重建整个表。

下面有一个小例子，展示了如何对一个有多个移植行的表进行重建。TABLE1和TABLE2都是从TABLE_T的一个10,000,000行的实例 创建的.TABLE1是一个常规的未分区表，而TABLE2是一个散列分区表，有8个分区:

sys@JINGYONG> create table table1
2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
3 OBJECT_ID, DATA_OBJECT_ID,
4 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
5 TIMESTAMP, STATUS, TEMPORARY,
6 GENERATED, SECONDARY )
7 tablespace table1
8 as
9 select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
10 OBJECT_ID, DATA_OBJECT_ID,
11 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
12 TIMESTAMP, STATUS, TEMPORARY,
13 GENERATED, SECONDARY
14 from table_T;
Table created.

sys@JINGYONG> create table table2
2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
3 OBJECT_ID, DATA_OBJECT_ID,
4 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
5 TIMESTAMP, STATUS, TEMPORARY,
6 GENERATED, SECONDARY )
7 partition by hash(id)
8 (partition part_1 tablespace table2,
9  partition part_2 tablespace table2,
10 partition part_3 tablespace table2,
11 partition part_4 tablespace table2,
12 partition part_5 tablespace table2,
13 partition part_6 tablespace table2,
14 partition part_7 tablespace table2,
15 partition part_8 tablespace table2
16 )
17 as
18 select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
19 OBJECT_ID, DATA_OBJECT_ID,
20 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
21 TIMESTAMP, STATUS, TEMPORARY,
22 GENERATED, SECONDARY
23 from table_t;
Table created.

现在，每个表都在自己的表空间中，所以我们可以很容易地查询数据字典，来查看每个表空间中已分配的空间和空闲空间：

sys@JINGYONG> select b.tablespace_name,
2 mbytes_alloc,
3 mbytes_free
4 from ( select round(sum(bytes)/1024/1024) mbytes_free,
5 tablespace_name
6 from dba_free_space
7 group by tablespace_name ) a,
8 ( select round(sum(bytes)/1024/1024) mbytes_alloc,
9 tablespace_name
10 from dba_data_files
11 group by tablespace_name ) b
12 where a.tablespace_name (+) = b.tablespace_name
13 and b.tablespace_name in ('table1','table2')
14 /
TABLESPACE MBYTES_ALLOC MBYTES_FREE
---------- ------------ -----------
TABLE1 1596 524
TABLE2 1596 524

table1和table2的大小都大约是1.6GB，每个表空间都有524MB的空闲空间。我们想创建第一个表TABLE1：

sys@JINGYONG> alter table table1 move;
alter table table1 move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 1024 in tablespace table1

但失败了,table1表空间中要有足够的空闲空间来放下ABLE1的完整副本，同时它的原副本仍然保留，简单地说，我们需要一个很 短的时间内有大约两倍的存储空间（可能多一点，也可能少移动，这取决于重建后表的大小）。现在试图对TABLE2执行同样的操作

sys@JINGYONG> alter table table2 move;
alter table table2 move
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

这说明，Oracle在告诉我们：无法对这个“表”执行MOVE操作；我们必须在表的各个分区上执行这个操作。可以逐个地移动（相 应地重建和重组）各个分区：

sys@JINGYONG> alter table table2 move partition part_1;
Table altered.
sys@JINGYONG> alter table table2 move partition part_2;
Table altered.
sys@JINGYONG> alter table table2 move partition part_3;
Table altered.
sys@JINGYONG> alter table table2 move partition part_4;
Table altered.
sys@JINGYONG> alter table table2 move partition part_5;
Table altered.
sys@JINGYONG> alter table table2 move partition part_6;
Table altered.
sys@JINGYONG> alter table table2 move partition part_7;
Table altered.
sys@JINGYONG> alter table table2 move partition part_8;
Table altered.

对于每个移动，只需要有足够的空闲空间来存放原来数据的1/8的副本！因此，假设有先前同样多的空闲空间，这些命令就能成 功。我们需要的临时资源将显著减少。不仅如此，如果在移动到PART_4后但在PART_5完成“移动”之前系统失败了（例如，掉电 ），我们并不会丢失以前所做的所有工作，这与执行一个MOVE语句的情况不同。前4个分区仍是“移动”后的状态，等系统恢复 时，我们可以从分区PART_5继续处理。 也可以很容易地编写一个脚本来解决上面输入8次语句的题：

sys@JINGYONG> begin
2 for x in ( select partition_name
3 from user_tab_partitions
4 where table_name = 'TABLE2' )
5 loop
6 execute immediate
7 'alter table table2 move partition ' ||
8 x.partition_name;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.

关于分区和管理，还有一个因素需要考虑，这就是在维护数据仓库和归档中使用数据“滑动窗口”。在许多情况下，需要保证数 据在最后N个时间单位内一直在线。例如，假设需要保证最后12个月或最后5年的数据在线。如果没有分区，这通常是一个大规模 的INSERT，其后是一个大规模的DELETE。为此有相对多的DML，并且会生成大量的redo和undo。如果进行了分区，则只需做下面 的工作：
（1） 用新的月（或年，或者是其他）数据加载一个单独的表。
（2） 对这个表充分建立索引（这一步甚至可以在另一个实例中完成，然后传送到这个数据库中）。
（3） 将这个新加载（并建立了索引）的表附加到分区表的最后，这里使用一个快速DDL命令：ALTER TABLE EXCHANGE PARTITION。
（4） 从分区表另一端将最旧的分区去掉。

这样一来，现在就可以很容易地支持包含时间敏感信息的非常大的对象。数据很容易地从分区表中去除，如果不再需要它，可以 简单地将其删除；或者也可以归档到某个地方。新数据可以加载到一个单独的表中，这样在加载、建索引等工作完成之前就不会 影响分区表.

改善语句性能
分区最后一个好处体现在改进语句（SELECT、INSERT、UPDATE、DELETE、MERGE）的性能方面。我们来看两类语句，一种是修改 信息的语句，另一种是只读取信息的语句，并讨论在这种情况下可以从分区得到哪些好处。
1. 并行DML
修改数据库中数据的语句有可能会执行并行DML（parallel DML，PDML）。采用PDML时，Oracle使用多个线程或进程来执行 INSERT、UPDATE或DELETE， 而不是执行一个串行进程。在一个有充足I/O带宽的多CPU主机上，对于大规模的DML操作，速度的提 升可能相当显著。在Oracle9i以前的版本中，PDML要求必须分区。如果你的表没有分区，在先前的版本中就不能并行地执行这些 操作。如果表确实已经分区，Oracle会根据对象所有的物理分区数为对象指定一个最大并行度。从很大程度上讲，在Oracle9i及 以后版本中这个限制已经放松，只有两个突出的例外；如果希望在一个表上执行PDML，而且这个表的一个LOB列上有一个位图索 引，要并行执行操作就必须对这个表分区；另外并行度就限制为分区数。不过，总的说来，使用PDML并不一定要求进行分区。

查询性能
在只读查询（SELECT语句）的性能方面，分区对两类特殊操作起作用：
分区消除（partition elimination）：处理查询时不考虑某些数据分区。我们已经看到了一个分区消除的例子。
并行操作（parallel operation）：并行全表扫描和并行索引区间扫描就是这种操作的例子。
不过，由此得到的好处很多程度上取决于你使用何种类型的系统。

OLTP系统
在OLTP系统中，不应该把分区当作一种大幅改善查询性能的方法。实际上，在一个传统的OLTP系统中，你必须很小心地应用分区 ，提防着不要对运行时性能产生负面作用。在传统的OLTP系统中，大多数查询很可能立即返回，而且大多数数据库获取可能都通 过一个很小的索引区间扫描来完成。因此，以上所列分区性能方面可能的主要优点在OLTP系统中表现不出来。分区消除只在大对 象全面扫描时才有用，因为通过分区消除，你可以避免对对象的很大部分做全面的扫描。不过，在一个OLTP环 境中，本来就不 是大对象全面扫描（如果真是如此，则说明肯定存在严重的设计缺陷）。即使对索引进行了分区，就算是真的能在速度上有所提 高，通过扫描较小索引所得到的性能提升也是微乎其微的。如果某些查询使用了一个索引，而且它们根本无法消除任何分区，你 可能会发现，完成分区之后查询实际上运行得反而更慢了，因为你现在要扫描5、10或20个更小的索引，而不是一个较大的索引 。

尽管如此，有分区的OLTP系统确实也有可能得到效率提高。例如，可以用分区来减少竞争，从而提高并发度。可以利用分区将一 个表的修改分布到多个物理分区上。并不是只有一个表段和一个索引段，而是可以有10个表分区和20个索引分区。这就像有20个 表而不是1个表，相应地，修改期间就能减少对这个共享资源的竞争。

至于并行操作,你可能不希望在一个OLTP系统中执行并行查询。你会慎用并行操作，事实上在一个OLTP系统中，查询已经有以下 特点：即索引访问相当快，因此，分区不会让索引访问的速度有太大的高（甚至根本没有任何提高）。这并不是说要绝对避免在 OLTP系统中使用分区；而只是说不要指望通过分区来提供大幅的性能提升。尽管有些情况下分区能够改善查询的性能，但是这些 情况在大多数OLTP应用中并不成立。不过在OLTP系统中，你还是可以得到另外两个可能的好处：减轻管理负担以及有更高的可用 性。

数据仓库系统
在一个数据仓库/决策支持系统中，分区不仅是一个很强大的管理工具，还可以加快处理的速度。例如，你可能有一个大表，需 要在其中执行一个即席查询。你总是按销售定额（sales quarter）执行即席查询，因为每个销售定额包含数十万条记录，而你 有数百万条在线记录。因此，你想查询整个数据集中相当小的一部分，但是基于销售定额来索引不太可行。这个索引会指向数十 万条记录，以这种方式执行索引区间扫描会很糟糕. 处理许多查询时都要求执行一个全表扫描，但是最后却发现，一方面必须扫 描数百万条记录，但另一方面其中大多数记录并不适用于我们的查询。如果使用一种明智的分区机制，就可以按销售定额来聚集 数据，这样在查询某个给定销售定额的数据时，就可以只对这个销售定额的数据进行全面扫描。这在所有可能的解决方案中是 最佳的选择。
另外，在一个数据仓库/决策支持环境中，会频繁地使用并行查询。因此，诸如并行索引区间扫描或并行快速全面索引扫描等操 作不仅很有意义，而且对我们很有好处。我们希望充分地使用所有可用的资源，并行查询就提供了这样的一种途径。因此，在数 据仓库环境中，分区就意味着很有可能会加快处理速度。

表分区机制
目前Oracle中有4种对表分区的方法：
区间分区：可以指定应当存储在一起的数据区间。例如，时间戳在2005-06内的所有记录都存储在分区1中，时间戳在2005-06内 的所有记录都存储在分区2中，依此类推。这可能是Oracle中最常用的分区机制。
散列分区：是指在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。
列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放 在分区1中，STATUS值在（‘D’,’P’,’Q’）中的行放在分区2中，依此类推。
组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，你可以先对某些数据应用 区间分区，再在区间中根据散列或列表来选择最后的分区。

区间分区
区间分区表（range partitioned table）。下面的CREATE TABLE语句创建了一个使用RANGE_KEY_COLUMN列的区间分区表。 RANGE_KEY_COLUMN值严格小于2005-01-01的所有数据要放在分区PART_1中，RANGE_KEY_COLUMN值严格小于2006-01-01的所有数据 则放在分区PART_2中。不满足这两个条件的所有数据（例如，RANGE_KEY_COLUMN值为2007-01-01的行）将不能插入，因为它们无 法映射到任何分区：

sys@JINGYONG> create table range_example
  2  (range_key_column date,
  3  data varchar2(20)
  4  )
  5  partition by range(range_key_column)
  6  (partition part_1 values less than
  7  (to_date('2005-01-01','yyyy-mm-dd')),
  8  partition part_2 values less than
  9  (to_date('2006-01-01','yyyy-mm-dd'))
 10  )
 11  ;

表已创建。

sys@JINGYONG> insert into range_example values(to_date('2004-11-01','yyyy-mm-dd'
),'2004');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-11-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from range_example partition(part_1);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-04     2004

sys@JINGYONG> select * from range_example partition(part_2);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05     2005
01-1月 -05     2005

如果插入的日期超出上界会怎么样呢？答案是Oracle会产生一个错误：

sys@JINGYONG> insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'
),'2006');
insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'),'2006')
            *
第 1 行出现错误:
ORA-14400: inserted partition key does not map to any partition

假设你想像刚才一样，将2005年和2006年的日期分别聚集到各自的分区，但是另外你还希望将所有其他日期都归入第三个分区。 利用区间分区，这可以使用MAXVALUE子句做到这一点，如下所示：

sys@JINGYONG> CREATE TABLE range_example
2 ( range_key_column date ,
3 data varchar2(20)
4 )
5 PARTITION BY RANGE (range_key_column)
6 ( PARTITION part_1 VALUES LESS THAN
7 (to_date('01/01/2005','dd/mm/yyyy')),
8 PARTITION part_2 VALUES LESS THAN
9 (to_date('01/01/2006','dd/mm/yyyy'))
10 PARTITION part_3 VALUES LESS THAN
11 (MAXVALUE)
12 )
13 /
Table created.

现在，向这个表插入一个行时，这一行肯定会放入三个分区中的某一个分区中，而不会再拒绝任何行，因为分区PART_3可以接受 不能放在PART_1或PART_2中的任何RANG_KEY_COLUMN值（即使RANGE_KEY_COLUMN值为null，也会插入到这个新分区中）。

散列分区
对一个表执行散列分区（hash partitioning）时，Oracle会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪 一个分区中。Oracle建议N是2的一个幂（2、4、8、16等），从而得到最佳的总体分布.

散列分区如何工作
散列分区设计为能使数据很好地分布在多个不同设备（磁盘）上，或者只是将数据聚集到更可管理的块（chunk）上，为表选择 的散列键应当是惟一的一个列或一组列，或者至少有足够多的相异值，以便行能在多个分区上很好地（均匀地）分布。如果你选 择一个只有4个相异值的列，并使用两个分区，那么最后可能把所有行都散列到同一个分区上，这就有悖于分区的最初目标！
在这里，我们将创建一个有两个分区的散列表。在此使用名为HASH_KEY_COLUMN的列作为分区键。Oracle会取这个列中的值，并 计算它的散列值，从而确定这一行将存储在哪个分区中：

sys@JINGYONG> create table hash_example
  2  (hash_key_column date,
  3  data varchar2(20)
  4  )
  5  partition by hash(hash_key_column)
  6  (partition part_1 tablespace p1,
  7  partition part_2 tablespace p2
  8  )
  9  ;

表已创建。


sys@JINGYONG> insert into hash_example values(to_date('2004-01-01','yyyy-mm-dd'
),'2004');

已创建 1 行。

sys@JINGYONG> insert into hash_example values(to_date('2007-01-01','yyyy-mm-dd'
),'2007');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2006-01-01','yyyy-mm-dd'
),'2006');

已创建 1 行。

sys@JINGYONG> commit;

sys@JINGYONG> select * from hash_example partition(part_1);

HASH_KEY_COLUM DATA
-------------- --------------------
01-1月 -04     2004
01-1月 -07     2007

sys@JINGYONG> select * from hash_example partition(part_2);

HASH_KEY_COLUM DATA
-------------- --------------------
01-1月 -06     2006
01-1月 -05     2005

前面已经提到过，如果使用散列分区，你将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来 确定行会放在哪里。如果你由于某种原因希望将某个特定行放在分区PART_1中， 就不应该使用散列分区，实际上，此时也不能 使用散列分区。行会按散列函数的“指示”放在某个分区中，也就是说，散列函数说这一行该放在哪个分区，它就会放 在哪个 分区中。如果改变散列分区的个数，数据会在所有分区中重新分布（向一个散列分区表增加或删除一个分区时，将导致所有数据 都重写，因为现在每一行可能 属于一个不同的分区）。
如果你有一个大表,而且你想对它“分而治之”，此时散列分区最有用。你不用管理一个大表，而只是管理8或16个 较小的“表 ”。从某种程度上讲，散列分区对于提高可用性也很有用，临时丢掉一个散列分区，就能访问所有余下的分区。 也许有些用户 会受到影响，但是很有可能很多用户根本不受影响，但是很有可能很多用户根本不受影响。另外，恢复的单位现在也更小了。你 不用恢复一个完整的大表；而只需恢复表中的一小部分。

列表分区
列表分区（list partitioning）是Oracle9i Release 1的一个新特性。它提供了这样一种功能，可以根据离散的值列表来指定 一行位于哪个分区。如果能根据某个代码来进行分区（如州代码或区代码），这通常很有用。例如，你可能想把Maine州（ME） 、New Hampshire州（NH）、Vermont州（VT）和Massachusetts州（MA）中所有人的记录都归至一个分区中，因为这些州相互之 间挨得很近，而且你的应用按地理位置来查询数据。类似地，你可能希望将Connecticut州（CT）、Rhode Island州（RI）和New York州（NY）的数据分组在一起。对此不能使用区间分区，因为第一个分区的区间是ME到VT，第二个区间是CT到RI。这两个区间 有重叠。而且也不能使用散列分区，因为这样你就无法控制给定行要放到哪个分区中；而要由Oracle提供的内置散列函数来控制 。利用列表分区，我们可以很容易地完成这个定制分区机制：

sys@JINGYONG> create table list_example
  2  (state_cd varchar2(2),
  3  data varchar2(20)
  4  )
  5  partition by list(state_cd)
  6  (partition part_1 values('ME','NH','VT','MA'),
  7  partition part_2 values('CT','RI','NY')
  8  )
  9  ;

表已创建。

就像区间分区一样，如果我们想插入列表分区中未指定的一个值，Oracle会向客户应用返回一个合适的错误。换句话说，没有 DEFAULT分区的列表分区表会隐含地施加一个约束（非常像表上的一个检查约束）：

sys@JINGYONG> insert into list_example values('VA','data');
insert into list_example values('VA','data')
            *
第 1 行出现错误:
ORA-14400: inserted partition key does not map to any partition

如果想像前面一样把这个7个州分别聚集到各自的分区中，另外把其余的所有州代码放在第三个分区中（或者，实际上对于所插 入的任何其他行，如果STATE_CD列值不是以上7个州代码之一，就要放在第三个分区中），就可以使用VALUES（DEFAULT）子句。 在此，我们将修改表，增加这个分区（也可以在CREATE TABLE语句中使用这个子句）：

sys@JINGYONG> alter table list_example add partition part_3 values(default);

表已更改。

sys@JINGYONG> insert into list_example values('VA','data');

已创建 1 行。

sys@JINGYONG> select * from list_example partition(part_3);

ST DATA
-- --------------------
VA data

值列表中未显式列出的所有值都会放到这个（DEFAULT）分区中。关于DEFAULT的使用，有一点要注意：一旦列表分区表有一个 DEFAULT分区，就不能再向这个表中增加更多的分区了：

sys@JINGYONG> alter table list_example
  2  add partition part_4 values('CA','NM');
alter table list_example
            *
第 1 行出现错误:
ORA-14323: cannot add partition when DEFAULT partition exists

此时必须删除DEFAULT分区，然后增加PART_4，最后再加回DEFAULT分区。原因在于，原来DEFAULT分区可以有列表分区键值为CA 或NM的行，但增加PART_4之后，这些行将不再属于DEFAULT分区。

组合分区
最后我们会看到组合分区（composite partitioning）的一些例子，组合分区是区间分区和散列分区的组合，或者是区间分区与 列表分区的组合。
在组合分区中，顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区（在Oracle9i Release 1及以前的版本 中，只支持散列子分区，而没有列表分区）。有意思的是，使用组合分区时，并没有分区段，而只有子分区段。分区本身并没有 段（这就类似于分区表没有段）。数据物理的存储在子分区段上，分区成为一个逻辑容器，或者是一个指向实际子分区的容器。
在下面的例子中，我们将查看一个区间-散列组合分区。在此对区间分区使用的列集不同于散列分区使用的列集。并不是非得如 此，这两层分区也可以使用同样的列集：

sys@JINGYONG> create table composite_example
  2    (range_key_column date,
  3     hash_key_column int,
  4     data varchar2(20)
  5    )
  6    partition by range(range_key_column)
  7     subpartition by hash(hash_key_column) subpartitions 2
  8    (
  9      partition part_1
 10      values less than(to_date('2005-01-01','yyyy-mm-dd'))
 11      (subpartition part_1_sub_1,
 12       subpartition part_1_sub_2
 13      ),
 14      partition part_2
 15      values less than(to_date('2006-01-01','yyyy-mm-dd'))
 16      (
 17       subpartition part_2_sub_1,
 18       subpartition part_2_sub_2
 19      )
 20     );

表已创建。

在区间-散列组合分区中，Oracle首先会应用区间分区规则，得出数据属于哪个区间。然后再应用散列函数，来确定数据最后要 放在哪个物理分区中

sys@JINGYONG> insert into composite_example values(to_date('2004-12-01','yyyy-mm
-dd'),2004,'2004');

已创建 1 行。

sys@JINGYONG> insert into composite_example values(to_date('2005-01-01','yyyy-mm
-dd'),2005,'2005');

已创建 1 行。



sys@JINGYONG> insert into composite_example values(to_date('2004-01-01','yyyy-mm
-dd'),2006,'2004');

已创建 1 行。

sys@JINGYONG> insert into composite_example values(to_date('2005-11-01','yyyy-mm
-dd'),2006,'2004');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。



sys@JINGYONG> select * from composite_example partition(part_1);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-12月-04                2004 2004
01-1月 -04                2006 2004

sys@JINGYONG> select * from composite_example subpartition(part_1_sub_1);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-12月-04                2004 2004

sys@JINGYONG> select * from composite_example subpartition(part_1_sub_2);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04                2006 2004


sys@JINGYONG> select * from composite_example partition(part_2);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -05                2005 2005
01-11月-05                2006 2004

sys@JINGYONG> select * from composite_example subpartition(part_2_sub_1);

未选定行

sys@JINGYONG> select * from composite_example subpartition(part_2_sub_2);

RANGE_KEY_COLU HASH_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -05                2005 2005
01-11月-05                2006 2004

因此，利用组合分区，你就能把数据先按区间分解，如果认为某个给定的区间还太大，或者认为有必要做进一步的分区消除，可 以再利用散列或列表将其再做分解。有意思的是，每个区间分区不需要有相同数目的子分区；例如，假设你在对一个日期列完成 区间分区，以支持数据净化（快速而且容易地删除所有就数据）。在2004年，CODE_KEY_COLUMN值为“奇数”的数据量与 CODE_KEY_COLUMN值为“偶数”的数据量是相等的。但是到了2005年，你发现与奇数吗相关的记录数是偶数吗相关的记录数的两 倍，所以你希望对应奇数码有更多的子分区。只需定义更多的子分区，就能相当容易地做到这一点：

sys@JINGYONG> create table composite_range_list_example
  2  (range_key_column date,
  3  code_key_column int,
  4  data varchar2(20)
  5  )
  6  partition by range(range_key_column)
  7  subpartition by list(code_key_column)
  8  (
  9   partition part_1
 10    values less than(to_date('2005-01-01','yyyy-mm-dd'))
 11    (subpartition part_1_sub_1 values(1,3,5,7),
 12     subpartition part_1_sub_2 values(2,4,6,8)
 13    ),
 14   partition part_2
 15    values less than(to_date('2006-01-01','yyyy-mm-dd'))
 16    (subpartition part_2_sub_1 values(1,3),
 17     subpartition part_2_sub_2 values(5,7),
 18     subpartition part_2_sub_3 values(2,4,6,8)
 19    )
 20  );

表已创建。
在此，最后总共有5个分区：分区PART_1有两个子分区，分区PART_2有3个子分区。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2004-01-0
1','yyyy-mm-dd'),1,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2004-11-0
1','yyyy-mm-dd'),2,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),2,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),1,'data');

已创建 1 行。

sys@JINGYONG> insert into composite_range_list_example values(to_date('2005-11-0
1','yyyy-mm-dd'),7,'data');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from composite_range_list_example partition(part_1);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04                   1 data
01-11月-04                   2 data

sys@JINGYONG> select * from composite_range_list_example partition(part_2);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   1 data
01-11月-05                   7 data
01-11月-05                   2 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_1_sub
_1);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-1月 -04                   1 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_1_sub
_2);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-04                   2 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_1);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   1 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_2);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   7 data

sys@JINGYONG> select * from composite_range_list_example subpartition(part_2_sub
_3);

RANGE_KEY_COLU CODE_KEY_COLUMN DATA
-------------- --------------- --------------------
01-11月-05                   2 data

行移动
你可能想知道，在前面所述的各种分区机制中，如果用于确定分区的列有修改会发生什么。需要考虑两种情况：
修改不会导致使用一个不同的分区；行仍属于原来的分区。这在所有情况下都得到支持。
修改会导致行跨分区移动。只有当表启用了行移动时才支持这种情况；否则，会产生一个错误。
这些行为很容易观察。在前面的例子中，我们向RANGE_EXAMPLE表的PART_1插入了三行：

sys@JINGYONG> insert into range_example values(to_date('2004-11-01','yyyy-mm-dd'
),'2004');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-11-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> insert into range_example values(to_date('2005-01-01','yyyy-mm-dd'
),'2005');

已创建 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from range_example partition(part_1);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-04     2004

sys@JINGYONG> select * from range_example partition(part_2);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05     2005
01-1月 -05     2005

取其中一行，并更新其RANGE_KEY_COLUMN值，不过更新后它还能放在PART_1中：
sys@JINGYONG> update range_example
  2  set range_key_column=trunc(range_key_column)
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');

已更新 1 行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> select * from range_example partition(part_2);

RANGE_KEY_COLU DATA
-------------- --------------------
01-11月-05     2005
01-1月 -05     2005

不出所料，这会成功：行仍在分区PART_2中。接下来，再把RANGE_KEY_COLUMN更新为另一个值，但这次更新后的值将导致它属于 分区PART_1：

sys@JINGYONG> update range_example
  2  set range_key_column=to_date('2004-12-01','yyyy-mm-dd')
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');
update range_example
       *
第 1 行出现错误:
ORA-14402: updating partition key column would cause a partition change

这会立即产生一个错误，因为我们没有显式地启用行移动。在Oracle8i及以后的版本中，可以在这个表上启用行移动（row movement），以允许从一个分区移动到另一个分区。注意 Oracle 8.0中没有行移动功能；在这个版本中，你必须先删除行，再 重新将其插入。不过，要注意这样做有一个小小的副作用；行的ROWID会由于更新而改变：

sys@JINGYONG> select rowid
  2  from range_example
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');

ROWID
------------------
AAAM++AABAAAPAqAAB

sys@JINGYONG> alter table range_example enable row movement;

表已更改。

sys@JINGYONG> update range_example
  2  set range_key_column=to_date('2004-12-01','yyyy-mm-dd')
  3  where range_key_column=to_date('2005-01-01','yyyy-mm-dd');

已更新 1 行。

sys@JINGYONG> select rowid
  2  from range_example
  3  where range_key_column=to_date('2004-12-01','yyyy-mm-dd');

ROWID
------------------
AAAM+9AABAAAPAiAAB

既然知道执行这个更新时行的ROWID会改变，所以要启用行移动，这样才允许更新分区键。
注意 在其他一些情况下，ROWID也有可能因为更新而改变。更新IOT的主键可能导致ROWID改变，该行的通用ROWID（UROWID）也 会改变。Oracle 10g的FLASHBACK TABLE命令可能改变行的ROWID，此外Oracle 10g的ALTER TABLE SHRINK命令也可能使行的 ROWID改变。

要知道，执行行移动时，实际上在内部就好像先删除了这一行，然后再将其重新插入。这会更新这个表上的索引，删除旧的索引 条目，再插入一个新条目。此时会完成DELETE再加一个INSERT的相应物理工作。不过，尽管在此执行了行的物理删除和插入，在 Oracle看来却还是一个更新，因此，不会导致INSERT和DELETE触发器触发，只有UPDATE触发器会触发。另外，由于外键约束可能 不允许DELETE的子表也不会触发DELETE触发器。不过，还是要对将完成的额外工作有所准备；行移动的开销比正常的UPDATE昂贵 得多。

表分区机制小结
一般来讲，如果将数据按某个（某些）值逻辑聚集，区间分区就很有用。基于时间的数据就是这方面经典的例子，如按“销售定 额”、“财政年度”或“月份”分区。在许多情况下，区间分区都能利用分区消除，这包括使用完全相等性和区间（小于、大于 、介于…之间等）。

如果不能按自然的区间进行分区，散列分区就很合适。例如，如果必须加载一个表，其中装满与人口普查相关的数据，可能无 法找到一个合适的属性来按这个属性完成区间分区。不过，你可能还是想得到分区提供的管理、性能和可用性提升等诸多好处 。在此，只需选择惟一的一个列或几乎惟一的一个列集，对其计算散列。这样一 来，无论有多少个分区，都能得到均匀的数据 分布。使用完全相等性或IN（value,value,…）时，散列分区对象可以利用分区消除，但是使用数据区间时，散列分区则无法利 用分区消除。

如果数据中有一列有一组离散值，而且根据应用使用这一列的方式来看，按这一列进行分区很有意义（例如，这样一来，查询 中可以轻松地利用分区消除），这种数据 就很适合采用列表分区。列表分区的经典例子包括按州或区域代码分区，实际上，一 般来讲许多“代码”性属性都很适合应用列表分区。

如果某些数据逻辑上可以进行区间分区，但是得到的区间分区还是太小，不能有效地管理，就可以使用组合分区。可以先应用区 间分区，再进一步划分各个区间，按一个散列函数或使用列表来分区。这样就能将I/O请求分布到任何给定大分区中的多个磁盘 上。另外，现在可以得到3个层次的分区消除。如果在区间分区键上查询，Oracle就能消除任何不满足条件的区间分区。如果向 查询增加散列或列表键，Oracle可以消除该区间中其他的散列或列表分区。如果只是在散列或列表键上查询（而不使用区间分区 键），Oracle就只会查询各个区间分区中的这些散列或列表子分区。

如果可以按某个属性自然地对数据完成区间分区，就应该使用区间分区，而不是散列分区或列表分区。散列和列表分 区能提供分区的许多突出优点，但是在分区消除方面都不如区间分区有用。如果所得到的区间分区太大，不能很好地管理；或者 如果你想使用所有PDML功能或对一个区间分区使用并行索引扫描，则建议在区间分区中再使用散列或列表分区。

参考:Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions

同事修改存储过程trans_kc21时过程无法被编译被卡死
查V$DB_OBJECT_CACHE

SQL> SELECT * FROM V$DB_OBJECT_CACHE WHERE name=upper('trans_kc21') AND LOCKS!='0';

OWNER                                                            NAME                                                                             DB_LINK                                                          NAMESPACE                                                        TYPE                                                             SHARABLE_MEM      LOADS EXECUTIONS      LOCKS       PINS KEPT CHILD_LATCH INVALIDATIONS HASH_VALUE LOCK_MODE PIN_MODE  STATUS              TIMESTAMP                              PREVIOUS_TIMESTAMP                     LOCKED_TOTAL PINNED_TOTAL PROPERTY                                                                         FULL_HASH_VALUE
---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ ---------- ---------- ---------- ---------- ---- ----------- ------------- ---------- --------- --------- ------------------- -------------------------------------- -------------------------------------- ------------ ------------ -------------------------------------------------------------------------------- --------------------------------
SICP3_GZYB                                                       TRANS_KC21                                                                                                                                    TABLE/PROCEDURE                                                  PROCEDURE                                                              341712         34          1          1          1 NO         15307             0 1839741899 NULL      SHARED    VALID               2013-09-26/15:48:21                    2013-09-26/15:46:17                              47           68                                                                                  356a3b5c43dd126d3e52acbe6da83bcb

发现locks＝1

按对象查出sid的值
v$access显示目前库缓存对象被锁定的信息,这个被强加的锁用来确保库缓存对象
在sql执行请求它时不会被老化

SQL> select SID from V$ACCESS WHERE object=upper('trans_kc21');

       SID
----------
      6566

查看session 状态

SQL> select a.SID,a.SERIAL#,b.SPID from v$session a,v$process b where a.SID=6566 and a.PADDR=b.ADDR;

       SID    SERIAL# SPID
---------- ---------- ------------------------
      6566         27 23462626

kill这个会话

SQL>alter system kill session '6566,27';

如果这个会话已经是被kill的

SQL> select a.SID,a.SERIAL#,b.SPID,a.STATUS from v$session a,v$process b where a.SID=6566 and a.PADDR=b.ADDR;

       SID    SERIAL# SPID                     STATUS
---------- ---------- ------------------------ --------
      6566         27 23462626                 KILLED

就要在操作系统层来kill

[/@zzld03]#kill -9 23462626

rac中一条查询语句并行到各个节点执行没完成被中断后被限制到特定节点上执行时查询时查询语句执行不了.
客户情况是四个节点的11g rac,五个险种在做数据转换,由于一个险种的有些数据转换脚本的查询使用了并行执行被并行到别的节点上,而其它险种发现后将并行进程kill了.后面该险种的被kill掉的查询语句在特定节点上执行时当查询特定月份的数据时会卡住.产生的等待事件是gc cr request.

global cache cr request
当一个进程访问需要一个或者多个块时，它会首先检查自己的CACHE是否存在该块，如果发现没有，就会先通过global cache赋予这些块共享访问的权限，然后再访问。假如，通过global cache 发现这些块已经在另一个实例的CACHE里面，那么这些块就会通过CACHE FUSION,在节点之间直接传递，同时出现global cache cr request等待事件
注意：在10G以后，global cache cr request 已经简称为 gc cr request

查询语句如下:

            SELECT /*+ INDEX(MBF,IDX_MT_BIZ_FIN_DATE)USE_NL(MBF,MFF) */
                   mff.hospital_id AKB020,    --医疗机构编号
                   mff.serial_no AAZ218,    --业务序列号
                   mff.serial_fee AAZ213,    --费用序列号
                   mff.stat_type AKA063,    --统计类别
                   mff.fee_batch PKA001,    --费用批次
                   decode(mff.medi_item_type,0,'2',1,'1',2,'4',3,'5') AKE003,    --项目药品类型(0:项目，1：西药，2：中成药，3：中草药)
                   mff.item_code AKE001,    --中心药品项目编码
                   mff.item_name AKE002,    --中心药品项目名称
                   mff.his_item_code AKE005,    --医院药品项目编码
                   mff.his_item_name AKE006,    --医院药品项目名称
                   mff.serial_apply AAZ267,    --申请序列号
                   mff.fee_date PKA051,    --费用发生时间
                   mff.model PKA052,    --剂型
                   mff.factory PKA053,    --厂家
                   mff.standard PKA054,    --规格
                   mff.unit PKA055,    --计量单位
                   mff.price PKA056,    --单价
                   mff.dosage PKA057,    --用量
                   mff.money PKA058,    --金额
                   mff.reduce_money PKA059,    --冲减金额（主要为计算方便使用）
                   mff.usage_flag PKA060,    --使用标志（1：出院带药 2：抢救用药 3：急诊）
                   mff.usage_days PKA061,    --出院带药天数
                   mff.opp_serial_fee PKA062,    --对应费用序列号
                   mff.input_staff PKA063,    --录入人工号
                   mff.input_man PKA064,    --录入人
                   mff.input_date PKA065,    --录入时间
                   mff.calc_flag PKA066,    --计算标志
                   mff.frozen_flag PKA067,    --费用冻结标志，用来表识参保人所在单位的基本医疗保险被冻结期间录入的费用。0：未冻结；1：已冻结；2：冻结已处理
                   mff.frozen_serial_fee PKA068,    --对应冻结的费用序列号
                   mff.trans_date PKA069,    --费用上传时间
                   mff.recipe_no PKA070,    --处方号
                   mff.hos_serial PKA071,    --对应医院费用号
                   mff.recipe_hospital_id PKA072,    --处方医院编号
                   mff.recipe_hospital_name PKA073,    --处方医院名称
                   mff.doctor_no PKA074,    --处方医生编号
                   mff.doctor_name PKA075,    --处方医生姓名
                   mff.audit_flag PKA076,    --审核标志
                   mff.trans_flag PKA044,    --传输标志(0:未传输 1:已成功传输 2:未成功传输）
                   mff.valid_flag AAE100  --有效标志
              FROM gzyb.mt_biz_fin/*@gzybcx_link*/ mbf,
                   gzyb.mt_fee_fin/*@gzybcx_link*/ mff
             WHERE mbf.hospital_id = '004003'
               AND mbf.fin_date >= to_date('20130101', 'yyyy-mm-dd')
               AND mbf.fin_date  < to_date('20130201', 'yyyy-mm-dd')
               AND mbf.valid_flag = '1'
               AND mbf.hospital_id = mff.hospital_id
               AND mbf.serial_no = mff.serial_no;

在指定的特定节点执行上面的语句会产生gc cr request,因为之前被分发到别的节点上执行,该查询的部分数据被缓存到其它的节点上了.而现在不能并行在指定了特定实例来运行上面的查询，所以查询不能访问其它节点实例的sga,因为我的机器名是JINGYONG.从下面的查询可以看到上面的语句在没加parallel提示时该语句是在特定节点prddb02执行的.

      SID    SERIAL# USERNAME       PROGRAM                  MACHINE                  
---------- ---------- -------------  -----------------------  ------------------------
      6345       1387 SICP3_GZYB     oracle@prddb02 (J001)    prddb02                 
      6454        371 SICP3_GZYB     oracle@prddb02           WORKGROUP\POWERSI-HP    
      2508       1513 SICP3_GZYB     oracle@prddb02 (J000)    prddb02                 
      7865       234  SICP3_GZYB     oracle@prddb02 (J002)    WORKGROUP\JINGYONG

sid=7865,serial#=234就上面执行的sql语句.

当给上面的sql语句加上并行parallel提示

            SELECT /*+ INDEX(MBF,IDX_MT_BIZ_FIN_DATE)USE_NL(MBF,MFF) parallel(mff,1) */
                   mff.hospital_id AKB020,    --医疗机构编号
                   mff.serial_no AAZ218,    --业务序列号
                   mff.serial_fee AAZ213,    --费用序列号
                   mff.stat_type AKA063,    --统计类别
                   mff.fee_batch PKA001,    --费用批次
                   decode(mff.medi_item_type,0,'2',1,'1',2,'4',3,'5') AKE003,    --项目药品类型(0:项目，1：西药，2：中成药，3：中草药)
                   mff.item_code AKE001,    --中心药品项目编码
                   mff.item_name AKE002,    --中心药品项目名称
                   mff.his_item_code AKE005,    --医院药品项目编码
                   mff.his_item_name AKE006,    --医院药品项目名称
                   mff.serial_apply AAZ267,    --申请序列号
                   mff.fee_date PKA051,    --费用发生时间
                   mff.model PKA052,    --剂型
                   mff.factory PKA053,    --厂家
                   mff.standard PKA054,    --规格
                   mff.unit PKA055,    --计量单位
                   mff.price PKA056,    --单价
                   mff.dosage PKA057,    --用量
                   mff.money PKA058,    --金额
                   mff.reduce_money PKA059,    --冲减金额（主要为计算方便使用）
                   mff.usage_flag PKA060,    --使用标志（1：出院带药 2：抢救用药 3：急诊）
                   mff.usage_days PKA061,    --出院带药天数
                   mff.opp_serial_fee PKA062,    --对应费用序列号
                   mff.input_staff PKA063,    --录入人工号
                   mff.input_man PKA064,    --录入人
                   mff.input_date PKA065,    --录入时间
                   mff.calc_flag PKA066,    --计算标志
                   mff.frozen_flag PKA067,    --费用冻结标志，用来表识参保人所在单位的基本医疗保险被冻结期间录入的费用。0：未冻结；1：已冻结；2：冻结已处理
                   mff.frozen_serial_fee PKA068,    --对应冻结的费用序列号
                   mff.trans_date PKA069,    --费用上传时间
                   mff.recipe_no PKA070,    --处方号
                   mff.hos_serial PKA071,    --对应医院费用号
                   mff.recipe_hospital_id PKA072,    --处方医院编号
                   mff.recipe_hospital_name PKA073,    --处方医院名称
                   mff.doctor_no PKA074,    --处方医生编号
                   mff.doctor_name PKA075,    --处方医生姓名
                   mff.audit_flag PKA076,    --审核标志
                   mff.trans_flag PKA044,    --传输标志(0:未传输 1:已成功传输 2:未成功传输）
                   mff.valid_flag AAE100  --有效标志
              FROM gzyb.mt_biz_fin/*@gzybcx_link*/ mbf,
                   gzyb.mt_fee_fin/*@gzybcx_link*/ mff
             WHERE mbf.hospital_id = '004003'
               AND mbf.fin_date >= to_date('20130101', 'yyyy-mm-dd')
               AND mbf.fin_date  < to_date('20130201', 'yyyy-mm-dd')
               AND mbf.valid_flag = '1'
               AND mbf.hospital_id = mff.hospital_id
               AND mbf.serial_no = mff.serial_no;

这时查询虽然指定了运行的特定实例还是被并行到其它节点上执行了从而能访问其它实例sga所以查询很快就能查询出来
从下面的查询可以看到当加并行parallel后上面的sql虽然是在特定实例执行但却被并行到其它节点执行了

       SID    SERIAL# USERNAME       PROGRAM                  MACHINE                  
---------- ---------- -------------  -----------------------  ------------------------
      6345       1387 SICP3_GZYB     oracle@prddb02 (J001)    prddb02                 
      6454        371 SICP3_GZYB     oracle@prddb02           WORKGROUP\POWERSI-HP    
      2508       1513 SICP3_GZYB     oracle@prddb02 (J000)    prddb02  

因此上面的sql在特定实例执行卡住的原因找到了.

要想让并行进程只在单节点上执行可以设置parallel_force_local参数为true

SQL> show parameter force

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local                 boolean     TRUE

如果想要让job也在指定的节点上运行而不被并行到其它节点上执行在创建job时要指定instance参数,将instance参数指定为你要使用的节点

rac中各节点监听程序本来只监听当前节点的vip 和public ip,不知为何客户的rac各个节点配置成监听自己节点和对方节点的vip
连接rac数据库报TNS-12545错误
登录数据库服务器执行crs_stat -t

[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi1
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    OFFLINE
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    OFFLINE
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2

检查listener.ora文件:10.53.1.237和10.53.1.238是两个节点
的虚拟IP地址

[oracle@keqsi2 admin]$ more listener.ora
# listener.ora.keqsi2 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi2
# Generated by Oracle configuration tools.

LISTENER_KEQSI2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.237)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

检查网络是否能拼通

[oracle@keqsi2 admin]$ hostname
keqsi2
[oracle@keqsi2 admin]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1       keqsi2  localhost.localdomain   localhost
127.0.0.1       localhost.localdomain    localhost
172.18.20.1     keqsi1-priv
172.18.20.2     keqsi2-priv
10.53.1.230     keqsi1
10.53.1.237     keqsi1-vip
10.53.1.231     keqsi2
10.53.1.238     keqsi2-vip
[oracle@keqsi2 admin]$ ping keqsi1
PING keqsi1 (10.53.1.230) 56(84) bytes of data.
64 bytes from keqsi1 (10.53.1.230): icmp_seq=0 ttl=64 time=0.131 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=1 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=2 ttl=64 time=0.128 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=3 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=4 ttl=64 time=0.126 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=5 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=6 ttl=64 time=0.126 ms

--- keqsi1 ping statistics ---
7 packets transmitted, 7 received, 0% packet loss, time 5999ms
rtt min/avg/max/mdev = 0.125/0.126/0.131/0.012 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi2
PING keqsi2 (10.53.1.231) 56(84) bytes of data.
64 bytes from keqsi2 (10.53.1.231): icmp_seq=0 ttl=64 time=0.024 ms
64 bytes from keqsi2 (10.53.1.231): icmp_seq=1 ttl=64 time=0.010 ms
64 bytes from keqsi2 (10.53.1.231): icmp_seq=2 ttl=64 time=0.015 ms

--- keqsi2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 1999ms
rtt min/avg/max/mdev = 0.010/0.016/0.024/0.006 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi2-vip
PING keqsi2-vip (10.53.1.238) 56(84) bytes of data.
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=0 ttl=64 time=0.018 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=1 ttl=64 time=0.011 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=2 ttl=64 time=0.010 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=3 ttl=64 time=0.009 ms

--- keqsi2-vip ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 2999ms
rtt min/avg/max/mdev = 0.009/0.012/0.018/0.003 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi1-vip
PING keqsi1-vip (10.53.1.237) 56(84) bytes of data.
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=0 ttl=64 time=0.135 ms
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=1 ttl=64 time=0.129 ms
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=2 ttl=64 time=0.121 ms

--- keqsi1-vip ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2000ms
rtt min/avg/max/mdev = 0.121/0.128/0.135/0.010 ms, pipe 2

经上面的命令检查网络没有问题

下面手功启动listener还是报TNS-12545

[oracle@keqsi2 admin]$ srvctl start listener -n keqsi2
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:26:14
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Copyright (c) 1991, 2007, Oracle.  All rights reserved.
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNSLSNR for Linux: Version 10.2.0.4.0 - Production
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12545: Connect failed because target host or object does not exist
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00515: Connect failed because target host or object does not exist
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 99: Cannot assign requested address
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Listener failed to start. See the error message(s) above...
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:26:14
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Copyright (c) 1991, 2007, Oracle.  All rights reserved.
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12541: TNS:no listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00511: No listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 111: Connection refused
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.238)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12541: TNS:no listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00511: No listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 111: Connection refused
CRS-0215: Could not start resource 'ora.keqsi2.LISTENER_KEQSI2.lsnr'.

下面让各个节点的监听程序只监听自己节点的vip

[oracle@keqsi2 admin]$ vi listener.ora
   
# listener.ora.keqsi2 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi2
# Generated by Oracle configuration tools.

LISTENER_KEQSI2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"listener.ora" 19L, 474C written

[oracle@keqsi1 admin]$ vi listener.ora

# listener.ora.keqsi1 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi1
# Generated by Oracle configuration tools.

LISTENER_KEQSI1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.53.1.237)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"listener.ora" 19L, 473C written

启动监听程序正常

[oracle@keqsi2 admin]$ lsnrctl start LISTENER_KEQSI2

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:42:17

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.238)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.238)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_KEQSI2
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                28-AUG-2013 08:42:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/10gR2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.238)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@keqsi1 admin]$ lsnrctl start LISTENER_KEQSI1

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:37:08

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi1.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.237)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_KEQSI1
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                28-AUG-2013 08:37:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/10gR2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/10gR2/db/network/log/listener_keqsi1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.237)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi1
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    ONLINE    keqsi1
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    ONLINE    keqsi2
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2

然后再重新启动rac数据库能正常启动

[oracle@keqsi1 admin]$ crs_stop -all
Attempting to stop `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1`
Attempting to stop `ora.keqsi1.gsd` on member `keqsi1`
Stop of `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1` succeeded.
Attempting to stop `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2`
Attempting to stop `ora.keqjm.db` on member `keqsi1`
Attempting to stop `ora.keqsi1.ons` on member `keqsi1`
Attempting to stop `ora.keqjm.orcl.cs` on member `keqsi2`
Attempting to stop `ora.keqsi2.gsd` on member `keqsi2`
Attempting to stop `ora.keqsi2.ons` on member `keqsi2`
Stop of `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2` succeeded.
Stop of `ora.keqsi1.gsd` on member `keqsi1` succeeded.
Stop of `ora.keqsi1.ons` on member `keqsi1` succeeded.
Stop of `ora.keqsi2.gsd` on member `keqsi2` succeeded.
Stop of `ora.keqsi2.ons` on member `keqsi2` succeeded.
Stop of `ora.keqjm.orcl.cs` on member `keqsi2` succeeded.
Stop of `ora.keqjm.db` on member `keqsi1` succeeded.
`ora.keqjm.keqjm1.inst` is already OFFLINE.
`ora.keqjm.keqjm2.inst` is already OFFLINE.
Attempting to stop `ora.keqsi1.ASM1.asm` on member `keqsi1`
Attempting to stop `ora.keqsi2.ASM2.asm` on member `keqsi2`
Attempting to stop `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1`
Attempting to stop `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2`
Stop of `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2` succeeded.
Attempting to stop `ora.keqsi2.vip` on member `keqsi2`
Stop of `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1` succeeded.
Attempting to stop `ora.keqsi1.vip` on member `keqsi1`
Stop of `ora.keqsi2.vip` on member `keqsi2` succeeded.
Stop of `ora.keqsi1.ASM1.asm` on member `keqsi1` succeeded.
Stop of `ora.keqsi1.vip` on member `keqsi1` succeeded.
Stop of `ora.keqsi2.ASM2.asm` on member `keqsi2` succeeded.
CRS-0216: Could not stop resource 'ora.keqjm.keqjm1.inst'.

CRS-0216: Could not stop resource 'ora.keqjm.keqjm2.inst'.

[oracle@keqsi1 admin]$ crs_start -all
Attempting to start `ora.keqsi1.ASM1.asm` on member `keqsi1`
Attempting to start `ora.keqsi1.vip` on member `keqsi1`
Attempting to start `ora.keqsi2.ASM2.asm` on member `keqsi2`
Attempting to start `ora.keqsi2.vip` on member `keqsi2`
Start of `ora.keqsi1.vip` on member `keqsi1` succeeded.
Attempting to start `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1`
Start of `ora.keqsi2.vip` on member `keqsi2` succeeded.
Attempting to start `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2`
Start of `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1` succeeded.
Start of `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2` succeeded.
Start of `ora.keqsi1.ASM1.asm` on member `keqsi1` succeeded.
Attempting to start `ora.keqjm.keqjm1.inst` on member `keqsi1`
Start of `ora.keqsi2.ASM2.asm` on member `keqsi2` succeeded.
Attempting to start `ora.keqjm.keqjm2.inst` on member `keqsi2`
Start of `ora.keqjm.keqjm1.inst` on member `keqsi1` succeeded.
Start of `ora.keqjm.keqjm2.inst` on member `keqsi2` succeeded.
CRS-1002: Resource 'ora.keqsi1.ons' is already running on member 'keqsi1'

CRS-1002: Resource 'ora.keqsi2.ons' is already running on member 'keqsi2'

CRS-1002: Resource 'ora.keqjm.db' is already running on member 'keqsi2'

Attempting to start `ora.keqjm.orcl.cs` on member `keqsi2`
Attempting to start `ora.keqsi1.gsd` on member `keqsi1`
Attempting to start `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1`
Attempting to start `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2`
Attempting to start `ora.keqsi2.gsd` on member `keqsi2`
Start of `ora.keqjm.orcl.cs` on member `keqsi2` succeeded.
Start of `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1` succeeded.
Start of `ora.keqsi1.gsd` on member `keqsi1` succeeded.
Start of `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2` succeeded.
Start of `ora.keqsi2.gsd` on member `keqsi2` succeeded.
CRS-0223: Resource 'ora.keqjm.db' has placement error.

CRS-0223: Resource 'ora.keqsi1.ons' has placement error.

CRS-0223: Resource 'ora.keqsi2.ons' has placement error.

[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi2
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    ONLINE    keqsi1
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    ONLINE    keqsi2
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2
[oracle@keqsi1 admin]$

修改数据库中的初始化参数LOCAL_LISTENER和REMOTE_LISTENER

oracle@keqsi1 admin ]$ sqlplus  / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:26:39 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.237)(PORT = 1521))'
3 SID = 'keqsi1';

系统已更改。


SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521))'
3 SID = 'keqsi2';

系统已更改。
SQL> ALTER SYSTEM
2 SET REMOTE_LISTENER = 'LISTENERS_KEQJM'
3 SID = '*';
系统已更改。

其中’LISTENERS_KEQJM’对应于tnsnames.ora中的LISTENERS_KEQJM
连接串,该参数用于rac的负载均衡

[oracle@keqsi1 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

KEQJM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
    )
  )

KEQJM2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
      (INSTANCE_NAME = keqjm2)
    )
  )

KEQJM1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
      (INSTANCE_NAME = keqjm1)
    )
  )

LISTENERS_KEQJM =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

下面是测看各个节点只监听自己的vip地址时通过remote_listener能不能实现
rac的负载均衡
在本机测试

[oracle@test admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:31:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn test/test@keqsi
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
keqjm1

SQL>exit

[oracle@test admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:31:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn test/test@keqsi
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
keqjm2

经测试rac是可以负载均衡的

SHOW_SPACE过程
SHOW_SPACE例程用于打印数据库段空间利用率信息:

sys@DEVELOP> create or replace procedure show_space
  2  ( p_segname in varchar2,
  3    p_owner in varchar2 default user,
  4    p_type in varchar2 default 'TABLE',
  5    p_partition in varchar2 default NULL )
  6  -- this procedure uses authid current user so it can query DBA_*
  7  -- views using privileges from a ROLE, and so it can be installed
  8  -- once per database, instead of once per user who wanted to use it
  9    authid current_user
 10  as
 11    l_free_blks number;
 12    l_total_blocks number;
 13    l_total_bytes number;
 14    l_unused_blocks number;
 15    l_unused_bytes number;
 16    l_LastUsedExtFileId number;
 17    l_LastUsedExtBlockId number;
 18    l_LAST_USED_BLOCK number;
 19    l_segment_space_mgmt varchar2(255);
 20    l_unformatted_blocks number;
 21    l_unformatted_bytes number;
 22    l_fs1_blocks number; l_fs1_bytes number;
 23    l_fs2_blocks number; l_fs2_bytes number;
 24    l_fs3_blocks number; l_fs3_bytes number;
 25    l_fs4_blocks number; l_fs4_bytes number;
 26    l_full_blocks number; l_full_bytes number;
 27  -- inline procedure to print out numbers nicely formatted
 28  -- with a simple label
 29  procedure p( p_label in varchar2, p_num in number )
 30  is
 31  begin
 32    dbms_output.put_line( rpad(p_label,40,'.') ||
 33    to_char(p_num,'999,999,999,999') );
 34  end;
 35  begin
 36  -- this query is executed dynamically in order to allow this procedure
 37  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
 38  -- via a role as is customary.
 39  -- NOTE: at runtime, the invoker MUST have access to these two
-- views!
 40   41  -- this query determines if the object is an ASSM object or not
 42    begin
 43      execute immediate
 44      'select ts.segment_space_management
 45      from dba_segments seg, dba_tablespaces ts
 46      where seg.segment_name = :p_segname
    and (:p_partition is null or
 47   48      seg.partition_name = :p_partition)
 49      and seg.owner = :p_owner
 50      and seg.tablespace_name = ts.tablespace_name'
 51      into l_segment_space_mgmt
 52      using p_segname, p_partition, p_partition, p_owner;
 53    exception
 54    when too_many_rows then
 55      dbms_output.put_line
 56        ( 'This must be a partitioned table, use p_partition => ');
 57      return;
 58    end;
 59  -- if the object is in an ASSM tablespace, we must use this API
 60  -- call to get space information, otherwise we use the FREE_BLOCKS
 61  -- API for the user-managed segments
 62    if l_segment_space_mgmt = 'AUTO'   then
 63       dbms_space.space_usage
 64       ( p_owner, p_segname, p_type, l_unformatted_blocks,
 65       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 66       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 67       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
 68       p( 'Unformatted Blocks ', l_unformatted_blocks );
 69       p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
 70       p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
 71       p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
 72       p( 'FS4 Blocks (75-100)', l_fs4_blocks );
 73       p( 'Full Blocks ', l_full_blocks );
 74    else
 75       dbms_space.free_blocks(
 76       segment_owner => p_owner,
 77       segment_name => p_segname,
 78       segment_type => p_type,
 79       freelist_group_id => 0,
 80       free_blks => l_free_blks);
 81       p( 'Free Blocks', l_free_blks );
 82    end if;
 83  -- and then the unused space API call to get the rest of the
 84  -- information
 85    dbms_space.unused_space
 86     ( segment_owner => p_owner,
 87       segment_name => p_segname,
 88       segment_type => p_type,
 89       partition_name => p_partition,
 90       total_blocks => l_total_blocks,
 91       total_bytes => l_total_bytes,
 92       unused_blocks => l_unused_blocks,
 93       unused_bytes => l_unused_bytes,
 94       LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 95       LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 96       LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 97       p( 'Total Blocks', l_total_blocks );
 98       p( 'Total Bytes', l_total_bytes );
 99       p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
100       p( 'Unused Blocks', l_unused_blocks );
101       p( 'Unused Bytes', l_unused_bytes );
102       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
103       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
104       p( 'Last Used Block', l_LAST_USED_BLOCK );
105  end;
106  /

Procedure created.

show_space过程包含以下参数:

sys@DEVELOP> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT

参数如下：
• P_SEGNAME：段名（例如，表或索引名）。
• P_OWNER：默认为当前用户，不过也可以使用这个例程查看另外某个用户。
• P_TYPE：默认为TABLE，这个参数表示查看哪种类型的对象（段）。例如，SELECT DISTINCT SEGMENT_TYPE FROM DBA_SEGMENTS
会列出合法的段类型。
• P_PARTITION：显示分区对象的空间时所用的分区名。SHOW_SPACE一次只显示一个分区的空间利用率。

这个过程的输出如下，这里段位于一个自动段空间管理（Automatic Segment Space Management， ASSM）表空间中：

sys@DEVELOP> exec show_space('AC01','HNSIC');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              12
Full Blocks ............................          16,305
Total Blocks............................         146,304
Total Bytes.............................   1,198,522,368
Total MBytes............................           1,143
Unused Blocks...........................         129,920
Unused Bytes............................   1,064,304,640
Last Used Ext FileId....................              10
Last Used Ext BlockId...................         348,800
Last Used Block.........................           8,192

PL/SQL procedure successfully completed.

报告的各项结果说明如下：
• Unformatted Blocks：为表分配的位于高水位线（high-water mark, HWM）之下但未用的块数。把未格式化和未用的块加在一起，就是已为表分配但从未用于保存ASSM对象数据的总块数。
• FS1 Blocks-FS4 Blocks：包含数据的格式化块。项名后的数字区间表示各块的“空闲度”。例如，（0-25）是指空闲度为
0~25%的块数。
• Full Blocks：已满的块数，不能再对这些执行插入。
• Total Blocks、Total bytes、Total Mbytes：为所查看的段分配的总空间量，单位分别是数据库块、字节和兆字节。
• Unused Blocks、Unused Bytes：表示未用空间所占的比例（未用空间量）。这些块已经分配给所查看的段，但目前在段的HWM之上。
• Last Used Ext FileId：最后使用的文件的文件ID，该文件包含最后一个含数据的区段（extent）。
• Last Used Ext BlockId：最后一个区段开始处的块ID；这是最后使用的文件中的块ID。
• Last Used Block：最后一个区段中最后一个块的偏移量。

如果对象在用户空间管理的表空间中，使用SHOW_SPACE查看时，输出如下：

sys@DEVELOP> exec show_space('T1');
Free Blocks.............................               3
Total Blocks............................          17,408
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................             869
Unused Bytes............................       7,118,848
Last Used Ext FileId....................               1
Last Used Ext BlockId...................       1,696,896
Last Used Block.........................             155

PL/SQL procedure successfully completed.

这里惟一的区别是报告中最前面的Free Blocks项。这是段的第一个freelist（自由列表）组中的块数。
脚本只测试了第一个freelist组。如果想测试多个freelist组，还需要修改这个脚本。

mystat.sql和相应的mystat2.sql用于展示完成某操作之前和之后的某些Oracle“统计结果”的变化情况。mystat.sql只是获得统计结果的开始值：
mystat.sql脚本内容如下:

set echo off
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

mystat2.sql用于报告统计结果的变化情况（差值）：

set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

例如，要查看某个UPDATE生成的redo数，可以使用以下命令：

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat.sql "redo size"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         756293676
redo size for lost write detection                                        0
redo size for direct writes                                               0

sys@DEVELOP> update t1 set aac009='1';

795680 rows updated.

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat2.sql

NAME                                                                      V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size                                                        1139306496    1,139,306,496
redo size for lost write detection                                        0                0
redo size for direct writes                                               0                0

由此可见，795,680行记录的UPDATE会生成1,139,306,496字节的redo.

runstats是一个工具，能对做同一件事的两个不同方法进行比较，得出孰优孰劣的结果。只需提供两个不同的方法， 余下的事情都由runstats负责。runstats只是测量3个要素：
? 耗用时间（elapsed time）：知道耗用时间很有用，不过这不是最重要的信息。
? 系统统计结果：会并排显示每个方法做某件事（如执行一个解析调用）的次数，并展示二者之差。
? 闩锁（latching）：这是这个报告的关键输出。
闩锁（latch）是一种轻量级的锁。锁（lock）是一种串行化设备，而串行化设备不支持并发。如果应用不支 持并发，可扩缩性就比较差，只能支持较少的用户，而且需要更多的资源。构建应用时，我们往往希望应用能很好地扩缩，也就是说，为1位用户服务与为1,000或10,000位用户服务应该是一样的。应用中使用的闩锁越少，性能就越好。如果一种方法从耗用时间来看运行时间较长，但是只使用了另一种方法10%的闩锁,我可能会选择前者。因为与使用更多闩锁的方法相比，使用较少闩锁的方法能更好地扩缩。
runstats最后独立使用，也就是说，最好在一个单用户数据库上运行。它会测量各个方法的统计结果和闩锁（锁定）活动。 runstats在运行过程中，不希望其他任务对系统的负载或闩锁产生影响。只需一个小的测试数据库就能很好地完成这些测试。要使用runstats，需要能访问几个V$视图，并创建一个表来存储统计结果，还要创建runstats包。为此，需要访问3个V$表:V$STATNAME、V$MYSTAT和V$LATCH。以下是我使用的视图：

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch;

如果你能得到V$STATNAME、V$MYSTAT、V$LATCH和V$TIMER的直接授权，就能直接对这些表执行SELECT操作（相应地可以自行创建 视图）；否则，可以由其他人对这些表执行SELECT操作为你创建视图，并授予你在这个视图上执行SELECT的权限。
一旦建立视图，接下来只需要一个小表来收集统计结果：

sys@DEVELOP> create global temporary table run_stats
  2  ( runid varchar2(15),
  3  name varchar2(80),
  4  value int )
  5  on commit preserve rows;

Table created.

最后，需要创建runstats包。其中包含3个简单的API调用：
runstats测试开始时调用RS_STAT（runstats开始）。
正如你想象的，RS_MIDDLE会在测试之间调用。
完成时调用RS_STOP，打印报告。
创建runstats包的规范如下：

sys@DEVELOP> create or replace package runstats_pkg
  2  as
  3  procedure rs_start;
  4  procedure rs_middle;
  5  procedure rs_stop(p_difference_threshold in number default 0);
  6  end;
  7  /

Package created.

参数P_DIFFERENCE_THRESHOLD用于控制最后打印的数据量。runstats会收集并得到每次运行的统计结果和闩信息，然后打印一个 报告，说明每次测试（每个方法）使用了多少资源，以及不同测试（不同方法）的结果之差。可以使用这个输入参数来控制只查 看差值大于这个数的统计结果和闩信息。由于这个参数默认为0，所以默认情况下可以看到所有输出。
下面我们逐一分析包体中的过程。包前面是一些全局变量，这些全局变量用于记录每次运行的耗用时间：
RS_START过程。这个过程只是清空保存统计结果的表，并填入“上一次”（before）得到的统计结果和闩信息。然后获得 当前定时器值，这是一种时钟，可用于计算耗用时间（单位百分之一秒）：

接下来是RS_MIDDLE过程。这个过程只是把第一次测试运行的耗用时间记录在G_RUN1中。然后插入当前的一组统计结果和闩信息。 如果把这些值与先前在RS_START中保存的值相减，就会发现第一个方法使用了多少闩，以及使用了多少游标（一种统计结果）， 等等。
最后，记录下一次运行的开始时间：

完整的包体创建语句如下:

sys@DEVELOP> create or replace package body runstats_pkg
  2   as
  3
  4   g_start number;
  5   g_run1 number;
  6   g_run2 number;
  7
  8   procedure rs_start
  9   is
 10   begin
 11     delete from run_stats;
 12
 13     insert into run_stats
 14     select 'before', stats.* from stats;
 15
 16     g_start := dbms_utility.get_time;
 17   end;
 18
 19   procedure rs_middle
 20   is
 21   begin
 22     g_run1 := (dbms_utility.get_time-g_start);
 23
 24     insert into run_stats
 25     select 'after 1', stats.* from stats;
 26     g_start := dbms_utility.get_time;
 27
 28   end;
 29
 30   procedure rs_stop(p_difference_threshold in number default 0)
 31   is
 32   begin
 33     g_run2 := (dbms_utility.get_time-g_start);
 34
 35     dbms_output.put_line
 36       ( 'Run1 ran in ' || g_run1 || ' hsecs' );
 37     dbms_output.put_line
 38       ( 'Run2 ran in ' || g_run2 || ' hsecs' );
 39     dbms_output.put_line
 40       ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
 41       '% of the time' );
 42     dbms_output.put_line( chr(9) );
 43
 44     insert into run_stats
 45     select 'after 2', stats.* from stats;
 46
 47     dbms_output.put_line
 48       ( rpad( 'Name', 30 ) || lpad( 'Run1', 10 ) ||
 49       lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
 50
 51     for x in
 52       ( select rpad( a.name, 30 ) ||
 53         to_char( b.value-a.value, '9,999,999' ) ||
 54         to_char( c.value-b.value, '9,999,999' ) ||
 55         to_char( ( (c.value-b.value)-(b.value-a.value)), '9,999,999' ) data
 56         from run_stats a, run_stats b, run_stats c
 57         where a.name = b.name
 58         and b.name = c.name
 59         and a.runid = 'before'
 60         and b.runid = 'after 1'
 61         and c.runid = 'after 2'
 62         and (c.value-a.value) > 0
 63         and abs( (c.value-b.value) - (b.value-a.value) )
 64         > p_difference_threshold
 65         order by abs( (c.value-b.value)-(b.value-a.value))
 66       ) loop
 67       dbms_output.put_line( x.data );
 68     end loop;
 69
 70     dbms_output.put_line( chr(9) );
 71     dbms_output.put_line
 72       ( 'Run1 latches total versus runs -- difference and pct' );
 73     dbms_output.put_line
 74       ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
 75         lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
 76
 77     for x in
 78       ( select to_char( run1, '9,999,999' ) ||
 79       to_char( run2, '9,999,999' ) ||
 80       to_char( diff, '9,999,999' ) ||
 81       to_char( round( run1/run2*100,2 ), '999.99' ) || '%' data
 82       from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
 83       sum( (c.value-b.value)-(b.value-a.value)) diff
 84       from run_stats a, run_stats b, run_stats c
 85       where a.name = b.name
 86       and b.name = c.name
 87       and a.runid = 'before'
 88       and b.runid = 'after 1'
 89       and c.runid = 'after 2'
 90       and a.name like 'LATCH%'
 91          )
 92       ) loop
 93       dbms_output.put_line( x.data );
 94     end loop;
 95   end;
 96
 97  end;
 98   /

Package body created.

下面可以使用runstats了。我们将通过例子来说明如何使用runstats对批量插入（INSERT）和逐行处理进行比较，
看看哪种方法效率更高。首先建立两个表，要在其中插入795,680行记录：

sys@DEVELOP> create table t1 as select * from hnsic.ac01 where 1=0;

Table created.

sys@DEVELOP> create table t2 as select * from hnsic.ac01 where 1=0;

Table created.

接下来使用第一种方法插入记录，也就是使用单独一条SQL语句完成批量插入。首先调用RUNSTATS_PKG.RS_START：

sys@DEVELOP> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

sys@DEVELOP> insert into t1 select * from hnsic.ac01;

795680 rows created.

sys@DEVELOP> commit;

Commit complete.

下面准备执行第二种方法，即逐行地插入数据：

sys@DEVELOP> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

sys@DEVELOP> begin
  2   for x in (select * from hnsic.ac01)
  3   loop
  4     insert into t2 values x;
  5   end loop;
  6   commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

最后生成报告:

sys@DEVELOP> exec runstats_pkg.rs_stop;
Run1 ran in 4835 hsecs
Run2 ran in 14497 hsecs
run 1 ran in 33.35% of the time

Name                                                                                            Run1              

  Run2
Diff
LATCH.space background state object latch                                                1         0        -1
LATCH.file cache latch                                                                  93        94         1
LATCH.ktm global data                                                                    5         4        -1
LATCH.KTF sga latch                                                                      1         0        -1
LATCH.dml lock allocation                                                                1         2         1
LATCH.tablespace key chain                                                               1         2         1
LATCH.deferred cleanup latch                                                             1         2         1
LATCH.kcbtsemkid latch                                                                   2         1        -1
LATCH.threshold alerts latch                                                             1         2         1
LATCH.cp sga latch                                                                       1         2         1
LATCH.hash table modification latch                                                      1         2         1
STAT...parse count (hard)                                                                1         2         1
STAT...parse count (total)                                                               5         4        -1
STAT...sql area evicted                                                                  1         0        -1
LATCH.ASM network state latch                                                            1         2         1
STAT...write clones created in foreground                                                3         2        -1
STAT...shared hash latch upgrades - no wait                                             93        94         1
LATCH.ncodef allocation latch                                                            1         2         1
STAT...deferred (CURRENT) block cleanout applications                                    1         2         1
STAT...commit txn count during cleanout                                                  0         1         1
STAT...IMU Flushes                                                                       0         1         1
STAT...table fetch by rowid                                                             94        95         1
STAT...index scans kdiixs1                                                              94        95         1
STAT...Requests to/from client                                                           6         4        -2
STAT...session cursor cache count                                                        5         3        -2
STAT...redo log space wait time                                                          2         0        -2
STAT...redo log space requests                                                           4         2        -2
LATCH.ksv class latch                                                                    3         5         2
STAT...non-idle wait time                                                                2         0        -2
STAT...calls to get snapshot scn: kcmgss                                               184       186         2
STAT...SQL*Net roundtrips to/from client                                                 6         4        -2
LATCH.MinActiveScn Latch                                                                 0         2         2
STAT...db block gets from cache (fastpath)                                             103       105         2
LATCH.ksv allocation latch                                                               4         6         2
STAT...redo entries                                                                849,543   849,545         2
LATCH.message pool operations parent latch                                               4         1        -3
STAT...parse time cpu                                                                    1         4         3
STAT...user calls                                                                        9         6        -3
LATCH.kwqbsn:qsga                                                                        2         5         3
LATCH.Shared B-Tree                                                                      3         6         3
LATCH.interrupt manipulation                                                             1         5         4
LATCH.SQL memory manager latch                                                           1         5         4
LATCH.kokc descriptor allocation latch                                                   2         6         4
STAT...redo buffer allocation retries                                                    5         1        -4
LATCH.multiblock read objects                                                            6         2        -4
LATCH.object stats modification                                                          8        13         5
STAT...enqueue releases                                                              3,711     3,716         5
STAT...enqueue requests                                                              3,710     3,716         6
STAT...redo subscn max counts                                                       23,411    23,405        -6
LATCH.corrupted undo seg latch                                                          18        12        -6
STAT...heap block compress                                                               7         0        -7
STAT...free buffer requested                                                        23,406    23,414         8
LATCH.ksuosstats global area                                                             5        14         9
STAT...calls to kcmgas                                                              10,299    10,289       -10
STAT...redo ordering marks                                                          10,201    10,191       -10
STAT...calls to kcmgcs                                                              33,174    33,187        13
LATCH.loader state object freelist                                                      14        28        14
LATCH.qmn task queue latch                                                              15        30        15
LATCH.job_queue_processes parameter latch                                               11        31        20
LATCH.parallel query alloc buffer                                                        7        31        24
STAT...consistent changes                                                               31        56        25
STAT...db block gets from cache                                                    870,280   870,307        27
STAT...db block gets                                                               870,280   870,307        27
STAT...db block changes                                                          1,672,435 1,672,465        30
STAT...active txn count during cleanout                                             16,027    15,995       -32
STAT...cleanout - number of ktugct calls                                            16,027    15,995       -32
STAT...consistent gets - examination                                                16,032    16,000       -32
LATCH.session timer                                                                     16        48        32
LATCH.Change Notification Hash table latch                                              16        48        32
LATCH.KMG MMAN ready and startup request latch                                          16        49        33
STAT...workarea memory allocated                                                        40        -5       -45
STAT...messages sent                                                                   370       322       -48
LATCH.sort extent pool                                                                   2        61        59
STAT...undo change vector size                                                  ####################        60
LATCH.ASM db client latch                                                               36        99        63
LATCH.simulator lru latch                                                            6,903     6,977        74
LATCH.parameter list                                                                    36       125        89
LATCH.FOB s.o list latch                                                                 6        97        91
LATCH.active checkpoint queue latch                                                    143       237        94
LATCH.global tx hash mapping                                                            19       129       110
LATCH.transaction branch allocation                                                     32       162       130
LATCH.space background task latch                                                       89       270       181
LATCH.post/wait queue                                                                   68       251       183
LATCH.SGA IO buffer pool latch                                                         193       442       249
STAT...change write time                                                             1,393     1,648       255
LATCH.Real-time plan statistics latch                                                  383       763       380
LATCH.shared pool simulator                                                            106       508       402
STAT...bytes sent via SQL*Net to client                                              1,438       948      -490
LATCH.JS queue state obj latch                                                         360     1,044       684
STAT...bytes received via SQL*Net from client                                        2,313     1,623      -690
LATCH.lgwr LWN SCN                                                                     401     1,402     1,001
LATCH.Consistent RBA                                                                   399     1,401     1,002
LATCH.session switching                                                                  7     1,009     1,002
LATCH.resmgr:actses change group                                                         9     1,018     1,009
LATCH.resmgr group change latch                                                          7     1,016     1,009
LATCH.compile environment latch                                                          8     1,017     1,009
LATCH.global KZLD latch for mem in SGA                                                   7     1,016     1,009
LATCH.mostly latch-free SCN                                                            408     1,427     1,019
LATCH.PL/SQL warning settings                                                           53     1,086     1,033
LATCH.In memory undo latch                                                             298     1,793     1,495
LATCH.channel handle pool latch                                                         15     2,024     2,009
LATCH.dummy allocation                                                                  14     2,024     2,010
LATCH.ksz_so allocation latch                                                           13     2,023     2,010
LATCH.OS process: request allocation                                                    13     2,023     2,010
LATCH.resmgr:active threads                                                             13     2,023     2,010
LATCH.resmgr:free threads list                                                          13     2,023     2,010
LATCH.process allocation                                                                14     2,024     2,010
LATCH.process group creation                                                            13     2,023     2,010
LATCH.list of block allocation                                                       1,665     3,678     2,013
LATCH.Event Group Locks                                                                 17     2,035     2,018
LATCH.session state list latch                                                          13     2,042     2,029
LATCH.transaction allocation                                                            24     2,079     2,055
STAT...IMU undo allocation size                                                      3,256     1,080    -2,176
LATCH.DML lock allocation                                                               73     2,251     2,178
LATCH.parameter table management                                                        69     2,249     2,180
LATCH.OS process allocation                                                            113     2,315     2,202
LATCH.redo allocation                                                                2,664     5,249     2,585
LATCH.sequence cache                                                                    21     3,048     3,027
LATCH.undo global data                                                              23,400    26,617     3,217
LATCH.active service list                                                              146     3,396     3,250
LATCH.cache buffer handles                                                              42     3,356     3,314
LATCH.redo writing                                                                   1,389     4,733     3,344
LATCH.channel operations parent latch                                                  273     3,729     3,456
STAT...CPU used when call started                                                    2,405     6,330     3,925
STAT...CPU used by this session                                                      2,403     6,330     3,927
STAT...DB time                                                                       2,416     6,351     3,935
LATCH.OS process                                                                        29     4,041     4,012
LATCH.call allocation                                                                   26     4,252     4,226
LATCH.session allocation                                                               200     4,567     4,367
STAT...free buffer inspected                                                         9,080    13,902     4,822
LATCH.messages                                                                       1,938     7,030     5,092
STAT...hot buffers moved to head of LRU                                              3,719     9,033     5,314
STAT...recursive cpu usage                                                               6     5,494     5,488
LATCH.client/application info                                                           52     7,115     7,063
STAT...consistent gets from cache                                                   49,254    57,049     7,795
STAT...consistent gets                                                              49,254    57,049     7,795
STAT...table scan blocks gotten                                                     16,318    24,129     7,811
STAT...no work - consistent read gets                                               16,507    24,320     7,813
STAT...buffer is not pinned count                                                   16,695    24,510     7,815
STAT...session logical reads                                                       919,534   927,356     7,822
STAT...consistent gets from cache (fastpath)                                        33,128    40,954     7,826
LATCH.enqueue hash chains                                                            8,891    17,620     8,729
LATCH.object queue header heap                                                      12,561    21,765     9,204
STAT...session cursor cache hits                                                        97     9,347     9,250
STAT...IMU Redo allocation size                                                        476    11,228    10,752
STAT...redo size                                                                ####################    17,552
LATCH.session idle bit                                                               5,575    23,796    18,221
LATCH.cache buffers lru chain                                                       18,487    42,303    23,816
LATCH.checkpoint queue latch                                                        17,631    49,092    31,461
LATCH.object queue header operation                                                122,486   188,006    65,520
STAT...session uga memory max                                                      123,488    31,848   -91,640
LATCH.simulator hash latch                                                         237,377   443,767   206,390
LATCH.SQL memory manager workarea list latch                                         1,219   237,704   236,485
STAT...session pga memory max                                                            0   262,144   262,144
STAT...execute count                                                                   101   795,782   795,681
STAT...opened cursors cumulative                                                       102   795,789   795,687
STAT...recursive calls                                                                 848   804,498   803,650
LATCH.shared pool                                                                    7,037   884,303   877,266
LATCH.row cache objects                                                             25,994 1,205,408 1,179,414
LATCH.cache buffers chains                                                      #################### 7,690,411
STAT...logical read bytes from cache                                            ##############################

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
##############################  53.64%

PL/SQL procedure successfully completed.

本文参考Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions