OracleAWR报告分析实例讲解

{管理信息化ORACLE}OracleAWR报告分析实例讲解WORKLOADREPOSITORYreportforDBNameDBIdInstanceInstnumReleaseRACHostICCI396ICCI11.0YESHPGICCI1SnapIdSnapTimeSessionsCursors/SessionBeginSnap:267825-Dec-0814:04:50241.5EndSnap:268025-Dec-0815:23:37261.5Elapsed:78.79(mins)DBTime:11.05(mins)DBTime不包括OracleDBTime远远小于Elapsed时间，说明数据库比较空闲。在79分钟里（其间收集了3次快照数据），数据库耗时11分钟，RDA数据中显示系统有8个逻辑CPU（4个物理CPUCPU耗时1.4分钟，CPU利用率只有大约2%（1.4/79）。说明系统压力非常小。表性能问题的时间段。ReportSummaryCacheSizesBeginEndBufferCache:3,344M3,344MStdBlockSize:8KSharedPoolSize:704M704MLogBuffer:14,352K显示SGA中每个区域的大小（在AMM较。sharedpool主要包括librarycache和dictionarycache。librarycache用来SQLPL/SQL和Javaclasseslibrarycache用来存储最近引用的数据字典。发生在librarycache或dictionarycache的cachemiss代价要比发生在buffercachesharedpool的设置要确保最近使用的数据都能被cache。LoadProfilePerSecondPerTransactionRedosize:918,805.72775,912.72Logicalreads:3,521.772,974.06Blockchanges:1,817.951,535.22Physicalreads:68.2657.64Physicalwrites:362.59306.20Usercalls:326.69275.88Parses:38.6632.65Hardparses:0.030.03Sorts:0.610.51Logons:0.010.01Executes:354.34299.23Transactions:1.18%BlockschangedperRead:51.62RecursiveCall%:51.72Rollbackpertransaction%:85.49RowsperSort:########载情况，绝大多数据并没有一个所谓“正确”的值，然而Logons大于每秒1~2Hardparses大于每秒100parses超过每秒300表明可能有争用问题。Redosize：每秒/每事务产生的redo繁重程序。Logicalreads：每秒/每事务逻辑读的块数Blockchanges：每秒/每事务修改的块数Physicalreads：每秒/每事务物理读的块数Physicalwrites：每秒/每事务物理写的块数Usercalls：每秒/每事务用户call次数Parses：SQL解析的次数Hardparses：其中硬解析的次数，硬解析太多，说明SQL重用率不高。Sorts：每秒/每事务的排序次数Logons：每秒/每事务登录的次数Executes：每秒/每事务SQL执行次数Transactions：每秒事务数BlockschangedperRead：表示逻辑读用于修改数据块的比例RecursiveCall：递归调用占所有操作的比率Rollbackpertransaction：每事务的回滚率RowsperSort：每次排序的行数注:Oracle的硬解析和软解析提到软解析(softparse)和硬解析(hardparse)，就不能不说一下Oracle对sqlsql语句交付OracleOracle对此sql将进行几个步骤的处理过程：1、语法检查(syntaxcheck)检查此sql的拼写是否语法。2、语义检查(semanticcheck)诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。3、对sql语句进行解析(parse)利用内部算法对sql进行解析，生成解析树(parsetree)及执行计划(executionplan)。4、执行sql，返回结果(executeandreturn)其中，软、硬解析就发生在第三个过程里。Oracle利用内部的hash算法来取得该sql的hash值，然后在librarycache里查找是否存在该hash值；假设存在，则将此sql与cache中的进行比较；这也就是软解析的过程。2生成执行计划的动作。这个过程就叫硬解析。sql极力避免硬解析，尽量使用软解析。InstanceEfficiencyPercentages(Target100%)BufferNowait%:100.00RedoNoWait%:100.00BufferHit%:98.72In-memorySort%:99.86LibraryHit%:99.97SoftParse%:99.92ExecutetoParse%:89.09LatchHit%:99.99ParseCPUtoParseElapsd%:7.99%Non-ParseCPU:99.95本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。其中BufferHitRatio也称CacheHitRatio，LibraryHitratio也称LibraryCacheHitratio。同LoadProfile一节相同，这一节也没有所谓“正确”询的DSS环境，20%的BufferHitRatio是可以接受的，而这个值对于一个OLTPOracleOLTPT系统，BufferHitRatio理想应该在90%以上。BufferNowait表示在内存获得数据的未等待比例。bufferhit表示进程从内存中找到数据块的比率，监视这个值是否发生重大变OLTP80%库分配更多的内存。RedoNoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低（可参考90%阀值），考虑增加LOGBUFFER。libraryhit表示Oracle从LibraryCache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，Oracle检查LibraryCache确定是否存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在LibraryCache中为它分配共享SQL区。低的libraryhitratio会导致过多的解析，增加CPU消耗，降低性能。如果libraryhitratio低于90%，可能需要调大sharedpool区。LatchHitLatchSERVER进程获取访问LatchHit>99%SharedPoollatch争用，可能由于未共享的SQL，或者LibraryCache太小，可使用绑定变更或调大SharedPool解决。ParseCPUtoParseElapsd/(解析实际运行时间+解析中等待资源时间)，越高越好。Non-ParseCPUSQL实际运行时间/(SQL实际运行时间+SQL解析时间)示解析消耗时间过多。ExecutetoParseSQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。In-memorySort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。SoftParse：软解析的百分比（softs/softs+hardssql在共享区的命中率，太低则需要调整应用使用绑定变量。SharedPoolStatisticsBeginEndMemoryUsage%:47.1947.50%SQLwithexecutions>1:88.4879.81%MemoryforSQLw/exec>1:79.9973.52MemoryUsage%应该稳定在75%-90%间，如果太小，说明SharedPool有浪费，而如果高于90，说明共享池中有争用，内存不足。SQLwithexecutions>1：执行次数大于1的sql比率，如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。MemoryforSQLw/exec>1：执行次数大于1的SQL消耗内存的占比。Top5TimedEventsEventWaitsTime(s)AvgWait(ms)%TotalCallTimeWaitClassCPUtime51577.6SQL*Netmoredatafromclient27,3196429.7Networklogfileparallelwrite5,4974797.1SystemI/Odbfilesequentialread7,9003545.3UserI/Odbfileparallelwrite4,8063475.1SystemI/O这是报告概要的最后一节，显示了系统中最严重的5‘bufferbusywait’是较严重的等待事件，我们应当继续研究报告中BufferWait和File/TablespaceIO区的内容，识别哪I/O排序的SQL语句区以识别哪些语句在执行大量I/OTablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待，就需要察看详细的LATCH统计识别哪些LATCH产生的问题。logfileparallelwrite7%的CPU时间。通常，在没有问题的数据库中，CPUtime总是列在第一个。更多的等待事件，参见本报告的WaitEvents一节。RACStatisticsBeginEndNumberofInstances:22GlobalCacheLoadProfilePerSecondPerTransactionGlobalCacheblocksreceived:4.163.51GlobalCacheblocksserved:5.975.04GCS/GESmessagesreceived:408.47344.95GCS/GESmessagessent:258.03217.90DBWRFusionwrites:0.050.05EstdInterconnecttraffic(KB)211.16GlobalCacheEfficiencyPercentages(Targetlocal+remote100%)Bufferaccess-localcache%:98.60Bufferaccess-remotecache%:0.12Bufferaccess-disk%:1.28GlobalCacheandEnqueueServices-WorkloadCharacteristicsAvgglobalenqueuegettime(ms):0.1Avgglobalcachecrblockreceivetime(ms):1.1Avgglobalcachecurrentblockreceivetime(ms):0.8Avgglobalcachecrblockbuildtime(ms):0.0Avgglobalcachecrblocksendtime(ms):0.0Globalcachelogflushesforcrblocksserved%:3.5Avgglobalcachecrblockflushtime(ms):3.9Avgglobalcachecurrentblockpintime(ms):0.0Avgglobalcachecurrentblocksendtime(ms):0.0Globalcachelogflushesforcurrentblocksserved%:0.4Avgglobalcachecurrentblockflushtime(ms):3.0GlobalCacheandEnqueueServices-MessagingStatisticsAvgmessagesentqueuetime(ms):0.0Avgmessagesentqueuetimeonksxp(ms):0.3Avgmessagereceivedqueuetime(ms):0.5AvgGCSmessageprocesstime(ms):0.0AvgGESmessageprocesstime(ms):0.0%ofdirectsentmessages:14.40%ofindirectsentmessages:77.04%offlowcontrolledmessages:8.56MainReportWaitEventsStatisticsSQLStatisticsInstanceActivityStatisticsIOStatsBufferPoolStatisticsAdvisoryStatisticsWaitStatisticsUndoStatisticsLatchStatisticsSegmentStatisticsDictionaryCacheStatisticsLibraryCacheStatisticsMemoryStatisticsStreamsStatisticsResourceLimitStatisticsParametersWaitEventsStatisticsTimeModelStatisticsWaitClassWaitEventsBackgroundWaitEventsOperatingSystemStatisticsServiceStatisticsServiceWaitClassStatsBacktoTopTimeModelStatisticsTotaltimeindatabaseuser-calls(DBTime):663sStatisticsincludingtheword"background"measurebackgroundprocesstime,andsodonotcontributetotheDBtimestatisticOrderedby%orDBtimedesc,StatisticnameStatisticNameTime(s)%ofDBTimeDBCPU514.5077.61sqlexecuteelapsedtime482.2772.74parsetimeelapsed3.760.57PL/SQLexecutionelapsedtime0.500.08hardparseelapsedtime0.340.05connectionmanagementcallelapsedtime0.080.01hardparse(sharingcriteria)elapsedtime0.000.00repeatedbindelapsedtime0.000.00PL/SQLpilationelapsedtime0.000.00failedparseelapsedtime0.000.00DBtime662.97backgroundelapsedtime185.19backgroundcputime67.48此节显示了各种类型的数据库处理任务所占用的CPU时间。BacktoWaitEventsStatisticsBacktoTopWaitClasss-secondcs-centisecond-100thofasecondms-millisecond-1000thofasecondus-microsecond-thofasecondorderedbywaittimedesc,waitsdescWaitClassWaits%Time-outsTotalWaitTime(s)Avgwait(ms)Waits/txnUserI/O66,8370.00120211.94SystemI/O28,2950.009335.05Network1,571,4500.00660280.72Cluster210,5480.0029037.61Other81,78371.8228014.61Application333,1550.0016059.51Concurrency5,1820.04510.93Commit9190.00440.16Configuration25,42799.46104.54BacktoWaitEventsStatisticsBacktoTopWaitEventss-secondcs-centisecond-100thofasecondms-millisecond-1000thofasecondus-microsecond-thofasecondorderedbywaittimedesc,waitsdesc(idleeventslast)EventWaits%Time-outsTotalWaitTime(s)Avgwait(ms)Waits/txnSQL*Netmoredatafromclient27,3190.006424.88logfileparallelwrite5,4970.004790.98dbfilesequentialread7,9000.003541.41dbfileparallelwrite4,8060.003470.86dbfilescatteredread10,3100.003131.84directpathwrite42,7240.003017.63reliablemessage3552.8218490.06SQL*Netbreak/resettoclient333,0840.0016059.50dbfileparallelread3,7320.001340.67gccurrentmultiblockrequest175,7100.0010031.39controlfilesequentialread15,9740.001012.85directpathreadtemp1,8730.00950.33gccrmultiblockrequest20,8770.00803.73logfilesync9190.00440.16gccrblockbusy5260.00360.09enq:FB-contention10,3840.00301.85DFSlockhandle3,5170.00310.63controlfileparallelwrite1,9460.00310.35gccurrentblock2-way4,1650.00200.74librarycachelock4320.00240.08name-servicecallwait220.002760.00rowcachelock3,8940.00200.70gcslogflushsync1,25942.02210.22osthreadstartup185.562890.00gccrblock2-way3,6710.00200.66gccurrentblockbusy1130.001120.02SQL*Netmessagetoclient1,544,110.0010275.835gcbufferbusy156.671700.00gccrdiskread3,2720.00100.58directpathwritetemp1590.00150.03gccurrentgrantbusy8980.00110.16logfileswitchpletion290.001170.01CGSwaitforIPCmsg48,73999.87008.71gccurrentgrant2-way1,1420.00000.20kjbdrmcvtqlmondrmquiesce:pingpletion90.000190.00enq:US-contention5670.00000.10directpathread1380.00010.02enq:WF-contention140.00090.00ksxrpollremoteinstances13,29158.45002.37librarycachepin2110.00010.04gesglobalresourcedirectorytobefrozen9100.000100.00waitforscnack5830.00000.10logfilesequentialread360.00020.01undosegmentextension25,34299.79004.53rdbmsipcreply2790.00000.05ktfbtgex6100.000100.00enq:HW-contention440.00010.01gccrgrant2-way1580.00000.03enq:TX-indexcontention10.000340.00enq:CF-contention640.00010.01PXDeq:SignalACK3721.62010.01latchfree30.000100.00bufferbusywaits6250.16000.11KJC:Waitformsgsendstoplete1540.00000.03logbufferspace110.00020.00enq:PS-contention460.00010.01enq:TM-contention700.00000.01IPCsendpletionsync40100.00000.01PXDeq:reapcredit1,54499.81000.28logfilesinglewrite360.00000.01enq:TT-contention460.00000.01enq:TD-KTFdumpentries120.00010.00readbyothersession10.000120.00LGWRwaitforredocopy5400.00000.10PXDeqCredit:sendblkd175.88000.00enq:TA-contention140.00000.00latch:gesresourcehashlist440.00000.01enq:PI-contention80.00000.00writepletewaits10.00020.00enq:DR-contention30.00000.00enq:MW-contention30.00000.00enq:TS-contention30.00000.00PXqreflatch150100.00000.03enq:MD-contention20.00000.00latch:KCLgcelementparentlatch110.00000.00enq:JS-jobrunlock-synchronize10.00010.00SQL*Netmoredatatoclient160.00000.00latch:cachebufferslruchain10.00000.00enq:UL-contention10.00000.00gccurrentsplit10.00000.00enq:AF-taskserialization10.00000.00latch:objectqueueheaderoperation30.00000.00latch:cachebufferschains10.00000.00latch:enqueuehashchains20.00000.00SQL*Netmessagefromclient1,544,110.0012,6268275.833gcsremotemessage634,88498.649,20314113.41DIAGidlewait23,6280.004,6161954.22gesremotemessage149,59193.454,6123126.72StreamsAQ:qmnslaveidlewait1670.004,611276110.03StreamsAQ:qmncoordinatoridlewait35147.864,611131370.06StreamsAQ:waitingformessagesinthequeue488100.004,60594360.09virtualcircuitstatus157100.004,596292720.03PXIdleWait1,07297.112,58124070.19jobqslavewait14597.9342028960.03StreamsAQ:waitingfortimemanagementorcleanupt1100.002702697470.00asksPXDeq:ParseReply4040.00030.01PXDeq:ExecutionMsg12126.45000.02PXDeq:JoinACK3842.11010.01PXDeq:ExecuteReply3432.35000.01PXDeq:MsgFragment160.00000.00StreamsAQ:RACqmncoordinatoridlewait351100.00000.06classslavewait20.00000.00dbfilescatteredread等待事件是当SESSION等待multi-blockI/O时发生的，通过是由于fulltablescans或indexfastfullscans。发生过多读操作的Segments可以在“和节中识别（在其它版本的报告中，可能是别的名称）。如果在OLTP应用中，不应该有过多的全扫描操作，而应使用选择性好的索引操作。DBfilesequentialread等待意味着发生顺序I/O操作的热点SEGMENT，然后通过对大表进行分区以减少I/O量，或者优化执行计划（通过使用存储大纲或执行数据分析）以避免单块读操作引起的sequentialread等待。通过在批量应用中，DBfilesequentialread是很影响性能的事件，总是应当设法避免。LogFileParallelWrite事件是在等待LGWR进程将REDO记录从LOG缓冲区写到联机日志文件时发生的。虽然写操作可能是并发的，但LGWR需要等待最后的I/OOS完成所有请求的LOG文件移到更快的磁盘上或者条带化磁盘（减少争用）而降低这个等待。BufferBusyWaits事件是在一个SESSION需要访问BUFFERCACHE中的一个数据库块而又不能访问时发生的。缓冲区“busy”的两个原因是：1SESSION正在将数据块读进BUFFER。2）另一个SESSION正在以排它模式占用着这块被请求的BUFFER。可以在“SegmentsbyBufferBusyWaits”一节中找出发生这种等待的SEGMENTreverse-keyindexes并对热表进行分区而减少这种等待事件。LogFileSync事件，当用户SESSION执行事务操作（COMMIT或ROLLBACK等）LGWR进程将所需要的所有REDO信息从LOGBUFFER写到LOG用户SESSION等待LGWR返回安全写入磁盘的通知时发生此等待。减少此等待的方法写LogFileParallelWrite事件的处理。EnqueueWaitsSESSION锁类型。导致Enqueue等待的主要锁类型有三种：TX（事务锁）,TMD（ML锁）和ST（空间管理锁）。BacktoWaitEventsStatisticsBacktoTopBackgroundWaitEventsorderedbywaittimedesc,waitsdesc(idleeventslast)EventWaits%Time-outsTotalWaitTime(s)Avgwait(ms)Waits/txnlogfileparallelwrite5,4970.004790.98dbfileparallelwrite4,8060.003470.86eventsinwaitclassOther69,00283.2522012.33controlfilesequentialread9,3230.00711.67controlfileparallelwrite1,9460.00310.35osthreadstartup185.562890.00directpathread1380.00010.02dbfilesequentialread210.00050.00directpathwrite1380.00000.02logfilesequentialread360.00020.01gccrblock2-way960.00000.02gccurrentblock2-way780.00000.01logbufferspace110.00020.00rowcachelock590.00000.01logfilesinglewrite360.00000.01bufferbusywaits1510.66000.03gccurrentgrantbusy290.00000.01librarycachelock40.00010.00enq:TM-contention100.00000.00gccurrentgrant2-way80.00000.00gccrmultiblockrequest70.00000.00gccrgrant2-way50.00000.00rdbmsipcmessage97,28873.7750,19451617.38gcsremotemessage634,88698.649,20314113.41DIAGidlewait23,6280.004,6161954.22pmontimer1,621100.004,61528470.29gesremotemessage149,59193.454,6123126.72StreamsAQ:qmnslaveidlewait1670.004,611276110.03StreamsAQ:qmncoordinatoridlewait35147.864,611131370.06smontimer2776.504,531163560.05StreamsAQ:waitingfortimemanagementorcleanupta1100.002702697470.00sksPXDeq:ParseReply4040.00030.01PXDeq:JoinACK3842.11010.01PXDeq:ExecuteReply3432.35000.01StreamsAQ:RACqmncoordinatoridlewait351100.00000.06BacktoWaitEventsStatisticsBacktoTopOperatingSystemStatisticsStatisticTotalNUM_LCPUS0NUM_VCPUS0AVG_BUSY_TIME101,442AVG_IDLE_TIME371,241AVG_IOWAIT_TIME5,460AVG_SYS_TIME25,795AVG_USER_TIME75,510BUSY_TIME812,644IDLE_TIME2,971,077IOWAIT_TIME44,794SYS_TIME207,429USER_TIME605,215LOAD0OS_CPU_WAIT_TIME854,100RSRC_MGR_CPU_WAIT_TIME0PHYSICAL_MEMORY_BYTES8,589,934,592NUM_CPUS8NUM_CPU_CORES4NUM_LCPUS：如果显示0，是因为没有设置LPARSNUM_VCPUS：同上。AVG_BUSY_TIME：BUSY_TIME/NUM_CPUSAVG_IDLE_TIME：IDLE_TIME/NUM_CPUSAVG_IOWAIT_TIME：IOWAIT_TIME/NUM_CPUSAVG_SYS_TIME：SYS_TIME/NUM_CPUSAVG_USER_TIME：USER_TIME/NUM_CPUSaroBUSY_TIME：timeequivof%usr+%sysinsaroutputIDLE_TIME：timeequivof%idleinsarIOWAIT_TIME：timeequivof%wioinsarSYS_TIME：timeequivof%sysinsarUSER_TIME：timeequivof%usrinsarLOAD：未知OS_CPU_WAIT_TIME：supposedlytimewaitingonrunqueuesRSRC_MGR_CPU_WAIT_TIME：timewaitedcozofresourcemanagerPHYSICAL_MEMORY_BYTES：totalmemoryinusesupposedlyNUM_CPUS：numberofCPUsreportedbyOSNUM_CPU_CORES：numberofCPUsocketsonmotherboard总的elapsedtime也可以用以公式计算：BUSY_TIME+IDLE_TIME+IOWAITTIME或：SYS_TIME+USER_TIME+IDLE_TIME+IOWAIT_TIME（因为BUSY_TIME=SYS_TIME+USER_TIME）BacktoWaitEventsStatisticsBacktoTopServiceStatisticsorderedbyDBTimeServiceNameDBTime(s)DBCPU(s)PhysicalReadsLogicalReadsICCI608.10496.60315,84916,550,972SYS$USERS54.7017.806,53958,929ICCIXDB0.000.0000SYS$BACKGROUND0.000.0028238,990BacktoWaitEventsStatisticsBacktoTopServiceWaitClassStatsWaitClassinfoforservicesintheServiceStatisticssection.TotalWaitsandTimeWaiteddisplayedforthefollowingwaitclasses:UserI/O,Concurrency,Administrative,NetworkTimeWaited(WtTime)incentisecond(100thofasecond)ServiceNameUserI/OTotalWtsUserI/OWtTimeConcurcyTotalWtsConcurcyWtTimeAdminTotalWtsAdminWtTimeNetworkTotalWtsNetworkWtTimeICCI5982686404621338006552SYS$USERS65673238231110073233SYS$BACKGROU4431153301680000NDBacktoWaitEventsStatisticsBacktoTopSQLStatisticsSQLorderedbyElapsedTimeSQLorderedbyCPUTimeSQLorderedbyGetsSQLorderedbyReadsSQLorderedbyExecutionsSQLorderedbyParseCallsSQLorderedbySharableMemorySQLorderedbyVersionCountSQLorderedbyClusterWaitTimeCompleteListofSQLText本节按各种资源分别列出对资源消耗最严重的SQL期内全部资源的比例，这给出我们调优指南。例如在一个系统中，CPU资源是系统性能瓶颈所在，那么优化buffergets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中，调优的目标应该是physicalIOs最多的SQL语句。在STATSPACKSQLHashValue通过下面语句从数据库中查到：selectsql_textfromstats$sqltextwherehash_value=&hash_valueorderbypiece;BacktoTopSQLorderedbyElapsedTimeResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.%TotalDBTimeistheElapsedTimeoftheSQLstatementdividedintotheTotalDatabaseTimemultipliedby100ElapsedTimeCPUTime(ExecutioElapperExe%TotalDBTiSQLIdSQLModulSQLText(s)s)nsc(s)mee9357193.5014.10d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUID_y)...7675172,3290.0011.524vja2k2gdtyu(TNSV1-V3insertintoICCICCSvalues(:p)...5842158.048.75569r5k05drsj(TNSV1-V3insertintoCUMIselectCUSV_7)...5142150.937.68ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUSM_c)...3836166,0690.005.677gtztzv329wg,fromco0...353135.005.286z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAME,TdO_...2323172,3290.003.461dm3bq36vu3g(TNSV1-V3insertintoiccifnsactvalue8)s...151152.982.25djs2w2f17nw2DECLAREjobBINARY_INTEGER:z=...1414172,9830.002.167wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORM_Az)D...1313172,3370.002.00gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalues4)...1313166,0510.001.89chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvalues(:j)...10419.701.460yv9t4qb1zb2(TNSV1-V3selectCUID_CUST_NO,CUID_Ib)D_...10851.911.441crajpb7j5tyINSERTINTOSTATS$SGA_TARGEzT_A...88172,3290.001.2538apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOVEs)R...88172,9830.001.165c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEwhx)...BacktoSQLStatisticsBacktoTopSQLorderedbyCPUTimeResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.%TotalDBTimeistheElapsedTimeoftheSQLstatementdividedintotheTotalDatabaseTimemultipliedby100CPUTime(s)ElapsedTime(s)ExecutionsCPUperExec(s)%TotalDBTimeSQLIdSQLModuleSQLText7576172,3290.0011.524vja2k2gdtyu(TNSV1-V3insertintoICCICCSvalues(:p)...5793157.3114.10d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUID_y)...4251142.437.68ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUSM_c)...4258142.018.75569r5k05drsj(TNSV1-V3insertintoCUMIselectCUSV_7)...3638166,0690.005.677gtztzv329wg,fromco0...2323172,3290.003.461dm3bq36vu3g(TNSV1-V3insertintoiccifnsactvalue8)s...1414172,9830.002.167wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORM_Az)D...1313172,3370.002.00gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalues4)...1313166,0510.001.89chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvalues(:j)...111552.232.25djs2w2f17nw2DECLAREjobBINARY_INTEGER:z=...88172,3290.001.2538apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOVEs)R...81051.601.441crajpb7j5tyINSERTINTOSTATS$SGA_TARGEzT_A...88172,9830.001.165c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEwhx)...41013.541.460yv9t4qb1zb2(TNSV1-V3selectCUID_CUST_NO,CUID_Ib)D_...33513.135.286z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAME,TdO_...BacktoSQLStatisticsBacktoTopSQLorderedbyGetsResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.TotalBufferGets:16,648,792CapturedSQLaccountfor97.9%ofTotalBufferGetsExecutionsGetsperExec%TotalCPUTime(s)ElapsedTime(s)SQLIdSQLModuleSQLText3,305,363172,32919.1819.8574.5776.414vja2k2gdtyu(TNSV1-V3insertintoICCICCSvaluesp)(:...2,064,41412,064,414.12.4057.3193.50d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUI00y)D_...1,826,869166,06911.0010.9735.8437.607gtztzv329wg,from0co...1,427,648172,3378.288.5812.9713.29gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalu4)es...1,278,667172,3297.427.6822.8522.941dm3bq36vu3g(TNSV1-V3insertintoiccifnsactval8)ues...1,216,36711,216,367.7.3142.4350.93ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUS00c)M_...1,107,30511,107,305.6.6542.0158.04569r5k05drsj(TNSV1-V3insertintoCUMIselectCUS007)V_...898,868172,9835.205.4014.2814.347wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORMz)_AD...711,450166,0514.284.2712.5212.55chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvaluesj)(:...692,996172,3294.024.168.318.3138apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOs)VER...666,748166,0524.024.006.366.367v9dyf5r424y(TNSV1-V3selectNEWACTNOinto:b0frh)om...345,357172,9832.002.077.707.715c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEx)wh...231,75651,6334.491.395.755.8349ms69srnaxz(TNSV1-V3insertintoICCIRPYVvaluej)s(...BacktoSQLStatisticsBacktoTopSQLorderedbyReadsTotalDiskReads:322,678CapturedSQLaccountfor66.1%ofTotalPhysicalReadsExecutionsReadsperExec%TotalCPUTime(s)ElapsedTime(s)SQLIdSQLModuleSQLText66,286166,286.0020.5457.3193.50d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUIy)D_...50,646150,646.0015.703.549.700yv9t4qb1zb2(TNSV1-V3selectCUID_CUST_NO,CUIDb)_ID_...24,507124,507.007.5942.0158.04569r5k05drsj(TNSV1-V3insertintoCUMIselectCUS7)V_...21,893121,893.006.7842.4350.93ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUSc)M_...19,761119,761.004a7nh7j8zmfrz(TNSV1-V3selectCUSV_CUST_NOfromCw)UMI...19,554119,554.006.061.273.8338gak8u2qm11SQL*Plusselectcount(*)fromCUSVAwA_T...6,34216,342.001.973.1335.006z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAMdE,TO_...4,38514,385.001.361.592.43cp5duhcsj72q(TNSV1-V3selectCUSM_CUST_ACCT_NO0)from...63512.600.0211.1714.91djs2w2f17nw2DECLAREjobBINARY_INTEGEzR:=...35135.000.010.080.671uk5m5qbzj1vSQL*PlusBEGINdbms_workload_repotsitory...BacktoSQLStatisticsBacktoTopSQLorderedbyExecutionsTotalExecutions:1,675,112CapturedSQLaccountfor99.8%ofTotalExecutionsRowsProcessedRowsperExecCPUperExec(s)ElapperExec(s)SQLIdSQLModuleSQLText172,983172,3291.000.000.005c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEx)wh...172,983172,3291.000.000.007wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORMz)_AD...172,337172,3371.000.000.00gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalu4)es...172,329172,3291.000.000.001dm3bq36vu3g(TNSV1-V3insertintoiccifnsactval8)ues...172,329172,3291.000.000.0038apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOs)VER...172,3296,2860.040.000.004vja2k2gdtyu(TNSV1-V3insertintoICCICCSvaluesp)(:...166,069166,0691.000.000.007gtztzv329wg,from0co...166,052166,0521.000.000.007v9dyf5r424y(TNSV1-V3selectNEWACTNOinto:b0frh)om...166,051166,0511.000.000.00chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvaluesj)(:...51,74051,7401.000.000.00bu8tnqr3xv25(TNSV1-V3selectcount(*)into:b0frq)o...51,63351,6331.000.000.0049ms69srnaxz(TNSV1-V3insertintoICCIRPYVvaluej)s(...BacktoSQLStatisticsBacktoTopSQLorderedbyParseCallsTotalParseCalls:182,780CapturedSQLaccountfor99.0%ofTotalParseCallsExecutions%TotalParsesSQLIdSQLModuleSQLText166,069166,06990.867gtztzv329wg0,fromco...6,3046,3043.452ym6hhaq30r73selecttype#,blocks,extents,...2,4372,4381.33bsa0wjtftg3uwselectfile#fromfile$where...1,5681,5680.869qgtwh66xg6nzupdateseg$settype#=:4,bloc...1,5541,5540.85aq4js2gkfjru8updatetsq$setblocks=:3,max...4444440.24104pd9mm3fh9pselectblocks,maxblocks,gran...4214210.23350f5yrnnmshslocktable_mods$inex...4214210.23g00cj285jmgswupdate_mods$setinser...86860.053m8smr0v7v1m6INSERTINTO$_adv_messa...81810.04f80h0xb1qvbskSELECT$_adv_seq_msggro...BacktoSQLStatisticsBacktoTopSQLorderedbySharableMemoryNodataexistsforthissectionofthereport.BacktoSQLStatisticsBacktoTopSQLorderedbyVersionCountNodataexistsforthissectionofthereport.BacktoSQLStatisticsBacktoTopSQLorderedbyClusterWaitTimeClusterWaitTime(s)CWT%ofElapsdTimeElapsedTime(s)CPUTime(s)ExecutionsSQLIdSQLModuleSQLText10.9611.7293.5057.311d8z0u8hgj8xd(TNSV1-V3)insertintoCUIDselectCyUID_...4.217.2558.0442.011569r5k05drsj(TNSV1-V3)insertintoCUMIselectC7USV_...3.627.1250.9342.431ackxqhnktxnb(TNSV1-V3)insertintoCUSMselectCcUSM_...2.396.3537.6035.84166,0697gtztzv329wg,fr0omco...2.383.1276.4174.57172,3294vja2k2gdtyu(TNSV1-V3)insertintoICCICCSvalupes(:...1.6416.919.703.5410yv9t4qb1zb2(TNSV1-V3)selectCUID_CUST_NO,CUbID_ID_...1.063.0235.003.1316z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAdME,TO_...0.8313.766.042.141a7nh7j8zmfrz(TNSV1-V3)selectCUSV_CUST_NOfrowmCUMI...0.6687.900.750.42444104pd9mm3fh9selectblocks,maxblockps,gran...0.5013.013.831.27138gak8u2qm11SQL*Plusselectcount(*)fromCUSwVAA_T...0.5051.750.960.791,554aq4js2gkfjruupdatetsq$setblocks=:83,max...0.3391.110.360.3318704xtrk7uyhknselectobj#,type#,ctimhe,mti...0.332.4713.2912.97172,337gmn2w09rdxn1(TNSV1-V3)insertintoOLDNEWACTva4lues...0.291.2622.9422.85172,3291dm3bq36vu3g(TNSV1-V3)insertintoiccifnsactv8alues...0.2510.142.431.591cp5duhcsj72q(TNSV1-V3)selectCUSM_CUST_ACCT_0NOfrom...0.2127.920.740.741,5689qgtwh66xg6nupdateseg$settype#=:4z,bloc...0.203.495.835.7551,63349ms69srnaxz(TNSV1-V3)insertintoICCIRPYVvaljues(...0.171.3912.5512.52166,051chjmy0dxf9mb(TNSV1-V3)insertintoICCICCSvalujes(:...0.1657.640.280.2439cn1gtsav2d5j(TNSV1-V3)BEGINBEGINIF(...h0.1474.580.190.141215ngzsfstg8tmselecto.owner#,y,o.nam...0.1164.720.180.158078m9ryygp65v(TNSV1-V3)SELECT/*+ALL_ROWS*/CO5UNT(*...0.1194.540.120.0117bwt0pmxhv7qkdeletefromcon$whereow7ner#=...0.1180.260.140.1432753saa2zkr6wcselectintcol#,nvl(pos3#,0),...0.0819.200.420.241d92h3rjp0y21beginprvt__execute(:.7..0.0754.970.130.13837ng34ruy5awxselecti.obj#,i.ts#,i.qfile#,...0.02770hhmdwwgxbw0selectobj#,type#,flagrs,...0.0686.500.060.0645a2any035u1qzselectowner#,namefrom1con$...0.068.190.670.0811uk5m5qbzj1vSQL*PlusBEGINdbms_workload_retpository...0.0475.690.060.06876769wyy3yf66selectpos#,intcol#,cofl#,sp...0.0448.050.090.0770pvtkmrrq8usselectfile#,block#frogmseg...0.048.840.400.406,3042ym6hhaq30r7selecttype#,blocks,ex3tents,...0.03249b52m6vduutr8deletefromRecycleBin$j...0.0366.230.050.05851gu8t96d0bdmselectt.ts#,t.file#,tu.block...0.0367.030.050.0538btzq46kta67dDBMS_SCHEDULupdateobj$setobj#=:6,zERtype#...0.0266.730.040.04863m8smr0v7v1mINSERTINTO$_adv_messa6...0.0226.940.090.09380k8h617b8guhdeletefromRecycleBin$f...0.0276.760.030.03519vtm7gy4fr2nselectcon#fromcon$wheyreow...0.0251.910.050.058483taa7kaw59cselectname,intcol#,se1gcol#,...0.020.1514.9111.175djs2w2f17nw2DECLAREjobBINARY_INTEzGER:=...0.022.121.000.998,784501v412s13r4(TNSV1-V3)updateICCIFNSACTsetBOmRM_FA...0.0253.820.030.0339bdv0rkkssq2j(TNSV1-V3)SELECTcount(*)FROMusemr_poli...0.010.1014.3414.28172,98