LastUpdate:July2002(Oracle8/8i/9iR1/9iR2,HP-UX11/11i)
Oracle9irecommendations:
MinimumRAM=256MB
HP-UX11orHP-UX11i
SwapSpace:Minimum2xRAMor400MB,whicheverisgreater
availableDiskSpace:3GB
CreateOSUNIXgroupsforOraclesystem:
OSDBAgroup:Required
OSOPERgroup:Optional
ORAINVENTORYgroup:Optional
APACHEgroup:Recommended
Pleasecheckappendix6forSCHED_NOAGEusageandMLOCK
www.changshady.com
Note:TheseareminimumkernelrequirementsforOracle9i.Ifyouhavepreviously
tunedyourkernelparameterstolevelsequaltoorhigherthanthesevalues(becarefulforfilesystemcacheparameters–dbc_max_pct,dbc_min_pct),continuetousethehighervalues.Asystemrestartisnecessaryforkernelchangestotakeeffect.
KernelParameterSettingPurpose
HP-UX11.x
KernelParameter Description,Default/RecommendedValue bufpages NumberofstaticBufferPagesof4kB,
enablesdynamicbuffercache(seedbc_max/min_pct).
default=0
recommended:0
www.tangxiaobei.com dbc_max_pct MaxdynamicBufferCachesizeinpercent
ofsystemmemory,whenbufpagesand
nbufarebothsetto0,
default=50.
recommended:between3and10
It''smoreefficienttousesystemmemory
forOracleSGAinsteadoffilesystem
buffercache
dbc_min_pct MindynamicBufferCachesizeinpercent
ofsystemmemory(seedbc_max_pct),
default=5.
recommended:between2and5
www.changshady.com fs_async seeappendix2andappendix6
KSI_ALLOC_MAX
(NPROC8)
Definesthesystemwidelimitofqueuedsignalthatcanbeallocated. max_async_ports Maximumnumberofasyncdskportsthatcanbeopenatonetime.Ifanerrorhappenswiththis,itwillnotshownintheOraclealert.logfileorintracefiles.
default:50
recommended:max.noofshadowprocesses+noofparallelquerieslaves
max_thread_proc Maxno.ofthreadsallowedineachprocess
default:64
recommended:
<=Oracle8.1.6=64
>=Oracle8.1.7=256
maxfiles SoftFileLimitperProcess,softlimitfornumberoffilesaprocessisallowedtohaveopensimultaneously.Processescanincreasetheirsoftlimituntiltheyreachthehardlimitmaxfiles_lim.
default=60.
recommended=1024
maxfiles_lim HardFileLimitperProcess,
default=1024.
recommended=1024(default).
maxdsiz Referstothemaximumdatasegmentsizefor32-bitsystems.Settingthisvaluetoolowmaycausetheprocessestorunoutofmemory.
MaxDataSegmentSize(Bytes)
default:67108864
recommended:
<=Oracle8.1.7=128MB
>=Oracle9i=1073741824bytes
maxdsiz_64bit Referstothemaximumdatasegmentsizefor64-bitsystems.Settingthisvaluetoolowmaycausetheprocessestorunoutofmemory.
MaxDataSegmentSize(Bytes)
default:1073741824
recommended:
<=Oracle8.1.7=1073741824bytes
>=Oracle9i=2147483648bytes
MAXSSIZ Definesthemaximumstacksegmentsizeinbytesfor32-bitsystems.
Recommended:
>=Oracle9i=134217728bytes
MAXSSIZ_64BIT Definesthemaximumstacksegmentsizeinbytesfor64-bitsystems.
Recommended:
>=Oracle9i=1073741824bytes
maxtsize MaxTextSegmentsize(Bytes)
Recommended:128MB
max_fcp_reqs MaximumNumberofConcurrentFiberChannelRequestsPerAdapter
recommended:512
maxswapchunks maximumnumberofswapchunkswhereSWCHUNKistheswapchunksize(1KBblocks).
Default:2048
recommended:
<=Oracle8i=4096>=Oracle9i=16384
maxusers ValueofMAXUSERSmacro,limitsthe
Ssstemresourceallocation(nottheactual
numberofusers).Influencesnproc,ninode,nfile.
default=32.
recommended:
settonumberofconcurrentOracleDBusers+64
maxuprc MaxNumberofsimultaneoususerprocesses(peruser-id!).
default=75
Becausealldatabaseprocessesoftenrun
withtheOracleuser-id,it''s
recommended:
<=Oracle8.1.7=maxusers5
>=Oracle9i=((NPROC9)/10)
Maxvgs Max.numberofvolumegroups:
Default:10
Recommended:increasetothenumberofvolumegroupsyouwouldliketohaveonthesystem(maximum256) MSGMAP Definesthemaximumnumberofmessagemapentries.
Recommended:
>=Oracle9i=(MSGTQL+2)
MSGMNI Definesthenumberofmessagequeue
identifiers.
Recommended:
>=Oracle9i=(NPROC)
MSGSEG Definesthenumberofsegmentsavailableformessages.
Recommended:
>=Oracle9i=(NPROC4)(atleast32767)
MSGTQL Definesthenumberofmessageheaders.
Recommended:
>=Oracle9i=(NPROC)
NCALLOUT Definesthemaximumnumberofpendingtimeouts.
Recommended:
>=Oracle9i=(NPROC+16) NCSIZE DefinestheDirectoryNameLookupCache(DNLC)spaceneededforinodes.
Recommended:
>=Oracle9i=((8NPROC+2048)+VX_NCSIZE)
VX_NCSIZEisbydefault1024. nfile MaxNumberofsimultaneouslyOpen
filessystem-wideatanygiventime.Total
numberofslotsitthefiledescriptortable,
default=16(nproc+16+maxusers)/10+32+2(npty+nstrpty)
recommended:
<=Oracle8.1.7=tousedefault.
>=Oracle9i=(15NPROC+2048)
nflocks MaxNumberofFileLocksavailable
system-wide,(checkwithglanceifyouaregettingclosetothevalueyouhaveset)
default=200or200+10(num_clients)
recommended:
<=Oracle8.1.7=atleast200+sumoffalldb_filesforallinstancesofOracle
>=Oracle9i=(nproc)(atleast4096) NINODE Definesthemaximumnumberofopeninodes.
Recommended:
>=Oracle9i=(8NPROC+2048)
NKTHREAD Definesthemaximumnumberofkernelthreadssupportedbythesystem.
Recommended:
>=Oracle9i=(((NPROC7)/4)+16)
nproc MaxNumberofProcessesthatcanexist
simultaneouslyinthesystem,
default=(20+8MAXUSERS),
influencesninode,nfile.
recommended:
<=Oracle8.1.7=tousedefault
>=Oracle9i=4096
npty Numberofptys(pseudottys),
default=60.
recommended:ifyouareusingrlogin/telnet
connectionsfromclientstoserverthen
increasenptyuptothenumberofclient
users,otherwiseusedefault.
Note:ptysarenotusedwhenusingOracleSQLNet
num_tachyon_adapters HP-UX11.0only
NumberofTachyon-basedFiberChannelAdaptersintheSystem
Recommended:minimum5,settorealnumberofFiberChannelAdapters
HP-UX11i–nosupportfortachyonadapters,thenewistachlite,whichdonotusethiskernelparameter.
o_sync_is_o_dsync Enable/DisabletranslationofO_SYNCto
O_DSYNCinopen()/fcntl()calls,
default=0.
recommended:
useOraclePatchforBug#310042.
>=Oracle7.3=usedefaultforo_sync_is_o_dsync=0(patch#310042notneeded).
SEMMAP Definesthemaximumnumberof
semaphoremapentries.
Recommended:
>=Oracle9i=(SEMMNI+2)
semmni NumberofSemaphoreIdentifiers,
specifiesthemaximumnumberofsetsof
semaphoresthatcanexistsimultaneously
onthesystem,
default=64.
recommended:
<=Oracle8i=usedefaultoratleast10
perOracledatabase
>=Oracle9i=4096
semmns MaxNumberofSemaphores,definesthe
system-widemaximumnumberof
individualsemaphoresthatcanbe
allocatedforusers,
default=128.
recommended:
<=Oracle8.1.7=256oratleast1per
Oracleprocess
>=Oracle9i=(semmni2)
SEMMNU Definesthenumberofsemaphoreundo
structures.
Recommended:
>=Oracle9i=(NPROC–4) SEMVMX Maximumvalueofasemaphore.
Recommended:
>=Oracle9i=32768 shmmax(seeappendix4) maximumallowablesizeofonesharedmemory
segment.TheSHMMAXsettingshouldbelarge
enoughtoholdtheentireSGAinonesharedmemorysegment.Alowsettingcancausecreationofmultiplesharedmemorysegmentswhichmayleadtoperformancedegradation,butnormallyitisoktouseasmallnumberofsharedmemorysegments.
default=0x04000000(64MB)Oracle7
0x40000000(1GB=1073741824Bytes)
Oracle8,8i
recommended:
db_block_sizedb_block_buffers
+shared_pool_size
+log_buffer
+sharedmemoryfortheapplication
or
holdtheentireSGAinonesharedmemorysegment!
(for64bituseuptosizeofphysicalmemory,
butOraclecanalsousemultiplesegments(pleasecheckappendix4)) Shmmni maximumnumberofsharedmemorysegmentsintheentiresystem.
default=200.
recommended:
<=Oracle8.1.7=usedefault.
>=Oracle9i=512 Shmseg maximumnumberofsharedmemorysegmentsoneprocesscanattach.
default=12.
recommended:
<=Oracle8.1.7=10perOracledatabase
>=Oracle9i=32perOracledatabase
Streampipes Setthisparameterto0duringontheinstallationofOracleDBwillreducetheinstallationtime.
Default:0
Recommendedforinstallation:0 swapmem_on
(seeappendix5) AllowMemorytoExceedSwapSpace
default:0(off)
recommended:1(on)
Swchunk SwapChunkSize(1KBlocks)
default:2048
recommended:4096
VPS_CEILING maximumSystem-SelectedPageSizeinkilobytes.
Recommended:
>=Oracle9i=64
HelpfulTools:
Tolistallkernelparametersonthesystem
useSAMor
/usr/sbim/kmtune–l|more
Toupdateakernelparameter
UseSAMor
/usr/sbin/kmtune–s{=|+}
Tofindout32or64bitHP-UX11.x
getconfKERNEL_BITS
Limitationsonprocesses
ulimit–a
Thecommandsysdefanalyzesthecurrentlyrunningsystemandreportsonitstunableconfigurationparameters.
sysdef|more
Tofindout32or64bitOraclesoftwareversion
$cd$ORACLE_HOME/bin
$fileoracle
#ifthisistheanswer,youhave32-bitOracle
oracle:PA-RISC1.1sharedexecutabledynamicallylinked-notstripped
#ifthisistheanswer,youhave64bitOracle
oracle:ELF-64executableobjectfile-PA-RISC2.0(LP64)
APPENDIX1:db_block_size
Thedefaultdb_block_sizeonHP-UXis2048.Butwerecommendtousethefollowingvalues:
-db_block_size=4096to8192forOLTPApplications,
-db_block_size=8192to16384forDSS/DWApplications.
APPENDIX2:RawDevices+AsynchronousIO
WerecommendtouserawdevicesinsteadoffilesystemfilesforOracle
datafilesandtouseasynchronousIO,bothresultinginmuchbetter
performance.
UsestrippingwithalargenumberofdiskstospreadIOovermultipledisks(highnumberisgood).Definethenumberofdisksinyourstripesetalsodependentofyourfuturegrowthofyourstorage,otherwiseyouwillgetprobablyhotspotsinthefuture.Forthestrippingare2possibilityexists.Forahighnumberofdiskswedidnotfiguredoutbigdifferenceinperformance.
UsetheHP-UXLogicalVolumeManagertostripelogicalvolumes(usedas
datafiles)acrosssingledisks.Recommendedstripesize:64KB
or
useHP-UXExtend-based-stripingwith1MBorevenlargerextends.
ToimplementasynchronousIOonHP-UXsystems(rawdevicesarerequired)
-addtheasyncdskDriver(AsynchronousDiskPseudoDriver)totheHP-UXKernel(usingSAM)andregeneratenewkernel
createthedevicefile:
- /sbin/mknod/dev/asyncc1010x0
- chownoracle:dba/dev/async
chmod660/dev/async
dependingontheHP-UXversionyouhavetoprovideprivilegetogroupdba
-HP-UX11.0(dependendonthepatchlevel)PatchPHHKL_22380?andallnewer?patcheswhichsupersedeit(likePHKL_22440)useaspecial"feature"whichhastobesettoreallyuseasyncio.Asroot:
/usr/sbin/setprivgrpdbaMLOCK
?
-HP-UX11i(>=11.11)
Touseasyncio:Asroot:
/usr/sbin/setprivgrpdbaMLOCK
setoracleinit.oraparameters:
use_async_io=true(Oracle7.3)
disk_async_io=true(Oracle8.0,8i)
db_writers=1(
TotesttheasyncIO
-checkwith fuser/dev/async
-orlook?withglance/gpmattheora_dbwr.
-lookforopenfiles:??
-/dev/async??mustbeshownasopenedfile
-if/dev/asyncisnotseenandyouwanttouseasyncio:
TocheckthepriviligestouseasyncIOonHP-UX11(withPatchPHHKL_22380?andallnewer?patcheswhichsupersedeitlikePHKL_22440)andHP-UX11i
-Tochecktheprivilegecapabilitiesforagroup,issuethecommand:?????????/usr/bin/getprivgrp-Iftheoutputofgetprivgrp(1)doesnotindicatethatthegrouphastheMLOCKprivilege,itcanbesetbyissuingthefollowingcommandasroot:/usr/bin/setprivgrp?dbaMLOCK
WhennotusingasynchronousIO
setdb_writerstonumberofdisk(forOracle7)
setdb_writer_processes=1–10(Oracle8.0,8i)
setdbwr_io_slaves=0–999(Oracle8.0,8i)(Pleasetestwithyourapplication,ifthebehaviourusingmultipledb_writer_processesisbetterthanusemultipledbwr_io_slaves)
Pleasecheckalsoappendix6forOracle9i(SCHED_NOAGE)
APPENDIX3:ParallelQueryOption
WhenusingOracleParallelQueryOption,thereareuptoparallel_max_serversmoreOracleProcessesrunningonthesystem.It''snecessarytoincreasetherelatedHP-UXParameters(maxusers,maxuprc,nfile,nproc)
APPENDIX4:shmmax
IfyougotanOracleerrorsayingORA-3113:end-of-fileoncommunicationchannel
thanyouhavetoadaptyourshmmaxtoausablevaluewhichmeans,
forOracle32-bitmax1GB
forOracle64-bitshmmaxcanberaisedabove1GB
Pleaseremind,thatyouhavenormallymorethanonesharedmemorysegments(shmmni(maxnumberofsharedmemorysegmentsallowedtoexistsimultaneously),shmseg(perprocess)aretherelatedkernelparameters),whichcanbeusedbyOracle.IfyouhavemultipleOracleinstancesononesystem,itisnormallybettertodecreasethesizeofsharedmemoryandusemultiplesharedmemorysegmentsinstead(whichisdoneautomatically).
Limitations:
Oracle7
thebuffercachepartcanbecomposedofmorethanonesegment,butthevariableportionoftheSGA(mainlytheshared_pool)mustfitinonesharedmemorysegment
Oracle8/Oracle9i
thebuffercachepartcanbecomposedofmorethanonesegment,thevariableportionoftheSGA(mainlytheshared_pool)canbecomposedofmorethanonesharedmemorysegment,assumingthatthesharedmemorysegmentsarecontiguous
Appendix5:Swapping/Paging
Howmuchmemorydoesthemaschinehave?
dmesg|grepPhysical
orgrepPhysical/var/adm/syslog/syslog.log
Whatisthepagesize?
dmesg|grepphysical
orgrepphysical/var/adm/syslog/syslog.log
Isthemaschineswappinginthemoment?
vmstat55
page.piindicatedthenumberofpagesmovingfromdisktomemory
page.poindicatedthenumberofpagesmovingfrommemorytodisk
memoryfreeindicatestheamountoffreememoryinpages
Howmuchswapspaceisconfigured/used?
swapinfo–m
AswapareaofTYPE=devgivestheNAMEoftheareaofdiskusedforpaging./etc/fstabwillshowNAMEasaswapdevice.
AswapareaofTYPE=reserveindicatestheamountofswapspacethatwouldberequiredifHP-UXneededtopageoutallin-memorypagesforallprocessesthatarecurrentlyrunning.
AswapareaofTYPE=memoryonlyappearsif''memorypaging''isenabled,bysettingtheconfigurablekernelparameterswapmem_onto1(on).Thisdefaultvalueforthisparameteris1.''memorypaging''isalsoknownas''pseudo-swap''.Ifpresent,theAVAILfigureis(totalmemory-memoryusedafterbootup).ThisswapareaisnotactuallyusedbyHP-UXforpaging(sinceitdoesnotmakesensetopagefrommemory,andthenbackintomemory).
Appendix6:PrivilegesforSCHED_NOAGE(forOracle9i)andMLOCK
Completerootuserset-uptasks:
GranttheOSDBAgroupRTSCHED,RTPRIOandMLOCKprivileges.
AnewHPschedulingpolicycalledSCHED_NOAGEenhancesOracle9i’sperformancebyschedulingOracleprocessessothattheydonotincreaseordecreaseinpriority,orbecomepreempted.
TheRTSCHEDandRTPRIOprivilegesgrantOracletheabilitytochangeitsprocessschedulingpolicytoSCHED_NOAGEandalsotellOraclewhatprioritylevelitshouldusewhensettingthepolicy.
TheMLOCKprivilegegrantsOracletheabilitytoexecuteasynchI/OsthroughtheHPasynchdriver.Withoutthisprivilege,Oracle9igeneratestracefileswiththefollowingerrormessage:Ioctl
ASYNCH_CONFIGerror,errno=1
Asroot,dothefollowing:
_Ifitdoesnotalreadyexist,createthe/etc/privgroupfile.Addthefollowinglinetothefile:
dbaMLOCKRTSCHEDRTPRIO
_Usethefollowingcommandsyntaxtoassigntheseprivileges:
#setprivgroupnameprivileges
Intheprecedingcommand,groupnameisthenameofthegroupthatreceivestheprivileges,andprivilegesaretheprivilegesthataregrantedtothegroup.
Forexample,tosettheprivilegesfortheOSDBAgroup,whichistypicallynameddba,enterthefollowingcommand:
#setprivgrpdbaMLOCKRTSCHEDRTPRIO
Ihavecheckedthesystemandfoundatleastthefollowingparametersneedtuning:
1.dbc_min/max_pctIfuserusesrawdevice,tuningthesetwovaluestoabout100MB
Ifuserusesfilesystem,tuningthisto20%RAM
2.max_thread_proc256
3.maxfiles1024
4.maxdsiz>512MB
5.maxtsiz128MB
6.maxssiz128MB
7.maxuser>=concurrentusernumber
8.maxuprc
9.nfile
10.nflock
11.nkthread
12.nproc>=4096
Theaboveparamtersshouldbetunedaccordingtousersystem''smemory.
AndattachedisthekernelparametertuningguidefororacleDB,Youcanreferthistotunethem.
HP-UXKernelConfigurationforOracleDBs
1
3
|
|
