【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之前的操作问题,需要具体问题具体分析。