ORACLE9i 性能调优.docx

ORACLE9i 性能调优.docx

《ORACLE9i 性能调优.docx》由会员分享，可在线阅读，更多相关《ORACLE9i 性能调优.docx（37页珍藏版）》请在冰点文库上搜索。

ORACLE9i性能调优

性能调优

Chapter01OverviewofOracke9iDatabasePerformanceTuning

Objectives

Aftercompletingthislesson,youshouldbeabletodothefollowing:

Definetherolesassociatedwiththedatabasetuningprocess

Describethedependenciesbetweentuningindifferentdevelopmentphases

Describeservicelevelagreements

Identifytuninggoals

Identifycommontuningproblems

Employtuningactivitiesduringdevelopmentandproduction

Balanceperformanceandsafetytrade-offs

ReferenceBooks

TuningQuestions

Whotunes?

-Applicationdesigners

-Applicationdevelopers

-Databaseadministrators

-Systemadministrators

Whattotune?

Howmuchtuningisrequired?

WhatDoestheDBATune?

Application:

SQLstatementperformanceSharedwithdevelopers

Changemanagement

Instancetuning;

Memory

Databasestructure

Instanceconfiguration

TuningPhases

Applicationdesignandprogramming

Databaseconfiguration

Addinganewapplicationtoanexistingdatabase

Troubleshootingandtuning

TuningGoals

Minimizingresponsetime

Increasingthroughput

Increasingloadcapabilities

Decreasingrecoverytime

（SLA）

CommPerformanceProblems

Badsessionmanagement:

Limitsscalabilitytoapointsthatcannotbeexceeded

Makesthesystemoneortwoordersofmagnitudeslowerthanitshouldbe

Badcursormanagement

Badrelationaldesign:

Unnecessarytablejoinsperformed

Usuallyaresultoftryingtobuildanobjectinterfacetorelationalstorage

TuningStepsIndevelopment

Tunethedesign

Tunetheapplication

Tunememory

TuneI/O

Tunecontention

Tunetheoperatingsystem

BaselineofStatistics

Abaselinesetofstatisticsisusedto:

Provideasetofstatisticsthatarecollectedwhenthesystemwasoperatingwithintheboundsset

Createahypothesisaboutwhathaschangeonthesystem

StepsforaProductionDB

1.Definetheproblem

2.ExaminethehostsystemandOraclestatistics

3.Considersomecommonperformanceerrors

4.Buildaconceptualmodel

5.Implementandmeasurethechange

6.Checkthatthebottleneckhasbeenresolved

TuningMethodology

Checkalertlogandtracefilesforerrors.

Checktheparameterfileforanydiagnosticofinappropriateparametersetting.

Checkmemory,I/O,andCPUusage.Identifyprocesseswithresourceusageanomalies.

IdentifyandtuneSQLstatementsthatareheavyconsumersofCPUofI/O.

Tuningresponsetime:

Analyzesystemperformanceintermsofworkdone（CPUorservicetime）versustimespentwaitingforwork（waittime）

Determinewhichcomponentconsumesthegreatestamountoftime

Drilldowntotunethatcomponent,ifappropriate.

Trade-OffsofP&S

Factorsthataffectperformance:

Multiplecontrolfiles

Multipleredologmembersinagroup

Frequentcheckpointing

Performingarchiving

Blockchecknumbers

Numberofconcurrentusersandtransactions

Summary

Inthislesson,youshouldhavelearndhowto:

Createagoodinitialdesign

Defineatuningmethodology

Performproductiontuning

Establishquantifiablegoals

Listtuningproblems

Decidebetweenperformanceandsafety

Chapter02DiagnosticandTuningTools

Objectives

Aftercompletingthislesson,youshouldbeabletodothefollowing:

Identifykeytuningcomponentsofthealertlogfile

Identifykeytuningcomponentsofbackgroundtracefiles

Identifykeytuningcomponentsofusertracefiles

CollectstatisticswithOracleEnterpriseManager

DescribehowStatspackcollectsstatistics

CollectstatisticswithStatspack

Identifydynamicperformanceviewsusefulintuning

Describeothertoolsusedfortuning

MaintenanceofAlertlog

Thealertlogfileconsistsofachronologicallogofmessagesanderrors.

Checkthealterlogfileregularlyto:

Detectinternalerrors（ORA-600）andblockcorruptionerrors

Monitordatabaseoperations

Viewthenodefaltinitializationparameters

Removerortrimthefileregularlyafterchecking

TuningComponents

Thealertlogfilecontainsthefollowinginformationwhichcanbeusedintuningdatabase:

Checkpointstartandendtimes

Imcompletecheckpoints

Timetoperformarchiving

Instancerecoverystartandcompletetimes

Deadlockandtimeouterrors

BPTraceFiles

TheOracleserverdumpsinformationabouterrorsdetectedbyanybackgroundprocessintotracefiles.

OracleSupportusesthesetracefilestodiagnoseandtroubleshoot.

Thesefilesdonotusuallycontaintuninginformation.

UserTraceFiles

Serverprocesstracingcanbeenabledordisabledatthesessionorinstancelevel.

AusertracefilecontainsstatisticsfortracedSQLstatementsinthatsession.

Usertracefilesarecreatdonaperserserverprocessbasis.

Usertracefilescanalsobecreateby:

-Backupcontrolfiletotrace

-DatabaseSETEVENTs

Views,Utilities,andTools

ToolsandviewsthatareavailabletotheDBAfordeterminingperformance:

OracleEnterpriseManager

Diagnosticsandtuningpacks

Statspack

V$xxxdynamictroubleshootingandperformanceviews

Dba_xxxdictionaryviews

Oraclewaitevents

Utlbstat.sqlandutlestat.sqlscripts

Statspack

InstallationofStatspackusingthespcreate.sqlscript

Collectionofstatisticsexecutestatspack.snap

Automaticcollectionofstatisticsusingthespauto.sqlscript

Produceareportusingthespreport.sqlscript

TocollecttiminginformationsetTIMED_STATISTICS=True

StatspackOutput

Informationfoundintheremainderofthedocument:

Completelistofwaitevents

InformationonSQLstatementscurrentlyinthepool

Instanceactivitystatistics

TablespaceandfileI/O

Bufferpoolstatistics

Rollbackorundosegmentstatistics

Latchactivity

Dictionarycachestatistics

Librarycachestatistics

SystemGlobalArea（SGA）statistics

Startupvaluesforinitializationparameters

DDandSpecialViews

Thefollowingdictionaryandspecialviewsprovideusefulstatisticsafterusingthedbms_statspackage:

Dba_tables,dba_tab_columns

Dba_clusters

Dba_indexes,index_stats

Index_histogram,dba_tab_histograms

Thisstatisticalinformationisstaticuntilyoureexecutedbms_stats.

SystemwideStatistics

Session-RelatedStatistics

OracleWaitEvents

Acollectionofwaiteventsprovidesinformationonthesessionsthathadtowaitormustwaitfordifferentreasons.

Theseeventsarelistedinthev$event_nameview,whichhasthefollowingcolumns

-EVENT#

-NAME

-PARAMETER1

-PARAMETER2

-PARAMETER3

V$event_name

StatisticsEventViews

V$session_event:

Waitsforaneventforeachsessionthathadtowait

V$session_wait:

Waitsforaneventforcurrentactivesessionthatarewaiting

V$system_event:

Totalwaitsforanevent,allsessionstogether

V$session_eventView

V$session_waitView

V$system_eventView

SpecialViews

V$views:

BasedonX$tables

Listedinv$fixed_table

X$tables:

Notusuallyquerieddirectly

Dynamicandconstantlychanging

Namesabbreviatedandobscure

Populatedatstartupandclearedatshutdown

TSandtuningviews

DBA-DevelopedTools

Developyourownscripts.

Usethesuppliedpackagesfortuning.

Scheduleperiodicperformancechecking.

TakeadvantageoftheEnterpriseManagerEventservicetotrackoftrackspecificsituations.

TakeadvantageoftheOracleEnterpriseManagerJobserviceto:

Automatetheregularexecutionofadministrativetasks.

ApplytasksthatautomaticallysolveproblemsdetectedbytheOracleEnterpriseManagereventservice.

LevelofStaticsCollection

Theinitializationparametersthatdeterminethelevelofstatisticcollectionare:

STATISTICS_LEVEL

TIMED_STATISTICS

TIMED_OS_STATISTICS

DB_CACHE_ADVICE

Summary

Inthislesson,youshouldhavelearnedhowto:

Usethealertlogfile

Getinformationfrombackgroupprocessestracefiles

TraceuserSQLstatements

Collectstatisticsfromdictionaryanddynamicperformancetroubleshootingviews

UsetheStatspackutilitytocollectperformancedata

Retrivevwaiteventsinformation

Chapter03DatabaseConfigurationandI/OIssues

Objectives

Aftercompletingthislesson,youshouldbeabletodothefollowing:

ListtheadvantagesofdistributingdifferentOraclefiletypes

Diagnosetablespaceusageproblems

Listreasonsforpartitioningdataintablespace

Describehowcheckpointswork

Monitorandtunechechpoints

Monitorandtuneredologs

OracleDatabase11Gincludesthreestandardstorageoptions:

Filesystem

Networkattachedstorage（NAS）

Storageareanetwork（SAN）

Directattachedstorage

Rawpartitions

AutomaticStorageManagement（ASM）

OracleProcessesandFiles

PerformanceGuidelines

Basicperformancerulesareasfollow:

KeepdiskI/Otoaminimum.

Spreadyourdiskloadacrossdiskdevicesandcontrollers.

Usetemporarytablespacewhereappropriate.

DistributingFiles

Separatedatafilesandredologfiles.

Stripetabledata

ReducediskI/Ounrelatedtothedatabase.

TablespaceUsage

Reservethesystemtablespacefordatadictionaryobjects.

Createlocallymanagedtablespacestoavoidspacemanagementissues.

Splittablesandindexesintoseparatetablespaces.

Createrollbacksegmentsintheirowntablespace.

Storeverylargeobjectsintheirowntablespace.

Createoneormoretemporarytablespace.

LocallyManagedsysTs

Creaedatabasesthathavealocallymanagedsystemtablespace.

ToolsforI/OStatistics

Usingv$filestatView

I/OStatistics

selectd.tablespace_nametablespace,d.file_name,f.phyrds,f.phywrts

fromv$filestatf,dba_data_filesd

wheref.file#=d.file_id

FileStriping

Operatingsystemstriping:

-Useoperatingsystemstripingsoftwareoraredundantarrayofinexpensivedisks（RAID）.

-Determinetherightstripesize.

Manualstriping:

UsetheCREATETABLEorALTERTABLEcommandwiththeALLOCATE