Oracle Logbook

April 27, 2012

Avoiding redolog delays using commit_wait and commit_logging

Filed under: Uncategorized — heliodias @ 11:44 pm

Abstract:

This post shows how to get a lot of benefits regarding improve the redolog written,  having as tradeoff  a tiny little chance to lost commited transactions in a event of crash.

Those who can´t afford lose a commited transaction e.g. (BANKs, Financial corporations, Hospitals) should disregard what I´m going to present.

 

Oracle have the hability to group together redo operations instead of writting as fast as it could, by changing parameters

For the experimentation , I advice to open two sessions, one for control the other for the operations undergoing.

session 1
create table l3_t1 (id number);
session 2
select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/
set linesize 28
set pagesize 0
spool insert1.sql
select ‘insert into l3_t1 values(1); commit;’ from dba_objects
where rownum <10001;

@insert1
select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/

/*After 10 thousands of inserts you should keep your eyes the the around 10000 messages and 500 sync time  */

NAME                                                                  VALUE
—————————————————————- ———-
messages sent                                                         10002
redo entries                                                          10079
redo size                                                           5150552
redo synch time                                                         552
redo synch writes                                                     10002

@insert1
select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/

/*After the second round of more 10 thousands of inserts you should keep your eyes the the around 20000 messages and 920 sync time  */
NAME                                                                  VALUE
—————————————————————- ———-
messages sent                                                         20003
redo entries                                                          20166
redo size                                                          10299892
redo synch time                                                         920
redo synch writes                                                     20003

==============
Session 1

SQL> alter system set commit_logging=batch scope=both;

System altered.

SQL> alter system set commit_wait=nowait scope=both;

System altered.
===============

Session 2

@insert1

select name,value from v$statname t1,v$mystat t2 where t1.statistic#=t2.statistic#
and value>0
and (name like ‘redo%’ or name =’messages sent’)
order by 1
/

/*Now the magic , you almost didn´t have messages nor sync time, even doing once again more 10.000 inserts & commits  */
NAME                                                                  VALUE
—————————————————————- ———-
messages sent                                                         20008
redo entries                                                          34497
redo size                                                          15107148
redo synch time                                                         921
redo synch writes                                                     20005

 

I stated before that was “tiny,little chance” due to the fact that in the event of a crash , to actually loose some information, Oracle had buffered grouped redo, which is unlikelly, but not impossible.

Once again, don´t  use it, unless the loss of transaction is not a big deal for you.

Just to make myself clear, the stacks are lose in the worse case the last few seconds of transaction. If this will cost you nothing, and you are suffering from redo sync wait event , this could be a solution.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: