EBS问题诊断方法

EBS问题诊断常用方法--OracleSSC--AndrewDuTopics•HowToGetLevel12Trace•HowToGetTraceFileForConcurrentPrograms?•HowtogetFormsLevelTraceandFRD?•HowToGetFNDDebug?•HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?•HowToStoreFADebugMessagesInAFile?•HowtoGetaDebuginReceivablesin11.5.10?•HowtorunaPayablesConcurrentPrograminDebugMode?•HowtogenerateadebugfileinOM?•HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData•HowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?•HowToDebugWIP?•HowtogetDebugInformationforInventoryMaterialTransaction?HowToGetLevel12Trace?为什么需要Trace?•提取执行与错误信息•获取执行程序/SQL•获取执行计划与成本信息•获取绑定变量值•获取等待事件等HowToGetLevel12Trace?Gathering10046trace•SQL_TRACE是Oracle数据库提供的用于进行SQL跟踪的手段，在某种意义上讲，可以说是Oracle最强有力的辅助诊断工具。•10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强（包含绑定值与等待事务事件)•10046事件可以设置以下四个级别:•1-启用标准的SQL_TRACE功能,等价于sql_trace•4-Level1加上绑定值(bindvalues)•8-Level1+等待事件跟踪•12-Level1+Level4+Level8HowToGetLevel12Trace?在EBS中启用10046trace•切换到系统管理员职责•Navigate:SystemAdministratorProfile=System•在用户层查询profileoption'InitializationSQLStatement-Custom'.•User:Usersubmittingtheprocess•Profile:InitializationSQLStatement-Custom•编辑User字段，输入下列值并保存•beginfnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTERSESSIONSETEVENTS='||''''||'10046TRACENAMECONTEXTFOREVER,LEVEL12'||'''');end;HowToGetLevel12Trace?SessionTracing•altersessionsettracefile_identifier='10046';•altersessionsettimed_statistics=true;•altersessionsetstatistics_level=all;•altersessionsetmax_dump_file_size=unlimited;•altersessionsetevents'10046tracenamecontextforever,level12';•altersessionsetevents'10046tracenamecontextoff';HowToGetLevel12Trace?TraceotherSession•OpenTrace•execsys.dbms_system.set_ev(sid,serial#,10046,12,'');•CloseTrace•execsys.dbms_system.set_ev(sid,serial#,10046,0,'');HowToGetLevel12Trace?•SQLTracepath•showparameteruser_dump_dest•select*fromv$parameterwherenamelike'user_dump_dest'HowToGetLevel12Trace?Whattolookforinthetrace?•Rollback•Error•Selectreturningnorows(forORA-01403errors)•Calltostoredprocedures•Execstatements(lastexeccompletedpriortorollback)•ForhandledexceptionslookforcallstocodethatthrowserrormessageHowToGetLevel12Trace?HowtoreadaRawTrace(note39817.1)•CaseStudy:ORA-01403NoDataFound•PARSE#198:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1078209519010842•EXEC#198:c=0,e=423,p=0,cr=0,cu=20,mis=0,r=0,dep=1,og=4,tim=1078209519011382•RPCEXEC:c=530000,e=1892352•RPCCALL:PROCEDUREAPPS.ARP_STANDARD.DEBUG(LINEINVARCHAR2);•RPCBINDS:•bind0:dty=1bfp=0ae65b50flg=08avl=54mxl=54val=01:42:27-arxtwmai_form_main.arxtwmai_main(ON-ERROR)+•PARSINGINCURSOR#92len=16dep=0uid=173oct=45lid=173tim=1078209519016729hv=2670021379ad='60292bf0'•ROLLBACKTOFM_1•ENDOFSTMTLastStatemExecutedEntBeforeRollbackFindRollbackHowToGetLevel12Trace?Howtoreadarawtrace(cont.)PARSINGINCURSOR#198len=2959dep=1uid=173oct=3lid=173tim=1078209518764177hv=3671187712ad='63528a1c'SELECTcm.customer_trx_line_id,cm.previous_customer_trx_line_id,….WHEREcm.previous_customer_trx_line_id=:b0BINDS#198:bind0:dty=11mxl=16(16)mal=00scl=00pre=00oacflg=18oacfl2=1size=16offset=0bfp=40cd29e8bln=16avl=16flg=05value=254825ExecstatementsFindBindvaluesSearchfor“BINDS”statementHowToGetLevel12Trace?RawTrace+Understanding=RootCause•Inthepriorexample:–TheformthrewanORA-01403nodatafound–Cursor#198wasthelaststatementexecutedpriortorollbackandreturnednorows(causeofORA-1403error)–SQLbelongingto#198identifiedtheselectstatement–IDbelongingtotransactionthatfailedwaslistedasabind•Nextsteps:–Drilldowntothecodeleveltoinvestigatetheissue–TARrelated,uploadtheRAWandTKProftracefilesTracefileanalyzetool•Tkprofcommand•tkproftracefileoutfile[explain=user/password][options...]•TkprofOptions•print=integerListonlythefirst'integer'SQLstatements.•insert=filenameListSQLstatementsanddatainsideINSERTstatements.•sys=noTKPROFdoesnotlistSQLstatementsrunasuserSYS.•record=filenameRecordstatementsfoundinthetracefile.•sort=optionSetofzeroormoresortoptionsTracefileanalyze(TKProfInterpretation)•TKProfoutputstructure•SQLStatement•Parse/Execute/Fetchstatisticsandtimings•LibraryCacheinformation•Rowsourceplan•EventswaitedforbythestatementTracefileanalyze(TKProfInterpretation)HowToGetTraceFileForConcurrentPrograms?•在并发程序定义中启用EnableTrace选项SystemAdministrator-Concurrent-Program-DefineHowToGetTraceFileForConcurrentPrograms?•SetProfileConcurrent:AllowDebuggingenabledhighleveltraceTopics•HowToGetLevel12Trace•HowToGetTraceFileForConcurrentPrograms?•HowtogetFormsLevelTraceandFRD?•HowToGetFNDDebug?•HowToRunSelectedGeneralLedgerProgramsInDebugModeinR11.5?•HowToStoreFADebugMessagesInAFile?•HowtoGetaDebuginReceivablesin11.5.10?•HowtorunaPayablesConcurrentPrograminDebugMode?•HowtogenerateadebugfileinOM?•HowtoGenerateandReturnthePODocumentApprovalManagerTraceDebugData•HowtoGenerateReceivingTransactionDebugStatementsin11.5.10andR12?•HowToDebugWIP?•HowtogetDebugInformationforInventoryMaterialTransaction?HowtogetFormsLevelTraceandFRD?WhatisFRD?•FRDisFormsRuntimeDiagnostics•TheFormsRuntimeDiagnostics(FRD)loggingmechanismwasintroducedinForms4.5.10,andisa