Oracle 9i 数据库性能调优技术-les02

Copyright©OracleCorporation,2002.Allrightsreserved.DiagnosticandTuningTools2-2Copyright©OracleCorporation,2002.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Identifykeytuningcomponentsofthealertlogfile•Identifykeytuningcomponentsofbackgroundtracefiles•Identifykeytuningcomponentsofusertracefiles•CollectstatisticswithOracleEnterpriseManager•DescribehowStatspackcollectsstatistics•CollectstatisticswithStatspack•Identifydynamicperformanceviewsusefulintuning•Describeothertoolsusedfortuning2-3Copyright©OracleCorporation,2002.Allrightsreserved.MaintenanceoftheAlertLogFile•Thealertlogfileconsistsofachronologicallogofmessagesanderrors.•Checkthealertlogfileregularlyto:–Detectinternalerrors(ORA-600)andblockcorruptionerrors–Monitordatabaseoperations–Viewthenondefaultinitializationparameters•Removeortrimthefileregularlyafterchecking.2-4Copyright©OracleCorporation,2002.Allrightsreserved.TuningComponentsoftheAlertLogFileThealertlogfilecontainsthefollowinginformationwhichcanbeusedintuningthedatabase:•Checkpointstartandendtimes•Incompletecheckpoints•Timetoperformarchiving•Instancerecoverystartandcompletetimes•Deadlockandtimeouterrors2-5Copyright©OracleCorporation,2002.Allrightsreserved.BackgroundProcessesTraceFiles•TheOracleserverdumpsinformationabouterrorsdetectedbyanybackgroundprocessintotracefiles.•OracleSupportusesthesetracefilestodiagnoseandtroubleshoot.•Thesefilesdonotusuallycontaintuninginformation.2-6Copyright©OracleCorporation,2002.Allrightsreserved.UserTraceFiles•Serverprocesstracingcanbeenabledordisabledatthesessionorinstancelevel.•AusertracefilecontainsstatisticsfortracedSQLstatementsinthatsession.•Usertracefilesarecreatedonaperserverprocessbasis.•Usertracefilescanalsobecreatedby:–Backupcontrolfiletotrace–DatabaseSETEVENTs2-7Copyright©OracleCorporation,2002.Allrightsreserved.Views,Utilities,andToolsToolsandviewsthatareavailabletotheDBAfordeterminingperformance:•OracleEnterpriseManager•Diagnosticsandtuningpacks•Statspack•v$xxxdynamictroubleshootingandperformanceviews•dba_xxxdictionaryviews•Oraclewaitevents•utlbstat.sqlandutlestat.sqlscripts2-9Copyright©OracleCorporation,2002.Allrightsreserved.OracleEnterpriseManagerConsole2-10Copyright©OracleCorporation,2002.Allrightsreserved.PerformanceManager2-12Copyright©OracleCorporation,2002.Allrightsreserved.OverviewofOracleExpertTuningMethodologySpecifytuningscopeCollectdataViewandeditdataandrulesAnalyzedataReviewrecommendationsImplementrecommendations2-13Copyright©OracleCorporation,2002.Allrightsreserved.TuningUsingOracleExpert2-14Copyright©OracleCorporation,2002.Allrightsreserved.TuningUsingOracleExpert2-15Copyright©OracleCorporation,2002.Allrightsreserved.Statspack•InstallationofStatspackusingthespcreate.sqlscript•Collectionofstatisticsexecutestatspack.snap•Automaticcollectionofstatisticsusingthespauto.sqlscript•Produceareportusingthespreport.sqlscript•Tocollecttiminginformation,setTIMED_STATISTICS=True2-17Copyright©OracleCorporation,2002.Allrightsreserved.StatspackOutputInformationfoundonthefirstpage:•Databaseandinstancename•Timeatwhichthesnapshotsweretaken•Currentsizesofthecaches•Loadprofile•Efficiencypercentagesoftheinstance•Topfivewaitevents2-18Copyright©OracleCorporation,2002.Allrightsreserved.StatspackOutputInformationfoundintheremainderofthedocument:•Completelistofwaitevents•InformationonSQLstatementscurrentlyinthepool•Instanceactivitystatistics•TablespaceandfileI/O•Bufferpoolstatistics2-19Copyright©OracleCorporation,2002.Allrightsreserved.StatspackOutputInformationfoundintheremainderofthedocument:•Rollbackorundosegmentstatistics•Latchactivity•Dictionarycachestatistics•Librarycachestatistics•SGAstatistics•Startupvaluesforinitializationparameters2-20Copyright©OracleCorporation,2002.Allrightsreserved.DictionaryandSpecialViewsThefollowingdictionaryandspecialviewsprovideusefulstatisticsafterusingthedbms_statspackage:•dba_tables,dba_tab_columns•dba_clusters•dba_indexes,index_stats•index_histogram,dba_tab_histogramsThisstatisticalinformationisstaticuntilyoureexecutedbms_stats.2-21Copyright©OracleCorporation,2002.Allrightsreserved.DisplayingSystemwideStatisticsv$sysstat•statistic#•name•class•valuev$sgastat•pool•name•bytesv$event_name•eventnumber•name•parameter1•parameter2•parameter3v$system_event•event•total_waits•total_timeouts•time_waited•average_wait2-24Copyright©OracleCorporation,2002.Allrightsreserved.DisplayingSession-RelatedStatisticsv$statname•statistic#•name•classv$sesstat•sid•statistic#•valuev$session_wait•sid•seq#•event•p1/2/3•p1/2/3text•p1/2/3raw•waittime•seconds_in_wait•statev$session•sid•serial#•username•osuserv$session_event•sid•event•total_waits•total_timeouts•time_waited•average_wait•max_waitv$event_name•event#•name•parameter1•parameter2•parameter32-26Copyright©OracleCorporation,2002.Allrightsreserved.OracleWaitEvents•Acollectionofwaiteventsprovidesinformationonthesessionsthathadtowaitormustwaitfordifferentreasons.•Theseeventsarelistedinthev$event_nameview,whichhasthefollowingcolumns:–EVENT#–NAME–PARAMETER1–PARAME