oracel分区之索引分区

索引分区
索引与表类似,也可以分区。对索引进行分区有两种可能的方法:
随表对索引完成相应的分区:这也称为局部(本地)分区索引(locally pertitioned index)。每个表分区都有一个索引分区, 而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区,表分区中的所有行都表示在一个索引分区中。

按区间对索引分区:这也称为全局分区索引(globally partitioned index)。在此,索引按区间分区(或者在Oracle 10g中该 可以按散列分区),一个索引分区可能指向任何(和所有)表分区。

对于全局分区索引,要注意实际上索引分区数可能不同于表分区数。
由于全局索引只按区间或散列分区,如果希望有一个列表或组合分区索引,就必须使用局部索引。局部索引会使用底层表相同的 机制分区。

注意 全局索引的散列分区是Oracle 10g Release 1及以后的版本中才有的新特性。在Oracle9i及以前的版本中,只能按区间进 行全局分区。

局部索引
Oracle划分了以下两类局部索引:
局部前缀索引(local prefixed index):在这些索引中,分区键在索引定义的前几列上。例如,一个表在名为LOAD_DATE的列 上进行区间分区,该表上的局部前缀索引就是LOAD_DATE作为其索引列列表中的第一列。
局部非前缀索引(local nonprefixed index):这些索引不以分区键作为其列列表的前几列。索引可能包含分区键列,也可能 不包含。
这两类索引都可以利用分区消除,它们都支持惟一性(只有前缀索引包含分区键)等。事实上,使用局部前缀索引的查询总允许 索引分区消除,而使用局部非前缀索引的查询可能不允许。正是由于这个原因,所以在某些人看来局部非前缀索引“更慢”,它 们不能保证分区消除(但确实可以支持分区消除)。

如果查询中将索引用作访问表的初始路径,那么从本质来讲,局部前缀索引并不比局部非前缀索引更好。也就是说,如何查询把 “扫描一个索引”作为第一步,那么前缀索引和非前缀索引之间并没有太大的差别。

分区消除行为
如果查询首先访问索引,它是否能消除分区完全取决于查询中的谓词。下面的代码创建了一个表PARTITIONED_TABLE,它在一个 数字列A上进行区间分区,使得小于2的值都在分区PART_1中,小于3的值则都在分区PART_2中:

sys@JINGYONG> create table partitioned_table
  2  (a int,
  3   b int,
  4   data varchar2(20)
  5  )
  6  partition by range(a)
  7  (
  8   partition part_1 values less than(2) tablespace p1,
  9   partition part_2 values less than(3) tablespace p2
 10  )
 11  ;

表已创建。

然后我们创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。注意,非前缀索引在其定义中没有 以A作为其最前列,这是这一点使之成为一个非前缀索引:

sys@JINGYONG> create index local_prefixed on partitioned_table(a,b) local;

索引已创建。

sys@JINGYONG> create index local_nonprefixed on partitioned_table(b) local;

索引已创建。

接下来,我们向一个分区中插入一些数据,并收集统计信息:

sys@JINGYONG> insert into partitioned_table
  2  select mod(rownum-1,2)+1,rownum,'x' from all_objects;

已创建50324行。

sys@JINGYONG> commit;

提交完成。

sys@JINGYONG> exec dbms_stats.gather_table_stats(user,'PARTITIONED_TABLE',estima
te_percent=>100,method_opt=>'for all columns size repeat',cascade=>true);

PL/SQL 过程已成功完成。

将表空间P2脱机,其中包含用于表和索引的PART_2分区:

sys@JINGYONG> alter tablespace p2 offline;

表空间已更改。

表空间P2脱机后,Oracle就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”,导致分区不可用。现在我们查 询这个表,来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引:

sys@JINGYONG> select * from partitioned_table where a=1 and b=1;

         A          B DATA
---------- ---------- --------------------
         1          1 x

这个查询成功了,通过查看解释计划,可以看到这个查询为什么能成功。我们将使用内置包DBMS_XPLAN来查看这个查询访问了哪 些分区。输出中的PSTART (分区开始)和PSTOP(分区结束)这两列准确地显示出,这个查询要想成功需要哪些分区必须在线而且 可用:

sys@JINGYONG> delete from plan_table;

已删除0行。

sys@JINGYONG> explain plan for
  2  select * from partitioned_table where a=1 and b=1;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- ---------
Plan hash value: 1622054381

--------------------------------------------------------------------------------------------------------------- ---------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|  Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
|   0 | SELECT STATEMENT                   |                   |     1 |     9 |     2   (0)| 00:00:01 |       |        |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_PREFIXED    |     1 |       |     1   (0)| 00:00:01 |     1 |      1 |
--------------------------------------------------------------------------------------------------------------- ---------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=1 AND "B"=1)

已选择15行。

因此,使用LOCAL_PREFIXED的查询成功了。优化器能消除LOCAL_PREFIXED的PART_2不予考虑,因为我们在查询中指定了A=1,而 且在计划中可以清楚地看到PSTART和PSTOP都等于1.分区消除帮助了我们。不过,第二个查询却失败了

sys@JINGYONG> select * from partitioned_table where b=1;
ERROR:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/product/10.2.0/oradata/jingyong/jingyon
g/p201.dbf'

我们可以通过查看这个查询的执行计划看到为什么会失败

sys@JINGYONG> delete from plan_table;

已删除4行。

sys@JINGYONG> explain plan for
  2  select * from partitioned_table where b=1;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------
Plan hash value: 440752652

--------------------------------------------------------------------------------------------------------------- ---------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|  Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
|   0 | SELECT STATEMENT                   |                   |     1 |     9 |     4   (0)| 00:00:01 |       |        |
|   1 |  PARTITION RANGE ALL               |                   |     1 |     9 |     4   (0)| 00:00:01 |     1 |      2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     9 |     4   (0)| 00:00:01 |     1 |      2 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |     3   (0)| 00:00:01 |     1 |      2 |
--------------------------------------------------------------------------------------------------------------- ---------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"=1)

已选择15行。

在此,优化器不能不考虑LOCAL_NONPREFIXED的PART_2,为了查看是否有B=1,索引的PART_1和PART_2都必须检查。在此,局部非 前缀索引存在一个性能问题:它不能像前缀索引那样,在谓词中使用分区键。并不是说前缀索引更好,我们的意思是:要使用非 前缀索引,必须使用一个允许分区消除的查询

sys@JINGYONG> drop index local_prefixed;

索引已删除。

sys@JINGYONG> select * from partitioned_table where a=1 and b=1;

         A          B DATA
---------- ---------- --------------------
         1          1 x

它会成功,但是正如我们所见,这里使用了先前失败的索引。该计划显示出,在此Oracle能利用分区消除,有了谓词A=1,就有 了足够的信息可以让数据库消除索引分区PART_2而不予考虑:

sys@JINGYONG> delete from plan_table;

已删除4行。

sys@JINGYONG> explain plan for
  2  select * from partitioned_table where a=1 and b=1;

已解释。

sys@JINGYONG> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------- ---------
Plan hash value: 904532382

--------------------------------------------------------------------------------------------------------------- ---------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|  Pstop |
--------------------------------------------------------------------------------------------------------------- ---------
|   0 | SELECT STATEMENT                   |                   |     1 |     9 |     2   (0)| 00:00:01 |       |        |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     9 |     2   (0)| 00:00:01 |     1 |      1 |
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |     1   (0)| 00:00:01 |     1 |      1 |
--------------------------------------------------------------------------------------------------------------- ---------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"=1)
   3 - access("B"=1)

已选择16行。

注意PSTART和PSTOP列值为1和1.这就证明,优化器甚至对非前缀局部索引也能执行分区消除。
如果你频繁地用以下查询来查询先前的表:
select … from partitioned_table where a = :a and b = :b;
select … from partitioned_table where b = :b;
可以考虑在(b,a)上使用一个局部非前缀索引。这个索引对于前面的两个查询都是有用的。(a,b)上的局部前缀索引只对第一 个查询有用。
这里的关键是,不必对非前缀索引退避三舍,也不要认为非前缀索引是主要的性能障碍。如果你有多个如前所列的查询(可以得 益于非前缀索引),就应该考虑使用一个非前缀索引。重点是,要尽可能保证查询包含的谓词允许索引分区消除。使用前缀局部 索引可以保证这一点,使用非前缀索引则不能保证。还要考虑如何使用索引。如果将索引用作查询计划中的第一步,那么这两种 类型的索引没有多少差别。

局部索引和惟一约束
为了保证惟一性(这包括UNIQUE约束或PRIMARY KEY约束),如果你想使用一个局部索引来保证这个约束,那么分区键必须包括 在约束本身中。在我看来,这是局部索引的最大限制。Oracle只保证索引分区内部的惟一性,而不能跨分区。这说明什么呢?例 如,这意味着不能一方面在一个TIMESTAMP字段上执行区间分区,而另一方面在ID上有一个主键(使用一个局部分区索引来保证 )。Oracle会利用全局索引来保证惟一性。
在下面的例子中,我们将创建一个区间分区表,它按一个名为LOAD_DATE的列分区,却在ID列上有一个主键。通过查看这个分表 的每一个段,就能很容易地看出到底创建了哪些对象:

sys@JINGYONG> create table partitioned
  2  (load_date date,
  3  id int,
  4  constraint partitioned_pk primary key(id)
  5  )
  6  partition by range(load_date)
  7  (
  8  partition part_1 values less than(to_date('2000-01-01','yyyy-mm-dd')),
  9  partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))
 10  );

表已创建。

sys@JINGYONG> select segment_name,partition_name,segment_type
  2  from user_segments where segment_name like'PARTITIONED%';

SEGMENT_NAME      PARTITION_NAME    SEGMENT_TYPE
--------------    --------------    ------------------
PARTITIONED       PART_1            TABLE PARTITION
PARTITIONED       PART_2            TABLE PARTITION
PARTITIONED_PK                      INDEX

PARTITIONED_PK索引没有分区.而且我们将会看到,它根本无法进行局部分区。由于认识到非惟一索引也能像惟一索引一样保证 主键,我们想以此骗过Oracle,但是可以看到这种方法也不能奏效:

sys@JINGYONG> create table partitioned
  2  (timestamp date,
  3  id int
  4  )
  5  partition by range(timestamp)
  6  (
  7  partition part_1 values less than
  8  (to_date('2000-01-01','yyyy-mm-dd')),
  9  partition part_2 values less than
 10  (to_date('2001-01-01','yyyy-mm-dd'))
 11  );

表已创建。

sys@JINGYONG> create index partitioned_idx on partitioned(id) local;

索引已创建。

sys@JINGYONG> select segment_name,partition_name,segment_type
  2  from user_segments where segment_name like'PARTITIONED%';

SEGMENT_NAME            PARTITION_NAME    SEGMENT_TYPE
--------------------    ---------------   ------------------
PARTITIONED             PART_1            TABLE PARTITION
PARTITIONED             PART_2            TABLE PARTITION
PARTITIONED_IDX         PART_2            INDEX PARTITION
PARTITIONED_IDX         PART_1            INDEX PARTITION

sys@JINGYONG> alter table partitioned
  2  add constraint partitioned_pk primary key(id);
alter table partitioned
*
第 1 行出现错误:
ORA-01408: such column list already indexed

在此,Oracle试图在ID上创建一个全局索引,却发现办不到,这是因为ID上已经存在一个索引。如果已创建的索引没有分区,前 面的语句就能工作,Oracle会使用这个索引来保证约束。
为什么局部分区索引不能保证惟一性(除非分区键是约束的一部分),原因有两方面。首先,如果Oracle允许如此,就会丧失分 区的大多数好处。可用性和可扩缩性都会丧失殆尽,因为对于任何插入和更新,总是要求所有分区都一定可用,而且要扫描每一 个分区。你的分区越多,数据就会变得越不可用。另外,分区越多,要扫描的索引分区就越多,分区也会变得越发不可扩缩。这 样做不仅不能提供可用性和可扩缩性,相反,实际上 反倒会削弱可用性和可扩缩性。

另外,倘若局部分区索引能保证惟一性,Oracle就必须在事务级对这个表的插入和更新有效地串行化。这是因为,如果向PART_1 增加ID=1,Oracle就必须以某种方式防止其他人向PART_2增加ID=1。对此惟一的做法是防止别人修改索引分区PART_2,因为无法 通过对这个分区中的内容“锁定”来做到(找不出什么可以锁定)。

在一个OLTP系统中,惟一性约束必须由系统保证(也就是说,由Oracle保证),以确保数据的完整性。这意味着,应用的逻辑模 型会对物理设计产生影响。惟一性约束能决定底层的表分区机制,影响分区键的选择,或者指示你应该使用全局索引。下面将更 深入地了解全局索引。

全局索引
全局索引使用一种有别于底层表的机制进行分区。表可以按一个TIMESTAMP列划分为10个分区,而这个表上的一个全局索引可以 按REGION列划分为5个分区。与局部索引不同,全局索引只有一类,这就是前缀全局索引(prefixed global index)。如果全局 索引的索引键未从该索引的分区键开始,这是不允许的。这说明,不论用什么属性对索引分区,这些属性都必须是索引键的前几 列。
下面继续看前面的例子,这里给出一个使用全局索引的小例子。它显示全局分区索引可以用于保证主键的惟一性,这样一来,即 使不包括表的分区键,也能保证惟一性的分区索引。下面的例子创建了一个按TIMESTAMP分区的表,它有一个按ID分区的索引:

sys@JINGYONG> create table partitioned
  2  (timestamp date,
  3  id int
  4  )
  5  partition by range(timestamp)
  6  (
  7  partition part_1 values less than(to_date('2000-01-01','yyyy-mm-dd')),
  8  partition part_2 values less than(to_date('2001-01-01','yyyy-mm-dd'))
  9  );

表已创建。

sys@JINGYONG> create index partitioned_index on partitioned(id) global
  2  partition by range(id)
  3  (
  4  partition part_1 values less than(1000),
  5  partition part_2 values less than(maxvalue)
  6  );

索引已创建。

注意,这个索引中使用了MAXVALUE。MAXVALUE不仅可以用于索引中,还可以用于任何区间分区表中。它表示区间的“无限上界” 。在此前的所有例子中,我们都使用了区间的硬性上界(小于< 某个值>的值)。不过,全局索引有一个需求,即最高分区(最后 一个分区)必须有一个值为MAXVALUE的分区上界。这可以确保底层表中的所有行都能放在这个索引中。
下面,在这个例子的最后,我们将向表增加主键:

sys@JINGYONG> alter table partitioned add constraint
  2  partitioned_pk primary key(id);

表已更改。

从这个代码还不能明显看出Oracle在使用我们创建的索引来保证主键,所以可以试着删除这个索引来证明这一点:

sys@JINGYONG> drop index partitioned_index;
drop index partitioned_index
           *
第 1 行出现错误:
ORA-02429: cannot drop index used for enforcement of unique/primary key

为了显示Oracle不允许创建一个非前缀全局索引,只需执行下面的语句:

sys@JINGYONG> create index partitioned_index2 on partitioned(timestamp,id)
  2  global
  3  partition by range(id)
  4  (
  5  partition part_1 values less than(1000),
  6  partition part_2 values less than(maxvalue)
  7  );
partition by range(id)
                     *
第 3 行出现错误:
ORA-14038: GLOBAL partitioned index must be prefixed

为了创建一个全局索引那么索引的分区键必须是全局索引中的前几列才行如下:

sys@JINGYONG> create index partitioned_index2 on partitioned(id,timestamp)
  2  global
  3  partition by range(id)
  4  (
  5  partition part_1 values less than(1000),
  6  partition part_2 values less than(maxvalue)
  7  );

索引已创建。

错误信息相当明确。全局索引必须是前缀索引。那么,要在什么时候使用全局索引呢?我们将分析两种不同类型的系统(数据仓 库和OLTP)。来看看何时可以应用全局索引

数据仓库和全局索引
原先数据仓库和全局索引是相当互斥的。数据仓库就意味着系统有某些性质,如有大量的数据出入。许多数据仓库都实现了一种 滑动窗口(sliding window)方法来管理数据,也就是说,删除表中最旧的分区,并为新加载的数据增加一个新分区。在过去( Oracle8i及以前的版本),数据仓库系统都避免使用全局索引,对此有一个很好的原因:全局索引缺乏可用性。大多数分区操作 (如删除一个旧分区)都会使全局索引无效,除非重建全局索引,否则无法使用,这会严重地影响可用性,以前往往都是如此。

滑动窗口和索引
下面的例子实现了一个经典的数据滑动窗口。在许多实现中,会随着时间的推移向仓库中增加数据,而最旧的数据会老化。在很 多时候,这个数据会按一个日期属性进行区间分区,所以最旧的数据多存储在一个分区中,新加载的数据很可能都存储在一个新 分区中。每月的加载过程涉及:
去除老数据:最旧的分区要么被删除,要么与一个空表交换(将最旧的分区变为一个表),从而允许对旧数据进行归档。
加载新数据并建立索引:将新数据加载到一个“工作”表中,建立索引并进行验证。
关联新数据:一旦加载并处理了新数据,数据所在的表会与分区表中的一个空分区交换,将表中的这些新加载的数据变成分区表 中的一个分区(分区表会变得更大)。
这个过程会没有重复,或者执行加载过程的任何周期重复;可以是每天或每周。我们将在这一节实现这个非常典型的过程,显示 全局分区索引的影响,并展示分区操作期间可以用哪些选项来提高可用性,从而能实现一个数据滑动窗口,并维持数据的连续可 用性。
在这个例子中,我们将处理每年的数据,并加载2004和2005财政年度的数据。这个表按TIMESTAMP列分区,并创建了两个索引, 一个是ID列上的局部分区索引,另一个是TIMESTAMP列上的全局索引(这里为分区):

sys@JINGYONG> create table partitioned
  2  (timestamp date,
  3  id int
  4  )
  5  partition by range(timestamp)
  6  (
  7  partition fy_2004 values less than(to_date('2005-01-01','yyyy-mm-dd')),
  8  partition fy_2005 values less than(to_date('2006-01-01','yyyy-mm-dd'))
  9  );

表已创建。

sys@JINGYONG> insert into partitioned partition(fy_2004)
  2  select to_date('2004-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
  3  from all_objects;

已创建50318行。

sys@JINGYONG> insert into partitioned partition(fy_2005)
  2  select to_date('2005-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
  3  from all_objects;

已创建50318行。

sys@JINGYONG> create index partitioned_idx_local on partitioned(id) local;

索引已创建。

sys@JINGYONG> create index partitioned_idx_global
  2  on partitioned(timestamp) global;

索引已创建。

这就建立了我们的“仓库”表。数据按财政年度分区,而且最后两年的数据在线。这个表有两个索引:一个是LOCAL索引,另一 个是GLOBAL索引。现在正处于年末,我们想做下面的工作:
(1) 删除最旧的财政年度数据。我们不想永远地丢掉这个数据,而只是希望它老化,并将其归档。
(2) 增加最新的财政年度数据。加载、转换、建索引等工作需要一定的时间。我们想做这个工作,但是希望尽可能不影响当前 数据的可用性。
第一步是为2004财政年度建立一个看上去就像分区表的空表。我们将使用这个表与分区表中的FY_2004分区交换,将这个分区转 变成一个表,相应地使分区表中的分区为空。这样做的效果就是分区表中最旧的数据(实际上)会在交换之后被删除:

sys@JINGYONG> create table fy_2004(timestamp date,id int);

表已创建。

sys@JINGYONG> create index fy_2004_idx on fy_2004(id);

索引已创建。

对要加载的新数据做同样的工作。我们将创建并加载一个表,其结构就像是现在的分区表(但是它本身并不是分区表):

sys@JINGYONG> create table fy_2006(timestamp date,id int);

表已创建。

sys@JINGYONG> insert into fy_2006
  2  select to_date('2006-12-31','yyyy-mm-dd')-mod(rownum,360),object_id
  3  from all_objects;

已创建50325行。

sys@JINGYONG> create index fy_2006_idx on fy_2006(id) nologging;

索引已创建。

我们将当前的满分区变成一个空分区,并创建了一个包含FY_2004数据的“慢”表。而且,我们完成了使用FY_2006数据的所有必 要工作,这包括验证数据、进行转换以及准备这些数据所需完成的所有复杂任务。
现在可以使用一个交换分区来更新“活动”数据:

sys@JINGYONG> alter table partitioned
  2  exchange partition fy_2004
  3  with table fy_2004
  4  including indexes
  5  without validation;

表已更改。

sys@JINGYONG> alter table partitioned drop partition fy_2004;

表已更改。

要把旧数据“老化”,所要做的仅此而已。我们将分区变成一个满表,而将空表变成一个分区。这是一个简单的数据字典更新, 瞬时就会完成,而不会发生大量的I/O。现在可以将FY_2004表从数据库中导出(可能要使用一个可移植的表空间)来实现归档。 如果需要,还可以很快地重新关联这些数据。
接下来,我们想“滑入”(即增加)新数据:

sys@JINGYONG> alter table partitioned
  2  add partition fy_2006
  3  values less than(to_date('2007-01-01','yyyy-mm-dd'));

表已更改。

sys@JINGYONG> alter table partitioned
  2  exchange partition fy_2006
  3  with table fy_2006
  4  including indexes
  5  without validation;

表已更改。

sys@JINGYONG> select index_name,status from user_indexes where table_name=’PARTI
TIONED’;

INDEX_NAME STATUS
—————————— ——–
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL UNUSABLE

sys@JINGYONG> select index_name,status from user_indexes where index_name like’F
Y%’;

INDEX_NAME STATUS
—————————— ——–
FY_2004_IDX VALID
FY_2006_IDX VALID

当然,在这个操作之后,全局索引是不可用的。由于每个索引分区可能指向任何表分区,而我们刚才取走了一个分区,并增加了 一个分区,所以这个索引已经无效了。 其中有些条目指向我们已经生成的分区,却没有任何条目指向刚增加的分区。使用了这 个索引的任何查询可能会失败而无法执行,或者如果我们跳过不可用的索引, 尽管查询能执行,但查询的性能会受到负面影响 (因为无法使用这个索引):

sys@JINGYONG> set autotrace on explain
sys@JINGYONG> select /*+ index(partitioned partitioned_idx_global) */ count(*)
  2  from partitioned
  3  where timestamp between sysdate-50 and sysdate;
select /*+ index(partitioned partitioned_idx_global) */ count(*)
*
第 1 行出现错误:
ORA-01502: index 'SYS.PARTITIONED_IDX_GLOBAL' or partition of such index is in u
nusable state


sys@JINGYONG> select  count(*)
  2  from partitioned
  3  where timestamp between sysdate-50 and sysdate;

  COUNT(*)
----------
         0


执行计划
----------------------------------------------------------
Plan hash value: 2869581836

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)|Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |     9 |    63  (12)|00:00:01 |       |       |
|   1 |  SORT AGGREGATE            |             |     1 |     9 |            |         |       |       |
|*  2 |   FILTER                   |             |       |       |            |         |       |       |
|   3 |    PARTITION RANGE ITERATOR|             |     3 |    27 |    63  (12)|00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PARTITIONED |     3 |    27 |    63  (12)|00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYSDATE@!-50< =SYSDATE@!)
   4 - filter("TIMESTAMP"<=SYSDATE@! AND "TIMESTAMP">=SYSDATE@!-50)

Note
-----
   - dynamic sampling used for this statement

因此,执行这个分区操作后,对于全局索引,我们有以下选择:
跳过索引,可以像这个例子中一样(Oracle 10g会透明地这样做),在9i中则可以通过设置会话参数 SKIP_UNUSABLE_INDEXES=TRUE来跳过索引(Oracle 10g将这个设置默认为TRUE)。但是这样一来,就丢失了索引所提供的性能提 升。让查询接收到一个错误,就像9i中一样(SKIP_UNUSABLE_INDEX设置为FALSE),在10g中,显式地请求使用提示的任何查询 都会接收到错误。要想让数据再次真正可用,必须重建这个索引。

到此为止滑动窗口过程几乎不会带来任何停机时间,但是在我们重建全局索引时,需要相当长的时间才能完成。如果查询依赖于 这些索引,在此期间它们的运行时查询 性能就会受到负面影响,可能根本不会运行,也可能运行时得不到索引提供的好处。所 有数据都必须扫描,而且要根据数据重建整个索引。如果表的大小为数百GB,这会占用相当多的资源。

活动全局索引维护
从Oracle9i开始,对于分区维护又增加了另一个选项:可以在分区操作期间使用UPDATE GLOBAL INEXES子句来维护全局索引。这 意味着,在你删除一个分区、分解一个分区以及在分区上执行任何必要的操作时,Oracle会对全局索引执行必要的修改,保证它 是最新的。由于大多数分区操作都会导致全局索引无效,这个特征对于需要提供数据连续访问的系统来说是一个大福音。你会发 现,通过牺牲分区操作的速度(但是原先重建索引后会有一个可观的不可用窗口,即不可用的停机时间相当长),可以换取100% 的数据可用性(尽管分区操作的总体响应时间会更慢)。简单地说,如果数据仓库不允许有停机时间,而且必须支持数据的滑入 滑出等数据仓库技术,这个特性就再合适不过了,但是你必须了解它带来的影响。

再来看前面的例子,如果分区操作在必要时使用了UPDATE GLOBAL INDEXES子句(在这个例子中,在ADD PARTITION语句上就没有 必要使用这个子句,因为新增加的分区中没有任何行):

sys@JINGYONG> alter table partitioned
2 exchange partition fy_2004
3 with table fy_2004
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.

sys@JINGYONG> alter table partitioned
2 drop partition fy_2004
3 UPDATE GLOBAL INDEXES
4 /
Table altered.

sys@JINGYONG> alter table partitioned
2 add partition fy_2006
3 values less than ( to_date('01-jan-2007','dd-mon-yyyy') )
4 /
Table altered.

sys@JINGYONG> alter table partitioned
2 exchange partition fy_2006
3 with table fy_2006
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.

就会发现索引完全有效,不论在操作期间还是操作之后这个索引都是可用的:

sys@JINGYONG> select index_name, status from user_indexes;
INDEX_NAME                     STATUS
------------------------------ --------
FY_2006_IDX                    VALID
FY_2004_IDX                    VALID
PARTITIONED_IDX_GLOBAL         VALID
PARTITIONED_IDX_LOCAL          N/A
4 rows selected.

sys@JINGYONG> set autotrace on explain
sys@JINGYONG> select count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;
COUNT(*)
----------
6750
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (RANGE SCAN) OF 'PARTITIONED_IDX_GLOBAL' (INDEX) (Cost=9...

但是这里要做一个权衡:我们要在全局索引结构上执行INSERT和DELETE操作的相应逻辑操作。删除一个分区时,必须删除可能指 向该分区的所有全局索引条目。执行表与分区的交换时,必须删除指向原数据的所有全局索引条目,再插入指向刚滑入的数据的 新条目。所以ALTER命令执行的工作量会大幅增加。

索引重建方法比使用UPDATE GLOBAL INDEXES子句来更新全局分区索引确实运行得更快一些,所以,UPDATE GLOBAL INDEXES是一 种允许用资源耗费的增加来换取可用性的选项。如果需要提供连续的可用性,这就是一个必要的选择。但是,你必须理解相关的 问题,并且适当地确定系统中其他组件的大小。具体地将,许多数据仓库过一段时间都会改为使用大批量的直接路径操作,而绕 过undo生成,如果允许的话,还会绕过redo生成。但是倘若使用UPDATE GLOBAL INDEXES,就不能绕过undo或redo生成。在使用这 个特性之前,需要检查确定组件大小所用的规则,从而确保这种方法在你的系统上确实能正常工作。

OLTP和全局索引
OLTP系统的特点是会频繁出现许多小的读写事务,一般来讲,在OLTP系统中,首要的是需要快速访问所需的行,而且数据完整性 很关键,另外可用性也非常重要。

在OLTP系统中,许多情况下全局索引很有意义。表数据可以按一个键(一个列键)分区。不过,你可能需要以多种不同的方式访 问数据。例如,可能会按表中的LOCATION来划分EMPLOYEE数据,但是还需要按以下列快速访问EMPLOYEE数据:
DEPARTMENT:部门的地理位置很分散。部门和位置之间没有任何关系。
EMPLOYEE_ID:尽管员工ID能确定位置,但是你不希望必须按EMPLOYEE_ID和LOCATION搜索,因为这样一来索引分区上将不能发生 分区消除。而且EMPLOYEE_ID本身必然是惟一的。
JOB_TITLE:JOB_TITLE和LOCATION之间没有任何关系。任何LOCATION上都可以出现所有JOB_TITLE值。
这里需要按多种不同的键来访问应用中不同位置的EMPLOYEE数据,而且速度至上。在一个数据仓库中,可以只使用这些键上的局 部分区索引,并使用并行索引区间扫描来快速收集大量数据。

oracel分区之表分区

分区
分区(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

oracle rac中让sql语句在指定的节点执行的方法

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参数指定为你要使用的节点

重建控制文件时resetlogs与noresetlogs的使用情况

重建控制文件时resetlogs与noresetlogs的使用情况

控制文件中记录着数据库的数据文件,日志文件,备份数据等信息,更为重要的,控制文件中还记录了数据库的检查点
和scn信息,这些信息在数据恢复的过程中将起到关键性作用.

一个正常运行的数据库,通常控制文件都存在多份镜像,这些镜像的内容是完全相同的,oracle缺省就创建多份控制
文件更说明了控制文件的重要:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl
/u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl

可以通过如下一条命令将控制文件的创建语句备份到跟踪文件中:

SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc

SQL> host sz /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
rz
Starting zmodem transfer.  Press Ctrl+C to cancel.
  100%       8 KB    8 KB/s 00:00:01       0 Errors

此跟踪文件中会记录控制文件的创建脚本,脚本包含两个主要的段落,其中一段如下所示:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
CHARACTER SET ZHS16GBK
;

当数据库处于nomount状态下时,可以通过运行这段脚本创建控制文件,控制文件会自动创建到参数文件中
记录控制文件的位置(原来的控制文件在创建过程会被覆盖).这里需要理解的一个主要选项是:
noresetlogs/resetlogs.在跟踪文件中包含如下注释,详细解释了这两个选项的含义:

-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.

当数据库当前的redo log都可用时,可以通过noresetlogs参数重建控制文件,此时oracle能够从日志文件中
读取redo信息,记录到控制文件中,由于redo中记录的信息足以重演所有提交成功的事务,所以最终能够实现
完全恢复,成功打开数据库,这时的数据库就如同进行了一次断电之后的实例恢复,数据没有损失,重做日志
可以继续向前写入:

下面测试来看一下以noresetlogs重建控制文件进行数据库恢复的过程
先在数据库正常运行状态下对控制文件执行一次转储:

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19350.trc

这个转储文件中将包含数据库的检查点,redo thread信息,数据文件等信息,看一下
log file records内容:

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 3, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea466
 Low scn: 0x0000.000ea474 05/02/2013 11:40:58
 Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea474
 Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000e8ed8
 Low scn: 0x0000.000ea466 05/02/2013 11:40:52
 Next scn: 0x0000.000ea474 05/02/2013 11:40:58

从记录信息中我们可以看到redo02.log文件的next scn:0xffff.ffffffff,所以redo02.log文件是当前的
日志文件,我们可以从v$log视图中查看当前的重做日志组

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

接下来通过shutdown abort模拟一次数据库故障:

SQL> shutdown abort;
ORACLE instance shut down.

启动数据库到nomount状态,再来使用noresetlogs参数来重建控制文件:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
 14    '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
 15    '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
 16    '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
 17    '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
 18    '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
 19  CHARACTER SET ZHS16GBK
 20  ;

Control file created.

此时再来对控制文件进行一次转储,检查log file records部分:

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x0 dup: 1
 Archive links: fwrd: 2 back: 3 Prev scn: 0x0000.000ea466
 Low scn: 0x0000.000ea474 05/02/2013 11:40:58
 Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
 Archive links: fwrd: 0 back: 1 Prev scn: 0x0000.000ea474
 Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x0 dup: 1
 Archive links: fwrd: 1 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.000ea466 05/02/2013 11:40:52
 Next scn: 0x0000.000ea474 05/02/2013 11:40:58

从上面的记录我们可以看到重建的控文件能够从当前的日志文件获得正确的SCN及时间点等信息.同样地,控制
文件也能够从数据文件中获得详细的检查点信息:

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 6,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53
 thread:0 rba:(0x0.0.0)
 .....
 DATA FILE #2:
  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
 tablespace 1, index=2 krfil=2 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57
 thread:0 rba:(0x0.0.0)
 .....
 DATA FILE #3:
  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
 tablespace 2, index=3 krfil=3 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54
 thread:0 rba:(0x0.0.0)
 .....
 DATA FILE #4:
  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06
 thread:0 rba:(0x0.0.0)
 ....
 DATA FILE #5:
  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
 tablespace 6, index=5 krfil=5 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52
 thread:0 rba:(0x0.0.0)
 ....
 DATA FILE #6:
  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
 tablespace 7, index=6 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22
 thread:0 rba:(0x0.0.0)
 .....

从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:
Stop scn: 0xffff.ffffffff,接下来对数据库执行恢复,当恢复完成后再对控制文件进行转储:

SQL> recover database;
Media recovery complete.

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc

来观察此跟踪文件中的数据文件信息:

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 6,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x2 head=9 tail=9 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #2:
  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x2 head=8 tail=8 dup=1
 tablespace 1, index=2 krfil=2 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57
 thread:0 rba:(0x0.0.0)
....
DATA FILE #3:
  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1
 tablespace 2, index=3 krfil=3 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #4:
  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #5:
  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1
 tablespace 6, index=5 krfil=5 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #6:
  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
 tablespace 7, index=6 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22
 thread:0 rba:(0x0.0.0)
 ....
 

经过恢复之后,数据文件达到了一致状态,checkpoint scn(0x0000.000efd7d)和Stop scn(0x0000.000efd7d)
达到了一致,此时数据库就完成了恢复,数据库可以顺利启动:

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
  2       SIZE 30M  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 40M;

Tablespace altered.

现在我们来实验使用resetlogs方式来重建控制文件:
模拟数据库故障

SQL> shutdown abort;
ORACLE instance shut down.

以resetlogs来重建控制文件
SQL> startup nomount
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
 14    '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
 15    '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
 16    '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
 17    '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
 18    '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
 19  CHARACTER SET ZHS16GBK
 20  ;

Control file created.

此时对控制文件进行一次转储

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc

观察转储的跟踪文件中的log file record的信息:

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #2:
  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #3:
  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00

从上面的信息可以看到此时控制文件中的日志信息都是空的,oracle认为resetlogs方式下,当前的日志文件
已经损坏,那么就意味着oracle可能会丢失提交成功的数据,恢复将是一次不完全的介质恢复.

此时的数据文件信息如下:

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 6,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #2:
  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
 tablespace 1, index=2 krfil=2 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #3:
  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
 tablespace 2, index=3 krfil=3 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #4:
  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #5:
  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
 tablespace 6, index=5 krfil=5 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #6:
  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
 tablespace 7, index=6 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22
 thread:0 rba:(0x0.0.0)
 ....
 

从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:
Stop scn: 0xffff.ffffffff

不完全恢复最终要求数据库通过resetlogs方式打开,resetlogs将会强制清空或重建联机重做日志文件.
此时执行恢复必须使用backup controlfile选项,否则将会报错:

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00279: change 982400 generated at 05/02/2013 12:53:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_18_%u_.arc
ORA-00280: change 982400 for thread 1 is in sequence #18


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

如果在线日志操坏,那么恢复到最后,oracle提示的最后一个归档日志将是不存在的(如果在线日志没有
损坏,则可以指定在线日志文件执行恢复),此时可以输入cancel取消恢复,然后可以强制打开数据库:

alter database open resetlogs;

SQL> recover database using backup controlfile;
ORA-00279: change 1003572 generated at 05/02/2013 13:20:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_19_%u_.arc
ORA-00280: change 1003572 for thread 1 is in sequence #19


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
  2       SIZE 30M  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 40M;

Tablespace altered.

oracle checkpoint检查点

检查点

检查点只是一个数据库事件,它存在的根本意义在于减少崩溃恢复(crash recovery)时间.检查点事件由ckpt后台进程触发,当检查点发生时,ckpt进程会负责通知dbwr进程将脏数据(dirty buffer)写出到数据文件上,ckpt进程的另外一个职责是负责更新数据文件头及控制文件上的检查点信息.

检查点(checkpoint)的工作原理
在oracle数据库中,当进行数据修改时,需要首先将数据读入内存中(buffer cache),修改数据的同时,oracle会记录重做(redo)信息用于恢复.因为有了重做信息的存在,oracle不需要在事务提交时(commit)立即将变化的数据写回磁盘(立即写的效率会很低),重做的存在也正是为了在数据库崩溃之后,数据可以恢复.

常见的情况,数据库可能因为断电而crash,那么内存中修改过的,尚没有写入数据文件的数据将会丢失.在下一次数据库启动之后,oracle可以通过重做(redo)日志进行事务重演(也就是进行前滚),将数据库恢复到崩溃之前的状态,然后数据库可以打开提供使用之后oracle可以将没有提交的事务进行回滚.

检查点的存在就是为了缩短这个恢复时间.当检查点发生时(此时的scn被称为checkpoint scn). oracle会通知dbwr进程,把修改过的数据,也就是此checkpoint scn之前的脏数据(dirty data)从buffer cache写入磁盘,当写入完成之后,ckpt进程则会相应更新控制文件和数据文件头,记录检查点信息,标识变更.

checkpoint scn可以从数据库中查询得到:

SQL> select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1            1131856 2013-01-18 01:33:36
         2            1131856 2013-01-18 01:33:36
         3            1131856 2013-01-18 01:33:36
         4            1131856 2013-01-18 01:33:36
         5            1131856 2013-01-18 01:33:36
         6            1131856 2013-01-18 01:33:36

6 rows selected.

SQL> select dbid,checkpoint_change# from v$database;

      DBID CHECKPOINT_CHANGE#
---------- ------------------
3172629284            1131856

在检查点完成之后,此检查点之前修改过的数据都已经写回磁盘,重做日志文件中的相应重做记录对于崩溃/实例恢复不再有用.

如果检查点的频率高,那么恢复时间需要应用的重做日志就相对得少,恢复时间就可以缩短,如果oracle可以在性能允许的情况下,使得检查点的scn接宾redo的最新变更,那么,使得oracle可以最大化地减少恢复时间.

常规检查点与增量检查点
脏缓冲列表(Dirty List).
当数据在buffer cache中被修改之后,Dirty Buffer会被转移到dirty list,以便将来
执行的检查点可以将这些修改过的buffer写出到数据文件上.

但是注意,由于dirty list上的buffer并没有顺序,有的buffer反复被修改,在链表上的位置就可能发生变化,当检查点发生时,oracle需要将脏缓冲列表上的数据全部写出到数据文件.为了区分,在oracle8之前,oracle实施的这类检查点通常被称为常规检查点(Conventional checkpoint),由于检查点时需要写出全部的脏数据,所以也被称为完全检查点(complete checkpoint),常规检查点按特定的条件触(log_checkpoint_interval,
log_checkpoint_timeout参数设置及log switch等条件触发),触发时会同时更新数据文件头以及控制文件记录检查点信息.

从oracle8开始,oracle引入了增量检查点(Incremental checkpoint)的概念.和以前的
版本相比,在新版本中,主要的变化是引入了检查点队列(checkpoint queue ckptq)机制.在数据库内部,每一个脏数据块都会被记录到检查点队列中,按照LRBA(Low RBA,第一次对此数据块修改所对应的redo byte address)的顺序来排列,如果一个数据块进行过多次修改,该数据块在检查点队列上的顺序并不会发生变化(相对LRBA,
后面修改的RBA被称为HRBA)

当执行增量检查点时,dbwr从检查点队列按照Low RBA的顺序写出,此时先修改的数据就可以被按顺序优先写出,实例检查点因此可以不断增进,同时,ckpt进程也阶段性地使用非常轻量级的控制文件更新协议,将当前的最低的RBA写控制文件.为了减少频繁增量检查点的性能影响,ckpt在进行轻量级更新时,并不会改写控制文件中数据文件
的检查点信息以及数据文件头信息.而只是记录控制文件检查点scn(controlfile checkpoint at scn)并且根据增量检查点的写出增进RBA信息.

通过增量检查点,数据库可以将以前的全量写出变更为增量渐进写出,从而可以极大工减少对于数据库性能的影响;而检查点队列则进一步地将RBA和检查点关联起来,从而可以通过检查点来确定恢复的起点.

检查点队列在数据库内部通过latch保护:

select name,gets,misses from v$latch where name='checkpoint queue latch';

SQL> select name,gets,misses from v$latch where name='checkpoint queue latch';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
checkpoint queue latch                                  86382          0

checkpoint queue latch存在多个子latch,可以通过v$latch_children视图查询:

select name,gets,misses from v$latch_children where name='checkpoint queue latch';

SQL> select name,gets,misses from v$latch_children where name='checkpoint queue latch';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                  11374          0
checkpoint queue latch                                  11724          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0

16 rows selected.

除了检查点队列(ckptq)之外,数据库中还存在另外一个队列和检查点相关,这就是文件检查点队列(file queue),通常缩写为fileq,文件检查点队列的引入提高了表空间检查点(tablespace checkpoint)的性能.每个dirty buffer同时链接到这两个队列,ckptq包含实例所有需要执行检查点的buffer,fileq包含属于特定文件需要执行检查点的buffer,每个文件都包含一个文件队列,在执行表空间检查点请求时需要使用fileq,通常当对表空间执行offline等操作时会触发表空间检查点.

在buffer cache中,每个buffer的header上都存在ckptq以及fileq队列信息,通过如下命令可以转储buffer cache信息(注意应发仅在测试环境中尝试);

alter session set events 'immediate trace name buffers level 10';

SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4239.trc

以下bh信息来自oracle10g

    BH (0x24ff61dc) file#: 3 rdba: 0x00c00a6c (3/2668) class: 1 ba: 0x24eee000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 8780 objn: 8780 tsn: 2 afn: 3
      hash: [29115d3c,29115d3c] lru: [24ff62e0,24ff60d0]
      lru-flags:
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      st: XCURRENT md: NULL tch: 35
      flags: buffer_dirty gotten_in_current_mode block_written_once
              redo_since_read
      LRBA: [0x5.2e7.0] HSCN: [0x0.11546d] HSUB: [1]
      buffer tsn: 2 rdba: 0x00c00a6c (3/2668)
      scn: 0x0000.0011546d seq: 0x01 flg: 0x02 tail: 0x546d0601
      frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

注意信息中的ckptq和fileq,这就是检查点队列和文件队列,每个队列后面记录了两个地址信息,分别是前一块以及下一块的地址,通过这个信息ckptq和fileq构成了双向链表.注意仅仅只有dirty buffer才会包含ckptq信息,否则为null,信息类似如下

    BH (0x24be637c) file#: 1 rdba: 0x004096b0 (1/38576) class: 1 ba: 0x2480a000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 18 objn: 18 tsn: 0 afn: 1
      hash: [290c0878,290c0878] lru: [24be6320,24be6480]
      lru-flags: moved_to_tail
      ckptq: [NULL] fileq: [NULL] objq: [24be6374,24be64d4]
      st: XCURRENT md: NULL tch: 3
      flags: only_sequential_access
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x004096b0 (1/38576)
      scn: 0x0000.00030101 seq: 0x01 flg: 0x04 tail: 0x01010601
      frmt: 0x02 chkval: 0x8c8d type: 0x06=trans data

[oracle@jingyong udump]$ grep ckptq jingyong_ora_4239.trc |grep -v NULL
      ckptq: [26c13b04,233f6364] fileq: [237e9e1c,237e9d6c] objq: [237e9de4,237ea0a4]
      ckptq: [233f6414,237e6be4] fileq: [233f641c,237e6bec] objq: [237e6c64,233f6494]
      ckptq: [237e9aa4,237e9cb4] fileq: [237e9aac,237e9cbc] objq: [237e9d34,27b8afa8]
      ckptq: [237e6c94,233f6154] fileq: [237e6c9c,233f615c] objq: [233f61d4,237e6d14]
      ckptq: [237e9944,237ea0d4] fileq: [237e994c,237ea0dc] objq: [237ea154,237e99c4]
      ckptq: [29150a60,237e9944] fileq: [29150a74,237e994c] objq: [27b1cc58,27b1cc58]
      ckptq: [26c13b04,26c18194] fileq: [26c13b0c,26c1819c] objq: [26c18214,26c13b84]
      ckptq: [26c12924,291529c8] fileq: [26c1292c,29152a40] objq: [27b1d0b8,26c129a4]
      ckptq: [26c18034,26c12874] fileq: [26c1803c,26c1287c] objq: [26c128f4,26c180b4]
      ckptq: [291529c8,26c12be4] fileq: [29152a40,26c12bec] objq: [26c12c64,27b1d0b8]
      ckptq: [233f6204,26c18034] fileq: [233f620c,26c1803c] objq: [26c180b4,233f6284]
      ckptq: [26c12b34,233f6204] fileq: [26c12b3c,233f620c] objq: [233f6284,26c12bb4]
      ckptq: [26c12be4,26c127c4] fileq: [26c12bec,26c127cc] objq: [26c12844,26c12c64]
      ckptq: [26c12874,233f6364] fileq: [26c1287c,233f636c] objq: [233f63e4,26c128f4]
      ckptq: [26c127c4,233f6414] fileq: [26c127cc,233f641c] objq: [233f6494,26c12844]
      ckptq: [29150a60,24ff50d4] fileq: [29150a9c,24ff50dc] objq: [27b37f18,27b37f18]
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      ckptq: [233f6364,26c12924] fileq: [233f636c,26c1292c] objq: [26c129a4,233f63e4]

在sga中存在一块内存区域用于记录这个检查点队列

select name,bytes from v$sgastat where upper(name) like '%CHECKPOINT%';

SQL> select name,bytes from v$sgastat where upper(name) like '%CHECKPOINT%';

NAME                            BYTES
-------------------------- ----------
Checkpoint queue               128320

从oracle10g开始,数据库中额外增加了对象检查点队列(object queue,objq)用于记录对象检查点信息:

    BH (0x24ff61dc) file#: 3 rdba: 0x00c00a6c (3/2668) class: 1 ba: 0x24eee000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 8780 objn: 8780 tsn: 2 afn: 3
      hash: [29115d3c,29115d3c] lru: [24ff62e0,24ff60d0]
      lru-flags:
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      st: XCURRENT md: NULL tch: 35
      flags: buffer_dirty gotten_in_current_mode block_written_once
              redo_since_read
      LRBA: [0x5.2e7.0] HSCN: [0x0.11546d] HSUB: [1]
      buffer tsn: 2 rdba: 0x00c00a6c (3/2668)
      scn: 0x0000.0011546d seq: 0x01 flg: 0x02 tail: 0x546d0601
      frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

共享池中分配了相关内存用于OBJECT QUEUE:

select * from v$sgastat where name like 'object queue%';
SQL> select * from v$sgastat where name like 'object queue%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  object queue hash table d        3040
shared pool  object queue hash buckets       69632
shared pool  object queue                    16352

下面来看一下控制文件以及增量检查点的协同工作,以下输出来自oracle10g,两次level 8级控制文件的转储.
第一部分重要信息是控制文件的seq号,控制文件随着数据库的变化而增进版本.

[oracle@jingyong udump]$ diff jingyong_ora_4445.trc jingyong_ora_4463.trc
1c1
< /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4445.trc
---
> /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4463.trc
13c13
< Unix process pid: 4445, image: oracle@jingyong (TNS V1-V3)
---
> Unix process pid: 4463, image: oracle@jingyong (TNS V1-V3)
15,18c15,18
< *** 2013-01-18 05:40:06.832
< *** SERVICE NAME:(SYS$USERS) 2013-01-18 05:40:06.797
< *** SESSION ID:(159.15) 2013-01-18 05:40:06.797
< DUMP OF CONTROL FILES, Seq # 4498 = 0x1192
---
> *** 2013-01-18 05:44:49.284
> *** SERVICE NAME:(SYS$USERS) 2013-01-18 05:44:49.258
> *** SESSION ID:(159.21) 2013-01-18 05:44:49.258
> DUMP OF CONTROL FILES, Seq # 4499 = 0x1193
23c23
< Control Seq=4498=0x1192, File size=450=0x1c2
---
>       Control Seq=4499=0x1193, File size=450=0x1c2
44c44
< Database checkpoint: Thread=1 scn: 0x0000.00114550
---
>  Database checkpoint: Thread=1 scn: 0x0000.00115af8

接下来是控制文件检查点scn,增量检查点不断增进的内容之一:
66c66
< Controlfile Checkpointed at scn:  0x0000.001145c1 01/18/2013 05:38:56
---
>  Controlfile Checkpointed at scn:  0x0000.00115af8 01/18/2013 05:44:37
96,97c96,97

检查点记录之后是RBA信息,检查点和redo相关联在这里实现,通过以下信息可以注意到,通过增量检查点之后,而low cache rba从0x5.307.0增进到0x5.412.0,low cache rba是下一次恢复的起点,而on disk rba则是指已经写入磁盘(redo log file)的rba地址.
这就是前滚恢复能够到达的终点.增量检查点的作用由此体现:

< low cache rba:(0x5.307.0) on disk rba:(0x5.407.0)
< on disk scn: 0x0000.00115a04 01/18/2013 05:33:13
---
> low cache rba:(0x5.412.0) on disk rba:(0x5.442.0)
> on disk scn: 0x0000.00115af9 01/18/2013 05:44:38
99c99

最后一部分是heartbeat心跳信息,每3秒更新一次用于验证实例的存活性:

< heartbeat: 805047415 mount id: 3142683107
---
> heartbeat: 805047509 mount id: 3142683107

通过以上分析可以清晰地看到增量检查点的实施过程,因为增量检查点可以连续进行,所以检查点rba可以比常规点更接近数据库的最后状态,从而在数据库的实例恢复中可以极大地减少恢复时间.而且,通过增量检查点,dbwr可以持续进行写出,从而避免了常规检查点出发的峰值,写入对于I/O的过度征用.

显而易见的是,增量检查点明显优于常规的完全检查点,所以在引入检查点队列之后,数据库正常情况下执行的都是增量检查点,从oracle8i开始,完全检查点仅仅在以下两种情况下出现:
alter system checkpoint;
shutdown(除了abort方式外)
log switch事件同样是触发的增量检查点,但是在log switch触发的检查点会促使数据文件头与控制文件信息的同步

log_checkpoints_to_alert参数
在数据库中,可以设置初始化参数log_checkpoints_to_alert为true,则数据库会将检查点的执行情况记入警告日志文件中,这个参数的初始值为false:

SQL> show parameter checkpoints_to

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE
SQL> alter system set log_checkpoints_to_alert=true;

System altered.

当数据库执行各类检查点进,日志文件中会记录详细信息,以下是来自oracle 10g的警告日志文件中的信息摘录.注意以下信息中,共发生了两次检查点,触发条件都是log switch,在日志中,注意rba信息和检查点scn同时出现,这就是检查点队列的作用,log switch检查点的特别之处在于,需要同时在控制文件和数据文件头上标记检查点进度

[oracle@jingyong bdump]$ tail -20 alert_jingyong.log
Fri Jan 18 06:12:00 2013
Beginning log switch checkpoint up to RBA [0x8.2.10], SCN: 1138121
Thread 1 advanced to log sequence 8
  Current log# 2 seq# 8 mem# 0: /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
Fri Jan 18 06:12:21 2013
Beginning log switch checkpoint up to RBA [0x9.2.10], SCN: 1138140
Thread 1 advanced to log sequence 9
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
Fri Jan 18 06:17:12 2013
Completed checkpoint up to RBA [0x8.2.10], SCN: 1138121
Fri Jan 18 06:17:30 2013
Completed checkpoint up to RBA [0x9.2.10], SCN: 1138140

从以上信息还可以观察到,检查点的触发和检查点完成具有一定的时间间隔,这进一步说明,检查点仅仅是一个数据库事件,发生检查点时ckpt进程负责通知dbwr执行写出,但是检查点不会等待写出完成,它会在下一次触发时写出上一次成功完成的检查点信息.

在警告日志文件中,可能还会看到Incremental checkpoint的信息,这些信息和检查点的另外一个触发条件有关.为了保证检查点不会滞后整个日志文件,oracle限制最长的检查点跨度不超过最小日志大小的90%.所以数据库在运行过程中会根据log tail进行计算,主动触发增量检查点.

Proudly powered by WordPress | Indrajeet by Sus Hill.