oracle 影响索引吗,【Oracle】-【COMMIT对索引的影响】-从trace看COMMIT对索引的影响...

【Oracle】-【COMMIT对索引的影响】-从trace看COMMIT对索引的影响

最近因为工作上的需求，有个任务涉及到数据迁移，因此一直关注COMMIT耗时的问题，就想按照老杨的方法，看看对于普通索引，上述所说的COMMIT是否有影响。

测试环境：Oracle 10.2.0.4+Linux x86_64

用例1：INSERT后COMMIT操作。

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index t_idx on t(object_id);

Index created.

SQL> insert into t(object_id) values(1);

1 row created.

SQL> alter session set sql_trace=true;

Session altered.

SQL> commit;

Commit complete.

SQL> alter session set sql_trace=false;

Session altered.

用例2：DELETE后COMMIT操作。

重登陆

SQL> delete from t where object_id=1;

1 row deleted.

SQL> alter session set sql_trace=true;

Session altered.

SQL> commit;

Commit complete.

SQL> alter session set sql_trace=false;

Session altered.

这里重登陆再trace是为了防止重用会话缓存的游标，从而使结果更清晰。

用例1的trace文件：

*** 2013-07-31 08:56:57.328

*** ACTION NAME:() 2013-07-31 08:56:57.328

*** MODULE NAME:(sqlplus@vm-vmw4131-t (TNS V1-V3)) 2013-07-31 08:56:57.328

*** SERVICE NAME:(SYS$USERS) 2013-07-31 08:56:57.328

*** SESSION ID:(508.20733) 2013-07-31 08:56:57.327

=====================

PARSING IN CURSOR #1 len=6 dep=0 uid=0 oct=44 lid=0 tim=1343000212234337 hv=3480936638 ad='0'

commit

END OF STMT

PARSE #1:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1343000212234330

XCTEND rlbk=0, rd_only=0

EXEC #1:c=0,e=374,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=1343000212235249

=====================

PARSING IN CURSOR #2 len=33 dep=0 uid=0 oct=42 lid=0 tim=1343000219675725 hv=525901419 ad='0'

alter session set sql_trace=false

END OF STMT

PARSE #2:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1343000219675717

EXEC #2:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1343000219675914

用例2的trace文件：

*** 2013-07-31 08:57:43.829

*** ACTION NAME:() 2013-07-31 08:57:43.828

*** MODULE NAME:(sqlplus@vm-vmw4131-t (TNS V1-V3)) 2013-07-31 08:57:43.828

*** SERVICE NAME:(SYS$USERS) 2013-07-31 08:57:43.828

*** SESSION ID:(508.20743) 2013-07-31 08:57:43.828

=====================

PARSING IN CURSOR #3 len=6 dep=0 uid=0 oct=44 lid=0 tim=1343000257645312 hv=3480936638 ad='0'

commit

END OF STMT

PARSE #3:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1343000257645304

XCTEND rlbk=0, rd_only=0

EXEC #3:c=0,e=424,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=1343000257646177

=====================

PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=42 lid=0 tim=1343000265207698 hv=525901419 ad='0'

alter session set sql_trace=false

END OF STMT

PARSE #1:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1343000265207690

EXEC #1:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1343000265207917

由此可见，两种操作后的trace显示仅仅包含COMMIT操作，并没有类似文章中提到的对全文索引那样的维护操作。换句话说，我理解COMMIT操作自身除触发LGWR外，没有其它的耗时。如果COMMIT的时间长，一方面可能是LGWR的问题，另一方面可能是COMMIT之前的操作问题，需要具体问题具体分析。