12 Dictionary Cache Stats
Dictionary Cache Stats DB/Inst: MAC/MAC2 Snaps: 70719-70723-> "Pct Misses" should be very low (< 2% in most cases) -> "Final Usage" is the number of cache entries being used Get Pct Scan Pct Mod FinalCache Requests Miss Reqs Miss Reqs Usage------------------------- ------------ ------ ------- ----- -------- ----------dc_awr_control 87 2.3 0 N/A 6 1dc_global_oids 1,134 7.8 0 N/A 0 13dc_histogram_data 6,119,027 0.9 0 N/A 0 11,784dc_histogram_defs 1,898,714 2.3 0 N/A 0 5,462dc_object_grants 175 26.9 0 N/A 0 4dc_objects 10,254,514 0.2 0 N/A 0 3,807dc_profiles 8,452 0.0 0 N/A 0 2dc_rollback_segments 3,031,044 0.0 0 N/A 0 1,947dc_segments 1,812,243 1.4 0 N/A 10 3,595dc_sequences 15,783 69.6 0 N/A 15,782 20dc_table_scns 70 2.9 0 N/A 0 1dc_tablespaces 1,628,112 0.0 0 N/A 0 37dc_users 2,037,138 0.0 0 N/A 0 52global database name 7,698 0.0 0 N/A 0 1outstanding_alerts 264 99.6 0 N/A 8 1sch_lj_oids 51 7.8 0 N/A 0 1

Dictionary Cache 字典缓存也叫row cache数据来源为dba_hist_rowcache_summary
Cache 字典缓存类名kqrstcid <=> kqrsttxt cid=3(dc_rollback_segments)Get Requests 申请获取该数据字典缓存对象的次数 getsMiss : GETMISSES 申请获取该数据字典缓存对象但 miss的次数Pct Miss ：　GETMISSES /Gets ， Miss的比例 ，这个pct miss应当非常低 小于2%，否则有出现大量row cache lock的可能Scan Reqs：扫描申请的次数 ，kqrssc 、kqrpScan 、kqrpsiv时发生scan 会导致扫描数增加 kqrstsrq++(scan requests) ，例如migrate tablespace 时调用 kttm2b函数 为了安全删除uet$中的记录会callback kqrpsiv (used extent cache)，实际很少见
Pct Miss：SCANMISSES/SCANSMod Reqs: 申请修改字典缓存对象的次数，从上面的数据可以看到dc_sequences的mod reqs很高，这是因为sequence是变化较多的字典对象Final Usage ：包含有有效数据的字典缓存记录的总数 也就是正在被使用的row cache记录 USAGE Number of cache entries that contain valid data

Dictionary Cache Stats (RAC) DB/Inst: MAC/MAC2 Snaps: 70719-70723 GES GES GESCache Requests Conflicts Releases------------------------- ------------ ------------ ------------dc_awr_control 14 2 0dc_global_oids 88 0 102dc_histogram_defs 43,518 0 43,521dc_objects 21,608 17 21,176dc_profiles 1 0 1dc_segments 24,974 14 24,428dc_sequences 25,178 10,644 347dc_table_scns 2 0 2dc_tablespaces 165 0 166dc_users 119 0 119outstanding_alerts 478 8 250sch_lj_oids 4 0 4

GES Request kqrstilr total instance lock requests ，通过全局队列服务GES 来申请instance lock的次数GES request 申请的原因可能是 dump cache object、kqrbfr LCK进程要background free some parent objects释放一些parent objects 等
GES Conflicts kqrstifr instance lock forced-releases ， LCK进程以AST方式 释放锁的次数 ，仅出现在kqrbrl中GES Releases kqrstisr instance lock self-releases ，LCK进程要background free some parent objects释放一些parent objects 时可能自增
上述数据中可以看到仅有dc_sequences 对应的GES Conflicts较多， 对于sequence 使用ordered和non-cache选项会导致RAC中的一个边际效应，即”row cache lock”等待源于DC_SEQUENCES ROW CACHE。 DC_SEQUENCES 上的GETS request、modifications 、GES requests和GES conflict 与引发生成一个新的 sequence number的特定SQL执行频率相关。在Oracle 10g中，ORDERED Sequence还可能在高并发下造成大量DFS lock Handle 等待，由于bug 5209859

13 Library Cache Activity
Library Cache Activity DB/Inst: MAC/MAC2 Snaps: 70719-70723-> "Pct Misses" should be very low Get Pct Pin Pct Invali-Namespace Requests Miss Requests Miss Reloads dations--------------- ------------ ------ -------------- ------ ---------- --------ACCOUNT_STATUS 8,436 0.3 0 N/A 0 0BODY 8,697 0.7 15,537 0.7 49 0CLUSTER 317 4.7 321 4.7 0 0DBLINK 9,212 0.1 0 N/A 0 0EDITION 4,431 0.0 8,660 0.0 0 0HINTSET OBJECT 1,027 9.5 1,027 14.4 0 0INDEX 792 18.2 792 18.2 0 0QUEUE 10 0.0 1,733 0.0 0 0RULESET 0 N/A 8 87.5 7 0SCHEMA 8,169 0.0 0 N/A 0 0SQL AREA 533,409 4.8 -4,246,727,944 101.1 44,864 576SQL AREA BUILD 71,500 65.5 0 N/A 0 0SQL AREA STATS 41,008 90.3 41,008 90.3 1 0TABLE/PROCEDURE 320,310 0.6 1,033,991 3.6 25,378 0TRIGGER 847 0.0 38,442 0.3 110 0
NameSpace library cache 的命名空间GETS Requests 该命名空间所包含对象的library cache lock被申请的次数GETHITS 对象的 library cache handle 正好在内存中被找到的次数Pct Misses : ( 1- ( GETHITS /GETS Requests)) *100Pin Requests 该命名空间所包含对象上pin被申请的次数PINHITS 要pin的对象的heap metadata正好在shared pool中的次数Pct Miss ( 1- ( PINHITS /Pin Requests)) *100Reloads 指从object handle 被重建开始不是第一次PIN该对象的PIN ，且该次PIN要求对象从磁盘上读取加载的次数 ;Reloads值较高的情况 建议增大shared_pool_sizeINVALIDATIONS 由于以来对象被修改导致该命名空间所包含对象被标记为无效的次数

Library Cache Activity (RAC) DB/Inst: MAC/MAC2 Snaps: 70719-70723 GES Lock GES Pin GES Pin GES Inval GES Invali-Namespace Requests Requests Releases Requests dations--------------- ------------ ------------ ------------ ----------- -----------ACCOUNT_STATUS 8,436 0 0 0 0BODY 0 15,497 15,497 0 0CLUSTER 321 321 321 0 0DBLINK 9,212 0 0 0 0EDITION 4,431 4,431 4,431 0 0HINTSET OBJECT 1,027 1,027 1,027 0 0INDEX 792 792 792 0 0QUEUE 8 1,733 1,733 0 0RULESET 0 8 8 0 0SCHEMA 4,226 0 0 0 0TABLE/PROCEDURE 373,163 704,816 704,816 0 0TRIGGER 0 38,430 38,430 0 0
GES Lock Request: dlm_lock_requests Lock instance-lock ReQuests 申请获得lock instance lock的次数GES PIN request : DLM_PIN_REQUESTS Pin instance-lock ReQuests 申请获得pin instance lock的次数GES Pin Releases DLM_PIN_RELEASES release the pin instance lock 释放pin instance lock的次数GES Inval Requests DLM_INVALIDATION_REQUESTS get the invalidation instance lock 申请获得invalidation instance lock的次数GES Invali- dations DLM_INVALIDATIONS 接收到其他节点的invalidation pings次数

14 Process Memory Summary

Process Memory Summary DB/Inst: MAC/MAC2 Snaps: 70719-70723-> B: Begin Snap E: End Snap-> All rows below contain absolute values (i.e. not diffed over the interval)-> Max Alloc is Maximum PGA Allocation size at snapshot time-> Hist Max Alloc is the Historical Max Allocation for still-connected processes-> ordered by Begin/End snapshot, Alloc (MB) desc Hist Avg Std Dev Max Max Alloc Used Alloc Alloc Alloc Alloc Num Num Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc- -------- --------- --------- -------- -------- ------- ------- ------ ------B Other 16,062.7 N/A 6.1 66.6 3,370 3,370 2,612 2,612 SQL 5,412.2 4,462.9 2.2 89.5 4,483 4,483 2,508 2,498 Freeable 2,116.4 .0 .9 6.3 298 N/A 2,266 2,266 PL/SQL 94.0 69.8 .0 .0 1 1 2,610 2,609E Other 15,977.3 N/A 6.1 66.9 3,387 3,387 2,616 2,616 SQL 5,447.9 4,519.0 2.2 89.8 4,505 4,505 2,514 2,503 Freeable 2,119.9 .0 .9 6.3 297 N/A 2,273 2,273 PL/SQL 93.2 69.2 .0 .0 1 1 2,614 2,613

数据来源为dba_hist_process_mem_summary， 这里是对PGA 使用的一个小结，帮助我们了解到底谁用掉了PGA
B: 开始快照 E: 结束快照该环节列出 PGA中各分类的使用量Category 分类名，包括”SQL”, “PL/SQL”, “OLAP” 和”JAVA”. 特殊分类是 “Freeable” 和”Other”. Free memory是指哪些 OS已经分配给进程，但没有分配给任何分类的内存。 “Other”是已经分配给分类的内存，但不是已命名的分类Alloc (MB) allocated_total 该分类被分配的总内存Used (MB) used_total 该分类已使用的内存Avg Alloc (MB) allocated_avg 平均每个进程中该分类分配的内存量Std Dev Alloc (MB) ：该分类分配的内存在每个进程之间的标准差Max　Alloc　(MB) ALLOCATED_MAX　：在快照时间内单个进程该分类最大分配过的内存量：Max Alloc is Maximum PGA Allocation size at snapshot timeHist Max Alloc (MB) MAX_ALLOCATED_MAX: 目前仍链接着的进程该分类最大分配过的内存量：Hist Max Alloc is the Historical Max Allocation for still-connected processesNum Proc num_processes 进程数目Num Alloc NON_ZERO_ALLOCS 分配了该类型 内存的进程数目

14 SGA信息
14 -1 SGA Memory Summary
SGA Memory Summary DB/Inst: MAC/MAC2 Snaps: 70719-70723 End Size (Bytes)SGA regions Begin Size (Bytes) (if different)------------------------------ ------------------- -------------------Database Buffers 20,669,530,112Fixed Size 2,241,880Redo Buffers 125,669,376Variable Size 10,536,094,376 -------------------sum 31,333,535,744

粗粒度的sga区域内存使用信息， End Size仅在于begin size不同时打印

14-2 SGA breakdown difference

SGA breakdown difference DB/Inst: MAC/MAC2 Snaps: 70719-70723-> ordered by Pool, Name-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshotPool Name Begin MB End MB % Diff------ ------------------------------ -------------- -------------- -------java free memory 64.0 64.0 0.00large PX msg pool 7.8 7.8 0.00large free memory 247.8 247.8 0.00shared Checkpoint queue 140.6 140.6 0.00shared FileOpenBlock 2,459.2 2,459.2 0.00shared KGH: NO ACCESS 1,629.6 1,629.6 0.00shared KGLH0 997.7 990.5 -0.71shared KKSSP 312.2 308.9 -1.06shared SQLA 376.6 370.6 -1.61shared db_block_hash_buckets 178.0 178.0 0.00shared dbktb: trace buffer 156.3 156.3 0.00shared event statistics per sess 187.1 187.1 0.00shared free memory 1,208.9 1,220.6 0.97shared gcs resources 435.0 435.0 0.00shared gcs shadows 320.6 320.6 0.00shared ges enqueues 228.9 228.9 0.00shared ges resource 118.3 118.3 0.00shared init_heap_kfsg 1,063.6 1,068.1 0.43shared kglsim object batch 124.3 124.3 0.00shared ksunfy : SSO free list 174.7 174.7 0.00stream free memory 128.0 128.0 0.00 buffer_cache 19,712.0 19,712.0 0.00 fixed_sga 2.1 2.1 0.00 log_buffer 119.8 119.8 0.00 -------------------------------------------------------------
Pool 内存池的名字Name 内存池中细分组件的名字 例如KGLH0 存放KEL Heap 0 、SQLA存放SQL执行计划等Begin MB 快照开始时该组件的内存大小End MB 快照结束时该组件的内存大小% Diff 差异百分比特别注意 由于AMM /ASMM引起的shared pool收缩 一般在sga breakdown中可以提现 例如SQLA 、KQR等组件大幅缩小 ，可能导致一系列的解析等待 cursor: Pin S on X 、row cache lock等此处的free memory信息也值得我们关注， 一般推荐shared pool应当有300~400 MB 的free memory为宜

15 Streams统计

Streams CPU/IO Usage DB/Inst: ORCL/orcl1 Snaps: 556-559-> Streams processes ordered by CPU usage-> CPU and I/O Time in micro secondsSession Type CPU Time User I/O Time Sys I/O Time------------------------- -------------- -------------- --------------QMON Coordinator 101,698 0 0QMON Slaves 63,856 0 0 -------------------------------------------------------------Streams Capture DB/Inst: CATGT/catgt Snaps: 911-912 -> Lag Change should be small or negative (in seconds) Captured Enqueued Pct Pct Pct Pct Per Per Lag RuleEval Enqueue RedoWait PauseCapture Name Second Second Change Time Time Time Time ------------ -------- -------- -------- -------- -------- -------- -------- CAPTURE_CAT 650 391 93 0 23 0 71 ------------------------------------------------------------- Streams Apply DB/Inst: CATGT/catgt Snaps: 911-912 -> Pct DB is the percentage of all DB transactions that this apply handled -> WDEP is the wait for dependency -> WCMT is the wait for commit -> RBK is rollbacks -> MPS is messages per second -> TPM is time per message in milli-seconds -> Lag Change should be small or negative (in seconds) Applied Pct Pct Pct Pct Applied Dequeue Apply Lag Apply Name TPS DB WDEP WCMT RBK MPS TPM TPM Change ------------ -------- ---- ---- ---- --- -------- -------- -------- -------- APPLY_CAT 0 0 0 0 0 0 0 0 0 -------------------------------------------------------------

Capture Name : Streams捕获进程名Captured Per Second ：每秒挖掘出来的message 条数Enqueued Per Second: 每秒入队的message条数lag change: 指日志生成的时间到挖掘到该日志生成 message的时间延迟Pct Enqueue Time： 入队时间的比例Pct redoWait Time : 等待redo的时间比例Pct Pause Time : Pause 时间的比例
Apply Name Streams 应用Apply进程的名字Applied TPS : 每秒应用的事务数Pct DB: 所有的DB事务中 apply处理的比例Pct WDEP: 由于等待依赖的数据而耗费的时间比例Pct WCMT: 由于等待commit而耗费的时间比例Pct RBK: 事务rollback 回滚的比例Applied MPS: 每秒应用的message 数Dequeue TPM: 每毫秒出队的message数Lag Change:指最新message生成的时间到其被Apply收到的延迟

16 Resource Limit
Resource Limit Stats DB/Inst: MAC/MAC2 Snap: 70723-> only rows with Current or Maximum Utilization > 80% of Limit are shown-> ordered by resource name Current Maximum InitialResource Name Utilization Utilization Allocation Limit------------------------------ ------------ ------------ ---------- ----------ges_procs 2,612 8,007 10003 10003processes 2,615 8,011 10000 10000

数据源于dba_hist_resource_limit
注意这里仅列出当前使用或最大使用量>80% *最大限制的资源名，如果没有列在这里则说明 资源使用量安全
Current Utilization 当前对该资源(包括Enqueue Resource、Lock和processes)的使用量Maximum Utilization 从最近一次实例启动到现在该资源的最大使用量Initial Allocation 初始分配值，一般等于参数文件中指定的值Limit 实际上限值

17 init.ora Parameters
init.ora Parameters DB/Inst: MAC/MAC2 Snaps: 70719-70723 End valueParameter Name Begin value (if different)----------------------------- --------------------------------- --------------_compression_compatibility 11.2.0_kghdsidx_count 4_ksmg_granule_size 67108864_shared_pool_reserved_min_all 4100archive_lag_target 900audit_file_dest /u01/app/oracle/admin/MAC/adumaudit_trail OScluster_database TRUEcompatible 11.2.0.2.0control_files +DATA/MAC/control01.ctl, +RECOdb_16k_cache_size 268435456db_block_size 8192db_cache_size 19327352832db_create_file_dest +DATA
Parameter Name 参数名Begin value 开始快照时的参数值End value 结束快照时的参数值 (仅在发生变化时打印)

18 Global Messaging Statistics
Global Messaging Statistics DB/Inst: MAC/MAC2 Snaps: 70719-70723Statistic Total per Second per Trans--------------------------------- ---------------- ------------ ------------acks for commit broadcast(actual) 53,705 14.9 0.2acks for commit broadcast(logical 311,182 86.1 1.3broadcast msgs on commit(actual) 317,082 87.7 1.3broadcast msgs on commit(logical) 317,082 87.7 1.3broadcast msgs on commit(wasted) 263,332 72.9 1.1dynamically allocated gcs resourc 0 0.0 0.0dynamically allocated gcs shadows 0 0.0 0.0flow control messages received 267 0.1 0.0flow control messages sent 127 0.0 0.0gcs apply delta 0 0.0 0.0gcs assume cvt 55,541 15.4 0.2全局通信统计信息，数据来源WRH$_DLM_MISC;

20 Global CR Served Stats
Global CR Served Stats DB/Inst: MAC/MAC2 Snaps: 70719-70723Statistic Total------------------------------ ------------------CR Block Requests 403,703CURRENT Block Requests 444,896Data Block Requests 403,705Undo Block Requests 94,336TX Block Requests 307,896Current Results 652,746Private results 21,057Zero Results 104,720Disk Read Results 69,418Fail Results 508Fairness Down Converts 102,844Fairness Clears 15,207Free GC Elements 0Flushes 105,052Flushes Queued 0Flush Queue Full 0Flush Max Time (us) 0Light Works 71,793Errors 117

LMS传输CR BLOCK的统计信息，数据来源WRH$_CR_BLOCK_SERVER

21 Global CURRENT Served Stats

Global CURRENT Served Stats DB/Inst: MAC/MAC2 Snaps: 70719-70723-> Pins = CURRENT Block Pin Operations-> Flushes = Redo Flush before CURRENT Block Served Operations-> Writes = CURRENT Block Fusion Write OperationsStatistic Total % <1ms % <10ms % <100ms % <1s % <10s---------- ------------ -------- -------- -------- -------- --------Pins 73,018 12.27 75.96 8.49 2.21 1.08Flushes 79,336 5.98 50.17 14.45 19.45 9.95Writes 102,189 3.14 35.23 19.34 33.26 9.03 数据来源dba_hist_current_block_server Time to process current block request = (pin time + flush time + send time)Pins CURRENT Block Pin Operations ， PIN的内涵是处理一个BAST 不包含对global current block的flush和实际传输The pin time represents how much time is required to process a BAST. It does not include the flush time and the send time. The average pin time per block served should be very low because the processing consists mainly of code path and should never be blocked.Flush 指 脏块被LMS进程传输出去之前，其相关的redo必须由LGWR已经flush 到磁盘上 Write 指fusion write number of writes which were mediated； 节点之间写脏块需求相互促成的行为 KJBL.KJBLREQWRITE gcs write request msgs 、gcs writes refused% <1ms % <10ms % <100ms % <1s % <10s 分别对应为pin、flush、write行为耗时的比例例如在上例中flush和 write 在1s 到10s之间的有9%，在100ms 和1s之间的有19%和33%，因为flush和write都是IO操作 所以这里可以预见IO存在问题，延迟较高