9-1 Undo Segment Summary

Undo Segment Summary Snaps: 70719-70723-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)-> STO - Snapshot Too Old count, OOS - Out of Space count-> Undo segment block stats:-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed-> eS - expired Stolen, eR - expired Released, eU - expired reUsedUndo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concurcy TR (mins) OOS eS/eR/eU---- ---------- --------------- -------- -------- --------- ----- -------------- 4 85.0 200,127 55,448 317 1040.2/10 0/0 0/0/0/0/0/0 -------------------------------------------------------------Undo Segment Stats Snaps: 70719-70723-> Most recent 35 Undostat rows, ordered by Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU------------ ----------- ------------ ------- ------- ------- ----- ------------29-Aug 05:52 11,700 35,098 55,448 234 1,070 0/0 0/0/0/0/0/029-Aug 05:42 12,203 24,677 54,844 284 1,065 0/0 0/0/0/0/0/029-Aug 05:32 14,132 37,826 54,241 237 1,060 0/0 0/0/0/0/0/029-Aug 05:22 14,379 32,315 53,637 317 1,050 0/0 0/0/0/0/0/029-Aug 05:12 15,693 34,157 53,033 299 1,045 0/0 0/0/0/0/0/029-Aug 05:02 16,878 36,054 52,428 250 1,040 0/0 0/0/0/0/0/0

数据来源： WRH$_UNDOSTAT ， undo相关的使用信息每10分钟刷新到v$undostat中
Undo Extent有三种状态 active 、unexpired 、expiredactive => extent中 包括了活动的事务 ，active的undo extent 一般不允许被其他事务重用覆盖unexpired => extent中没有活动的事务，但相关undo 记录从inactive到目前还未经过undo retention(注意 auto undo retention的问题 因为这个特性 可能在观察dba_undo_extents时看到大部分block都是unexpired，这是正常的) 指定的时间，所以为unexpired。 对于没有guarantee retention的undo tablespace而言，unexpired extent可能被 steal 为其他事物重用expired => extent中没有活动事务，且超过了undo retention的时间
Undo TS# 在使用的这个undo 表空间的表空间号， 一个实例 同一时间只能用1个undo tablespace ， RAC不同节点可以用不同的undo tablespaceNum Undo Blocks (K) 指被消费的 undo 数据块的数量, (K)代表要乘以1000才是实际值； 可以用该指标来评估系统对undo block的消费量， 以便基于实际负载情况来评估UNDO表空间的大小Number of Transactions 指该段时间内该undo表空间上执行过的事务transaction总量Max Qry Len (s) 该时段内 持续最久的查询 时间， 单位为秒Max Tx Concy 该时段内 最大的事务并发量Min/Max TR (mins) 最小和最大的tuned undo retention ，单位为分钟； tuned undo retention 是自动undo调优特性，见undo自动调优介绍。STO/ OOS STO 指 ORA-01555 Snapshot Too Old错误出现的次数； OOS – 指Out of Space count 错误出现的次数uS – unexpired Stolen 尝试从未过期的undo extent中偷取undo space的次数uR – unexpired Released 从未过期的undo extent中释放的块数目uU – unexpired reUsed 未过期的undo extent中的block被其他事务重用的 块数目eS – expired Stolen 尝试从过期的undo extent中偷取undo space的次数eR – expired Released 从过期的undo extent中释放的块数目eU – expired reUsed 过期的undo extent中的block被其他事务重用的 块数目

[table=98%]
UNXPSTEALCNTNUMBERNumber of attempts to obtain undo space by stealing unexpired extents from other transactions
UNXPBLKRELCNTNUMBERNumber of unexpired blocks removed from certain undo segments so they can be used by other transactions
UNXPBLKREUCNTNUMBERNumber of unexpired undo blocks reused by transactions
EXPSTEALCNTNUMBERNumber of attempts to steal expired undo blocks from other undo segments
EXPBLKRELCNTNUMBERNumber of expired undo blocks stolen from other undo segments
EXPBLKREUCNTNUMBERNumber of expired undo blocks reused within the same undo segments
SSOLDERRCNTNUMBERIdentifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.

10-1 Latch Activity

Latch Activity Snaps: 70719-70723-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests-> "Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWaitLatch Name Requests Miss /Miss (s) Requests Miss------------------------ -------------- ------ ------ ------ ------------ ------AQ deq hash table latch 4 0.0 0 0 N/AASM Keyed state latch 9,048 0.1 0.2 0 0 N/AASM allocation 15,017 0.2 0.8 1 0 N/AASM db client latch 72,745 0.0 0 0 N/AASM map headers 5,860 0.6 0.6 1 0 N/AASM map load waiting lis 1,462 0.0 0 0 N/AASM map operation freeli 63,539 0.1 0.4 1 0 N/AASM map operation hash t 76,484,447 0.1 1.0 66 0 N/A
latch name Latch闩的名字Get Requests latch被以willing-to-wait模式申请并获得的次数Pct Get Miss miss是指latch被以willing-to-wait 模式申请但是申请者必须等待的次数， Pct Get Miss = Miss/Get Requests ; miss可以从后面的Latch Sleep Breakdown 获得Avg Slps /Miss Sleep 是指latch被以willing-to-wait模式申请最终导致session需要sleep以等待该latch的次数 ； Avg Slps /Miss = Sleeps/ Misses ; Sleeps可以从后面的Latch Sleep Breakdown 获得Wait Time (s) 指花费在等待latch上的时间，单位为秒
NoWait Requests 指latch被以no-wait模式来申请的次数Pct NoWait Miss 以no-wait模式来申请latch但直接失败的次数对于高并发的latch例如cache buffers chains，其Pct Misses应当十分接近于0一般的调优原则：如果latch : cache buffers chains是 Top 5 事件，则需要考虑优化SQL减少 全表扫描 并减少Top buffer gets SQL语句的逻辑读如果latch : redo copy 、redo allocation 等待较多，则可以考虑增大LOG_BUFFER如果latch:library cache 发生较多，则考虑增大shared_pool_size

10-2 Latch Sleep Breakdown

Latch Sleep Breakdown DB/Inst: ITSCMP/itscmp2 Snaps: 70719-70723-> ordered by misses desc Get SpinLatch Name Requests Misses Sleeps Gets-------------------------- --------------- ------------ ----------- -----------cache buffers chains 3,365,097,866 12,831,875 130,058 12,683,450row cache objects 69,050,058 349,839 1,320 348,649session idle bit 389,437,460 268,285 2,768 265,752enqueue hash chains 8,698,453 239,880 22,476 219,950ges resource hash list 8,388,730 158,894 70,728 91,104gc element 100,383,385 135,759 6,285 129,742gcs remastering latch 12,213,169 72,373 1 72,371enqueues 4,662,545 46,374 259 46,155ASM map operation hash tab 76,484,447 46,231 45,210 1,952Lsod array latch 72,598 24,224 24,577 1,519

latch name Latch闩的名字Get Requests latch被以willing-to-wait模式申请并获得的次数misses 是指latch被以willing-to-wait 模式申请但是申请者必须等待的次数9i以后miss之后一般有2种情况 spin gets了 或者sleep一睡不醒直到 被post，具体见全面解析9i以后Oracle Latch闩锁原理；8i以前的latch算法可以参考：Oracle Latch:一段描绘Latch运作的伪代码所以一般来说9i以后的 misses= Sleeps+ Spin Gets ，虽然不是绝对如此Sleeps 是指latch被以willing-to-wait模式申请最终导致session需要sleep以等待该latch的次数Spin Gets 以willing-to-wait模式去申请latch，在miss之后以spin方式获得了latch的次数

10-3 Latch Miss Sources

Latch Miss Sources Snaps: 70719-70723-> only latches with sleeps are shown-> ordered by name, sleeps desc NoWait WaiterLatch Name Where Misses Sleeps Sleeps------------------------ -------------------------- ------- ---------- --------ASM Keyed state latch kfksolGet 0 1 1ASM allocation kfgpnSetDisks2 0 17 0ASM allocation kfgpnClearDisks 0 5 0ASM allocation kfgscCreate 0 4 0ASM allocation kfgrpGetByName 0 1 26ASM map headers kffmUnidentify_3 0 7 8ASM map headers kffmAllocate 0 6 0ASM map headers kffmIdentify 0 6 11ASM map headers kffmFree 0 1 0ASM map operation freeli kffmTranslate2 0 15 8ASM map operation hash t kffmUnidentify 0 44,677 36,784ASM map operation hash t kffmTranslate 0 220 3,517
数据来源为DBA_HIST_LATCH_MISSES_SUMMARY
latch name Latch闩的名字
where : 指哪些代码路径内核函数持有过这些该latch ，而不是哪些代码路径要申请这些latch； 例如kcbgtcr函数的作用是Get a block for Consistent read，其持有latch :cache buffers chain是很正常的事情NoWait Misses: 以no-wait模式来申请latch但直接失败的次数Sleeps: 指latch被以willing-to-wait模式申请最终导致session需要sleep以等待该latch的次数 time of sleeps resulted in making the latch requestWaiter Sleeps：等待者休眠的次数 times of sleeps that waiters did for each where; Sleep 是阻塞者等待的次数 ， Waiter Sleeps是被阻塞者等待的次数

10-4 Mutex Sleep Summary
Mutex Sleep Summary Snaps: 70719-70723-> ordered by number of sleeps desc WaitMutex Type Location Sleeps Time (ms)--------------------- -------------------------------- ------------ ------------Cursor Pin kksfbc [KKSCHLFSP2] 4,364 14,520Cursor Pin kkslce [KKSCHLPIN2] 2,396 2,498Library Cache kglpndl1 95 903 475Library Cache kglpin1 4 800 458Library Cache kglpnal2 91 799 259Library Cache kglget1 1 553 1,697Library Cache kglpnal1 90 489 88Library Cache kgllkdl1 85 481 1,528Cursor Pin kksLockDelete [KKSCHLPIN6] 410 666Cursor Stat kkocsStoreBindAwareStats [KKSSTA 346 497Library Cache kglhdgn2 106 167 348Library Cache kglhdgh1 64 26 84Library Cache kgldtin1 42 19 55Cursor Pin kksfbc [KKSCHLPIN1] 13 34Library Cache kglhdgn1 62 11 13Library Cache kgllkal1 80 9 12Library Cache kgllkc1 57 6 0Cursor Pin kksSetBindType [KKSCHLPIN3] 5 5Library Cache kglGetHandleReference 124 4 20Library Cache kglUpgradeLock 119 4 0Library Cache kglget2 2 3 0Library Cache kglati1 45 1 0Library Cache kglini1 32 1 0Library Cache kglobld1 75 1 0Library Cache kglobpn1 71 1 0

Mutex是10.2.0.2以后引入的新的内存锁机制，具体对Mutex的描述见 《深入理解Oracle中的Mutex》：http://www.askmaclean.com/archives/understanding-oracle-mutex.htmlMutex TypeMutex的类型其实就是 mutex对应的客户的名字， 在版本10.2中基本只有KKS使用Mutex，所以仅有3种:[list]
[*]Cursor Stat (kgx_kks1)
[*]Cursor Parent (kgx_kks2)
[*]Cursor Pin (kgx_kks3)
11g中增加了Library Cache
Location 发起对该Mutex申请的代码路径code location，而不是还持有该Mutex的代码路径或曰内核函数10.2中最常见的下面的几个函数kkspsc0 -负责解析游标 – 检测我们正在解析的游标是否有对象的parent cursor heap 0存在kksfbc – 负责找到合适的子游标 或者创建一个新的子游标kksFindCursorstat
Sleeps:Mutex的Get和Sleep当一个Mutex被申请时， 一般称为一个get request。 若初始的申请未能得到授权， 则该进程会因为此次申请而进入到255次SPIN中(_mutex_spin_count Mutex spin count)，每次SPIN循环迭代过程中该进程都会去看看Mutex被释放了吗。若该Mutex在SPIN之后仍未被释放，则该进程针对申请的mutex进入对应的mutex wait等待事件中。 实际进程的等待事件和等待方式由mutex的类型锁决定，例如 Cursor pin、Cursor Parent。 举例来说，这种等待可能是阻塞等待，也可以是sleep。但是请注意在V$MUTEX_SLEEP_*视图上的sleep列意味着等待的次数。相关代码函数在开始进入等待时自加这个sleep字段。等待计时从进程进入等待前开始计算等待时间， 当一个进程结束其等待，则等待的时间加入都总和total中。 该进程再次尝试申请之前的Mutex，若该Mutex仍不可用，则它再次进入spin/wait的循环。
V$MUTEX_SLEEP_HISTORY视图的GETS列仅在成功申请到一个Mutex时才增加。
Wait Time (ms) 类似于latch，spin time 不算做mutex的消耗时间，它只包含等待消耗的时间。
=====================================================================
11 segment statistics 段级统计
11-1 Segments by Logical Reads

Segments by Logical Reads DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Logical Reads: 2,021,476,421-> Captured Segments account for 83.7% of Total Tablespace Subobject Obj. LogicalOwner Name Object Name Name Type Reads %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW INDEX_TS MZ_PRODUCT_ATTRIBUTE INDEX 372,849,920 18.44CONTENT_OW INDEX_TS MZ_PRODUCT__LS_PK INDEX 329,829,632 16.32CONTENT_OW DATA_TS MZ_PRODUCT_ATTRIBUTE TABLE 218,419,008 10.80CONTENT_OW PLAYLIST_A MZ_PLAYLIST_ARTIST TABLE 182,426,240 9.02CONTENT_OW DATA_TS MZ_PRODUCT TABLE 108,597,376 5.37owner : 数据段的所有者Tablespace Name: 数据段所在表空间名Object Name : 对象名Subobject Name：子对象名，例如一个分区表的某个分区obj Type: 对象类型 一般为TABLE /INDEX 或者分区或子分区Logical Reads ：该数据段上发生过的逻辑读 ， 单位为 块数*次数%Total : 占总的逻辑读的百分比 ， (当前对象上发生过的逻辑读/ Total DB 逻辑读)

11-2 Segments by Physical Reads

Segments by Physical Reads DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Physical Reads: 56,839,035-> Captured Segments account for 51.9% of Total Tablespace Subobject Obj. PhysicalOwner Name Object Name Name Type Reads %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW SONG_TS MZ_SONG TABLE 7,311,928 12.86CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 4,896,554 8.61CONTENT_OW DATA_TS MZ_CONTENT_PROVIDER_ TABLE 3,099,387 5.45CONTENT_OW DATA_TS MZ_PRODUCT_ATTRIBUTE TABLE 1,529,971 2.69CONTENT_OW DATA_TS MZ_PUBLICATION TABLE 1,391,735 2.45
Physical Reads: 该数据段上发生过的物理读 ， 单位为 块数*次数%Total : 占总的物理读的百分比 ， (当前对象上发生过的逻辑读/ Total DB 逻辑读)

11-3 Segments by Physical Read Requests

Segments by Physical Read Requests DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Physical Read Requests: 33,936,360-> Captured Segments account for 45.5% of Total Tablespace Subobject Obj. Phys ReadOwner Name Object Name Name Type Requests %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW DATA_TS MZ_CONTENT_PROVIDER_ TABLE 3,099,346 9.13CONTENT_OW DATA_TS MZ_PRODUCT_ATTRIBUTE TABLE 1,529,950 4.51CONTENT_OW DATA_TS MZ_PRODUCT TABLE 1,306,756 3.85CONTENT_OW DATA_TS MZ_AUDIO_FILE TABLE 910,537 2.68CONTENT_OW INDEX_TS MZ_PRODUCT_ATTRIBUTE INDEX 820,459 2.42

Phys Read Requests ： 物理读的申请次数%Total ：　(该段上发生的物理读的申请次数/ physical read IO requests)

11-4 Segments by UnOptimized Reads

Segments by UnOptimized Reads DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total UnOptimized Read Requests: 811,466-> Captured Segments account for 58.5% of Total Tablespace Subobject Obj. UnOptimizedOwner Name Object Name Name Type Reads %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW DATA_TS MZ_CONTENT_PROVIDER_ TABLE 103,580 12.76CONTENT_OW SONG_TS MZ_SONG TABLE 56,946 7.02CONTENT_OW DATA_TS MZ_IMAGE TABLE 47,017 5.79CONTENT_OW DATA_TS MZ_PRODUCT_ATTRIBUTE TABLE 40,950 5.05CONTENT_OW DATA_TS MZ_PRODUCT TABLE 30,406 3.75
UnOptimized Reads UnOptimized Read Reqs = Physical Read Reqts – Optimized Read Reqs
Optimized Read Requests是指 哪些满足Exadata Smart Flash Cache ( or the Smart Flash Cache in OracleExadata V2 (Note that despite same name, concept and use of
‘Smart Flash Cache’ in Exadata V2 is different from ‘Smart Flash Cache’ in Database Smart Flash Cache)).的物理读 次数 。 满足从smart flash cache走的读取申请呗认为是optimized ，因为这些读取要比普通从磁盘走快得多。此外通过smart scan 读取storage index的情况也被认为是’optimized read requests’ ，源于可以避免读取不相关的数据。当用户不在使用Exadata时，则UnOptimized Read Reqs总是等于 Physical Read Reqts%Total : (该段上发生的物理读的UnOptimized Read Reqs / ( physical read IO requests – physical read requests optimized ))
11-5 Segments by Optimized Reads

Segments by Optimized Reads DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Optimized Read Requests: 33,124,894-> Captured Segments account for 45.2% of Total Tablespace Subobject Obj. OptimizedOwner Name Object Name Name Type Reads %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW DATA_TS MZ_CONTENT_PROVIDER_ TABLE 2,995,766 9.04CONTENT_OW DATA_TS MZ_PRODUCT_ATTRIBUTE TABLE 1,489,000 4.50CONTENT_OW DATA_TS MZ_PRODUCT TABLE 1,276,350 3.85CONTENT_OW DATA_TS MZ_AUDIO_FILE TABLE 890,775 2.69CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3 INDEX 816,067 2.46
关于optimizerd read 上面已经解释过了，这里的单位是 request 次数%Total : (该段上发生的物理读的 Optimized Read Reqs/ physical read requests optimized )

11-6 Segments by Direct Physical Reads
Segments by Direct Physical Reads DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Direct Physical Reads: 14,118,552-> Captured Segments account for 94.2% of Total Tablespace Subobject Obj. DirectOwner Name Object Name Name Type Reads %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW SONG_TS MZ_SONG TABLE 7,084,416 50.18CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 4,839,984 34.28CONTENT_OW DATA_TS MZ_PUBLICATION TABLE 1,361,133 9.64CONTENT_OW DATA_TS SYS_LOB0000203660C00 LOB 5,904 .04CONTENT_OW DATA_TS SYS_LOB0000203733C00 LOB 1,656 .01

Direct reads 直接路径物理读，单位为 块数*次数
%Total (该段上发生的direct path reads /Total physical reads direct )

11-7 Segments by Physical WritesSegments by Physical Writes DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Physical Writes: 590,563-> Captured Segments account for 38.3% of Total Tablespace Subobject Obj. PhysicalOwner Name Object Name Name Type Writes %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 23,595 4.00CONTENT_OW DATA_TS MZ_PODCAST TABLE 19,834 3.36CONTENT_OW INDEX_TS MZ_IMAGE_IX2 INDEX 16,345 2.77SYS SYSAUX WRH$_ACTIVE_SESSION_ 1367_70520 TABLE 14,173 2.40CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3 INDEX 9,645 1.63Physical Writes ，物理写 单位为 块数*次数Total % (该段上发生的物理写 /Total physical writes )

11-9 Segments by Physical Write Requests
Segments by Physical Write Requests DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Physical Write Requestss: 436,789-> Captured Segments account for 43.1% of Total Tablespace Subobject Obj. Phys WriteOwner Name Object Name Name Type Requests %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 22,581 5.17CONTENT_OW DATA_TS MZ_PODCAST TABLE 19,797 4.53CONTENT_OW INDEX_TS MZ_IMAGE_IX2 INDEX 14,529 3.33CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3 INDEX 9,434 2.16CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 8,618 1.97
Phys Write Requests 物理写的请求次数 ，单位为次数%Total (该段上发生的物理写请求次数 /physical write IO requests )

11-10 Segments by Direct Physical Writes

Segments by Direct Physical Writes DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Direct Physical Writes: 29,660-> Captured Segments account for 18.3% of Total Tablespace Subobject Obj. DirectOwner Name Object Name Name Type Writes %Total---------- ---------- -------------------- ---------- ----- ------------ -------SYS SYSAUX WRH$_ACTIVE_SESSION_ 1367_70520 TABLE 4,601 15.51CONTENT_OW DATA_TS SYS_LOB0000203733C00 LOB 620 2.09CONTENT_OW DATA_TS SYS_LOB0000203660C00 LOB 134 .45CONTENT_OW DATA_TS SYS_LOB0000203779C00 LOB 46 .16CONTENT_OW DATA_TS SYS_LOB0000203796C00 LOB 41 .14

Direct Writes 直接路径写， 单位额为块数*次数
%Total 为(该段上发生的直接路径写 /physical writes direct )

11-11 Segments by Table Scans

Segments by Table Scans DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Table Scans: 10,713-> Captured Segments account for 1.0% of Total Tablespace Subobject Obj. TableOwner Name Object Name Name Type Scans %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW DATA_TS MZ_PUBLICATION TABLE 92 .86CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 14 .13CONTENT_OW SONG_TS MZ_SONG TABLE 3 .03CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 1 .01

Table Scans 来源为dba_hist_seg_stat.table_scans_delta 不过这个指标并不十分精确
11-12 Segments by DB Blocks Changes

Segments by DB Blocks Changes DB/Inst: MAC/MAC2 Snaps: 70719-70723-> % of Capture shows % of DB Block Changes for each top segment compared-> with total DB Block Changes for all segments captured by the Snapshot Tablespace Subobject Obj. DB Block % ofOwner Name Object Name Name Type Changes Capture---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX8 INDEX 347,856 10.21CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 269,504 7.91CONTENT_OW INDEX_TS MZ_AM_REQUEST_PK INDEX 251,904 7.39CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 201,056 5.90CONTENT_OW INDEX_TS MZ_PRODUCT_ATTRIBUTE INDEX 199,888 5.86DB Block Changes ，单位为块数*次数%Total : (该段上发生block changes / db block changes )

11-13 Segments by Row Lock Waits

Segments by Row Lock Waits DB/Inst: MAC/MAC2 Snaps: 70719-70723-> % of Capture shows % of row lock waits for each top segment compared-> with total row lock waits for all segments captured by the Snapshot Row Tablespace Subobject Obj. Lock % ofOwner Name Object Name Name Type Waits Capture---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW LOB_8K_TS MZ_ASSET_WORK_EVENT_ INDEX 72,005 43.86CONTENT_OW LOB_8K_TS MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX 13,795 8.40CONTENT_OW LOB_8K_TS MZ_CS_WORK_INFO_PART _2013_5_35 INDEX 12,383 7.54CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 8,937 5.44CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 8,531 5.20

Row Lock Waits 是指行锁的等待次数 数据来源于 dba_hist_seg_stat.ROW_LOCK_WAITS_DELTA

11-14 Segments by ITL WAITS
Segments by ITL Waits DB/Inst: MAC/MAC2 Snaps: 70719-70723-> % of Capture shows % of ITL waits for each top segment compared-> with total ITL waits for all segments captured by the Snapshot Tablespace Subobject Obj. ITL % ofOwner Name Object Name Name Type Waits Capture---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW LOB_8K_TS MZ_ASSET_WORK_EVENT_ INDEX 95 30.16CONTENT_OW LOB_8K_TS MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX 48 15.24CONTENT_OW LOB_8K_TS MZ_CS_WORK_INFO_PART _2013_5_35 INDEX 21 6.67CONTENT_OW INDEX_TS MZ_SALABLE_FIRST_AVA INDEX 21 6.67CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 20 6.35
关于 ITL的介绍详见： http://www.askmaclean.com/archives/enqueue-tx-row-lock-index-itl-wait-event.htmlITL Waits 等待 ITL 的次数，数据来源为 dba_hist_seg_stat.itl_waits_delta

11-14 Segments by Buffer Busy Waits
Segments by Buffer Busy Waits DB/Inst: MAC/MAC2 Snaps: 70719-70723-> % of Capture shows % of Buffer Busy Waits for each top segment compared-> with total Buffer Busy Waits for all segments captured by the Snapshot Buffer Tablespace Subobject Obj. Busy % ofOwner Name Object Name Name Type Waits Capture---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW LOB_8K_TS MZ_ASSET_WORK_EVENT_ INDEX 251,073 57.07CONTENT_OW LOB_8K_TS MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX 36,186 8.23CONTENT_OW LOB_8K_TS MZ_CS_WORK_INFO_PART _2013_5_35 INDEX 31,786 7.23CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 15,663 3.56CONTENT_OW INDEX_TS MZ_CS_WORK_PENDING_R INDEX 11,087 2.52
Buffer Busy Waits 该数据段上发生 buffer busy wait的次数 数据来源 dba_hist_seg_stat.buffer_busy_waits_delta

11-15 Segments by Global Cache Buffer

Segments by Global Cache Buffer BusyDB/Inst: MAC/MAC2 Snaps: 70719-7072-> % of Capture shows % of GC Buffer Busy for each top segment compared-> with GC Buffer Busy for all segments captured by the Snapshot GC Tablespace Subobject Obj. Buffer % ofOwner Name Object Name Name Type Busy Capture---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3 INDEX 2,135,528 50.07CONTENT_OW DATA_TS MZ_CONTENT_PROVIDER_ TABLE 652,900 15.31CONTENT_OW LOB_8K_TS MZ_ASSET_WORK_EVENT_ INDEX 552,161 12.95CONTENT_OW LOB_8K_TS MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX 113,042 2.65CONTENT_OW LOB_8K_TS MZ_CS_WORK_INFO_PART _2013_5_35 INDEX 98,134 2.30

GC Buffer Busy 数据段上发挥僧gc buffer busy的次数， 数据源 dba_hist_seg_stat.gc_buffer_busy_delta

11-15 Segments by CR Blocks Received

Segments by CR Blocks Received DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total CR Blocks Received: 763,037-> Captured Segments account for 40.9% of Total CR Tablespace Subobject Obj. BlocksOwner Name Object Name Name Type Received %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 69,100 9.06CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 44,491 5.83CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 36,830 4.83CONTENT_OW DATA_TS MZ_PODCAST TABLE 36,632 4.80CONTENT_OW INDEX_TS MZ_AM_REQUEST_PK INDEX 19,646 2.57

CR Blocks Received ：是指RAC中本地节点接收到global cache CR blocks 的数量； 数据来源为 dba_hist_seg_stat.gc_cu_blocks_received_delta%Total : (该段上在本节点接收的Global CR blocks / gc cr blocks received )

11-16 Segments by Current Blocks Received

Segments by Current Blocks ReceivedDB/Inst: MAC/MAC2 Snaps: 70719-70723-> Total Current Blocks Received: 704,731-> Captured Segments account for 61.8% of Total Current Tablespace Subobject Obj. BlocksOwner Name Object Name Name Type Received %Total---------- ---------- -------------------- ---------- ----- ------------ -------CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3 INDEX 56,287 7.99CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 45,139 6.41CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 40,350 5.73CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 22,808 3.24CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX8 INDEX 13,343 1.89

Current Blocks Received :是指RAC中本地节点接收到global cache Current blocks 的数量 ，数据来源DBA_HIST_SEG_STAT.gc_cu_blocks_received_delta%Total : (该段上在本节点接收的 global cache current blocks / gc current blocks received)