Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Direct I/O, better performance?

RE: Direct I/O, better performance?

From: Roger Xu <roger_xu_at_dp7uptx.com>
Date: Tue, 19 Oct 2004 14:59:01 -0500
Message-ID: <A6801E8A03316A4DA597866F77A013F7051836E7@irv2kexch01> 





Thank=20you=20all=20for=20replying=20my=20email.=20You=20guys=20are=20awes=
ome.


Lots=20of=20good=20ideas=20and=20deep=20thoughts.
My=20expectation=20was=20to=20improve=20overall=20performance,=20not=20jus=
t


statistics=20gathering.=20


I=20think=20I=20am=20going=20to=20turn=20off=20"direct=20I/O",=20because=20=
I=20also


found=20out=20the=20datafiles=20backup=20ran=20slower=20than=20before.
Thanks=20again.



-----Original=20Message-----


From:=20Bobak,=20Mark=20[mailto:Mark.Bobak_at_il.proquest.com]

Sent:=20Tuesday,=20October=2019,=202004=202:46=20PM
To:=20Roger=20Xu;=20Oracle-L_at_Freelists.=20Org=20(E-mail)
Subject:=20RE:=20Direct=20I/O,=20better=20performance?






Roger,



Why=20would=20you=20expect=20the=20statistics=20gathering=20process=20to=20=
improve


performance?=20=20Did=20you=20identify=20some=20inefficiency=20in=20the=20=
process


which=20you=20determined=20would=20be=20addressed=20by=20switching=20to=20=
direct=20I/O?



In=20general,=20I=20think=20it's=20safe=20to=20say=20that=20direct=20I/O=20=
is=20better


than=20buffered.=20=20However,=20I=20would=20not=20expect=20an=20instant=20=



performance=20increase=20with=20something=20like=20stats=20gathering.
The=20idea=20with=20direct=20I/O=20is=20that=20the=20O/S=20does=20not=20at=
tempt


to=20buffer=20the=20datafiles=20in=20memory.=20=20This=20frees=20that=20me=
mory


and=20allows=20it=20to=20be=20allocated=20to=20the=20Oracle=20kernel=20(SG=
A),


where=20it=20may=20(perhaps)=20be=20used=20to=20allocate=20a=20KEEP=20and/=
or


RECYCLE=20buffer=20pool,=20allowing=20Oracle=20to=20manage=20the=20bufferi=
ng


of=20datafiles=20directly,=20rather=20than=20allowing=20the=20O/S
to=20attempt=20to=20do=20so.=20=20The=20idea=20is=20that=20the=20Oracle
kernel=20knows=20more=20about=20how=20data=20in=20the=20datafiles
is=20used=20than=20the=20O/S,=20and=20therefore=20should=20be=20better
at=20managing=20memory=20dedicated=20to=20buffering=20datafile
contents.



As=20to=20the=20slowness=20with=20statistics=20collection,=20well,=20I=20t=
hink


you=20have=20to=20start=20at=20the=20beginning.=20=20Treat=20it=20like=20a=
ny=20other


poorly=20performing=20business=20process.=20=20Set=20a=20SQL=20trace=20at=20=
level


8,=20and=20rn=20the=20stats.=20=20Analyze=20where=20time=20is=20being=20sp=
ent.




Finally,=20one=20more=20point=20regarding=20direct=20I/O.=20=20While=20it'=
s


safe=20to=20say=20that=20direct=20I/O=20is=20better=20than=20buffered=20I/=
O,


there=20is=20at=20least=20one=20case=20where=20that's=20not=20true.
(Thanks=20to=20Jonathan=20for=20this=20example.)=20=20
It's=20possible,=20if=20you=20have=20a=20process=20that=20does=20a=20full=20=
table


scan=20on=20a=20moderately=20large=20table.=20=20(Say,=20on=20the=20order=20=
of


1GB=20or=202=20GB.)=20=20=20Consider=20that=20the=20server=20you're=20on=20=
has


lots=20and=20lots=20of=20memory,=20resulting=20in=20the=20aforementioned
table=20being=20cached=20in=20the=20filesystem=20buffer=20cache.=20=20The
result=20is=20that=20all=20those=20'db=20file=20scattered=20read'=20events=



are=20really,=20really=20fast,=20cause=20they=20are=20all=20(almost=20all?=
)


being=20satisfied=20from=20buffer=20cache.=20=20Remember,=20buffers=20are
being=20aged=20out=20of=20the=20Oracle=20buffer=20cache=20quickly,=20cause=



it's=20a=20sufficiently=20large=20table,=20and=20the=20operation=20is=20a=20=
full


table=20scan.=20=20So,=20now=20you=20move=20to=20direct=20I/O.=20=20Well,=20=
the=20Oracle


buffer=20cache=20is=20behaving=20the=20same=20way,=20aggressively=20aging
the=20full=20scanned=20blocks=20out=20of=20the=20cache.=20=20But=20now,=20=
there


is=20no=20filesystem=20buffer=20cache.=20=20So,=20all=20those=20'db=20file=



scattered=20read'=20events=20are=20resulting=20in=20a=20real=20physical=20=
I/O.


So,=20the=20performance=20of=20the=20job=20suffers.=20=20Conclusion?
Direct=20I/O=20sucks!=20=20Of=20course,=20a=20better=20solution=20would=20=
be=20to


grow=20the=20buffer=20cache=20by=20the=20amount=20of=20memory=20saved=20by=


=20not



having=20the=20filesystem=20buffer=20cache,=20and=20perhaps=20use=20that
memory=20to=20allocate=20or=20grow=20the=20KEEP=20buffer=20pool,=20and=20p=
ut=20that


table=20there.=20=20Now,=20Oracle=20can=20satisfy=20the=20full=20scan=20wi=
thout


attempting=20a=20physical=20read.=20=20



Come=20to=20think=20of=20it,=20the=20stats=20process=20is=20probably=20doi=
ng


FTS=20behind=20the=20scenes.=20=20The=20situation=20outlined=20above=20cou=
ld


be=20what's=20happening=20to=20you.=20=20(Could=20be....not=20enough=20inf=
o


to=20draw=20any=20conclusions.)



Hope=20that=20helps,



-Mark



-----Original=20Message-----


From:=20oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On=20Behalf=20Of=20Roger=20Xu
Sent:=20Tuesday,=20October=2019,=202004=203:16=20PM
To:=20Oracle-L_at_Freelists.=20Org=20(E-mail)
Subject:=20Direct=20I/O,=20better=20performance?




Hi,


=20



We=20are=20running=20Solaris=209=20with=20UFS=20on=20Oracle=209.2.0.4.0.
We=20switched=20to=20direct=20I/O=20and=20did=20not=20see=20a=20better=20p=
erformance


as=20far=20as=20updating=20statistics=20concerned.=20Why?


=20



It=20used=20to=20take=20us=2022=20hours=20to=20update=20statistics=20for=20=
all=20tables,


but=20now=2031=20hours.


=20



Thanks,


Roger=20Xu=20


Database=20Administrator=20


Dr=20Pepper=20Bottling=20Company=20of=20Texas=20
(972)721-8337=20



=20



This=20e-mail=20is=20intended=20solely=20for=20the=20person=20or=20entity=20=
to=20which=20it=20is=20addressed=20and=20may=20contain=20confidential=20an=
d/or=20privileged=20information.=20Any=20review,=20dissemination,=20copyin=


g,=20printing=20or=20other=20use=20of=20this=20e-mail=20by=20persons=20or=20=
entities=20other=20than=20the=20addressee=20is=20prohibited.=20If=20you=20=
have=20received=20this=20e-mail=20in=20error,=20please=20contact=20the=20s=
ender=20immediately=20and=20delete=20the=20material.=20



This=20email=20has=20been=20scanned=20for=20all=20viruses=20by=20the=20Mes=
sageLabs=20Email=20Security=20System.=20Any=20questions=20please=20call=20=
972-721-8257=20or=20email=20your=20request=20to=20tech_support_at_dp7uptx.com=


.

--
http://www.freelists.org/webpage/oracle-l

For=20technical=20support=20please=20email=20tech_support_at_dp7uptx.com=20or=


=20you=20can

call=20(972)721-8257.=20
This=20email=20has=20been=20scanned=20for=20all=20viruses=20by=20the=20Mes=
sageLabs=20Email=20Security=20System.

This=20e-mail=20is=20intended=20solely=20for=20the=20person=20or=20entity=20=
to=20which=20it=20is=20addressed=20and=20may=20contain=20confidential=20an=
d/or=20privileged=20information.=20Any=20review,=20dissemination,=20copyin=
g,=20printing=20or=20other=20use=20of=20this=20e-mail=20by=20persons=20or=20=
entities=20other=20than=20the=20addressee=20is=20prohibited.=20If=20you=20=
have=20received=20this=20e-mail=20in=20error,=20please=20contact=20the=20s=
ender=20immediately=20and=20delete=20the=20material.=20
____________________________________________________________________
This=20email=20has=20been=20scanned=20for=20all=20viruses=20by=20the=20Mes=
sageLabs=20Email=20Security=20System.=20Any=20questions=20please=20call=20=
972-721-8257=20or=20email=20your=20request=20to=20tech_support_at_dp7uptx.com=
.
--
http://www.freelists.org/webpage/oracle-l


Received on Tue Oct 19 2004 - 14:54:35 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US