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