《Expert one on one Oracle》- 表- 笔记-2

堆表

平常我们使用的绝大多数的表都是堆表。堆，在数据结构中就是一块大的空间，磁盘或者内存，并以随机的方式管理。所以，查询堆表结果并不是以插入的顺序输出。

SQL> show parameters db_block_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> create table t
2 ( a int,
3 b varchar2(4000) default rpad('*',4000,'*'),
4 c varchar2(3000) default rpad('*',3000,'*')
5 )
6 /
Table created.

SQL> insert into t (a) values ( 1);
1 row created.

SQL> insert into t (a) values ( 2);
1 row created.

SQL> insert into t (a) values ( 3);
1 row created.

SQL> delete from t where a = 2 ;
1 row deleted.

SQL> insert into t (a) values ( 4);
1 row created.

SQL> SELECT a, DBMS_ROWID.rowid_object (ROWID) OBJECT,
2 DBMS_ROWID.rowid_relative_fno (ROWID) file_no,
3 DBMS_ROWID.rowid_block_number (ROWID) BLOCK,
4 DBMS_ROWID.rowid_row_number (ROWID) rowno
5 FROM t; ;--输出顺序不是134，而是143, 最后插入的块被插入到中间的块

A OBJECT FILE_NO BLOCK ROWNO
---------- ---------- ---------- ---------- ----------
1 30943 12 10 0
4 30943 12 11 1
3 30943 12 12 0

创建表的时候有非常多选项，但是我们通常只需要关心PCTFREE，PCTUSED，INITRANS，FREELISTS。通过如下的方式可以查看创建过程的其他参数：创建的内容在T.SQL文件中。
exp userid=scott/scott tables=t
imp userid=scott/scott full=y indexfile=t.sql

索引组织表

以索引的结构存储数据的表。表的数据就是索引，索引也即数据。相对堆表来讲，索引表需要更高的管理和维护成本。因为索引表的存储是顺序的，所以对于数据的增删改都需要较大的成本。

对于更新相对少的表，可以考虑使用索引表，例如一些代码表等。还有，对于那些物理上希望存储在一块的数据，也可以考虑使用索引表，例如：在父子表关联中，希望子表的多条记录物理上存储在一块。

索引表的选项：

相对堆表，无PCTUSED。因为索引表的数据，由于数据的值已经决定它所在的数据块，而不管这块空余空间的大小。同样的原因，PCTFREE值也只在对象创建和填充索引结构的时使用。

NOCRPRESS，让ORACLE存储索引和值的时候不要压缩。如果为CRPRESS，后面整形参数，表示需要压缩的列的数量。从而使得那些重复的列值不再存储。
下面的例子中，表的owner，type是存在大量重复的。所以，可以采用压缩的方式创建索引表。

如果压缩因子为2，创建完成之后，可能的存储为：
Scott/table
11111
22222
33333
44444
55555
…
99999
如果不压缩：可能存储为：
Scott/table/11111
Scott/table/22222
Scott/table/33333
Scott/table/44444
Scott/table/55555
Scott/table/66666
…
Scott/table/99999

通过如下例子，对比压缩和非压缩所使用的空间。

SQL> create or replace
2 procedure show_iot_space
3 ( p_segname in varchar2 )
4 as
5 l_segname varchar2(30);
6 l_total_blocks number;
7 l_total_bytes number;
8 l_unused_blocks number;
9 l_unused_bytes number;
10 l_LastUsedExtFileId number;
11 l_LastUsedExtBlockId number;
12 l_LAST_USED_BLOCK number;
13 begin
14 select 'SYS_IOT_TOP_' object_id
15 into l_segname
16 from user_objects
17 where object_name = upper(p_segname);
18
19 dbms_space.unused_space
20 ( segment_owner => user,
21 segment_name => l_segname,
22 segment_type => 'INDEX',
23 total_blocks => l_total_blocks,
24 total_bytes => l_total_bytes,
25 unused_blocks => l_unused_blocks,
26 unused_bytes => l_unused_bytes,
27 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
28 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
29 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
30
31 dbms_output.put_line
32 ( 'IOT used ' to_char(l_total_blocks-l_unused_blocks) );
33 end;
34 /
Procedure created.

SQL> create table iot
2 ( owner, object_type, object_id,
3 primary key(owner,object_type,object_id)
4 )
5 organization index
6 NOCOMPRESS
7 as
8 select owner, object_type, object_id from all_objects
9 order by owner, object_type, object_id
10 /
Table created.

SQL>
SQL> set serveroutput on
SQL> exec show_iot_space( 'iot' );
IOT used 96
PL/SQL procedure successfully completed.

SQL> drop table iot;
Table dropped.

SQL> create table iot
2 ( owner, object_type, object_id,
3 primary key(owner,object_type,object_id)
4 )
5 organization index
6 compress 1
7 as
8 select owner, object_type, object_id from all_objects
9 order by owner, object_type, object_id
10 /
Table created.

SQL> exec show_iot_space('iot' );
IOT used 74
PL/SQL procedure successfully completed.

SQL> drop table iot;
Table dropped.

SQL> create table iot
2 ( owner, object_type, object_id,
3 primary key(owner,object_type,object_id)
4 )
5 organization index
6 compress 2
7 as
8 select owner, object_type, object_id from all_objects
9 order by owner, object_type, object_id
10 /
Table created.

SQL> exec show_iot_space( 'iot' );
IOT used 38
PL/SQL procedure successfully completed.

通过如上的例子可以看到，参数COMPRESS可以节省存储空间，减少I/O（通常也会消耗更多的CPU）。

PCTTHRESHOLD, OVERFLOW, and INCLUDING

这三个参数用来确保索引的叶块（实际存储索引数据的块）能够有效的存储数据。
a. OVERFLOW：当行的数据增大的时候，通过OVERFLOW设置另外一个段，可以让行数据OVERFLOW到那个段去。在OVERFLOW中也有PCTUSED，PCTFREE设置，这些都与堆表的相同。
b. PCTTHRESHOLD：当行的数量超过一定数量的时候，将会将行的一部分存到其他地方。对于8K的BLOCK，如果PCTTHRESHOLD为10，那么当行的大小超过800BYTE的时候，行的部分内容将不会存储在这个索引块上了。
c. INCLUDING：INCLUDING指定的列以及前面的列，都存储到索引块中，其他的列存储到溢出块中。

这几个参数是创建IOT的重要参数，可以根据列的访问频率考虑是否需要将其放到OVERFLOW中去，可以根据一块要存储的行数来计算PCCTHRESHOLD。
可以在IOT表上创建索引。由于IOT表的行与堆表的行不同，经常移动。IOT表中的行根据主健的值在索引结构中确定位置，当索引本身的大小和形状改变的时候，位置也会更改。所以，ORACLE采用了逻辑ROWID，ROWID基于IOT的主健。IOT表上的索引效率通常比堆表的索引低。

《Expert one on one Oracle》- 表- 笔记-1

术语

高水位标示（HIGH WATER MARKET）

HWM是已经使用过的存储空间和未使用过的存储空间之间的分界线。在表刚创建的时候，HWM从第一数据块开始。随着数据插入的增多，HWM会上升。如果删除数据行，虽然部分被删除的哪些块不再包含数据，但是这些块仍然在HWM之下，HWM位置不会改变，直到HWM被重建或者TRUNCATE。ORACLE会扫描HWM下所有的块，即使这些块已经不包含数据。所以，这也会影响到性能。如果需要删除表中所有的行，尝试使用TRUNCATE。

FREELISTS

对于HWM下那些还有空闲空间的块，ORACLE使用FREELIST来记录。每个对象至少有一个FREELISTS。只有在HWM下的块才可以在FREELIST中找到。如果FREELISTS已经为空，那么HWM就会上移，新增块到FREELIST。一个对象可以拥有多个FREELIST。如果一个对象只有一个FREELIST，那么对于并发访问同一些的块，就会发生块等待事件。

但是也不是将FREELISTS设置越多越好。因为当一个进程用完一个FREELIST空闲块的时候，并不去使用这个对象的其他FREELIST的空闲块，而是上移HWM。如果当前的EXTENT不够，将会使用其他的EXTENT。所以，设置太多的FREELIST会造成空间上的浪费。

PCTFREE 和PCTUSED

这两个参数用来控制什么条件下将块加入FREELIST和从FREELIST中移除。PCTFREE默认的大小为10，意味着当块被使用了90%之后，将会从FREELIST中移除。剩下的10%的空间用于块中数据行的更新。PCTUSED的默认的大小为40，意味着对于那些不在FREELIST中的块，如果其空余空间达到61%的时候，ORACLE会将这些块重新加入到FREELIST中去。怎样设置这两个值需要根据数据处理情况，例如对于那些很少更新的数据，设置过大的PCTFREE是空间的浪费。

行迁移

由于行的更新，使得行所占用的空间增大，当行原来所在数据块空间不够容纳更新后的行，行必须迁移到新的数据块上。行迁移的过程，会将行的数据迁移到新的块，同时在原来块中保留指向新行的指针。这是因为索引的物理指针仍然需要知道原来数据行的地址。由于这样，行迁移会带来更多的I/O访问（访问原来所在块，然后再通过指针访问前以后所在的块）。

已经迁移到新块的行，由于被再次更新，新块的空余空间也不能容纳，这行将会再次迁移。如果原来的块中现在有足够的空间了，这行将会迁移回去。否则，将会迁移到第三个块上去。

PCTFREE 和PCTUSED设置

下面的例子看设置PCTFREE和PCTUSED怎样影响FREELIST。
SQL> create table t ( x int, y char(1000) default 'x' );
Table created.

SQL> create or replace procedure measure_usage
2 as
3 l_free_blks number;
4 l_total_blocks number;
5 l_total_bytes number;
6 l_unused_blocks number;
7 l_unused_bytes number;
8 l_LastUsedExtFileId number;
9 l_LastUsedExtBlockId number;
10 l_LAST_USED_BLOCK number;
11
12 procedure get_data
13 is
14 begin
15 dbms_space.free_blocks
16 ( segment_owner => USER,
17 segment_name => 'T',
18 segment_type => 'TABLE',
19 FREELIST_group_id => 0,
20 free_blks => l_free_blks );
21
22 dbms_space.unused_space
23 ( segment_owner => USER,
24 segment_name => 'T',
25 segment_type => 'TABLE',
26 total_blocks => l_total_blocks,
27 total_bytes => l_total_bytes,
28 unused_blocks => l_unused_blocks,
29 unused_bytes => l_unused_bytes,
30 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
31 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
32 LAST_USED_BLOCK => l_last_used_block ) ;
33
34
35 dbms_output.put_line( L_free_blks ' on FREELIST, '
36 to_number(l_total_blocks-l_unused_blocks-1 )

37 ' used by table' );
38 end;
39 begin
40 for i in 0 .. 10
41 loop
42 dbms_output.put( 'insert ' to_char(i,'00') ' ' );
43 get_data;
44 insert into t (x) values ( i );
45 commit ;
46 end loop;
47
48
49 for i in 0 .. 10
50 loop
51 dbms_output.put( 'update ' to_char(i,'00') ' ' );
52 get_data;
53 update t set y = null where x = i;
54 commit;
55 end loop;
56 end;
57 /
Procedure created.

SQL> exec measure_usage
insert 00 0 on FREELIST, 0 used by table
insert 01 1 on FREELIST, 1 used by table
insert 02 1 on FREELIST, 1 used by table
insert 03 1 on FREELIST, 1 used by table
insert 04 1 on FREELIST, 1 used by table
insert 05 1 on FREELIST, 1 used by table
insert 06 1 on FREELIST, 1 used by table
insert 07 1 on FREELIST, 1 used by table
insert 08 1 on FREELIST, 2 used by table--插入到第八行数据的时候，由于原来块上的空间不足，新增一个块供使用
insert 09 1 on FREELIST, 2 used by table
insert 10 1 on FREELIST, 2 used by table
update 00 1 on FREELIST, 2 used by table
update 01 1 on FREELIST, 2 used by table
update 02 1 on FREELIST, 2 used by table
update 03 1 on FREELIST, 2 used by table
update 04 2 on FREELIST, 2 used by table--更新到第四行的时候，将空间返回给FREELIST
update 05 2 on FREELIST, 2 used by table
update 06 2 on FREELIST, 2 used by table
update 07 2 on FREELIST, 2 used by table
update 08 2 on FREELIST, 2 used by table
update 09 2 on FREELIST, 2 used by table
update 10 2 on FREELIST, 2 used by table

PL/SQL procedure successfully completed.

INITIAL, NEXT, PCTINCREASE

定义初始EXTENT的大小以及后续EXTENT的大小。例如：INITIAL为1，NEXT为2，PCTINCREASE为为50，那么从EXTENT的一次大小为：1M，2M，3M，4.5M。如果数据库使用本地管理的表空间，EXTENT设置为UNIFORM的，那么INITIAL等于NEXT，PCTINCREASE没有意义。如果没有使用本地管理的表空间，也建议设置INITIAL等于NEXT，将PCTINCREASE设置为0。

MINEXTENTS，MAXEXTENTS

设置一个对象拥有最小和最大的EXTENT。当然，最大的EXTENT表空间的限制。

LOGGING，NOLOGGING

LOGGING，创建表的时候产生重做日志。使用NOLOGGING，创建表不产生重做日志，但是插入等操作仍然产生重做日志。

INITRANS，MAXTRANS

对象的每个块都有一个块头。块头中有一张事务表。在这张事务表中记录那些事务锁住了这个块上的那些行。初始值由INITRANS指定，最大值有MAXTRANS指定。

《Expert one on one Oracle》- 重做回滚- 笔记-3

临时表以及重做/回滚

Oracle不会为临时表的数据块产生重做日志，所以临时表上的操作都是不可“恢复”的。当对临时标的数据块进行更改的时候，更改的纪录不会在重做日志中记录。然而，临时表会产生回滚，回滚信息会被重做日志纪录。所以，临时表会产生重做日志。临时表之所以会产生回滚信息，这是为了支持事务中回滚。例如：临时表和普通表一样也有约束（constrain），所以当不满足约束的数据更新或者插入的临时表的时候，必须能够回滚。

插入临时表产生的回滚数据非常少，查询临时表不产生回滚数据。但是更新和删除临时表数据将会产生较多的回滚信息。

SQL> create table perm
2 ( x char(2000) default 'x',
3 y char(2000) default 'y',
4 z char(2000) default 'z' )
5 /
Table created.

SQL>
SQL> create global temporary table temp
2 ( x char(2000) default 'x',
3 y char(2000) default 'y',
4 z char(2000) default 'z' )
5 on commit preserve rows
6 /
Table created.

SQL> create or replace procedure do_sql( p_sql in varchar2 )
2 as
3 l_start_redo number;
4 l_redo number;
5 begin
6 select value into l_start_redo from redo_size;
7
8 execute immediate p_sql;
9 commit;
10
11 select value-l_start_redo into l_redo from redo_size;
12
13 dbms_output.put_line
14 ( to_char(l_redo,'9,999,999') ' bytes of redo generated for "'
15 substr( replace( p_sql, chr(10), ' '), 1, 25 ) '"...' );
16 end;
17 /

Procedure created.

SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> begin
2 do_sql( 'insert into perm (x,y,z)
3 select 1,1,1
4 from all_objects
5 where rownum <= 500' );
6
7 do_sql( 'insert into temp (x,y,z)
8 select 1,1,1
9 from all_objects
10 where rownum <= 500' );
11
12 do_sql( 'update perm set x = 2' );
13 do_sql( 'update temp set x = 2' );
14
15 do_sql( 'delete from perm' );
16 do_sql( 'delete from temp' );
17 end;
18 /
3,263,576 bytes of redo generated for "insert into perm (x,y,z) "...
58,428 bytes of redo generated for "insert into temp (x,y,z) "...
2,160,600 bytes of redo generated for "update perm set x = 2"...
1,082,848 bytes of redo generated for "update temp set x = 2"...
3,353,108 bytes of redo generated for "delete from perm"...
3,253,220 bytes of redo generated for "delete from temp"...

PL/SQL procedure successfully completed.

通过如上可以看到，插入非临时表产生大量的重做日志，插入临时表则只有回滚段的“重做”日志产生，所以非常小。

更新非临时表产生大概两倍于更新临时表的“重做”日志。对于临时表，“镜像”前的更新被保存，而“镜像”后的更新无需保存。

删除在临时表和非临时表上产生的重做日志大小基本相同。这是因为，删除操作产生大量的回滚数据，而产生的更改块的重做日志非常小。

对于临时表，总结起来有如下规则：
a. 插入操作产生相当少的回滚/重做活动。
b. 删除操作产生的重做数量基本等于非临时表。
c. 更新操作产生的重做数量是更新非临时表的一半（如果字段的所有的值为空，以2000bytes的值更新这个字段，那么产生的重做类似于插入操作。反之，如果字段几乎所有的值有2000bytes，全部以NULL更新，那么产生的重做类似于DELETE）。

拥有如上知识后，在临时表实际的应用中，尽量只使用INSERT和SELECT操作。避免删除临时表，而是使用TRUNCATE命令或者让临时表在提交之后自行清空表。

分析表

可以通过LogMiner能够将重做日志载入系统的动态表（V$），从而能够方便的查询日志。可以看到执行DML语句之后，列值的变化；可以看到重做和回滚事务的SQL；可以当删除一个表的时候Oracle在OBJ$表上删除操作。这些都有利于DBA进行分析，找到恢复所需要的数据和时间点。
回滚

什么操作产生最少/最多UNDO

INSERT操作产生很少的UNDO，因为ORACLE只需要记录需要删除的ROW ID。UPDATE操作通常列第二位，因为ORACLE需要纪录更改的BYTES，而UPDATE通常只更新整行的部分字段。DELETE操作一般意义来说，产生最多的UNDO。因为需要纪录删除前整行的数据。

SET TRANSACTION

通过这个语句可以指定事务运行所使用的回滚段。一般不用使用这种方式，而是将MAXEXTENTS设置足够大，然ORACLE自动区扩展回滚段。如果需要，设置参数使得回滚段在扩展到一定大小之后，再自动缩减到一定大小。

使用打得回滚段的一个主要问题就是——不能阻止其他事务使用这个回滚段。所以最好的方式是让系统挑选回滚段，并让其能够自动扩展。同时，有些工具并不支持指定回滚段。IMP不支持，SQLLDR不支持，SNAPSHOT的刷新支持。

如果确实存在有一次性的大量的数据需要使用大的回滚段，也可以创建一个，然后进行处理，然后再置为OFFLINE，然后再删除。对于这种情况，将标分为几个部分，然后采用多个任务同时更新，这样各自的任务能够使用各自的回滚段，也许更有效。

ORA-01555：回滚段太旧

如下三个原因会造成这个错误：
a. 回滚段太小
b. 程序跨COMMITS取数
c. 块清除

前面两个都和ORACLE持续读相关。ORACLE使用回滚段来回滚那些查询开始后已经更改的块。通过这种方式，ORACLE提供一个连续的数据库的快照。通过两个Session来验证：

步骤1：
Session 1:创建表，插入数据，更新一条数据不提交
SQL> create table t1 ( a number );
Table created.

SQL> insert into t1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> update t1 set a = 10 where a = 1;
1 row updated.

步骤2：
Session 2:插入数据
SQL> insert into t1 select * from t1 where a = 1;
1 row created.

步骤3：
Session 1:提交更新结果，查询表数据
SQL> commit;

Commit complete.

步骤4：
Session 2: 提交插入，查询数据表
SQL> commit;
Commit complete.

SQL> select * from t1;--由于一致性读，步骤2仍然能够插入数据
A
----------
10
1

处理这种ORA-01555的方法有：
a. 分析表，进行块清除。
b. 分配更大的回滚段。
c. 优化查询SQL。

回滚段太小造成ORA-01555的错误

重现错误:
a. Session A 发起一个查询。
b. Session B 发起一个更新并且提交。相关回滚信息记录在回滚段，同时因为已经提交，回滚段信息在需要的时候可能被覆盖。假定第1000个数据块被修改了。
c. Session A 继续运行，查询到第800个数据块。由于其他的一些操作，占用了大量的回滚段，回滚段的空间不够，特别地，1000个数据块更改的回滚信息被覆盖。
d. Session A 查询到1000个数据块的时候，发现数据块已经被更改，尝试查找回滚信息来保证一致性读，发现回滚信息已经被覆盖，从而引发了了ORA-01555错误。
即使将会滚段的MAXEXTENT设置足够大，但是由于查询语句并不自动扩展（更新语句能够自动扩展），所以通常还是在查询的时候会出现ORA-01555的错误。所以，不要让其自动扩展，而是通过手工调节。

对于9i的数据库，因为是自动管理回滚段，所以需要将其更改为手动管理之后，重新启动之后，才能运行如下的例子。
alter system set undo_management= manual scope=spfile;

步骤1：
Session1:创建很小的回滚段，创建测试表，执行更新语句
SQL> create rollback segment rbs_small--创建一个很小的回滚段
2 storage
3 ( initial 8k next 8k
4 minextents 2 maxextents 3 )
5 --tablespace rbs_test
6 /
Rollback segment created.

SQL> alter rollback segment rbs_small online;--设置为online
Rollback segment altered.

SQL> create table t
2 as
3 select *
4 from all_objects
5 /
Table created.

SQL> create index t_idx on t(object_id)
2 /
Index created.

SQL> begin
2 for x in ( select rowid rid from t )--执行更新语句，更新过程全表扫描
3 loop
4 commit;
5 set transaction use rollback segment rbs_small;--设置事务使用刚才建立回滚段
6 update t
7 set object_name = lower(object_name)
8 where rowid = x.rid;
9 end loop;
10 commit;
11 end;
12 /

步骤2：
Session2：执行查询语句（要确保步骤1的更新还未完成的时候运行）
SQL> select object_name from t where object_id > 0 order by object_id;
…….
ERROR: 出现ORA-01555错误
ORA-01555: snapshot too old: rollback segment number 11 with name "RBS_SMALL"
too small

由于步骤2中的查询是通过索引，所以其访问数据块是顺序是随机的，查询访问那些步骤1中被更新的快的几率会增大，从而可能造成ORA-01555的错误。如果是步骤2中使用全表扫描查询（通过RULE指定），那么由于步骤2的查询比步骤1的更新所花费的时间更少，所以经常是查询完成再去更新，从而产生ORA-01555错误的几率将会非常小。

交叉提交造成ORA-01555的错误

如下的例子中，将会每100行更新之后就提交一次。就有可能，查询会重新访问已经提交更新的块。而由于回滚段的信息已经被覆盖，所以也会造成ORA-01555错误。

SQL> declare
2 l_cnt number default 0;
3 begin
4 for x in ( select rowid rid, t.* from t where object_id > 0 )
5 loop
6 if ( mod(l_cnt,100) = 0 )
7 then
8 commit;
9 set transaction use rollback segment rbs_small;
10 end if;
11 update t
12 set object_name = lower(object_name)
13 where rowid = x.rid;
14 l_cnt := l_cnt + 1;
15 end loop;
16 commit;
17 end;
18 /
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 11 with name "RBS_SMALL"
too small
ORA-06512: at line 4

块延迟清除造成ORA-01555的错误

造成错误的原因是非常严格的，所以现实情况中由这种原因而造成的ORA-01555非常少，需要满足如下条件：
a. 一个更新被提交，部分块没有被自动清除事务标示。
b. 在某个SQL访问这些块前，这些块不被其他的SESSION访问。
c. 这个SQL开始运行，其SCN为T1。这个SQL最终会访问到上述没有被清除事务标示的块。
d. 系统中又有许多其它的事务提交，但这些事务并不访问上述的块。
e. 由于其他事务的提交，最初的更改数据事务的事务实体被循环重用。
f. 最终，回滚段中最小的SCN号已经大于T1。这个时候，SQL访问这些块的时候，不能判断COMMIT SCN是大于T1（如果大于T1，回滚）还是小于T1（如果小于T1，SQL将会使用这个块）。

参考：回滚段探究

《Expert one on one Oracle》- 重做回滚- 笔记-2

块清除

当Oracle更改数据块时，会对这个数据块进行跟踪。在提交的时候，将会重新访问这个块并将更改标记为已经提交。但是只标示那些还没有从db_block_buffers中清除的块。而且，如果更改了太多的块，那么也只有前面少数的块（db_block_buffers的10%）被重新访问。

当提交的时候，如果这些块还在缓存中，那么Oracle将会进行fast commit，清除块头部的事务信息。对于那些已经从db_block_buffers中清除，被写入磁盘的数据块，Oracle将会忽略这些块。当下次的DML或者查询语句再访问这些块的时候，由这些语句负责清除，这就是所谓的delayed block cleanout。所以，即使是查询语句也可能产生重做日志。

通过如下的方法来验证：
a. 创建表，保证一块只能存储一行，在8K大小数据块中，创建每行大小为6K的数据库；
b. 插入499行纪录，使用499个数据块，其大小已经超过30（也即300的10%）；
c. 提交；
d. 对于在db_block_buffers的数据块（同时保证小于db_block_buffers 10%），Oracle执行fast commit，对于其他的数据块Oracle将忽略；
e. 执行全表扫描的查询语句。这条语句将会重新访问步骤d中没有被清除的块，对这些块头的事务信息进行清除，这个步骤将会产生重做日志。
f. 检查通过步骤e查询所产生的重做日志。
g. 执行新的查询，由于步骤e已经清除块头的事务信息，这个时候查询将不会需要清除块，也不会产生重做日志。

SQL> create table t --保证一块只能存储一行
2 ( x char(2000) default 'x',
3 y char(2000) default 'y',
4 z char(2000) default 'z' )
5 /

Table created.

SQL>
SQL> insert into t --插入499行纪录，使用499个数据块
2 select 'x','y','z'
3 from all_objects where rownum <500
SQL> commit;-- 提交，插入过程中部分快会执行fast commit，部分块被忽略

Commit complete.

SQL>
SQL> column value new_value old_value
SQL>
SQL> select * from redo_size;

VALUE
----------
3332424

SQL>
SQL> select *
2 from t
3 where x = y;

no rows selected

SQL> select value-&old_value REDO_GENERATED from redo_size;
old 1: select value-&old_value REDO_GENERATED from redo_size
new 1: select value- 3332424 REDO_GENERATED from redo_size

REDO_GENERATED
--------------
10740 --查询过程产生了重做日志，证明进行了块清除操作

SQL> commit;

Commit complete.

SQL>
SQL> select value from redo_size;

VALUE
----------
3343164

SQL>
SQL> select *
2 from t
3 where x = y;

no rows selected

SQL>
SQL> select value-&old_value REDO_GENERATED from redo_size;
old 1: select value-&old_value REDO_GENERATED from redo_size
new 1: select value- 3343164 REDO_GENERATED from redo_size

REDO_GENERATED
--------------
0--查询过程没有产生重做日志，前面的查询已经清除块，第二次查询不再有清除动作


上面的第一个查询产生了重做日志，有可能促使这些块被DBWR重写。Oracle必须采用delayed clear out的策略，否则在提交的时候必须重新访问更新块，还可能从磁盘中读取快，这将会是相当耗时的工作。

有些事务创建“干净的”数据块，例如：CREATE TABLE AS SELECT。

在某些情况下，进行大量的数据更新之后，也可以主动访问数据块，让最终用户访问的时候速度会更快。例如通过ANALYZE命令，可以清除块。

参考：
Block cleanout - fast or delayed.

《Expert one on one Oracle》- 重做回滚- 笔记-1

“重做”允许Oracle重做事务。“回滚”则允许Oracle撤销或者回滚事务。

重做

重做日志文件是数据库的事务日志，只用来恢复数据库。Oracle有两种类型重做日志文件：在线重做日志和归档重做日志。在线重做日志以循环的方式来写入，当其中一个日志文件写满，就会切换写另外的日志文件，循环往复。归档日至是当在线日志写满的时候，将在线日志拷贝到另外一个地方。

COMMIT

COMMIT语句的执行并不消耗太多时间，而且并不是事务越大COMMIT所消耗的时间越多。实际的情况是，在COMMIT执行之前，Oracle已经完成所要提交事务的大部分工作。

SQL> create table t ( x int );
Table created.

SQL> set serveroutput on
SQL>
SQL> declare
2 l_start number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 10000
5 loop
6 insert into t values ( 1 );
7 end loop;
8 commit;
9 dbms_output.put_line
10 ( dbms_utility.get_time-l_start ' hsecs' );
11 end;
12 /
135 hsecs

PL/SQL procedure successfully completed.

SQL>
SQL> declare
2 l_start number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 10000
5 loop
6 insert into t values ( 1 );
7 commit;
8 end loop;
9 dbms_output.put_line
10 ( dbms_utility.get_time-l_start ' hsecs' );
11 end;
12 /
161 hsecs

PL/SQL procedure successfully completed.

从上面可以看到，插入多样的数据一次提交比进行多次提交消耗更少的时间。所以，提交的响应时间和事务大小并无太大关系。因为提交之前，Oracle已经完成99%的工作，具体为：
a. 回滚段的纪录已经在SGA中产生。
b. 更改的数据块已经在SGA中产生。
c. 上述两项的缓存的REDO已经在SGA产生。
d. 基于如上三项的的大小以及所消耗时间，上述数据的部分已经写入磁盘。
e. 已经获取所有需要的锁。

那么提交的时候，需要完成如下工作：
a. 为事务产生SCN。
b. LGWR将余下的缓存的重做日志写入磁盘，同时将SCN好记入到重做日志文件。完成这一步之后，事务实际上已经提交。事务实体被移除。在V$TRANSACTION表中找不到事物的相关纪录。
c. Session所持有的锁被释放。
d. 如果被事务所修改的快还在缓存中，那么大多数将被访问以及被“清除”。

真正消耗时间的在于LGWR将重做日志写入磁盘，因为这是物理的I/O访问。但是COMMIT之后，LGWR并不是将所有的 内容写入重做日志。在COMMIT之前，LGWR已经持续的写重做日志。当如下条件满足的时候，LGWR写重做日志：
a. 每三秒
b. 当达到1/3或者1MB大小
c. 当COMMIT的时候

SCN是ORACLE用户保证事务的顺序，确保数据库能被正确恢复。同时，它也用来保证数据库的读一至性以及检查点（checkpointing）。每当事务提交，SCN号增一。

SQL> create table t
2 as
3 select * from all_objects
4 /
Table created.

SQL> insert into t select * from t;
29257 rows created.

SQL> insert into t select * from t;
58514 rows created.

SQL> insert into t select * from t where rownum < 12000;
11999 rows created.

SQL> commit;
Commit complete.

SQL>
SQL> create or replace procedure do_commit( p_rows in number )
2 as
3 l_start number;
4 l_after_redo number;
5 l_before_redo number;
6 begin
7 select v$mystat.value into l_before_redo
8 from v$mystat, v$statname
9 where v$mystat.statistic# = v$statname.statistic#
10 and v$statname.name = 'redo size';
11
12 l_start := dbms_utility.get_time;
13 insert into t select * from t where rownum < name =" 'redo">
SQL> set serveroutput on format wrapped
SQL> begin
2 for i in 1 .. 5
3 loop
4 do_commit( power(10,i) );
5 end loop;
6 end;
7 /
9 rows created
Time to INSERT: .05 seconds
Time to COMMIT: .00 seconds
Generated 1,368 bytes of redo
99 rows created
Time to INSERT: .00 seconds
Time to COMMIT: .00 seconds
Generated 11,596 bytes of redo

999 rows created
Time to INSERT: .03 seconds
Time to COMMIT: .00 seconds
Generated 116,732 bytes of redo

9999 rows created
Time to INSERT: 1.34 seconds
Time to COMMIT: .00 seconds
Generated 1,079,844 bytes of redo

99999 rows created
Time to INSERT: 5.56 seconds
Time to COMMIT: .00 seconds
Generated 11,175,056 bytes of redo

PL/SQL procedure successfully completed.
从上述运行结果可以看到，随着插入数据量的增大，产生的重做日志也逐渐增大，插入所消耗的时间也增多，但是COMMIT所消耗的时间仍然相当的少。所以，也验证了上述结论：在COMMIT之前，ORACLE已经完成了绝大多数的工作，所以COMMIT锁耗用的时间是相当少的。

回滚

将上述程序的COMMIT（23，25行）替换成ROLLBACK，再执行察看回滚所消耗的时间。

Time to INSERT: .04 seconds
Time to ROLLBACK: .00 seconds
Generated 1,504 bytes of redo

99 rows created
Time to INSERT: .00 seconds
Time to ROLLBACK: .00 seconds
Generated 12,288 bytes of redo

999 rows created
Time to INSERT: .03 seconds
Time to ROLLBACK: .00 seconds
Generated 123,736 bytes of redo
9999 rows created
Time to INSERT: .42 seconds
Time to ROLLBACK: .00 seconds
Generated 1,146,652 bytes of redo

99999 rows created
Time to INSERT: 3.99 seconds
Time to ROLLBACK: 1.64 seconds
Generated 11,883,064 bytes of redo

PL/SQL procedure successfully completed.

从上述运行结果可知，与COMMIT不同，随着数据量的增大，回滚所消耗的时间也增加。这是因为回滚是一个消耗时间的操作。

回滚之前，数据库所完成的工作与提交部分（可以参考上述COMMIT部分）相同。
回滚时，ORACLE所需要完成的工作为：
a. 回滚所有的更改。从回滚段中读取数据，并执行与原来相反的操作。例如，如果原来插入一行，回滚时便删除一行。如果更新一行，回滚时候必须更新回去。
b. Session所持有的锁被释放。

测量产生的日志数量

创建如下的一个视图，可以方便的查询系统中的重做日志。
create or replace view redo_size
as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

通过测试脚本，可以看到：通过一条语句更新N行，与通过N条语句来更新N行，所产生的重做日志的数量大致相同。对于删除操作，结果也类似。但对于更新操作，批量更新产生的重做日志更小一些。

还有，如果我们插入2000bytes的行，实际每行产生的重做日志要高于2000bytes。对于删除操作也类似。对于更新操作，将会是2000betys的两倍数量（需要记录数据以及回滚）。

触发器对重做日志的影响：
a. 删除操作的BEFORE和AFTER触发器不会增加额外的重做日志。
b. 插入操作的BEFORE和AFTER触发器都会增加额外的重做日志。
c. 更下操作的BEFORE触发器产生额外重做日志，而AFTER触发器不产生。
d. 行的大小影响插入过程额外重做日志，更新操作不受影响。

能否关闭重做日志

尽量不要关闭重做日志功能，有些语句NOLOGGING因子，但是实际上还是会产生重做日志。这些日志因为记录数据词典更改而产生的。多于NOLOGGING，有如下几点需要注意：
a. 即使使用NOLOGGING，还是会产生少量的重做日志，这用来保护数据词典。
b. NOLOGGING之影响当前操作。例如如果在创建表的语句中使用了NOLOGGING，那么只是在创建表的过程中不产生重做日志。后续对于标的插入删除以及更新还是会产生日志。其他的特殊操作例如INSERT /*+ APPEND */和SQLLDR插入数据不会产生日志。
c. 在以归档模式运行的数据库中，一旦使用NOLOGGING，那么尽快将受影响的文件备份。

待续....

《Expert one on one Oracle》- 事务 - 笔记

事务的四个属性ACID（Atomicity，Consistency，Isolation，Durability）。

1.1 事务控制语句

Oracle中没有“开始事务”的语句，当第一条更改数据的语句（第一个获取TX锁语句）执行的时候，便隐式地开始了一个事物。

可以通过COMMIT或者ROLLBACK来显式结束事务。否则，你使用的工具将会为你结束事务。例如在SQL*PLUS中，退出session时SQL*PLUS就会替你提交事务，而在PRO*C中会替你回滚事务。

相关控制语句语句有COMMIT，ROLLBACK，SAVEPOINT，ROLLBACK TO ，SET TRANSACTION。通过语句SET TRANSACTION可以指定事务的隔离级别，是否只读，以及使用的回滚段。

Oracle中事务具有Atomicity，语句也具有Atomicity。单条语句的失败，不会影响到前面已经执行语句的回滚。察看下面例子：

SQL> create table t( a int check(a>0 ) );
Table created.
SQL> insert into t values(1);
1 row created.
SQL> insert into t values(-1);
insert into t values(-1)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C003586) violated
SQL> select * from t;
A
----------
1
SQL>

上面例子，显示即使单条语句也具有原子性，第二条语句执行失败，但是第一条语句仍然成功。实际上Oracle处理上述语句的时候，隐式的按照如下方式执行：

Savepoint statement1;
Insert into t values ( 1 );
If error then rollback to statement1;
Savepoint statement2;
Insert into t values ( ‐1 );
If error then rollback to statement2;

另为对于存储过程的调用Oracle也将其作为原子性语句来执行。

SQL> create table t( a int check(a>0 ) );
Table created.

SQL> create or replace procedure p1
2 as
3 begin
4 insert into t values (1);
5 insert into t values (-1);
6 end;
7 /
Procedure created.

SQL> begin p1; end;
2 /
begin p1; end;
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C003588) violated
ORA-06512: at "SCOTT.P1", line 5
ORA-06512: at line 1

SQL> select * from t;
no rows selected

由于整个存储过程的调用作为一个原子性操作，所以第一条插入语句也被回滚。Oracle默认在调用存储过程之前插入了一个SAVEPOINT，所以P1调用失败，标回滚到调用P1之前，所以第一条插入语句也被回滚。

如果采用另外一段语句来调用P1：

SQL> begin p1;
2 exception
3 when others then null;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select * from t;
A
----------
1

这是因为在调用过程中增加了异常处理函数。虽然捕获异常不做任何处理，Oracle还是认为执行成功了。这个时候，“如果错误，那么回滚”的原则在这里将不起作用。同时，在P1中，由于两条插入语句也是原子性操作，这个时候P1是提交这两条语句的Oracle客户端，所以第一条语句执行成功。

从上可以看到，在开发程序的过程中，对事物的控制是多么的重要。如果上述P1的例子的业务要求两次插入要么全部成功要么全部失败，增加异常处理语句而实际不做任何处理，将会带来与业务需求相悖的结果。

1.2 事务与完整约束性检查：

a. 是SQL语句的而不是语句的完整约束性检查。如果一个存储过程中包含多个SQL语句，那么每条SQL语句执行完成的时候，都需要检查。

b. 是在语句执行完成之后检查，而不是在执行过程中。如果更新10行纪录，那么是要等到10行记录更新之后才检查，而不是更新其中的某几条就检查。

c. 可以通过DEFERRED选项来延迟约束性检查。
DEFERRABLE INITIALLY DEFERRED：允许延迟验证约束，默认方式为延迟，等待提交后验证，可以修改为第二种状态。
DEFERRABLE INITIALLY IMMEDIATE： 允许延迟验证约束，默认方式为立即，处理完一条记录就立即验证，可以修改为第一种状态
NOT DEFERRABLE：不允许延迟验证约束，不能与上面两种状态进行转换。

SQL> create table pt
2 ( pk int primary key )
3 /
Table created.

SQL> create table ct
2 ( fk int constraint c_fk
3 references pt(pk)
4 deferrable
5 initially immediate -- 立即检查
6 )
7 /
Table created.

SQL> insert into pt values (1);
1 row created.

SQL> insert into ct values (1);
1 row created.

SQL> insert into ct values (2);--如果是立即检查，一旦不符合完整性约束，就会出错
insert into ct values (2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.C_FK) violated - parent key not found

SQL> rollback;
Rollback complete.

SQL> insert into pt values (1);
1 row created.

SQL> set constraint c_fk deferred;--更换成提交的时候再检查
Constraint set.

SQL> insert into ct values (2);
1 row created.

SQL> commit;--直到提交的时候才检查
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.C_FK) violated - parent key not found

1.3 编程的坏习惯

两个坏习惯
a. 可以一次提交的事务而多次提交。
b. 不设定足够大的回滚段。

关于第一点的测试，可以编写循环，在循环中多次提交，同一次提交所耗用的时间进行对比。

对于第二点的测试，可以将原来的rollback置为offline的状态，创建小的回滚段，执行循环更新的语句，检查结果是否报ORA‐01555错误。

对于ODBC和JDBC，默认都是自动提交。ODBC最初由SQL Server的开发人员设计。而在SQL Server中，锁是稀少资源，所以被设置为自动提交。而对于JDBC，在开发的时候最好将其设置为不自动提交。

1.4 分布式事务

Oracle能够透明的处理分布式事务，采用2PC协议来实现。通过DBLink，不同数据库之间可以相互访问。客户端最初登录的那个数据库，将作为事务的协调者。这样，这个数据库会询问其他数据库是否准备提交，其他的数据库就会反馈状态。如果都是YES，那么这个事务将会提交。如果有一个数据库反馈NO，那么整个事务就会回滚。

通常作为事务协调者的数据库如果出现故障，那么其他的数据库事务就会挂起等待。这个时候，需要手工在其他的数据库上进行提交或者回滚（因为有回滚段的存在）。

DBLink的使用有如下的限制：
a. 不能通过DB Link提交Commit语句。
b. 不能通过DB Link执行DDL语句。
c. 不能通过DB Link创建SAVEPOINT。

1.5 Redo和Undo
待续………….

执行计划中Row 数量为0，为什么

从Metalink中找到答案
如果游标没有关闭，那么通过TKPROF生成的输出将不会包含精确的执行计划，在原始的trace文件中要么没有STAT行，或则行数量遗失。当然可以通过EXPLAIN选项来产生执行计划，但这个时候产生的执行计划不一定是正确的。如果游标关闭，STAT行写入原始的trace文件。

解决方法：
可以通过执行其他无关的SQL来关闭游标或通过dbms_session.reset_package过程来完成。

验证方法：
对比两种不同方式，检查在游标关闭和非关闭两种情况下，是否生成了对应的执行计划。

验证过程：
1. 创建一个过程，在过程中打开一个游标，执行某些操作，然后关闭游标。

创建表：

SQL> desc t_plan;
Name Null? Type
----------------------------------------- -------- --------------------

OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)

创建过程：
CREATE OR REPLACE PROCEDURE scott.tkprof_test
AS
counts NUMBER;

CURSOR c1
IS
SELECT COUNT (*)
FROM t_plan
GROUP BY owner;
BEGIN
OPEN c1;

LOOP
FETCH c1
INTO counts;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.put_line ('counts:' counts);
END LOOP;

CLOSE c1;
END;

2. 登录SQL*PLUS，打开一个session，设置SQL_TRACE为true，执行步骤1中创建的过程，不执行reset_package过程，设置SQL_TRACE为false。
SQL> alter session set sql_trace=true;

Session altered.

SQL> exec tkprof_test;
counts:99

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.

3. 通过TKPROF命令，检查步骤2输出文件。

********************************************************************************

SELECT COUNT (*)
FROM t_plan
GROUP BY owner

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.11 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.11 0 4 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************
4. 登录SQL*PLUS，打开一个session，设置SQL_TRACE为true，执行步骤1中创建的过程，执行reset_package过程，设置SQL_TRACE为false。
SQL> alter session set sql_trace=true;

Session altered.

SQL> exec tkprof_test;
counts:99

PL/SQL procedure successfully completed.

SQL> exec dbms_session.reset_package;

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.
5. 通过TKPROF命令，检查步骤4输出文件。

********************************************************************************

SELECT COUNT (*)
FROM t_plan
GROUP BY owner

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.03 0 4 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT GROUP BY
99 TABLE ACCESS FULL T_PLAN

********************************************************************************


注意：
1. 执行reset_package必须在alter session set sql_tracle = false命令之前执行，否则同样不能得到执行计划。
2. 对于单个在SQL*PLUS中执行的语句，执行完成后，可以通过“Select * from dual”或者“alter session set sql_tracle = ture”，游标会立刻关闭。
3. 今天读到Tom的《Oracle Expert one by one》10章的时候，提到通过原始的trace文件判断是否已经生成可信任的执行计划。在原始的trace文件中，STAT行纪录了运行时精确的执行计划，同时也会包含每一步执行计划中关联的正确的记录行数。也提到了只有当相关游标关闭之后，才会产生相关纪录。回过头看一下上面关闭游标的例子，其生成的trace文件相关部分内容为：

=====================
PARSING IN CURSOR #2 len=61 dep=1 uid=59 oct=3 lid=59 tim=19288630504 hv=2097396184 ad='66c8c0a4'
SELECT COUNT (*)
FROM t_plan
GROUP BY owner
END OF STMT
PARSE #2:c=0,e=38470,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=19288630498
EXEC #2:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19288660693
FETCH #2:c=15625,e=170,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=19288665392
FETCH #2:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19288670162
EXEC #1:c=15625,e=83199,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=4,tim=19288675090
=====================
PARSING IN CURSOR #3 len=52 dep=0 uid=59 oct=47 lid=59 tim=19288679793 hv=1697159799 ad='66c61e20'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #3:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19288679789
EXEC #3:c=0,e=209,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=19288702763
*** 2007-06-13 14:35:20.000
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=59 oct=47 lid=59 tim=19313599313 hv=1443640743 ad='66b81f64'
BEGIN dbms_session.reset_package; END;
END OF STMT
PARSE #1:c=0,e=342,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19313599304
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY '
STAT #2 id=2 cnt=99 pid=1 pos=1 obj=30711 op='TABLE ACCESS FULL T_PLAN '
EXEC #1:c=0,e=21520,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=19313684039
=====================
已经将相关部分highlight，可以看到当查询的SELECT COUNT (*) FROM t_plan GROUP BY owner; 执行完成之后，并没有生成STAT纪录。直到调用dbms_session.reset_package方法之后，生成了STAT相关纪录。从STAT的记录行，可以看到操作以及对应的行数。

对大表的分析

怎样对数据库中的大表进行分析
1)分析前备份统计信息: dbms_stats.EXPORT_TABLE_STATS
2)察看是否统计：user_table中的last_anaylyzed更新日起，user_index表中也存在对应字段。
3)对于数据量大的表要抽样分析，用dbms_stats分析对表的影响比analyze 更小一些。
4)对于数据变化比较频繁的表，需要定时分析，否则会产生性能问题。

参考:http://www.itpub.net/787943,1.html

V$OPEN_CURSOR, V$SESSION_CACHED_CURSOR

概念：
OPEN_CURSOR，定义每个Session最大能够打开的游标数量。在init.ora文件中定义，可以通过select * from v$parameter where name = 'open_cursors'查询。
V$OPEN_CURSOR，当前Session缓存的游标，而不是曾经打开的游标。
V$SESSION_CACHED_CURSOR，当前Session已经关闭并被缓存的游标。
V$OPEN_CURSOR中显示的当前Session游标缓存中游标，如果要精确查询当前Session打开的游标总数，需要从V$ SESSTAT中查询。
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';


Session Cache的原理
当设定SESSION_CACHED_CURSOR的值之后，当有parse请求的时候，Oracle会从library cache中查询。如果有超过3次同样的parse请求，这个游标将会存入Session的游标缓存中。对于将来同样的查询，就甚至不要soft parse，直接从Session的游标缓存中取。

登录两个SQL*PLUS客户端，分别为Session test和Session monitor。

1. 检查是否参数设置，以及执行的SQL语句是否在V$OPEN_CURSOR找到。
Session Test:
SQL> show parameter session_cached_cursors;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
SQL> select sid from v$mystat where rownum=1;

SID
----------
9

SQL> select sid from v$mystat where rownum=1;

SID
----------
9

通过如上的执行结果可以知道，当前参数设置session_cached_cursors的值为0，不缓存当前Session关闭的游标。当前Session的ID为9。
Session Monitor:
SQL> SELECT SID, n.NAME para_name, s.VALUE used
2 FROM SYS.v_$statname n, SYS.v_$sesstat s
3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
4 AND s.statistic# = n.statistic#
5 AND SID = 9;

SID PARA_NAME USED
--- ------------------------------ ----------
9 opened cursors current 1
9 session cursor cache count 0

SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9;

SID USER_NAME SQL_TEXT
--- ---------- ------------------------------------------------------------
9 SCOTT select sid from v$mystat where rownum=1
通过如上的执行结果可以知道，当前在V$OPEN_CURSOR存储一个游标，对应SQL为Session Test执行的最后一条语句。V$SESSION_CACHED_CURSOR没有存储游标。

2. 更改参数V$SESSION_CACHED_CURSOR值。
Session Test:
SQL> alter session set session_cached_cursors = 1;

Session altered.

SQL> show parameter session_cached_cursors;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 1

3. 验证如下结论。
如果游标被存入SESSION_CACHED_CURSOR，前提是游标已经关闭，游标对应的SQL被执行3次以上。OPEN_CURSOR中会存储保存在SESSION_CACHED_CURSOR以及打开的游标（不是精确值）。
Session Test:
SQL> select sid from v$mystat where rownum =1;

SID
----------
9

SQL> select sid from v$mystat where rownum =1;

SID
----------
9

SQL> select sid from v$mystat where rownum =1;

SID
----------
9

Session Monitor:

SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9;

SID USER_NAME SQL_TEXT
--- ---------- ------------------------------------------------------------
9 SCOTT select sid from v$mystat where rownum =1

SQL> SELECT SID, n.NAME para_name, s.VALUE used
2 FROM SYS.v_$statname n, SYS.v_$sesstat s
3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
4 AND s.statistic# = n.statistic#
5 AND SID = 9;

SID PARA_NAME USED
--- ------------------------------ ----------
9 opened cursors current 1
9 session cursor cache count 0
v$open_cursor dooes not show all open cursors. it shows more than that, the best option to find the number of open cursors is from v$sysstat.

通过如上的执行结果可以知道，即使同一个游标被打开3次，在SESSION_CACHED_CURSOR的数量仍然为0。下面，将会在Session Test中关闭游标（通过执行一条其他的语句）。
Session Test:
SQL> select * from t where rownum!=7;

no rows selected
Session Monitor:
SQL> SELECT SID, n.NAME para_name, s.VALUE used
2 FROM SYS.v_$statname n, SYS.v_$sesstat s
3 WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
4 AND s.statistic# = n.statistic#
5 AND SID = 9;

SID PARA_NAME USED
--- ------------------------------ ----------
9 opened cursors current 1
9 session cursor cache count 1

SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9;

SID USER_NAME SQL_TEXT
--- ---------- ------------------------------------------------------------
9 SCOTT select sid from v$mystat where rownum=1
9 SCOTT select * from t where rownum!=7
通过如上的执行结果可以知道，游标被打开3次之后，如果这个游标关闭之后，游标会被存储到SESSION_CACHED_CURSOR当中。同时，通过OPEN_CURSOR中显示的SQL可以得知，OPEN_CURSOR中会存储保存在SESSION_CACHED_CURSOR以及打开的游标（不是精确值）。

SESSION_CACHED_CURSOR采用的是LRU算法，如果如果有新的游标需要缓存，而当前游标缓存已经满，最少使用的游标将会被清除出去。
调整SESSION_CACHED_CURSOR参数。通过如下SQL得到从缓存中取游标以及取PARSE的数量，为调整作参考。
select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;


参考：
Monitoring Open and Cached Cursors
http://www.orafaq.com/node/758
v$open_cursor与session_cached_cursor
http://wzwanghai.spaces.live.com/Blog/cns!1p6cztYuyVBgutMjvxSWkuhw!151.entry

Jinitiator默认大小

问题：
Compuware统计结果显示许多instance的launce transaction都显示为red，证明性能没有达到定义的SLA水平。其实这个Launce transaction的动作就是——打开浏览器，输入ERP的Form登录地址，载入显示登录界面。为什么这么简单的事务性能却很差。作为访问ERP客户的Agent离ERP服务器很近，网络不应该是考虑因素。最后定位为Cache问题。通过控制面板中，点开Jinitiator，察看Cache面板，缓存大小为安装默认的50M。察看存放缓存的目录，已经达到48M。因为在这个Agent上会访问很多的Instance，所以会有相当多的Jar需要Cache。然后，在Basic面板，Enable Java Console，这样可以从Console查看Applet载入的过程。打开一个最近很少访问的ERP地址，从Console可以看到有部分Jar文件需要重新Download（因为不在Cache目录中），有部分Jar从Cache目录中删除（因为已经达到Cache目录的最大限制），这些都是消耗时间的步骤。

解决方法：
增大Cache目录大小。选定无限制，这是因为这个Agent还有20G，而只用来测试ERP性能。

其他：
可以通过控制台设定Java使用的Cache大小，如果系统运行过程中发现是因为客户端速度太慢的问题。先观察一下更改Cache后的结果在说。有资料说The caching mechanism uses a smart expansion algorithm to store JAR files in an uncompressed format。找了很久没有找到到底是什么smart expansion algorithm。算了，也不重要了。

登录Agent后，发现Cache目录的大小刚刚被人改掉了，出手太慢。检查一下Cache目录大小，已经达到61M了。应该和设想的一样。其他性能较差部分，需要再检查一遍QRun的脚本，看到底是服务器性能问题还是脚本问题。

参考：
Metalink: Doc ID: Note:310976.1
http://www.oracle.com/technology/software/products/developer/files/1.3.1.25/readme.html

察看当前Session登录的数据库

如果可以访问V$表，可以通过
select instance_name, version from v$instance;

如果没有相关权限，也可以通过SYS_CONTEXT得到Session登录的数据库。
select SYS_CONTEXT('USERENV' ,'DB_NAME') db_name from dual;

通过sys_context( namespace, parameter, [ length ] )，可以获取Oracle相关的环境信息。如果需要得到当前session的Oracle相关信息，在namespace的参数值为'USERENV'。还可以通过userenv函数来获取当前session的Oracle相关信息，这是一个遗留函数，Oracle推荐使用sys_context来获取。DBMS_SESSION.set_context设置parameter的值。

select SYS_CONTEXT('USERENV' ,'TERMINAL') terminal from dual;
select SYS_CONTEXT('USERENV' ,'LANGUAGE') language from dual;
select SYS_CONTEXT('USERENV' ,'SESSIONID') sessionid from dual;
select SYS_CONTEXT('USERENV' ,'INSTANCE') instance from dual;
select SYS_CONTEXT('USERENV' ,'ENTRYID') entryid from dual;
select SYS_CONTEXT('USERENV' ,'ISDBA') isdba from dual;
select SYS_CONTEXT('USERENV' ,'NLS_TERRITORY') nls_territory from dual;
select SYS_CONTEXT('USERENV' ,'NLS_CURRENCY') nls_currency from dual;
select SYS_CONTEXT('USERENV' ,'NLS_CALENDAR') nls_calendar from dual;
select SYS_CONTEXT('USERENV' ,'NLS_DATE_FORMAT') nls_date_format from dual;
select SYS_CONTEXT('USERENV' ,'NLS_DATE_LANGUAGE') nls_date_language from dual;
select SYS_CONTEXT('USERENV' ,'NLS_SORT') nls_sort from dual;
select SYS_CONTEXT('USERENV' ,'CURRENT_USER') current_user from dual;
select SYS_CONTEXT('USERENV' ,'CURRENT_USERID') current_userid from dual;
select SYS_CONTEXT('USERENV' ,'SESSION_USER') session_user from dual;
select SYS_CONTEXT('USERENV' ,'SESSION_USERID') session_userid from dual;
select SYS_CONTEXT('USERENV' ,'PROXY_USER') proxy_user from dual;
select SYS_CONTEXT('USERENV' ,'PROXY_USERID') proxy_userid from dual;
select SYS_CONTEXT('USERENV' ,'DB_DOMAIN') db_domain from dual;
select SYS_CONTEXT('USERENV' ,'DB_NAME') db_name from dual;
select SYS_CONTEXT('USERENV' ,'HOST') host from dual;
select SYS_CONTEXT('USERENV' ,'OS_USER') os_user from dual;
select SYS_CONTEXT('USERENV' ,'EXTERNAL_NAME') external_name from dual;
select SYS_CONTEXT('USERENV' ,'IP_ADDRESS') ip_address from dual;
select SYS_CONTEXT('USERENV' ,'NETWORK_PROTOCOL') network_protocol from dual;
select SYS_CONTEXT('USERENV' ,'BG_JOB_ID') bg_job_id from dual;
select SYS_CONTEXT('USERENV' ,'FG_JOB_ID') fg_job_id from dual;
select SYS_CONTEXT('USERENV' ,'AUTHENTICATION_TYPE') authentication_type from dual;
select SYS_CONTEXT('USERENV' ,'AUTHENTICATION_DATA') authentication_data from dual;

执行DBMS_SESSION的SET_CONTEXT函数
http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-11-18.7319544765

定义SQL*PLUS类型的并发请求

在定义可执行的时候，其中有一种类型为SQL*PLUS。虽然可能使用不频繁，但是这种类型在系统中的确存在。可以通过如下SQL查询当前系统中，各种类型可执行的数量。
SELECT COUNT (*), meaning
FROM fnd_executables_vl fev, fnd_lookups fl
WHERE fev.execution_method_code = fl.lookup_code(+)
AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
GROUP BY meaning;
通过输出结果可以发现，ERP中最多的类型为PL/SQL Stored Procedure，有2000多个，SQL*Plus只有300多个，Java类型的70个。记得Tom曾经说过，如果你的任务能够用SQL完成，那么就选择SQL，其次选在SQL*PLUS，然后PL/SQL，然后Java Procedure，然后C*PROC。Oracle绝大多数的并发请求都通过PL/SQL Stored Procedure实现。

那么怎样定义一个SQL*PLUS的并发请求呢？
1. 编写SQL*PLUS脚本，后缀为.sql。
clear buffer;
set heading on
set verify off
set feed off
set linesize 80
set pagesize 4
set underline '='
set serveroutput on size 1000000
begin
dbms_output.put_line('this is output by procedure dbms_output');
end;
/
set heading off
prompt
select application_short_name app_name from applsys.fnd_application where rownum <>

手工修改spife出现错误

问题：
正在阅读到Expert One on One Oracle，想验证log_buffer的最小值为最大数据块的4倍。打开位于${ORACLE_HOME}/database/ SPFILEORA9I.ORA的数据库参数文件，发现没有对应的参数条目，然后手工增加log_buffer=1，通过服务控制台重新启动数据库。然后通过sql/plus连接，发现不能连接数据库。出现的错误为“ORA- 01034 : ORACLE not available”，“ORA-27101 : shared memory realm does not exist”。Google一下，许多人提到这是因为数据库没有启动。手工启动数据库，相关命令如下：
c:>sqlplus /nolog
sql>conn sys/abcdefg@server as sysdba
sql>startup
而执行如上语句，能够连接数据库，但是startup的时候，系统却报错：“ora-00600,[][][]”。通过“Database Configuration Assistant”进行参数修改的时候，出现同样的错误。

解决方法：

Google一下，原来有人和我犯了同样的错误：Sfile是一个二进制文件，不能通过文本的方式修改。如果需要修改，必须通过“alter system set ... scope=.. ”或者先修改，然后通过“create spfile from pfile;”将pfile里的内容复制到spfile里。 参考：http://www.itpub.net/226670,1.html。

1) 将${ORACLE_HOME}/admin/ora9i/pfile/init.ora.4162007215332文件（这是数据库的pfile参数文件，8i前使用，可以手工编辑）拷贝到${ORACLE_HOME}/database目录下，更名为init.ora。

2) 运行如下命令，使用Pfile参数文件打开数据库。
sqlplus /nolog
conn sys/abcdefg@server as sysdba
startup pfile =${ORACLE_HOME}/databaseinit.ora;

3) 从Pfile重新创建Spfile。create spfile from pfile = 'init.ora';

Spfile相关内容：
找到了eygle关于Spfile的文章（http://www.eygle.com/faq/Oracle9i.New.Feature.Spfile.01.htm）。

1). Oracle通过startup时候，搜索参数文件的顺序：
a.spfile${ORACLE_SID}.ora in (NT: ${ORACLE_HOME}/database)
b.spfile.ora in (NT: ${ORACLE_HOME}/database)
c.init${ORACLESID}.ora in (NT: ${ORACLE_HOME}/database or${ORACLE_HOME}/admindb_name/pfile)

2).修改参数ALTER SYSTEM ，SCOPE参数有三个可选值：MEMORY ,SPFILE , BOTH。

3).查询是否使用了Spfile。

SELECT name,value FROM v$parameter WHERE name='spfile';
SHOW PARAMETER spfile;
SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;

4).使用Rman备份Spfile。

5).使用Spfile屏蔽或者更改Oracle行为。alter system set event='10841 trace name context forever' scope=spfile;

其他问题。

解决问题的过程中，使用“Startup pfile”命令的时候还出现过“ORA-01031: insufficient privileges upon instance startup”错误。一般检查两个地方，登录系统的O/S用户是否在ORA_DBA用户组中。另为，是否配置${ORACLE_HOME}/network/admin下的sqlnet.ora文件是否存在，以及是否有“SQLNET.AUTHENTICATION_SERVICES = (NTS)”条目，否在Oracle不能使用O/S认证。