Skip to Main Content
  • Questions

  • Autonomous transactions and commit point optimization

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, lh.

Asked: October 13, 2021 - 4:02 pm UTC

Last updated: October 28, 2021 - 3:04 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi


We have an application written with PL/SQL and thus with commit point optimization. However AWR reports show a lot of log file sync waits. How do autonomous transactions, which we are using for logging purposes operate with pl/sql code. Is the commit point optimization utilized?

If some procedure is called which is defined as an autonomous transaction and no actual dml operations are done (which could happen in logging operations), is there still some commit handling done ? If so, what ?



lh

and Connor said...

Looks like we still take advantage of it, even if the autonomous part is doing dml, eg

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------------------------------------------------------------------------------
981

SQL> create table t ( x int );

Table created.

SQL>
SQL> create or replace
  2  procedure my_logger is
  3     pragma autonomous_transaction;
  4  begin
  5     commit;
  6  end;
  7  /

Procedure created.

SQL>
SQL> select sid, event, total_waits
  2  from v$session_event
  3  where event like 'log file%';

       SID EVENT                                        TOTAL_WAITS
---------- -------------------------------------------- -----------
         3 log file sequential read                             104
       126 log file sequential read                              50
       371 log file sequential read                              16
       490 log file sequential read                              18
       490 log file single write                                 18
       490 log file parallel write                           355034
       734 log file parallel write                           270283
       978 log file parallel write                            77950
       981 log file sync                                          2
      1106 log file sync                                       1872
      1223 log file sequential read                              45

11 rows selected.

SQL>
SQL> begin
  2  for i in 1 .. 500000 loop
  3    insert into t values (i);
  4    commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select sid, event, total_waits
  2  from v$session_event
  3  where event like 'log file%';

       SID EVENT                                        TOTAL_WAITS
---------- -------------------------------------------- -----------
         3 log file sequential read                             104
       126 log file sequential read                              82
       371 log file sequential read                              16
       490 log file sequential read                              20
       490 log file single write                                 20
       490 log file parallel write                           467239
       734 log file parallel write                           408822
       978 log file parallel write                           212621
       981 log file switch (private strand flush incomp           2
           lete)

       981 log file sync                                          3
      1106 log file sync                                       1872
      1223 log file sequential read                              45

12 rows selected.

SQL>
SQL>
SQL>
SQL> begin
  2  for i in 1 .. 500000 loop
  3    insert into t values (i);
  4    my_logger;
  5    commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select sid, event, total_waits
  2  from v$session_event
  3  where event like 'log file%';

       SID EVENT                                        TOTAL_WAITS
---------- -------------------------------------------- -----------
         3 log file sequential read                             104
       126 log file sequential read                              82
       371 log file sequential read                              44
       490 log file sequential read                              22
       490 log file single write                                 22
       490 log file parallel write                           528138
       734 log file parallel write                           597315
       978 log file parallel write                           393023
       981 log file switch (private strand flush incomp           4
           lete)

       981 log file sync                                          4
      1106 log file sync                                       1872
      1223 log file sequential read                              45

12 rows selected.

SQL> create or replace
  2  procedure my_logger is
  3     pragma autonomous_transaction;
  4  begin
  5     insert into t1 values (0);
  6     commit;
  7  end;
  8  /

Procedure created.

SQL> select sid, event, total_waits
  2  from v$session_event
  3  where event like 'log file%';

       SID EVENT                                        TOTAL_WAITS
---------- -------------------------------------------- -----------
         3 log file sequential read                             104
       126 log file sequential read                              82
       371 log file sequential read                              44
       490 log file sequential read                              22
       490 log file single write                                 22
       490 log file parallel write                           528238
       734 log file parallel write                           597315
       978 log file parallel write                           393023
       981 log file switch (private strand flush incomp           4
           lete)

       981 log file sync                                          5
      1106 log file sync                                       1872
      1223 log file sequential read                              45
      1348 log file sync                                          4

13 rows selected.

SQL>
SQL>
SQL> begin
  2  for i in 1 .. 500000 loop
  3    insert into t values (i);
  4    my_logger;
  5    commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> select sid, event, total_waits
  2  from v$session_event
  3  where event like 'log file%';

       SID EVENT                                        TOTAL_WAITS
---------- -------------------------------------------- -----------
         3 log file sequential read                             104
       126 log file sequential read                              82
       371 log file sequential read                              44
       490 log file sequential read                              24
       490 log file single write                                 24
       490 log file parallel write                          1187259
       734 log file parallel write                           597315
       978 log file parallel write                           393023
       981 log file switch (private strand flush incomp           4
           lete)

       981 log file switch completion                             2
       981 log file sync                                          6
      1106 log file sync                                       1872
      1223 log file sequential read                              59
      1348 log file sync                                          4

14 rows selected.

SQL>
SQL>


log file sync for sid 981 creeps up by 1 each time, even with the 500k calls to an autonomous txn proc

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

A reader, October 21, 2021 - 6:53 am UTC


Connor McDonald
October 22, 2021 - 3:14 am UTC

glad we could help

commit point optimization and execute immediate

lh, October 27, 2021 - 3:42 pm UTC

Hi

We are still suffering from lot of 'log file sync' events even if the application is almost solely coded with pl/sql and there are only few 'commit work' calls.

We were wondering if commit point optimization does not work when procedure is called by "execute immedate 'Begin ...end'; style.
Does commit work type of operation occur when procedure is executed.

We were using dynamic sql because
- there are the procedure to be called is dynamic
- it this procedure is hard coded in one giant case statement, and program is executing, one could not update this procedure.

How exactly commit point optimization is working ?

lh
Connor McDonald
October 28, 2021 - 3:04 am UTC

Got a test case you can throw our way and we'll take a look
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library