绝对干货！|【性能调优】Oracle AWR报告指标全解析
啥是AWR?==============================
(Automatic Workload Repository)
一堆历史性能数据，放在SYSAUX表空间上， AWR和SYSAUX都是10g出现的，是Oracle调优的关键特性；
默认快照间隔1小时，10g保存7天、11g保存8天;
AWR程序核心是dbms_workload_repository包@?/rdbms/admin/awrrpt 本实例
@?/rdbms/admin/awrrpti RAC中选择实例号
谁维护AWR?主要是MMON(Manageability Monitor Process)和它的小工进程(m00x)
MMON的功能包括:
1.启动slave进程m00x去做AWR快照
2.当某个度量阀值被超过时发出alert告警
3.为最近改变过的SQL对象捕获指标信息AWR小技巧
手动执行一个快照：
Exec dbms_workload_repository.create_snapshot;
创建一个AWR基线
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id，end_snap_id ,baseline_name); @?/rdbms/admin/awrddrpt AWR比对报告 @?/rdbms/admin/awrgrpt RAC 全局AWR
1、报告总结
WORKLOAD REPOSITORY report for
| DB Name | DB Id | Instance | Inst num | Startup Time | Release | RAC |
|---|---|---|---|---|---|---|
| CUFS | 3961207481 | cufs | 1 | 24-Aug-19 21:08 | 11.2.0.4.0 | NO |
| Host Name | Platform | CPUs | Cores | Sockets | Memory (GB) |
|---|---|---|---|---|---|
| database | Linux x86 64-bit | 16 | 16 | 4 | 31.48 |
| Snap Id | Snap Time | Sessions | Cursors/Session | |
|---|---|---|---|---|
| Begin Snap: | 9512 | 04-Dec-19 10:00:20 | 85 | 9.3 |
| End Snap: | 9513 | 04-Dec-19 11:00:28 | 80 | 3.4 |
| Elapsed: | 60.14 (mins) | |||
| DB Time: | 64.77 (mins) |
Elapsed为该AWR性能报告的时间跨度(自然时间的跨度，例如前一个快照snapshot是9点生成的，后一个快照snapshot是11点生成的，
则若使用@?/rdbms/admin/awrrpt 脚本中指定这2个快照的话，那么其elapsed = (11-9)=2 个小时)
一个AWR性能报告至少需要2个AWR snapshot性能快照才能生成 ( 这2个快照时间实例不能重启过，否则指定这2个快照生成AWR性能报告会报错)，
DB TIME= 所有前台session花费在database调用上的总和时间：注意是前台进程foreground sessions
包括CPU时间、IO Time、和其他一系列非空闲等待时间
DB TIME 不等于 响应时间，DB TIME高了未必响应慢，DB TIME低了未必响应快DB Time描绘了数据库总体负载，但要和elapsed time结合其来。Average Active Session AAS= DB time/Elapsed Time
DB Time =60min，Elapsed Time =60min AAS=60/60=1 负载一般
DB Time= 1min，Elapsed Time= 60min AAS= 1/60 负载很轻
DB Time= 60000min，Elapsed Time=60min AAS=1000 系统hang住
DB TIME= DB CPU + Non-Idle Wait + Wait on CPU queue如果仅有2个逻辑CPU，而2个session在60分钟都没等待事件，一直跑在CPU上，那么：DB CPU=2*60mins ， DB Time =2*60 +0 +0 =120
AAS = 120/60=2 正好等于OS load 2。
如果有3个session都100%仅消耗CPU，那么总有一个要wait on queue
DB CPU =2* 60mins ，wait on CPU queue=60mins
AAS= (120+ 60)/60=3 主机load 亦为3，此时vmstat 看waiting for run time
1-1 内存参数大小
Cache Sizes
| Begin | End | |||
|---|---|---|---|---|
| Buffer Cache: | 2,880M | 2,880M | Std Block Size: | 8K |
| Shared Pool Size: | 4,782M | 4,796M | Log Buffer: | 4,356K |
1-2 Load Profile
Load Profile
| Per Second | Per Transaction | Per Exec | Per Call | |
|---|---|---|---|---|
| DB Time(s): | 1.1 | 0.0 | 0.00 | 0.00 |
| DB CPU(s): | 1.1 | 0.0 | 0.00 | 0.00 |
| Redo size (bytes): | 35,814.6 | 140.3 | ||
| Logical read (blocks): | 22,708.3 | 88.9 | ||
| Block changes: | 230.3 | 0.9 | ||
| Physical read (blocks): | 395.7 | 1.6 | ||
| Physical write (blocks): | 4.6 | 0.0 | ||
| Read IO requests: | 8.7 | 0.0 | ||
| Write IO requests: | 2.3 | 0.0 | ||
| Read IO (MB): | 3.1 | 0.0 | ||
| Write IO (MB): | 0.0 | 0.0 | ||
| User calls: | 2,795.2 | 11.0 | ||
| Parses (SQL): | 635.8 | 2.5 | ||
| Hard parses (SQL): | 20.9 | 0.1 | ||
| SQL Work Area (MB): | 2.7 | 0.0 | ||
| Logons: | 0.1 | 0.0 | ||
| Executes (SQL): | 641.8 | 2.5 | ||
| Rollbacks: | 252.3 | 1.0 | ||
| Transactions: | 255.3 |
指标含义
redo size单位bytes，redosize可以用来估量update/insert/delete的频率，大的redo size往往对lgwr写日志，和arch归档造成I/O压力，
Per Transaction可以用来分辨是大量小事务,还是少量大事务。如上例每秒redo 约1MB,每个事务800 字节，符合OLTP特征
Logical Read 单位(次数*块数),逻辑读耗CPU,主频和CPU核数都很重要，逻辑读高则DB CPU往往高，也往往可以看到latch: cache buffer chains等待。
Block changes 单位(次数*块数)，描绘数据变化频率
Physical Read 单位(次数*块数)，物理读消耗IO读，体现在IOPS和吞吐量等不同纬度上；但减少物理读可能意味着消耗更多CPU。
好的存储 每秒物理读能力达到几GB，例如Exadata。这个physical read包含了physical reads cache和physical reads direct
Physical writes 单位(次数*块数)，主要是DBWR写datafile，也有direct path write。
dbwr长期写出慢会导致定期log file switch(checkpoint no complete) 检查点无法完成的前台等待。
这个physical write 包含了physical writes direct +physical writes from cacheUser Calls 单位次数，用户调用数，more details from internalParses 解析次数，包括软解析+硬解析，软解析优化得不好，则夸张地说几乎等于每秒SQL执行次数。即执行解析比1:1，而我们希望的是解析一次到处运行
Hard Parses 万恶之源:Cursor pin s on X， library cache: mutex X ， latch: row cache objects /shared pool……………..。硬解析最好少于每秒20次
W/A MB processed 单位MB W/A workarea workarea中处理的数据数量，结合 In-memory Sort%Logons 登陆次数， 结合AUDIT审计数据一起看。
Executes 执行次数，反应执行频率Rollback 回滚次数，反应回滚频率,但是这个指标不太精确，
Transactions 每秒事务数，是数据库层的TPS，可以看做压力测试或比对性能时的一个指标，孤立看无意义% Blocks changed per Read 每次逻辑读导致数据块变化的比率；
如果’redo size’, ‘block changes’ ‘pct of blocks changed per read’三个指标都很高，则说明系统正执行大量insert/update/delete;pct of blocks changed per read = (block changes ) /( logical reads)
Recursive Call % 递归调用的比率;Recursive Call% = (recursive calls)/(user calls)
Rollback per transaction % 事务回滚比率。Rollback per transaction %= (rollback)/(transactions)
Rows per Sort平均每次排序涉及到的行数;Rows per Sort=(sorts(rows) ) / ( sorts(disk) + sorts(memory))
注意这些Load Profile负载指标在本环节提供了2个维度per second和per transaction。
per Second: 主要是把快照内的时间值除以快照时间的秒数,例如在A快照中V$SYSSTAT视图反应 table scans (long tables) 这个指标是 100 ，
在B快照中V$SYSSTAT视图反应 table scans (long tables) 这个指标是 3700, 而A快照和B快照之间间隔了一个小时3600秒，则对于table scans (long tables) per second 就是 ( 3700- 100) /3600=1。
per transaction : 基于事务的维度，与per second相比是把除数从时间的秒数改为了该段时间内的事务数。这个维度的很大用户是用来识别应用特性的变化
若2个AWR性能报告中该维度指标 出现了大幅变化，例如 redo size从本来per transaction 1k变化为 10k per transaction，则说明SQL业务逻辑肯定发生了某些变化。
注意AWR中的这些指标 并不仅仅用来孤立地了解 Oracle数据库负载情况， 实施调优工作。对于 故障诊断 例如HANG、Crash等， 完全可以通过对比问题时段的性能报告和常规时间来对比，通过各项指标的对比往往可以找出 病灶所在。
1-3 Instance Efficiency Percentages (Target 100%
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 100.00 | In-memory Sort %: | 100.00 |
| Library Hit %: | 90.16 | Soft Parse %: | 96.72 |
| Execute to Parse %: | 0.93 | Latch Hit %: | 99.94 |
| Parse CPU to Parse Elapsd %: | 75.79 | % Non-Parse CPU: | 87.28 |
上述所有指标的目标均为100%，越大越好
80%以上 %Non-Parse CPU
90%以上 Buffer Hit%, In-memory Sort%, Soft Parse%
95%以上 Library Hit%, Redo Nowait%, Buffer Nowait%
98%以上 Latch Hit%
1、Buffer Nowait % session申请一个buffer(兼容模式)不等待的次数比例。需要访问buffer时立即可以访问的比率，
2、buffer HIT%: 高速缓存命中率，反应物理读和缓存命中间的纠结，但这个指标即便99% 也不能说明物理读等待少了
不合理的db_cache_size,或者是SGA自动管理ASMM /Memory自动管理AMM下都可能因为db_cache_size过小引起大量的db file sequential /scattered read等待事件；
此外与 buffer HIT%相关的指标值得关注的还有 table scans(long tables) 大表扫描这个统计项目、此外相关的栏目还有Buffer Pool Statistics 、Buffer Pool Advisory等
3、Redo nowait%: session在生成redo entry时不用等待的比例，redo相关的资源争用例如redo space request争用可能造成生成redo时需求等待。此项数据来源于v$sysstat中的(redo log space requests/redo entries)。
一般来说10g以后不太用关注log_buffer参数的大小，需要关注是否有十分频繁的 log switch ；过小的redo logfile size 如果配合较大的SGA和频繁的commit提交都可能造成该问题。
考虑增到redo logfile 的尺寸 : 1-2G 每个，10-15组都是合适的。同时考虑优化redo logfile和datafile 的I/O。
4、In-memory Sort%:这个指标因为它不计算workarea中所有的操作类型，纯粹在内存中完成的排序比例。
5、Library Hit%: library cache命中率，申请一个library cache object例如一个SQL cursor时，其已经在library cache中的比例。合理值：>95% ，ns
6、Soft Parse: 软解析比例，经典指标，合理值>95%
Soft Parse %是AWR中另一个重要的解析指标，该指标反应了快照时间内软解析次数和总解析次数 (soft+hard 软解析次数+硬解析次数)的比值，若该指标很低，那么说明了可能存在剧烈的hard parse硬解析，大量的硬解析会消耗更多的CPU时间片并产生解析争用(此时可以考虑使用cursor_sharing=FORCE)；
理论上我们总是希望 Soft Parse % 接近于100%， 但并不是说100%的软解析就是最理想的解析状态，通过设置 session_cached_cursors参数和反复重用游标我们可以让解析来的更轻量级，即通俗所说的利用会话缓存游标实现的软软解析(soft soft parse)。
7、Execute to Parse%指标反映了执行解析比其公式为 1-(parse/execute) , 目标为100% 及接近于只执行而不解析。
在oracle中解析往往是执行的先提工作，但是通过游标共享可以解析一次执行多次，执行解析可能分成多种场景：
hard coding => 代码硬解析一次,执行一次，理论上其执行解析比为 1:1 ，则理论上Execute to Parse =0 极差，且soft parse比例也为0%
绑定变量但是仍软解析=》软解析一次，执行一次 ，这种情况虽然比前一种好 但是执行解析比(这里的parse，包含了软解析和硬解析)仍是1:1， 理论上Execute to Parse =0 极差，但是soft parse比例可能很高
使用静态SQL、动态绑定、session_cached_cursor、open cursors等技术实现的解析一次，执行多次，执行解析比为N:1，则Execute to Parse= 1- (1/N) 执行次数越多Execute to Parse越接近100%，这种是我们在OLTP环境中希望看到的。
通俗地说 soft parse% 反映了软解析率， 而软解析在oracle中仍是较昂贵的操作， 我们希望的是解析1次执行N次，如果每次执行均需要软解析，那么虽然soft parse%=100% 但是parse time仍可能是消耗DB TIME的大头。
Execute to Parse反映了执行解析比，Execute to Parse和soft parse% 都很低那么说明确实没有绑定变量，而如果 soft parse%接近99%而Execute to Parse 不足90% 则说明没有执行解析比低,需要通过静态SQL、动态绑定、session_cached_cursor、open cursors等技术减少软解析。
8、Latch Hit%: willing-to-wait latch闩申请不要等待的比例。
9、Parse CPU To Parse Elapsd:该指标反映了快照内解析CPU时间和总的解析时间的比值(Parse CPU Time/ Parse Elapsed Time)；若该指标水平很低，那么说明在整个解析过程中 实际在CPU上运算的时间是很短的，而主要的解析时间都耗费在各种其他非空闲的等待事件上了(如latch:shared pool,row cache lock之类等)
10、%Non-Parse CPU非解析cpu比例,公式为 (DB CPU – Parse CPU)/DB CPU，
1-5 Top 10 Foreground Events by Total Wait Time
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| DB CPU | 3823.1 | 98.4 | |||
| log file sync | 10,933 | 101.5 | 9 | 2.6 | Commit |
| cursor: pin S | 3,299 | 23.4 | 7 | .6 | Concurrency |
| SQL*Net message to client | 7,835,076 | 13.3 | 0 | .3 | Network |
| latch: shared pool | 773 | 9.9 | 13 | .3 | Concurrency |
| direct path read | 29,439 | 4.2 | 0 | .1 | User I/O |
| library cache: mutex X | 681 | 3.1 | 5 | .1 | Concurrency |
| cursor: pin S wait on X | 73 | 2.6 | 36 | .1 | Concurrency |
| latch: row cache objects | 544 | 2.3 | 4 | .1 | Concurrency |
| SQL*Net more data to client | 122,900 | 2 | 0 | .1 | Network |
丰富的等待事件以足够的细节来描绘系统运行的性能瓶颈(Mysql梦寐以求的)
Waits : 该等待事件发生的次数
Times : 该等待事件消耗的总计时间,单位为秒,对于DB CPU而言是前台进程所消耗CPU时间片的总和Avg
Wait(ms) :该等待事件平均等待的时间,实际就是Times/Waits,单位ms，
Wait Class: 等待类型：
Concurrency,SystemI/O,UserI/O,Administrative,Other,Configuration,Scheduler,Cluster,Application,Idle,Network,Commit
常见的等待事件=========================>
1、db file scattered read：Avg wait time应当小于20ms如果数据库执行全表扫描或者是全索引扫描会执行 Multi block I/O ，此时等待物理I/O 结束会出现此等待事件。一般从应用程序(SQL），I/O 方面入手调整; 注意和index fast full scans (full) 以及 table scans结合起来一起看。
2、db file sequential read:该等待事件Avg wait time平均单次等待时间应当小于20msdb file sequential read单块读等待是一种最为常见的物理IO等待事件，这里的sequential指的是将数据块读入到相连的内存空间中，而不是指所读取的数据块是连续的。
3、latch free:其实是未获得latch 而进入latch sleep
4、enq:XX 队列锁等待:视乎不同的队列锁有不同的情况：
Oracle队列锁: Enqueue HW
enq: TX – row lock/index contention等待事件
enq: TT – contention等待事件
enq: JI – contention等待事件
enq: US – contention等待事件
enq: TM – contention等待事件
enq: RO fast object reuse等待事件
enq: HW – contention等待事件
5、free buffer waits：是由于无法找到可用的buffer cache 空闲区域，需要等待DBWR 写入完成引起
一般是由于低效的sql、过小的buffer cache、DBWR 工作负荷过量引起，
6、buffer busy wait/ read by other session：以上2个等待事件可以归为一起处理
7、write complete waits :此类等待事件是由于DBWR 将脏数据写入数据文件，其他进程如果需要修改 buffer cache会引起此等待事件，一般是 I/O 性能问题或者是DBWR 工作负荷过量引起
8、control file parallel write：频繁的更新控制文件会造成大量此类等待事件，如日志频繁切换，检查点经常发生，nologging 引起频繁的数据文件更改，I/O 系统性能缓慢。
9、log file sync：此类等待时间是由于 LGWR 进程讲redo log buffer 写入redo log 中发生。如果此类事件频繁发生，可以判断为：commit 次数是否过多、I/O 系统问题、重做日志是否不必要被创建、redo log buffer是否过大
2-1 Time Model Statistics
Time Model Statistics
- Total time in database user-calls (DB Time): 3886.1s
- Statistics including the word “background” measure background process time, and so do not contribute to the DB time statistic
- Ordered by % or DB time desc, Statistic name
| Statistic Name | Time (s) | % of DB Time |
|---|---|---|
| DB CPU | 3,823.09 | 98.38 |
| sql execute elapsed time | 1,382.61 | 35.58 |
| parse time elapsed | 698.98 | 17.99 |
| hard parse elapsed time | 340.29 | 8.76 |
| PL/SQL execution elapsed time | 8.17 | 0.21 |
| connection management call elapsed time | 1.19 | 0.03 |
| hard parse (sharing criteria) elapsed time | 0.56 | 0.01 |
| PL/SQL compilation elapsed time | 0.54 | 0.01 |
| failed parse elapsed time | 0.38 | 0.01 |
| sequence load elapsed time | 0.17 | 0.00 |
| repeated bind elapsed time | 0.02 | 0.00 |
| DB time | 3,886.11 | |
| background elapsed time | 248.93 | |
| background cpu time | 48.61 |
Time Model Statistics几个特别有用的时间指标：
parse time elapsed、hard parse elapsed time 结合起来看解析是否是主要矛盾，若是则重点是软解析还是硬解析
sequence load elapsed time sequence序列争用是否是问题焦点
PL/SQL compilation elapsed time PL/SQL对象编译的耗时
注意PL/SQL execution elapsed time 纯耗费在PL/SQL解释器上的时间。不包括花在执行和解析其包含SQL上的时间
connection management call elapsed time 建立数据库session连接和断开的耗时
failed parse elapsed time 解析失败，例如由于ORA-4031
hard parse(sharing criteria)elapsed time由于无法共享游标造成的硬解析
hard parse(bind mismatch)elapsed time由于bind type or bind size 不一致造成的硬解析
注意该时间模型中的指标存在包含关系所以Time Model Statistics加起来超过100%再正常不过1) background elapsed time
2) background cpu time
3) RMAN cpu time (backup/restore)
1) DB time
2) DB CPU
2) connection management call elapsed time
2) sequence load elapsed time
2) sql execute elapsed time
2) parse time elapsed
3) hard parse elapsed time
4) hard parse (sharing criteria) elapsed time
5) hard parse (bind mismatch) elapsed time
3) failed parse elapsed time
4) failed parse (out of shared memory) elapsed time
2) PL/SQL execution elapsed time
2) inbound PL/SQL rpc elapsed time
2) PL/SQL compilation elapsed time
2) Java execution elapsed time
2) repeated bind elapsed time
2-2 Foreground Wait Class
Foreground Wait Class
- s – second, ms – millisecond – 1000th of a second
- ordered by wait time desc, waits desc
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
- Captured Time accounts for 102.7% of Total DB time 3,886.11 (s)
- Total FG Wait Time: 166.02 (s) DB CPU time: 3,823.09 (s)
| Wait Class | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | %DB time |
|---|---|---|---|---|---|
| DB CPU | 3,823 | 98.38 | |||
| Commit | 10,933 | 0 | 102 | 9 | 2.61 |
| Concurrency | 5,479 | 0 | 41 | 8 | 1.07 |
| Network | 7,965,502 | 0 | 15 | 0 | 0.40 |
| User I/O | 31,504 | 0 | 6 | 0 | 0.14 |
| Application | 339 | 0 | 2 | 5 | 0.04 |
| Other | 577 | 1 | 1 | 1 | 0.02 |
| Configuration | 2 | 100 | 0 | 6 | 0.00 |
| System I/O | 12 | 0 | 0 | 0 | 0.00 |
常见的WAIT_CLASS类型
—————————————
Concurrency
User I/O
System I/O
Administrative
Other
Configuration
Scheduler
Cluster
Application
Queueing
Idle
Network
Commit
Wait Class: 等待事件的类型。
Waits: 该类型所属等待事件在快照时间内的等待次数
%Time Out 等待超时的比率，未超时次数/waits * 100 (%)
Total Wait Time: 该类型所属等待事件总的耗时，单位为秒
Avg Wait(ms) : 该类型所属等待事件的平均单次等待时间，单位为ms ，实际这个指标对commit 和 user i/o 以及system i/o类型有点意义
Other 类型，遇到该类型等待事件的话常见的原因是Oracle Bug或者网络、I/O存在问题，建议提交MOS
Concurrency 类型并行争用类型的等待事件,典型的如 latch: shared pool、latch: library cache、row cache lock、library cache pin/lockCluster 类型 为Real Application
Cluster RAC环境中的等待事件， 需要注意的是如果启用了RAC option，那么即使你的集群中只启动了一个实例，那么该实例也可能遇到 Cluster类型的等待事件, 例如gc buffer busy
System I/O 主要是后台进程维护数据库所产生的I/O，例如control file parallel write 、log file parallel write、db file parallel write。
User I/O 主要是前台进程做了一些I/O操作，并不是说后台进程不会有这些等待事件。典型的如db file sequential/scattered read、direct path read
Configuration 由于配置引起的等待事件，例如日志切换的log file switch
Application 应用造成的等待事件，例如enq: TM – contention和enq: TX – row lock
contention；Oracle认为这是由于应用设计不当造成的等待事件， 但实际这些Application class 等待可能受到 Concurrency、Cluster、System I/O 、User I/O等多种类型等待的影响，例如本来commit只要1ms ，则某一行数据仅被锁定1ms，但由于commit变慢从而释放行锁变慢，引发大量的enq: TX – row lock contention等待事件。
Network : 网络类型的等待事件 例如 SQL*Net more data to client 、SQL*Net more data to dblink
Idle 空闲等待事件 ，最为常见的是rdbms ipc message
SQL*Net message from client 等待SQL*NET传来信息
2-3 Foreground Wait Events
Foreground Wait Events
- s – second, ms – millisecond – 1000th of a second
- Only events with Total Wait Time (s) >= .001 are shown
- ordered by wait time desc, waits desc (idle events last)
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
| Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn | % DB time |
|---|---|---|---|---|---|---|
| log file sync | 10,933 | 0 | 102 | 9 | 0.01 | 2.61 |
| cursor: pin S | 3,299 | 0 | 23 | 7 | 0.00 | 0.60 |
| SQL*Net message to client | 7,835,076 | 0 | 13 | 0 | 8.50 | 0.34 |
| latch: shared pool | 773 | 0 | 10 | 13 | 0.00 | 0.26 |
| direct path read | 29,439 | 0 | 4 | 0 | 0.03 | 0.11 |
| library cache: mutex X | 681 | 0 | 3 | 5 | 0.00 | 0.08 |
| cursor: pin S wait on X | 73 | 0 | 3 | 36 | 0.00 | 0.07 |
| latch: row cache objects | 544 | 0 | 2 | 4 | 0.00 | 0.06 |
| SQL*Net more data to client | 122,900 | 0 | 2 | 0 | 0.13 | 0.05 |
| enq: KO – fast object checkpoint | 23 | 0 | 1 | 48 | 0.00 | 0.03 |
| db file sequential read | 1,608 | 0 | 1 | 1 | 0.00 | 0.03 |
| latch free | 545 | 0 | 0 | 1 | 0.00 | 0.01 |
| direct path sync | 15 | 0 | 0 | 18 | 0.00 | 0.01 |
| kksfbc child completion | 4 | 100 | 0 | 51 | 0.00 | 0.01 |
| enq: TX – row lock contention | 8 | 0 | 0 | 25 | 0.00 | 0.01 |
| SQL*Net break/reset to client | 306 | 0 | 0 | 1 | 0.00 | 0.01 |
| SQL*Net more data from client | 7,526 | 0 | 0 | 0 | 0.01 | 0.00 |
| local write wait | 7 | 0 | 0 | 4 | 0.00 | 0.00 |
| Disk file operations I/O | 177 | 0 | 0 | 0 | 0.00 | 0.00 |
| enq: RO – fast object reuse | 2 | 0 | 0 | 11 | 0.00 | 0.00 |
| latch: cache buffers chains | 53 | 0 | 0 | 0 | 0.00 | 0.00 |
| reliable message | 16 | 0 | 0 | 1 | 0.00 | 0.00 |
| undo segment extension | 2 | 100 | 0 | 6 | 0.00 | 0.00 |
| cursor: mutex S | 1 | 0 | 0 | 11 | 0.00 | 0.00 |
| buffer busy waits | 24 | 0 | 0 | 0 | 0.00 | 0.00 |
| read by other session | 2 | 0 | 0 | 3 | 0.00 | 0.00 |
| direct path write | 243 | 0 | 0 | 0 | 0.00 | 0.00 |
| db file parallel read | 1 | 0 | 0 | 3 | 0.00 | 0.00 |
| latch: session allocation | 10 | 0 | 0 | 0 | 0.00 | 0.00 |
| latch: In memory undo latch | 12 | 0 | 0 | 0 | 0.00 | 0.00 |
| library cache lock | 2 | 0 | 0 | 1 | 0.00 | 0.00 |
| library cache load lock | 6 | 0 | 0 | 0 | 0.00 | 0.00 |
| latch: enqueue hash chains | 1 | 0 | 0 | 1 | 0.00 | 0.00 |
| SQL*Net message from client | 7,835,081 | 0 | 261,357 | 33 | 8.50 | |
| jobq slave wait | 7,496 | 100 | 3,763 | 502 | 0.01 |
2-4 Background Wait Events
Background Wait Events
- ordered by wait time desc, waits desc (idle events last)
- Only events with Total Wait Time (s) >= .001 are shown
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
| Event | Waits | %Time -outs | Total Wait Time (s) | Avg wait (ms) | Waits /txn | % bg time |
|---|---|---|---|---|---|---|
| log file parallel write | 11,570 | 0 | 104 | 9 | 0.01 | 41.67 |
| db file async I/O submit | 2,497 | 0 | 75 | 30 | 0.00 | 30.27 |
| control file parallel write | 1,576 | 0 | 17 | 11 | 0.00 | 6.77 |
| os thread startup | 139 | 0 | 8 | 60 | 0.00 | 3.35 |
| db file sequential read | 175 | 0 | 0 | 2 | 0.00 | 0.13 |
| latch: shared pool | 17 | 0 | 0 | 16 | 0.00 | 0.11 |
| control file sequential read | 4,168 | 0 | 0 | 0 | 0.00 | 0.06 |
| direct path sync | 1 | 0 | 0 | 104 | 0.00 | 0.04 |
| log file sync | 2 | 0 | 0 | 28 | 0.00 | 0.02 |
| ADR block file read | 16 | 0 | 0 | 2 | 0.00 | 0.01 |
| ADR block file write | 5 | 0 | 0 | 3 | 0.00 | 0.01 |
| reliable message | 23 | 0 | 0 | 0 | 0.00 | 0.00 |
| asynch descriptor resize | 245 | 100 | 0 | 0 | 0.00 | 0.00 |
| Disk file operations I/O | 66 | 0 | 0 | 0 | 0.00 | 0.00 |
| LGWR wait for redo copy | 32 | 0 | 0 | 0 | 0.00 | 0.00 |
| direct path write | 23 | 0 | 0 | 0 | 0.00 | 0.00 |
| rdbms ipc message | 30,297 | 62 | 61,071 | 2016 | 0.03 | |
| DIAG idle wait | 7,192 | 100 | 7,214 | 1003 | 0.01 | |
| smon timer | 15 | 73 | 3,755 | 250334 | 0.00 | |
| shared server idle wait | 121 | 100 | 3,631 | 30010 | 0.00 | |
| Streams AQ: qmn coordinator idle wait | 266 | 48 | 3,613 | 13583 | 0.00 | |
| Streams AQ: qmn slave idle wait | 129 | 0 | 3,613 | 28008 | 0.00 | |
| pmon timer | 1,201 | 100 | 3,609 | 3005 | 0.00 | |
| Space Manager: slave idle wait | 722 | 100 | 3,608 | 4997 | 0.00 | |
| dispatcher timer | 60 | 100 | 3,601 | 60012 | 0.00 | |
| SQL*Net message from client | 532 | 0 | 0 | 1 | 0.00 | |
| class slave wait | 19 | 0 | 0 | 0 | 0.00 |
2-5 Operating System Statistics
Operating System Statistics
- *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
- ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name
| Statistic | Value | End Value |
|---|---|---|
| BUSY_TIME | 389,214 | |
| IDLE_TIME | 5,201,491 | |
| IOWAIT_TIME | 11,255 | |
| NICE_TIME | 0 | |
| SYS_TIME | 155,118 | |
| USER_TIME | 227,910 | |
| LOAD | 0 | 1 |
| RSRC_MGR_CPU_WAIT_TIME | 0 | |
| VM_IN_BYTES | 0 | |
| VM_OUT_BYTES | 0 | |
| PHYSICAL_MEMORY_BYTES | 33,805,135,872 | |
| NUM_CPUS | 16 | |
| NUM_CPU_CORES | 16 | |
| NUM_CPU_SOCKETS | 4 | |
| GLOBAL_RECEIVE_SIZE_MAX | 4,194,304 | |
| GLOBAL_SEND_SIZE_MAX | 1,048,576 | |
| TCP_RECEIVE_SIZE_DEFAULT | 87,380 | |
| TCP_RECEIVE_SIZE_MAX | 4,194,304 | |
| TCP_RECEIVE_SIZE_MIN | 4,096 | |
| TCP_SEND_SIZE_DEFAULT | 16,384 | |
| TCP_SEND_SIZE_MAX | 4,194,304 | |
| TCP_SEND_SIZE_MIN | 4,096 |
Operating System Statistics 操作系统统计信息, TIME相关的指标单位均为百分之一秒
NUM_CPU_SOCKETS:物理CPU的数目
NUM_CPU_CORES:CPU的核数
NUM_CPUS:逻辑CPU的数目
SYS_TIME:在内核态被消耗掉的CPU时间片，单位为百分之一秒
USER_TIME:在用户态被消耗掉的CPU时间片，单位为百分之一秒
BUSY_TIME:Busy_Time=SYS_TIME+USER_TIME 消耗的CPU时间片，单位为百分之一秒
AVG_BUSY_TIME:AVG_BUSY_TIME= BUSY_TIME/NUM_CPUS
IDLE_TIME 空闲的CPU时间片，单位为百分之一秒
OS_CPU_WAIT_TIME:进程等OS调度的时间
IOWAIT_TIME:所有CPU花费在等待I/O完成上的时间 单位为百分之一秒
2-6 Service Statistcs
Service Statistics
- ordered by DB Time
| Service Name | DB Time (s) | DB CPU (s) | Physical Reads (K) | Logical Reads (K) |
|---|---|---|---|---|
| SYS$USERS | 3,886 | 3,823 | 1,428 | 81,868 |
| SYS$BACKGROUND | 0 | 0 | 0 | 63 |
| cufsoa | 0 | 0 | 0 | 0 |
| cufsoaXDB | 0 | 0 | 0 | 0 |
Service Name 对应的服务名 (v$services)，
SYS$BACKGROUND代表后台进程，
SYS$USERS一般是系统用户登录
DB TIME (s): 本服务名所消耗的DB TIME时间，单位为秒
DB CPU(s): 本服务名所消耗的DB CPU 时间，单位为秒
Physical Reads : 本服务名所消耗的物理读Logical Reads : 本服务所消耗的逻辑读
2-7 Service Wait Class Stats
Service Wait Class Stats
- Wait Class info for services in the Service Statistics section.
- Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
- Time Waited (Wt Time) in seconds
| Service Name | User I/O Total Wts | User I/O Wt Time | Concurcy Total Wts | Concurcy Wt Time | Admin Total Wts | Admin Wt Time | Network Total Wts | Network Wt Time |
|---|---|---|---|---|---|---|---|---|
| SYS$USERS | 31504 | 6 | 5479 | 41 | 0 | 0 | 7965512 | 15 |
| SYS$BACKGROUND | 264 | 0 | 148 | 8 | 0 | 0 | 0 | 0 |
| cufsoa | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
User I/O Total Wts : 对应该服务名下用户I/O类等待的总的次数
User I/O Wt Time :对应该服务名下用户I/O累等待的总时间,单位为 1/100秒
Concurcy Total Wts: 对应该服务名下 Concurrency 类型等待的总次数
Concurcy Wt Time :对应该服务名下 Concurrency 类型等待的总时间， 单位为 1/100秒
Admin Total Wts: 对应该服务名下Admin 类等待的总次数
Admin Wt Time: 对应该服务名下Admin类等待的总时间，单位为1/100秒
Network Total Wts : 对应服务名下Network类等待的总次数
Network Wt Time：对应服务名下Network类等待的总事件，单位为1/100秒
2-8 Host CPU
Host CPU
| CPUs | Cores | Sockets | Load Average Begin | Load Average End | %User | %System | %WIO | %Idle |
|---|---|---|---|---|---|---|---|---|
| 16 | 16 | 4 | 0.48 | 0.62 | 4.1 | 2.8 | 0.2 | 93.0 |
Load Average begin/end值代表每个CPU的大致运行队列大小。
2-8 Instance CPU
Instance CPU
| %Total CPU | %Busy CPU | %DB time waiting for CPU (Resource Manager) |
|---|---|---|
| 6.9 | 99.5 | 0.0 |
%Busy CPU，该实例所使用的Cpu占总的被使用CPU的比例 % of busy CPU for Instance
例如共4个逻辑CPU，其中3个被完全使用，3个中的1个完全被该实例使用，则%Total CPU= ¼ =25%，而%Busy CPU= 1/3= 33%
当CPU高时一般看%Busy CPU可以确定CPU到底是否是本实例消耗的，还是主机上其他程序3 TOP SQL3-1 SQL ordered by Elapsed Time ，
SQL ordered by Elapsed Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
- %Total – Elapsed Time as a percentage of Total DB time
- %CPU – CPU Time as a percentage of Elapsed Time
- %IO – User I/O Time as a percentage of Elapsed Time
- Captured SQL account for 13.9% of Total DB Time (s): 3,886
- Captured PL/SQL account for 0.1% of Total DB Time (s): 3,886
| Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 192.02 | 916,682 | 0.00 | 4.94 | 46.61 | 0.00 | 7gwxpwru0czqw | ||
| 27.05 | 59 | 0.46 | 0.70 | 98.60 | 0.00 | 4nwgd6fbmbkm0 |
注意对于PL/SQL，SQL Statistics不仅会体现该PL/SQL的执行情况，还会包括该PL/SQL包含的SQL语句的情况。
对于Top SQL很有必要一探究竟Elapsed Time (s): 该SQL累计运行所消耗的时间，Executions : 该SQL在快照时间内总计运行的次数 ；注意对于在快照时间内还没有执行完的SQL不计为一次，所以如果看到executions=0而又是TOP SQL,则很有可能是因为该SQL运行较旧还没执行完，需要特别关注一下。
Elapsed Time per Exec (s)：平均每次执行该SQL耗费的时间，对于OLTP类型的SELECT/INSERT/UPDATE/DELETE而言平均单次执行时间应当非常短，
如0.1秒 或者更短才能满足其业务需求，如果这类轻微的OLTP操作单次也要几秒钟的话，是无法满足对外业务的需求的；
如果这些操作也变得很慢，则会出现大量事务阻塞，系统负载升高，DB TIME急剧上升的现象。对于OLTP数据库而言 如果执行计划稳定，那么这些OLTP操作的性能应当是稳定的，但是一旦某个因素 发生变化,例如存储的明显变慢、内存换页的大量出现时则上述的这些transaction操作很可能成数倍到几十倍的变慢，这将让此事务系统短期内不可用。
%Total 该SQL所消耗的时间占总的DB Time的百分比， 即 (SQL Elapsed Time / Total DB TIME)
% CPU 该SQL 所消耗的CPU时间占该SQL消耗的时间里的比例，
%IO 该SQL 所消耗的I/O时间占该SQL消耗的时间里的比例，该指标说明了该语句是否是I/O敏感的SQL Id : 通过计算SQL 文本获得的SQL_ID ，不同的SQL文本必然有不同的SQL_ID， 对于10g~11g而言 只要SQL文本不变那么在数据库之间 该SQL 对应的SQL_ID应当不不变的， 12c中修改了SQL_ID的计算方法
3-2 SQL ordered by CPU Time
SQL ordered by CPU Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- %Total – CPU Time as a percentage of Total DB CPU
- %CPU – CPU Time as a percentage of Elapsed Time
- %IO – User I/O Time as a percentage of Elapsed Time
- Captured SQL account for 10.1% of Total CPU Time (s): 3,823
- Captured PL/SQL account for 0.0% of Total CPU Time (s): 3,823
| CPU Time (s) | Executions | CPU per Exec (s) | %Total | Elapsed Time (s) | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|---|
| 89.51 | 916,682 | 0.00 | 2.34 | 192.02 | 46.61 | 0.00 | 7gwxpwru0czqw | ||
| 26.67 | 59 | 0.45 | 0.70 | 27.05 | 98.60 | 0.00 | 4nwgd6fbmbkm0 |
CPU TIME : 该SQL在快照时间内累计执行所消耗的CPU时间片，单位为s
Executions : 该SQL在快照时间内累计执行的次数
CPU per Exec (s) ：该SQL 平均单次执行所消耗的CPU时间，%Total : 该SQL累计消耗的CPU时间占该时段总的DB CPU的比例，
% CPU 该SQL 所消耗的CPU时间占该SQL消耗的时间里的比例，该指标说明了该语句是否是CPU敏感的
%IO 该SQL 所消耗的I/O时间占该SQL消耗的时间里的比例， 该指标说明了该语句是否是I/O敏感的
3-3 Buffer Gets SQL ordered by Gets
SQL ordered by Reads
- %Total – Physical Reads as a percentage of Total Disk Reads
- %CPU – CPU Time as a percentage of Elapsed Time
- %IO – User I/O Time as a percentage of Elapsed Time
- Total Disk Reads: 1,427,765
- Captured SQL account for 0.0% of Total
| Physical Reads | Executions | Reads per Exec | %Total | Elapsed Time (s) | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 3,218 | 0.00 | 0.00 | 0.78 | 37.01 | 0.00 | 074w4jr8a9fu8 | ||
| 0 | 33,180 | 0.00 | 0.00 | 7.55 | 63.61 | 0.00 | 09tx5h4ngu7va |
注意 buffer gets 逻辑读是消耗CPU TIME的重要源泉， 但并不是说消耗CPU TIME的只有buffer gets。
大多数情况下 SQL order by CPU TIME和SQL order by buffers gets 2个部分的TOP SQL 及其排列顺序都是一样的，此种情况说明消耗最多buffer gets的 就是消耗最多CPU 的SQL ，如果我们希望降低系统的CPU使用率，那么只需要调优SQL 降低buffer gets 即可。
但也并不是100%的情况都是如此，CPU TIME的消耗者还包括函数运算、PL/SQL 控制、Latch /Mutex 的Spin等等， 所以SQL order by CPU TIME 和 SQL order by buffers gets 2个部分的TOP SQL 完全不一样也是有可能的，
Buffer Gets : 该SQL在快照时间内累计运行所消耗的buffer gets，包括了consistent read 和 current readExecutions : 该SQL在快照时间内累计执行的次数
Gets per Exec : 该SQL平均单次的buffer gets ， 对于事务型transaction操作而言 一般该单次buffer gets小于2000% Total 该SQL累计运行所消耗的buffer gets占总的db buffer gets的比率，
3-4 Physical Reads SQL ordered by Reads
SQL ordered by Physical Reads (UnOptimized)
- UnOptimized Read Reqs = Physical Read Reqts – Optimized Read Reqs
- %Opt – Optimized Reads as percentage of SQL Read Requests
- %Total – UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
- Total Physical Read Requests: 31,234
- Captured SQL account for 0.0% of Total
- Total UnOptimized Read Requests: 31,234
- Captured SQL account for 0.0% of Total
- Total Optimized Read Requests: 1
- Captured SQL account for 0.0% of Total
| UnOptimized Read Reqs | Physical Read Reqs | Executions | UnOptimized Reqs per Exec | %Opt | %Total | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 12 | 12 | 1 | 12.00 | 0.00 | 0.04 | gjm43un5cy843 | ||
| 0 | 0 | 3,218 | 0.00 | 0.00 | 074w4jr8a9fu8 |
Physical reads : 该SQL累计运行所消耗的物理读
Executions : 该SQL在快照时间内累计执行的次数
Reads per Exec : 该SQL单次运行所消耗的物理读， 对于OLTP transaction 类型的操作而言单次一般不超过100%Total : 该SQL 累计消耗的物理读占该时段总的物理读的比例，
3-5 Executions SQL ordered by Executions
SQL ordered by Executions
- %CPU – CPU Time as a percentage of Elapsed Time
- %IO – User I/O Time as a percentage of Elapsed Time
- Total Executions: 2,315,874
- Captured SQL account for 52.1% of Total
| Executions | Rows Processed | Rows per Exec | Elapsed Time (s) | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 916,682 | 916,316 | 1.00 | 192.02 | 46.6 | 0 | 7gwxpwru0czqw | ||
| 51,293 | 0 | 0.00 | 15.91 | 40.2 | 0 | fdywauggm8byz |
按照执行次数来排序的话，也是性能报告对比时一个重要的参考因素，因为如果TOP SQL的执行次数有明显的增长，那么性能问题的出现也是意料之中的事情了。当然执行次数最多的，未必便是对性能影响最大的TOP SQL
Executions : 该SQL在快照时间内累计执行的次数Rows Processed：该SQL在快照时间内累计执行所处理的总行数
Rows per Exec：SQL平均单次执行所处理的行数，这个指标在诊断一些数据问题造成的SQL性能问题时很有用
3-6 Parse Calls SQL ordered by Parse Calls
SQL ordered by Parse Calls
- Total Parse Calls: 2,294,317
- Captured SQL account for 52.6% of Total
| Parse Calls | Executions | % Total Parses | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|
| 916,829 | 916,682 | 39.96 | 7gwxpwru0czqw | select companyname from licens… | |
| 51,293 | 51,293 | 2.24 | fdywauggm8byz | select * from WorkFlowPlanSet … |
Parse Calls : 解析调用次数，与上文的 Load Profile中的Parse数一样包括 软解析soft parse和硬解析hard parse
Executions : 该SQL在快照时间内累计执行的次数%Total Parses : 本SQL 解析调用次数占该时段数据库总解析次数的比率
3-7 SQL ordered by Sharable MemoryShareable Mem(b): SQL对象所占用的共享内存使用量Executions : 该SQL在快照时间内累计执行的次数%Total : 该SQL 对象锁占共享内存 占总的共享内存的比率
3-8 SQL ordered by Version Count
SQL ordered by Version Count
- Only Statements with Version Count greater than 20 are displayed
| Version Count | Executions | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|
| 22 | 1 | 6wm3n4d7bnddg | SELECT source, (case when tim… |
Executions : 该SQL在快照时间内累计执行的次数
Hash Value : 共享SQL 的哈希值
4 Instance Activity Stats
4-1 Other by statistic name
Other Instance Activity Stats
- Ordered by statistic name
| Statistic | Total | per Second | per Trans |
|---|---|---|---|
| Batched IO (bound) vector count | 0 | 0.00 | 0.00 |
| Batched IO (full) vector count | 0 | 0.00 | 0.00 |
| Batched IO (space) vector count | 0 | 0.00 | 0.00 |
| Batched IO block miss count | 3 | 0.00 | 0.00 |
| Batched IO buffer defrag count | 0 | 0.00 | 0.00 |
| Batched IO double miss count | 1 | 0.00 | 0.00 |
| Batched IO same unit count | 0 | 0.00 | 0.00 |
| Batched IO single block count | 0 | 0.00 | 0.00 |
| Batched IO slow jump count | 0 | 0.00 | 0.00 |
| Batched IO vector block count | 3 | 0.00 | 0.00 |
| Batched IO vector read count | 1 | 0.00 | 0.00 |
| Block Cleanout Optim referenced | 6 | 0.00 | 0.00 |
| CCursor + sql area evicted | 321 | 0.09 | 0.00 |
| CPU used by this session | 189,051 | 52.39 | 0.21 |
| CPU used when call started | 187,601 | 51.99 | 0.20 |
| CR blocks created | 261 | 0.07 | 0.00 |
| Cached Commit SCN referenced | 567,554 | 157.29 | 0.62 |
| Commit SCN cached | 74 | 0.02 | 0.00 |
| DBWR checkpoint buffers written | 16,185 | 4.49 | 0.02 |
| DBWR checkpoints | 26 | 0.01 | 0.00 |
| DBWR object drop buffers written | 6 | 0.00 | 0.00 |
| DBWR revisited being-written buffer | 0 | 0.00 | 0.00 |
| DBWR tablespace checkpoint buffers written | 157 | 0.04 | 0.00 |
| DBWR thread checkpoint buffers written | 0 | 0.00 | 0.00 |
| DBWR transaction table writes | 122 | 0.03 | 0.00 |
| DBWR undo block writes | 6,538 | 1.81 | 0.01 |
| HSC Heap Segment Block Changes | 143,725 | 39.83 | 0.16 |
| HSC IDL Compressed Blocks | 0 | 0.00 | 0.00 |
| Heap Segment Array Inserts | 2,807 | 0.78 | 0.00 |
| Heap Segment Array Updates | 4,642 | 1.29 | 0.01 |
| IMU CR rollbacks | 218 | 0.06 | 0.00 |
| IMU Flushes |
这里每一个指标都代表一种数据库行为的活跃度，
虽然这些指标均只有Total、per Second每秒、 per Trans每事务 三个维度，但对诊断问题十分有用。例如：
1、 例如当 Top Event 中存在direct path read为Top 等待事件， 则需要分清楚是对普通堆表的direct read还是由于大量LOB读造成的direct path read， 这个问题可以借助 table scans (direct read)、table scans (long tables)、physical reads direct 、physical reads direct (lob) 、
physical reads direct temporary几个指标来分析，
假设 physical reads direct >> 远大于 physical reads direct (lob)+physical reads direct temporary ， 且有较大的table scans (direct read)、table scans (long tables) (注意这2个指标代表的是 扫描表的次数 不同于上面的phsical reads 的单位为 块数*次数)， 则说明了是 大表扫描引起的direct path read。
2、 例如当 Top Event中存在enq Tx:index contention等待事件， 则需要分析root node splits 、branch node splits 、leaf node 90-10 splits 、leaf node splits 、failed probes on index block rec 几个指标
4-2 Statistics with absolute values
Instance Activity Stats – Absolute Values
- Statistics with absolute values (should not be diffed)
| Statistic | Begin Value | End Value |
|---|---|---|
| logons current | 85 | 80 |
| opened cursors current | 793 | 275 |
| session cursor cache count | 17,245,584 | 17,263,174 |
| session pga memory | 373,767,091,272 | 373,863,965,728 |
| session pga memory max | 429,564,886,056 | 429,369,395,424 |
| session uga memory | 283,783,601,784 | 283,855,681,528 |
| session uga memory max | 758,640,598,976 | 759,257,627,368 |
Instance Activity Stats – Absolute Values是显示快照 起点 和终点的一些指标的绝对值
logon current ：当前时间点的登录数opened cursors current ：当前打开的游标数session cursor cache count ：当前存在的session缓存游标数
5 IO 统计5-1 Tablespace IO Stats 基于表空间分组的IO信息
Tablespace IO Stats
- ordered by IOs (Reads + Writes) desc
| Tablespace | Reads | Av Rds/s | Av Rd(ms) | Av Blks/Rd | 1-bk Rds/s | Av 1-bk Rd(ms) | Writes | Writes avg/s | Buffer Waits | Av Buf Wt(ms) |
|---|---|---|---|---|---|---|---|---|---|---|
| USERS | 24,117 | 7 | 0.02 | 26.68 | 4,267 | 5.18 | 0 | 1 | 4 | 0.00 |
| 1 | 6,391 | 2 | 0.00 | 122.52 | 714 | 0.06 | 0 | 0 | 19 | 1.05 |
| 1 | 271 | 0 | 1.11 | 1.07 | 1,673 | 0.07 | 1 | 0 | 2 | 0.00 |
| SYSAUX | 138 | 0 | 1.74 | 1.00 | 904 | 0.04 | 2 | 0 | 0 | 0.00 |
| UNDOTBS1 | 0 | 0 | 0.00 | 0.00 | 652 | 0.00 | 0 | 0 | 4 | 2.50 |
| SYSTEM | 299 | 0 | 0.94 | 1.00 | 122 | 0.08 | 1 | 0 | 1 | 0.00 |
| TEMP | 6 | 0 | 0.00 | 1.00 | 1 | 0.00 | 0 | 0 | 0 | 0.00 |
reads : 指 该表空间上发生的物理读的次数(单位不是块，而是次数)
Av Reads/s : 指该表空间上平均每秒的物理读次数 (单位不是块，而是次数)
Av Rd(ms): 指该表空间上每次读的平均读取延迟
Av Blks/Rd: 指该表空间上平均每次读取的块数目，因为一次物理读可以读多个数据块；如果Av Blks/Rd>>1则可能系统有较多db file scattered read 可能是诊断FULL TABLE SCAN或FAST FULL INDEX SCAN，需要关注table scans (long tables) 和index fast full scans (full) 2个指标
Writes : 该表空间上发生的物理写的次数 ; 对于那些Writes总是等于0的表空间 不妨了解下是否数据为只读，如果是可以通过read only tablespace来解决 RAC中的一些性能问题。
Av Writes/s : 指该表空间上平均每秒的物理写次数
buffer Waits: 该表空间上发生buffer busy waits和read by other session的次数( 9i中buffer busy waits包含了read by other session)。
Av Buf Wt(ms): 该表空间上发生buffer Waits的平均等待时间，单位为ms
5-2 File I/O
File IO Stats
- ordered by Tablespace, File
| Tablespace | Filename | Reads | Av Rds/s | Av Rd(ms) | Av Blks/Rd | 1-bk Rds/s | Av 1-bk Rd(ms) | Writes | Writes avg/s | Buffer Waits | Av Buf Wt(ms) |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 01.dbf | 6,391 | 2 | 0.00 | 122.52 | 0 | 0.09 | 714 | 0 | 19 | 1.05 |
| 1 | 01.dbf | 271 | 0 | 1.11 | 1.07 | 0 | 1.09 | 1,673 | 0 | 2 | 0.00 |
| SYSAUX | 01.dbf | 138 | 0 | 1.74 | 1.00 | 0 | 1.74 | 904 | 0 | 0 | 0.00 |
| SYSTEM | 01.dbf | 299 | 0 | 0.94 | 1.00 | 0 | 0.97 | 122 | 0 | 1 | 0.00 |
| TEMP | 01.dbf | 6 | 0 | 0.00 | 1.00 | 0 | 0.00 | 1 | 0 | 0 | |
| UNDOTBS1 | 01.dbf | 0 | 0 | 0 | 652 | 0 | 4 | 2.50 | |||
| USERS | 01.dbf | 24,117 | 7 | 0.02 | 26.68 | 5 | 0.02 | 4,267 | 1 | 4 | 0.00 |
Tablespace 表空间名
FileName 数据文件的路径
Reads: 该数据文件上累计发生过的物理读次数，不是块数
Av Reads/s: 该数据文件上平均每秒发生过的物理读次数，不是块数
Av Rd(ms): 该数据文件上平均每次物理读取的延迟，单位为ms
Av Blks/Rd: 该数据文件上平均每次读取涉及到的块数，OLTP环境该值接近 1
Writes : 该数据文件上累计发生过的物理写次数，不是块数
Av Writes/s: 该数据文件上平均每秒发生过的物理写次数，不是块数
buffer Waits: 该数据文件上发生buffer busy waits和read by other session的次数
Av Buf Wt(ms): 该数据文件上发生buffer Waits的平均等待时间，单位为ms若某个表空间上有较高的IO负载，则有必要分析一下是否其所属的数据文件上的IO 较为均匀还是存在倾斜， 是否需要结合存储特征来将数据均衡分布到不同磁盘上的数据文件上，以优化 I/O
6 缓冲池统计 Buffer Pool Statistics
Buffer Pool Statistics
- Standard block size Pools D: default, K: keep, R: recycle
- Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
| P | Number of Buffers | Pool Hit% | Buffer Gets | Physical Reads | Physical Writes | Free Buff Wait | Writ Comp Wait | Buffer Busy Waits |
|---|---|---|---|---|---|---|---|---|
| D | 354,600 | 100 | 80,349,220 | 1,603 | 16,200 | 0 | 0 | 30 |
P: pool池的名字
D: 默认的缓冲池 default buffer pool ,
K : Keep Pool ,
R: Recycle Pool ;
2k 4k 8k 16k 32k: 代表各种非标准块大小的缓冲池
Number of buffers: 实际的 缓冲块数目， 约等于池的大小/池的块大小
Pool Hit % : 该缓冲池的命中率
Buffer Gets: 对该缓冲池的中块的访问次数包括consistent gets 和db block gets
Physical Reads: 该缓冲池Buffer Cache引起了多少物理读，其实是physical reads cache ，单位为 块数*次数
Physical Writes ：该缓冲池中Buffer cache被写的物理写，其实是physical writes from cache，单位为 块数*次数
Free Buffer Waits: 等待空闲缓冲的次数，可以看做该buffer pool 发生free buffer waits 等待的次数
Write Comp Wait: 等待DBWR写入脏buffer到磁盘的次数，可以看做该buffer pool发生write complete waits等待的次数
Buffer Busy Waits: 该缓冲池发生buffer busy wait 等待的次数
7-1 Checkpoint Activity 检查点
Checkpoint Activity
- Total Physical Writes: 16,478
| MTTR Writes | Log Size Writes | Log Ckpt Writes | Other Settings Writes | Autotune Ckpt Writes | Thread Ckpt Writes |
|---|---|---|---|---|---|
| 0 | 0 | 0 | 0 | 16,028 | 0 |
Instance Recovery Stats 实例恢复
Instance Recovery Stats
- B: Begin Snapshot, E: End Snapshot
| Targt MTTR (s) | Estd MTTR (s) | Recovery Estd IOs | Actual RedoBlks | Target RedoBlks | Log Sz RedoBlks | Log Ckpt Timeout RedoBlks | Log Ckpt Interval RedoBlks | Opt Log Sz(M) | Estd RAC Avail Time | |
|---|---|---|---|---|---|---|---|---|---|---|
| B | 0 | 23 | 1056 | 4461 | 39600 | 3397383 | 39600 | |||
| E | 0 | 23 | 1505 | 16588 | 74323 | 3397383 | 74323 |
MTTR Writes : 为了满足FAST_START_MTTR_TARGET指定的MTTR值而做出的物理写 WRITES_MTTR
Log Size Writes ：由于最小的redo log file而做出的物理写
WRITES_LOGFILE_SIZELog Ckpt
writes:由于 LOG_CHECKPOINT_INTERVAL 和 LOG_CHECKPOINT_TIMEOUT 驱动的增量检查点而做出的物理写
WRITES_LOG_CHECKPOINT_SETTINGS
Other Settings Writes ：由于其他设置(例如FAST_START_IO_TARGET）而引起的物理写， WRITES_OTHER_SETTINGS
Autotune Ckpt Writes : 由于自动调优检查点而引起的物理写， WRITES_AUTOTUNE
Thread Ckpt Writes ：由于thread checkpoint而引起的物理写，
WRITES_FULL_THREAD_CKPTB 代表 开始点， E 代表结尾Targt MTTR (s) : 目标MTTR (mean time to recover)意为有效恢复时间，单位为秒。
estimated_mttr (s): 当前基于脏buffer和重做日志块的数量，而评估出的有效恢复时间 。它的估算告诉用户以当下系统的负载若发生实例crash，则需要多久时间来做crash recovery的前滚操作，之后才能打开数据库。
Recovery Estd IOs ：实际是当前buffer cache中的脏块数量，一旦实例崩溃 这些脏块要被前滚
Actual RedoBlks ：当前实际需要恢复的redo重做块数量
Target RedoBlks :是Log Sz RedoBlks、Log Ckpt Timeout RedoBlks、 Log Ckpt Interval RedoBlks 三者的最小值
Log Sz RedoBlks : 代表必须在log file switch日志切换之前完成的 checkpoint 中涉及到的redo block，也叫max log lag；
Log Ckpt Timeout RedoBlks ：为了满足LOG_CHECKPOINT_TIMEOUT 所需要处理的redo block数，lag for checkpoint timeout ；
Log Ckpt Interval RedoBlks ：为了满足LOG_CHECKPOINT_INTERVAL 所需要处理的redo block数， lag for checkpoint interval；
Opt Log Sz(M) : 基于FAST_START_MTTR_TARGET 而估算出来的redo logfile 的大小，单位为MB 。
7-2 Buffer Pool Advisory 缓冲池建议
Buffer Pool Advisory
- Only rows with estimated physical reads >0 are displayed
- ordered by Block Size, Buffers For Estimate
| P | Size for Est (M) | Size Factor | Buffers (thousands) | Est Phys Read Factor | Estimated Phys Reads (thousands) | Est Phys Read Time | Est %DBtime for Rds |
|---|---|---|---|---|---|---|---|
| D | 288 | 0.10 | 35 | 96.40 | 2,217,689 | 1 | 466300.00 |
| D | 576 | 0.20 | 71 | 31.73 | 729,999 | 1 | 153419.00 |
| D | 864 | 0.30 | 106 | 13.10 | 301,470 | 1 | 63293.00 |
| D | 1,152 | 0.40 | 142 | 7.56 | 173,942 | 1 | 36472.00 |
| D | 1,440 | 0.49 | 177 | 4.62 | 106,305 | 1 | 22247.00 |
| D | 1,728 | 0.59 | 213 | 3.27 | 75,245 | 1 | 15715.00 |
| D | 2,016 | 0.69 | 248 | 2.58 | 59,336 | 1 | 12369.00 |
| D | 2,304 | 0.79 | 284 | 2.03 | 46,738 | 1 | 9720.00 |
| D | 2,592 | 0.89 | 319 | 1.46 | 33,610 | 1 | 6958.00 |
| D | 2,880 | 0.99 | 355 | 1.02 | 23,504 | 1 | 4833.00 |
| D | 2,912 | 1.00 | 359 | 1.00 | 23,006 | 1 | 4728.00 |
| D | 3,168 | 1.09 | 390 | 0.74 | 16,975 | 1 | 3460.00 |
| D | 3,456 | 1.19 | 426 | 0.62 | 14,344 | 1 | 2907.00 |
| D | 3,744 | 1.29 | 461 | 0.56 | 12,781 | 1 | 2578.00 |
| D | 4,032 | 1.38 | 496 | 0.50 | 11,420 | 1 | 2292.00 |
| D | 4,320 | 1.48 | 532 | 0.46 | 10,541 | 1 | 2107.00 |
| D | 4,608 | 1.58 | 567 | 0.43 | 9,966 | 1 | 1986.00 |
| D | 4,896 | 1.68 | 603 | 0.42 | 9,587 | 1 | 1906.00 |
| D | 5,184 | 1.78 | 638 | 0.41 | 9,326 | 1 | 1851.00 |
| D | 5,472 | 1.88 | 674 | 0.40 | 9,124 | 1 | 1809.00 |
| D | 5,760 | 1.98 | 709 | 0.38 | 8,778 | 1 | 1736.00 |
缓冲池的颗粒大小
P 指缓冲池的名字可能包括有
D default buffer pool ,
K Keep Pool ,
R recycle Pool
Size For Est(M): 指以该尺寸的buffer pool作为评估的对象，一般是目前current size的 10% ~ 200%，以便了解 buffer pool增大 ~减小对物理读的影响
Size Factor : 尺寸因子，只对应buffer pool大小对当前设置的比例因子Buffers (thousands) ：指这个buffer pool 尺寸下的buffer数量， 要乘以1000才是实际值
Est Phys Read Factor ：评估的物理读因子，
Estimated Phys Reads (thousands)：评估的物理读数目，要乘以1000才是实际值， 显然不同尺寸的buffer pool对应不同的评估的物理读数目
Est Phys Read Time ：评估的物理读时间
Est %DBtime for Rds：评估的物理读占DB TIME的比率我们看buffer pool advisory 一般有2个目的：在物理读较多的情况下，希望通过增加buffer pool 大小来缓解物理读等待，这是我们关注Size Factor > 1的buffer pool尺寸是否能共有效减少Est Phys Read Factor， 如果Est Phys Read Factor随着Size Factor增大而显著减少，那么说明增大buffer cache 是可以有效减少物理读的。在内存紧张的情况下 ，希望从buffer pool中匀出部分内存来移作他用，但是又不希望 buffer cache变小导致物理读增多性能下降，则此时观察Est Phys Read Factor 是否随着Size Factor 减小而显著增大，如果不是 则说明减少部分buffer cache 不会导致 物理读大幅增加，也就可以安心减少buffer cache
7-3 PGA Aggr Summary
PGA Aggr Summary
- PGA cache hit % – percentage of W/A (WorkArea) data processed only in-memory
| PGA Cache Hit % | W/A MB Processed | Extra W/A MB Read/Written |
|---|---|---|
| 100.00 | 9,696 | 0 |
PGA Cache Hit % : 指W/A WorkArea工作区的数据仅在内存中处理的比率， PGA缓存命中率
W/A MB processes: 指在Workarea中处理过的数据的量，单位为MB
Extra W/A MB Read/Written : 指额外从磁盘上读写的工作区数据,单位为 MB
7-4 PGA Aggr Target StatsPGA Aggr Target Stats
No data exists for this section of the report.
PGA Aggr Target(M) ：本质上就是pga_aggregate_target
Auto PGA Target(M) : 在自动PGA 管理模式下实际可用的工作区内存 “aggregate PGA auto target “，
W/A PGA Used(M) ：所有的工作区workarea(包括manual和 auto)使用的内存总和量， 单位为MB
%PGA W/A Mem: 分配给workarea的内存量占总的PGA的比例，
%Auto W/A Mem : AUTO 自动工作区管理所控制的内存占总的workarea内存的比例
%Man W/A Mem : MANUAL 手动工作区管理所控制的内存占总的workarea内存的比例
Global Mem Bound(K) : 指 在自动PGA管理模式下一个工作区所能分配的最大内存(注意 一个SQL执行过程中可能有多个工作区workarea)。
Global Mem Bound(K)这个指标在实例运行过程中将被持续性的修正，以反应数据库当时工作区的负载情况。显然在有众多活跃工作区的系统负载下相应地Global Mem Bound将会下降。但应当保持global bound值不要小于1 MB ， 否则建议 调高pga_aggregate_target
7-5 PGA Aggr Target Histogram
PGA Aggr Target Histogram
- Optimal Executions are purely in-memory operations
| Low Optimal | High Optimal | Total Execs | Optimal Execs | 1-Pass Execs | M-Pass Execs |
|---|---|---|---|---|---|
| 2K | 4K | 96,633 | 96,633 | 0 | 0 |
| 64K | 128K | 504 | 504 | 0 | 0 |
| 128K | 256K | 88 | 88 | 0 | 0 |
| 256K | 512K | 70 | 70 | 0 | 0 |
| 512K | 1024K | 4,616 | 4,616 | 0 | 0 |
| 1M | 2M | 2,915 | 2,915 | 0 | 0 |
| 2M | 4M | 643 | 643 | 0 | 0 |
| 4M | 8M | 6 | 6 | 0 | 0 |
| 8M | 16M | 9 | 9 | 0 | 0 |
| 16M | 32M | 6 | 6 | 0 | 0 |
| 32M | 64M | 2 | 2 | 0 | 0 |
Low Optimal：此行所包含工作区workarea最适合内存要求的下限
High Optimal：此行所包含工作区workarea最适合内存要求的上限
Total Execs: 在 Low Optimal~High Optimal 范围工作区内完成的总执行数
Optimal execs: optimal执行是指完全在PGA内存中完成的执行次数
1-pass Execs : 指操作过程中仅发生1次磁盘读取的执行次数
M-pass Execs: 指操作过程中发生了1次以上的磁盘读取，频发磁盘读取的执行次数
7-6 PGA Memory AdvisoryPGA Memory Advisory
- When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
| PGA Target Est (MB) | Size Factr | W/A MB Processed | Estd Extra W/A MB Read/ Written to Disk | Estd PGA Cache Hit % | Estd PGA Overalloc Count | Estd Time |
|---|---|---|---|---|---|---|
| 564 | 0.13 | 8,289,305.45 | 287,052.29 | 97.00 | 45 | 4,922,528,131 |
| 1,128 | 0.25 | 8,289,305.45 | 84,121.76 | 99.00 | 0 | 4,806,053,130 |
| 2,256 | 0.50 | 8,289,305.45 | 83,858.20 | 99.00 | 0 | 4,805,901,855 |
| 3,384 | 0.75 | 8,289,305.45 | 83,858.20 | 99.00 | 0 | 4,805,901,855 |
| 4,512 | 1.00 | 8,289,305.45 | 60,642.91 | 99.00 | 0 | 4,792,577,099 |
| 5,414 | 1.20 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 6,317 | 1.40 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 7,219 | 1.60 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 8,122 | 1.80 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 9,024 | 2.00 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 13,536 | 3.00 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 18,048 | 4.00 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 27,072 | 6.00 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
| 36,096 | 8.00 | 8,289,305.45 | 60,521.43 | 99.00 | 0 | 4,792,507,373 |
PGA Target Est (MB) 用以评估的 PGA_AGGREGATE _TARGET值
Size Factr ， 当前用以评估的PGA_AGGREGATE _TARGET和当前实际设置的PGA_AGGREGATE_TARGET之间的比例因子PGA Target Est / PGA_AGGREGATE_TARGE
W/A MB Processed ：workarea中要处理的数据量， 单位为MB
Estd Extra W/A MB Read/ Written to Disk : 以 one-pass 、M-Pass方式处理的数据量预估值， 单位为MB
Estd P Cache Hit % : 预估的PGA缓存命中率
Estd PGA Overalloc Count: 预估的PGA过载量,所述PGA_AGGREGATE _TARGET仅是一个目标值，无法真正限制PGA内存的使用，当出现 PGA内存硬性需求时会产生PGA overallocate 过载(When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0)
7-7 Shared Pool Advisory
Shared Pool Advisory
- SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
- Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
| Shared Pool Size(M) | SP Size Factr | Est LC Size (M) | Est LC Mem Obj | Est LC Time Saved (s) | Est LC Time Saved Factr | Est LC Load Time (s) | Est LC Load Time Factr | Est LC Mem Obj Hits (K) |
|---|---|---|---|---|---|---|---|---|
| 3,040 | 0.60 | 416 | 60,195 | 3,762,898 | 0.44 | 5,169,739 | 17.62 | 974,023 |
| 3,552 | 0.70 | 927 | 86,857 | 5,146,665 | 0.60 | 3,785,972 | 12.90 | 1,140,581 |
| 4,064 | 0.80 | 1,438 | 115,517 | 6,393,850 | 0.74 | 2,538,787 | 8.65 | 1,276,670 |
| 4,576 | 0.90 | 1,949 | 138,557 | 7,580,669 | 0.88 | 1,351,968 | 4.61 | 1,402,383 |
| 4,768 | 0.94 | 2,141 | 147,522 | 8,009,651 | 0.93 | 922,986 | 3.15 | 1,447,003 |
| 4,800 | 0.94 | 2,173 | 149,280 | 8,079,477 | 0.94 | 853,160 | 2.91 | 1,454,012 |
| 4,832 | 0.95 | 2,205 | 150,603 | 8,149,115 | 0.94 | 783,522 | 2.67 | 1,460,993 |
| 4,864 | 0.96 | 2,237 | 152,440 | 8,219,195 | 0.95 | 713,442 | 2.43 | 1,468,076 |
| 4,896 | 0.96 | 2,269 | 153,686 | 8,287,515 | 0.96 | 645,122 | 2.20 | 1,474,824 |
| 4,928 | 0.97 | 2,301 | 155,511 | 8,355,821 | 0.97 | 576,816 | 1.97 | 1,481,547 |
| 4,960 | 0.97 | 2,333 | 157,308 | 8,424,114 | 0.98 | 508,523 | 1.73 | 1,488,291 |
| 4,992 | 0.98 | 2,365 | 158,859 | 8,492,427 | 0.98 | 440,210 | 1.50 | 1,495,136 |
| 5,024 | 0.99 | 2,397 | 160,249 | 8,559,773 | 0.99 | 372,864 | 1.27 | 1,502,818 |
| 5,056 | 0.99 | 2,429 | 161,635 | 8,600,300 | 1.00 | 332,337 | 1.13 | 1,507,575 |
| 5,088 | 1.00 | 2,461 | 163,084 | 8,639,210 | 1.00 | 293,427 | 1.00 | 1,512,485 |
| 5,120 | 1.01 | 2,493 | 165,151 | 8,641,318 | 1.00 | 291,319 | 0.99 | 1,513,085 |
| 5,152 | 1.01 | 2,523 | 167,106 | 8,641,402 | 1.00 | 291,235 | 0.99 | 1,513,123 |
| 5,184 | 1.02 | 2,555 | 168,990 | 8,641,499 | 1.00 | 291,138 | 0.99 | 1,513,163 |
| 5,216 | 1.03 | 2,587 | 170,484 | 8,641,600 | 1.00 | 291,037 | 0.99 | 1,513,203 |
| 5,248 | 1.03 | 2,619 | 172,220 | 8,641,702 | 1.00 | 290,935 | 0.99 | 1,513,237 |
| 5,280 | 1.04 | 2,651 | 173,866 | 8,641,779 | 1.00 | 290,858 | 0.99 | 1,513,268 |
| 5,312 | 1.04 | 2,683 | 175,560 | 8,641,852 | 1.00 | 290,785 | 0.99 | 1,513,300 |
| 5,344 | 1.05 | 2,715 | 177,195 | 8,641,932 | 1.00 | 290,705 | 0.99 | 1,513,332 |
| 5,376 | 1.06 | 2,747 | 178,918 | 8,642,006 | 1.00 | 290,631 | 0.99 | 1,513,363 |
| 5,600 | 1.10 | 2,971 | 192,344 | 8,642,523 | 1.00 | 290,114 | 0.99 | 1,513,598 |
| 6,112 | 1.20 | 3,483 | 219,847 | 8,643,633 | 1.00 | 289,004 | 0.98 | 1,514,118 |
| 6,624 | 1.30 | 3,995 | 248,721 | 8,644,548 | 1.00 | 288,089 | 0.98 | 1,514,525 |
| 7,136 | 1.40 | 4,507 | 274,955 | 8,645,425 | 1.00 | 287,212 | 0.98 | 1,514,932 |
| 7,648 | 1.50 | 5,019 | 295,115 | 8,646,263 | 1.00 | 286,374 | 0.98 | 1,515,331 |
| 8,160 | 1.60 | 5,530 | 323,720 | 8,647,049 | 1.00 | 285,588 | 0.97 | 1,515,713 |
| 8,672 | 1.70 | 6,041 | 364,001 | 8,647,797 | 1.00 | 284,840 | 0.97 | 1,516,028 |
| 9,184 | 1.81 | 6,552 | 387,864 | 8,648,513 | 1.00 | 284,124 | 0.97 | 1,516,322 |
| 9,696 | 1.91 | 7,063 | 415,296 | 8,649,184 | 1.00 | 283,453 | 0.97 | 1,516,607 |
| 10,208 | 2.01 | 7,575 | 443,210 | 8,649,818 | 1.00 | 282,819 | 0.96 | 1,516,874 |
Shared Pool Size(M) : 用以评估的shared pool共享池大小，在AMM /ASMM环境下 shared_pool 大小都可能浮动
SP Size Factr ：共享池大小的比例因子，（Shared Pool Size for Estim / SHARED_POOL_SIZE）
Estd LC Size(M) : 评估的 library cache大小,单位为MB，因为是shared pool中包含 library cache 当然还有其他例如row cache
7-8 SGA Target Advisory
SGA Target Advisory
| SGA Target Size (M) | SGA Size Factor | Est DB Time (s) | Est Physical Reads |
|---|---|---|---|
| 4,192 | 0.50 | 5,510,525 | 3,005,545,869 |
| 5,240 | 0.63 | 3,074,402 | 3,005,545,869 |
| 6,288 | 0.75 | 1,609,103 | 408,575,030 |
| 7,336 | 0.88 | 1,558,431 | 80,417,920 |
| 8,384 | 1.00 | 1,549,598 | 23,006,128 |
| 9,432 | 1.13 | 1,547,429 | 23,006,128 |
| 10,480 | 1.25 | 1,545,569 | 17,321,314 |
| 11,528 | 1.38 | 1,543,864 | 17,321,314 |
| 12,576 | 1.50 | 1,542,315 | 17,321,314 |
| 13,624 | 1.63 | 1,540,920 | 17,321,314 |
| 14,672 | 1.75 | 1,539,836 | 14,284,505 |
| 15,720 | 1.88 | 1,539,526 | 12,365,794 |
| 16,768 | 2.00 | 1,539,526 | 11,896,469 |
SGA target Size : 用以评估的sga target大小 (sga_target)
SGA Size Factor: SGA Size的比例因子，
Est DB Time (s): 评估对应于该指定sga target size会产生多少量的DB TIME，单位为秒
Est Physical Reads：评估对应该指定的sga target size 会产生多少的物理读
7-9 Streams Pool Advisory
Streams Pool Advisory
| Size for Est (MB) | Size Factor | Est Spill Count | Est Spill Time (s) | Est Unspill Count | Est Unspill Time (s) |
|---|---|---|---|---|---|
| 32 | 0.50 | 0 | 0 | 0 | 0 |
| 64 | 1.00 | 0 | 0 | 0 | 0 |
| 96 | 1.50 | 0 | 0 | 0 | 0 |
| 128 | 2.00 | 0 | 0 | 0 | 0 |
| 160 | 2.50 | 0 | 0 | 0 | 0 |
| 192 | 3.00 | 0 | 0 | 0 | 0 |
| 224 | 3.50 | 0 | 0 | 0 | 0 |
| 256 | 4.00 | 0 | 0 | 0 | 0 |
| 288 | 4.50 | 0 | 0 | 0 | 0 |
| 320 | 5.00 | 0 | 0 | 0 | 0 |
| 352 | 5.50 | 0 | 0 | 0 | 0 |
| 384 | 6.00 | 0 | 0 | 0 | 0 |
| 416 | 6.50 | 0 | 0 | 0 | 0 |
| 448 | 7.00 | 0 | 0 | 0 | 0 |
| 480 | 7.50 | 0 | 0 | 0 | 0 |
| 512 | 8.00 | 0 | 0 | 0 | 0 |
| 544 | 8.50 | 0 | 0 | 0 | 0 |
| 576 | 9.00 | 0 | 0 | 0 | 0 |
| 608 | 9.50 | 0 | 0 | 0 | 0 |
| 640 | 10.00 | 0 | 0 | 0 | 0 |
Size for Est (MB) : 用以评估的 streams pool大小
Size Factor ：streams pool大小的比例因子
Est Spill Count ：评估出的当使用该大小的流池时 message溢出到磁盘的数量 ESTD_SPILL_COUNT
Est Spill Time (s)：评估出的当使用该大小的流池时 message溢出到磁盘的耗时，单位为秒 ESTD_SPILL_TIME
Est Unspill Count：评估的当使用该大小的流池时 message unspill 即从磁盘上读取的数量 ESTD_UNSPILL_COUNT
Est Unspill Time (s) ：评估的 当使用该大小的流池时 message unspill 即从磁盘上读取的耗时，单位为秒 ESTD_UNSPILL_TIME
8 Wait Statistics8-1 Buffer Wait Statistics
Buffer Wait Statistics
- ordered by wait time desc, waits desc
| Class | Waits | Total Wait Time (s) | Avg Time (ms) |
|---|---|---|---|
| data block | 26 | 0 | 1 |
| undo header | 4 | 0 | 3 |
该环节是对 缓冲池中各类型(class) 块 等待的汇总信息，wait的原因一般是 buffer busy waits 和 read by other session
class 数据块的class， 一个oracle数据块即有class 属性 还有type 属性，数据块中记录type属性(KCBH)， 而在buffer header里存有class属性(X$BH.class)
Waits: 该类型数据块的等待次数
Total Wait Time (s) : 该类型数据块的合计等待时间 单位为秒
Avg Time (ms) : 该类型数据块 平均每次等待的耗时， 单位 ms对于INSERT ON INDEX 引起的争用：
使用反向索引key
使用HASH分区和本地索引
可能的情况下 减少index的density
8-2 Enqueue Activity
Enqueue Activity
- only enqueues with waits are shown
- Enqueue stats gathered prior to 10g should not be compared with 10g data
- ordered by Wait Time desc, Waits desc
| Enqueue Type (Request Reason) | Requests | Succ Gets | Failed Gets | Waits | Wt Time (s) | Av Wt Time(ms) |
|---|---|---|---|---|---|---|
| KO-Multiple Object Checkpoint (fast object checkpoint) | 230 | 230 | 0 | 23 | 1 | 48.70 |
| TX-Transaction (row lock contention) | 8 | 8 | 0 | 8 | 0 | 25.00 |
| RO-Multiple Object Reuse (fast object reuse) | 30 | 30 | 0 | 2 | 0 | 10.00 |
Enqueue Type (Request Reason) enqueue 队列的类型，enqueue type是队列锁所要保护的资源如TM 表锁CF 控制文件锁，enqueue mode 是持有队列锁的模式 (SS、SX 、S、SSX、X)Requests : 申请对应的enqueue type资源或者队列转换(enqueue conversion 例如 S 转 SSX ) 的次数Succ Gets ：对应的enqueue被成功 申请或转换的次数
Failed Gets ：对应的enqueue的申请 或者转换失败的次数
Waits ：由对应的enqueue的申请或者转换而造成等待的次数
Wt Time (s) ：由对应的enqueue的申请或者转换而造成等待的等待时间
Av Wt Time(ms) ：由对应的enqueue的申请或者转换而造成等待的平均等待时间 ， Wt Time (s) / Waits ,单位为m主要的enqueue 等待事件：
enq: TX – row lock/index contention、allocate ITL等待事件
enq: TM – contention等待事件8-3 Undo Segment Summary
Undo Segment Summary
- 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 reUsed
| Undo TS# | Num Undo Blocks (K) | Number of Transactions | Max Qry Len (s) | Max Tx Concurcy | Min/Max TR (mins) | STO/ OOS | uS/uR/uU/ eS/eR/eU |
|---|---|---|---|---|---|---|---|
| 2 | 6.49 | 14,017 | 905 | 4 | 16/26.1 | 0/0 | 0/0/0/0/0/0 |
Undo Extent有三种状态 active 、unexpired 、expired
active => extent中 包括了活动的事务 ，active的undo extent 一般不允许被其他事务重用覆盖Max Qry Len (s) 该时段内 持续最久的查询 时间， 单位为秒Max Tx Concy 该时段内 最大的事务并发量
10-1 Latch Activity
Latch Activity
- “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
| Latch Name | Get Requests | Pct Get Miss | Avg Slps /Miss | Wait Time (s) | NoWait Requests | Pct NoWait Miss |
|---|---|---|---|---|---|---|
| AQ deq hash table latch | 1 | 0.00 | 0 | 0 | ||
| ASM db client latch | 2,458 | 0.00 | 0 | 0 | ||
| ASM map operation hash table | 1 | 0.00 | 0 | 0 | ||
| ASM network state latch | 57 | 0.00 | 0 | 0 | ||
| AWR Alerted Metric Element list | 28,644 | 0.00 | 0 | 0 | ||
| Change Notification Hash table latch | 1,202 | 0.00 | 0 | 0 | ||
| Consistent RBA | 11,572 | 0.00 | 0 | 0 | ||
| DML lock allocation | 40,814 | 0.00 | 0 | 0 | ||
| Event Group Locks | 559 | 0.00 | 0 | 0 | ||
| FAL Queue | 144 | 0.00 | 0 | 0 | ||
| FOB s.o list latch | 617 | 0.16 | 0.00 | 0 | 0 | |
| File State Object Pool Parent Latch | 1 | 0.00 | 0 | 0 | ||
| I/O Staticstics latch | 1 | 0.00 | 0 | 0 | ||
| IPC stats buffer allocation latch | 1 | 0.00 | 0 | 0 | ||
| In memory undo latch | 73,620 | 0.02 | 0.67 | 0 | 16,139 | 0.00 |
| JS Sh mem access | 7 | 0.00 | 0 | 0 | ||
| JS mem alloc latch | 4 | 0.00 | 0 | 0 | ||
| JS queue access latch | 5 | 0.00 | 0 | 0 | ||
| JS queue state obj latch | 26,134 | 0.00 | 0 | 0 |
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
- ordered by misses desc
| Latch Name | Get Requests | Misses | Sleeps | Spin Gets |
|---|---|---|---|---|
| row cache objects | 23,008,103 | 68,048 | 551 | 67,505 |
| cache buffers chains | 160,393,751 | 48,378 | 60 | 48,326 |
| session idle bit | 23,321,979 | 7,797 | 542 | 7,263 |
| shared pool | 4,190,965 | 5,811 | 878 | 5,031 |
| session allocation | 3,152,314 | 350 | 10 | 340 |
| redo allocation | 67,103 | 22 | 2 | 20 |
| mostly latch-free SCN | 11,861 | 21 | 4 | 17 |
| In memory undo latch | 73,620 | 18 | 12 | 6 |
| simulator hash latch | 4,671,494 | 18 | 4 | 14 |
| shared pool simulator | 276,285 | 14 | 3 | 11 |
| enqueue hash chains | 297,395 | 10 | 1 | 9 |
| call allocation | 2,328 | 3 | 1 | 2 |
| simulator lru latch | 7,463 | 3 | 2 | 1 |
latch name Latch闩的名字
Get Requests latch被以willing-to-wait模式申请并获得的次数misses 是指latch被以willing-to-wait 模式申请但是申请者必须等待的次数
10-3 Latch Miss SourcesLatch Miss Sources
- only latches with sleeps are shown
- ordered by name, sleeps desc
| Latch Name | Where | NoWait Misses | Sleeps | Waiter Sleeps |
|---|---|---|---|---|
| In memory undo latch | ktiFlush: child | 0 | 11 | 11 |
| In memory undo latch | kticmt: child | 0 | 3 | 1 |
| In memory undo latch | ktichg: child | 0 | 1 | 0 |
| cache buffers chains | kcbgtcr: fast path (cr pin) | 0 | 45 | 11 |
| cache buffers chains | kcbrls_2 | 0 | 18 | 50 |
| cache buffers chains | kcbchg1: mod cr pin | 0 | 14 | 5 |
| cache buffers chains | kcbgtcr: kslbegin excl | 0 | 13 | 11 |
| cache buffers chains | kcbgcur_2 | 0 | 5 | 1 |
| cache buffers chains | kcbgcur_4 | 0 | 1 | 1 |
| cache buffers chains | kcbgtcr_2 | 0 | 1 | 0 |
| call allocation | ksudlp: top call | 0 | 1 | 0 |
| enqueue hash chains | ksqrcl | 0 | 1 | 0 |
| mostly latch-free SCN | kcslcu3 | 0 | 4 | 4 |
| redo allocation | kcrfw_redo_write: before write | 0 | 2 | 0 |
| row cache objects | kqreqd: reget | 0 | 303 | 2 |
| row cache objects | kqrpre: find obj | 0 | 127 | 427 |
| row cache objects | kqrso | 0 | 94 | 93 |
| row cache objects | kqreqd | 0 | 24 | 28 |
| session allocation | ksucri_int : SSO | 0 | 5 | 0 |
| session allocation | ksuxds | 0 | 5 | 10 |
| session idle bit | ksupuc: set busy | 0 | 396 | 204 |
| session idle bit | ksupuc: clear busy | 0 | 133 | 235 |
| session idle bit | ksuxds | 0 | 59 | 149 |
| shared pool | kghalo | 0 | 566 | 416 |
| shared pool | kghalp | 0 | 188 | 164 |
| shared pool | kghupr1 | 0 | 90 | 263 |
| shared pool | kgh_heap_sizes | 0 | 16 | 12 |
| shared pool | kghasp | 0 | 10 | 6 |
| shared pool | kghfre | 0 | 7 | 12 |
| shared pool | kghfrunp: clatch: nowait | 0 | 2 | 0 |
| shared pool | kghfree_extents: scan | 0 | 1 | 5 |
| shared pool | kghfrunp: clatch: wait | 0 | 1 | 0 |
| shared pool simulator | kglsim_upd_newhp | 0 | 3 | 2 |
| simulator hash latch | kcbsacc: lookup dba | 0 | 4 | 4 |
| simulator lru latch | kcbs_simulate: simulate set | 0 | 2 | 0 |
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 request
Waiter Sleeps：等待者休眠的次数 times of sleeps that waiters did for each where; Sleep 是阻塞者等待的次数 ， Waiter Sleeps是被阻塞者等待的次数
10-4 Mutex Sleep Summary
Mutex Sleep Summary
- ordered by number of sleeps desc
| Mutex Type | Location | Sleeps | Wait Time (ms) |
|---|---|---|---|
| Cursor Pin | kksfbc [KKSCHLPIN1] | 4,526 | 0 |
| Cursor Pin | kksLockDelete [KKSCHLPIN6] | 2,996 | 0 |
| Cursor Pin | kkslce [KKSCHLPIN2] | 2,116 | 0 |
| Library Cache | kglhdgn2 106 | 285 | 0 |
| Library Cache | kglget2 2 | 172 | 0 |
| Library Cache | kglget1 1 | 164 | 0 |
| Library Cache | kgllkdl1 85 | 105 | 0 |
| Library Cache | kglhdgh1 64 | 65 | 0 |
| Library Cache | kglhdgn1 62 | 62 | 0 |
| Library Cache | kglpndl1 95 | 57 | 0 |
| Library Cache | kgldtin1 42 | 48 | 0 |
| Library Cache | kglrfcl1 79 | 46 | 0 |
| Library Cache | kglpin1 4 | 37 | 0 |
| Library Cache | kgllkc1 57 | 26 | 0 |
| Library Cache | kglobpn1 71 | 25 | 0 |
| Library Cache | kglpnal1 90 | 15 | 0 |
| hash table | kkscsSearchChildList [KKSHBKLOC2] | 3 | 0 |
| Library Cache | kglllal3 111 | 1 | 0 |
11 segment statistics
11-1 Segments by Logical Reads
Segments by Logical Reads
- Total Logical Reads: 81,939,646
- Captured Segments account for 92.6% of Total
| Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Logical Reads | %Total |
|---|---|---|---|---|---|---|
| O | USERS | TABLE | 41,127,232 | 50.19 | ||
| O | 1 | TABLE | 2,717,760 | 3.32 | ||
| O | 1 | TABLE | 2,600,624 | 3.17 | ||
| O | USERS | TABLE | 2,581,152 | 3.15 | ||
| O | 1 | TABLE | 2,211,792 | 2.70 |
owner : 数据段的所有者
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
- Total Physical Reads: 1,427,765
- Captured Segments account for 99.2% of Total
| Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Physical Reads | %Total |
|---|---|---|---|---|---|---|
| O | TABLE | 783,324 | 54.86 | |||
| O | USERS | TABLE | 624,402 | 43.73 | ||
| O | USERS | $$ | LOB | 8,316 | 0.58 | |
| O | TABLE | 145 | 0.01 | |||
| O | USERS | TABLE | 128 | 0.01 |
Physical Reads: 该数据段上发生过的物理读，单位为块数*次数%Total : 占总的物理读的百分比 ，(当前对象上发生过的逻辑读/Total DB 逻辑读)
11-3 Segments by Physical Read Requests
Segments by Physical Read Requests
- Total Physical Read Requests: 31,234
- Captured Segments account for 64.2% of Total
| Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | Phys Read Requests | %Total |
|---|---|---|---|---|---|---|
| O | USERS | $$ | LOB | 8,195 | 26.24 | |
| O | TABLE | 6,180 | 19.79 | |||
| O | USERS | TABLE | 5,242 | 16.78 | ||
| O | TABLE | 145 | 0.46 | |||
| O | USERS | TABLE | 128 | 0.41 |
Phys Read Requests ：物理读的申请次数
%Total ：(该段上发生的物理读的申请次数/ physical read IO requests)
12 Dictionary Cache Stats
Dictionary Cache Stats
- “Pct Misses” should be very low (< 2% in most cases)
- “Final Usage” is the number of cache entries being used
| Cache | Get Requests | Pct Miss | Scan Reqs | Pct Miss | Mod Reqs | Final Usage |
|---|---|---|---|---|---|---|
| dc_awr_control | 65 | 0.00 | 0 | 2 | 1 | |
| dc_files | 6 | 100.00 | 0 | 0 | 6 | |
| dc_global_oids | 435 | 4.14 | 0 | 0 | 308 | |
| dc_histogram_data | 3,518,709 | 0.05 | 0 | 0 | 6,216 | |
| dc_histogram_defs | 1,307,798 | 0.28 | 0 | 1 | 7,437 | |
| dc_object_grants | 38 | 7.89 | 0 | 0 | 26 | |
| dc_objects | 1,750,326 | 0.06 | 0 | 48 | 2,177 | |
| dc_profiles | 282 | 0.00 | 0 | 0 | 1 | |
| dc_rollback_segments | 886 | 0.00 | 0 | 0 | 22 | |
| dc_segments | 546,372 | 0.06 | 0 | 2 | 6,319 | |
| dc_sequences | 339 | 13.57 | 0 | 339 | 39 | |
| dc_tablespaces | 1,316,800 | 0.00 | 0 | 0 | 7 | |
| dc_users | 1,569,629 | 0.00 | 0 | 0 | 125 | |
| global database name | 2,591 | 0.00 | 0 | 0 | 1 | |
| outstanding_alerts | 30 | 0.00 | 0 | 0 | 5 | |
| sch_lj_oids | 4 | 25.00 | 0 | 0 | 1 |
Get Requests 申请获取该数据字典缓存对象的次数 gets
Miss : GETMISSES 申请获取该数据字典缓存对象但 miss的次数
Pct Miss ： GETMISSES /Gets ， Miss的比例 ，这个pct miss应当非常低 小于2%，否则有出现大量row cache lock的可能
Scan Reqs：扫描申请的次数 ，Pct Miss：SCANMISSES/SCANS
13 Library Cache Activity
Library Cache Activity
- “Pct Misses” should be very low
| Namespace | Get Requests | Pct Miss | Pin Requests | Pct Miss | Reloads | Invali- dations |
|---|---|---|---|---|---|---|
| ACCOUNT_STATUS | 318 | 0.31 | 0 | 0 | 0 | |
| BODY | 1,578 | 1.08 | 3,444 | 0.64 | 2 | 0 |
| CLUSTER | 179 | 1.12 | 179 | 1.12 | 0 | 0 |
| DBLINK | 320 | 0.63 | 0 | 0 | 0 | |
| EDITION | 189 | 0.00 | 363 | 0.00 | 0 | 0 |
| INDEX | 42 | 4.76 | 42 | 33.33 | 12 | 0 |
| OBJECT ID | 1 | 100.00 | 0 | 0 | 0 | |
| QUEUE | 63 | 0.00 | 215 | 0.00 | 0 | 0 |
| SCHEMA | 639 | 0.16 | 0 | 0 | 0 | |
| SQL AREA | 317,164 | 20.79 | 2,489,844 | 8.74 | 7,179 | 3,253 |
| SQL AREA BUILD | 75,120 | 96.34 | 0 | 0 | 0 | |
| SQL AREA STATS | 74,461 | 95.31 | 74,461 | 95.31 | 0 | 0 |
| SUBSCRIPTION | 7 | 0.00 | 7 | 0.00 | 0 | 0 |
| TABLE/PROCEDURE | 96,208 | 0.35 | 374,862 | 0.58 | 1,068 | 0 |
| TRIGGER | 2,027 | 3.75 | 11,088 | 1.25 | 61 | 0 |
NameSpace library cache 的命名空间
GETS Requests 该命名空间所包含对象的library cache lock被申请的次数
14 SGA信息14 -1 SGA Memory Summary
SGA Memory Summary
| SGA regions | Begin Size (Bytes) | End Size (Bytes) (if different) |
|---|---|---|
| Database Buffers | 3,019,898,880 | |
| Fixed Size | 2,265,984 | |
| Redo Buffers | 4,460,544 | |
| Variable Size | 10,435,431,552 |
14-2 SGA breakdown difference
SGA breakdown difference
- 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 snapshot
| Pool | Name | Begin MB | End MB | % Diff |
|---|---|---|---|---|
| java | free memory | 128.00 | 128.00 | 0.00 |
| large | PX msg pool | 7.81 | 7.81 | 0.00 |
| large | free memory | 152.19 | 152.19 | 0.00 |
| shared | KGLDA | 68.12 | 71.25 | 4.59 |
| shared | KGLH0 | 1,322.65 | 1,374.36 | 3.91 |
| shared | KGLHD | 201.44 | 206.36 | 2.44 |
| shared | SQLA | 1,710.04 | 1,874.21 | 9.60 |
| shared | free memory | 1,291.59 | 1,080.89 | -16.31 |
| shared | kglsim heap | 69.35 | 69.35 | 0.00 |
| shared | kglsim object batch | 119.14 | 119.14 | 0.00 |
| streams | free memory | 63.94 | 63.94 | 0.00 |
| buffer_cache | 2,880.00 | 2,880.00 | 0.00 | |
| fixed_sga | 2.16 | 2.16 | 0.00 | |
| log_buffer | 4.25 | 4.25 | 0.00 |
Pool 内存池的名字Name 内存池中细分组件的名字 例如KGLH0 存放KEL Heap 0 、SQLA存放SQL执行计划等Begin MB 快照开始时该组件的内存大小
End MB 快照结束时该组件的内存大小
% Diff 差异百分比以上
更多内容请关注微信公众号：数据与人
