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.
Thurs 7th June 2012 17:00 CEST 16:00 BST 11.00 EDT 10.00 CDT 08.00 PDT
Oracle Logbook 