本篇内容介绍了“oracle数据库CPU过高问题定位分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一、ASH

颗粒度最细最常用的诊断工具，通常当问题刚发生时，即可立即根据生成的ash报告诊断问题

下文示例为当问题出现后，查看最近5分钟的性能报告，运行ashrpt脚本后，其他选项直接默认跳过，在Enter value for begin_time选项中填写"-5"

SQL>@ashrptCurrentInstance~~~~~~~~~~~~~~~~DBIdDBNameInstNumInstance-------------------------------------------1506959389ORCL1orclSpecifytheReportType~~~~~~~~~~~~~~~~~~~~~~~Enter'html'foranHTMLreport,or'text'forplaintextDefaultsto'html'Entervalueforreport_type:TypeSpecified:htmlInstancesinthisWorkloadRepositoryschema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DBIdInstNumDBNameInstanceHost--------------------------------------------------------*15069593891ORCLorcllocalhost.localdomainDefaultstocurrentdatabaseUsingdatabaseid:1506959389Enterinstancenumbers.Enter'ALL'forallinstancesinaRACclusterorexplicitlyspecifylistofinstances(e.g.,1,2,3).Defaultstocurrentinstance.Usinginstancenumber(s):1ASHSamplesinthisWorkloadRepositoryschema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~OldestASHsampleavailable:19-Jun-1819:41:38[52092minsinthepast]LatestASHsampleavailable:25-Jul-1823:52:52[1minsinthepast]SpecifythetimeframetogeneratetheASHreport~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enterbegintimeforreport:--Validinputformats:--Tospecifyabsolutebegintime:--[MM/DD[/YY]]HH24:MI[:SS]--Examples:02/23/0314:30:15--02/2314:30:15--14:30:15--14:30--Tospecifyrelativebegintime:(startwith'-'sign)---[HH24:]MI--Examples:-1:15(SYSDATE-1Hr15Mins)---25(SYSDATE-25Mins)Defaultsto-15minsEntervalueforbegin_time:-5Reportbegintimespecified:-5Enterdurationinminutesstartingfrombegintime:DefaultstoSYSDATE-begin_timePressEntertoanalyzetillcurrenttimeEntervalueforduration:Reportdurationspecified:Using25-Jul-1823:48:39asreportbegintimeUsing25-Jul-1823:53:41asreportendtimeSpecifySlotWidth(usingashrpti.sql)for'ActivityOverTime'section~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~--Explanation:--Inthe'ActivityOverTime'sectionoftheASHreport,--theanalysisperiodisdividedintosmallerslots--andtopwaiteventsarereportedineachofthoseslots.--Default:--Theanalysisperiodwillbeautomaticallysplitupto10slots--complyingtoaminimumslotwidthof--1minute,ifthesourceisV$ACTIVE_SESSION_HISTORYor--5minutes,ifthesourceisDBA_HIST_ACTIVE_SESS_HISTORY.SpecifySlotWidthinsecondstouseinthe'ActivityOverTime'section:Defaultstoavalueasexplainedabove:SlotWidthspecified:SpecifyReportTargets(usingashrpti.sql)togeneratetheASHreport~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~--Explanation:--ASHReportcanaccept"ReportTargets",--likeaparticularSQLstatement,oraparticularSESSION,--togeneratethereporton.Ifoneormorereporttargetsare--specified,thenthedatausedtogeneratethereportwillonlybe--theASHsamplesthatpertaintoALLthespecifiedreporttargets.--Default:--Ifnoneofthereporttargetsarespecified,--thenthetargetdefaultstoallactivityinthedatabaseinstance.SpecifySESSION_ID(eg:fromV$SESSION.SID)reporttarget:DefaultstoNULL:SESSIONreporttargetspecified:SpecifySQL_ID(eg:fromV$SQL.SQL_ID)reporttarget:DefaultstoNULL:(%and_wildcardsallowed)SQLreporttargetspecified:SpecifyWAIT_CLASSname(eg:fromV$EVENT_NAME.WAIT_CLASS)reporttarget:[Enter'CPU'toinvestigateCPUusage]DefaultstoNULL:(%and_wildcardsallowed)WAIT_CLASSreporttargetspecified:SpecifySERVICE_HASH(eg:fromV$ACTIVE_SERVICES.NAME_HASH)reporttarget:DefaultstoNULL:SERVICEreporttargetspecified:SpecifyMODULEname(eg:fromV$SESSION.MODULE)reporttarget:DefaultstoNULL:(%and_wildcardsallowed)MODULEreporttargetspecified:SpecifyACTIONname(eg:fromV$SESSION.ACTION)reporttarget:DefaultstoNULL:(%and_wildcardsallowed)ACTIONreporttargetspecified:SpecifyCLIENT_ID(eg:fromV$SESSION.CLIENT_IDENTIFIER)reporttarget:DefaultstoNULL:(%and_wildcardsallowed)CLIENT_IDreporttargetspecified:SpecifyPLSQL_ENTRYname(eg:"SYS.DBMS_LOB.*")reporttarget:DefaultstoNULL:(%and_wildcardsallowed)PLSQL_ENTRYreporttargetspecified:SpecifytheReportName~~~~~~~~~~~~~~~~~~~~~~~Thedefaultreportfilenameisashrpt_1_0725_2353.html.Tousethisname,press<return>tocontinue,otherwiseenteranalternative.Entervalueforreport_name:/home/oracle/ash_test.htmlUsingthereportname/home/oracle/ash_test.htmlSummaryofAllUserInput-------------------------Format:HTMLDBId:1506959389Instnum:1Begintime:25-Jul-1823:48:39Endtime:25-Jul-1823:53:41Slotwidth:DefaultReporttargets:0Reportname:/home/oracle/ash_test.html

通过结果图可以观察到短时间内引发问题的sql：

二、AWR报告

由oracle的两份快照而产生的差异报告，通常一小时一次，问题情况下可通过手动生成，缩小分析问题时段的范围

一般可在问题出现时以及几分钟后，各运行exec dbms_workload_repository.create_snapshot一次

SQL>execdbms_workload_repository.create_snapshot;PL/SQLproceduresuccessfullycompleted.SQL>execdbms_workload_repository.create_snapshot;PL/SQLproceduresuccessfullycompleted.SQL>@awrrptCurrentInstance~~~~~~~~~~~~~~~~DBIdDBNameInstNumInstance-------------------------------------------1506959389ORCL1orclSpecifytheReportType~~~~~~~~~~~~~~~~~~~~~~~WouldyoulikeanHTMLreport,oraplaintextreport?Enter'html'foranHTMLreport,or'text'forplaintextDefaultsto'html'Entervalueforreport_type:TypeSpecified:htmlInstancesinthisWorkloadRepositoryschema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DBIdInstNumDBNameInstanceHost--------------------------------------------------------*15069593891ORCLorcllocalhost.localdomainUsing1506959389fordatabaseIdUsing1forinstancenumberSpecifythenumberofdaysofsnapshotstochoosefrom~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enteringthenumberofdays(n)willresultinthemostrecent(n)daysofsnapshotsbeinglisted.Pressing<return>withoutspecifyinganumberlistsallcompletedsnapshots.Entervaluefornum_days:ListingallCompletedSnapshotsSnapInstanceDBNameSnapIdSnapStartedLevel--------------------------------------------------------orclORCL2125Jul201822:3012225Jul201823:4212325Jul201823:4512425Jul201823:4712525Jul201823:4912625Jul201823:5112726Jul201800:0412826Jul201800:0712926Jul201800:0813026Jul201800:091SpecifytheBeginandEndSnapshotIds~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Entervalueforbegin_snap:29BeginSnapshotIdspecified:29Entervalueforend_snap:30EndSnapshotIdspecified:30SpecifytheReportName~~~~~~~~~~~~~~~~~~~~~~~Thedefaultreportfilenameisawrrpt_1_29_30.html.Tousethisname,press<return>tocontinue,otherwiseenteranalternative.Entervalueforreport_name:/home/oracle/awr_test1.htmlUsingthereportname/home/oracle/awr_test1.html

根据查看SQL ordered by CPU Time，可观察出相应时间段的高CPU消耗SQL

三、ADDM

与awr类似，依托于快照生成，但有详细的分析建议（可在方法二中手动生成两次快照后使用）

运行exec DBMS_ADDM.ANALYZE_DB()命令时，第2、3参数分别为两次手动生成的快照号

SQL>vartask_namevarchar2(30);SQL>execDBMS_ADDM.ANALYZE_DB(:task_name,21,36);PL/SQLproceduresuccessfullycompleted.SQL>print:task_nameTASK_NAME--------------------------------------------------------------------------------TASK_236SQL>setlong1000000pagesize0;SQL>selectdbms_addm.get_report('TASK_236')fromdual;通过报告可以精确的看到性能主要被消耗于哪里，消耗大小，以及事件描述ADDMReportforTask'TASK_236'-------------------------------AnalysisPeriod---------------AWRsnapshotrangefrom21to36.Timeperiodstartsat25-JUL-1810.30.54PMTimeperiodendsat26-JUL-1810.00.29PMAnalysisTarget---------------Database'ORCL'withDBID1506959389.Databaseversion11.2.0.4.0.Analysiswasrequestedforallinstances,butADDManalyzedinstanceorcl,numbered1andhostedatlocalhost.localdomain.Seethe"AdditionalInformation"sectionformoreinformationontherequestedinstances.ActivityDuringtheAnalysisPeriod-----------------------------------Totaldatabasetimewas1445seconds.Theaveragenumberofactivesessionswas.02.ADDManalyzed1oftherequested1instances.SummaryofFindings-------------------DescriptionActiveSessionsRecommendationsPercentofActivity--------------------------------------------------------------------------1TopSQLStatements.02|88.7342PL/SQLExecution.01|41.5513Unusual"UserI/O"WaitEvent0|28.7844TopSegmentsby"UserI/O"and"Cluster"0|28.6415Undersizedinstancememory0|23.616SequenceUsage0|2.141~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~FindingsandRecommendations----------------------------Finding1:TopSQLStatementsImpactis.02activesessions,88.73%oftotalactivity.--------------------------------------------------------SQLstatementsconsumingsignificantdatabasetimewerefound.Thesestatementsofferagoodopportunityforperformanceimprovement.Recommendation1:SQLTuningEstimatedbenefitis.01activesessions,36.62%oftotalactivity.-------------------------------------------------------------------ActionRunSQLTuningAdvisorontheINSERTstatementwithSQL_ID"5hrxg25g8bdpd".RelatedObjectSQLstatementwithSQL_ID5hrxg25g8bdpd.INSERTINTOT1VALUES(S_T1_ID.NEXTVAL,DBMS_RANDOM.STRING('u',10),SYSDATE)RationaleTheSQLspent100%ofitsdatabasetimeonCPU,I/OandClusterwaits.ThispartofdatabasetimemaybeimprovedbytheSQLTuningAdvisor.RationaleDatabasetimeforthisSQLwasdividedasfollows:45%forSQLexecution,0%forparsing,55%forPL/SQLexecutionand0%forJavaexecution.RationaleSQLstatementwithSQL_ID"5hrxg25g8bdpd"wasexecuted8000000timesandhadanaverageelapsedtimeof0.00004seconds.RationaleToplevelcallstoexecutethePL/SQLstatementwithSQL_ID"7j89gjdpf4m4u"areresponsiblefor100%ofthedatabasetimespentontheINSERTstatementwithSQL_ID"5hrxg25g8bdpd".RelatedObjectSQLstatementwithSQL_ID7j89gjdpf4m4u.beginforiin1..1000000loopinsertintot1values(s_t1_id.Nextval,dbms_random.string('u',10),sysdate);endloop;end;Recommendation2:SQLTuningEstimatedbenefitis0activesessions,28.87%oftotalactivity.-----------------------------------------------------------------ActionInvestigatetheALTERTABLESPACEstatementwithSQL_ID"6sqc239sgbmqf"forpossibleperformanceimprovements.YoucansupplementtheinformationgivenherewithanASHreportforthisSQL_ID.RelatedObjectSQLstatementwithSQL_ID6sqc239sgbmqf.RationaleTheSQLTuningAdvisorcannotoperateonALTERTABLESPACEstatements.RationaleDatabasetimeforthisSQLwasdividedasfollows:100%forSQLexecution,0%forparsing,0%forPL/SQLexecutionand0%forJavaexecution.RationaleI/OandClusterwaitforTABLE"SYS.KOTTD$"withobjectID543consumed97%ofthedatabasetimespentonthisSQLstatement.Recommendation3:SQLTuningEstimatedbenefitis0activesessions,14.08%oftotalactivity.-----------------------------------------------------------------ActionRunSQLTuningAdvisorontheSELECTstatementwithSQL_ID"a0qbnz3z4x4ns".RelatedObjectSQLstatementwithSQL_IDa0qbnz3z4x4ns.select*fromscott.t1wherename=dbms_random.string('u',10)RationaleTheSQLspent100%ofitsdatabasetimeonCPU,I/OandClusterwaits.ThispartofdatabasetimemaybeimprovedbytheSQLTuningAdvisor.RationaleDatabasetimeforthisSQLwasdividedasfollows:5%forSQLexecution,0%forparsing,95%forPL/SQLexecutionand0%forJavaexecution.RationaleSQLstatementwithSQL_ID"a0qbnz3z4x4ns"wasexecuted2timesandhadanaverageelapsedtimeof89seconds.Recommendation4:SQLTuningEstimatedbenefitis0activesessions,9.15%oftotalactivity.----------------------------------------------------------------ActionRunSQLTuningAdvisorontheSELECTstatementwithSQL_ID"gmkaj9nz7vyvw".RelatedObjectSQLstatementwithSQL_IDgmkaj9nz7vyvw.select*fromscott.t3wherename=dbms_random.string('u',10)RationaleTheSQLspent100%ofitsdatabasetimeonCPU,I/OandClusterwaits.ThispartofdatabasetimemaybeimprovedbytheSQLTuningAdvisor.RationaleDatabasetimeforthisSQLwasdividedasfollows:16%forSQLexecution,0%forparsing,84%forPL/SQLexecutionand0%forJavaexecution.RationaleSQLstatementwithSQL_ID"gmkaj9nz7vyvw"wasexecuted2timesandhadanaverageelapsedtimeof62seconds.Finding2:PL/SQLExecutionImpactis.01activesessions,41.55%oftotalactivity.--------------------------------------------------------PL/SQLexecutionconsumedsignificantdatabasetime.Recommendation1:SQLTuningEstimatedbenefitis.01activesessions,41.55%oftotalactivity.-------------------------------------------------------------------ActionTunetheentrypointPL/SQL"SYS.DBMS_RANDOM.STRING"oftype"PACKAGE"andID9372.RefertothePL/SQLdocumentationforadditioninformation.Rationale193secondsspentinexecutingPL/SQL"SYS.DBMS_RANDOM.RECORD_RANDOM_NUMBER"oftype"PACKAGE"andID9372.Rationale193secondsspentinexecutingPL/SQL"SYS.DBMS_RANDOM.REPLAY_RANDOM_NUMBER"oftype"PACKAGE"andID9372.Rationale111secondsspentinexecutingPL/SQL"SYS.DBMS_RANDOM.STRING"oftype"PACKAGE"andID9372.Rationale101secondsspentinexecutingPL/SQL"SYS.DBMS_RANDOM.VALUE#1"oftype"PACKAGE"andID9372.Finding3:Unusual"UserI/O"WaitEventImpactis0activesessions,28.78%oftotalactivity.------------------------------------------------------Waitevent"Datafileinitwrite"inwaitclass"UserI/O"wasconsumingsignificantdatabasetime.Recommendation1:ApplicationAnalysisEstimatedbenefitis0activesessions,28.78%oftotalactivity.-----------------------------------------------------------------ActionInvestigatethecauseforhigh"Datafileinitwrite"waits.RefertoOracle's"DatabaseReference"forthedescriptionofthiswaitevent.Recommendation2:ApplicationAnalysisEstimatedbenefitis0activesessions,28.78%oftotalactivity.-----------------------------------------------------------------ActionInvestigatethecauseforhigh"Datafileinitwrite"waitsinModule"sqlplus@localhost.localdomain(TNSV1-V3)".Recommendation3:ApplicationAnalysisEstimatedbenefitis0activesessions,28.78%oftotalactivity.-----------------------------------------------------------------ActionInvestigatethecauseforhigh"Datafileinitwrite"waitsinService"SYS$USERS".Recommendation4:ApplicationAnalysisEstimatedbenefitis0activesessions,28.78%oftotalactivity.-----------------------------------------------------------------ActionInvestigatethecauseforhigh"Datafileinitwrite"waitswithP1,P2,P3("count,intr,timeout")values"1","256"and"4294967295"respectively.SymptomsThatLedtotheFinding:---------------------------------Waitclass"UserI/O"wasconsumingsignificantdatabasetime.Impactis.01activesessions,32.41%oftotalactivity.Finding4:TopSegmentsby"UserI/O"and"Cluster"Impactis0activesessions,28.64%oftotalactivity.------------------------------------------------------Individualdatabasesegmentsresponsibleforsignificant"UserI/O"and"Cluster"waitswerefound.Recommendation1:SegmentTuningEstimatedbenefitis0activesessions,28.64%oftotalactivity.-----------------------------------------------------------------ActionInvestigateapplicationlogicinvolvingI/OonTABLE"SYS.KOTTD$"withobjectID543.RelatedObjectDatabaseobjectwithID543.ActionLookatthe"TopSQLStatements"findingforSQLstatementsconsumingsignificantI/Oonthissegment.Forexample,theALTERTABLESPACEstatementwithSQL_ID"6sqc239sgbmqf"isresponsiblefor98%of"UserI/O"and"Cluster"waitsforthissegment.RationaleTheI/Ousagestatisticsfortheobjectare:0fullobjectscans,2physicalreads,0physicalwritesand0directreads.SymptomsThatLedtotheFinding:---------------------------------Waitclass"UserI/O"wasconsumingsignificantdatabasetime.Impactis.01activesessions,32.41%oftotalactivity.Finding5:UndersizedinstancememoryImpactis0activesessions,23.6%oftotalactivity.-----------------------------------------------------TheOracleinstancememory(SGAandPGA)wasinadequatelysized,causingadditionalI/OandCPUusage.Thevalueofparameter"memory_target"was"300M"duringtheanalysisperiod.Recommendation1:DatabaseConfigurationEstimatedbenefitis0activesessions,14.46%oftotalactivity.-----------------------------------------------------------------ActionIncreasememoryallocatedtotheinstancebysettingtheparameter"memory_target"to450M.SymptomsThatLedtotheFinding:---------------------------------Waitclass"UserI/O"wasconsumingsignificantdatabasetime.Impactis.01activesessions,32.41%oftotalactivity.HardparsingofSQLstatementswasconsumingsignificantdatabasetime.Impactis0activesessions,2.07%oftotalactivity.Finding6:SequenceUsageImpactis0activesessions,2.14%oftotalactivity.-----------------------------------------------------Sequencecachemisseswereconsumingsignificantdatabasetime.Recommendation1:ApplicationAnalysisEstimatedbenefitis0activesessions,2.14%oftotalactivity.----------------------------------------------------------------ActionInvestigateapplicationorlookattopSQLtofindhotsequences.Usealargercachesizeforthosesequences.TryavoidingtheuseoftheORDERsettingifrunningRAC.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~AdditionalInformation----------------------MiscellaneousInformation-------------------------Waitclass"Application"wasnotconsumingsignificantdatabasetime.Waitclass"Commit"wasnotconsumingsignificantdatabasetime.Waitclass"Concurrency"wasnotconsumingsignificantdatabasetime.Waitclass"Configuration"wasnotconsumingsignificantdatabasetime.CPUwasnotabottleneckfortheinstance.Waitclass"Network"wasnotconsumingsignificantdatabasetime.Sessionconnectanddisconnectcallswerenotconsumingsignificantdatabasetime.

“oracle数据库CPU过高问题定位分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！