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

Source: Internet
Author: User

[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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.