[Oracle]-[impact of COMMIT on indexes]-view the impact of COMMIT on indexes from the trace

[Oracle]-[impact of COMMIT on indexes]-from trace, we can see the impact of COMMIT on indexes. Recently, due to work requirements, there is a task involving data migration, therefore, I have been paying attention to the time-consuming issue of COMMIT and want to follow the old Yang's method to see if the aforementioned COMMIT has an impact on common indexes. Test environment: Oracle 10.2.0.4 + Linux x86_64 Case 1: COMMIT operation after INSERT.

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.

 

Case 2: COMMIT operation after DELETE. Re-Login

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.

 

Here, re-login and trace are used to prevent reuse of session cache cursors so that the results are clearer. Trace file of Case 1:

* ** 08:56:57. 328 *** action name :() 08:56:57. 328 *** module name :( sqlplus @ vm-vmw4131-t (TNS V1-V3) 08:56:57. 328 *** service name :( SYS $ USERS) 08:56:57. 328 *** session id: (508.20733) 08:56:57. 327 ================================ parsing in cursor #1 len = 6 dep = 0 uid = 0 oct = 44 lid = 0 tim = 1343000212234337 hv = 3480936638 ad = '0' commitEND of stmtparse #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 = falseEND OF STMTPARSE #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 Example 2 trace file: *** 08:57:43. 829 *** action name :() 08:57:43. 828 *** module name :( sqlplus @ vm-vmw4131-t (TNS V1-V3) 08:57:43. 828 *** service name :( SYS $ USERS) 08:57:43. 828 *** session id: (508.20743) 08:57:43. 828 ================================ parsing in cursor #3 len = 6 dep = 0 uid = 0 oct = 44 lid = 0 tim = 1343000257645312 hv = 3480936638 ad = '0' commitEND of stmtparse #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 = falseEND 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

 

It can be seen that the trace after the two operations only contains the COMMIT operation, and there is no maintenance operation similar to the full-text index mentioned in the article. In other words, I understand that the COMMIT operation itself does not consume much time except for triggering LGWR. If the COMMIT takes a long time, on the one hand, it may be a problem with LGWR, on the other hand, it may be a problem with the operations before COMMIT, and the specific problem needs to be analyzed.