用户组www.xunlei520.info

上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/【性能调优】OracleAWR报告指标全解析byMaclean.
liuliu.
maclean@gmail.
comwww.
askmaclean.
com上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/HowtoFindSHOUG上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/【性能调优】OracleAWR报告指标全解析开Oracle调优鹰眼,深入理解AWR性能报告:http://www.
askmaclean.
com/archives/awr-hawk-eyes-training.
html开Oracle调优鹰眼,深入理解AWR性能报告第二讲:http://www.
askmaclean.
com/archives/awr-tuning-hawk-eyes.
html有同学在看过《Oracle调优鹰眼,深入理解AWR性能报告》的教学视频后急切期待第三讲,但实际是第三讲需要结合大量的原理知识才能充分理解例如Latchactivity、Undo、DynamicResourceMaster均需要理解其原理才能充分理解.
所以这些AWR的环节将在Maclean今后的系列调优讲座中介绍.
对于《Oracle调优鹰眼系列》则会增加本附录,作为对全部OracleAWR指标的介绍,本附录对于原理理解方面的内容将不多,而更侧重于指标含义的介绍,是对AWR鹰眼讲座的工具文档.
如果你觉得本AWR解析中的哪些指标仍理解不透彻或者讲的不清楚的,可以在本页中留言,谢谢大家的支持.
HawkEyes看AWR的鹰眼=基础理论夯实+看过500份以上AWR啥是AWRAWR(AutomaticWorkloadRepository)一堆历史性能数据,放在SYSAUX表空间上,AWR和SYSAUX都是10g出现的,是Oracle调优的关键特性;大约1999年左右开始开发,已经有15年历史默认快照间隔1小时,10g保存7天、11g保存8天;可以通过DBMS_WORKLOAD_REPOSITORY.
MODIFY_SNAPSHOT_SETTINGS修改DBA_HIST_WR_CONTROLAWR程序核心是dbms_workload_repository包@/rdbms/admin/awrrpt本实例@/rdbms/admin/awrrptiRAC中选择实例号谁维护AWR主要是MMON(ManageabilityMonitorProcess)和它的小工进程(m00x)MMON的功能包括:1.
启动slave进程m00x去做AWR快照2.
当某个度量阀值被超过时发出alert告警3.
为最近改变过的SQL对象捕获指标信息上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/AWR小技巧手动执行一个快照:Execdbms_workload_repository.
create_snapshot;(这个要背出来哦,用的时候去翻手册,丢脸哦J!
)创建一个AWR基线ExecDBMS_WORKLOAD_REPOSITORY.
CREATE_BASELINE(start_snap_id,end_snap_id,baseline_name);@/rdbms/admin/awrddrptAWR比对报告@/rdbms/admin/awrgrptRAC全局AWR自动生成AWRHTML报告:http://www.
oracle-base.
com/dba/10g/generate_multiple_awr_reports.
sql1、报告总结WORKLOADREPOSITORYreportforDBNameDBIdInstanceInstNumStartupTimeReleaseRACMAC2629627371askmaclean.
com122-Jan-1316:4911.
2.
0.
3.
0YESHostNamePlatformCPUsCoresSocketsMemory(GB)MAC10AIX-BasedSystems(64-bit)12832320.
00SnapIdSnapTimeSessionsCurs/SessBeginSnap:585323-Jan-1315:00:563,5201.
8EndSnap:585423-Jan-1315:30:413,7651.
9Elapsed:29.
75(mins)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/DBTime:7,633.
76(mins)Elapsed为该AWR性能报告的时间跨度(自然时间的跨度,例如前一个快照snapshot是4点生成的,后一个快照snapshot是6点生成的,则若使用@/rdbms/admin/awrrpt脚本中指定这2个快照的话,那么其elapsed=(6-4)=2个小时),一个AWR性能报告至少需要2个AWRsnapshot性能快照才能生成(注意这2个快照时间实例不能重启过,否则指定这2个快照生成AWR性能报告会报错),AWR性能报告中的指标往往是后一个快照和前一个快照的指标的delta,这是因为累计值并不能反映某段时间内的系统workload.
DBTIME=所有前台session花费在database调用上的总和时间:注意是前台进程foregroundsessions包括CPU时间、IOTime、和其他一系列非空闲等待时间,别忘了cpuonqueuetimeDBTIME不等于响应时间,DBTIME高了未必响应慢,DBTIME低了未必响应快DBTime描绘了数据库总体负载,但要和elapsedtime逝去时间结合其他来.
AverageActiveSessionAAS=DBtime/ElapsedTimeDBTime=60min,ElapsedTime=60minAAS=60/60=1负载一般DBTime=1min,ElapsedTime=60minAAS=1/60负载很轻DBTime=60000min,ElapsedTime=60minAAS=1000系统hang了吧DBTIME=DBCPU+Non-IdleWait+WaitonCPUqueue如果仅有2个逻辑CPU,而2个session在60分钟都没等待事件,一直跑在CPU上,那么:DBCPU=2*60mins,DBTime=2*60+0+0=120AAS=120/60=2正好等于OSload2.
如果有3个session都100%仅消耗CPU,那么总有一个要waitonqueueDBCPU=2*60mins,waitonCPUqueue=60minsAAS=(120+60)/60=3主机load亦为3,此时vmstat看waitingforruntime真实世界中DBCpu=xxmins,Non-IdleWait=enq:TX+cursorpinSonX+latch:xxx+dbfilesequentialread+……….
.
阿猫阿狗1-1内存参数大小CacheSizesBeginEndBufferCache:49,152M49,152MStdBlockSize:8KSharedPoolSize:13,312M13,312MLogBuffer:334,848K上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/内存管理方式:MSMM、ASMM(sga_target)、AMM(memory_target)小内存有小内存的问题,大内存有大内存的麻烦!
ORA-04031!
!
Buffercache和sharedpoolsize的begin/end值在ASMM、AMM和11gR2MSMM下可是会动的哦!
这里说sharedpool一直收缩,则在shrink过程中一些rowcache对象被lock住可能导致前台rowcachelock等解析等待,最好别让sharedpoolshrink.
如果这里sharedpool一直在grow,那说明sharedpool原有大小不足以满足需求(可能是大量硬解析),结合下文的解析信息和SGAbreakdown来一起诊断问题.
1-2LoadProfileLoadProfilePerSecondPerTransactionPerExecPerCallDBTime(s):256.
60.
20.
070.
03DBCPU(s):3.
70.
00.
000.
00Redosize:1,020,943.
0826.
5Logicalreads:196,888.
0159.
4Blockchanges:6,339.
45.
1Physicalreads:5,076.
74.
1Physicalwrites:379.
20.
3Usercalls:10,157.
48.
2Parses:204.
00.
2Hardparses:0.
90.
0W/AMBprocessed:5.
00.
0Logons:1.
70.
0Executes:3,936.
63.
2Rollbacks:1,126.
30.
9Transactions:1,235.
3上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/%BlockschangedperRead:53.
49RecursiveCall%:98.
04Rollbackpertransaction%:36.
57RowsperSort:73.
70指标指标含义redosize单位bytes,redosize可以用来估量update/insert/delete的频率,大的redosize往往对lgwr写日志,和arch归档造成I/O压力,PerTransaction可以用来分辨是大量小事务,还是少量大事务.
如上例每秒redo约1MB,每个事务800字节,符合OLTP特征LogicalRead单位次数*块数,相当于―人*次‖,如上例196,888*db_block_size=1538MB/s,逻辑读耗CPU,主频和CPU核数都很重要,逻辑读高则DBCPU往往高,也往往可以看到latch:cachebufferchains等待.
大量OLTP系统(例如siebel)可以高达几十乃至上百Gbytes.
Blockchanges单位次数*块数,描绘数据变化频率PhysicalRead单位次数*块数,如上例5076*8k=39MB/s,物理读消耗IO读,体现在IOPS和吞吐量等不同纬度上;但减少物理读可能意味着消耗更多CPU.
好的存储每秒物理读能力达到几GB,例如Exadata.
这个physicalread包含了physicalreadscache和physicalreadsdirectPhysicalwrites单位次数*块数,主要是DBWR写datafile,也有directpathwrite.
dbwr长期写出慢会导致定期logfileswitch(checkpointnocomplete)检查点无法完成的前台等待.
这个physicalwrite包含了physicalwritesdirect+physicalwritesfromcacheUserCalls单位次数,用户调用数,moredetailsfrominternalParses解析次数,包括软解析+硬解析,软解析优化得不好,则夸张地说几乎等于每秒SQL执行次数.
即执行解析比1:1,而我们希望的是解析一次到处运行哦!
HardParses万恶之源.
CursorpinsonX,librarycache:mutexX,latch:rowcacheobjects/sharedpool…硬解析最好少于每秒20次W/AMBpro-cessed单位MBW/Aworkareaworkarea中处理的数据数量结合In-memorySort%,sorts(disk)PGAAggr一起看Logons登陆次数,logonstorm登陆风暴,结合AUDIT审计数据一起看.
短连接的附带效应是游标缓存无用Executes执行次数,反应执行频率Rollback回滚次数,反应回滚频率,但是这个指标不太精确,参考而已,别太当真Transactions每秒事务数,是数据库层的TPS,可以看做压力测试或比对性能时的一个指标,孤立看无意义%BlockschangedperRead每次逻辑读导致数据块变化的比率;如果'redosize',blockchanges'pctofblockschangedperread'三个指标都很高,则说明系统正执行大量insert/update/delete;pctofblockschangedperread=(blockchanges)/(logicalreads)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/RecursiveCall%递归调用的比率;RecursiveCall%=(recursivecalls)/(usercalls)Rollbackpertransaction%事务回滚比率.
Rollbackpertransaction%=(rollback)/(transactions)RowsperSort平均每次排序涉及到的行数;RowsperSort=(sorts(rows))/(sorts(disk)+sorts(memory))注意这些LoadProfile负载指标在本环节提供了2个维度persecond和pertransaction.
perSecond:主要是把快照内的delta值除以快站时间的秒数,例如在A快照中V$SYSSTAT视图反应tablescans(longtables)这个指标是100,在B快照中V$SYSSTAT视图反应tablescans(longtables)这个指标是3700,而A快照和B快照之间间隔了一个小时3600秒,则对于tablescans(longtables)persecond就是(3700-100)/3600=1.
pertSecond是我们审视数据的主要维度,任何性能数据脱离了时间模型则毫无意义.
在statspack/AWR出现之前的调优洪荒时代,有很多DBA依赖V$SYSSTAT等视图中的累计统计信息来调优,以当前的调优眼光来看,那无异于刀耕火种.
pertransaction:基于事务的维度,与persecond相比是把除数从时间的秒数改为了该段时间内的事务数.
这个维度的很大用户是用来识别应用特性的变化,若2个AWR性能报告中该维度指标出现了大幅变化,例如redosize从本来pertransaction1k变化为10kpertransaction,则说明SQL业务逻辑肯定发生了某些变化.
注意AWR中的这些指标并不仅仅用来孤立地了解Oracle数据库负载情况,实施调优工作.
对于故障诊断例如HANG、Crash等,完全可以通过对比问题时段的性能报告和常规时间来对比,通过各项指标的对比往往可以找出病灶所在.
1-3InstanceEfficiencyPercentages(Target100%)InstanceEfficiencyPercentages(Target100%)BufferNowait%:99.
97RedoNoWait%:100.
00BufferHit%:97.
43In-memorySort%:100.
00LibraryHit%:99.
88SoftParse%:99.
58ExecutetoParse%:94.
82LatchHit%:99.
95ParseCPUtoParseElapsd%:1.
75%Non-ParseCPU:99.
85上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/上述所有指标的目标均为100%,即越大越好,在少数bug情况下可能超过100%或者为负值.
80%以上%Non-ParseCPU90%以上BufferHit%,In-memorySort%,SoftParse%95%以上LibraryHit%,RedoNowait%,BufferNowait%98%以上LatchHit%1、BufferNowait%session申请一个buffer(兼容模式)不等待的次数比例.
需要访问buffer时立即可以访问的比率,不兼容的情况在9i中是bufferbusywaits,从10g以后bufferbusywaits分离为bufferbusywait和readbyothersession2个等待事件:9i中waitstat的总次数基本等于bufferbusywaits等待事件的次数SQL>selectsum(TOTAL_WAITS)fromv$system_eventwhereevent='bufferbusywaits';SUM(TOTAL_WAITS)—————-33070394SQL>selectsum(count)fromv$waitstat;SUM(COUNT)———-3306933510gwaitstat的总次数基本等于bufferbusywaits和readbyothersession等待的次数总和SQL>selectsum(TOTAL_WAITS)fromv$system_eventwhereevent='bufferbusywaits'orevent='readbyothersession';SUM(TOTAL_WAITS)—————-上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/60675815SQL>selectsum(count)fromv$waitstat;SUM(COUNT)———-60423739BufferNowait%的计算公式是sum(v$waitstat.
wait_count)/(v$sysstatstatisticsessionlogicalreads),例如在AWR中:ClassWaitsTotalWaitTime(s)AvgTime(ms)datablock24,5432,26792undoheader74323undoblock1,116001stlevelbmb3500sessionlogicalreads40,769,80022,544.
84204.
71BufferNowait%:99.
94BufferNowait=(40,769,800–(24543+743+1116+35))/(40,769,800)=0.
99935=99.
94%SELECTSUM(WAIT_COUNT)FROMDBA_HIST_WAITSTATWHERESNAP_ID=:B3ANDDBID=:B2ANDINSTANCE_NUMBER=:B12、bufferHIT%:经典的经典,高速缓存命中率,反应物理读和缓存命中间的纠结,但这个指标即便99%也不能说明物理读等待少了不合理的db_cache_size,或者是SGA自动管理ASMM/Memory自动管理AMM下都可能因为db_cache_size过小引起大量的dbfilesequential/scatteredread等待事件;maclean曾经遇到过因为大量硬解析导致ASMM下sharedpool共享池大幅度膨胀,而dbcache相应缩小shrink的例子,最终dbcache收缩到只有几百兆,本来没有的物理读等待事件都大幅涌现出来.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/此外与bufferHIT%相关的指标值得关注的还有tablescans(longtables)大表扫描这个统计项目、此外相关的栏目还有BufferPoolStatistics、BufferPoolAdvisory等(如果不知道在哪里,直接找一个AWR去搜索这些关键词即可).
bufferHIT%在不同版本有多个计算公式:在9i中BufferHitRatio=1–((physicalreads–physicalreadsdirect–physicalreadsdirect(lob))/(dbblockgets+consistentgets–physicalreadsdirect–physicalreadsdirect(lob))在10g以后:BufferHitRatio=1–((physicalreadscache')/(consistentgetsfromcache'+dbblockgetsfromcache')注意:但是实际AWR中似乎还是按照9i中的算法,虽然算法的区别对最后算得的比率影响不大.
对于bufferhit%看它的命中率有多高没有意义,主要是关注未命中的次数有多少.
通过上述公式很容易反推出未命中的物理读的次数.
dbblockgets、consistentgets以及sessionlogicalreads的关系如下:dbblockgets=dbblockgetsdirect+dbblockgetsfromcacheconsistentgets=consistentgetsfromcache+consistentgetsdirectconsistentgetsfromcache=consistentgets–examination+elseconsistentgets–examination==>指的是不需要pinbuffer直接可以执行consistentget的次数,常用于索引,只需要一次latchgetsessionlogicalreads=dbblockgets+consistentgets其中physicalreads、physicalreadscache、physicalreadsdirect、physicalreadsdirect(lob)几者的关系为:physicalreads=physicalreadscache+physicalreadsdirect这个公式其实说明了物理读有2种:物理读进入buffercache中,是常见的模式physicalreadscache物理读直接进入PGA直接路径读,即physicalreadsdirectphysicalreads8Totalnumberofdatablocksreadfromdisk.
Thisvaluecanbegreaterthanthevalueof―physicalreadsdirect‖plus―physicalreadscache‖asreadsintoprocessprivatebuffersalsoincludedinthisstatistic.
physicalreadscache8Totalnumberofdatablocksreadfromdiskintothebuffercache.
Thisisasubsetof―physicalreads‖statistic.
physicalreadsdirect8Numberofreadsdirectlyfromdisk,bypassingthebuffercache.
Forexample,inhighbandwidth,data-intensiveoperationssuchasparallelquery,readsofdiskblocksbypassthebuffercachetomaximizetransferratesandtopreventtheprematureagingofshareddatablocksresidentinthebuffercache.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/physicalreadsdirect=physicalreadsdirect(lob)+physicalreadsdirecttemporarytablespace+physicalreadsdirect(普通)这个公式也说明了直接路径读分成三个部分:physicalreadsdirect(lob)直接路径读LOB对象physicalreadsdirecttemporarytablespace直接路径读临时表空间physicalreaddirect(普通)普通的直接路径读,一般是11g开始的自动的大表directpathread和并行引起的directpathreadphysicalwritesdirect=physicalwritesdirect(lob)+physicalwritesdirecttemporarytablespaceDBWRcheckpointbufferswritten=DBWRthreadcheckpointbufferswritten+DBWRtablespacecheckpointbufferswritten+DBWRPQtablespacecheckpointbufferswritten+….
3、Redonowait%:session在生成redoentry时不用等待的比例,redo相关的资源争用例如redospacerequest争用可能造成生成redo时需求等待.
此项数据来源于v$sysstat中的(redologspacerequests/redoentries).
一般来说10g以后不太用关注log_buffer参数的大小,需要关注是否有十分频繁的logswitch;过小的redologfilesize如果配合较大的SGA和频繁的commit提交都可能造成该问题.
考虑增到redologfile的尺寸:1~4G每个,7~10组都是合适的.
同时考虑优化redologfile和datafile的I/O.
4、In-memorySort%:这个指标因为它不计算workarea中所有的操作类型,所以现在越来越鸡肋了.
纯粹在内存中完成的排序比例.
数据来源于v$sysstatstatisticssorts(disk)和sorts(memory),In-memorySort%=sort(memory)/(sort(disk)+sort(memory))5、LibraryHit%:librarycache命中率,申请一个librarycacheobject例如一个SQLcursor时,其已经在librarycache中的比例.
数据来源V$librarycache的pins和pinhits.
合理值:>95%,该比例来源于1-(Σ(pinRequests*PctMiss)/Sum(PinRequests))维护这个指标的重点是保持sharedpool共享池有足够的FreeMemory,且没有过多的内存碎片,具体可以参考这里.
显然过小的sharedpool可用空间会导致librarycacheobject被agedout换出共享池.
此外保证SQL语句绑定变量和游标可以共享也是很重要的因素.
LibraryCacheActivityDB/Inst:G10R25/G10R25Snaps:2964-2965->"PctMisses"shouldbeverylowhttp://www.
askmaclean.
comGetPctPinPctInvali-NamespaceRequestsMissRequestsMissReloadsdations上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/BODY50.
0616.
710CLUSTER100.
0260.
000SQLAREA601,35799.
8902,82899.
7472TABLE/PROCEDURE839.
6601,4430.
0480GETSNUMBERNumberoftimesalockwasrequestedforobjectsofthisnamespaceGETHITSNUMBERNumberoftimesanobject'shandlewasfoundinmemoryGETHITRATIONUMBERRatioofGETHITStoGETSPINSNUMBERNumberoftimesaPINwasrequestedforobjectsofthisnamespacePINHITSNUMBERNumberoftimesallofthemetadatapiecesofthelibraryobjectwerefoundinmemoryPINHITRATIONUMBERRatioofPINHITStoPINSRELOADSNUMBERAnyPINofanobjectthatisnotthefirstPINperformedsincetheobjecthandlewascreat-ed,andwhichrequiresloadingtheobjectfromdiskINVALIDATIONSNUMBERTotalnumberoftimesobjectsinthisnamespaceweremarkedinvalidbecauseadependentobjectwasmodifiedSELECTSUM(PINS),SUM(PINHITS)FROMDBA_HIST_LIBRARYCACHEWHERESNAP_ID=:B3ANDDBID=:B2ANDINSTANCE_NUMBER=:B16、SoftParse:软解析比例,无需多说的经典指标,数据来源v$sysstatstatistics的parsecount(total)和parsecount(hard).
合理值>95%SoftParse%是AWR中另一个重要的解析指标,该指标反应了快照时间内软解析次数和总解析次数(soft+hard软解析次数+硬解析次数)的比值,若该指标很低,那么说明了可能存在剧烈的hardparse硬解析,大量的硬解析会消耗更多的CPU时间片并产生解析争用(此时可以考虑使用cursor_sharing=FORCE);理论上我们总是希望SoftParse%接近于100%,但并不是说100%的软解析就是最理想的解析状态,通过设置session_cached_cursors参数和反复重用游标我们可以让解析来的更轻量级,即通俗所说的利用会话缓存游标实现的软软解析(softsoftparse).
7、ExecutetoParse%指标反映了执行解析比其公式为1-(parse/execute),目标为100%及接近于只执行而不解析.
数据来源v$sysstatstatisticsparsecount(total)和executecount在oracle中解析往往是执行的先提工作,但是通过游标共享可以解析一次执行多次,执行解析可能分成多种场景:1.
hardcoding=>硬编码代码硬解析一次,执行一次,则理论上其执行解析比为1:1,则理论上ExecutetoParse=0极差,且softparse比例也为0%上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/2.
绑定变量但是仍软解析=》软解析一次,执行一次,这种情况虽然比前一种好但是执行解析比(这里的parse,包含了软解析和硬解析)仍是1:1,理论上ExecutetoParse=0极差,但是softparse比例可能很高3.
使用静态SQL、动态绑定、session_cached_cursor、opencursors等技术实现的解析一次,执行多次,执行解析比为N:1,则ExecutetoParse=1-(1/N)执行次数越多ExecutetoParse越接近100%,这种是我们在OLTP环境中喜闻乐见的!
通俗地说softparse%反映了软解析率,而软解析在oracle中仍是较昂贵的操作,我们希望的是解析1次执行N次,如果每次执行均需要软解析,那么虽然softparse%=100%但是parsetime仍可能是消耗DBTIME的大头.
ExecutetoParse反映了执行解析比,ExecutetoParse和softparse%都很低那么说明确实没有绑定变量,而如果softparse%接近99%而ExecutetoParse不足90%则说明没有执行解析比低,需要通过静态SQL、动态绑定、session_cached_cursor、opencursors等技术减少软解析.
8、LatchHit%:willing-to-waitlatch闩申请不要等待的比例.
数据来源V$latchgets和missesLatchNameGetRequestsMissesSleepsSpinGetsSleep1Sleep2Sleep3sharedpool9,988,63736423341000librarycache6,753,4681526146000MemoryManagementLatch369110000qmntaskqueuelatch24110000LatchHit%:=(1–(Sum(misses)/Sum(gets)))关于Latch的更多信息内容可以参考AWR后面的专栏LatchStatistics,注意对于一个并发设计良好的OLTP应用来说,Latch、Enqueue等并发控制不应当成为系统的主要瓶颈,同时对于这些并发争用而言堆积硬件CPU和内存很难有效改善性能.
SELECTSUM(GETS),SUM(MISSES)FROMDBA_HIST_LATCHWHERESNAP_ID=:B3ANDDBID=:B2ANDINSTANCE_NUMBER=:B19、上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/ParseCPUToParseElapsd:该指标反映了快照内解析CPU时间和总的解析时间的比值(ParseCPUTime/ParseElapsedTime);若该指标水平很低,那么说明在整个解析过程中实际在CPU上运算的时间是很短的,而主要的解析时间都耗费在各种其他非空闲的等待事件上了(如latch:sharedpool,rowcachelock之类等)数据来源V$sysstat的parsetimecpu和parsetimeelapsed10、%Non-ParseCPU非解析cpu比例,公式为(DBCPU–ParseCPU)/DBCPU,若大多数CPU都用在解析上了,则可能好钢没用在刃上了.
数据来源v$sysstat的parsetimecpu和cpuusedbythissession1-4SharedPoolStatisticsSharedPoolStatisticsBeginEndMemoryUsage%:84.
6479.
67%SQLwithexecutions>1:93.
7724.
69%MemoryforSQLw/exec>1:85.
3634.
8该环节提供一个大致的SQL重用及sharedpool内存使用的评估.
应用是否共享SQL有多少内存是给只运行一次的SQL占掉的,对比共享SQL呢如果该环节中%SQLwithexecutions>1的比例小于%90,考虑用下面链接的SQL去抓硬编码的非绑定变量SQL语句.
利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQLMemoryUsage%:(sharedpool的实时大小-sharedpoolfreememory)/sharedpool的实时大小,代表sharedpool的空间使用率,虽然有使用率但没有标明碎片程度%SQLwithexecutions>1复用的SQL占总的SQL语句的比率,数据来源DBA_HIST_SQL_SUMMARY的SINGLE_USE_SQL和TOTAL_SQL:1–SINGLE_USE_SQL/TOTAL_SQL上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/%MemoryforSQLw/exec>1执行2次以上的SQL所占内存占总的SQL内存的比率,数据来源DBA_HIST_SQL_SUMMARY的SINGLE_USE_SQL_MEM和TOTAL_SQL_MEM:1–SIN-GLE_USE_SQL_MEM/TOTAL_SQL_MEM==》上面2个指标也可以用来大致了解sharedpool中的内存碎片程序,因为SINGLE_USE_SQL单次执行的SQL多的话,那么显然可能有较多的共享池内存碎片SQL复用率低的原因一般来说就是硬绑定变量(hardCoding)未合理使用绑定变量(bindvariable),对于这种现象短期无法修改代表使用绑定变量的可以ALTERSYSTEMSETCURSOR_SHARING=FORCE;来绕过问题,对于长期来看还是要修改代码绑定变量.
Oracle从11g开始宣称今后将废弃CURSOR_SHARING的SIMILAR选项,同时SIMILAR选项本身也造成了很多问题,所以一律不推荐用CURSOR_SHARING=SIMILAR.
如果memoryusage%比率一直很高,则可以关注下后面sgabreakdown中的sharedpoolfreememory大小,一般推荐至少让freememroy有个300~500MB以避免隐患.
1-5Top5TimedEventsTop5TimedEventsAvg%TotalwaitCallEventWaitsTime(s)(ms)TimeWaitClassgcbufferbusy79,08373,02492365.
4Clusterenq:TX-rowlockcontention35,06817,12348815.
3ApplicatioCPUtime12,20510.
9gccurrentrequest2,7143,31512213.
0Clustergccrmultiblockrequest83,6661,008120.
9Cluster基于WaitInterface的调优是目前的主流!
每个指标都重要!
基于命中比例的调优,好比是统计局的报告,张财主家财产100万,李木匠家财产1万,平均财产50.
5万.
基于等待事件的调优,好比马路上100辆汽车的行驶记录表,上车用了几分钟,红灯等了几分钟,拥堵塞了几分钟.
.
.
丰富的等待事件以足够的细节来描绘系统运行的性能瓶颈,这是Mysql梦寐以求的东西……Waits:该等待事件发生的次数,对于DBCPU此项不可用上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/Times:该等待事件消耗的总计时间,单位为秒,对于DBCPU而言是前台进程所消耗CPU时间片的总和,但不包括WaitonCPUQUEUEAvgWait(ms):该等待事件平均等待的时间,实际就是Times/Waits,单位ms,对于DBCPU此项不可用%TotalCallTime,该等待事件占总的calltime的比率totalcalltime=totalCPUtime+totalwaittimefornon-idleevents%TotalCallTime=timeforeachtimedevent/totalcalltimeWaitClass:等待类型:Concurrency,SystemI/O,UserI/O,Administrative,Other,Configuration,Scheduler,Cluster,Application,Idle,Network,CommitCPU上在干什么逻辑读解析LatchspinPL/SQL、函数运算DBCPU/CPUtime是Top1是好事情吗未必!
注意DBCPU不包含waitoncpuqueue!
SELECTe.
event_nameevent,e.
total_waits-NVL(b.
total_waits,0)waits,DECODE(e.
total_waits-NVL(b.
total_waits,0),0,TO_NUMBER(NULL),DECODE(e.
total_timeouts-NVL(b.
total_timeouts,0),0,TO_NUMBER(NULL),100*(e.
total_timeouts-NVL(b.
total_timeouts,0))/(e.
total_waits-NVL(b.
total_waits,0))))pctto,(e.
time_waited_micro-NVL(b.
time_waited_micro,0))/1000000time,DECODE((e.
total_waits-NVL(b.
total_waits,0)),0,TO_NUMBER(NULL),上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/((e.
time_waited_micro-NVL(b.
time_waited_micro,0))/1000)/(e.
total_waits-NVL(b.
total_waits,0)))avgwt,DECODE(e.
wait_class,'Idle',99,0)idleFROMdba_hist_system_eventb,dba_hist_system_eventeWHEREb.
snap_id(+)=&bidANDe.
snap_id=&eid--ANDb.
dbid(+)=:dbid--ANDe.
dbid=:dbidANDb.
instance_number(+)=1ANDe.
instance_number=1ANDb.
event_id(+)=e.
event_idANDe.
total_waits>NVL(b.
total_waits,0)ANDe.
event_nameNOTIN('smontimer','pmontimer','dispatchertimer','dispatcherlistentimer','rdbmsipcmessage')ORDERBYidle,timeDESC,waitsDESC,event几种常见的等待事件上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/dbfilescatteredread,Avgwaittime应当小于20ms如果数据库执行全表扫描或者是全索引扫描会执行MultiblockI/O,此时等待物理I/O结束会出现此等待事件.
一般会从应用程序(SQL),I/O方面入手调整;注意和《InstanceActivityStats》中的indexfastfullscans(full)以及tablescans(longtables)集合起来一起看.
dbfilesequentialread,该等待事件Avgwaittime平均单次等待时间应当小于20ms‖dbfilesequentialread‖单块读等待是一种最为常见的物理IO等待事件,这里的sequential指的是将数据块读入到相连的内存空间中(contiguousmemoryspace),而不是指所读取的数据块是连续的.
该waitevent可能在以下情景中发生:http://www.
askmaclean.
com/archives/db-file-sequential-read-wait-event.
htmllatchfree其实是未获得latch,而进入latchsleep,见《全面解析9i以后OracleLatch闩锁原理》enq:XX队列锁等待,视乎不同的队列锁有不同的情况:你有多了解OracleEnqueuelock队列锁机制Oracle队列锁:EnqueueHWOracle队列锁enq:US,UndoSegmentenq:TX–rowlock/indexcontention、allocateITL等待事件enq:TT–contention等待事件Oracle队列锁enq:TS,TemporarySegment(alsoTableSpace)enq:JI–contention等待事件enq:US–contention等待事件enq:TM–contention等待事件enq:ROfastobjectreuse等待事件enq:HW–contention等待事件freebufferwaits:是由于无法找到可用的buffercache空闲区域,需要等待DBWR写入完成引起一般是由于低效的sql过小的buffercacheDBWR工作负荷过量bufferbusywait/readbyothersession一般以上2个等待事件可以归为一起处理,建议客户都进行监控.
以上等待时间可以由如下操作引起select/select—-readbyothersession:由于需要从数据文件中将数据块读入buffercache中引起,有可能是大量的逻辑/物理读;或者过小的buffercache引起select/update—-bufferbusywaits/readbyothersession是由于更新某数据块后需要在undo中重建构建过去时间的块,有可能伴生enq:cr-contention是由于大量的物理读/逻辑读造成.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/update/update—-bufferbusywaits由于更新同一个数据块(非同一行,同一行是enq:TX-contention)此类问题是热点块造成insert/insert—-bufferbusywaits是由于freelist争用造成,可以将表空间更改为ASSM管理或者加大freelist.
writecompletewaits:一般此类等待事件是由于DBWR将脏数据写入数据文件,其他进程如果需要修改buffercache会引起此等待事件,一般是I/O性能问题或者是DBWR工作负荷过量引起Waittime1Seconds.
controlfileparallelwrite:频繁的更新控制文件会造成大量此类等待事件,如日志频繁切换,检查点经常发生,nologging引起频繁的数据文件更改,I/O系统性能缓慢.
logfilesync:一般此类等待时间是由于LGWR进程讲redologbuffer写入redolog中发生.
如果此类事件频繁发生,可以判断为:commit次数是否过多I/O系统问题重做日志是否不必要被创建redologbuffer是否过大2-1TimeModelStatisticsTimeModelStatisticsDB/Inst:ITSCMP/itscmp2Snaps:70719-70723->Totaltimeindatabaseuser-calls(DBTime):883542.
2s->Statisticsincludingtheword"background"measurebackgroundprocesstime,andsodonotcontributetotheDBtimestatistic->Orderedby%orDBtimedesc,StatisticnameStatisticNameTime(s)%ofDBTimesqlexecuteelapsedtime805,159.
791.
1sequenceloadelapsedtime41,159.
24.
7DBCPU20,649.
12.
3上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/parsetimeelapsed1,112.
8.
1hardparseelapsedtime995.
2.
1hardparse(sharingcriteria)elapsedtime237.
3.
0hardparse(bindmismatch)elapsedtime227.
6.
0connectionmanagementcallelapsedtime29.
7.
0PL/SQLexecutionelapsedtime9.
2.
0PL/SQLcompilationelapsedtime6.
6.
0failedparseelapsedtime2.
0.
0repeatedbindelapsedtime0.
4.
0DBtime883,542.
2backgroundelapsedtime25,439.
0backgroundcputime1,980.
9TimeModelStatistics几个特别有用的时间指标:parsetimeelapsed、hardparseelapsedtime结合起来看解析是否是主要矛盾,若是则重点是软解析还是硬解析sequenceloadelapsedtimesequence序列争用是否是问题焦点PL/SQLcompilationelapsedtimePL/SQL对象编译的耗时注意PL/SQLexecutionelapsedtime纯耗费在PL/SQL解释器上的时间.
不包括花在执行和解析其包含SQL上的时间connectionmanagementcallelapsedtime建立数据库session连接和断开的耗时failedparseelapsedtime解析失败,例如由于ORA-4031hardparse(sharingcriteria)elapsedtime由于无法共享游标造成的硬解析hardparse(bindmismatch)elapsedtime由于bindtypeorbindsize不一致造成的硬解析注意该时间模型中的指标存在包含关系所以TimeModelStatistics加起来超过100%再正常不过1)backgroundelapsedtime2)backgroundcputime3)RMANcputime(backup/restore)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/1)DBtime2)DBCPU2)connectionmanagementcallelapsedtime2)sequenceloadelapsedtime2)sqlexecuteelapsedtime2)parsetimeelapsed3)hardparseelapsedtime4)hardparse(sharingcriteria)elapsedtime5)hardparse(bindmismatch)elapsedtime3)failedparseelapsedtime4)failedparse(outofsharedmemory)elapsedtime2)PL/SQLexecutionelapsedtime2)inboundPL/SQLrpcelapsedtime2)PL/SQLcompilationelapsedtime2)Javaexecutionelapsedtime2)repeatedbindelapsedtime2-2ForegroundWaitClassForegroundWaitClass->s-second,ms-millisecond-1000thofasecond->orderedbywaittimedesc,waitsdesc->%Timeouts:valueof0indicatesvaluewasCapturedTimeaccountsfor102.
7%ofTotalDBtime883,542.
21(s)->TotalFGWaitTime:886,957.
73(s)DBCPUtime:20,649.
06(s)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/Avg%TimeTotalWaitwaitWaitClassWaits-outsTime(s)(ms)%DBtimeCluster9,825,8841525,1345359.
4Concurrency688,3750113,78216512.
9UserI/O34,405,042076,69528.
7Commit172,193062,7763657.
1Application11,422057,76050576.
5Configuration19,418148,88925185.
5DBCPU20,6492.
3Other1,757,8969492410.
1SystemI/O30,1650598200.
1Network171,955,673040000.
0Administrative210001010.
0selectdistinctwait_classfromv$event_name;WAIT_CLASSConcurrencyUserI/OSystemI/OAdministrative上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/OtherConfigurationSchedulerClusterApplicationQueueingIdleNetworkCommitWaitClass:等待事件的类型,如上查询所示,被分作12个类型.
10.
2.
0.
5有916个等待事件,其中Other类型占622个.
Waits:该类型所属等待事件在快照时间内的等待次数%TimeOut等待超时的比率,未超时次数/waits*100(%)TotalWaitTime:该类型所属等待事件总的耗时,单位为秒AvgWait(ms):该类型所属等待事件的平均单次等待时间,单位为ms,实际这个指标对commit和useri/o以及systemi/o类型有点意义,其他等待类型由于等待事件差异较大所以看平均值的意义较小waits/txn:该类型所属等待事件的等待次数和事务比Other类型,遇到该类型等待事件的话常见的原因是OracleBug或者网络、I/O存在问题,一般推荐联系Maclean.
Concurrency类型并行争用类型的等待事件,典型的如latch:sharedpool、latch:librarycache、rowcachelock、librarycachepin/lockCluster类型为RealApplicationClusterRAC环境中的等待事件,需要注意的是如果启用了RACoption,那么即使你的集群中只启动了一个实例,那么该实例也可能遇到Cluster类型的等待事件,例如gcbufferbusySystemI/O主要是后台进程维护数据库所产生的I/O,例如controlfileparallelwrite、logfileparallelwrite、dbfileparallelwrite.
UserI/O主要是前台进程做了一些I/O操作,并不是说后台进程不会有这些等待事件.
典型的如dbfilese-quential/scatteredread、directpathreadConfiguration由于配置引起的等待事件,例如日志切换的logfileswitchcompletion(日志文件大小/数目不够),sequence的enq:SQ–contention(Sequence使用nocache);Oracle认为它们是由于配置不当引起的,但实际未必真是这样的配置引起的.
Application应用造成的等待事件,例如enq:TM–contention和enq:TX–rowlockcontention;Oracle认为这是由于应用设计不当造成的等待事件,但实际这些Applicationclass等待可能受到Concurrency、Cluster、SystemI/O、UserI/O等多种类型等待的影响,例如本来commit只要1ms,则某一行数据仅被锁定1ms,但由于commit变慢从而释放行锁变慢,引发大量的enq:TX–rowlockcontention等待事件.
Commit仅logfilesync,logfilesync的影响十分广泛,值得我们深入讨论.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/Network:网络类型的等待事件例如SQL*Netmoredatatoclient、SQL*NetmoredatatodblinkIdle空闲等待事件,最为常见的是rdbmsipcmessage(等待实例内部的ipc通信才干活,即别人告知我有活干,我才干,否则我休息==》Idle),SQL*Netmessagefromclient(等待SQL*NET传来信息,否则目前没事干)2-3前台等待事件ForegroundWaitEventsSnaps:70719-70723->s-second,ms-millisecond-1000thofasecond->OnlyeventswithTotalWaitTime(s)>=.
001areshown->orderedbywaittimedesc,waitsdesc(idleeventslast)->%Timeouts:valueof0indicatesvaluewasorderedbywaittimedesc,waitsdesc(idleeventslast)->OnlyeventswithTotalWaitTime(s)>=.
001areshown->%Timeouts:valueof0indicatesvaluewasorderedbystatistictype(CPUUse,VirtualMemory,HardwareConfig),NameStatisticValueEndValue上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/BUSY_TIME2,894,855IDLE_TIME5,568,240IOWAIT_TIME18,973SYS_TIME602,532USER_TIME2,090,082LOAD813VM_IN_BYTES0VM_OUT_BYTES0PHYSICAL_MEMORY_BYTES101,221,343,232NUM_CPUS24NUM_CPU_CORES12NUM_CPU_SOCKETS2GLOBAL_RECEIVE_SIZE_MAX4,194,304GLOBAL_SEND_SIZE_MAX2,097,152TCP_RECEIVE_SIZE_DEFAULT87,380TCP_RECEIVE_SIZE_MAX4,194,304TCP_RECEIVE_SIZE_MIN4,096TCP_SEND_SIZE_DEFAULT16,384TCP_SEND_SIZE_MAX4,194,304TCP_SEND_SIZE_MIN4,096OperatingSystemStatistics操作系统统计信息数据来源于V$OSSTAT/DBA_HIST_OSSTAT,,TIME相关的指标单位均为百分之一秒统计项描述NUM_CPU_SOCKETS物理CPU的数目NUM_CPU_CORESCPU的核数上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/NUM_CPUS逻辑CPU的数目SYS_TIME在内核态被消耗掉的CPU时间片,单位为百分之一秒USER_TIME在用户态被消耗掉的CPU时间片,单位为百分之一秒BUSY_TIMEBusy_Time=SYS_TIME+USER_TIME消耗的CPU时间片,单位为百分之一秒AVG_BUSY_TIMEAVG_BUSY_TIME=BUSY_TIME/NUM_CPUSIDLE_TIME空闲的CPU时间片,单位为百分之一秒所有CPU所能提供总的时间片BUSY_TIME+IDLE_TIME=ELAPSED_TIME*CPU_COUNTOS_CPU_WAIT_TIME进程等OS调度的时间,cpuqueuingVM_IN_BYTES换入页的字节数VM_OUT_BYTES换出页的字节数,部分版本下并不准确,例如Bug11712010Ab-stract:VIRTUALMEMORYPAGINGON11.
2.
0.
2DATABASES,仅供参考IOWAIT_TIME所有CPU花费在等待I/O完成上的时间单位为百分之一秒RSRC_MGR_CPU_WAIT_TIME是指当resourcemanager控制CPU调度时,需要控制对应进程暂时不使用CPU而进程到内部运行队列中,以保证该进程对应的consumergroup(消费组)没有消耗比指定resourcemanager指令更多的CPU.
RSRC_MGR_CPU_WAIT_TIME指等在内部运行队列上的时间,在等待时不消耗CPU2-6ServiceStatistcsServiceStatisticsSnaps:70719-70723->orderedbyDBTimePhysicalLogicalServiceNameDBTime(s)DBCPU(s)Reads(K)Reads(K)itms-contentmasterdb-prod897,09920,61835,6681,958,580SYS$USERS4,3121895,95713,333itmscmp1,94112114,94918,187itscmp33120114218上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/itscmp_dgmgrl121100SYS$BACKGROUND0014230,022ITSCMP1_PR0000its-reference-prod0000itscmpXDB0000按照ServiceName来分组时间模型和物理、逻辑读取,部分数据来源于WRH$_SERVICE_NAME;ServiceName对应的服务名(v$services),SYS$BACKGROUND代表后台进程,SYS$USERS一般是系统用户登录DBTIME(s):本服务名所消耗的DBTIME时间,单位为秒DBCPU(s):本服务名所消耗的DBCPU时间,单位为秒PhysicalReads:本服务名所消耗的物理读LogicalReads:本服务所消耗的逻辑读2-7ServiceWaitClassStatsServiceWaitClassStatsSnaps:70719-70723->WaitClassinfoforservicesintheServiceStatisticssection.
->TotalWaitsandTimeWaiteddisplayedforthefollowingwaitclasses:UserI/O,Concurrency,Administrative,Network->TimeWaited(WtTime)insecondsServiceNameUserI/OUserI/OConcurcyConcurcyAdminAdminNetworkNetworkTotalWtsWtTimeTotalWtsWtTimeTotalWtsWtTimeTotalWtsWtTimeitms-contentmasterdb-prod上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/3332167071443678373113759001.
718E+08127SYS$USERS173233365667383020726743itmscmp6767731319183100022160itscmp2195772361093000181120itscmp_dgmgrl340800090SYS$BACKGROUND7194013003206775600442252872UserI/OTotalWts:对应该服务名下用户I/O类等待的总的次数UserI/OWtTime:对应该服务名下用户I/O累等待的总时间,单位为1/100秒ConcurcyTotalWts:对应该服务名下Concurrency类型等待的总次数ConcurcyWtTime:对应该服务名下Concurrency类型等待的总时间,单位为1/100秒AdminTotalWts:对应该服务名下Admin类等待的总次数AdminWtTime:对应该服务名下Admin类等待的总时间,单位为1/100秒NetworkTotalWts:对应服务名下Network类等待的总次数NetworkWtTime:对应服务名下Network类等待的总事件,单位为1/100秒2-8HostCPUHostCPU(CPUs:24Cores:12Sockets:2)LoadAverageBeginEnd%User%System%WIO%Idle8.
4112.
8424.
77.
10.
265.
8上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/―LoadAverage‖begin/end值代表每个CPU的大致运行队列大小.
上例中快照开始到结束,平均CPU负载增加了;与《2-5OperatingSystemStatistics》中的LOAD相呼应.
%User+%System=>总的CPU使用率,在这里是31.
8%ElapsedTime*NUM_CPUS*CPUutilization=60.
23(mins)*24*31.
8%=459.
67536mins=BusyTime2-8InstanceCPUInstanceCPU%oftotalCPUforInstance:26.
7%ofbusyCPUforInstance:78.
2%DBtimewaitingforCPU-ResourceMgr:0.
0%TotalCPU,该实例所使用的CPU占总CPU的比例%oftotalCPUforInstance%BusyCPU,该实例所使用的Cpu占总的被使用CPU的比例%ofbusyCPUforInstance例如共4个逻辑CPU,其中3个被完全使用,3个中的1个完全被该实例使用,则%TotalCPU==25%,而%BusyCPU=1/3=33%当CPU高时一般看%BusyCPU可以确定CPU到底是否是本实例消耗的,还是主机上其他程序%ofbusyCPUforInstance=(DBCPU+backgroundcputime)/(BUSY_TIME/100)=(20,649.
1+1,980.
9)/(2,894,855/100)=78.
17%%ofTotalCPUforInstance=(DBCPU+backgroundcputime)/(BUSY_TIME+IDLE_TIME/100)=(20,649.
1+1,980.
9)/((2,894,855+5,568,240)/100)=26.
73%%DBtimewaitingforCPU(ResourceManager)=(RSRC_MGR_CPU_WAIT_TIME/100)/DBTIME3TOPSQLTOPSQL的数据部分来源于dba_hist_sqlstat3-1SQLorderedbyElapsedTime,按照SQL消耗的时间来排列TOPSQLSQLorderedbyElapsedTimeSnaps:70719-70723->ResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.
->%TotalDBTimeistheElapsedTimeoftheSQLstatementdivided上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/intotheTotalDatabaseTimemultipliedby100->%Total-ElapsedTimeasapercentageofTotalDBtime->%CPU-CPUTimeasapercentageofElapsedTime->%IO-UserI/OTimeasapercentageofElapsedTime->CapturedSQLaccountfor53.
9%ofTotalDBTime(s):883,542->CapturedPL/SQLaccountfor0.
5%ofTotalDBTime(s):883,542ElapsedElapsedTimeTime(s)ExecutionsperExec(s)%Total%CPU%IOSQLId181,411.
338,8484.
6720.
5.
0.
1g0yc9szpuu068注意对于PL/SQL,SQLStatistics不仅会体现该PL/SQL的执行情况,还会包括该PL/SQL包含的SQL语句的情况.
如上例一个TOPPL/SQL执行了448s,而这448s中绝大多数是这个PL/SQL下的一个SQL执行500次耗费的.
则该TOPPL/SQL和TOPSQL都上榜,一个执行一次耗时448s,一个执行500次耗时448s.
如此情况则ElapsedTime加起来可能超过100%的ElapsedTime,这是正常的.
对于鹤立鸡群的SQL很有必要一探究竟,跑个@/rdbms/admin/awrsqrpt看看吧!
ElapsedTime(s):该SQL累计运行所消耗的时间,Executions:该SQL在快照时间内总计运行的次数;注意,对于在快照时间内还没有执行完的SQL不计为1一次,所以如果看到executions=0而又是TOPSQL,则很有可能是因为该SQL运行较旧还没执行完,需要特别关注一下.
ElapsedTimeperExec(s):平均每次执行该SQL耗费的时间,对于OLTP类型的SELECT/INSERT/UPDATE/DELETE而言平均单次执行时间应当非常短,如0.
1秒或者更短才能满足其业务需求,如果这类轻微的OLTP操作单次也要几秒钟的话,是无法满足对外业务的需求的;例如你在ATM上提款,并不仅仅是对你的账务库的简单UPDATE,而需要在类似风险控制的前置系统中记录你本次的流水操作记录,实际取一次钱可能要有几十乃至上百个OLTP类型的语句被执行,但它们应当都是十分快速的操作;如果这些操作也变得很慢,则会出现大量事务阻塞,系统负载升高,DBTIME急剧上升的现象.
对于OLTP数据库而言如果执行计划稳定,那么这些OLTP操作的性能应当是铁板钉钉的,但是一旦某个因素发生变化,例如存储的明显变慢、内存换页的大量出现时则上述的这些transaction操作很可能成数倍到几十倍的变慢,这将让此事务系统短期内不可用.
对于维护操作,例如加载或清除数据,大的跑批次、报表而言ElapsedTimeperExec(s)高一些是正常的.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/%Total该SQL所消耗的时间占总的DBTime的百分比,即(SQLElapsedTime/TotalDBTIME)%CPU该SQL所消耗的CPU时间占该SQL消耗的时间里的比例,即(SQLCPUTime/SQLElapsedTime),该指标说明了该语句是否是CPU敏感的%IO该SQL所消耗的I/O时间占该SQL消耗的时间里的比例,即(SQLI/OTime/SQLElapsedTime),该指标说明了该语句是否是I/O敏感的SQLId:通过计算SQL文本获得的SQL_ID,不同的SQL文本必然有不同的SQL_ID,对于10g~11g而言只要SQL文本不变那么在数据库之间该SQL对应的SQL_ID应当不不变的,12c中修改了SQL_ID的计算方法CapturedSQLaccountfor53.
9%ofTotalDBTime(s)对于不绑定变量的应用来说TopSQL有可能失准,所以要参考本项3-2SQLorderedbyCPUTimeSQLorderedbyCPUTimeSnaps:70719-70723->ResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.
->%Total-CPUTimeasapercentageofTotalDBCPU->%CPU-CPUTimeasapercentageofElapsedTime->%IO-UserI/OTimeasapercentageofElapsedTime->CapturedSQLaccountfor34.
9%ofTotalCPUTime(s):20,649->CapturedPL/SQLaccountfor0.
5%ofTotalCPUTime(s):20,649CPUCPUperElapsedTime(s)ExecutionsExec(s)%TotalTime(s)%CPU%IOSQLId1,545.
01,864,4240.
007.
54,687.
833.
065.
78g6a701j83c8qModule:MZIndexerSELECTt0.
BOOLEAN_VALUE,t0.
CLASS_CODE,t0.
CREATED,t0.
END_DATE,t0.
PRODUCT_ATTRIBUTE_ID,t0.
LAST_MODIFIED,t0.
OVERRIDE_FLAG,t0.
PRICE,t0.
PRODUCT_ATTRIBUTE_TYPE_ID,t0.
PRODUCT_ID,t0.
PRODUCT_PUB_RELEASE_TYPE_ID,t0.
PRODUCT_VOD_TYPE_ID,t0.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/SAP_PRODUCT_ID,t0.
START_DATE,t0.
STRING_VALUEFROMmz_product_attributet0WHERCPUTIME:该SQL在快照时间内累计执行所消耗的CPU时间片,单位为sExecutions:该SQL在快照时间内累计执行的次数CPUperExec(s):该SQL平均单次执行所消耗的CPU时间,即(SQLCPUTIME/SQLExecutions)%Total:该SQL累计消耗的CPU时间占该时段总的DBCPU的比例,即(SQLCPUTIME/TotalDBCPU)%CPU该SQL所消耗的CPU时间占该SQL消耗的时间里的比例,即(SQLCPUTime/SQLElapsedTime),该指标说明了该语句是否是CPU敏感的%IO该SQL所消耗的I/O时间占该SQL消耗的时间里的比例,即(SQLI/OTime/SQLElapsedTime),该指标说明了该语句是否是I/O敏感的3-3BufferGetsSQLorderedbyGetsSQLorderedbyGetsDB/Inst:ITSCMP/itscmp2Snaps:70719-70723->ResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.
->%Total-BufferGetsasapercentageofTotalBufferGets->%CPU-CPUTimeasapercentageofElapsedTime->%IO-UserI/OTimeasapercentageofElapsedTime->TotalBufferGets:2,021,476,421->CapturedSQLaccountfor68.
2%ofTotalBufferGetsElapsedGetsExecutionsperExec%TotalTime(s)%CPU%IOSQLId4.
61155E+081,864,424247.
322.
84,687.
833.
065.
78g6a701j83c注意buffergets逻辑读是消耗CPUTIME的重要源泉,但并不是说消耗CPUTIME的只有buffergets.
大多数情况下SQLorderbyCPUTIME和SQLorderbybuffersgets2个部分的TOPSQL及其排列顺序都是一样的,此种情况说明消耗最多buffergets的就是消耗最多CPU的SQL,如果我们希望降低系统的CPU使用率,那么只需要调优SQL降低buffergets即可.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/但也并不是100%的情况都是如此,CPUTIME的消耗者还包括函数运算、PL/SQL控制、Latch/Mutex的Spin等等,所以SQLorderbyCPUTIME和SQLorderbybuffersgets2个部分的TOPSQL完全不一样也是有可能的,需要因地制宜来探究到底是什么问题导致的HighCPU,进而裁度解决之道.
BufferGets:该SQL在快照时间内累计运行所消耗的buffergets,包括了consistentread和currentreadExecutions:该SQL在快照时间内累计执行的次数GetsperExec:该SQL平均单次的buffergets,对于事务型transaction操作而言一般该单次buffergets小于2000%Total该SQL累计运行所消耗的buffergets占总的dbbuffergets的比率,(SQLbuffergets/DBtotalbuffergets)3-4PhysicalReadsSQLorderedbyReadsSQLorderedbyReadsDB/Inst:ITSCMP/itscmp2Snaps:70719-70723->%Total-PhysicalReadsasapercentageofTotalDiskReads->%CPU-CPUTimeasapercentageofElapsedTime->%IO-UserI/OTimeasapercentageofElapsedTime->TotalDiskReads:56,839,035->CapturedSQLaccountfor34.
0%ofTotalPhysicalReadsElapsedReadsExecutionsperExec%TotalTime(s)%CPU%IOSQLId9,006,16319.
0062E+0615.
8720.
95.
980.
94g36tmp70h185Physicalreads:该SQL累计运行所消耗的物理读Executions:该SQL在快照时间内累计执行的次数ReadsperExec:该SQL单次运行所消耗的物理读,(SQLPhysicalreads/Executions),对于OLTPtransaction类型的操作而言单次一般不超过100%Total:该SQL累计消耗的物理读占该时段总的物理读的比例,即(SQLphysicalread/TotalDBphysicalread)3-5ExecutionsSQLorderedbyExecutions上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/SQLorderedbyExecutionsSnaps:70719-70723->%CPU-CPUTimeasapercentageofElapsedTime->%IO-UserI/OTimeasapercentageofElapsedTime->TotalExecutions:48,078,147->CapturedSQLaccountfor50.
4%ofTotalElapsedExecutionsRowsProcessedRowsperExecTime(s)%CPU%IOSQLId6,327,96311,249,6451.
8590.
547.
852.
71avv7759j8r按照执行次数来排序的话,也是性能报告对比时一个重要的参考因素,因为如果TOPSQL的执行次数有明显的增长,那么性能问题的出现也是意料之中的事情了.
当然执行次数最多的,未必便是对性能影响最大的TOPSQLExecutions:该SQL在快照时间内累计执行的次数RowsProcessed:该SQL在快照时间内累计执行所处理的总行数RowsperExec:SQL平均单次执行所处理的行数,这个指标在诊断一些数据问题造成的SQL性能问题时很有用3-6ParseCallsSQLorderedbyParseCallsSQLorderedbyParseCallsSnaps:70719-70723->TotalParseCalls:2,160,124->CapturedSQLaccountfor58.
3%ofTotal%TotalParseCallsExecutionsParsesSQLId上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/496,475577,35722.
98d07gaa3wntdffParseCalls:解析调用次数,与上文的LoadProfile中的Parse数一样包括软解析softparse和硬解析hardparseExecutions:该SQL在快照时间内累计执行的次数%TotalParses:本SQL解析调用次数占该时段数据库总解析次数的比率,为(SQLParseCalls/TotalDBParseCalls)3-7SQLorderedbySharableMemorySQLorderedbySharableMemorySnaps:70719-70723->OnlyStatementswithSharableMemorygreaterthan1048576aredisplayedSharableMem(b)Executions%TotalSQLId8,468,359390.
08au89sasqfb2ynModule:MZContentBridgeSELECTt0.
ASPECT_RATIO,t0.
CREATED,t0.
FILE_EXTENSION,t0.
HEIGHT,t0.
VIDEO_FILE_DIMENSIONS_ID,t0.
LAST_MODIFIED,t0.
NAME,t0.
WIDTHFROMMZ_VIDEO_FILE_DIMENSIONSt0WHERE(t0.
HEIGHT=:1ANDt0.
WIDTH=:2)SQLorderedbySharableMemory,一般该部分仅列出SharableMem(b)为1MB以上的SQL对象(OnlyState-mentswithSharableMemorygreaterthan1048576aredisplayed)数据来源是DBA_HIST_SQLSTAT.
SHARABLE_MEMShareableMem(b):SQL对象所占用的共享内存使用量Executions:该SQL在快照时间内累计执行的次数%Total:该SQL对象锁占共享内存占总的共享内存的比率3-8SQLorderedbyVersionCountVersionCountOracle中的执行计划可以是多版本的,即对于同一个SQL语句有多个不同版本的执行计划,这些执行计划又称作子游标,而一个SQL语句的文本可以称作一个父游标.
一个父游标对应多个子游标,产生上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/不同子游标的原因是SQL在被执行时无法共享之前已经生成的子游标,原因是多种多样的,例如在本session中做了一个优化器参数的修改例如optimizer_index_cost_adj从100修改到99,则本session的优化环境optimizerenv将不同于之前的子游标生成环境,这样就需要生成一个新的子游标,例如:SQL>createtableempasselect*fromscott.
emp;Tablecreated.
SQL>select*fromempwhereempno=1;norowsselectedSQL>select/*+MACLEAN*/*fromempwhereempno=1;norowsselectedSQL>selectSQL_ID,version_countfromV$SQLAREAWHERESQL_TEXTlike'%MACLEAN%'andSQL_TEXTnotlike'%like%';SQL_IDVERSION_COUNTbxnnm7z1qmg261SQL>selectcount(*)fromv$SQLwhereSQL_ID='bxnnm7z1qmg26';COUNT(*)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/1SQL>altersessionsetoptimizer_index_cost_adj=99;Sessionaltered.
SQL>select/*+MACLEAN*/*fromempwhereempno=1;norowsselectedSQL>selectSQL_ID,version_countfromV$SQLAREAWHERESQL_TEXTlike'%MACLEAN%'andSQL_TEXTnotlike'%like%';SQL_IDVERSION_COUNTbxnnm7z1qmg262SQL>selectcount(*)fromv$SQLwhereSQL_ID='bxnnm7z1qmg26';COUNT(*)2SQL>selectchild_number,OPTIMIZER_ENV_HASH_VALUE,PLAN_HASH_VALUEfromv$SQLwhereSQL_ID='bxnnm7z1qmg26';CHILD_NUMBEROPTIMIZER_ENV_HASH_VALUEPLAN_HASH_VALUE上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/037041287403956160932136364789583956160932可以看到上述演示中修改optimizer_index_cost_adj=99导致CBO优化器的优化环境发生变化,表现为不同的OPTIMIZER_ENV_HASH_VALUE,之后生成了2个子游标,但是这2个子游标的PLAN_HASH_VALUE同为3956160932,则说明了虽然是不同的子游标但实际子游标里包含了的执行计划是一样的;所以请注意任何一个优化环境的变化(V$SQL_SHARED_CURSOR)以及相关衍生的BUG都可能导致子游标无法共享,虽然子游标无法共享但这些子游标扔可能包含完全一样的执行计划,这往往是一种浪费.
注意V$SQLAREA.
VERSION_COUNT未必等于selectcount(*)FROMV$SQLWHERESQL_ID=‖,即V$SQLAREA.
VERSION_COUNT显示的子游标数目未必等于当前实例中还存有的子游标数目,由于sharedpoolagedout算法和其他一些可能导致游标失效的原因存在,所以子游标被清理掉是很常见的事情.
V$SQLAREA.
VERSION_COUNT只是一个计数器,它告诉我们曾经生成了多少个childcursor,但不保证这些child都还在sharedpool里面.
此外可以通过v$SQL的child_number字段来分析该问题,如果child_number存在跳号则也说明了部分child被清理了.
子游标过多的影响,当子游标过多(例如超过3000个时),进程需要去扫描长长的子游标列表childcursorlist以找到一个合适的子游标childcursor,进而导致cursorsharing性能问题现大量的Cursor:MutexS和librarycachelock等待事件.
关于子游标的数量控制,可以参考《11gR2游标共享新特性带来的一些问题以及_cursor_features_enabled、_cursor_obsolete_threshold和106001event》.
Executions:该SQL在快照时间内累计执行的次数HashValue:共享SQL的哈希值OnlyStatementswithVersionCountgreaterthan20aredisplayed注意该环节仅列出versioncount>20的语句3-9ClusterWaitTimeSQLorderedbyClusterWaitTimeSQLorderedbyClusterWaitTimeDB/Inst:ITSCMP/itscmp2Snaps:70719-70723->%Total-ClusterTimeasapercentageofTotalClusterWaitTime->%Clu-ClusterTimeasapercentageofElapsedTime->%CPU-CPUTimeasapercentageofElapsedTime->%IO-UserI/OTimeasapercentageofElapsedTime上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/->OnlySQLwithClusterWaitTime>.
005secondsisreported->TotalClusterWaitTime(s):525,480->CapturedSQLaccountfor57.
2%ofTotalClusterElapsedWaitTime(s)Executions%TotalTime(s)%Clu%CPU%IOSQLId132,639.
338,84825.
2181,411.
373.
1.
0.
1g0yc9szpuu068OnlySQLwithClusterWaitTime>.
005secondsisreported这个环节仅仅列出ClusterWaitTime>0.
005s的SQL该环节的数据主要来源于DBA_HIST_SQLSTAT.
CLWAIT_DELTADeltavalueofclusterwaittimeClusterWaitTime:该SQL语句累计执行过程中等待在集群等待上的时间,单位为秒,你可以理解为当一个SQL执行过程中遇到了gcbufferbusy、gccrmultiblockrequest之类的Cluster等待,则这些等待消耗的时间全部算在ClusterWaitTime里.
Executions:该SQL在快照时间内累计执行的次数%Total:该SQL所消耗的ClusterWaittime占总的ClusterWaittime的比率,为(SQLclusterwaittime/DBtotalclusterWaitTime)%Clu:该SQL所消耗的ClusterWaittime占该SQL总的耗时的比率,为(SQLclusterwaittime/SQLelapsedTime),该指标说明了该语句是否是集群等待敏感的%CPU该SQL所消耗的CPU时间占该SQL消耗的时间里的比例,即(SQLCPUTime/SQLElapsedTime),该指标说明了该语句是否是CPU敏感的%IO该SQL所消耗的I/O时间占该SQL消耗的时间里的比例,即(SQLI/OTime/SQLElapsedTime),该指标说明了该语句是否是I/O敏感的4InstanceActivityStatsInstanceActivityStatsDB/Inst:ITSCMP/itscmp2Snaps:70719-70723->OrderedbystatisticnameStatisticTotalperSecondperTrans上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/BatchedIO(bound)vectorcount450,449124.
61.
8BatchedIO(full)vectorcount5,4851.
50.
0BatchedIO(space)vectorcount1,4670.
40.
0BatchedIOblockmisscount4,119,0701,139.
716.
7BatchedIObufferdefragcount39,71011.
00.
2BatchedIOdoublemisscount297,35782.
31.
2BatchedIOsameunitcount1,710,492473.
37.
0BatchedIOsingleblockcount329,52191.
21.
3BatchedIOslowjumpcount47,10413.
00.
2BatchedIOvectorblockcount2,069,852572.
78.
4BatchedIOvectorreadcount262,16172.
51.
1BlockCleanoutOptimreferenced37,57410.
40.
2CCursor+sqlareaevicted1,4570.
40.
0InstanceActivityStats的数据来自于DBA_HIST_SYSSTAT,DBA_HIST_SYSSTAT来自于V$SYSSTAT.
这里每一个指标都代表一种数据库行为的活跃度,例如redosize是指生成redo的量,sorts(disk)是指磁盘排序的次数,tablescans(directread)是指直接路径扫描表的次数.
虽然这些指标均只有Total、perSecond每秒、perTrans每事务三个维度,但对诊断问题十分有用.
我们来举几个例子:1、例如当TopEvent中存在directpathread为Top等待事件,则需要分清楚是对普通堆表的directread还是由于大量LOB读造成的directpathread,这个问题可以借助tablescans(directread)、tablescans(longtables)、physicalreadsdirect、physicalreadsdirect(lob)、physicalreadsdirecttemporary几个指标来分析,假设physi-calreadsdirect>>远大于physicalreadsdirect(lob)+physicalreadsdirecttemporary,且有较大的tablescans(di-rectread)、tablescans(longtables)(注意这2个指标代表的是扫描表的次数不同于上面的phsicalreads的单位为块数*次数),则说明了是大表扫描引起的directpathread.
2、例如当TopEvent中存在enqTx:indexcontention等待事件,则需要分析rootnodesplits、branchnodesplits、leafnode90-10splits、leafnodesplits、failedprobesonindexblockrec几个指标,具体可以见文档《Oracle索引块分裂split信息汇总》上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/3、系统出现IO类型的等待事件为TOpFive例如dbfilesequential/scatteredread,我们需要通过AWR来获得系统IO吞吐量和IOPS:physicalreadbytes主要是应用造成的物理读取(Totalsizeinbytesofalldiskreadsbyapplicationactivity(andnototherinstanceactivity)only.
)而physicalreadtotalbytes则包括了rman备份恢复和后台维护任务所涉及的物理读字节数,所以我们在研究IO负载时一般参考physicalreadtotalbytes;以下4对指标均存在上述的关系physicalreadbytesphysicalreadtotalbytes物理读的吞吐量/秒physicalreadIOrequestsphysicalreadtotalIOrequests物理读的IOPSphysicalwritebytesphysicalwritetotalbytes物理写的吞吐量/秒physicalwriteIOrequestsphysicalwritetotalIOrequests物理写的IOPS总的物理吞吐量/秒=physicalreadtotalbytes+physicalwritetotalbytes总的物理IOPS=physicalreadtotalIOrequests+physicalwritetotalIOrequestsIO的主要指标吞吐量、IOPS和延迟均可以从AWR中获得了,IO延迟的信息可以从UserI/O的WaitClassAvgWaittime获得,也可以参考11g出现的IOStatbyFunctionsummaryInstanceActivityStats有大量的指标,但是对于这些指标的介绍没有那一份文档有完整详尽的描述,即便在Oracle原厂内部要没有(或者是Maclean没找到),实际是开发人员要引入某一个ActivityStats是比较容易的,并不像申请引入一个新后台进程那样麻烦,Oracle对于新版本中新后台进程的引入有严格的要求,但ActivityStats却很容易,往往一个one-offpatch中就可以引入了,实际上ActivityStats在源代码层仅仅是一些计数器.
'较为基础的statistics,大家可以参考官方文档的StatisticsDescriptions描述,地址在这里.
对于深入的指标例如―BatchedIO(space)vectorcount‖这种由于某些新特性被引入的,一般没有很详细的材料,需要到源代码中去阅读相关模块才能总结其用途,对于这个工作一般原厂是很延迟去完成的,所以没有一个完整的列表.
如果大家有对此的疑问,请去t.
askmaclean.
com发一个帖子提问.
InstanceActivityStats-AbsoluteValuesSnaps:7071->Statisticswithabsolutevalues(shouldnotbediffed)StatisticBeginValueEndValuesessionpgamemorymax1.
157882826E+121.
154290304E+12sessioncursorcachecount157,042,373157,083,136sessionugamemory5.
496429019E+145.
496775467E+14openedcursorscurrent268,916265,694上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/workareamemoryallocated827,704837,487logonscurrent2,6092,613sessionugamemorymax1.
749481584E+131.
749737418E+13sessionpgamemory4.
150306913E+114.
150008177E+11InstanceActivityStats–AbsoluteValues是显示快照起点和终点的一些指标的绝对值logoncurrent当前时间点的登录数openedcursorscurrent当前打开的游标数sessioncursorcachecount当前存在的session缓存游标数InstanceActivityStats-ThreadActivityDB/Inst:G10R25/G10R25Snaps:3663-3->Statisticsidentifiedby'(derived)'comefromsourcesotherthanSYSSTATStatisticTotalperHourlogswitches(derived)172,326.
47logswitches(derived)日志切换次数,见《理想的在线重做日志切换时间是多长》5IO统计5-1TablespaceIOStats基于表空间分组的IO信息TablespaceIOStatsDB/Inst:ITSCMP/itscmp2Snaps:70719-70723->orderedbyIOs(Reads+Writes)desc上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/TablespaceAvAvAvAvBufferAvBufReadsReads/sRd(ms)Blks/RdWritesWrites/sWaitsWt(ms)DATA_TS17,349,3984,8012.
31.
5141,077394,083,7045.
8INDEX_TS9,193,1222,5442.
01.
0238,563663,158,18746.
1UNDOTBS11,582,6594380.
71.
02012,43169.
0reads:指该表空间上发生的物理读的次数(单位不是块,而是次数)AvReads/s:指该表空间上平均每秒的物理读次数(单位不是块,而是次数)AvRd(ms):指该表空间上每次读的平均读取延迟AvBlks/Rd:指该表空间上平均每次读取的块数目,因为一次物理读可以读多个数据块;如果AvBlks/Rd>>1则可能系统有较多dbfilescatteredread可能是诊断FULLTABLESCAN或FASTFULLINDEXSCAN,需要关注tablescans(longtables)和indexfastfullscans(full)2个指标Writes:该表空间上发生的物理写的次数;对于那些Writes总是等于0的表空间不妨了解下是否数据为只读,如果是可以通过readonlytablespace来解决RAC中的一些性能问题.
AvWrites/s:指该表空间上平均每秒的物理写次数bufferWaits:该表空间上发生bufferbusywaits和readbyothersession的次数(9i中bufferbusywaits包含了readbyothersession).
AvBufWt(ms):该表空间上发生bufferWaits的平均等待时间,单位为ms5-2FileI/OFileIOStatsSnaps:70719-70723->orderedbyTablespace,File上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/TablespaceFilenameAvAvAvAvBufferAvBufReadsReads/sRd(ms)Blks/RdWritesWrites/sWaitsWt(ms)AMG_ALBUM_IDX_TS+DATA/itscmp/plugged/data2/amg_album_idx_ts01.
dbf23,29860.
61.
02000.
0AMG_ALBUM_IDX_TS+DATA/itscmp/plugged/data3/amg_album_idx_ts02.
dbf3,00310.
61.
02000.
0Tablespace表空间名FileName数据文件的路径Reads:该数据文件上累计发生过的物理读次数,不是块数AvReads/s:该数据文件上平均每秒发生过的物理读次数,不是块数AvRd(ms):该数据文件上平均每次物理读取的延迟,单位为msAvBlks/Rd:该数据文件上平均每次读取涉及到的块数,OLTP环境该值接近1Writes:该数据文件上累计发生过的物理写次数,不是块数AvWrites/s:该数据文件上平均每秒发生过的物理写次数,不是块数bufferWaits:该数据文件上发生bufferbusywaits和readbyothersession的次数(9i中bufferbusywaits包含了readbyothersession).
AvBufWt(ms):该数据文件上发生bufferWaits的平均等待时间,单位为ms若某个表空间上有较高的IO负载,则有必要分析一下是否其所属的数据文件上的IO较为均匀还是存在倾斜,是否需要结合存储特征来将数据均衡分布到不同磁盘上的数据文件上,以优化I/O6缓冲池统计BufferPoolStatisticsBufferPoolStatisticsSnaps:70719-70723->StandardblocksizePoolsD:default,K:keep,R:recycle->DefaultPoolsforotherblocksizes:2k,4k,8k,16k,32k上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/FreeWritBufferNumberofPoolBufferPhysicalPhysicalBuffCompBusyPBuffersHit%GetsReadsWritesWaitWaitWaits16k15,720N/A000000D2,259,159982.
005084E+0942,753,650560,460018.
51E+06该环节的数据主要来源于WRH$_BUFFER_POOL_STATISTICS,而WRH$_BUFFER_POOL_STATISTICS是定期汇总v$SYSSTAT中的数据Ppool池的名字D:默认的缓冲池defaultbufferpool,K:KeepPool,R:RecyclePool;2k4k8k16k32k:代表各种非标准块大小的缓冲池Numberofbuffers:实际的缓冲块数目,约等于池的大小/池的块大小PoolHit%:该缓冲池的命中率BufferGets:对该缓冲池的中块的访问次数包括consistentgets和dbblockgetsPhysicalReads:该缓冲池BufferCache引起了多少物理读,其实是physicalreadscache,单位为块数*次数PhysicalWrites:该缓冲池中Buffercache被写的物理写,其实是physicalwritesfromcache,单位为块数*次数FreeBufferWaits:等待空闲缓冲的次数,可以看做该bufferpool发生freebufferwaits等待的次数WriteCompWait:等待DBWR写入脏buffer到磁盘的次数,可以看做该bufferpool发生writecompletewaits等待的次数BufferBusyWaits:该缓冲池发生bufferbusywait等待的次数7-1CheckpointActivity检查点与InstanceRecoveryStats实例恢复CheckpointActivitySnaps:70719-70723->TotalPhysicalWrites:590,563OtherAutotuneThreadMTTRLogSizeLogCkptSettingsCkptCkptWritesWritesWritesWritesWritesWrites000012,8990上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/InstanceRecoveryStatsSnaps:70719-70723->B:BeginSnapshot,E:EndSnapshotEstdTargtEstdLogCkptLogCkptOptRACMTTRMTTRRecoveryActualTargetLogSzTimeoutIntervalLogAvail(s)(s)EstdIOsRedoBlksRedoBlksRedoBlksRedoBlksRedoBlksSz(M)TimeB0612828477505178697150960341786971N/AN/A3E0716990586071231420750960342314207N/AN/A3该环节的数据来源于WRH$_INSTANCE_RECOVERYMTTRWrites:为了满足FAST_START_MTTR_TARGET指定的MTTR值而做出的物理写WRITES_MTTRLogSizeWrites:由于最小的redologfile而做出的物理写WRITES_LOGFILE_SIZELogCkptwrites:由于LOG_CHECKPOINT_INTERVAL和LOG_CHECKPOINT_TIMEOUT驱动的增量检查点而做出的物理写WRITES_LOG_CHECKPOINT_SETTINGSOtherSettingsWrites:由于其他设置(例如FAST_START_IO_TARGET)而引起的物理写,WRITES_OTHER_SETTINGSAutotuneCkptWrites:由于自动调优检查点而引起的物理写,WRITES_AUTOTUNEThreadCkptWrites:由于threadcheckpoint而引起的物理写,WRITES_FULL_THREAD_CKPTB代表开始点,E代表结尾TargtMTTR(s):目标MTTR(meantimetorecover)意为有效恢复时间,单位为秒.
TARGET_MTTR的计算基于给定的参数FAST_START_MTTR_TARGET,而TARGET_MTTR作为内部使用.
实际在使用中TargetMTTR未必能和FAST_START_MTTR_TARGET一样.
如果FAST_START_MTTR_TARGET过小,那么TARGET_MTTR将是系统条件所允许的最小估算值;如果FAST_START_MTTR_TARGET过大,则TARGET_MTTR以保守算法计算以获得完成恢复的最长估算时间.
estimated_mttr(s):当前基于脏buffer和重做日志块的数量,而评估出的有效恢复时间.
它的估算告诉用户以当下系统的负载若发生实例crash,则需要多久时间来做crashrecovery的前滚操作,之后才能打开数据库.
RecoveryEstdIOs:实际是当前buffercache中的脏块数量,一旦实例崩溃这些脏块要被前滚ActualRedoBlks:当前实际需要恢复的redo重做块数量上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/TargetRedoBlks:是LogSzRedoBlks、LogCkptTimeoutRedoBlks、LogCkptIntervalRedoBlks三者的最小值LogSzRedoBlks:代表必须在logfileswitch日志切换之前完成的checkpoint中涉及到的redoblock,也叫maxloglag;数据来源selectLOGFILESZfromX$targetrba;selectLOG_FILE_SIZE_REDO_BLKSfromv$instance_recovery;LogCkptTimeoutRedoBlks:为了满足LOG_CHECKPOINT_TIMEOUT所需要处理的redoblock数,lagforcheckpointtimeout;数据来源selectCT_LAGfromx$targetrba;LogCkptIntervalRedoBlks:为了满足LOG_CHECKPOINT_INTERVAL所需要处理的redoblock数,lagforcheckpointinterval;数据来源selectCI_LAGfromx$targetrba;OptLogSz(M):基于FAST_START_MTTR_TARGET而估算出来的redologfile的大小,单位为MB.
Oracle官方推荐创建的重做日志大小至少大于这个估算值EstdRACAvailTime:指评估的RAC中节点失败后集群从冻结到部分可用的时间,这个指标仅在RAC中可用,单位为秒.
ESTD_CLUSTER_AVAILABLE_TIME7-2BufferPoolAdvisory缓冲池建议BufferPoolAdvisoryDB/Inst:ITSCMP/itscmp2Snap:70723->Onlyrowswithestimatedphysicalreads>0aredisplayed->orderedbyBlockSize,BuffersForEstimateEstPhysEstimatedEstSizeforSizeBuffersReadPhysReadsEstPhys%DBtimePEst(M)Factor(thousands)Factor(thousands)ReadTimeforRdsD1,920.
12274.
91,110,565,59711.
0E+09D3,840.
24543.
6832,483,88617.
4E+08D5,760.
36802.
8634,092,57815.
6E+08D7,680.
49072.
2500,313,58914.
3E+08D9,600.
51,1341.
8410,179,55713.
5E+08D11,520.
61,3611.
5348,214,28312.
9E+08上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/D13,440.
71,5881.
3304,658,44112.
5E+08D15,360.
81,8141.
2273,119,80812.
2E+08D17,280.
92,0411.
1249,352,94312.
0E+08D19,2001.
02,2681.
0230,687,20611.
8E+08D19,4561.
02,2981.
0228,664,26911.
8E+08D21,1201.
12,4950.
9215,507,85811.
7E+08D23,0401.
22,7220.
9202,816,78711.
6E+08D24,9601.
32,9480.
8191,974,19611.
5E+08D26,8801.
43,1750.
8182,542,76511.
4E+08D28,8001.
53,4020.
8174,209,19911.
3E+08D30,7201.
63,6290.
7166,751,63111.
2E+08D32,6401.
73,8560.
7160,002,42011.
2E+08D34,5601.
84,0820.
7153,827,35111.
1E+08D36,4801.
94,3090.
6148,103,33811.
1E+08D38,4002.
04,5360.
6142,699,86611.
0E+08缓冲池的颗粒大小可以参考SELECT*FROMV$SGAINFOwherenamelike(Granule%');P指缓冲池的名字可能包括有Ddefaultbufferpool,KKeepPool,RrecyclePoolSizeForEst(M):指以该尺寸的bufferpool作为评估的对象,一般是目前currentsize的10%~200%,以便了解bufferpool增大~减小对物理读的影响SizeFactor:尺寸因子,只对应bufferpool大小对当前设置的比例因子,例如current_size是100M,则如果评估值是110M那么sizeFactor就是1.
1Buffers(thousands):指这个bufferpool尺寸下的buffer数量,要乘以1000才是实际值EstPhysReadFactor:评估的物理读因子,例如当前尺寸的bufferpool会引起100个物理读,则别的尺寸的bufferpool如果引起120个物理读,那么对应尺寸的EstPhysReadFactor就是1.
2EstimatedPhysReads(thousands):评估的物理读数目,要乘以1000才是实际值,显然不同尺寸的bufferpool对应不同的评估的物理读数目EstPhysReadTime:评估的物理读时间Est%DBtimeforRds:评估的物理读占DBTIME的比率我们看bufferpooladvisory一般有2个目的:上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/1.
在物理读较多的情况下,希望通过增加bufferpool大小来缓解物理读等待,这是我们关注SizeFactor>1的bufferpool尺寸是否能共有效减少EstPhysReadFactor,如果EstPhysReadFactor随着SizeFactor增大而显著减少,那么说明增大buffercache是可以有效减少物理读的.
2.
在内存紧张的情况下,希望从bufferpool中匀出部分内存来移作他用,但是又不希望buffercache变小导致物理读增多性能下降,则此时观察EstPhysReadFactor是否随着SizeFactor减小而显著增大,如果不是则说明减少部分buffercache不会导致物理读大幅增加,也就可以安心减少buffercache注意SizeFactor和EstPhysReadFactor之间不是简单的线性关系,所以需要人为介入评估得失7-3PGAAggrSummaryPGAAggrSummarySnaps:70719-70723->PGAcachehit%-percentageofW/A(WorkArea)dataprocessedonlyin-memoryPGACacheHit%W/AMBProcessedExtraW/AMBRead/Written99.
9412,527375PGACacheHit%:指W/AWorkArea工作区的数据仅在内存中处理的比率,PGA缓存命中率workarea是PGA中负责处理排序、哈希连接和位图合并操作的区域;workarea也叫做SQL作业区域W/AMBprocesses:指在Workarea中处理过的数据的量,单位为MBExtraW/AMBRead/Written:指额外从磁盘上读写的工作区数据,单位为MB7-4PGAAggrTargetStatsWarning:pga_aggregate_targetwassettoolowforcurrentworkload,asthisvaluewasexceededduringthisinterval.
UsethePGAAdvisoryviewtohelpidentifyadifferentvalueforpga_aggregate_target.
PGAAggrTargetStatsSnaps:70719-70723->B:BeginSnapE:EndSnap(rowsdentifiedwithBorEcontaindatawhichisabsolutei.
e.
notdiffedovertheinterval)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/->AutoPGATarget-actualworkareamemorytarget->W/APGAUsed-amountofmemoryusedforallWorkareas(manual+auto)->%PGAW/AMem-percentageofPGAmemoryallocatedtoworkareas->%AutoW/AMem-percentageofworkareamemorycontrolledbyAutoMemMgmt->%ManW/AMem-percentageofworkareamemoryundermanualcontrol%PGA%Auto%ManPGAAggrAutoPGAPGAMemW/APGAW/AW/AW/AGlobalMemTarget(M)Target(M)Alloc(M)Used(M)MemMemMemBound(K)B8,19251223,690.
5150.
1.
6100.
0.
0838,860E8,19251223,623.
6156.
9.
7100.
0.
0838,860此环节的数据来源主要是WRH$_PGASTATPGAAggrTarget(M):本质上就是pga_aggregate_target,当然在AMM(memory_target)环境下这个值可能会自动变化AutoPGATarget(M):在自动PGA管理模式下实际可用的工作区内存―aggregatePGAautotarget―,因为PGA还有其他用途,不能全部作为workareamemoryPGAMemAlloc(M):目前已分配的PGA内存,alloc不等于inuse即分配的内存不等于在使用的内存,理论上PGA会将确实不使用的内存返回给OS(PGAmemoryfreedbacktoOS),但是存在PGA占用大量内存而不释放的场景在上例中pga_aggregate_target仅为8192M,而实际processes在2,615~8000之间,如果一个进程耗费5MB的PGA也需要10000M的PGA,而实际这里PGAMemAlloc(M)是23,690M,这说明存在PGA的过载,需要调整pga_aggregate_targetW/APGAUsed(M):所有的工作区workarea(包括manual和auto)使用的内存总和量,单位为MB%PGAW/AMem:分配给workarea的内存量占总的PGA的比例,(W/APGAUsed)/PGAMemAlloc%AutoW/AMem:AUTO自动工作区管理所控制的内存(workarea_size_policy=AUTO)占总的workarea内存的比例%ManW/AMem:MANUAL手动工作区管理所控制的内存(workarea_size_policy=MANUAL)占总的workarea内存的比例GlobalMemBound(K):指在自动PGA管理模式下一个工作区所能分配的最大内存(注意一个SQL执行过程中可能有多个工作区workarea).
GlobalMemBound(K)这个指标在实例运行过程中将被持续性的修正,以反应数据库当时工作区的负载情况.
显然在有众多活跃工作区的系统负载下相应地GlobalMemBound将会下降.
但应当保持globalbound值不要小于1MB,否则建议调高pga_aggregate_target上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/7-5PGAAggrTargetHistogramPGAAggrTargetHistogramSnaps:70719-70723->OptimalExecutionsarepurelyin-memoryoperationsLowHighOptimalOptimalTotalExecsOptimalExecs1-PassExecsM-PassExecs2K4K262,086262,0860064K128K49749700128K256K86286200256K512K36836800512K1024K440,585440,585001M2M68,31368,313002M4M169161804M8M5042808M16M82820016M32M110032M64M121200128M256M2020数据来源:WRH$_SQL_WORKAREA_HISTOGRAMLowOptimal:此行所包含工作区workarea最适合内存要求的下限HighOptimal:此行所包含工作区workarea最适合内存要求的上限TotalExecs:在LowOptimal~HighOptimal范围工作区内完成的总执行数上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/Optimalexecs:optimal执行是指完全在PGA内存中完成的执行次数1-passExecs:指操作过程中仅发生1次磁盘读取的执行次数M-passExecs:指操作过程中发生了1次以上的磁盘读取,频发磁盘读取的执行次数7-6PGAMemoryAdvisoryPGAMemoryAdvisorySnap:70723->WhenusingAutoMemoryMgmt,minimallychooseapga_aggregate_targetvaluewhereEstdPGAOverallocCountis0EstdExtraEstdPEstdPGAPGATargetSizeW/AMBW/AMBRead/CacheOveralloEstdEst(MB)FactrProcessedWrittentoDiskHit%CountTime1,0240.
12,671,356,938.
7387,531,258.
987.
01.
07E+077.
9E+112,0480.
32,671,356,938.
7387,529,979.
187.
01.
07E+077.
9E+114,0960.
52,671,356,938.
7387,518,881.
887.
01.
07E+077.
9E+116,1440.
82,671,356,938.
7387,420,749.
587.
01.
07E+077.
9E+118,1921.
02,671,356,938.
723,056,196.
599.
01.
07E+076.
9E+119,8301.
22,671,356,938.
722,755,192.
699.
06.
81E+066.
9E+1111,4691.
42,671,356,938.
720,609,438.
599.
04.
15E+066.
9E+1113,1071.
62,671,356,938.
719,021,139.
199.
0581,3626.
9E+1114,7461.
82,671,356,938.
718,601,191.
099.
0543,5316.
9E+1116,3842.
02,671,356,938.
718,561,361.
199.
0509,6876.
9E+1124,5763.
02,671,356,938.
718,527,422.
399.
0232,8176.
9E+1132,7684.
02,671,356,938.
718,511,872.
699.
0120,1806.
9E+1149,1526.
02,671,356,938.
718,500,815.
399.
08,0216.
9E+11上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/65,5368.
02,671,356,938.
718,498,733.
099.
006.
9E+11PGATargetEst(MB)用以评估的PGA_AGGREGATE_TARGET值SizeFactr,当前用以评估的PGA_AGGREGATE_TARGET和当前实际设置的PGA_AGGREGATE_TARGET之间的比例因子PGATargetEst/PGA_AGGREGATE_TARGEW/AMBProcessed:workarea中要处理的数据量,单位为MBEstdExtraW/AMBRead/WrittentoDisk:以one-pass、M-Pass方式处理的数据量预估值,单位为MBEstdPCacheHit%:预估的PGA缓存命中率EstdPGAOverallocCount:预估的PGA过载量,如上文所述PGA_AGGREGATE_TARGET仅是一个目标值,无法真正限制PGA内存的使用,当出现PGA内存硬性需求时会产生PGAoverallocate过载(WhenusingAutoMemoryMgmt,minimallychooseapga_aggregate_targetvaluewhereEstdPGAOverallocCountis0)7-7SharedPoolAdvisorySharedPoolAdvisorySnap:70723->SP:SharedPoolEstLC:EstimatedLibraryCacheFactr:Factor->Notethereisoftena1:ManycorrelationbetweenasinglelogicalobjectintheLibraryCache,andthephysicalnumberofmemoryobjectsassociatedwithit.
ThereforecomparingthenumberofLibCacheobjects(e.
g.
inv$librarycache),withthenumberofLibCacheMemoryObjectsisinvalid.
EstLCEstLCEstLCEstLCSharedSPEstLCTimeTimeLoadLoadEstLCPoolSizeSizeEstLCSavedSavedTimeTimeMemObjSize(M)Factr(M)MemObj(s)Factr(s)FactrHits(K)304.
8563,9877,7281.
0611.
4332352.
91016,2437,7451.
0441.
03344001.
01147,7777,7451.
0441.
0334上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/4481.
11147,7777,7451.
0441.
03344961.
21147,7777,7451.
0441.
03345441.
41147,7777,7451.
0441.
03345921.
51147,7777,7451.
0441.
03346401.
61147,7777,7451.
0441.
03346881.
71147,7777,7451.
0441.
03347361.
81147,7777,7451.
0441.
03347842.
01147,7777,7451.
0441.
03348322.
11147,7777,7451.
0441.
0334SharedPoolSize(M):用以评估的sharedpool共享池大小,在AMM/ASMM环境下shared_pool大小都可能浮动SPSizeFactr:共享池大小的比例因子,(SharedPoolSizeforEstim/SHARED_POOL_SIZE)EstdLCSize(M):评估的librarycache大小,单位为MB,因为是sharedpool中包含librarycache当然还有其他例如rowcacheEstLCMemObj指评估的指定大小的共享池内的librarycachememoryobject的数量ESTD_LC_MEMORY_OBJECTSEstLCTimeSaved(s):指在指定的共享池大小情况下可找到需要的librarycachememoryobjects,从而节约的解析时间.
这些节约的解析时间也是花费在共享池内重复加载需要的对象(reload),这些对象可能因为共享池没有足够的freememory而被agedout.
ESTD_LC_TIME_SAVEDEstLCTimeSavedFactr:EstLCTimeSaved(s)的比例因子,(EstLCTimeSaved(s)/CurrentLCTimeSaved(s))ESTD_LC_TIME_SAVED_FACTOREstLCLoadTime(s):在指定的共享池大小情况下解析的耗时EstLCLoadTimeFactr:EstLCLoadTime(s)的比例因子,(EstLCLoadTime(s)/CurrentLCLoadTime(s))ESTD_LC_LOAD_TIME_FACTOREstLCMemObjHits(K):在指定的共享池大小情况下需要的librarycachememoryobject正好在共享池中被找到的次数ESTD_LC_MEMORY_OBJECT_HITS;对于想缩小shared_pool_size共享池大小的需求,可以关注EstLCMemObjHits(K),如上例中共享池为352M时EstLCMemObjHits(K)就为334且之后不动,则可以考虑缩小shared_pool_size到该值,但要注意每个版本/平台上对共享池的最低需求,包括RAC中gcsresource、gcsshadow等资源均驻留在sharedpool中,增大db_cache_size时要对应关注.
7-8SGATargetAdvisory上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/SGATargetAdvisorySnap:70723SGATargetSGASizeEstDBEstPhysicalSize(M)FactorTime(s)Reads3,7520.
11.
697191E+091.
4577142918E+127,5040.
31.
222939E+09832,293,601,35411,2560.
41.
000162E+09538,390,923,78415,0080.
5895,087,191399,888,743,90018,7600.
6840,062,594327,287,716,80322,5120.
8806,389,685282,881,041,33126,2640.
9782,971,706251,988,446,80830,0161.
0765,293,424228,664,652,27633,7681.
1751,135,535210,005,616,65037,5201.
3739,350,016194,387,820,90041,2721.
4733,533,785187,299,216,67945,0241.
5732,921,550187,299,216,67948,7761.
6732,691,962187,299,216,67952,5281.
8732,538,908187,299,216,67956,2801.
9732,538,917187,299,216,67960,0322.
0732,462,391187,299,458,716该环节数据来源于WRH$_SGA_TARGET_ADVICESGAtargetSize:用以评估的sgatarget大小(sga_target)SGASizeFactor:SGASize的比例因子,(estSGAtargetSize/CurrentSGAtargetSize)EstDBTime(s):评估对应于该指定sgatargetsize会产生多少量的DBTIME,单位为秒EstPhysicalReads:评估对应该指定的sgatargetsize会产生多少的物理读上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/7-9StreamsPoolAdvisoryStreamsPoolAdvisoryDB/Inst:ITSCMP/itscmp2Snap:70723SizeforSizeEstSpillEstSpillEstUnspillEstUnspillEst(MB)FactorCountTime(s)CountTime(s)640.
500001281.
000001921.
500002562.
000003202.
500003843.
000004483.
500005124.
000005764.
500006405.
000007045.
500007686.
000008326.
500008967.
000009607.
500001,0248.
000001,0888.
500001,1529.
000001,2169.
50000上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/1,28010.
00000该环节只有当使用了Streams流复制时才会有必要数据,数据来源WRH$_STREAMS_POOL_ADVICESizeforEst(MB):用以评估的streamspool大小SizeFactor:streamspool大小的比例因子EstSpillCount:评估出的当使用该大小的流池时message溢出到磁盘的数量ESTD_SPILL_COUNTEstSpillTime(s):评估出的当使用该大小的流池时message溢出到磁盘的耗时,单位为秒ESTD_SPILL_TIMEEstUnspillCount:评估的当使用该大小的流池时messageunspill即从磁盘上读取的数量ESTD_UNSPILL_COUNTEstUnspillTime(s):评估的当使用该大小的流池时messageunspill即从磁盘上读取的耗时,单位为秒ESTD_UNSPILL_TIME7-10JavaPoolAdvisoryjavapool的相关指标与sharedpool相似,不再鏖述8WaitStatistics8-1BufferWaitStatisticsBufferWaitStatisticsSnaps:70719-70723->orderedbywaittimedesc,waitsdescClassWaitsTotalWaitTime(s)AvgTime(ms)datablock8,442,041407,25948undoheader16,2121,711106undoblock21,023557261stlevelbmb1,0382662562ndlevelbmb540185342bitmapblock9025276segmentheader1971366上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/fileheaderblock132643bitmapindexblock1801extentmap200数据来源:WRH$_WAITSTAT该环节是对缓冲池中各类型(class)块等待的汇总信息,wait的原因一般是bufferbusywaits和readbyothersessionclass数据块的class,一个oracle数据块即有class属性还有type属性,数据块中记录type属性(KCBH),而在bufferheader里存有class属性(X$BH.
class)Waits:该类型数据块的等待次数TotalWaitTime(s):该类型数据块的合计等待时间单位为秒AvgTime(ms):该类型数据块平均每次等待的耗时,单位ms如果用户正使用undo_management=AUTO的SMU则一般不会因为rollbacksegment过少而引起undoheaderblock类块的等待对于INSERT而引起的buffer争用等待:1、对于手动segment管理MSSM考虑增加Freelists、FreelistGroups2、使用ASSM,当然ASSM本身没什么参数可调对于INSERTONINDEX引起的争用:使用反向索引key使用HASH分区和本地索引可能的情况下减少index的density8-2EnqueueActivityenqueue队列锁等待EnqueueActivitySnaps:70719-70723->onlyenqueueswithwaitsareshown->Enqueuestatsgatheredpriorto10gshouldnotbecomparedwith10gdata->orderedbyWaitTimedesc,WaitsdescEnqueueType(RequestReason)RequestsSuccGetsFailedGetsWaitsWtTime(s)AvWtTime(ms)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/TX-Transaction(indexcontention)201,270201,3260193,94897,517502.
80TM-DML702,731702,68141,08146,67143,174.
08SQ-SequenceCache28,64328,632017,41835,6062,044.
19HW-SegmentHighWaterMark9,2108,8453761,21612,50510,283.
85TX-Transaction(rowlockcontention)9,2889,28009,23210,4861,135.
80CF-ControlfileTransaction15,85114,0941,7562,7984,5651,631.
64TX-Transaction(allocateITLentry)471369102360169469.
28EnqueueType(RequestReason)enqueue队列的类型,大家在研究enqueue问题前至少搞清楚enqueuetype和enqueuemode,enqueuetype是队列锁所要保护的资源如TM表锁CF控制文件锁,enqueuemode是持有队列锁的模式(SS、SX、S、SSX、X)Requests:申请对应的enqueuetype资源或者队列转换(enqueueconversion例如S转SSX)的次数SuccGets:对应的enqueue被成功申请或转换的次数FailedGets:对应的enqueue的申请或者转换失败的次数Waits:由对应的enqueue的申请或者转换而造成等待的次数WtTime(s):由对应的enqueue的申请或者转换而造成等待的等待时间AvWtTime(ms):由对应的enqueue的申请或者转换而造成等待的平均等待时间,WtTime(s)/Waits,单位为ms主要的enqueue等待事件:enq:TX–rowlock/indexcontention、allocateITL等待事件enq:TM–contention等待事件Oracle队列锁enq:TS,TemporarySegment(alsoTableSpace)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/9-1UndoSegmentSummaryUndoSegmentSummarySnaps:70719-70723->Min/MaxTR(mins)-MinandMaxTunedRetention(minutes)->STO-SnapshotTooOldcount,OOS-OutofSpacecount->Undosegmentblockstats:->uS-unexpiredStolen,uR-unexpiredReleased,uU-unexpiredreUsed->eS-expiredStolen,eR-expiredReleased,eU-expiredreUsedUndoNumUndoNumberofMaxQryMaxTxMin/MaxSTO/uS/uR/uU/TS#Blocks(K)TransactionsLen(s)ConcurcyTR(mins)OOSeS/eR/eU485.
0200,12755,4483171040.
2/100/00/0/0/0/0/0UndoSegmentStatsSnaps:70719-70723->Mostrecent35Undostatrows,orderedbyTimedescNumUndoNumberofMaxQryMaxTxTunRetSTO/uS/uR/uU/EndTimeBlocksTransactionsLen(s)Concy(mins)OOSeS/eR/eU29-Aug05:5211,70035,09855,4482341,0700/00/0/0/0/0/029-Aug05:4212,20324,67754,8442841,0650/00/0/0/0/0/029-Aug05:3214,13237,82654,2412371,0600/00/0/0/0/0/029-Aug05:2214,37932,31553,6373171,0500/00/0/0/0/0/0上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/29-Aug05:1215,69334,15753,0332991,0450/00/0/0/0/0/029-Aug05:0216,87836,05452,4282501,0400/00/0/0/0/0/0数据来源:WRH$_UNDOSTAT,undo相关的使用信息每10分钟刷新到v$undostat中UndoExtent有三种状态active、unexpired、expiredactive=>extent中包括了活动的事务,active的undoextent一般不允许被其他事务重用覆盖unexpired=>extent中没有活动的事务,但相关undo记录从inactive到目前还未经过undoretention(注意autoundoretention的问题因为这个特性可能在观察dba_undo_extents时看到大部分block都是unexpired,这是正常的)指定的时间,所以为unexpired.
对于没有guaranteeretention的undotablespace而言,unexpiredextent可能被steal为其他事物重用expired=>extent中没有活动事务,且超过了undoretention的时间UndoTS#在使用的这个undo表空间的表空间号,一个实例同一时间只能用1个undotablespace,RAC不同节点可以用不同的undotablespaceNumUndoBlocks(K)指被消费的undo数据块的数量,(K)代表要乘以1000才是实际值;可以用该指标来评估系统对undoblock的消费量,以便基于实际负载情况来评估UNDO表空间的大小NumberofTransactions指该段时间内该undo表空间上执行过的事务transaction总量MaxQryLen(s)该时段内持续最久的查询时间,单位为秒MaxTxConcy该时段内最大的事务并发量Min/MaxTR(mins)最小和最大的tunedundoretention,单位为分钟;tunedundoretention是自动undo调优特性,见undo自动调优介绍.
STO/OOSSTO指ORA-01555SnapshotTooOld错误出现的次数;OOS–指OutofSpacecount错误出现的次数uS–unexpiredStolen尝试从未过期的undoextent中偷取undospace的次数uR–unexpiredReleased从未过期的undoextent中释放的块数目uU–unexpiredreUsed未过期的undoextent中的block被其他事务重用的块数目eS–expiredStolen尝试从过期的undoextent中偷取undospace的次数eR–expiredReleased从过期的undoextent中释放的块数目eU–expiredreUsed过期的undoextent中的block被其他事务重用的块数目UNXPSTEALCNTNUMBERNumberofattemptstoobtainundospacebystealingunexpiredextentsfromothertransac-tionsUNXPBLKRELCNTNUMBERNumberofunexpiredblocksremovedfromcertainundosegmentssotheycanbeusedbyothertransactionsUNXPBLKREUCNTNUMBERNumberofunexpiredundoblocksreusedbytransactionsEXPSTEALCNTNUMBERNumberofattemptstostealexpiredundoblocksfromotherundosegmentsEXPBLKRELCNTNUMBERNumberofexpiredundoblocksstolenfromotherundosegmentsEXPBLKREUCNTNUMBERNumberofexpiredundoblocksreusedwithinthesameundosegments上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/SSOLDERRCNTNUMBERIdentifiesthenumberoftimestheerrorORA-01555occurred.
YoucanusethisstatistictodecidewhetherornottheUNDO_RETENTIONinitializationparameterissetproperlygiventhesizeoftheundotablespace.
IncreasingthevalueofUNDO_RETENTIONcanreducetheoccurrenceofthiserror.
10-1LatchActivityLatchActivitySnaps:70719-70723->"GetRequests","PctGetMiss"and"AvgSlps/Miss"arestatisticsforwilling-to-waitlatchgetrequests->"NoWaitRequests","PctNoWaitMiss"areforno-waitlatchgetrequests->"PctMisses"forbothshouldbeverycloseto0.
0PctAvgWaitPctGetGetSlpsTimeNoWaitNoWaitLatchNameRequestsMiss/Miss(s)RequestsMissAQdeqhashtablelatch40.
000N/AASMKeyedstatelatch9,0480.
10.
200N/AASMallocation15,0170.
20.
810N/AASMdbclientlatch72,7450.
000N/AASMmapheaders5,8600.
60.
610N/AASMmaploadwaitinglis1,4620.
000N/AASMmapoperationfreeli63,5390.
10.
410N/AASMmapoperationhasht76,484,4470.
11.
0660N/AlatchnameLatch闩的名字GetRequestslatch被以willing-to-wait模式申请并获得的次数上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/PctGetMissmiss是指latch被以willing-to-wait模式申请但是申请者必须等待的次数,PctGetMiss=Miss/GetRequests;miss可以从后面的LatchSleepBreakdown获得AvgSlps/MissSleep是指latch被以willing-to-wait模式申请最终导致session需要sleep以等待该latch的次数;AvgSlps/Miss=Sleeps/Misses;Sleeps可以从后面的LatchSleepBreakdown获得WaitTime(s)指花费在等待latch上的时间,单位为秒NoWaitRequests指latch被以no-wait模式来申请的次数PctNoWaitMiss以no-wait模式来申请latch但直接失败的次数对于高并发的latch例如cachebufferschains,其PctMisses应当十分接近于0一般的调优原则:如果latch:cachebufferschains是Top5事件,则需要考虑优化SQL减少全表扫描并减少TopbuffergetsSQL语句的逻辑读如果latch:redocopy、redoallocation等待较多,则可以考虑增大LOG_BUFFER如果latch:librarycache发生较多,则考虑增大shared_pool_size10-2LatchSleepBreakdownLatchSleepBreakdownDB/Inst:ITSCMP/itscmp2Snaps:70719-70723->orderedbymissesdescGetSpinLatchNameRequestsMissesSleepsGetscachebufferschains3,365,097,86612,831,875130,05812,683,450rowcacheobjects69,050,058349,8391,320348,649sessionidlebit389,437,460268,2852,768265,752enqueuehashchains8,698,453239,88022,476219,950gesresourcehashlist8,388,730158,89470,72891,104gcelement100,383,385135,7596,285129,742gcsremasteringlatch12,213,16972,373172,371enqueues4,662,54546,37425946,155ASMmapoperationhashtab76,484,44746,23145,2101,952上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/Lsodarraylatch72,59824,22424,5771,519latchnameLatch闩的名字GetRequestslatch被以willing-to-wait模式申请并获得的次数misses是指latch被以willing-to-wait模式申请但是申请者必须等待的次数9i以后miss之后一般有2种情况spingets了或者sleep一睡不醒直到被post,具体见全面解析9i以后OracleLatch闩锁原理;8i以前的latch算法可以参考:OracleLatch:一段描绘Latch运作的伪代码所以一般来说9i以后的misses=Sleeps+SpinGets,虽然不是绝对如此Sleeps是指latch被以willing-to-wait模式申请最终导致session需要sleep以等待该latch的次数SpinGets以willing-to-wait模式去申请latch,在miss之后以spin方式获得了latch的次数10-3LatchMissSourcesLatchMissSourcesSnaps:70719-70723->onlylatcheswithsleepsareshown->orderedbyname,sleepsdescNoWaitWaiterLatchNameWhereMissesSleepsSleepsASMKeyedstatelatchkfksolGet011ASMallocationkfgpnSetDisks20170ASMallocationkfgpnClearDisks050ASMallocationkfgscCreate040ASMallocationkfgrpGetByName0126ASMmapheaderskffmUnidentify_3078ASMmapheaderskffmAllocate060ASMmapheaderskffmIdentify0611上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/ASMmapheaderskffmFree010ASMmapoperationfreelikffmTranslate20158ASMmapoperationhashtkffmUnidentify044,67736,784ASMmapoperationhashtkffmTranslate02203,517数据来源为DBA_HIST_LATCH_MISSES_SUMMARYlatchnameLatch闩的名字where:指哪些代码路径内核函数持有过这些该latch,而不是哪些代码路径要申请这些latch;例如kcbgtcr函数的作用是GetablockforConsistentread,其持有latch:cachebufferschain是很正常的事情NoWaitMisses:以no-wait模式来申请latch但直接失败的次数Sleeps:指latch被以willing-to-wait模式申请最终导致session需要sleep以等待该latch的次数timeofsleepsresultedinmakingthelatchrequestWaiterSleeps:等待者休眠的次数timesofsleepsthatwaitersdidforeachwhere;Sleep是阻塞者等待的次数,WaiterSleeps是被阻塞者等待的次数10-4MutexSleepSummaryMutexSleepSummarySnaps:70719-70723->orderedbynumberofsleepsdescWaitMutexTypeLocationSleepsTime(ms)CursorPinkksfbc[KKSCHLFSP2]4,36414,520CursorPinkkslce[KKSCHLPIN2]2,3962,498LibraryCachekglpndl195903475LibraryCachekglpin14800458LibraryCachekglpnal291799259LibraryCachekglget115531,697LibraryCachekglpnal19048988上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/LibraryCachekgllkdl1854811,528CursorPinkksLockDelete[KKSCHLPIN6]410666CursorStatkkocsStoreBindAwareStats[KKSSTA346497LibraryCachekglhdgn2106167348LibraryCachekglhdgh1642684LibraryCachekgldtin1421955CursorPinkksfbc[KKSCHLPIN1]1334LibraryCachekglhdgn1621113LibraryCachekgllkal180912LibraryCachekgllkc15760CursorPinkksSetBindType[KKSCHLPIN3]55LibraryCachekglGetHandleReference124420LibraryCachekglUpgradeLock11940LibraryCachekglget2230LibraryCachekglati14510LibraryCachekglini13210LibraryCachekglobld17510LibraryCachekglobpn17110Mutex是10.
2.
0.
2以后引入的新的内存锁机制,具体对Mutex的描述见《深入理解Oracle中的Mutex》:http://www.
askmaclean.
com/archives/understanding-oracle-mutex.
htmlMutexTypeMutex的类型其实就是mutex对应的客户的名字,在版本10.
2中基本只有KKS使用Mutex,所以仅有3种:CursorStat(kgx_kks1)CursorParent(kgx_kks2)CursorPin(kgx_kks3)11g中增加了LibraryCacheLocation发起对该Mutex申请的代码路径codelocation,而不是还持有该Mutex的代码路径或曰内核函数10.
2中最常见的下面的几个函数kkspsc0-负责解析游标–检测我们正在解析的游标是否有对象的parentcursorheap0存在上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/kksfbc–负责找到合适的子游标或者创建一个新的子游标kksFindCursorstatSleeps:Mutex的Get和Sleep当一个Mutex被申请时,一般称为一个getrequest.
若初始的申请未能得到授权,则该进程会因为此次申请而进入到255次SPIN中(_mutex_spin_countMutexspincount),每次SPIN循环迭代过程中该进程都会去看看Mutex被释放了吗.
若该Mutex在SPIN之后仍未被释放,则该进程针对申请的mutex进入对应的mutexwait等待事件中.
实际进程的等待事件和等待方式由mutex的类型锁决定,例如Cursorpin、CursorParent.
举例来说,这种等待可能是阻塞等待,也可以是sleep.
但是请注意在V$MUTEX_SLEEP_*视图上的sleep列意味着等待的次数.
相关代码函数在开始进入等待时自加这个sleep字段.
等待计时从进程进入等待前开始计算等待时间,当一个进程结束其等待,则等待的时间加入都总和total中.
该进程再次尝试申请之前的Mutex,若该Mutex仍不可用,则它再次进入spin/wait的循环.
V$MUTEX_SLEEP_HISTORY视图的GETS列仅在成功申请到一个Mutex时才增加.
WaitTime(ms)类似于latch,spintime不算做mutex的消耗时间,它只包含等待消耗的时间.
11segmentstatistics段级统计11-1SegmentsbyLogicalReadsSegmentsbyLogicalReadsDB/Inst:MAC/MAC2Snaps:70719-70723->TotalLogicalReads:2,021,476,421->CapturedSegmentsaccountfor83.
7%ofTotalTablespaceSubobjectObj.
LogicalOwnerNameObjectNameNameTypeReads%TotalCONTENT_OWINDEX_TSMZ_PRODUCT_ATTRIBUTEINDEX372,849,92018.
44CONTENT_OWINDEX_TSMZ_PRODUCT__LS_PKINDEX329,829,63216.
32CONTENT_OWDATA_TSMZ_PRODUCT_ATTRIBUTETABLE218,419,00810.
80CONTENT_OWPLAYLIST_AMZ_PLAYLIST_ARTISTTABLE182,426,2409.
02上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/CONTENT_OWDATA_TSMZ_PRODUCTTABLE108,597,3765.
37owner:数据段的所有者TablespaceName:数据段所在表空间名ObjectName:对象名SubobjectName:子对象名,例如一个分区表的某个分区objType:对象类型一般为TABLE/INDEX或者分区或子分区LogicalReads:该数据段上发生过的逻辑读,单位为块数*次数%Total:占总的逻辑读的百分比,(当前对象上发生过的逻辑读/TotalDB逻辑读)11-2SegmentsbyPhysicalReadsSegmentsbyPhysicalReadsDB/Inst:MAC/MAC2Snaps:70719-70723->TotalPhysicalReads:56,839,035->CapturedSegmentsaccountfor51.
9%ofTotalTablespaceSubobjectObj.
PhysicalOwnerNameObjectNameNameTypeReads%TotalCONTENT_OWSONG_TSMZ_SONGTABLE7,311,92812.
86CONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE4,896,5548.
61CONTENT_OWDATA_TSMZ_CONTENT_PROVIDER_TABLE3,099,3875.
45CONTENT_OWDATA_TSMZ_PRODUCT_ATTRIBUTETABLE1,529,9712.
69CONTENT_OWDATA_TSMZ_PUBLICATIONTABLE1,391,7352.
45PhysicalReads:该数据段上发生过的物理读,单位为块数*次数%Total:占总的物理读的百分比,(当前对象上发生过的逻辑读/TotalDB逻辑读)11-3SegmentsbyPhysicalReadRequestsSegmentsbyPhysicalReadRequestsDB/Inst:MAC/MAC2Snaps:70719-70723上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/->TotalPhysicalReadRequests:33,936,360->CapturedSegmentsaccountfor45.
5%ofTotalTablespaceSubobjectObj.
PhysReadOwnerNameObjectNameNameTypeRequests%TotalCONTENT_OWDATA_TSMZ_CONTENT_PROVIDER_TABLE3,099,3469.
13CONTENT_OWDATA_TSMZ_PRODUCT_ATTRIBUTETABLE1,529,9504.
51CONTENT_OWDATA_TSMZ_PRODUCTTABLE1,306,7563.
85CONTENT_OWDATA_TSMZ_AUDIO_FILETABLE910,5372.
68CONTENT_OWINDEX_TSMZ_PRODUCT_ATTRIBUTEINDEX820,4592.
42PhysReadRequests:物理读的申请次数%Total:(该段上发生的物理读的申请次数/physicalreadIOrequests)11-4SegmentsbyUnOptimizedReadsSegmentsbyUnOptimizedReadsDB/Inst:MAC/MAC2Snaps:70719-70723->TotalUnOptimizedReadRequests:811,466->CapturedSegmentsaccountfor58.
5%ofTotalTablespaceSubobjectObj.
UnOptimizedOwnerNameObjectNameNameTypeReads%TotalCONTENT_OWDATA_TSMZ_CONTENT_PROVIDER_TABLE103,58012.
76CONTENT_OWSONG_TSMZ_SONGTABLE56,9467.
02CONTENT_OWDATA_TSMZ_IMAGETABLE47,0175.
79上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/CONTENT_OWDATA_TSMZ_PRODUCT_ATTRIBUTETABLE40,9505.
05CONTENT_OWDATA_TSMZ_PRODUCTTABLE30,4063.
75UnOptimizedReadsUnOptimizedReadReqs=PhysicalReadReqts–OptimizedReadReqsOptimizedReadRequests是指哪些满足ExadataSmartFlashCache(ortheSmartFlashCacheinOracleExadataV2(Notethatdespitesamename,conceptanduseofSmartFlashCache'inExadataV2isdifferentfromSmartFlashCache'inDatabaseSmartFlashCache)).
的物理读次数.
满足从smartflashcache走的读取申请呗认为是optimized,因为这些读取要比普通从磁盘走快得多.
此外通过smartscan读取storageindex的情况也被认为是'optimizedreadrequests',源于可以避免读取不相关的数据.
当用户不在使用Exadata时,则UnOptimizedReadReqs总是等于PhysicalReadReqts%Total:(该段上发生的物理读的UnOptimizedReadReqs/(physicalreadIOrequests–physicalreadrequestsop-timized))11-5SegmentsbyOptimizedReadsSegmentsbyOptimizedReadsDB/Inst:MAC/MAC2Snaps:70719-70723->TotalOptimizedReadRequests:33,124,894->CapturedSegmentsaccountfor45.
2%ofTotalTablespaceSubobjectObj.
OptimizedOwnerNameObjectNameNameTypeReads%TotalCONTENT_OWDATA_TSMZ_CONTENT_PROVIDER_TABLE2,995,7669.
04CONTENT_OWDATA_TSMZ_PRODUCT_ATTRIBUTETABLE1,489,0004.
50CONTENT_OWDATA_TSMZ_PRODUCTTABLE1,276,3503.
85CONTENT_OWDATA_TSMZ_AUDIO_FILETABLE890,7752.
69CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3INDEX816,0672.
46关于optimizerdread上面已经解释过了,这里的单位是request次数%Total:(该段上发生的物理读的OptimizedReadReqs/physicalreadrequestsoptimized)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/11-6SegmentsbyDirectPhysicalReadsSegmentsbyDirectPhysicalReadsDB/Inst:MAC/MAC2Snaps:70719-70723->TotalDirectPhysicalReads:14,118,552->CapturedSegmentsaccountfor94.
2%ofTotalTablespaceSubobjectObj.
DirectOwnerNameObjectNameNameTypeReads%TotalCONTENT_OWSONG_TSMZ_SONGTABLE7,084,41650.
18CONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE4,839,98434.
28CONTENT_OWDATA_TSMZ_PUBLICATIONTABLE1,361,1339.
64CONTENT_OWDATA_TSSYS_LOB0000203660C00LOB5,904.
04CONTENT_OWDATA_TSSYS_LOB0000203733C00LOB1,656.
01Directreads直接路径物理读,单位为块数*次数%Total(该段上发生的directpathreads/Totalphysicalreadsdirect)11-7SegmentsbyPhysicalWritesSegmentsbyPhysicalWritesDB/Inst:MAC/MAC2Snaps:70719-70723->TotalPhysicalWrites:590,563->CapturedSegmentsaccountfor38.
3%ofTotalTablespaceSubobjectObj.
PhysicalOwnerNameObjectNameNameTypeWrites%TotalCONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE23,5954.
00上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/CONTENT_OWDATA_TSMZ_PODCASTTABLE19,8343.
36CONTENT_OWINDEX_TSMZ_IMAGE_IX2INDEX16,3452.
77SYSSYSAUXWRH$_ACTIVE_SESSION_1367_70520TABLE14,1732.
40CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3INDEX9,6451.
63PhysicalWrites,物理写单位为块数*次数Total%(该段上发生的物理写/Totalphysicalwrites)11-9SegmentsbyPhysicalWriteRequestsSegmentsbyPhysicalWriteRequestsDB/Inst:MAC/MAC2Snaps:70719-70723->TotalPhysicalWriteRequestss:436,789->CapturedSegmentsaccountfor43.
1%ofTotalTablespaceSubobjectObj.
PhysWriteOwnerNameObjectNameNameTypeRequests%TotalCONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE22,5815.
17CONTENT_OWDATA_TSMZ_PODCASTTABLE19,7974.
53CONTENT_OWINDEX_TSMZ_IMAGE_IX2INDEX14,5293.
33CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3INDEX9,4342.
16CONTENT_OWDATA_TSMZ_AM_REQUESTTABLE8,6181.
97PhysWriteRequests物理写的请求次数,单位为次数%Total(该段上发生的物理写请求次数/physicalwriteIOrequests)11-10SegmentsbyDirectPhysicalWritesSegmentsbyDirectPhysicalWritesDB/Inst:MAC/MAC2Snaps:70719-70723上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/->TotalDirectPhysicalWrites:29,660->CapturedSegmentsaccountfor18.
3%ofTotalTablespaceSubobjectObj.
DirectOwnerNameObjectNameNameTypeWrites%TotalSYSSYSAUXWRH$_ACTIVE_SESSION_1367_70520TABLE4,60115.
51CONTENT_OWDATA_TSSYS_LOB0000203733C00LOB6202.
09CONTENT_OWDATA_TSSYS_LOB0000203660C00LOB134.
45CONTENT_OWDATA_TSSYS_LOB0000203779C00LOB46.
16CONTENT_OWDATA_TSSYS_LOB0000203796C00LOB41.
14DirectWrites直接路径写,单位额为块数*次数%Total为(该段上发生的直接路径写/physicalwritesdirect)11-11SegmentsbyTableScansSegmentsbyTableScansDB/Inst:MAC/MAC2Snaps:70719-70723->TotalTableScans:10,713->CapturedSegmentsaccountfor1.
0%ofTotalTablespaceSubobjectObj.
TableOwnerNameObjectNameNameTypeScans%TotalCONTENT_OWDATA_TSMZ_PUBLICATIONTABLE92.
86CONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE14.
13上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/CONTENT_OWSONG_TSMZ_SONGTABLE3.
03CONTENT_OWDATA_TSMZ_AM_REQUESTTABLE1.
01TableScans来源为dba_hist_seg_stat.
table_scans_delta不过这个指标并不十分精确11-12SegmentsbyDBBlocksChangesSegmentsbyDBBlocksChangesDB/Inst:MAC/MAC2Snaps:70719-70723->%ofCaptureshows%ofDBBlockChangesforeachtopsegmentcompared->withtotalDBBlockChangesforallsegmentscapturedbytheSnapshotTablespaceSubobjectObj.
DBBlock%ofOwnerNameObjectNameNameTypeChangesCaptureCONTENT_OWINDEX_TSMZ_AM_REQUEST_IX8INDEX347,85610.
21CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3AINDEX269,5047.
91CONTENT_OWINDEX_TSMZ_AM_REQUEST_PKINDEX251,9047.
39CONTENT_OWDATA_TSMZ_AM_REQUESTTABLE201,0565.
90CONTENT_OWINDEX_TSMZ_PRODUCT_ATTRIBUTEINDEX199,8885.
86DBBlockChanges,单位为块数*次数%Total:(该段上发生blockchanges/dbblockchanges)11-13SegmentsbyRowLockWaitsSegmentsbyRowLockWaitsDB/Inst:MAC/MAC2Snaps:70719-70723->%ofCaptureshows%ofrowlockwaitsforeachtopsegmentcompared->withtotalrowlockwaitsforallsegmentscapturedbytheSnapshot上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/RowTablespaceSubobjectObj.
Lock%ofOwnerNameObjectNameNameTypeWaitsCaptureCONTENT_OWLOB_8K_TSMZ_ASSET_WORK_EVENT_INDEX72,00543.
86CONTENT_OWLOB_8K_TSMZ_CS_WORK_NOTE_RE_I_2013_1_36INDEX13,7958.
40CONTENT_OWLOB_8K_TSMZ_CS_WORK_INFO_PART_2013_5_35INDEX12,3837.
54CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3AINDEX8,9375.
44CONTENT_OWDATA_TSMZ_AM_REQUESTTABLE8,5315.
20RowLockWaits是指行锁的等待次数数据来源于dba_hist_seg_stat.
ROW_LOCK_WAITS_DELTA11-14SegmentsbyITLWAITSSegmentsbyITLWaitsDB/Inst:MAC/MAC2Snaps:70719-70723->%ofCaptureshows%ofITLwaitsforeachtopsegmentcompared->withtotalITLwaitsforallsegmentscapturedbytheSnapshotTablespaceSubobjectObj.
ITL%ofOwnerNameObjectNameNameTypeWaitsCaptureCONTENT_OWLOB_8K_TSMZ_ASSET_WORK_EVENT_INDEX9530.
16CONTENT_OWLOB_8K_TSMZ_CS_WORK_NOTE_RE_I_2013_1_36INDEX4815.
24CONTENT_OWLOB_8K_TSMZ_CS_WORK_INFO_PART_2013_5_35INDEX216.
67CONTENT_OWINDEX_TSMZ_SALABLE_FIRST_AVAINDEX216.
67CONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE206.
35上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/关于ITL的介绍详见:http://www.
askmaclean.
com/archives/enqueue-tx-row-lock-index-itl-wait-event.
htmlITLWaits等待ITL的次数,数据来源为dba_hist_seg_stat.
itl_waits_delta11-14SegmentsbyBufferBusyWaitsSegmentsbyBufferBusyWaitsDB/Inst:MAC/MAC2Snaps:70719-70723->%ofCaptureshows%ofBufferBusyWaitsforeachtopsegmentcompared->withtotalBufferBusyWaitsforallsegmentscapturedbytheSnapshotBufferTablespaceSubobjectObj.
Busy%ofOwnerNameObjectNameNameTypeWaitsCaptureCONTENT_OWLOB_8K_TSMZ_ASSET_WORK_EVENT_INDEX251,07357.
07CONTENT_OWLOB_8K_TSMZ_CS_WORK_NOTE_RE_I_2013_1_36INDEX36,1868.
23CONTENT_OWLOB_8K_TSMZ_CS_WORK_INFO_PART_2013_5_35INDEX31,7867.
23CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3AINDEX15,6633.
56CONTENT_OWINDEX_TSMZ_CS_WORK_PENDING_RINDEX11,0872.
52BufferBusyWaits该数据段上发生bufferbusywait的次数数据来源dba_hist_seg_stat.
buffer_busy_waits_delta11-15SegmentsbyGlobalCacheBufferSegmentsbyGlobalCacheBufferBusyDB/Inst:MAC/MAC2Snaps:70719-7072->%ofCaptureshows%ofGCBufferBusyforeachtopsegmentcompared->withGCBufferBusyforallsegmentscapturedbytheSnapshot上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/GCTablespaceSubobjectObj.
Buffer%ofOwnerNameObjectNameNameTypeBusyCaptureCONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3INDEX2,135,52850.
07CONTENT_OWDATA_TSMZ_CONTENT_PROVIDER_TABLE652,90015.
31CONTENT_OWLOB_8K_TSMZ_ASSET_WORK_EVENT_INDEX552,16112.
95CONTENT_OWLOB_8K_TSMZ_CS_WORK_NOTE_RE_I_2013_1_36INDEX113,0422.
65CONTENT_OWLOB_8K_TSMZ_CS_WORK_INFO_PART_2013_5_35INDEX98,1342.
30GCBufferBusy数据段上发挥僧gcbufferbusy的次数,数据源dba_hist_seg_stat.
gc_buffer_busy_delta11-15SegmentsbyCRBlocksReceivedSegmentsbyCRBlocksReceivedDB/Inst:MAC/MAC2Snaps:70719-70723->TotalCRBlocksReceived:763,037->CapturedSegmentsaccountfor40.
9%ofTotalCRTablespaceSubobjectObj.
BlocksOwnerNameObjectNameNameTypeReceived%TotalCONTENT_OWDATA_TSMZ_AM_REQUESTTABLE69,1009.
06CONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE44,4915.
83CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3AINDEX36,8304.
83CONTENT_OWDATA_TSMZ_PODCASTTABLE36,6324.
80上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/CONTENT_OWINDEX_TSMZ_AM_REQUEST_PKINDEX19,6462.
57CRBlocksReceived:是指RAC中本地节点接收到globalcacheCRblocks的数量;数据来源为dba_hist_seg_stat.
gc_cu_blocks_received_delta%Total:(该段上在本节点接收的GlobalCRblocks/gccrblocksreceived)11-16SegmentsbyCurrentBlocksReceivedSegmentsbyCurrentBlocksReceivedDB/Inst:MAC/MAC2Snaps:70719-70723->TotalCurrentBlocksReceived:704,731->CapturedSegmentsaccountfor61.
8%ofTotalCurrentTablespaceSubobjectObj.
BlocksOwnerNameObjectNameNameTypeReceived%TotalCONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3INDEX56,2877.
99CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX3AINDEX45,1396.
41CONTENT_OWDATA_TSMZ_AM_REQUESTTABLE40,3505.
73CONTENT_OWDATA_TSMZ_CS_WORK_PENDING_RTABLE22,8083.
24CONTENT_OWINDEX_TSMZ_AM_REQUEST_IX8INDEX13,3431.
89CurrentBlocksReceived:是指RAC中本地节点接收到globalcacheCurrentblocks的数量,数据来源DBA_HIST_SEG_STAT.
gc_cu_blocks_received_delta%Total:(该段上在本节点接收的globalcachecurrentblocks/gccurrentblocksreceived)上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/12DictionaryCacheStatsDictionaryCacheStatsDB/Inst:MAC/MAC2Snaps:70719-70723->"PctMisses"shouldbeverylow("FinalUsage"isthenumberofcacheentriesbeingusedGetPctScanPctModFinalCacheRequestsMissReqsMissReqsUsagedc_awr_control872.
30N/A61dc_global_oids1,1347.
80N/A013dc_histogram_data6,119,0270.
90N/A011,784dc_histogram_defs1,898,7142.
30N/A05,462dc_object_grants17526.
90N/A04dc_objects10,254,5140.
20N/A03,807dc_profiles8,4520.
00N/A02dc_rollback_segments3,031,0440.
00N/A01,947dc_segments1,812,2431.
40N/A103,595dc_sequences15,78369.
60N/A15,78220dc_table_scns702.
90N/A01dc_tablespaces1,628,1120.
00N/A037dc_users2,037,1380.
00N/A052globaldatabasename7,6980.
00N/A01outstanding_alerts26499.
60N/A81sch_lj_oids517.
80N/A01DictionaryCache字典缓存也叫rowcache上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/数据来源为dba_hist_rowcache_summaryCache字典缓存类名kqrstcidkqrsttxtcid=3(dc_rollback_segments)GetRequests申请获取该数据字典缓存对象的次数getsMiss:GETMISSES申请获取该数据字典缓存对象但miss的次数PctMiss:GETMISSES/Gets,Miss的比例,这个pctmiss应当非常低小于2%,否则有出现大量rowcachelock的可能ScanReqs:扫描申请的次数,kqrssc、kqrpScan、kqrpsiv时发生scan会导致扫描数增加kqrstsrq++(scanre-quests),例如migratetablespace时调用kttm2b函数为了安全删除uet$中的记录会callbackkqrpsiv(usedextentcache),实际很少见PctMiss:SCANMISSES/SCANSModReqs:申请修改字典缓存对象的次数,从上面的数据可以看到dc_sequences的modreqs很高,这是因为sequence是变化较多的字典对象FinalUsage:包含有有效数据的字典缓存记录的总数也就是正在被使用的rowcache记录USAGENumberofcacheentriesthatcontainvaliddataDictionaryCacheStats(RAC)DB/Inst:MAC/MAC2Snaps:70719-70723GESGESGESCacheRequestsConflictsReleasesdc_awr_control1420dc_global_oids880102dc_histogram_defs43,518043,521dc_objects21,6081721,176dc_profiles101dc_segments24,9741424,428dc_sequences25,17810,644347dc_table_scns202dc_tablespaces1650166dc_users1190119outstanding_alerts4788250上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/sch_lj_oids404GESRequestkqrstilrtotalinstancelockrequests,通过全局队列服务GES来申请instancelock的次数GESrequest申请的原因可能是dumpcacheobject、kqrbfrLCK进程要backgroundfreesomeparentobjects释放一些parentobjects等GESConflictskqrstifrinstancelockforced-releases,LCK进程以AST方式释放锁的次数,仅出现在kqrbrl中GESReleaseskqrstisrinstancelockself-releases,LCK进程要backgroundfreesomeparentobjects释放一些parentobjects时可能自增上述数据中可以看到仅有dc_sequences对应的GESConflicts较多,对于sequence使用ordered和non-cache选项会导致RAC中的一个边际效应,即‖rowcachelock‖等待源于DC_SEQUENCESROWCACHE.
DC_SEQUENCES上的GETSrequest、modifications、GESrequests和GESconflict与引发生成一个新的se-quencenumber的特定SQL执行频率相关.
在Oracle10g中,ORDEREDSequence还可能在高并发下造成大量DFSlockHandle等待,由于bug520985913LibraryCacheActivityLibraryCacheActivityDB/Inst:MAC/MAC2Snaps:70719-70723->"PctMisses"shouldbeverylowGetPctPinPctInvali-NamespaceRequestsMissRequestsMissReloadsdationsACCOUNT_STATUS8,4360.
30N/A00BODY8,6970.
715,5370.
7490CLUSTER3174.
73214.
700DBLINK9,2120.
10N/A00EDITION4,4310.
08,6600.
000HINTSETOBJECT1,0279.
51,02714.
400上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/INDEX79218.
279218.
200QUEUE100.
01,7330.
000RULESET0N/A887.
570SCHEMA8,1690.
00N/A00SQLAREA533,4094.
8-4,246,727,944101.
144,864576SQLAREABUILD71,50065.
50N/A00SQLAREASTATS41,00890.
341,00890.
310TABLE/PROCEDURE320,3100.
61,033,9913.
625,3780TRIGGER8470.
038,4420.
31100NameSpacelibrarycache的命名空间GETSRequests该命名空间所包含对象的librarycachelock被申请的次数GETHITS对象的librarycachehandle正好在内存中被找到的次数PctMisses:(1-(GETHITS/GETSRequests))*100PinRequests该命名空间所包含对象上pin被申请的次数PINHITS要pin的对象的heapmetadata正好在sharedpool中的次数PctMiss(1-(PINHITS/PinRequests))*100Reloads指从objecthandle被重建开始不是第一次PIN该对象的PIN,且该次PIN要求对象从磁盘上读取加载的次数;Reloads值较高的情况建议增大shared_pool_sizeINVALIDATIONS由于以来对象被修改导致该命名空间所包含对象被标记为无效的次数LibraryCacheActivity(RAC)DB/Inst:MAC/MAC2Snaps:70719-70723GESLockGESPinGESPinGESInvalGESInvali-NamespaceRequestsRequestsReleasesRequestsdationsACCOUNT_STATUS8,4360000BODY015,49715,49700CLUSTER32132132100DBLINK9,2120000上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/EDITION4,4314,4314,43100HINTSETOBJECT1,0271,0271,02700INDEX79279279200QUEUE81,7331,73300RULESET08800SCHEMA4,2260000TABLE/PROCEDURE373,163704,816704,81600TRIGGER038,43038,43000GESLockRequest:dlm_lock_requestsLockinstance-lockReQuests申请获得lockinstancelock的次数GESPINrequest:DLM_PIN_REQUESTSPininstance-lockReQuests申请获得pininstancelock的次数GESPinReleasesDLM_PIN_RELEASESreleasethepininstancelock释放pininstancelock的次数GESInvalRequestsDLM_INVALIDATION_REQUESTSgettheinvalidationinstancelock申请获得invalidationinstancelock的次数GESInvali-dationsDLM_INVALIDATIONS接收到其他节点的invalidationpings次数14ProcessMemorySummaryProcessMemorySummaryDB/Inst:MAC/MAC2Snaps:70719-70723->B:BeginSnapE:EndSnap->Allrowsbelowcontainabsolutevalues(i.
e.
notdiffedovertheinterval)->MaxAllocisMaximumPGAAllocationsizeatsnapshottime->HistMaxAllocistheHistoricalMaxAllocationforstill-connectedprocesses->orderedbyBegin/Endsnapshot,Alloc(MB)descHistAvgStdDevMaxMaxAllocUsedAllocAllocAllocAllocNumNum上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/Category(MB)(MB)(MB)(MB)(MB)(MB)ProcAllocBOther16,062.
7N/A6.
166.
63,3703,3702,6122,612SQL5,412.
24,462.
92.
289.
54,4834,4832,5082,498Freeable2,116.
4.
0.
96.
3298N/A2,2662,266PL/SQL94.
069.
8.
0.
0112,6102,609EOther15,977.
3N/A6.
166.
93,3873,3872,6162,616SQL5,447.
94,519.
02.
289.
84,5054,5052,5142,503Freeable2,119.
9.
0.
96.
3297N/A2,2732,273PL/SQL93.
269.
2.
0.
0112,6142,613数据来源为dba_hist_process_mem_summary,这里是对PGA使用的一个小结,帮助我们了解到底谁用掉了PGAB:开始快照E:结束快照该环节列出PGA中各分类的使用量Category分类名,包括‖SQL‖,―PL/SQL‖,―OLAP‖和‖JAVA‖.
特殊分类是―Freeable‖和‖Other‖.
Freememory是指哪些OS已经分配给进程,但没有分配给任何分类的内存.
―Other‖是已经分配给分类的内存,但不是已命名的分类Alloc(MB)allocated_total该分类被分配的总内存Used(MB)used_total该分类已使用的内存AvgAlloc(MB)allocated_avg平均每个进程中该分类分配的内存量StdDevAlloc(MB):该分类分配的内存在每个进程之间的标准差MaxAlloc(MB)ALLOCATED_MAX:在快照时间内单个进程该分类最大分配过的内存量:MaxAllocisMaximumPGAAllocationsizeatsnapshottimeHistMaxAlloc(MB)MAX_ALLOCATED_MAX:目前仍链接着的进程该分类最大分配过的内存量:HistMaxAllocistheHistoricalMaxAllocationforstill-connectedprocessesNumProcnum_processes进程数目NumAllocNON_ZERO_ALLOCS分配了该类型内存的进程数目14SGA信息14-1SGAMemorySummarySGAMemorySummaryDB/Inst:MAC/MAC2Snaps:70719-70723上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/EndSize(Bytes)SGAregionsBeginSize(Bytes)(ifdifferent)DatabaseBuffers20,669,530,112FixedSize2,241,880RedoBuffers125,669,376VariableSize10,536,094,376sum31,333,535,744粗粒度的sga区域内存使用信息,EndSize仅在于beginsize不同时打印14-2SGAbreakdowndifferenceSGAbreakdowndifferenceDB/Inst:MAC/MAC2Snaps:70719-70723->orderedbyPool,Name->N/AvalueforBeginMBorEndMBindicatesthesizeofthatPool/Namewasinsignificant,orzerointhatsnapshotPoolNameBeginMBEndMB%Diffjavafreememory64.
064.
00.
00largePXmsgpool7.
87.
80.
00largefreememory247.
8247.
80.
00sharedCheckpointqueue140.
6140.
60.
00上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/sharedFileOpenBlock2,459.
22,459.
20.
00sharedKGH:NOACCESS1,629.
61,629.
60.
00sharedKGLH0997.
7990.
5-0.
71sharedKKSSP312.
2308.
9-1.
06sharedSQLA376.
6370.
6-1.
61shareddb_block_hash_buckets178.
0178.
00.
00shareddbktb:tracebuffer156.
3156.
30.
00sharedeventstatisticspersess187.
1187.
10.
00sharedfreememory1,208.
91,220.
60.
97sharedgcsresources435.
0435.
00.
00sharedgcsshadows320.
6320.
60.
00sharedgesenqueues228.
9228.
90.
00sharedgesresource118.
3118.
30.
00sharedinit_heap_kfsg1,063.
61,068.
10.
43sharedkglsimobjectbatch124.
3124.
30.
00sharedksunfy:SSOfreelist174.
7174.
70.
00streamfreememory128.
0128.
00.
00buffer_cache19,712.
019,712.
00.
00fixed_sga2.
12.
10.
00log_buffer119.
8119.
80.
00Pool内存池的名字Name内存池中细分组件的名字例如KGLH0存放KELHeap0、SQLA存放SQL执行计划等BeginMB快照开始时该组件的内存大小EndMB快照结束时该组件的内存大小%Diff差异百分比特别注意由于AMM/ASMM引起的sharedpool收缩一般在sgabreakdown中可以提现例如SQLA、KQR等组件大幅缩小,可能导致一系列的解析等待cursor:PinSonX、rowcachelock等上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/此处的freememory信息也值得我们关注,一般推荐sharedpool应当有300~400MB的freememory为宜15Streams统计StreamsCPU/IOUsageDB/Inst:ORCL/orcl1Snaps:556-559->StreamsprocessesorderedbyCPUusage->CPUandI/OTimeinmicrosecondsSessionTypeCPUTimeUserI/OTimeSysI/OTimeQMONCoordinator101,69800QMONSlaves63,85600StreamsCaptureDB/Inst:CATGT/catgtSnaps:911-912->LagChangeshouldbesmallornegative(inseconds)CapturedEnqueuedPctPctPctPctPerPerLagRuleEvalEnqueueRedo-WaitPauseCaptureNameSecondSe-condChangeTimeTimeTimeTimeCAPTU-TU-RE_CAT65039193023071上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/StreamsApplyDB/Inst:CATGT/catgtSnaps:911-912->PctDBisthepercentageofallDBtransactionsthatthisapplyhandled->WDEPisthewaitfordependency->WCMTisthewaitforcommit->RBKisrollbacks->MPSismessagespersecond->TPMistimepermessageinmilli-seconds->LagChangeshouldbesmallornegative(inseconds)AppliedPctPctPctPctAppliedDequeueApplyLagApplyNameTPSDBWDEPWCMTRBKMPSTPMTPMChangeAP-PLY_CAT000000000CaptureName:Streams捕获进程名CapturedPerSecond:每秒挖掘出来的message条数EnqueuedPerSecond:每秒入队的message条数lagchange:指日志生成的时间到挖掘到该日志生成message的时间延迟PctEnqueueTime:入队时间的比例PctredoWaitTime:等待redo的时间比例PctPauseTime:Pause时间的比例ApplyNameStreams应用Apply进程的名字AppliedTPS:每秒应用的事务数PctDB:所有的DB事务中apply处理的比例PctWDEP:由于等待依赖的数据而耗费的时间比例PctWCMT:由于等待commit而耗费的时间比例PctRBK:事务rollback回滚的比例AppliedMPS:每秒应用的message数DequeueTPM:每毫秒出队的message数上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/LagChange:指最新message生成的时间到其被Apply收到的延迟16ResourceLimitResourceLimitStatsDB/Inst:MAC/MAC2Snap:70723->onlyrowswithCurrentorMaximumUtilization>80%ofLimitareshown->orderedbyresourcenameCurrentMaximumInitialResourceNameUtilizationUtilizationAllocationLimitges_procs2,6128,0071000310003processes2,6158,0111000010000数据源于dba_hist_resource_limit注意这里仅列出当前使用或最大使用量>80%*最大限制的资源名,如果没有列在这里则说明资源使用量安全CurrentUtilization当前对该资源(包括EnqueueResource、Lock和processes)的使用量MaximumUtilization从最近一次实例启动到现在该资源的最大使用量InitialAllocation初始分配值,一般等于参数文件中指定的值Limit实际上限值17init.
oraParametersinit.
oraParametersDB/Inst:MAC/MAC2Snaps:70719-70723EndvalueParameterNameBeginvalue(ifdifferent)_compression_compatibility11.
2.
0上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/_kghdsidx_count4_ksmg_granule_size67108864_shared_pool_reserved_min_all4100archive_lag_target900audit_file_dest/u01/app/oracle/admin/MAC/adumaudit_trailOScluster_databaseTRUEcompatible11.
2.
0.
2.
0control_files+DATA/MAC/control01.
ctl,+RECOdb_16k_cache_size268435456db_block_size8192db_cache_size19327352832db_create_file_dest+DATAParameterName参数名Beginvalue开始快照时的参数值Endvalue结束快照时的参数值(仅在发生变化时打印)18GlobalMessagingStatisticsGlobalMessagingStatisticsDB/Inst:MAC/MAC2Snaps:70719-70723StatisticTotalperSecondperTransacksforcommitbroadcast(actual)53,70514.
90.
2acksforcommitbroadcast(logical311,18286.
11.
3broadcastmsgsoncommit(actual)317,08287.
71.
3broadcastmsgsoncommit(logical)317,08287.
71.
3上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/broadcastmsgsoncommit(wasted)263,33272.
91.
1dynamicallyallocatedgcsresourc00.
00.
0dynamicallyallocatedgcsshadows00.
00.
0flowcontrolmessagesreceived2670.
10.
0flowcontrolmessagessent1270.
00.
0gcsapplydelta00.
00.
0gcsassumecvt55,54115.
40.
2全局通信统计信息,数据来源WRH$_DLM_MISC;20GlobalCRServedStatsGlobalCRServedStatsDB/Inst:MAC/MAC2Snaps:70719-70723StatisticTotalCRBlockRequests403,703CURRENTBlockRequests444,896DataBlockRequests403,705UndoBlockRequests94,336TXBlockRequests307,896CurrentResults652,746Privateresults21,057ZeroResults104,720DiskReadResults69,418FailResults508FairnessDownConverts102,844FairnessClears15,207上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/FreeGCElements0Flushes105,052FlushesQueued0FlushQueueFull0FlushMaxTime(us)0LightWorks71,793Errors117LMS传输CRBLOCK的统计信息,数据来源WRH$_CR_BLOCK_SERVER21GlobalCURRENTServedStatsGlobalCURRENTServedStatsDB/Inst:MAC/MAC2Snaps:70719-70723->Pins=CURRENTBlockPinOperations->Flushes=RedoFlushbeforeCURRENTBlockServedOperations->Writes=CURRENTBlockFusionWriteOperationsStatisticTotal%Immediate(Immed)-BlockTransferNOTimpactedbyRemoteProcessingDelays->Busy(Busy)-BlockTransferimpactedbyRemoteContention->Congested(Congst)-BlockTransferimpactedbyRemoteSystemLoad->orderedbyCR+CurrentBlocksReceiveddescCRCurrentInstBlockBlocks%%%Blocks%%%NoClassReceivedImmedBusyCongstReceivedImmedBusyCongst1datablock133,18776.
322.
61.
1233,13875.
223.
01.
74datablock143,16574.
124.
91.
0213,20476.
621.
81.
63datablock122,76175.
923.
01.
1220,02377.
721.
01.
31undoheader104,21995.
73.
21.
194193.
45.
8.
74undoheader95,82395.
23.
71.
180993.
45.
31.
23undoheader95,59295.
63.
31.
191294.
64.
5.
9上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/1undoblock25,00295.
83.
4.
90N/AN/AN/A4undoblock23,30396.
03.
1.
90N/AN/AN/A3undoblock21,67295.
43.
7.
90N/AN/AN/A1Others1,90992.
06.
81.
26,05789.
68.
91.
54Others1,73692.
46.
11.
55,84188.
89.
91.
33Others1,50092.
45.
91.
74,40587.
710.
81.
6数据来源DBA_HIST_INST_CACHE_TRANSFERInstNo节点号BlockClass块的类型CRBlocksReceived该节点上该类型CR块的接收数量CRImmed%:CR块请求立即接收到的比例CRBusy%:CR块请求由于远端争用而没有立即接收到的比例CRCongst%:CR块请求由于远端负载高而没有立即接收到的比例CurrentBlocksReceived该节点上该类型Current块的接收数量CurrentImmed%:Current块请求立即接收到的比例CurrentBusy%:Current块请求由于远端争用而没有立即接收到的比例CurrentCongst%:Current块请求由于远端负载高而没有立即接收到的比例Congst%的比例应当非常低不高于2%,Busy%很大程度受到IO的影响,如果超过10%一般会有严重的gcbufferbusyacquire/release参考文档StatisticsDescriptionshttp://docs.
oracle.
com/cd/B19306_01/server.
102/b14237/stats002.
htmMemoryConfigurationandUsehttp://docs.
oracle.
com/cd/B19306_01/server.
102/b14211/memory.
htmLibraryCacheHit(%)http://docs.
oracle.
com/cd/B16240_01/doc/doc.
102/e16282/oracle_database_help/oracle_database_instance_efficiency_libcache_hit_pct.
htmlOracleDatabasePerformanceTuningGuide12cRelease1(12.
1)HowtoInterpretthe―SQLorderedbyPhysicalReads(UnOptimized)‖SectioninAWRReports(11.
2onwards)[ID1466035.
1]2013,www.
askmaclean.
com.
版权所有.
文章允许转载,但必须以链接方式注明源地址,否则追求法律责任.
上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/作者个人简介SHOUG成员-刘相兵(MacleanLiu)刘相兵-网名MacleanLiuShanghaiOracleUsersGroup(SHOUG)创始人之一邮箱和Gtalk均是:liu.
maclean@gmail.
com个人技术博客地址:http://www.
askmaclean.
comMacleanLiu拥有7年数据库领域从业经验,目前在甲骨文公司中国上海工作.
主要客户包括:工商银行、交通银行、外汇交易中心、中国人寿保险、浦发银行、上海银行、人民银行、银联数据、银商资讯、太平人寿保险上海电信、上海联通、上海移动、浙江电信、湖北电信、湖北联通、湖北移动、江苏移动、德邦物流MacleanLiu对研究Oracle内部原理、新特性、高可用和性能调优饶有兴趣,同时撰写了大量Oracle数据库技术相关的专题文章.
MacleanLiu愿意通过网络同大家分享Oracle知识和经验,也愿意在能力范围能帮助大家解决实际工作中上海Oracle用户组--SHOUG--ShangHaiOracleUsersGrouphttp://www.
shoug.
info/遇到的问题.