 | | | Subject: Re: Asynchronous commit - wait or no wait 10GR2 | Subject: Re: Asynchronous commit - wait or no wait 10GR2 2007-10-03 - By Job Miller
Back
PL/SQL has always done this "optimization" automatically. so the default
within a pl/sql procedure is to commit upon return to the caller. the cwiw
setting must override the default pl/sql optimization.
Job
John Hallas <john.hallas@(protected)> wrote: st1\:*{behavior:url(
#default#ieooui) } 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
-- ---- ---- ---- ---- ---- -----
Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.
PL/SQL has always done this "optimization" automatically. so the default
within a pl/sql procedure is to commit upon return to the caller.
the cwiw setting must override the default pl/sql optimization.<br><br
>Job<br><br><b><i>John Hallas <john.hallas@(protected)></i></b> wrote:
<blockquote class="replbq" style="border-left: 2px solid rgb(16, 16, 255);
margin-left: 5px; padding-left: 5px;"> <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> </o:SmartTagType><div class=
"Section1"> <div class="MsoNormal"><font face="Comic Sans MS" size="2"><span
style="font-size: 10pt; 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></div> <div class="MsoNormal"><font face="Comic Sans MS"
size="2"><span style="font-size: 10pt; font-family: "Comic Sans MS";"
><o:p> </o:p></span></font></div> <div class="MsoNormal"><font face="Comic
Sans MS" size="2"><span style="font-size: 10pt; 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></div
> <div class="MsoNormal"><font face="Comic Sans MS" size="2"><span style="font
-size: 10pt; font-family: "Comic Sans MS";"><o:p> </o:p></span><
/font></div> <div class="MsoNormal"><font face="Comic Sans MS" size="2"><span
style="font-size: 10pt; font-family: "Comic Sans MS";">A simple
stored procedure<o:p></o:p></span></font></div> <div class="MsoNormal" style=
"margin-right: 74pt; margin-left: 45.8pt;
margin-bottom: 0.0001pt;"><font face="Comic Sans MS" size="2"><span style=
"font-size: 10pt; font-family: "Comic Sans MS";"><o:p> </o:p><
/span></font></div> <div class="MsoNormal" style="margin-right: 74pt; margin
-left: 36pt; margin-bottom: 0.0001pt;"><font face="Comic Sans MS" size="2"><span
style="font-size: 10pt; font-family: "Comic Sans MS";">create table
loaded1 ( id number, name varchar2(8), last_modified date);<o:p></o
:p></span></font></div> <div class="MsoNormal"><font face="Comic Sans MS" size=
"2"><span style="font-size: 10pt; font-family: "Comic Sans MS";"><o:p>
</o:p></span></font></div> <div class="MsoNormal"><font face="Comic Sans
MS" size="2"><span style="font-size: 10pt; font-family: "Comic Sans MS
";"><o:p> </o:p></span></font></div> <div class="MsoNormal" style=
"margin-left: 36pt;"><font face="Comic Sans MS" size="2"><span style="font-size:
10pt; font-family: "Comic Sans
MS";">create or replace procedure sp4 ( loops in number)<o:p></o:p>
</span></font></div> <div class="MsoNormal" style="margin-left: 36pt;"><font
face="Comic Sans MS" size="2"><span style="font-size: 10pt; font-family: "
;Comic Sans MS";">as<o:p></o:p></span></font></div> <div class="MsoNormal"
style="margin-left: 36pt;"><font face="Comic Sans MS" size="2"><span style=
"font-size: 10pt; font-family: "Comic Sans MS";">cnt number :=0;<o:p><
/o:p></span></font></div> <div class="MsoNormal" style="margin-left: 36pt;">
<font face="Comic Sans MS" size="2"><span style="font-size: 10pt; font-family:
"Comic Sans MS";"><o:p> </o:p></span></font></div> <div class=
"MsoNormal" style="margin-left: 36pt;"><font face="Comic Sans MS" size="2"><span
style="font-size: 10pt; font-family: "Comic Sans MS";">begin<o:p></o
:p></span></font></div> <div class="MsoNormal" style="margin-left: 36pt;"><font
face="Comic Sans MS" size="2"><span
style="font-size: 10pt; font-family: "Comic Sans MS";">while (cnt
< loops)<o:p></o:p></span></font></div> <div class="MsoNormal" style="margin
-left: 36pt;"><font face="Comic Sans MS" size="2"><span style="font-size: 10pt;
font-family: "Comic Sans MS";">
<st1:place w:st="on">LOOP</st1:place><o:p></o:p></span></font></div>
<div class="MsoNormal" style="margin-left: 36pt;"><font face="Comic Sans MS"
size="2"><span style="font-size: 10pt; font-family: "Comic Sans MS";"
> insert into loaded1 values (cnt,
'abcdefgh',sysdate);<o:p></o:p></span></font></div> <div class="MsoNormal"
style="margin-left: 36pt;"><font face="Comic Sans MS" size="2"><span style=
"font-size: 10pt; font-family: "Comic Sans MS";">
commit write immediate nowait; XXXXXXXXX<o
:p></o:p></span></font></div> <div class="MsoNormal"
style="margin-left: 36pt;"><font face="Comic Sans MS" size="2"><span style=
"font-size: 10pt; font-family: "Comic Sans MS";">
cnt := cnt+1;<o:p></o:p></span></font></div> <div
class="MsoNormal" style="margin-left: 36pt;"><font face="Comic Sans MS" size="2
"><span style="font-size: 10pt; font-family: "Comic Sans MS";">
END <st1:place w:st="on">LOOP</st1:place>;
<o:p></o:p></span></font></div> <div class="MsoNormal" style="margin-left: 36pt
;"><font face="Comic Sans MS" size="2"><span style="font-size: 10pt; font-family
: "Comic Sans MS";">commit;<o:p></o:p></span></font></div> <div class
="MsoNormal" style="margin-left: 36pt;"><font face="Comic Sans MS" size="2">
<span style="font-size: 10pt; font-family: "Comic Sans MS";"> end
;<o:p></o:p></span></font></div> <div class="MsoNormal" style="margin-left:
36pt;"><font face="Comic Sans MS" size="2"><span
style="font-size: 10pt; font-family: "Comic Sans MS";">/<o:p></o:p><
/span></font></div> <div class="MsoNormal"><font face="Comic Sans MS" size="2">
<span style="font-size: 10pt; 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></div> <div class="MsoNormal"><font face=
"Comic Sans MS" size="2"><span style="font-size: 10pt; font-family: "Comic
Sans MS";"><o:p> </o:p></span></font></div> <div class="MsoNormal">
<font face="Comic Sans MS" size="2"><span style="font-size: 10pt; font-family:
"Comic Sans MS";">Timings<o:p></o:p></span></font></div> <div class=
"MsoNormal"><font face="Comic Sans MS" size="2"><span style="font-size: 10pt;
font-family: "Comic Sans
MS";"><o:p> </o:p></span></font></div> <div class="MsoNormal"><font
face="Comic Sans MS" size="2"><span style="font-size: 10pt; font-family: "
;Comic Sans MS";">CWIW 5000 records 57 secs<o:p></o:p></span></font></div>
<div class="MsoNormal"><font face="Comic Sans MS" size="2"><span style="font
-size: 10pt; font-family: "Comic Sans MS";">CWINW 5000 records 3
seconds<o:p></o:p></span></font></div> <div class="MsoNormal"><font face=
"Comic Sans MS" size="2"><span style="font-size: 10pt; font-family: "Comic
Sans MS";">Commit 5000 records 3 seconds<o:p></o:p></span></font></div>
<div class="MsoNormal"><font face="Comic Sans MS" size="2"><span style="font
-size: 10pt; font-family: "Comic Sans MS";"><o:p> </o:p></span><
/font></div> <div class="MsoNormal"><font face="Comic Sans MS" size="2"><span
style="font-size: 10pt; 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></div> <div class="MsoNormal"><font face="Comic Sans
MS" size="2"><span style="font-size: 10pt; font-family: "Comic Sans MS
";"><o:p> </o:p></span></font></div> <div class="MsoNormal"><font
face="Comic Sans MS" size="2"><span style="font-size: 10pt; font-family: "
;Comic Sans MS";">Am I missing something here <o:p></o:p></span></font><
/div> <div><font face="Times New Roman" size="2"><span style="font-size: 10pt;"
> John<o:p></o:p></span></font></div> <div><font face="Times New Roman"
size="2"><span style="font-size: 10pt;">+44 (0)113 223 2274 (direct)<o:p></o:p>
</span></font></div> <div><font face="Times New Roman" size="2"><span style=
"font-size: 10pt;">+44 (0)113 297 9797</span></font><font size="2"><span style=
"font-size: 10pt;"><o:p></o:p></span></font></div>
<div class="MsoNormal"><font face="Times New Roman" size="3"><span style="font
-size: 12pt;"><o:p> </o:p></span></font></div> </div> <div><div></div>
<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> <div></div></div> </blockquote><br><p> 
<hr size=1>Be a better Heartthrob. <a href="http://us.rd.yahoo.com/evt
=48255/*http://answers.yahoo.com/dir/_ylc
=X3oDMTI5MGx2aThyBF9TAzIxMTU1MDAzNTIEX3MDMzk2NTQ1MTAzBHNlYwNCQUJwaWxsYXJfTklfMz
YwBHNsawNQcm9kdWN0X3F1ZXN0aW9uX3BhZ2U-?link=list&sid=396545433">Get better
relationship answers </a>from someone who knows.<br>Yahoo! Answers - Check it
out.
|
|
|