【导语】“nevermind”通过精心收集，向本站投稿了8篇优化Oracle库表设计的若干方法数据库教程，下面是小编整理后的优化Oracle库表设计的若干方法数据库教程，欢迎您能喜欢，也请多多分享。
- 目录
篇1：优化Oracle库表设计的若干方法数据库教程
oracle|设计|优化
前言绝大多数的Oracle数据库性能问题都是由于数据库设计不合理造成的，只有少部分问题根植于Database Buffer、Share Pool、Redo Log Buffer等内存模块配置不合理，I/O争用，CPU争用等DBA职责范围上，所以除非是面对一个业已完成不可变更的系统，否则我们不应过多地将关注点投向内存、I/O、CPU等性能调整项目上，而应关注数据库表本身的设计是否合理，库表设计的合理性才是程序性能的真正执牛耳者。
合理的数据库设计需要考虑以下的方面：
・业务数据以何种方式表达。如一个员工有多个Email，你可以在T_EMPLOYEE表中建立多个Email字段如email_1、email_2、email_3，也可以创建一个T_EMAIL子表来存储，甚至可以用逗号分隔开多个Email地址存放在一个字段中。
・数据以何种方式物理存储。如大表的分区，表空间的合理设计等。
・如何建立合理的数据表索引。表索引几乎是提高数据表查询性能最有效的方法，Oracle拥有类型丰富的数据表索引类型，如何取舍选择显得特别重要。
本文我们将目光主要聚焦于数据表的索引上，同时也将提及其他两点的内容。通过对一个简单的库表设计实例的分析引出设计中的不足，并逐一改正。考虑到手工编写库表的SQL脚本原始且低效，我们将用目前最流行的库表设计工具PowerDesigner 10来讲述表设计的过程，所以在本文中你还会了解到一些相关的PowerDesigner的使用技巧。
一个简单的例子
某个开发人员着手设计一个订单的系统，这个系统中有两个主要的业务表，分别是订单基本信息表和订单条目表，这两张表具有主从关系的表，其中T_ORDER是订单主表，而T_ORDER_ITEM是订单条目表。数据库设计人员的设计成果如图 1所示：
图 1 订单主从表
ORDER_ID是订单号，为T_ORDER的主键，通过名为SEQ_ORDER_ID的序列产生键值，而ITEM_ID是T_ORDER_ITEM表的主键，通过名为SEQ_ORDER_ITEM的序列产生键值，T_ORDER_ITEM通过ORDER_ID外键关联到T_ORDER表。
需求文档指出订单记录将通过以下两种方式来查询数据：
・CLIENT + ORDER_DATE+IS_SHPPED：根据“客户＋订货日期+是否发货”条件查询订单及订单条目。
・ORDER_DATE+IS_SHIPPED：根据“订货日期+是否发货”条件查询订单及订单条目。
数据库设计人员根据这个要求，在T_ORDER表的CLIENT、ORDER_DATE及IS_SHPPED三字段上建立了一个复合索引IDX_ORDER_COMPOSITE；在T_ORDER_ITEM为外键ORDER_ID建立IDX_ORDER_ITEM_ORDER_ID索引。
让我们看一下该份设计的最终SQL脚本：
/*订单表*/
create table T_ORDER (
ORDER_ID NUMBER(10) not null,
ADDRESS VARCHAR2(100),
CLIENT VARCHAR2(60),
ORDER_DATE CHAR(8),
IS_SHIPPED CHAR(1),
constraint PK_T_ORDER primary key (ORDER_ID)
);
create index IDX_CLIENT on T_ORDER (
CLIENT ASC,
ORDER_DATE ASC,
IS_SHIPPED ASC);
/*订单条目子表*/
create table T_ORDER_ITEM (
ITEM_ID NUMBER(10) not null,
ORDER_ID NUMBER(10),
ITEM VARCHAR2(20),
COUNT NUMBER(10),
constraint PK_T_ORDER_ITEM primary key (ITEM_ID)
);
create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM (
ORDER_ID ASC);
alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key (ORDER_ID) references T_ORDER (ORDER_ID);
我们承认在ER关系上，这份设计并不存在的缺陷，但却存在以下有待优化的地方：
・没有将表数据和索引数据存储到不同的表空间中，而不加区别地将它们存储到同一表空间里。这样，不但会造成I/O竞争，也为数据库的维护工作带来不便。
・ORACLE会自动为表的主键列创建一个普通B-Tree索引，但由于这两张表的主键值都通过序列提供，具有严格的顺序性（升序或降序），此时手工为其指定一个反键索引（reverse key index）将更加合理。
・在子表T_ORDER_ITEM外键列ORDER_ID上建立的IDX_ORDER_ITEM_ORDER_ID的普通B-Tree索引非常适合设置为压缩型索引，即建立一个压缩型的B-Tree索引。因为一份订单会对应多个订单条目，这就意味着T_ORDER_ITEM表存在许多同值的ORDER_ID列值，通过将其索引指定为压缩型的B-Tree索引，不但可以减少IDX_ORDER_ITEM_ORDER_ID所需的存储空间，还将提高表操作的性能。
・企图仅通过建立一个包含3字段IDX_ORDER_COMPOSITE复合索引满足如前所述的两种查询条件方式的索引是有问题的，事实上使用ORDER_DATE+IS_SHIPPED复合条件的查询将利用不到IDX_ORDER_COMPOSITE索引。
优化设计
1、将表数据和索引数据分开表空间存储
1.1 表数据和索引为何需要使用独立的表空间
Oracle强烈建立，任何一个应用程序的库表至少需要创建两个表空间，其中之一用于存储表数据，而另一个用于存储表索引数据。因为如果将表数据和索引数据放在一起，表数据的I/O操作和索引的I/O操作将产生影响系统性能的I/O竞争，降低系统的响应效率。将表数据和索引数据存放在不同的表空间中（如一个为APP_DATA，另一个为APP_IDX），并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上，就可以避免这种竞争了。
拥有独立的表空间，就意味着可以独立地为表数据和索引数据提供独立的物理存储参数，而不会发生相互影响，毕竟表数据和索引数据拥有不同的特性，而这些特性又直接影响了物理存储参数的设定。
此外，表数据和索引数据独立存储，还会带来数据管理和维护上的方面。如你在迁移一个业务数据库时，为了降低数据大小，可以只迁出表数据的表空间，在目标数据库中通过重建索引的方式就可以生成索引数据了。
1.2 表数据和索引使用不同表空间的SQL语法
指定表数据及索引数据存储表空间语句最简单的形式如下。
将表数据存储在APP_DATA表空间里：
create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace APP_DATA;
将索引数据存储在APP_IDX表空间里：
create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;
1.3 PowerDesigner中如何操作
1) 首先，必须创建两个表空间。通过Model->Tablespace...在List of Tablespaces中创建两个表空间：
图 2 创建表空间
2) 为每张表指定表数据存储的表空间。在设计区中双击表，打开Table Properties设计窗口，切换到options 页，按图 3所示指定表数据的存储表空间。
图 3 指定表数据的存储表空间
3) 为每个索引指定索引数据的存储表空间。在Table Properties中切换到Indexes页，在这里列出了表的所有索引，双击需设置表空间的索引，在弹出的Index Properties窗口中切换到Options页，按如下方式指定索引的存储表空间。
图 4 指定索引数据的存储表空间
将表空间的问题延展一下：一个应用系统库表的表空间可以进行更精细的划分。
首先，如果表中存在LOB类型的字段，有为其指定一个特定的表空间，因为LOB类型的数据在物理存储结构的管理上和一般数据的策略有很大的不同，将其放在一个独立的表空间中，就可方便地设置其物理存储参数了。
其次，需要考虑库表数据的DML操作特性：根据DML（INSERT,UPDATE,DELETE）操作频繁程度，将几乎不发生任何DML操作的数据放在独立的表空间中，因为极少DML操作的表可设置符合其特性的物理参数：如PCTFREE可置为0，其BUFFER_POOL指定为KEEP，以便将数据缓存在KEEP数据缓存区中等等，不一而足。
此外，还可以考虑按业务需要将不同的业务模块分开存放，这主要是考虑到备份问题。假设我们有一部分业务数据重要性很强，而其他的业务数据重要性相对较弱，这样就可以将两者分开存储，以便设置不同的备份策略。
当然，无节制的细化表空间也将带来管理上和部署上的复杂，根据业务需求合理地规划表空间以达到管理和性能上的最佳往往需要更多的权衡。
2、显式为主键列建立反向键索引
2.1 反向键索引的原理和用途
我们知道Oracle会自动为表的主键列建立索引，这个默认的索引是普通的B-Tree索引。对于主键值是按顺序(递增或递减)加入的情况，默认的B-Tree索引并不理想。这是因为如果索引列的值具有严格顺序时，随着数据行的插入，索引树的层级增长很快。搜索索引发生的I/O读写次数和索引树的层级数成正比，也就是说，一棵具有5个层级的B-Tree索引，在最终读取到索引数据时最多可能发生多达5次I/O操作。因而，减少索引的层级数是索引性能调整的一个重要方法。
如果索引列的数据以严格的有序的方式插入，那么B-Tree索引树将变成一棵不对称的“歪树”，如图 5所示：
图 5不对称的B-Tree索引
而如果索引列的数据以随机值的方式插入，我们将得到一棵趋向对称的索引树，如图 6所示：
图 6对称的B-Tree索引
比较图 5和图 6，在图 5中搜索到A块需要进行5次I/O操作，而图 6仅需要3次I/O操作。
既然索引列数据从序列中获取，其有序性无法规避，但在建立索引时，Oracle允许对索引列的值进行反向，即预先对列值进行比特位的反向，如1000,10001,10011,10111,1100经过反向后的值将是0001,1001,1101,0011。显然经过位反向处理的有序数据变得比较随机了，这样所得到的索引树就比较对称，从而提高表的查询性能。
但反向键索引也有它局限性：如果在WHERE语句中，需要对索引列的值进行范围性的搜索，如BETWEEN、<、>等，其反向键索引无法使用，此时，Oracle将执行全表扫描；只有对反向键索引列进行 和 = 的比较操作时，其反向键索引才会得到使用。
2.2 反向键索引的SQL语句
回到我们上面的例子，由于T_ORDER和T_ORDER_ITEM的主键值来源于序列，主键值是有严格顺序的，所以我们应该摒弃默认的Oracle所提供的索引，而采取显式为主键指定一个反向键索引的方式。
ORDER_ID为T_ORDER表的主键，主键名为PK_ORDER，我们为ORDER_ID列上建立一个反向键索引IDX_ORDER_ID，并使PK_ORDER_ID使用这个索引，其SQL语句如下：
create table T_ORDER (
ORDER_ID NUMBER(10) not null,
CLIENT VARCHAR2(60),
ADDRESS VARCHAR2(100),
ORDER_DATE CHAR(8));
create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse;alter table T_ORDER add constraint PK_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;
要保证创建IDX_ORDER_ID的SQL语句在创建PK_ORDER主键的SQL语句之前，因为主键需要引用到这个反向键索引，
由于主键列的数据是唯一的，所以为IDX_ORDER_ID加上unique限定，使其成为唯一型的索引。
2.3 PowerdDesigner如何操作
1) 首先，需要为ORDER_ID列建立一个反向键索引。打开T_ORDER的Table Properties的窗口，切换到Indexes页，新建一个名为IDX_ORDER_ID的索引。填写完索引的名称后，双击这个索引，弹出Index Properties窗口，在这个窗口的Columns中选择ORDER_ID列。然后，切换到Options页，按图 7的方式将其设置为反向键索引。
图 7 设置反向键索引
2) 显式指定主键PK_ORDER使用这个索引。在Table Properties窗口中切换到Keys页，默认情况下，PowerDesigner为T_ORDER所指定的主键名为Key1，我们将其更名为PK_ORDER，双击这个主键，弹出Key Properties窗口，切换到Options页，按图 8的方式为PK_ORDER指定IDX_ORDER_ID。
图 8 为主键指定特定的索引
不可否认PowerDesigner确实是目前业界最强大易用的数据库设计工具，但很遗憾，当我们为表主键指定一个索引时，其产生的语句在顺序上有问题：即创建主键的语句位于创建索引语句之前：
create table T_ORDER (…);alter table T_ORDER add constraint PK_T_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse;
我们可以通过对PowerDesigner生成SQL语句的设置进行调整，先生成创建表和索引的SQL语句，再创建为表添加主键和外键的SQL语句来达到曲线救国的目的，请看下一步。
3)通过菜单Database->Generate Database...调出Database Configuration窗口，切换到Keys&Indexes页，按图 9设置：
图 9 设置生成键和索引SQL的选项
这里，我们将Primary Keys和Foreign keys的选项都取消，而将Indexes勾选，以达到只生成表的索引SQL语句的目的。
点击“确定”后，生成创建数据库表及其索引的SQL语句，运行该SQL创建数据库后，再按图 10设置生成为表添加主键和外键的SQL语句：
图 10 生成创建表主键和外键的SQL语句
除此设置外，还必须切换到Tables & Views页下，取消所有选项，避免重新生成创建表的语句。
3、将子表的外键列的索引改为压缩型
3.1 压缩型索引的原理和用途
在前面的例子中，由于一条订单会对应多条订单条目，所以T_ORDER_ITEM的ORDER_ID字段总会出现重复的值，如：
ITEM_ID ORDER_ID ITEM COUNT
1 100 101 1
2 100 104 2
3 100 201 3
4 200 301 2
5 200 401 1
6 200 205 3
在ORDER_ID列上创建一个普通未压缩的B-Tree索引，则索引数据的物理上的存储形式如下：
图 11 未进行压缩的索引存储
ORDER_ID的重复值在索引块中重复出现，这样不但增加了存储空间的需求，而且因为查询时需要读取更多的索引数据块，所以查询性能也会降低＝。让我们来看一下经过压缩后索引数据的存储方式：
图 12 进行压缩的索引存储
压缩型的索引消除了重复的索引值，将相同索引列值所关联的ROWID存储在一起。这样，不但节省了存储空间，查询效率也提高了，真可谓两全齐美了。
对象T_ORDER和T_ORDER_ITEM这样的主从表进行查询时，一般情况下，我们都必须通过外键查询出子表所有关联的记录，所以在子表的外键上建立压缩型的索引是非常适合的。
3.2 压缩型索引的SQL语句
创建压缩型索引的SQL语句非常简单，在T_ORDER_ITEM的ORDER_ID上创建压缩型索引的SQL如下所示：
create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC) compress;
需要在创建索引的语句后附上compress关键字就可以了。
3.3 PowerDesigner如何创建压缩型索引
1) 打开T_ORDER_ITEM表的Table Properties的窗口，切换到Indexes页，为ORDER_ID列创建一个名为IDX_ORDER_ITEM_ORDER_ID的索引。
2) 双击IDX_ORDER_ITEM_ORDER_ID弹出Index Properties窗口，切换到Options页，按图 13将索引设置为压缩型：
图 13 将索引指定为压缩型
4、建立满足需求的复合键索引
设计人员希望通过T_ORDER表上的IDX_ORDER_COMPOSITE复合索引满足以下两种组合条件的查询：
・CLIENT + ORDER_DATE + IS_SHIPPED
・ORDER_DATE + IS_SHIPPED
为方便阐述，我们特地将IDX_ORDER_COMPOSITE的创建SQL语句再次列出：
create index IDX_ORDER_COMPOSITE on T_ORDER ( CLIENT ASC, ORDER_DATE ASC, IS_SHIPPED ASC);
事实上，在CLIENT + ORDER_DATE + IS_SHIPPED 三列上所执行的复合条件查询会应用到这个索引，而在ORDER_DATE + IS_SHIPPED列上所执行的复合查询不会使用这个索引，因而将导致一个全表扫描的操作。
可以用许多工具来了解查询语句的执行计划，通过SET AUTOTRACE ON来查询以上两个复合查询的执行计划：
打开SQL/Plus，输入以下的语句：
SQL>set autotrace on
SQL>select * from t_order where CLIENT = '1' and ORDER_DATE='1' and IS_SHIPPED='1';
分析得到的执行计划为：
SELECT STATEMENT ptimizer=CHOOSETABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' INDEX (RANGE SCAN) OF 'IDX_ORDER_COMPOSITE' (NON-UNIQUE)
可见Oracle先利用IDX_ORDER_COMPOSITE得到满足条件的记录ROWID，再通过ROWID返回记录。
而下面查询语句：
SQL>select * from t_order where ORDER_DATE='1' and IS_SHIPPED='1'
的执行计划则为：
SELECT STATEMENT ptimizer=CHOOSE TABLE ACCESS (FULL) OF 'T_ORDER'
很明显，Oracle在T_ORDER表上执行了一个全表扫描的操作，没有用到IDX_ORDER_COMPOSITE索引。
对复合列索引，我们得出这个结论：
假设在COL_1,COL_2,…,COL_n这些列上建立了一个复合索引：
create index IDX _COMPOSITE on TABLE1
{
COL_1,
COL_2,
…,
COL_n
}
则只有WHERE语句上包含COL_1(复合索引的第一个字段)的查询才会使用这个复合索引，而未包含COL_1的查询则不会使用这个复合索引。
回到我们的例子，如何建立满足CLIENT + ORDER_DATE + IS_SHIPPED和ORDER_DATE + IS_SHIPPED两种查询的索引呢？
考虑到IS_SHIPPED列基数很小，只有两个可能的值：0,1。在这种情况下，有两种方案：第一，分别为CLIENT + ORDER_DATE + IS_SHIPPED和ORDER_DATE + IS_SHIPPED建立一个复合索引；第二，分别在CLIENT和ORDER_DATE列上建立一个索引，而IS_SHIPEED列不建立索引。
第一种方案的查询效率最快，但因为CLIENT和ORDER_DATE在索引中会重复出现两次，占用较大的存储空间。第二种方案CLIENT和ORDER_DATE不会在索引存储出现两次，较为节省空间，查询效率比之于第一种方案会稍低一些，但影响不大。
我们采用第二种方案为CLIENT和ORDER_DATE分别创建索引IDX_CLIENT和IDX_ORDER_DATE，组合查询条件为CLIENT + ORDER_DATE + IS_SHIPPED时的执行计划为：
SELECT STATEMENT ptimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' AND-EQUAL INDEX (RANGE SCAN) OF 'IDX_CLIENT' (NON-UNIQUE) INDEX (RANGE SCAN) OF 'IDX_ORDER_DATE' (NON-UNIQUE)
而组合条件为ORDER_DATE + IS_SHIPPED时的执行计划为：
SELECT STATEMENT ptimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' INDEX (RANGE SCAN) OF 'IDX_ORDER_DATE' (NON-UNIQUE)
通过这样的改造，我们得到了一个满足两种组合查询的执行计划。
总结
贯穿本文的订单主从表实例结构上很简单，但是其粗糙的设计包含了许多问题，这也是许多对Oracle物理存储结构没有很好理解的数据库设计师容易忽视的地方。
在一般情况下，这样的设计并不会导致严重系统的性能问题，但是精益求精是每一位优秀软件设计师的品质，此外，对于设计师，一定要清楚这样一条规律：对于等质的性能提升，在编码层面往往需要比设计层面付出更多的艰辛。
在Oracle中提高数据库的性能需要考虑的问题，注意的误区还很多，本文涵盖是一些最常见的问题。下面，我们将提高数据库操作性能方法及一些误区作个小结：
・对于大表，可以考虑创建分区表，分区表有范围分区、散列分区、列表分区和散列分区几种，通过它可以达到化大表为小表的目的。
・考虑适量的数据冗余，如一个业务表有一个审批状态，审批需要经过多步，每一步对应审批表的一条记录，最后审批的那条记录决定了业务的状态。我们大可在业务表中存放一个审批状态的标志，以取消每次需要通过关联审批表获取业务审批状态的复杂的关联表查询。
・不要做太多的关联表查询，一些几乎不发生数据变动的表码表，如性别，学历，婚姻状态等表码表，可以考虑在应用程序启动时一次性地下载到应用程序的内存中缓存起来，在从数据库获取结果集后，再由程序利用这些缓存的表码表数据来翻译这些表码字段，而不要在数据库中通过表间的关联查询方式来翻译这些字段。
・常看到一些令我瞠目的设计：在需要进行频繁DML(INSERT,UPDATE,DELETE)操作的表的某些基数低的字段（如性别，婚姻状态）上创建位图索引。位图索引是好东西，但它是有使用范围的，在OLTP系统中，需要进行频繁DML操作的表中不应该出现位图索引，位图索引只适用于几乎不进行DML操作，只进行查询的DSS系统中。此外，聚簇和索引组织表也都更适合DSS系统，而非O
篇2：说一说 Oracle库的Hang数据库教程
oracle
有些时侯，我当然指的是非常少的情况下，我们会感觉我们的库”Hang”了，加了隐号的意思是说有时真的是Hang了，有一些则不是，是由于性能的问题引起的，我遇到过几次Hang的情况，结合着网上一些文章，把可能的原因、当时我们应该做的一些操作进行了如下的总结，不对的地方大家可以发Mail给我:qiuyb@21cn.com。
一、数据库Hang时可能的现象
1、最直观的是你的大部分的业务操作，比如说一个查询都使用好长的时间，或根本就返回不出结果。这和简单那种锁表是有区别的。
2、在操作系统上用Hp-unix用glance、Aix用nmon及用sar做监测会出现系统空闲的假象，表面看起来系统很闲，实际上系统已经Hang了。
3、查v$session_wait会出现大量的”latch free”、”enqueue”、” free buffer waits”等等待事件，有时后台会出现大量的.trc文件，另外需要观注一下$ORACLE_HOME/rdbms/log这个位置，有一些时侯trace文件会生成到这里。
二、Oracle库Hang时一些有用的操作和查询
1、如果要寻求Oracle的技术支持，我们需要dump一下Oracle的systemstate，操作如下
SQL>conn / as sysdba;
SQL>alter session set events 'immediate trace name SYSTEMSTATE level 10';
需要等几分钟的时间，这时在init
2、捕获一些视图的状态值
SQL>conn / as sysdba;
SQL>set linesize 500
SQL>set pagesize 0
SQL>spool v_views.txt
SQL>SELECT * FROM v$parameter;
SQL>SELECT class, value, name FROM v$sysstat;
SQL>SELECT sid, id1, id2, type, lmode, request FROM v$lock;
SQL>SELECT l.latch#, n.name, h.pid, l.gets, l.misses, l.immediate_gets, l.immediate_misses, l.sleeps FROM v$latchname n, v$latchholder h, v$latch l WHERE l.latch# = n.latch# AND l.addr = h.laddr(+);
SQL>SELECT * FROM v$session_wait ORDER BY sid; --隔几秒重复执行3次，
SQL>spool off
3、有条件的话用statspack生成了一个Report，如果你对statspach不熟悉，可以参照www.itpub.net/showthread.php?s=&threadid=144448这个链接。
三、产生Hang可能的几个原因
1、开归档的情况下，归档位置所在的文件系统满了，这时lgwr就会等待归档进程的完成，DML写不了日志，都处于等待的状态。
2、在HP的系统开异步Io时，没为dba的组设置MLOCK权限。正常来说Oracle的启动是会报错的，可是有些时侯比如Oracle8.1.6的版本就不报错启动，但是此时的异步Io是有问题的，仔细查看你会在$ORACLE_HOME/rdbms/log看到大量的.trc文件。这种情形引的Hang我遇到过。
3、由于异常进程引起的，我所说的是那些占用系统资源(cpu,memory)特别大的进程，这些进程一般占用cpu会达到90%以上，相对其它的进程比较突出。可以通过hp-unix的glance、topas，IBM的nmod,topas,ps等工具进行监测。
4、由于主机系统、阵列的某方面的瓶颈引起的，在实际中最普遍的应该是Io的问题，比如IO方面有瓶颈，则dbwr、lgwr就会引” free buffer waits”、” log buffer space”等一系列的等待事件，此时是性能的问题，感觉起来像Hang。
5、也有bug的因素，我的库都是最新的补丁，这方面的问题还没遇到过。
四、几点处理建议
当得到Oracle的库奇慢或Hang时保持冷静的头脑很重要的，可以按如下的方法进行一些处理：
1、首先要到操作系统上去，用glance等工具进行一下观察，看一下cpu、内存、交换区、磁盘的繁忙程度与平时是不是相似的，同时看一看有没有占系统资源特别大的进程，有些时侯是这些进程引起的，通过v$process和v$session两个视图找到这Oracle进程的sid,serial#,把它用Alter system kill session ‘sid,serial#’;杀掉就行了。如果平时同样的时段你的磁盘的繁忙程度是95%，而些时它为20%，当然是假设了，说明Oracle的库没准真的hang了。
2、时间来得及的话执行一下”二”中的语句是很必要的，这样可以把这些信息提交给Oracle的supporter，让你们给你分析一下，查一下原因。
3、如果你的库开归档了，出现Hang的时侯一定要看一下归档日志的文件系统是不是满了，做一个完备一些的网管系统是很必要的。
4、在HP系统上打开异步IO时一定不要忘记为dba这个组加上MLOCK的权限，我就吃过这个亏。
5、时常的观注一下v$session_wait或用statspack进行观注一下等待事件，一般IO相关的事件较多，为Oracle打开异步IO、多加几个写进程及为Oracle数据文件所用的raw device使用条带都是很必要的。与应用的开发商交流也很重要，没准最近新上的一个应用就特耗IO或特占CPU资源。
6、尽量要使用比较稳定的Oracle的版本,比如Oracle8.1.7.4及Oracel9.2.0.4(最新为 9.2.0.5)都很稳定的，可以少去好多的麻烦。
篇3：优化Oracle停机时间及数据库恢复数据库教程
oracle|恢复|数据|数据库|优化
这里会讨论令Oracle停机时间最小化的步骤，优化Oracle停机时间及数据库恢复数据库教程
。各种形式的停机--计划的或者是非计划的--总是不断地发生，一个DBA应该有正确的备份策略，这样在数据库出现问题时就可以更快地恢复。以下是假定的备份策略和数据库的运作条件
控制文件是镜像的
数据库运行在archivelog模式
每个星期都进行冷备份
每日都进行热备份
每日都进行一次全数据库导出
事件1：完整的数据库重构
在这种情形下，你可以使用全数据库导出或者冷热备份结合的方式来重构数据库。要注意的是无论你选择哪种方式，在线redo log中的事务都会丢失。
事件2：恢复部分的表空间
可以使用以下的步骤来恢复：
1、以restrict模式启动数据库
2、重新创建表空间
3、使用最新的全数据库导出来导入，并且使用ignore=y的选项；
4.关闭并且重新以normal的模式启动数据库实例
事件3：丢失一般的数据文件
丢失一般数据文件的恢复步骤根据所丢失的数据文件包含的表空间类型而定；例如：回滚段，用户表空间，索引表空间或者是只读的表空间、你可能会遇到以下的错误：
. 尝试启动数据库并且碰到错误的信息ORA-1157, ORA-1110，可能还有一个操作系统的错误
. 尝试以normal或者immediate的模式关闭数据库，可能会碰到ORA-1116, ORA-1110的错误信息，还有一个系统错误
以下的步骤可以用作恢复：
1、关闭数据库
2、由热备份中恢复丢失的数据文件
3、Startup mount数据库
4、执行以下的查询来得到所有你的在线redo log文件和它们相应的次序和首次修改号：
SELECT X.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG X, V$LOGILE Y
WHERE X.GROUP# = Y.GROUP#;
5、如果得到的CHANGE#比在线redo log最小的FIRST_CHANGE# 还小，那么该文件不能被完全恢复，你可以有两个选择：
. 如果可以接受丢失最近一次冷备份以来的数据库修改，装入备份并且继续恢复
. 如果不能接受丢失数据库的修改，那么必须重新创建表空间
6、通过使用存档和在线的redo log来恢复数据文件
7、打开数据库
事件4：恢复一个特别的表
可以采用以下的步骤恢复：
1、使用最近的一次全数据库导出来导入表，并且使用owner=和tables=的选项
2、考虑到性能的原因，可能需要重建表索引
事件5：丢失控制文件
在数据库起来并且运行时，通常都不能检测到控制文件的问题、如果控制文件丢失或者损坏了，Oracle将不会了解，下次数据库的启动时将会导致ORA-205错误（标识控制文件“%s的错误），还有一个系统级的错误、
如果只是丢失了其中的一个控制文件，可以采用下面的步骤来恢复：
1、如果它正在运行的话，先关闭它
2、查找丢失控制文件的原因、是由于硬件的问题吗（磁盘还是控制器）？
3、如果不是硬件的问题，将控制文件的一个好的拷贝复制到丢失的位置，并且跳到步骤5、
4、如果是硬件的问题，复制一个好的控制文件拷贝到一个可靠的位置
5、编辑initsid.ora 或者 configsid.ora，更新CONTROL_FILES以反映最新的控制文件位置
6、启动数据库
事件6：丢失全部的控制文件
可以采用以下的步骤恢复：
1、关闭数据库
2、进行一次全数据库备份，包括全部的数据文件和redo log文件
3、以NOMOUNT的状态启动数据库
4、使用CREATE CONTROLFILE重新创建控制文件、你也可以备份控制文件到一个trace文件，然后执行该文件
5、在数据库上进行媒体恢复
6、打开数据库
7、使用shutdown normal关闭数据库
8、对数据库进行一次冷备份
事件7：丢失一个索引
最简单的方法就是重新创建丢失的索引
事件8：丢失一个非活动的redo log
如果丢失redo数据，恢复将是不完全的，必须重新创建涉及的表空间。要重新创建表空间，可以使用全的数据库导出，这样就可以很容易的导入数据并且重新创建该表空间的对象。可以使用以下的步骤来恢复：
1、通过Alter system来切换redo log文件
2、关闭数据库
3、startup mount数据库
4、离线删除涉及的数据文件
5、打开数据库
6、删除用户的表空间，包括其中的内容、
7、通过全数据库备份重新创建表空间和其中的对象
事件9：丢失活动的Redo log
如事件8讨论的一样，如果丢失了redo数据，恢复将是不完全的，必须重新创建涉及的表空间、可以采用以下的步骤恢复：
1、关闭数据库
2、startup mount数据库
3、离线删除涉及的数据文件
4、打开数据库
5、删除用户的表空间，包括其中的内容、
6、通过全数据库备份重新创建表空间和其中的对象
要注意的是活动的事务将会丢失
事件10：丢失存档的Redo log文件
如果存档的redo log文件丢失，应该马上进行一次冷备份、最好也进行一次全数据库导出、没有丢失的存档redo log文件的任何恢复都将是不完全的、
事件11：丢失活动的回滚段
这里指的是丢失一个回滚段的一个数据文件、这是一个危急的恢复过程，它主要是在于保存活动的事务，
这里假定数据库已经起来，而你想保存当前运行的事务。要使用以下的恢复过程，数据库必须运行在archivelog模式下。
可以使用以下步骤恢复：
1、不要关闭数据库、对于这种事件，数据库启动比关闭更容易解决问题、
2、令属于该数据文件中的全部回滚段离线
3、删除全部离线的回滚段
4、在上面的第2步中，如果回滚段中有活动的事务，你将不能令它离线、可运行以下的查询来查看哪些事物是活动的：
SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'tablespace_name' AND
SEGMENT_ID = USN;
如果上面的查询没有结果，那么所有的回滚段都是离线的，但是，如果上面的查询返回一行或者多行，并且其状态为PENDING OFFLINE，那么可检查这些回滚段的ACTIVE_TX列、带有0值的回滚段将很快会离线；但是，非0的值表示上面有活动的事务，它们需要被提交或者回滚、
5、处理活动的事务、执行以下的查询来查看哪些用户的事务被指派到该回滚段：
SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME ”ROLLBACK“
FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME IN ('pending_rollback1','pending_rollback2', .... 'pending_rollbackN') AND
S.TADDR = T.ADDR AND
T.XIDUSN = R.USN;
在知道哪些用户在”pending offline“的回滚段上有活动的事务后，可以要求他们提交或者回滚他们的事务，或者可以使用以下的命令杀掉它们的进程：
ALTER SYSTEM KILL SESSION 'sid, serial#';
6、在你处理完所有活动的事务后，执行以下的步骤：
丢弃表空间及其中的全部内容
重新创建回滚表空间
重新创建回滚段，并且令它们在线
事件12：丢失全部的回滚段
在这种事件下，将丢失全部活动的事务，并且需要重新创建回滚段。这样大的问题可能是由于一个硬件问题造成的，可以采用以下的步骤恢复：
1、关闭数据库
2、使用DBVERIFY验证全部的数据文件
3、解决其它的硬件问题或者数据文件损坏
4、以startup mount的方式启动数据库实例
5、在数据库上执行媒体恢复
6、打开数据库
7、按需要创建新的回滚段
事件13：导出文件损坏
如果导出文件不能用了，那么应该冷备份数据库并且进行一个全的数据库导出、这是假定数据库自身没有问题、如果数据库也损坏了，那么应该执行以下的步骤：
1、ORA-1157错误信息通常都表示一个或者多个的数据文件损坏了。查明哪些表受到影响，它们应该是错误信息中指明的数据文件中的表格
2、跳过坏的数据块，将数据由表格中选择到临时表格中、
3、丢弃损坏的表
4、将临时表重命名为丢弃的表
5、重新建立受影响表上的全部索引
6、使用VALIDATE STRUCTURE CASCADE的选项来分析全部损坏的表
要注意的是损坏块中数据将会丢失并且不能恢复
事件14：在热备份时关机
如果在热备份正在进行的时候突然关机，其中的一些表空间将可能处在备份模式、当你尝试打开数据库时，它将只能mount，并且指示某些表空间处于热备份模式、由于数据库不能打开，你将不能让表空间脱离热备份模式、你可以使用以下的步骤恢复：
1、startup mount数据库
2、查询v$backup以查看哪些数据文件处于ACTIVE状态、
3、通过使用命令ALTER DATABASE DATAFILE END BACKUP.来将这些数据文件脱离备份模式
4、打开数据库
事件15：恢复到某个特别的时间点
以下的步骤可用来执行point-in-time恢复
1、关闭数据库实例
2、以NOMOUNT的状态启动数据库实例
3、使用UNTIL的选项来恢复数据库
4、打开数据库
5、Shutdown NORMAL
6、启动数据库实例
事件16：恢复到一个特别的事件或者活动
可以使用以下的步骤来恢复：
1、关闭数据库实例
2、以NOMOUNT状态启动数据库实例；
3、使用UNTIL CANCEL来恢复数据库，提供存档的redo log文件请求直到该活动/事件为止
4、输入CANCEL来取消恢复
5、打开数据库；
6、使用NORMAL的模式来关闭数据库
7、启动数据库实例
结论
高可用性对于任何的商业都是很重要的，ORACLE DBA可以通过一些计划以确保停机时间最小化、这篇文章讨论了不同的策略可以达到这个目的。
篇4：更改Oracle数据库表的表空间数据库教程
oracle|数据|数据库
在Oracle数据库管理系统中，创建库表（table）时要分配一个表空间（tablespace），如果未指定表空间，则使用系统用户确省的表空间，
在Oracle实际应用中，我们可能会遇到这样的问题。处于性能或者其他方面的考虑，需要改变某个表或者是某个用户的所有表的表空间。通常的做法就是首先将表删除，然后重新建表，在新建表时将表空间指定到我们需要改变的表空间。如果该用户已经保存了大量数据，这种办法就就显得不是很方便，因为有大量数据需要提前备份出来。下面介绍一种利用数据库的导出/导入功能来实现重新组织数据库表空间的方法。
下面是一个简单的例子，假定要将用户oa下的全部表从表空间A转换到表空间B，具体步骤（在Oracle 9i for linux环境）如下：
1.1. 导出db_zgxt下的所有表（Dos控制台下） 导出db_zgxt下的所有表（Dos控制台下）1. 导出db_zgxt下的所有表（Dos控制台下）
EXP oa/password@pararmount_server FILE=d:\\10_27_oa.dmp LOG=d:\\10_27_oa.LOG
2. 删除oa下的所有表（在SQL/PLUS中）
可以采用批处理的方式删除掉db_zgxt下的所有表，生成批处理的语句如下：
--其中set head off将表头信息去掉
SET HEAD OFF
SPOOL c:\\drop_tables.sql
select 'drop table '||table_name||';' from user_tables;
spool off;
@c:\\drop_tables.sql;
sql >@drop_tables.sql
3. 采用导入参数 INDEXFILE导入oa用户下的所有表（Dos控制台下）
把建表和索引的语句导出到文件,其中建表语句是加注释的，并没有实际导入
IMP oa/password@paramount_server FULL=Y FILE=d:\\10_27_oa.dmp INDEXFILE=d:\\altertablespace_table_index.SQL LOG=d:\\altertablespace.LOG
其中，指定参数INDEXFILE后，系统就将创建表和索引的语句写到一个文件，这里是altertablespace_table_index.SQL 中，
该文件中包含了所有创建索引（CREATE INDEX）语句和创建表（CREATETABLE）语句，但是这里所有创建表的语句均加了注释标志。在任何文本编辑器中打开并编辑该文件，去掉所有创建表语句的注释标志，将所有的表空间名称由A替换为B，同时对所有的创建索引语句加上注释标志。这些工作作完以后，在SQL/PLUS中运行该脚本文件，这些表就被创建，其表空间由A变为B。
采用导入参数INDEXES=N 和IGNORE=Y将db_zgxt用户的表数据导入库中（Dos控制台下）
4. 采用导入参数INDEXES=N 和IGNORE=Y将oa用户的表数据导入库中（Dos控制台下）
IMP oa/password@paramount_server FULL=Y INDEXES=N FILE=d:\\10_27_oa.dmp IGNORE=Y LOG=d:\\altertablespace.LOG
其中，参数INDEXES=N是指将数据导入数据库中时不加索引。IGNORE=Y是指在导入数据过程中，忽略表已经存在（table already exists）的错误。这样Oralce就将数据和一些约束条件导入到第3步创建的表中。
5. 创建索引
在文本编辑器中重新打开在第3步中创建的altertablespace_table_index.SQL 脚本文件，这次，将所有创建表（CREATE TABLE）的语句加上注释标志，然后将所有的创建索引（CREATE INDEX）语句去掉注释标志。在SQL/PLUS中再次运行该脚本文件。
至此，我们就成功完成了将oa用户下的全部表从表空间A转换到表空间B的工作。当然你可以只导入一部分表。
篇5：Oracle Freelist和HWM原理探讨及相关性能优化数据库教程
oracle|性能|优化
Oracle Freelist和HWM原理探讨及相关性能优化
中兴通讯重庆研究所 游波
关键词：Freelist，HWM，存储参数，段，块，dump，优化
文章摘要：
近期来，FreeList的重要作用逐渐为Oracle DBA所认识，网上也出现一些相关的讨论，本文以FreeList为线索对Oracle的存储管理的原理进行较深入的探讨，涉及Oracle段区块管理的原理，FreeList算法等。而与FreeList密切相关的一个重用特性HWM，与sql性能密切相关，本文也作了原理分析介绍。在原理探讨的基础上，介绍了常用的存储参数分析方法，并对所涉及的存储优化、HWM的优化和Freelist竞争优化作了说明。
缩略语：
ASSM：auto segement space management
HWM：high water mark
DBA：data block address
OLTP：online transaction process
OPS：oracle parallel server
1.简介
Oracle的空间管理和存储参数管理是Oracle管理及优化的重要部分。FreeList作为Oracle底层存储参数中的核心参数，其行为方式对Oracle的存储管理及性能优化有重大影响，而现有的Oracle文档对此方面的内容比较缺乏。虽然Oracle 9i已出现了ASSM，但是作为深入调优对FreeList认识仍是必要的。
近期来，FreeList的重要作用逐渐为Oracle DBA所认识，网上也出现一些相关的讨论。本文以FreeList为线索对Oracle的存储管理的原理进行较深入的探讨，涉及Oracle段区块管理的原理，FreeList算法等。而与FreeList密切相关的一个重用特性HWM，与sql性能密切相关，本文也作了原理分析介绍。在原理探讨的基础上，介绍了常用的存储参数分析方法，并对所涉及的存储优化、HWM的优化和Freelist竞争优化作了说明。
这些原理分析和性能优化都建立在探讨的基础上，限于篇幅和本人经验可能存在局限、偏差或谬误。
为了准确文中部分结构和字段的说明直接用英文描述。
限于篇幅本文不对同样很重要的block结构作更深入的讨论，对OPS性能有重要影响的free list group本文也未提及，因此本文在单一free list group下讨论。对于block的深入讨论、free list group的介绍与优化以及PCTUSED和PCTFREE等重要参数的优化请参见参考文献和资料。
2.原理探讨
FreeList作为一个Oracle存储管理的核心参数。其行为方式由Oralce内部控制，我们一般不需要掌握和控制。但是我们可能会遇到这些问题，当插入一条记录，会插入到那个块中？是使用新块，还是插入有数据的老块？段是什么时候扩展的，如何扩展的？表中只有一条记录，但是作一次select时代价却是上千个块，为什么？如果我们从原理上清楚了Oracle的存储管理方式，对相关这些问题的解决及性能优化就清晰自然了。
2.1 Oracle的逻辑储存结构
Oralce的逻辑存储结构按表空间，段，区，块进行管理。块是Oracle用来管理存储空间的最基本单元，Oracle数据库在进行输入输出操作时，都是以块为单位进行逻辑读写操作的。区由一系列连续的块组成，Oralce在进行空间分配、回收和管理时是以区为基本单位的。段由多个区组成，这些区可以是连续的也可以是不连续的，一般情况下一个对象拥有一个段。表空间中容纳段和区。
在生成段的时候，会同时分配初始区(initial extents)， 初始区的第一个块就格式化为segment header，并被用来记录free list描述信息、extents信息，HWM信息等。
2.2 free list概念
free list是一种单向链表用于定位可以接收数据的块，在字典管理方式的表空间中，Oracle使用free list来管理未分配的存储块。Oracle记录了有空闲空间的块用于insert或Update。空闲空间来源于两种方式：1.段中所有超过HWM的块，这些块已经分配给段了，但是还未被使用。2.段中所有在HWM下的且链入了free list的块，可以被重用。free list具有下列属性
l flag指示free list 被使用(1)或未使用(0)
l free list 链的首块的地址DBA(data block address)
l free list 链的尾块的地址DBA
free list 的信息通常保留在segment header中,这里给出segment header block dump片段加以说明：
nfl = 3, nfb = 1 typ = 1 nxf = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
SEG LST:: flg: USED lhd: 0x03c00233 ltl: 0x03c00233
SEG LST:: flg: USED lhd: 0x03c00234 ltl: 0x03c00234
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
Segment Header:
==>nfl: number of free lists/block
==>nfb: number of free list blocks + segment header
==>typ: block type
==>nxf: number of transaction free lists
Segment List:
==>flg: flag USED or UNUSED the free list
==>lhd: head of free list
==>ltl: tail of free list
在每一个块中都有一个标记flg用来表明块是否链入了 free list链中。如果这个标志置上，该块中后向指针指向free list链中下一个块的DBA。如果当前块是链的最末尾的块，该后向指针值为0。
这里给出位于free list上的block dump的片段
Block header dump: 0x03c00235
Object id on Block? Y
seg/obj: 0xe2d8 csc: 0x00.6264c61 itc: 1 flg: O typ: 1 - DATA
fsl: 1 fnx: 0x3c00234 ver: 0x01
==>Seg/obj Object ID in dictionary
==>csc SCN of last block cleanout
==>itc Number of ITL slots
==>flg O = On freelist , - = Not on freelist
==>typ 1 = DATA 2 = INDEX
==>fsl ITL TX freelist slot
==>fnx DBA of NEXT block on freelist
举例来说如果有五个块在free list中，分别为A,B,C,D,E
就会形成segment header->A->B->C->D->E--|
同时segment header->E
2.3 free list类别
在段中存在3类free list, 即Master Freelists (MFL), Process Freelists (PrFL), 和 Transaction Freelists.
2.3.1 Master Free List(公用空闲空间池):
每一个段中有一个Master free list，在段创建的时候自动生成。对于每一个段来说都有这样一个空闲空间池,对每个进程都是公用的，空闲空间就是位于master free list 的块上。由于Master free list是公用的，因此当多个进程同时插入行到同一个段上,master free list竞争使用程度就会增加。
2.3.2 Process Free Lists
为了减少Master Free list的竞争问题, 引入了另一种free list叫做Process free lists, 根据sql命令 CREATE/ALTER 中的参数FREELISTS 创建. 这样多个free list 就可以分摊空闲空间的管理，以提高OLTP应用作高度并发插入和更新事务时空间分配管理的性能。通过指定CREATE TABLE / CLUSTER or INDEX的子句STORAGE的参数FREELISTS 来创建，例如: CREATE TABLE flg ( . . . .) . . . STORAGE ( ... FREELISTS 10 ...)。缺省的FREELISTS为1，此时不会创建Process free lists。当FREELISTS>=2时，创建Process free lists。
进程在使用process free list是根据进程的Oracle PID (Process ID)来选择的，公式如下:
select list entry = (PID % NFL) + 1
NFL : FREELISTS定义的Process free list个数
2.3.3 Transaction Free Lists
当Oracle需要时动态创建。一个Transaction Free List 是一种专门给某一个事务使用的free list. 每个段至少有16个transactions free lists, 并且这个值在需要时会增长，直到达到Segment Header块的大小限制。一个事务只有下面情况下会需要分配一个Tx Free Lists entry: 块中释放空间时(DELETE or UPDATE) 并且还不存在Tx Free Lists entry时。
2.4 Free list行为2.4.1 Freelist Link and Unlink 操作
Freelist 按后进先出队列(LIFO) 方式管理。也就是说最后被link到freelist的块拥有最先unlink的机会。
当块中空闲空间增加到大于PCTFREE时，块放入freelist中。free list中的块可用来作update 或insert。 当块中没有足够的空间用于insert操作时并且使用空间大于PCTUSED，块就会从free list中移出。
在块在DELETE or UPDATE 操作之后，如果使用空间落到PCTUSED下，块再次link到free list中。每次块加入free list时，都是link到链表的头部。
例如：考虑段中有120个块编号由1到120。其中有6个块在free list上并假设HWM是 80。（block实际使用DBA编号）
10->24->45->46->65->80-|
现在作INSERT 操作,需要400 bytes空间。假设块10上空间不足，但块24上空间可用。现在数据插入到块 24 ，现在块24的剩余空间小于该表的PCTUSED。因此块 24 从free list链表中移出。PCTFREE and PCTUSED参数的目的就是用来控制数据块从free list的链表中移入/移出行为的。现在free lists象这样：
10->45->46->65->80-|
然后在同一事务中作DELETE同一个段的数据，使块 54 和 67落到PCTUSED下。现在这些块加入到free list链中。free list链现在象这样:
67->54->10->45->46->65->80-|
2.4.2 Transaction Free List 算法
扫描segment Header块中所有的Tx free list,检查是否还没有Tx free list entry分配给transaction， 如何没有，将寻找未使用的entry或已经提交了事务的空的Tx free list。如果上述搜索过程失败, 新的entry会在segment Header块中Tx free lists区域中开辟。如果没有空间来生成, 事务就必须等待entry的释放。
segment header中的最大free list个数:
Block Size Max # Freelists
----------- -----------------
2K 24
4K 50
8K 101
16k 204
事务T1释放出来的空闲块(DELETE or UPDATE)的使用 :
l 立即被T1所重用
l 当T1 commit后被其它需要空闲块的事务重用，过程举例如下:
2.5 HMW概念
HIGH WATER MARK代表一个表使用的最大的(top limit)块 。2.1中已经提到HIGH WATER MARK 记录在segment header中，并且在Oracle插入数据时一般增长5个blocks（并非总是5个块，具体参见2.4.2中流程图中HMW增长方式）。
segment header block中与HWM相关信息说明如下:
EXTENT CONTROL:
Extent Header:: spare1: 0 space2: 0 #extents: 13 #blocks: 1429
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x020004d0 ext#: 12 blk#: 275 ext size: 475
#blocks in seg. hdr's freelists: 5
#blocks below: 1229
mapblk 0x00000000 offset: 12
Unlocked
==>spare1: this field is no longer used (old inc#, now always 0)
==>space2: this field is no longer used (old ts#, now always 0)
==>#extents: number of extents allocated to segment
==>#blocks: number of blocks allocated to segment
==>last map: address of last extent map block
0 if extent map is entirely in the segment header
==>#maps: number of extent map block
==>offset: offset to end of extent map
==>HWM dba: address of block at highwater mark
==>ext#: HWM extent number relative to segment
==>blk#: HWM block number within extent
==>ext size: HWM extent size (in blocks)
==>#blocks in seg. hdr's freelists: number of blocks in seg. hdr's free list
==>#blocks below: number of blocks below HWM
==>mapblk dba: dba of extent map block containing HWM extent
is 0 if HWM is in the segment header
==>offset: offset within extent map block
is the ext# if HWM is in segment header
==>Locked by: if locked by a transaction, the xid is displayed
HWM可以说是已经使用过的存储空间和未使用过的存储空间之间的分界线。在表使用过程中，HWM一直向一个方向移动，插入记录时HWM可能会向增加的方向移动，但是删除记录时HWM并不会向相反的方向移动。参见2.4.2。下图显示了某个数据段中HWM的位置情况。
图2.5
HIGH WATER MARK之所以重要是因为它对全表扫描性能的影响，
当实施一个全表扫描时，Oracle会读取所有HIGH WATER MARK下的块即使它们是空块。当HIGH WATER MARK 下有很多unused block时实施全表扫描会增加额外的不必要的I/O。它也会在全局共享区中填充很多很多空块。
3.分析方法
存储参数基本上属于oracle internal的东西，因此oralce并没有提供很好的手段来分析。但是对于DBA来说，还是可以通过block dump和DBMS_SPACE等手段来获取部分信息。
3.1 提取block和free list信息
创建dbms_space使用的存储过程show_space
SQL>
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_last_used_block number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') || p_num );
end;
begin
dbms_space.free_blocks
( segment_owner =>p_owner,
segment_name =>p_segname,
segment_type =>p_type,
partition_name =>p_partition,
freelist_group_id =>0,
free_blks =>l_free_blks );
dbms_space.unused_space
( segment_owner =>p_owner,
segment_name =>p_segname,
segment_type =>p_type,
partition_name =>p_partition,
total_blocks =>l_total_blocks,
total_bytes =>l_total_bytes,
unused_blocks =>l_unused_blocks,
unused_bytes =>l_unused_bytes,
last_used_extent_file_id =>l_LastUsedExtFileId,
last_used_extent_block_id =>l_LastUsedExtBlockId,
last_used_block =>l_last_used_block );
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_last_used_block );
end;
过程已创建。
SQL>create table t1(a char(1000)) storage( freelists 3);
表已创建。
SQL>set serveroutput on;
SQL>exec show_space('T1');
Free Blocks.............................0 <==Number of blocks on freelist
Total Blocks............................5 <==Total data blocks in segment
Total Bytes.............................20480 <==Total bytes in segment
Unused Blocks...........................4 <==Total unused blocks in segment
Unused Bytes............................16384 <==Total unused bytes in segment
Last Used Ext FileId....................15 <==File id of last used extent
Last Used Ext BlockId...................562 <==Block id of last used extent
Last Used Block.........................1 <==Last used block in extent
PL/SQL 过程已成功完成。
有关show_space的进一步使用技巧可参考文献5。以下利用上面得到的数据对segment header block进行dump。
SQL>alter system dump datafile 15 block 562;
在udump/ora10792.trc中
*** 2004-09-08 15:29:57.343
Start dump data blocks tsn: 27 file#: 15 minblk 562 maxblk 562
buffer tsn: 27 rdba: 0x03c00232 (15/562)
scn: 0x0000.064560e4 seq: 0x02 flg: 0x00 tail: 0x60e41002
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 space2: 0 #extents: 1 #blocks: 4
last map 0x00000000 #maps: 0 offset: 2080
Highwater:: 0x03c00233 ext#: 0 blk#: 0 ext size: 4
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 60033 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x03c00233 length: 4
nfl = 3, nfb = 1 typ = 1 nxf = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 27 file#: 15 minblk 562 maxblk 562
对于上述块中字段的说明，以及相关试验。由于篇幅所限，本文不再列举。可参考文献7。
对非segment header的data block的dump方法和上述类似。data block的结构和segment header block不一样，如果需要了解，可查阅参考文献和资料。
3.2 提取HWM信息3.2.1 HWM位置
HWM位置按下面的公式计算:
HWM = useed byte = Total Bytes - Unused Blocks
Total Bytes和Unused Blocks都可以用show_space提取。
还可以通过ANALYZE tables得到HWM信息. DBA_TABLES视图中包含了可用于各表空间分析的列。其中blocks代表已使用过的块即HWM，empty_blocks代表未使用的空间。
3.2.1 HWM下空间利用信息
要比较有数据行的块的块数和HIGH WATER MARK下总块数，可以用下面的公式来展示HWM下未用空间的比例。
p = 1- r/h
r:有数据行的块的块数
h:HWM下的块数.
r可以通过如下方法获得：
Oracle7:
SELECT count(distinct substr(rowid, 15,4) || substr(rowid, 1,8) ) FROM schema.table;
Oracle8 and Oracle9:
SELECT count(distinct substr(rowid, 7,3) || substr(rowid, 10,6) ) FROM schema.table;
如果公式计算的结果 p是0，就不需要对表进行重建。如果结果p大于0,应该考虑系统状况和应用需要来决定是否需要总组表。
4.优化4.1手工回收存储空间
在HIGH WATER MARK以上的块对性能没有影响，但是会耗费空间。如何空间大小是一个考虑的问题，就可以决定回收空块。
假设表T1的存储示意图如图2.5所示，使用ALTER TABLE ... DEALLOCATE UNUSED语句可以回收HWM以上的空间。比如：
alter table t1 deallocate unused;
回收后T1的存储示意如图4.1.1
图4.1.1
如果在ALTER TABLE ... DEALLOCATE UNUSED语句中使用了KEEP关键字，则可以在HWM之后保留指定大小的空闲空间，比如：
alter table t1 deallocate unused keep 10K;
回收后T1的存储示意如图4.1.2
图4.1.2
4.2删减表
根据3.2.1可以得到HWM以下块的使用情况。如何p大于时，对全表扫描性能会产生影响，同时也会耗用空间。
如果能够确认应用有良好的索引几乎不会用到全表扫描，那么HIGH WATER MARK以下的空块，尽管耗费了空间，不会对访问产生影响。如果不能确定，那么就需要考虑删减表。
删减表的操作将删除表中所有的记录，并且重置HWM标记。表在删减之后将成为一个空表。
在Oracle中删减表只有如下的两种办法：
1.使用drop语句
先使用drop语句删除整个表，然后再重建这个表。在删除-重建的过程中，与表相关的所有索引、完整性约束以及触发器都会丢失，并且所有依赖于该表的对象都会变为INVALID状态，同时原来争对表的授权也会失效。因此采用这种方式删除表中的记录代价太大。
2．使用TRUNCATE语句
TRUNCATE语句属于DDL语句，不会产生任何回退信息，并且被立即自动提交。在执行TRUNCATE语句时不会影响到与被删减表相关的任何数据库对象与授权，也不会触发表中所定义的触发器。此外，在对标进行删减时，HWM将重置，已经为表分配的存储空间将被回收。
在执行TRUNCATE语句时，可以通过drop storage子句和reuse storage子句来控制被释放的区是否回收到表空间中。如何作在线系统的TRUNCATE，不希望表长时间锁住，那么可以使用reuse storage子句，仅将HWM重置。
4.3 free list优化
free list 竞争出现在多个进程使用同一个free list并试图同时修改free list头部数据块时。可以通过查询视图v$waitsate的class类型为data block 的记录来检查竞争情况。
产生data block类型竞争的主要原因是多个进程试图同时修改free list头部数据块。 然而，它也会出现在当进程准备将块读入buffer cathe时，另一个进程需要访问同一个块。如果能在V$SESSION_WAIT中正好捕获buffer busy waits，就可以通过查询V$SESSION_WAIT中的P3来判定是那一类。A 0 或 1014代表读类型，其他的值为修改竞争的类型。
下一步需要确定竞争涉及那些段。 如果能够在V$SESSION_WAIT捕获waits，就可以用P1和P2的值 (对应file 和 block) 在DBA_EXTENTS中找到段名。 如何是一个表，就很可能需要重建表来创建更多的process freelists。 一种计算需要创建多少个freelist的方法是dump一些段中接近HWM的块，检查interested transaction list的个数，具体方法可参见3.1。interested transactions个数的峰值加1 就是需要的最小process freelists的值。
从2.3和2.4可以看出，使用多个free list可能导致更多的空块未被使用， 也可能导致段更快地扩展。如果性能是当前所关心的重点，那么多free lists 可以用来提高并发访问能力，当然会增加一些额外空间的耗用。然而，如果空间使用大小是首先考虑的因素，那么推荐使用single freelist，使参数FREELISTS=1, 当然就不能提升并发事务的性能了。
V$WAITSTAT 也可显示其他类型class的竞争，包括segment header 和free list。 出现在同一个free list group中多个事务需要同时更新它们的free list header记录时。 有多种方法来解决这个问题如重建表采用更多的free list groups,或者增加 _bump_highwater_mark_count大小，或者调整应用本身。
参考文献和资料：
1.《FREELISTS and FREELIST GROUPS. SCOPE & APPLICATION 》
2.《INITRANS, MAXTRANS, FREELISTS and FREELIST GROUPS, PCTFREE and PCTUSED》,Mike Ault
3.《Freelist Internals: An OverviewKnowledge》,Xpert for Oracle Administration
4．《Blockdump - 8.x Data Segment Header in Oracle》
5．《AskTom dbms_space_free_space》，asktom.oracle.com
6．《Data Blocks and Freelists》，www.ixora.com.au
7．《偷窥Data block 的物理结构》，www.itpub.net
8．《Oracle 9i for windows nt/2000数据系统培训教程》,清华大学出版社
上述部分文章在我的blog网站blog.csdn.net/youbo2004上可找到，对于研究free list,free list group和block等有很好的帮助。
篇6：Oracle 9i 约束条件数据库教程
约束条件就是Oracle数据库系统提供的对数据的完整性进行制约的机制，
Oracle 9i 约束条件数据库教程
。Oracle 9i允许创建5种约束条件。参见表7.8。创建检查约束条件
（1）在【管理目标导航器】中按照7.6节修改数据表结构的步骤进行操作。
（2）切换到图7.61所示的编辑表的【约束条件】选项卡。
（3）上述创建检查约束条件的SQL码如下?br> DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
ALTER TABLE ”SCOTT“.”STUDENT“
ADD (CONSTRAINT ”研究生编号检查约束条件“
CHECK(student_id>=20020101 and student_id<=20030909))
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
【参见光盘文件】：\\第7章\\ createcheck.sql。
（4）读者也可以直接在【SQLPlus Worksheet】中执行createcheck.sql 文件完成检查约束条件的创建，如图7.62所示，
测试检查约束条件
（1）在7.63所示的【表数据编辑器】界面中按照图示内容输入，单击“应用(P)”按钮。
（2）上述输入数据的SQL代码如下。
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
INSERT INTO ”SCOTT“.”STUDENT“
(”STUDENT_ID“ ,”NAME“ ,”PROFESSIONAL“ ,”BIRTHDAY“ ,”DIRECTOR_ID“ )
VALUES (20010101 ,'纪晓芙' ,'软件工程' ,TO_DATE('15-7月 -1971', 'dd-Mon-yyyy HH:MI:SS AM') ,200201)
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
【参见光盘文件】：\\第7章\\ testcheck.sql。
（3）出现如图7.64所示界面。
（4）读者也可以直接在【SQLPlus Worksheet】中执行testcheck.sql 文件完成检查约束条件的测试，结果如图7.65所示。
篇7：删除Oracle 9i数据库数据库教程
（1）启动【数据库配置助手】，一直到出现如图6.44所示的【操作】界面，
删除Oracle 9i数据库数据库教程
。（2）出现如图6.45所示的【数据库】界面，
（3）出现如图6.46所示的【概要】界面。
（4）出现如图6.47所示的【删除确认】界面。
（5）成功删除数据库后出现如图6.48所示的【成功境】界面。单击“否”按钮?br>
篇8：Oracle数据库优化策略总结
SELECT时不利用函数
在做频繁的查询垄断时，尽量直接select字段名，然后利用C语言代码对查询收获做二次加工，避免让Oracle来做混杂的函数可能数学计算。因为Oracle出于通用性的琢磨，其函数及数学计算的速度远不及用C语言直接编译成机器码后计算来的快。
绑定变量
这个能够大幅度减退SQL的“hard parse”，我们大局部过程都曾经告终了变量绑定。个别未曾告终的，修正一下，也能很快看到收获。
批量FETCH
万一顺次select会归来多条（几百、上千）登记，利用批量Fetch，例如顺次fetch 1000条登记，要比一条条的fetch数据快的多，也能够管用减退oracle的压力。
批量提交
顺次修正多条（例如小于10000条左右）登记，然后顺次性提交，要比每条提交顺次快的多。当然前提是业务逻辑批准这么做。
批量增删改
万一必需顺次性修正可能剔除多条登记，能够批准批量数组绑定的措施，这个和前面说得“绑定变量”相仿，差异是前者绑定的是一个变量，这里绑定的是一个大数组的首指针，这种措施要比逐条绑定厉行快的多。
SQL预解析
前面的大局部是批量垄断，还有一种常见的场景是小事务垄断，但频率极其高nextplas.com。这种场景等闲SQL也不混杂，几乎未曾优化的余地了，然而由于垄断频繁，同样会构成CPU居高不下。现在我们的过程大局部都是下面这个利用形式：
loop parse sql; bind var; execute sql; end loop;
固然我们利用了绑定变量的措施，然而由于垄断频繁，同样构成许多的“soft parse”以及网络通信。在内存数据库中，我们等闲批准预解析的措施来长进效率，事实上，Oracle很早就给开发者供给这种形式，只是开发者嫌繁琐没利于用而已。将过程改成下列形式：
parse sql; bind var; loop execute sql; end loop;
这么就能够管用减退Oracle的压力，能够将厉行效率起码长进一倍。然而这种形式波及到过程构造的改变，定然在设计阶段就这么做。否则，后期再调剂的话，危险和工作量都会很大。
SQL语句的一些优化措施
1、SQL语句用大写的；因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再厉行。
2、避免在索引列上利用NOT等闲，我们要避免在索引列上利用NOT， NOT会发生在和在索引列上利用函数雷同的波及。
3、当Oracle“碰到”NOT，他就会静止利用索引转而厉行全表扫描。
4、避免在索引列上利用计算。WHERE子句中，假定索引列是函数的一局部。优化器将不利用索引而利用全表扫描。
5、尽量少用DISTINCT垄断，用EXISTS轮换DISTINCTvalues should never be negative。
[Oracle数据库优化策略总结]
优化Oracle库表设计的若干方法数据库教程（通用8篇）
