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: Mon, 25 Oct 2004 17:32:43 -0500
Message-ID: <A6801E8A03316A4DA597866F77A013F7051836F7@irv2kexch01> 





First=20of=20all,=20we=20do=20not=20have=20performance=20issue.=20Secondar=
ily,


I=20did=20not=20gain=20anything=20after=20I=20switching=20to=20Direct=20I/=
O.


All=20I=20have=20is=20longer=20statistics=20gathering,=20longer=20datafile=



backup=20and=20some=20longer=20jobs.



Anybody=20in=20the=20list=20see=20benefits=20after=20switching=20to=20Dire=
ct=20I/O?



Thanks.



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


From:=20oracle-dba_at_comcast.net=20[mailto:oracle-dba_at_comcast.net]
Sent:=20Tuesday,=20October=2019,=202004=203:43=20PM
To:=20Roger=20Xu;=20Bobak,=20Mark;=20Oracle-L_at_Freelists.=20Org=20(E-mail)
Cc:=20Roger=20Xu


Subject:=20RE:=20Direct=20I/O,=20better=20performance?




Roger,



Why=20turn=20off=20=20"forcedirectio"=20totally=20?=20=20I=20think=20Mark=20=
was=20not=20trying=20to=20discourage=20you=20from=20using=20it=20.=20=20=20=

Solaris's=20=20Concurrent=20Direct=20IO=20is=20a=20wonderful=20feature=20t=
hat=20should=20improve=20the=20database=20performance=20overall=20=20,=20e=
xcepting=20those=20cases=20which=20Mark=20mentioned,which=20can=20be=20tak=


en=20care=20of.=20You=20will=20need=20to=20readjust=20Oracle's=20SGA=20siz=
e=20to=20make=20use=20of=20the=20released=20Solaris=20Page=20cache.=20=20



Direct=20I/O=20not=20only=20avoids=20the=20double=20buffering=20,but=20als=
o=20provides=20the=20following=20benefits=20:
-shorter=20code=20path=20for=20I/O=20as=20the=20filesystem=20cache=20is=20=
bypassed.

-=20less=20pressure=20on=20Solaris's=20VM=20system
-=20elimination=20of=20Solaris's=20single=20writer=20lock=20on=20files
-=20batching=20large=20writes=20





An=20excellent=20choice=20especially=20for=20redologfiles.=20



-Thiru






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

awesome.


>=20Lots=20of=20good=20ideas=20and=20deep=20thoughts.

>=20

>=20My=20expectation=20was=20to=20improve=20overall=20performance,=20not=20=

just


>=20

>=20statistics=20gathering.=20

>=20

>=20I=20think=20I=20am=20going=20to=20turn=20off=20"direct=20I/O",=20becau=

se=20I=20also


>=20

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




>=20

>=20Thanks=20again.

>=20

>=20

>=20

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

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

>=20Sent:=20Tuesday,=20October=2019,=202004=202:46=20PM

>=20To:=20Roger=20Xu;=20Oracle-L_at_Freelists.=20Org=20(E-mail)

>=20Subject:=20RE:=20Direct=20I/O,=20better=20performance?

>=20

>=20

>=20Roger,

>=20

>=20Why=20would=20you=20expect=20the=20statistics=20gathering=20process=20=

to=20improve


>=20performance?=20=20Did=20you=20identify=20some=20inefficiency=20in=20th=


e=20process




>=20which=20you=20determined=20would=20be=20addressed=20by=20switching=20t=


o=20direct=20I/O?




>=20

>=20In=20general,=20I=20think=20it's=20safe=20to=20say=20that=20direct=20I=

/O=20is=20better


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

nt


>=20performance=20increase=20with=20something=20like=20stats=20gathering.

>=20The=20idea=20with=20direct=20I/O=20is=20that=20the=20O/S=20does=20not=20=

attempt


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

memory


>=20and=20allows=20it=20to=20be=20allocated=20to=20the=20Oracle=20kernel=20=

(SGA),



>=20where=20it=20may=20(perhaps)=20be=20used=20to=20allocate=20a=20KEEP=20=

and/or


>=20RECYCLE=20buffer=20pool,=20allowing=20Oracle=20to=20manage=20the=20buf=

fering


>=20of=20datafiles=20directly,=20rather=20than=20allowing=20the=20O/S

>=20to=20attempt=20to=20do=20so.=20=20The=20idea=20is=20that=20the=20Oracl=

e


>=20kernel=20knows=20more=20about=20how=20data=20in=20the=20datafiles

>=20is=20used=20than=20the=20O/S,=20and=20therefore=20should=20be=20better=




>=20at=20managing=20memory=20dedicated=20to=20buffering=20datafile

>=20contents.

>=20

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

=20think


>=20you=20have=20to=20start=20at=20the=20beginning.=20=20Treat=20it=20like=

=20any=20other


>=20poorly=20performing=20business=20process.=20=20Set=20a=20SQL=20trace=20=

at=20level


>=208,=20and=20rn=20the=20stats.=20=20Analyze=20where=20time=20is=20being=20=

spent.


>=20

>=20

>=20Finally,=20one=20more=20point=20regarding=20direct=20I/O.=20=20While=20=

it's


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

I/O,


>=20there=20is=20at=20least=20one=20case=20where=20that's=20not=20true.

>=20(Thanks=20to=20Jonathan=20for=20this=20example.)=20

>=20It's=20possible,=20if=20you=20have=20a=20process=20that=20does=20a=20f=

ull=20table


>=20scan=20on=20a=20moderately=20large=20table.=20=20(Say,=20on=20the=20or=

der=20of


>=201GB=20or=202=20GB.)=20=20=20Consider=20that=20the=20server=20you're=20=

on=20has


>=20lots=20and=20lots=20of=20memory,=20resulting=20in=20the=20aforemention=

ed


>=20table=20being=20cached=20in=20the=20filesystem=20buffer=20cache.=20=20=

The


>=20result=20is=20that=20all=20those=20'db=20file=20scattered=20read'=20ev=

ents


>=20are=20really,=20really=20fast,=20cause=20they=20are=20all=20(almost=20=

all?)


>=20being=20satisfied=20from=20buffer=20cache.=20=20Remember,=20buffers=20=

are


>=20being=20aged=20out=20of=20the=20Oracle=20buffer=20cache=20quickly,=20c=

ause


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

a=20full


>=20table=20scan.=20=20So,=20now=20you=20move=20to=20direct=20I/O.=20=20We=

ll,=20the=20Oracle


>=20buffer=20cache=20is=20behaving=20the=20same=20way,=20aggressively=20ag=

ing


>=20the=20full=20scanned=20blocks=20out=20of=20the=20cache.=20=20But=20now=

,=20there


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

file


>=20scattered=20read'=20events=20are=20resulting=20in=20a=20real=20physica=


l=20I/O.




>=20So,=20the=20performance=20of=20the=20job=20suffers.=20=20Conclusion?

>=20Direct=20I/O=20sucks!=20=20Of=20course,=20a=20better=20solution=20woul=

d=20be=20to


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

by=20not


>=20having=20the=20filesystem=20buffer=20cache,=20and=20perhaps=20use=20th=

at


>=20memory=20to=20allocate=20or=20grow=20the=20KEEP=20buffer=20pool,=20and=

=20put=20that


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

without


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

>=20

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

doing


>=20FTS=20behind=20the=20scenes.=20=20The=20situation=20outlined=20above=20=

could


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

info


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

>=20

>=20Hope=20that=20helps,

>=20

>=20-Mark

>=20

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

>=20From:=20oracle-l-bounce_at_freelists.org

>=20[=20mailto:oracle-l-bounce_at_freelists.org]On=20Behalf=20Of=20Roger=20Xu=




>=20Sent:=20Tuesday,=20October=2019,=202004=203:16=20PM

>=20To:=20Oracle-L_at_Freelists.=20Org=20(E-mail)

>=20Subject:=20Direct=20I/O,=20better=20performance?

>=20

>=20

>=20Hi,

>=20

>=20We=20are=20running=20Solaris=209=20with=20UFS=20on=20Oracle=209.2.0.4.=

0.


>=20We=20switched=20to=20direct=20I/O=20and=20did=20not=20see=20a=20better=

=20performance


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

>=20

>=20It=20used=20to=20take=20us=2022=20hours=20to=20update=20statistics=20f=

or=20all=20tables,


>=20but=20now=2031=20hours.

>=20

>=20Thanks,

>=20Roger=20Xu

>=20Database=20Administrator

>=20Dr=20Pepper=20Bottling=20Company=20of=20Texas

>=20(972)721-8337

>=20

>=20

>=20

>=20This=20e-mail=20is=20intended=20solely=20for=20the=20person=20or=20ent=

ity=20to=20which=20it=20is=20addressed=20


>=20and=20may=20contain=20confidential=20and/or=20privileged=20information=

.=20Any=20review,=20


>=20dissemination,=20copying,=20printing=20or=20other=20use=20of=20this=20=

e-mail=20by=20persons=20or=20


>=20entities=20other=20than=20the=20addressee=20is=20prohibited.=20If=20yo=

u=20have=20received=20this=20


>=20e-mail=20in=20error,=20please=20contact=20the=20sender=20immediately=20=

and=20delete=20the=20material.


>=20____________________________________________________________________

>=20This=20email=20has=20been=20scanned=20for=20all=20viruses=20by=20the=20=

MessageLabs=20Email=20Security=20


>=20System.=20Any=20questions=20please=20call=20972-721-8257=20or=20email=20=

your=20request=20to=20


>=20tech_support_at_dp7uptx.com.

>=20--

>=20http://www.freelists.org/webpage/oracle-l

>=20

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


or=20you=20can




>=20call=20(972)721-8257.

>=20This=20email=20has=20been=20scanned=20for=20all=20viruses=20by=20the=20=

MessageLabs=20Email=20Security=20


>=20System.

>=20

>=20

>=20

>=20This=20e-mail=20is=20intended=20solely=20for=20the=20person=20or=20ent=

ity=20to=20which=20it=20is=20addressed=20


>=20and=20may=20contain=20confidential=20and/or=20privileged=20information=

.=20Any=20review,=20


>=20dissemination,=20copying,=20printing=20or=20other=20use=20of=20this=20=

e-mail=20by=20persons=20or=20


>=20entities=20other=20than=20the=20addressee=20is=20prohibited.=20If=20yo=

u=20have=20received=20this=20


>=20e-mail=20in=20error,=20please=20contact=20the=20sender=20immediately=20=

and=20delete=20the=20material.=20


>=20____________________________________________________________________

>=20This=20email=20has=20been=20scanned=20for=20all=20viruses=20by=20the=20=

MessageLabs=20Email=20Security=20


>=20System.=20Any=20questions=20please=20call=20972-721-8257=20or=20email=20=

your=20request=20to=20


>=20tech_support_at_dp7uptx.com.

>=20--

>=20http://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 Mon Oct 25 2004 - 17:27:55 CDT












Original text of this message













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