RE: Direct I/O, better performance?

  • From: "Roger Xu" <roger_xu@xxxxxxxxxxx>
  • To: <oracle-dba@xxxxxxxxxxx>, "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 25 Oct 2004 17:32:43 -0500

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@xxxxxxxxxxx=20[mailto:oracle-dba@xxxxxxxxxxx]
Sent:=20Tuesday,=20October=2019,=202004=203:43=20PM
To:=20Roger=20Xu;=20Bobak,=20Mark;=20Oracle-L@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@xxxxxxxxxxxxxxx]
>=20Sent:=20Tuesday,=20October=2019,=202004=202:46=20PM
>=20To:=20Roger=20Xu;=20Oracle-L@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@xxxxxxxxxxxxx
>=20[=20mailto:oracle-l-bounce@xxxxxxxxxxxxx]On=20Behalf=20Of=20Roger=20Xu=

>=20Sent:=20Tuesday,=20October=2019,=202004=203:16=20PM
>=20To:=20Oracle-L@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@xxxxxxxxxxxx
>=20--
>=20//www.freelists.org/webpage/oracle-l
>=20
>=20For=20technical=20support=20please=20email=20tech_support@xxxxxxxxxxx=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@xxxxxxxxxxxx
>=20--
>=20//www.freelists.org/webpage/oracle-l

For=20technical=20support=20please=20email=20tech_support@xxxxxxxxxxx=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@xxxxxxxxxxx=
.
--
//www.freelists.org/webpage/oracle-l

Other related posts: