 | | | Subject: Asynchronous commit - wait or no wait 10GR2 | Subject: Asynchronous commit - wait or no wait 10GR2 2007-10-03 - By John Hallas
Back
In 10GR2 there is an option to commit immediate nowait which allows the
commit to return before the redo is persistent in the redo log. This is
not the default. The default commit remains as commit write immediate
wait.
I am testing Sybase and Oracle inserts and I have noticed an oddity with
in Oracle. Solaris 10G T2000 server 10.2.0.3
A simple stored procedure
create table loaded1 ( id number, name varchar2(8), last_modified
date);
create or replace procedure sp4 ( loops in number)
as
cnt number :=0;
begin
while (cnt < loops)
LOOP
insert into loaded1 values (cnt,'abcdefgh',sysdate);
commit write immediate nowait; XXXXXXXXX
cnt := cnt+1;
END LOOP;
commit;
end;
/
Where line xxxxxxxx can be commit write immediate wait;, commit write
immediate nowait; , commit; The first and last should be identical and
be the Oracle default. My parameter commit_write is not set. I am
truncating the table after each run.
Timings
CWIW 5000 records 57 secs
CWINW 5000 records 3 seconds
Commit 5000 records 3 seconds
These figures are repeatable. I think that the first and longest option
should be the Oracle default according to all the documentation but I am
thinking that in fact the 2nd option (nowait) is the real default.
Am I missing something here
John
+44 (0)113 223 2274 (direct)
+44 (0)113 297 9797
BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.
Registered in England with company number 2777575.
http://www.bjss.co.uk
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas
-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office
:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place" downloadurl="http://www.5iantlavalamp.com/"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@(protected)
{font-family:"Comic Sans MS";
panose-1 (See http://ose-1.ora-code.com):3 15 7 2 3 3 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Comic Sans MS";
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@(protected) Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>In 10GR2 there is an option to commit
immediate nowait which allows the commit to return before the redo is
persistent in the redo log. This is not the default. The default commit remains
as commit write immediate wait.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>I am testing Sybase and Oracle inserts and
I have noticed an oddity with in Oracle. Solaris 10G T2000 server 10.2.0.3<o:p>
</o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>A simple stored procedure<o:p></o:p></span>
</font></p>
<p class=MsoNormal style='mso-margin-top-alt:0cm;margin-right:74.0pt;
margin-bottom:0cm;margin-left:45.8pt;margin-bottom:.0001pt'><font size=2
face="Comic Sans MS"><span style='font-size:10.0pt;font-family:"Comic Sans MS"'
><o:p> </o:p></span></font></p>
<p class=MsoNormal style='mso-margin-top-alt:0cm;margin-right:74.0pt;
margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt'><font size=2
face="Comic Sans MS"><span style='font-size:10.0pt;font-family:"Comic Sans MS"'
>create
table loaded1 ( id number, name varchar2(8), last_modified date);<o
:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>create or replace
procedure sp4 ( loops in number)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>as<o:p></o:p></span></font
></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>cnt number :=0;<o:p></o:p>
</span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>begin<o:p></o:p></span><
/font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>while (cnt < loops)<o:p
></o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>
<st1:place w:st="on">LOOP</st1:place><o:p></o:p></span></font
></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>
insert into
loaded1 values (cnt,'abcdefgh',sysdate);<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>
commit write
immediate nowait; XXXXXXXXX<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>
cnt := cnt+1;<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>
END <st1:place
w:st="on">LOOP</st1:place>;<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>commit;<o:p></o:p></span><
/font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'> end;<o:p></o:p><
/span></font></p>
<p class=MsoNormal style='margin-left:36.0pt'><font size=2 face="Comic Sans MS"
><span
style='font-size:10.0pt;font-family:"Comic Sans MS"'>/<o:p></o:p></span></font>
</p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>Where line xxxxxxxx can be commit write
immediate wait;, commit write immediate nowait; , commit; The first and last
should be identical and be the Oracle default. My parameter commit_write is not
set. I am truncating the table after each run.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>Timings<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>CWIW 5000 records 57 secs<o:p></o:p></span>
</font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>CWINW 5000 records 3 seconds<o:p></o:p><
/span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>Commit 5000 records 3 seconds<o:p></o:p><
/span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>These figures are repeatable. I think that
the first and longest option should be the Oracle default according to all the
documentation but I am thinking that in fact the 2<sup>nd</sup> option (nowait)
is the real default.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt;font-family:"Comic Sans MS"'>Am I missing something here <o:p></o:p><
/span></font></p>
<p><font size=2 face="Times New Roman"><span style='font-size:10.0pt'> 
;John<o:p></o:p></span></font></p>
<p><font size=2 face="Times New Roman"><span style='font-size:10.0pt'>+44
(0)113 223 2274 (direct)<o:p></o:p></span></font></p>
<p><font size=2 face="Times New Roman"><span style='font-size:10.0pt'>+44
(0)113 297 9797</span></font><font size=2><span style='font-size:10.0pt'><o:p><
/o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<DIV><P><HR>
<BR>
BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.<BR>
Registered in England with company number 2777575.<BR>
http://www.bjss.co.uk<BR>
</P></DIV>
</body>
</html>
|
|
|