1.什么是AWR

1> 生成单实例AWR报告：@?/rdbms/admin/awrrpt.sql

2> 生成Oracle RAC AWR报告：@?/rdbms/admin/awrgrpt.sql

3> 生成RAC 环境中特定数据库实例的 AWR 报告：@?/rdbms/admin/awrrpti.sql

4> 生成OracleRAC环境中多个数据库实例的AWR报告的方法：@?/rdbms/admin/awrgrpti.sql

5> 生成SQL语句的AWR报告：@?/rdbms/admin/awrsqrpt.sql

6> 生成特定数据库实例上某个SQL语句的AWR报告：@?/rdbms/admin/awrsqrpi.sql ----生成 AWR 时段对比报告

7> 生成单实例AWR时段对比报告@?/rdbms/admin/awrddrpt.sql

8> 生成Oracle RAC AWR时段对比报告@?/rdbms/admin/awrgdrpt.sql

9> 生成特定数据库实例的AWR时段对比报告@?/rdbms/admin/awrddrpi.sql

10> 生成OracleRAC环境下特定（多个）数据库实例的AWR时段对比报告@?/rdbms/admin/awrgdrpi.sql

1.1 AWR (Automatic Workload Repository)

一堆历史性能数据，放在SYSAUX表空间上， AWR和SYSAUX都是10g出现的，是Oracle调优的关键特性；大约1999年左右开始开发，已经有15年历史

默认快照间隔1小时，10g保存7天、11g保存8天; 可以通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS修改

DBA_HIST_WR_CONTROL

AWR程序核心是dbms_workload_repository包

@?/rdbms/admin/awrrpt 本实例

@?/rdbms/admin/awrrpti RAC中选择实例号

1.2 谁维护AWR?

主要是MMON(Manageability Monitor Process)和它的小工进程(m00x)

MMON的功能包括:

1.启动slave进程m00x去做AWR快照

2.当某个度量阀值被超过时发出alert告警

3.为最近改变过的SQL对象捕获指标信息

1.3 AWR小技巧

手动执行一个快照：

Exec dbms_workload_repository.create_snapshot; (这个要背出来哦，用的时候去翻手册，丢脸哦 J!)

创建一个AWR基线

Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id，end_snap_id ,baseline_name);

@?/rdbms/admin/awrddrpt AWR比对报告

@?/rdbms/admin/awrgrpt RAC 全局AWR

自动生成AWR HTML报告：

http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql

-- -----------------------------------------------------------------------------------

-- File Name : http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql

-- Author : DR Timothy S Hall

-- Description : Generates AWR reports for all snapsots between the specified start and end point.

-- Requirements : Access to the v$ views, UTL_FILE and DBMS_WORKLOAD_REPOSITORY packages.

-- Call Syntax : Create the directory with the appropriate path.

-- Adjust the start and end snapshots as required.

-- @generate_multiple_awr_reports.sql

-- Last Modified: 02/08/2007

-- -----------------------------------------------------------------------------------

CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/';

DECLARE

-- Adjust before use.

l_snap_start NUMBER := 1;

l_snap_end NUMBER := 10;

l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR';

l_last_snap NUMBER := NULL;

l_dbid v$database.dbid%TYPE;

l_instance_number v$instance.instance_number%TYPE;

l_file UTL_FILE.file_type;

l_file_name VARCHAR(50);

BEGIN

SELECT dbid

INTO l_dbid

FROM v$database;

SELECT instance_number

INTO l_instance_number

FROM v$instance;

FOR cur_snap IN (SELECT snap_id

FROM dba_hist_snapshot

WHERE instance_number = l_instance_number

AND snap_id BETWEEN l_snap_start AND l_snap_end

ORDER BY snap_id)

LOOP

IF l_last_snap IS NOT NULL THEN

l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w', 32767);

FOR cur_rep IN (SELECT output

FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))

LOOP

UTL_FILE.put_line(l_file, cur_rep.output);

END LOOP;

UTL_FILE.fclose(l_file);

END IF;

l_last_snap := cur_snap.snap_id;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

RAISE;

END;

/

2.WORKLOAD REPOSITORY report for

WORKLOAD REPOSITORY report for(数据库总体负载报告总结)

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
ORCL	1437652505	test	1	22-Apr-16 22:04	11.2.0.4.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
zyx.test.com	Linux x86 64-bit	2	2	1	1.94

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	2	23-Apr-16 00:00:36	36	4.9
End Snap:	3	23-Apr-16 01:00:44	38	4.6
Elapsed:		60.13 (mins)
DB Time:		1.51 (mins)

Elapsed 为该AWR性能报告的时间跨度(自然时间的跨度，例如前一个快照snapshot是4点生成的，后一个快照snapshot是6点生成的，则若使用@?/rdbms/admin/awrrpt 脚本中指定这2个快照的话，那么其elapsed = (6-4)=2 个小时)，一个AWR性能报告至少需要2个AWR snapshot性能快照才能生成 (注意这2个快照时间实例不能重启过，否则指定这2个快照生成AWR性能报告会报错)，AWR性能报告中的指标往往是后一个快照和前一个快照的指标的delta，这是因为累计值并不能反映某段时间内的系统workload。

DB TIME= 所有前台session花费在database调用上的总和时间：

注意是前台进程foreground sessions

包括CPU时间、IO Time、和其他一系列非空闲等待时间，别忘了cpu on queue time

DB TIME 不等于响应时间，DB TIME高了未必响应慢，DB TIME低了未必响应快

DB Time描绘了数据库总体负载，但要和elapsed time逝去时间结合其他来。

Average Active Session AAS= DB time/Elapsed Time

DB Time =60 min ， Elapsed Time =60 min AAS=60/60=1 负载一般

DB Time= 1min , Elapsed Time= 60 min AAS= 1/60 负载很轻

DB Time= 60000 min，Elapsed Time= 60 min AAS=1000 系统hang了吧？

DB TIME= DB CPU + Non-Idle Wait + Wait on CPU queue

如果仅有2个逻辑CPU，而2个session在60分钟都没等待事件，一直跑在CPU上，那么：

DB CPU= 2 * 60 mins ， DB Time = 2* 60 + 0 + 0 =120

AAS = 120/60=2 正好等于OS load 2。

如果有3个session都100%仅消耗CPU，那么总有一个要wait on queue

DB CPU = 2* 60 mins ，wait on CPU queue= 60 mins

AAS= (120+ 60)/60=3 主机load 亦为3，此时vmstat 看waiting for run time

真实世界中？ DB Cpu = xx mins ， Non-Idle Wait= enq:TX + cursor pin S on X + latch : xxx + db file sequential read + ……….. 阿猫阿狗

2.1 内存参数大小

Memory Statistics

	Begin	End
Host Mem (MB):	1,990.9	1,990.9
SGA use (MB):	560.0	560.0
PGA use (MB):	128.2	129.3
% Host Mem used for SGA+PGA:	34.57	34.62

Cache Sizes

	Begin	End
Buffer Cache:	364M	356M	Std Block Size:	8K
Shared Pool Size:	131M	138M	Log Buffer:	5,792K

内存管理方式：MSMM、ASMM(sga_target)、AMM(memory_target)

小内存有小内存的问题，大内存有大内存的麻烦！ ORA-04031（Shared Pool）???!!

Buffer cache和shared pool size的 begin/end值在ASMM、AMM和11gR2 MSMM下可是会动的哦！

这里说 shared pool一直收缩，则在shrink过程中一些row cache 对象被lock住可能导致前台row cache lock等解析等待，最好别让shared pool shrink。如果这里shared pool一直在grow，那说明shared pool原有大小不足以满足需求(可能是大量硬解析)，结合下文的解析信息和SGA breakdown来一起诊断问题。

2.2 Load Profile

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	0.0	0.1	0.00	0.01
DB CPU(s):	0.0	0.1	0.00	0.00
Redo size (bytes):	6,786.3	18,015.6
Logical read (blocks):	106.0	281.4
Block changes:	20.5	54.3
Physical read (blocks):	0.2	0.5
Physical write (blocks):	1.2	3.2
Read IO requests:	0.1	0.4
Write IO requests:	0.4	1.1
Read IO (MB):	0.0	0.0
Write IO (MB):	0.0	0.0
User calls:	4.1	10.9
Parses (SQL):	8.5	22.5
Hard parses (SQL):	1.0	2.7
SQL Work Area (MB):	0.1	0.1
Logons:	0.1	0.2
Executes (SQL):	20.2	53.7
Rollbacks:	0.0	0.0
Transactions:	0.4

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.84	In-memory Sort %:	100.00
Library Hit %:	91.57	Soft Parse %:	88.08
Execute to Parse %:	58.10	Latch Hit %:	99.98
Parse CPU to Parse Elapsd %:	67.85	% Non-Parse CPU:	80.12

指标	指标含义
redo size	单位 bytes，redo size可以用来估量update/insert/delete的频率，大的redo size往往对lgwr写日志，和arch归档造成I/O压力， Per Transaction可以用来分辨是大量小事务，还是少量大事务。如上例每秒redo 约1MB ，每个事务800 字节，符合OLTP特征
Logical Read	单位次数块数，相当于 “人次”，如上例 106 * db_block_size=0.83MB/s ，逻辑读耗CPU，主频和CPU核数都很重要，逻辑读高则DB CPU往往高，也往往可以看到latch: cache buffer chains等待。大量OLTP系统(例如siebel)可以高达几十乃至上百Gbytes。
Block changes	单位次数*块数，描绘数据变化频率
Physical Read	单位次数块数，如上例 0.2 8k = 1.6k/s，物理读消耗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 cache
User Calls	单位次数，用户调用数，more details from internal
Parses	解析次数，包括软解析+硬解析，软解析优化得不好，则夸张地说几乎等于每秒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%， sorts (disk) PGA Aggr一起看
Logons	登陆次数， logon storm 登陆风暴，结合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: 主要是把快照内的delta值除以快站时间的秒数，例如在 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 Second是我们审视数据的主要维度，任何性能数据脱离了时间模型则毫无意义。

在statspack/AWR出现之前的调优洪荒时代，有很多DBA 依赖 V$SYSSTAT等视图中的累计统计信息来调优，以当前的调优眼光来看，那无异于刀耕火种。

per transaction : 基于事务的维度，与per second相比是把除数从时间的秒数改为了该段时间内的事务数。这个维度的很大用户是用来识别应用特性的变化，若2个AWR性能报告中该维度指标出现了大幅变化，例如 redo size从本来per transaction 1k变化为 10k per transaction，则说明SQL业务逻辑肯定发生了某些变化。

注意AWR中的这些指标并不仅仅用来孤立地了解 Oracle数据库负载情况，实施调优工作。对于故障诊断例如HANG、Crash等，完全可以通过对比问题时段的性能报告和常规时间来对比，通过各项指标的对比往往可以找出病灶所在。

2.3 Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.84	In-memory Sort %:	100.00
Library Hit %:	91.57	Soft Parse %:	88.08
Execute to Parse %:	58.10	Latch Hit %:	99.98
Parse CPU to Parse Elapsd %:	67.85	% Non-Parse CPU:	80.12

上述所有指标的目标均为100%，即越大越好，在少数bug情况下可能超过100%或者为负值。

80%以上 %Non-Parse CPU

90%以上 Buffer Hit%, In-memory Sort%, Soft Parse%

95%以上 Library Hit%, Redo Nowait%, Buffer Nowait%

98%以上 Latch Hit%

2.3.1 Buffer Nowait %

Buffer Nowait % session申请一个buffer(兼容模式)不等待的次数比例。需要访问buffer时立即可以访问的比率，不兼容的情况在9i中是 buffer busy waits，从10g以后 buffer busy waits 分离为 buffer busy wait 和 read by other session2个等待事件 :

---- 9i 中 waitstat的总次数基本等于buffer busy waits等待事件的次数

SQL> select sum(TOTAL_WAITS) from v$system_event where event='buffer busy waits';

SUM(TOTAL_WAITS)

----------------

2092405

SQL> select sum(count) from v$waitstat;

SUM(COUNT)

----------

2092401

---- 10g waitstat的总次数基本等于 buffer busy waits 和 read by other session 等待的次数总和

sys@ORCL> select sum(TOTAL_WAITS) from v$system_event where event='buffer busy waits' or event='read by other session';

SUM(TOTAL_WAITS)

----------------

21

sys@ORCL>select sum(count) from v$waitstat;

SUM(COUNT)

----------

23

Buffer Nowait %的计算公式是 sum(v$waitstat.wait_count) / (v$sysstat statistic session logical reads)，例如在AWR中：

Buffer Wait Statistics

Class	Waits	Total Wait Time (s)	Avg Time (ms)
data block	24,5	2,2	9

undo header	7	2	3
undo block	1,1	0	0
1st level bmb	1	0	0

Key Instance Activity Stats

Statistic	Total	per Second	per Trans
session logical reads	382,388	105.99	281.37

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:

100.00

Buffer Nowait= ( 382,388 – (245+7+11+1))/ (382,388) = 0.99930= 99.94%

2.3.2 buffer HIT%

buffer HIT%: 经典的经典，高速缓存命中率，反应物理读和缓存命中间的纠结，但这个指标即便99% 也不能说明物理读等待少了

不合理的db_cache_size，或者是SGA自动管理ASMM /Memory 自动管理AMM下都可能因为db_cache_size过小引起大量的db file sequential /scattered read等待事件； maclean曾经遇到过因为大量硬解析导致ASMM 下shared pool共享池大幅度膨胀，而db cache相应缩小shrink的例子，最终db cache收缩到只有几百兆，本来没有的物理读等待事件都大幅涌现出来。

此外与 buffer HIT%相关的指标值得关注的还有 table scans(long tables) 大表扫描这个统计项目、此外相关的栏目还有Buffer Pool Statistics 、Buffer Pool Advisory等（如果不知道在哪里，直接找一个AWR 去搜索这些关键词即可)。

buffer HIT%在不同版本有多个计算公式：

在9i中

Buffer Hit Ratio = 1 – ((physical reads – physical reads direct – physical reads direct (lob)) / (db block gets + consistent gets – physical reads direct – physical reads direct (lob))

在10g以后：

Buffer Hit Ratio= 1 – ((‘physical reads cache’) / (‘consistent gets from cache’ + ‘db block gets from cache’)

注意：但是实际AWR中 似乎还是按照9i中的算法，虽然算法的区别对最后算得的比率影响不大。

对于buffer hit % 看它的命中率有多高没有意义，主要是关注 未命中的次数有多少。通过上述公式很容易反推出未命中的物理读的次数。

db block gets 、consistent gets 以及 session logical reads的关系如下：

db block gets＝db block gets direct＋　db block gets from cache

consistent gets　＝　consistent gets from cache＋　consistent gets direct

consistent gets from cache＝　consistent gets – examination + else

consistent gets – examination==>指的是不需要pin buffer直接可以执行consistent get的次数，常用于索引，只需要一次latch get

session logical reads = db block gets +consistent gets

其中physical reads 、physical reads cache、physical reads direct、physical reads direct (lob)几者的关系为：

physical reads = physical reads cache +　physical reads direct

这个公式其实说明了物理读有2种：

1> 物理读进入buffer cache中，是常见的模式 physical reads cache

2> 物理读直接进入PGA 直接路径读，即physical reads direct

physical reads

从磁盘读取的数据块的总数。此值可以大于“physical reads direct”加上“physical reads cache”的值，作为读取到进程的私有缓冲区也包括在这个统计。

physical reads cache

从磁盘读取的数据块的总数到缓冲区高速缓存。这是“物理读取”统计的一个子集。

physical reads direct

直接从磁盘读取的数字，绕过缓冲区缓存。例如，在高带宽，数据密集型的操作，如并行查询，读取磁盘块绕过缓冲区高速缓存，以最大限度地提高传输速率，并防止过早老化的共享数据块驻留在缓冲区缓存。

physical reads direct = physical reads direct (lob) + physical reads direct temporary tablespace + physical reads direct(普通)

这个公式也说明了直接路径读分成三个部分：

1> physical reads direct (lob) 直接路径读LOB对象

2> physical reads direct temporary tablespace 直接路径读临时表空间

3> physical read direct(普通) 普通的直接路径读，一般是11g开始的自动的大表direct path read和并行引起的direct path read

physical writes direct= physical writes direct (lob)+ physical writes direct temporary tablespace

DBWR checkpoint buffers written = DBWR thread checkpoint buffers written+ DBWR tablespace checkpoint buffers written+ DBWR PQ tablespace checkpoint buffers written+….

2.3.3 Redo nowait%

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~4G 每个，7~10组都是合适的。同时考虑优化redo logfile和datafile 的I/O。

2.3.4 In-memory Sort%

In-memory Sort%:这个指标因为它不计算workarea中所有的操作类型，所以现在越来越鸡肋了。纯粹在内存中完成的排序比例。数据来源于v$sysstat statistics sorts (disk) 和 sorts (memory)， In-memory Sort% = sort(memory) / ( sort(disk)+ sort(memory) )

2.3.5 Library Hit%

Library Hit%: library cache命中率，申请一个library cache object例如一个SQL cursor时，其已经在library cache中的比例。数据来源 V$librarycache的pins和pinhits。合理值：>95% ，该比例来源于1- ( Σ(pin Requests * Pct Miss) / Sum(Pin Requests) )

维护这个指标的重点是保持shared pool共享池有足够的Free Memory，且没有过多的内存碎片。显然过小的shared pool可用空间会导致library cache object被aged out换出共享池。

此外保证SQL语句绑定变量和游标共享也是很重要的因素。

2.3.6 Soft Parse%

Soft Parse: 软解析比例，无需多说的经典指标，数据来源v$sysstat statistics的parse count(total)和parse count(hard)。合理值>95%

Soft Parse %是AWR中另一个重要的解析指标，该指标反应了快照时间内软解析次数和总解析次数 (soft+hard 软解析次数+硬解析次数)的比值，若该指标很低，那么说明了可能存在剧烈的hard parse硬解析，大量的硬解析会消耗更多的CPU时间片并产生解析争用(此时可以考虑使用cursor_sharing=FORCE)；理论上我们总是希望 Soft Parse % 接近于100%，但并不是说100%的软解析就是最理想的解析状态，通过设置 session_cached_cursors参数和反复重用游标我们可以让解析来的更轻量级，即通俗所说的利用会话缓存游标实现的软软解析(soft soft parse)。

----script 查看游标利用率：

select

'session_cached_cursors' parameter,

lpad(value, 5) value,

decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage

from

( select

max(s.value) used

from

v$statname n,

v$sesstat s

where

n.name = 'session cursor cache count' and

s.statistic# = n.statistic#

),

( select

value

from

v$parameter

where

name = 'session_cached_cursors'

)

union all

select

'open_cursors',

lpad(value, 5),

to_char(100 * used / value, '990') || '%'

from

( select

max(sum(s.value)) used

from

v$statname n,

v$sesstat s

where

n.name in ('opened cursors current', 'session cursor cache count') and

s.statistic# = n.statistic#

group by

s.sid

),

( select

value

from

v$parameter

where

name = 'open_cursors'

)

/

PARAMETER VALUE USAGE

---------------------- ---------- -----

session_cached_cursors 50 100%

open_cursors 300 33%

select

to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,

to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,

to_char(100 * hard / calls, '999990.00')||'%' hard_parses

from

( select sum(value) calls from v$mystat m, v$statname s

where m.statistic# = s.statistic# and

name in ('parse count (total)', 'parse count (failures)') ),

( select value hard from v$mystat m, v$statname s

where m.statistic# = s.statistic# and name = 'parse count (hard)' ),

( select value sess from v$mystat m, v$statname s

where m.statistic# = s.statistic# and name = 'session cursor cache hits' )

/

CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES

----------------- ----------- -----------

333.90% -261.09% 27.19%

2.3.7 Execute to Parse%

Execute to Parse% 指标反映了执行解析比其公式为 1-(parse/execute) , 目标为100% 及接近于只执行而不解析。数据来源v$sysstat statistics parse count (total) 和execute count

在oracle中解析往往是执行的前提工作，但是通过游标共享可以解析一次执行多次，执行解析可能分成多种场景：

1> hard coding => 硬编码代码硬解析一次，执行一次，则理论上其执行解析比为 1:1 ，则理论上Execute to Parse =0 极差，且soft parse比例也为0%

2> 绑定变量但是仍软解析=》软解析一次，执行一次，这种情况虽然比前一种好但是执行解析比(这里的parse，包含了软解析和硬解析)仍是1:1，理论上Execute to Parse =0 极差，但是soft parse比例可能很高

3> 使用静态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等技术减少软解析。

2.3.8 Latch Hit%

Latch Hit%: willing-to-wait latch闩申请不要等待的比例。数据来源V$latch gets和misses

Latch Sleep Breakdown

Latch Name	Get Requests	Misses	Sleeps	Spin Gets
shared pool	285,112	356	62	306
space background task latch	6,033	64	3	61
row cache objects	715,246	29	3	26
redo allocation	14,901	15	6	9

Latch Hit%:= (1 – (Sum(misses) / Sum(gets)))

注意对于一个并发设计良好的OLTP应用来说，Latch、Enqueue等并发控制不应当成为系统的主要瓶颈，同时对于这些并发争用而言堆积硬件CPU和内存很难有效改善性能。

2.3.9 Parse CPU To Parse Elapsd

Parse CPU To Parse Elapsd:该指标反映了快照内解析CPU时间和总的解析时间的比值(Parse CPU Time/ Parse Elapsed Time)；若该指标水平很低，那么说明在整个解析过程中实际在CPU上运算的时间是很短的，而主要的解析时间都耗费在各种其他非空闲的等待事件上了(如latch:shared pool,row cache lock之类等) 数据来源 V$sysstat 的 parse time cpu和parse time elapsed

2.3.10 %Non-Parse CPU

%Non-Parse CPU 非解析cpu比例，公式为 (DB CPU – Parse CPU)/DB CPU，若大多数CPU都用在解析上了，则可能好钢没用在刃上了。数据来源 v$sysstat 的 parse time cpu和 cpu used by this session

2.4 Shared Pool Statistics

Shared Pool Statistics

	Begin	End
Memory Usage %:	83.12	84.15
% SQL with executions>1:	83.98	89.90
% Memory for SQL w/exec>1:	81.41	84.90

该环节提供一个大致的SQL重用及shared pool内存使用的评估。应用是否共享SQL? 有多少内存是给只运行一次的SQL占掉的，对比共享SQL呢？

2.4.1 % SQL with executions>1

如果该环节中% SQL with executions>1的比例小于%90 ，考虑用下面的SQL去抓硬编码的非绑定变量SQL语句。

利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

----以下8i开始流行的捕获绑定变量语句，现在已经过时

SELECT substr(sql_text, 1, 80), count(1)

FROM v$sql

GROUP BY substr(sql_text, 1, 80)

HAVING count(1) > 10

ORDER BY 2;

10g以后v$SQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，其官方定义为”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。

[shall@ bin]$ cat find_literal.sql

set serveroutput on

set linesize 120

--

-- This anonymous PL/SQL block must be executed as INTERNAL or SYS

-- Execute from : SQL*PLUS

-- CAUTION:

-- This sample program has been tested on Oracle Server - Enterprise Edition

-- However, there is no guarantee of effectiveness because of the possibility

-- of error in transmitting or implementing it. It is meant to be used as a

-- template, and it may require modification.

--

declare

b_myadr VARCHAR2(20);

b_myadr1 VARCHAR2(20);

qstring VARCHAR2(100);

b_anybind NUMBER;

cursor my_statement is

select address from v$sql

group by address;

cursor getsqlcode is

select substr(sql_text,1,60)

from v$sql

where address = b_myadr;

cursor kglcur is

select kglhdadr from x$kglcursor

where kglhdpar = b_myadr

and kglhdpar != kglhdadr

and kglobt09 = 0;

cursor isthisliteral is

select kkscbndt

from x$kksbv

where kglhdadr = b_myadr1;

begin

dbms_output.enable(10000000);

open my_statement;

loop

Fetch my_statement into b_myadr;

open kglcur;

fetch kglcur into b_myadr1;

if kglcur%FOUND Then

open isthisliteral;

fetch isthisliteral into b_anybind;

if isthisliteral%NOTFOUND Then

open getsqlcode;

fetch getsqlcode into qstring;

dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);

close getsqlcode;

end if;

close isthisliteral;

end if;

close kglcur;

Exit When my_statement%NOTFOUND;

End loop;

close my_statement;

end;

/

SQL> @find_literal

Literal:select shared_pool_size_for_estimate s, shared_pool address: 00000000810F0210

Literal:UPDATE mgmt_db_featureusage SET AUX_COUNT= :1 , COLLECTION_T address: 0000000070FDF3E0

Literal:SELECT CONTEXT_TYPE_ID,CONTEXT_TYPE,TRACE_LEVEL,NULL,NULL FR address: 0000000081114808

Literal:/* OracleOEM */ select r.apply_name apply_name, r.total_r address: 000000006F332520

Literal:SELECT NVL(SUM(FAILURES), 0) FROM SYS.DBA_QUEUE_SCHEDULES address: 0000000070DF5908

Literal:select max(FA#) from SYS_FBA_FA address: 0000000070DEEF60

Literal:select propagation_name, 'BUFFERED', num_msgs ready, 0 from address: 0000000070CAD4A0

Literal:DELETE FROM MGMT_JOB_STEP_COMMAND_LOG WHERE STEP_ID=:B1 address: 0000000070E833A0

Literal:SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL address: 000000006F22EB68

2.4.2 Memory Usage %

Memory Usage %: (shared pool 的实时大小- shared pool free memory)/ shared pool 的实时大小，代表shared pool的空间使用率，虽然有使用率但没有标明碎片程度

2.4.3 % Memory for SQL w/exec>1

% SQL with executions>1 复用的SQL占总的SQL语句的比率,数据来源 DBA_HIST_SQL_SUMMARY 的 SINGLE_USE_SQL和TOTAL_SQL：1 – SINGLE_USE_SQL / TOTAL_SQL

% Memory for SQL w/exec>1 执行2次以上的SQL所占内存占总的SQL内存的比率，数据来源DBA_HIST_SQL_SUMMARY 的SINGLE_USE_SQL_MEM和TOTAL_SQL_MEM：1 – SINGLE_USE_SQL_MEM / TOTAL_SQL_MEM

==》上面2个指标也可以用来大致了解shared pool中的内存碎片程序，因为SINGLE_USE_SQL 单次执行的SQL多的话，那么显然可能有较多的共享池内存碎片

SQL复用率低的原因一般来说就是硬绑定变量(hard Coding)未合理使用绑定变量(bind variable)，对于这种现象短期无法修改代表使用绑定变量的可以ALTER SYSTEM SET CURSOR_SHARING=FORCE; 来绕过问题，对于长期来看还是要修改代码绑定变量。

Oracle 从11g开始宣称今后将废弃CURSOR_SHARING的SIMILAR选项，同时SIMILAR选项本身也造成了很多问题，所以一律不推荐用CURSOR_SHARING=SIMILAR。

如果memory usage%比率一直很高，则可以关注下后面sga breakdown中的shared pool free memory大小，一般推荐至少让free memroy有个300~500MB 以避免隐患。

2.5 Top5 Timed Events

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

gc buffer busy 79,083 73,024 923 65.4 Cluster

enq: TX - row lock contention 35,068 17,123 488 15.3 Applicatio

CPU time 12,205 10.9

gc current request 2,714 3,315 1221 3.0 Cluster

gc cr multi block request 83,666 1,008 12 0.9 Cluster

基于Wait Interface的调优是目前的主流！每个指标都重要！

基于命中比例的调优，好比是统计局的报告，张财主家财产100万，李木匠家财产1万，平均财产50.5万。

基于等待事件的调优，好比马路上100辆汽车的行驶记录表，上车用了几分钟，红灯等了几分钟，拥堵塞了几分钟。。。

丰富的等待事件以足够的细节来描绘系统运行的性能瓶颈，这是Mysql梦寐以求的东西……

Waits : 该等待事件发生的次数，对于DB CPU此项不可用

Times : 该等待事件消耗的总计时间，单位为秒，对于DB CPU 而言是前台进程所消耗CPU时间片的总和，但不包括Wait on CPU QUEUE

Avg Wait(ms) : 该等待事件平均等待的时间，实际就是 Times/Waits，单位ms，对于DB CPU此项不可用

% Total Call Time，该等待事件占总的call time的比率

total call time = total CPU time + total wait time for non-idle events

% Total Call Time = time for each timed event / total call time

Wait Class: 等待类型：

Concurrency,System I/O,User I/O,Administrative,Other, Configuration,Scheduler, Cluster, Application, Idle,Network,Commit

CPU 上在干什么？

逻辑读？解析？Latch spin? PL/SQL、函数运算?

DB CPU/CPU time是Top 1 是好事情吗？未必！

注意DB CPU不包含 wait on cpu queue！

SELECT e.event_name event,

e.total_waits - NVL (b.total_waits, 0) waits,

DECODE (

e.total_waits - NVL (b.total_waits, 0),

0, TO_NUMBER (NULL),

DECODE (

e.total_timeouts - NVL (b.total_timeouts, 0),

0, TO_NUMBER (NULL),

100

* (e.total_timeouts - NVL (b.total_timeouts, 0))

/ (e.total_waits - NVL (b.total_waits, 0))))

pctto,

(e.time_waited_micro - NVL (b.time_waited_micro, 0)) / 1000000 time,

DECODE (

(e.total_waits - NVL (b.total_waits, 0)),

0, TO_NUMBER (NULL),

( (e.time_waited_micro - NVL (b.time_waited_micro, 0)) / 1000)

/ (e.total_waits - NVL (b.total_waits, 0)))

avgwt,

DECODE (e.wait_class, 'Idle', 99, 0) idle

FROM dba_hist_system_event b, dba_hist_system_event e

WHERE b.snap_id(+) = &bid

AND e.snap_id = &eid

--AND b.dbid(+) = :dbid

--AND e.dbid = :dbid

AND b.instance_number(+) = 1

AND e.instance_number = 1

AND b.event_id(+) = e.event_id

AND e.total_waits > NVL (b.total_waits, 0)

AND e.event_name NOT IN

('smon timer',

'pmon timer',

'dispatcher timer',

'dispatcher listen timer',

'rdbms ipc message')

ORDER BY idle,

time DESC,

waits DESC,

event

几种常见的等待事件

=========================>

2.5.1 db file scattered read

db file scattered read (文件分散读), Avg wait time应当小于20ms 如果数据库执行全表扫描或者是全索引扫描会执行 Multi block I/O ，此时等待物理I/O 结束会出现此等待事件。一般会从应用程序（SQL），I/O 方面入手调整; 注意和《Instance Activity Stats》中的index fast full scans (full) 以及 table scans (long tables)集合起来一起看。

2.5.2 db file sequential read

db file sequential read (文件顺序读)，该等待事件Avg wait time平均单次等待时间应当小于20ms ”db file sequential read”单块读等待是一种最为常见的物理IO等待事件，这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space)，而不是指所读取的数据块是连续的。该wait event可能在以下情景中发生:

1> 最为常见的是执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN,此时出现”db file sequential read”等待是预料之中的，一般不需要我们去特别关注

2> 当执行计划包含了 INDEX RANGE SCAN - (“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”)，服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table，每次物理读取都会进入”db file sequential read”等待，且每次读取的都是一个数据块；这种情况下clustering_factor将发挥其作用，需要我们特别去关注，本例中提及的解决方法对这种情景也有效

3> Extent boundary(边界),假设一个Extent区间中有33个数据块，而一次”db file scattered read”多块读所读取的块数为8，那么在读取这个区间时经过4次多块读取后，还剩下一个数据块，但是请记住多块读scattered read是不能跨越一个区间的(span an extent)，此时就会单块读取并出现”db file sequential read”。这是一种正常现象，一般不需要额外关注

4> 假设某个区间内有8个数据块，它们可以是块a,b,c,d,e,f,g,h，恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了，而这时候恰好要访问这个区间中的数据，那么此时就会单块读取d这个数据块，并出现”db file sequential read”等待。注意这种情况不仅于表，也可能发生在索引上。这是一种正常现象，一般不需要额外关注

5> chained/migrated rows即链式或迁移行，这里我们不介绍链式行的形成原因，chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取，从而出现”db file sequential read”。这种现象需要我们特别去关注，因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表，在出现大量链式行后，全表扫描需要数个小时)，同时也会对其他操作造成不那么明显的性能影响。可以通过监控v$sysstat视图中的”table fetch continued row”操作统计来了解系统中链式/迁移行访问的情况，还可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况，当然这要求定期收集表上的统计信息；如果没有定期收集的习惯，那么可以配合@?/rdbms/admin/utlchain脚本和analyze table list chained rows 命令来获取必要的链式行信息

6> 创建Index entry，显然当对表上执行INSERT操作插入数据时，虽然在执行计划中你看不到过多的细节，但实际上我们需要利用索引来快速验证表上的某些约束是否合理，还需要在索引的叶子块中插入相关的记录，此时也可能出现”db file sequential read”等待事件，当然这还和具体的插入的方式有关系。这是一种正常现象，一般不需要额外关注

7> 针对表上的UPDATE / DELETE ，不同于之前提到的 ” INDEX RANGE SCAN-UPDATE/DELETE”，如果我们使用rowid去更新或删除数据时，服务进程会先访问rowid指向的表块(注意是先访问table block)上的行数据，之后会根据该行上的具体数据去访问索引叶子块(注意Oracle并不知道这些leaf block在哪里，所以这里同样要如range-scan/unique-scan那样去访问index branch block)，这些访问都将会是单块读取，并会出现’db file sequential read’，完成必要的读取后才会执行更新或删除的实际EXEC操作

2.5.3 latch free

latch free　　其实是未获得latch ，而进入latch sleep，先了解下《全面解析9i以后Oracle Latch闩锁原理》

Latches 是一种低级别(low-level)的锁机制，初学IT的同学请注意低级不代表简单， C语言对比java语言要低级一些但C并不比java简单。

在一些文章著作中也将latch称为spin lock 自旋锁。 latch用来保护共享内存(SGA)中的数据以及关键的代码区域。一般我们说有2种latch：

1)Test and Set 简称TAS :

TAS是计算机科学中的专指， test-and-set instruction 指令用以在一个原子操作(atomic 例如非中断操作)中写入到一个内存位置，并返回其旧的值。常见的是值1被写入到该内存位置。如果多个进程访问同一内存位置，若有一个进程先开始了test-and-set操作，则其他进程直到第一个进程结束TAS才可以开始另一个TAS。

关于TAS指令更多信息可以参考wiki ，包括TAS的伪代码例子: http://t.cn/zQgATRr

在Oracle中Test-And-Set类型的latch使用原生的Test-And-Set指令。在绝大多数平台上，零值zero代表latch是空闲或者可用的 , 而一个非零值代表 latch 正忙或者被持有。但是仅在HP PA-RISC上正相反。 TAS latch只有2种状态 : 空闲或者忙。

2) Compare-And-Swap 简称 CAS

Compare-And-Swap 也是计算机专有名词， Compare-And-Swap(CAS)是一个用在多线程环境中实现同步的原子指令( atomic )。该指令将在一个给定值(given value)和指定内存位置的内容之间比对，仅在一致的情况下修改该内存位置的内容为一个给定的新值(不是前面那个值)。这些行为都包含在一个单独的原子操作中。原子性保证了该新的值是基于最新的信息计算获得的；如果该内存位置的内容在同时被其他线程修改过，则本次写入失败。该操作的结果必须说明其到底是否执行了取代动作。它要么返回一个布尔类型的反馈，要么返回从指定内存地址读取到的值(而不是要写入的值)。

关于CAS的更多信息可以参考 http://t.cn/hcEqh

Oracle中的 Compare-And-Swap Latch也使用原生态的Compare-And-Swap指令。和TAS Latch类似，空值代表latch是free的，而一个非空值代表latch正忙。但是一个CAS latch 可以有多种状态：空闲的、以共享模式被持有、以排他模式被持有。 CAS latch可以在同一时间被多个进程或线程以共享模式持有，但还是仅有一个进程能以排他模式持有CAS latch。典型的情况下，共享持有CAS latch的进程以只读模式访问相关数据，而一个排他的持有者目的显然是要写入/修改对应CAS latch保护的数据。

举例来说， CAS latch的共享持有者是为了扫描一个链表 linked list ，而相反排他的持有者是为了修改这个列表。共享持有者的总数上线是0x0fffffff即10进制的 268435455。

注意几乎所有平台均支持CAS latch，仅仅只有HP的PA-RISC平台不支持(惠普真奇葩)。在PA-RISC上CAS latch实际是采用TAS latch。所以虽然在HP PA-RISC上代码仍会尝试以共享模式获得一个latch，但是抱歉最终会以排他模式获得这个latch。

一般一个latch会包含以下信息：

1> Latch type 类型， latch type定义了是TAS 还是CAS latch， latch class和 latch number

2> Latch的 level 级别

3> 持有该latch的代码位置where ,例如使用kslgetl函数获得某个latch，则持有文职为kslgetl

4> 持有该latch的原因

5> nowait模式下获得该latch的次数 V$LATCH.IMMEDIATE_GETS

6> wait模式下第一个尝试失败的次数 V$LATCH .MISSES

7> nowait模式下尝试失败的次数 V$LATCH.IMMEDIATE_MISSES

8> 获取latch失败造成sleep的总时间 X$KSLLTR.KSLLTWSL, V$LATCH.SLEEPS

9> 首次spin成功获得latch的次数 X$KSLLTR.KSLLTHST0, V$LATCH.SPIN_GETS

10> latch wait list等等

子闩 child latch

当一个单一的latch要保护过多的资源时会造成许多争用，在此种场景中 child latch变得很有用。为了使用child latch，需要分割原latch保护的资源为多个分区，最常见的例子是放入到多个hash buckets里，并将不同子集的资源分配给一个child latch。比起每一个hash bucket都去实现一个单独的latch来说，编程上使用child latch要方便的多，虽然这不是我们用户所需要考虑的。为一个latch 定义多个child latch，则这个latch称为parent latch父闩。 child latch 可以继承 parent latch的一些属性，这些属性包括级别和清理程序。换句话说， child latch就像是parent 父闩的拷贝一样。

经典情况下，在SGA 初始化过程中child latch将被分配和初始化(startup nomount)。但在目前版本中(10/11g)中也允许在实例启动后创建和删除latch。

child latch又可以分成2种：

允许一个进程/线程在同一时刻持有2个兄弟child latch

不允许一个进程/线程在同一时刻持有2个兄弟child latch

因为child latch从parent latch那里继承了属性，所以注意 child latch的 latch level和 parent 父闩是一样的。因为一个进程/线程不能在同一时间持有2个latch level一样的闩，所以正常情况下一个进程/线程也不能同一时间持有2个兄弟child latch。

回到我们说的hash bucket的例子里来，假设一个进程/线程有将一个resource从一个hash bucket 移动到另一个hash bucket的需求，在此场景中就需要同时持有2个兄弟child latch。但是如果允许这种同时持有2个兄弟child latch的行为发生的话，那么很容易造成死锁deadlock的麻烦。 oracle 不允许进程/线程任意地同时获得2个兄弟child latch，由于此种操作很容易引起死锁。由此引入了一些规则：兄弟child latch必须是相关的child number ,且进程/线程只能以特性的顺序来同时get 2个兄弟child latch，即child number 从大到小低贱的顺序。

此外需要注意的是仅有TAS latch可以同时get多个兄弟child latch，目前还不支持 CAS的latch。

Latch 清理恢复

Oracle中定义了一个latch，就需要这个latch对应的清理函数cleanup function，这个函数在以下2个场景中生效：

当某个latch被持有，但是持有进程遇到了某一个错误 ==》主动

当持有latch的进程die掉，需要PMON进程前去恢复这个latch的状态 ==》被动

经典情况下，执行清理函数的进程要么把正在执行过程中的操作回滚掉，要么前滚掉。为了为前滚(rolling forward)或者回滚(rolling back)提供必要的信息， oracle在latch结构中加入了recovery的结构，其中包含了这个正在执行过程中的操作的日志信息，这些日志信息必须包含足以前滚或者回滚的数据。如我们以前讲过的，理论上oracle进程可能在运行任何指令的时候意外终止，所以清理恢复是常事。

清理恢复最恶心的bug是PMON 执行cleanup function时因为代码bug ，把PMON自己给弄dead了，由于PMON的关键的后台进程，所以这会引起实例终止。

Latch和 10.2.0.2后引入的KGX Mutex对比

和 latch一样， kgx mutex也是用来控制串行化访问SGA中数据的，但仍有一些重要区别：

1> KGX mutex要比 CAS latch更轻量级， mutex 结构大约为16个字节，而一个latch结构大约是100个字节。因此 mutex嵌入到大量其他对象结构中是可行的，因为他的struct 足够小

2> 之所以mutex可以提供更小的结构很廉价的成本，其原因是使用mutex有一个简单的前提假设：对于mutex的争用是很小的。因此没有为mutex那样提供一个优化过的wait list ， mutex做更多的 SPIN & WAIT 并消耗更多的CPU。此外mutex也没有提供任何死锁检测和预防机制，这些都完全取决于Kgx mutex的用户自身的行为。

3> Latch在内部视图(例如X$KSLLT)中提供全面的诊断信息。 KGX mutex在(x$mutex_sleep、x$mutex_sleep_history等内部视图)中提供部分信息，同时也允许其用户在回调程序中用特定信息填充这些视图。

4> 除了共享和排他模式之外， KGX mutex还提供一种examine 模式，允许其在不以共享或排他模式持有mutex的情况下client检查一个mutex的状态以及其用户数据。这种模式是latch所没有的

Latch 和Enqueue lock队列锁对比，以下是latch和enqueue的几个重大区别：

1> 在典型情况下，latch被认为将仅仅被持有很短的一段时间(ms级别)，而enqueue 将被持有比之长得多的多的时间(秒=》分钟=》小时)。例如TX 队列事务锁在整个事务的生命周期中被持有。 latch被设计出来就是为了在函数运行到某几十个乃至上百个指令过程中被持有，这是很短暂的过程

2> latch是为了避免同一时间有一个以上的进程运行相似的代码片段，而enqueue是为了避免同一时间多于一个的进程访问相同的资源

3> latch的使用较为简单，而enqueue的使用则由于命名空间namespace和死锁检测的问题而较为麻烦

4> latch只有2个模式共享和排他，而enqueue 则支持6个模式

5> RAC中 latch 总是本地存放在当前实例的SGA中，而enqueue可以是Local的也可能是Global的

6> 9i以前latch不是FIFO的，是抢占式的；从9i开始大多数latch也是FIFO了； enqueue始终是FIFO的

有同学仍不理解 latch和enqueue的区别的话，可以这样想一下， latch 保护的SGA中的数据对用户来说几乎都是不可见的，例如 cache buffer的hash bucket 对不研究内部原理的用户来说等于不存在这个玩样，这些东西都是比较简单的数据结构struct ，如果你是开发oracle的人你会用几百个字节的enqueue 来保护几个字节的一个变量吗？

而队列锁 TX是针对事务的， TM是针对表的，US是针对 undo segment的，这些东西在实例里已经属于比较高级的对象了，也是用户常可见的对象，维护这些对象需要考虑死锁检测、并发多模式访问、RAC全局锁等等问题，所以需要用更复杂的enqueue lock。

死锁dead lock

为了使得latch使用足够轻量级，死锁预防机制十分简单。由此Oracle开发人员在构建一个latch时会定义一个数字级别 level (从 0 到 16 )，并且Oracle要求它们必须以level增序顺序获取。若一个进程/线程在持有一个 latch的情况下，要求一个相同或者更低level的latch的话，KSL层会生成一个内部错误，这种问题称为 “latch hierarchy violation”。

Latch Level 级别

Oracle在定义latch level的时候取决于以下2个原则：

1> 那些latch是在被持有的情况下，进程/线程还会去get其他的latch?

2> 当已经有latch被进程/线程持有的情况下，那些latch还会被 get?

如上文dead lock的描述， latch level的一大作用是帮助减少latch dead lock。

sys@ORCL>select distinct level# from v$latch order by 1;

LEVEL#

----------

0

1

2

3

4

5

6

7

8

9

10

11

14

15

16

15 rows selected.

仅有以nowait模式get latch时可以以级别(level) 非兼容的级别获得一个latch，但是这种情况非常少。

Latch Class

latch的类class定义了如下的内容：

1> spin count 自旋计数

2> yield count (number of times we yield cpu before sleeping) 产量计数

3> wait time sample rate (0 implies it is not enabled) 等待时间采集率

4> sleep (in microseconds and repeated [see below]) 睡眠

对于post/wait 类而言 SLEEP_BUCKET和SLEEP_TIME 是被忽略的。

以下是几个latch class:

Class 0 Post/Wait Class ，绝大多数latch都是该类型

Class 1 Waiter List Latch。该Latch保护对应latch的Waiter List，这种latch被假定总是只被持有非常短的时间(指令级别)，有充分的理由花费更多的spin count 消耗更多的CPU ，并尽可能减少sleep时间

Class 2 那些由于多种原因，不能使用post/wait机制的latch 。例如process allocation latch 这个闩是在一个新进程创建时所需要获取的，但是新进程还没加载post/wait的上下文，显然无法用post/wait ，所以这种 latch不能用post /wait机制

Class 3 非常短持有的latch，特性与class 1类似。

CLASS_KSLLT字段代表了latch的类型

sys@ORCL> select CLASS_KSLLT,count(*) from x$kslltr group by CLASS_KSLLT;

CLASS_KSLLT COUNT(*)

----------- ----------

2 1

0 581

sys@ORCL>select KSLLTNAM,CLASS_KSLLT from x$kslltr where CLASS_KSLLT=2;

KSLLTNAM CLASS_KSLLT

---------------------------------------------------------------- -----------

process allocation 2

从9.0.2 开始每个latch class的SPIN COUNT、YIELD COUNT 、WAITTIME_SAMPLING 、 SLEEP_TIME[1] …. SLEEP_TIME[i] 均在参数_latch_class_X中定义。

col name for a20

col avalue for a20

col sdesc for a20

SELECT x.ksppinm NAME,y.ksppstvl avalue,x.KSPPDESC sdesc

FROM SYS.x$ksppi x, SYS.x$ksppcv y

WHERE x.inst_id = USERENV ('Instance')

AND y.inst_id = USERENV ('Instance')

AND x.indx = y.indx

AND x.ksppinm like '%latch%class%';

NAME AVALUE SDESC

-------------------- -------------------- --------------------

_latch_class_0 latch class 0

_latch_class_1 latch class 1

_latch_class_2 latch class 2

_latch_class_3 latch class 3

_latch_class_4 latch class 4

_latch_class_5 latch class 5

_latch_class_6 latch class 6

_latch_class_7 latch class 7

_latch_classes latch classes override

9 rows selected.

sys@ORCL> select INDX,SPIN,YIELD,WAITTIME,SLEEP0 from X$KSLLCLASS;

INDX SPIN YIELD WAITTIME SLEEP0

---------- ---------- ---------- ---------- ----------

0 20000 0 1 8000

1 20000 0 1 1000

2 20000 0 1 8000

3 20000 0 1 1000

4 20000 0 1 8000

5 20000 0 1 8000

6 20000 0 1 8000

7 20000 0 1 8000

8 rows selected.

举例来说 _latch_class_1=”5000 2 0 1000 2000 4000 8000″ 则

SPIN_COUNT=5000

YIELD_COUNT=2

wait time sampling: 0 (不收集，一般都是1即收集)

增序的sleep time 1000 => 2000 => 4000 => 8000，单位是microseconds，超过4次则保持在8000

每一个wait class 适应自己对应_latch_class_X中的SPIN_COUNT、YIELD_COUNT等参数。而实例参数_SPIN_COUNT只做为向后兼容，若对应的latch Class没有自己的SPIN_COUNT属性才会生效。

由此实际生效的SPIN_COUNT由由以下几个参数按优先级从高到低生效：

首先是设置过的_latch_class_X 中的SPIN_COUNT

设置够的_SPIN_COUNT

内部函数

注意除非是oracle support建议你去修改这些latch参数，否则在任何系统中都不该去尝试修改它们，如果你确实遇到了latch free的问题，那么你应当首先做 SQL 调优和并发调整。

SPIN

还记得电影《inception》盗梦空间里中旋转的陀螺吗，旋转的陀螺在英文里就是spinning top。 spin 自旋是 latch话题中一个频率很高的词，但是一直以来我们对自旋的理解都不够彻底，下面我们彻底解释 9i以后的自旋SPIN 和 Busy Latch原理。

SPIN 是指当进程首先尝试获取latch失败后( 一般是别人持有了该latch)，有2种选择要么是退让CPU(yield CPU) 休眠一段时间后再重新尝试获取latch ，要么是本进程抱着希望在CPU 上空转，因为如果我不用CPU了让给别人用了就会造成context switch上下文切换 (vmstat 里看到的CS)，而我在CPU上空转的话就可以等等看这个latch是否会在这段时间里被人家释放，我的一次空转称为SPIN 一次，而SPIN_COUNT定义了我在这次总的SPIN 操作里总共SPIN 空转多少次，例如SPIN_COUNT=2000(注意见上文中对SPIN_COUNT的描述)就是说我有机会空转 2000次，空转一次后我跑去查看一下latch是否被别人释放了，如果没有我继续下一次空转，如果是释放了那么我就获得这个latch了，也就是SPIN_GETS成功了。如果SPIN 2000次了还是没有等到释放latch，则SPIN_GETS没有成功，之后该SLEEP就SLEEP( 9i前后从9i开始有区别，具体见下文)。

如果上述SPIN GET的成功获得了latch，那么因为我没有退让CPU 也就没有上下文切换，所以显然我获得latch的速度要比直接sleep并重试来的快。

另假设我提高了某个latch对应的 spin_count ，例如修改latch_class_1中的SPIN_COUNT为更高的值，则在上述情况下可能SPIN循环的次数更多，也就意味着有更高的概率在 SPIN阶段获得 latch，而代价是SPIN消耗更高的 CPU时间片。相反若降低SPIN_COUNT，则意味着SPIN阶段获得latch的概率降低， SPIN消耗相对少的CPU。

在中古的硬件中可能有仅有1个CPU的系统，虽然现在很少见了，但是显然在仅有一个CPU的情况下SPIN是无意义的，因为如果你把唯一的一个CPU用来SPIN了，显然真正持有对应latch的那个进程获取不到CPU，获取不到CPU的结果是它无法释放这个latch。在这种环境里代码自动把spin_count调整为1。

SPIN 与Latch Busy

从9.0.2开始oracle 开始大量启用 post/wait和latch class机制，我们来描述一下伪代码

SLEEPs //睡眠次数计数

yields //yield 计数 copyright askmaclean.com

on_wait_list = FALSE;

while (如果未获得latch)

{

在对应的latch上SPIN ，循环次数为SPIN_COUNT，SPIN_COUNT 来源一般为 _latch_class_X

if (获得latch)

break;

if(yields < YIELD_COUNT) // YIELD_COUNT来源为latch_class_X

{

yields++;

yield CPU ;

}

else

{

yield =0;

if (如果latch是post/wait机制的)

{

on_wait_list=TRUE ;

get wait list latch ; //获得wait list latch

add current process to wait list; //将被进程加入到wait list的尾部

free wait list latch; //释放wait list latch

wait to be posted; //等待被post

}

else

{

wait for SLEEP_TIMES[sleeps] microseconds; //等待SLEEP_TIME[sleeps]对应的时间来源为latch_class_X

if ( sleeps < SLEEP_BUCKETS) //SLEEP_BUCKETS 一般为4

sleeps++;

}

//如果某一刻获得了一个post/wait latch，且本进程在wait list上，则需要从wait list 上把自己移走：

if( 如果我在wait list上)

{

get wait list latch ; //获得wait list latch

remove current process from wait list; //从wait list 上把自己移走

free wait list latch; //释放wait list latch

}

从9i开始绝大多数 latch都是 post/wait class的，出去少量非post/wait class的latch和PMON进程外，进程都会进入非时控的sleep (sleep[1]..[sleep[i] i到4以后不再增加) 不会自己醒来，仅在该latch的持有者释放该latch 且等待的进程在wait list的头部的情况下被post唤醒 (awake)。 Oracle 选择这种非时控的sleep的原因为了避免在miss后引起反复的上下文交换context switch 以便改善性能。

但是这种实现也存在一种风险，即需要应对那些持有latch进程意外终止和存在丢失 post的bug的情况。

2.5.4 enq:XX

enq:XX 队列锁等待，视乎不同的队列锁有不同的情况：

§ 你有多了解Oracle Enqueue lock队列锁机制？

§ Oracle队列锁: Enqueue HW

§ Oracle队列锁enq:US,Undo Segment

§ enq: TX – row lock/index contention、allocate ITL等待事件

§ enq: TT – contention等待事件

§ Oracle队列锁enq:TS,Temporary Segment (also TableSpace)

§ enq: JI – contention等待事件

§ enq: US – contention等待事件

§ enq: TM – contention等待事件

§ enq: RO fast object reuse等待事件

§ enq: HW – contention等待事件

1> 你有多了解Oracle Enqueue lock队列锁机制？

概念

Enqueue 队列锁是 oracle中主要的并发访问控制和锁的机制。我们耳熟能详的 TM table lock表锁和 TX 事务锁都是 enqueue 。 enqueue 最多可以有6个模式， enqueue lock被设计出来用于那些需要较长时间锁机制的场景。

enqueue 的 6个锁模式分别为： X 、 SSX、 S、SX、SS、NULL。

以下是当一个进程以某种模式持有某个enqueue ，而另一个进程尝试以某种模式request 这个enqueue的兼容矩阵：

	请求模式
持有模式		NULL	SS	SX	S	SSX	X
	NULL	成功	成功	成功	成功	成功	成功
	SS	成功	成功	成功	成功	成功	失败
	SX	成功	成功	成功	失败	失败	失败
	S	成功	成功	失败	成功	失败	失败
	SSX	成功	成功	失败	失败	失败	失败
	X	成功	失败	失败	失败	失败	失败

再次比较 Enqueue 和Latch

latch是为了串行访问机制，而enqueue 更多是为了并发访问控制

从设计出发 enqueue 可以被持有较长的一段时间； latch应当在极短的时间内(us级别)持有和释放，否则 latch将成为严重的性能瓶颈，乃至HANG

enqueue 的request 和release 要比latch 使用更多的CPU

enqueue 要比latch在结构上复杂地多

enqueue 有6种模式

enqueue 可能是LOCAL的可能是cluster-wide global的

latch只对应一个process state object；而enqueue 对应一个session state object和一个process state object

每一个 Enqueue Resource 都以2个字母代表例如 TX和TM ，并带有4个数字例如 TM-00017927-00000000-00000000-00000000 称作 resource id 的4个部分，一般我们只用到前2个ID 。

在内部一个Enqueue Resource 对应一个 KSQRS 结构，该KSQRS 结构拥有三个列表：

owner list

waiter list

converter list

简单描绘一个enqueue resource的结构：

<TM resource> [owner list的头部] [waiter list的头部] [converter list的头部] [resource ID : ID1 ID2 ID3 ID4] [enqueue type]

| | |

S X S->X

| |

S SX

|

S

2> Oracle队列锁: Enqueue HW

HW真的是个著名的enqueue lock，著名度仅次于TM、TX吧。对于有高并发INSERT的OLTP数据库的DBA而言，HW enqueue真实家常便饭的等待事件。但是对于该等待事件的详细说明却少之又少。这里我们总结一下这个HW enqueue lock。

这里我们仅讨论high water mark高水位队列锁的相关信息以及其常见使用和争用场景。

虽然造成HW enqueue contention争用的根本原因多种多样，但本质上HW enqueue总是只在数据段segment的High Water Mark高水位线需要移动时才被持有，最常见的情形莫过于一顿并发猛烈地INSERT操作。

HW enqueue根本存在目的是为了串行化对segment高水位线的移动以及回收lob segment中的空间。已删除的LOB CHUNK在LOB INDEX中得到维护，以达到一致性读的目的；当LOB SEGMENT中的free space即将耗尽时则会则会从LOB INDEX中回收已提交的空闲空间。在持有HW enqueue的情况下Oracle操作从LOB INDEX移动free space到LOB本身SEGMENT的操作。

对于HW enq往往能采取以下的措施

1. 在segment扩展时，_bump_highwater_mark_count隐藏参数指出了单次高水位上涨的块数，但是该隐藏参数将影响整个DB。

2. 通过ATLER TABLE allocate extent (instance X)为指定实例、指定表预分配extent是另一种备选的解决HW enqueue的方案，而且该方案较为安全

3. 也建议检查表上的freelist 、fresslist group设置，若设置得过高，则可能由于格式化所有移动到freelist中的数据块而导致segment hw持有很长一段时间

4. 若在lob space回收期间发生该HW enq问题，则考虑检查LOB的pctversion配置。基于实际使用情况你可以设置pctversion为0或更小之避免空间回收，默认值为10%

不管是LMT本地管理还是DMT字典管理表空间总是要求在扩展数据段segment时持有HW enqueue。虽然在LMT上这种持有周期更短，但这仍是一个串行化的扩展过程。

如何避免争用：

以下是2中避免HW enq争用的行之有效方案：

1. 重建该对象并配以freelist group、并为每个实例预分配EXTENT，语法为 ALTER TABLE..allocate extent(INSTANCE X)。这种措施有效改善性能，但也可能造成某个实例释放的数据块不能为其他实例所用的确定。一般来说若实例间的删除DELETES和UPDATES更新是平衡的，则实际不构成问题。

手动分配extent：

alter table t allocate extent (size 1m);

alter table t allocate extent;

alter table t allocate extent(instance 1); ---- instance option一般是用于RAC中将extent加到相应的instance的freelist中，我们在单机环境下也可使用。

2. 若这种预分配仍不够显效，则考虑增加free list的数目。高水位线移动的速度很大取决于free list数目。

Enq HW的ID1、ID2

id1为扩展数据段(具体指高水位要调整的数据段)所在的表空间号tablespace number， ID2为segment header段头的RDBA 相对数据块地址。

3> Oracle队列锁enq:US,Undo Segment

一般情况下稳定系统中突然出现的Enq:US 是因为并发事务数突然变多导致的，而实际引发并发事务数变多的可能是某些DML语句遇到了性能问题，导致事务处理速度变慢，或者是commit变慢所致。

针对上述问题，enq:US本身只是性能问题所导致的“果”，而不是原因，一般解决了上述问题，enq:US的这个果也就消失了。

对于那些短期内无法解决根本原因的环境，可以通过如下手段调优Undo Segment的管理来减少US争用。

ALTER SYSTEM SET “_rollback_segment_count”= 2000;

–--副作用是可能的数据库startup变慢

ALTER SYSTEM SET “_undo_autotune” = false;

–--最好重建undo tabelspace

ALTER SYSTEM SET “_highthreshold_undoretention”=3600;

--–1小时

有多少Undo enqueue resource?

每一个rollback segment对应一个 US enqueue 资源

有多少US enqueue lock？

当进程要求访问一个rollback segment RBS时对应一个US enqueue lock

谁使用该enqueue lock?

所有的前台进程，SMON和PMON

何时使用该US enqueue ?

US,Undo Segment enqueue的存在是为了对给定undo segment(rollback segment)的串行化DDL，该US enqueue 串行化以下操作：

– CREATE ROLLBACK SEGMENT

– DROP ROLLBACK SEGMENT

– ALTER ROLLBACK SEGMENT ONLINE

– ALTER ROLLBACK OFFLINE

– ALTER ROLLBACK SEGMENT SHRINK

– ALTER ROLLBACK SEGMENT STORAGE

– Offlining PENDING OFFLINE RBS by SMON

– SMON – abortive offline cleanup.

– STARTUP.

注意在UNDO_MANAGEMENT=AUTO的 AMU模式下当Undo Tablespace可用空间紧张时也可能引起剧烈的enq: US队列锁争用等待事件。

Id1, Id2 组合:

Undo Segment#, Always 0.

Lock Value Block:

No.

Init.ora Parameters:

gc_rollback_locks, rollback_segments.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operations:

Synchronous.

4> enq: TX – row lock/index contention、allocate ITL等待事件

sys@ORCL>select name from v$event_name where name like '%TX%';

NAME

--------------------------------------------------

enq: TX - row lock contention

enq: TX - allocate ITL entry

enq: TX - index contention

enq: TX - contention

enqueue TX事务锁 transaction enqueue，顾名思义这个队列锁用来保护事务信息。

当进程修改某块中的一行数据，则Oracle必须将该事务信息与被改变的这一行联系起来，做法是在块中的row piece(块)的lk上标记ITL位，而实际的ITL记录了这个事务相关的回滚段号USN，以便能够定位其撤销链。

这样做的目的有几个：

1. 允许用户手动rollback或者因为dead transaction的发生而后台(PMON or SMON 取决于_cleanup_rollback_entries)回滚该事务。

2. 在块中留下Undo线索，才能让查询者Queryer顺藤摸瓜去构造这个块在事务发生前的镜像块。

绝大多数情况下 ,TX enqueue的申请、获得模式总是排他的exclusive的即mode=6/request=6的(v$LOCK)，这通常意味着我们的DML操作所感兴趣的数据行正被其他事务锁定着，但这并不绝对。

有场景中我们会以共享模式Share mode=4/request=4(V$LOCK)去申请、获得TX enqueue锁；一种场景是进程仅仅先需要申请到数据块(data block包括table/index)上的 ITL(interested transaction lists)事务槽。以share mode等待(request=4)TX enqueue 的一种可能就是块上无法再扩展本进程所需要的一个ITL了，要么这个块上的ITL 超过MAXTRANS(255)ITL的上限值了，要么是这个块没有更多的空间来容纳更多一个ITL了，(一个ITL 24字节)。

若争用contention缘起于排他的exclusive TX队列申请，则表明有并发事务试图锁住同一行数据。大多数情况下这是由于应用引起的。

实际上log file sync(其实是写redo慢)也会造成buffer busy wait。

1. OLTP类型的小DML操作一般都会是immediate block cleanout的，这要求在commit之前对block做change kcbchg

2. 在commit kcrf_commit_force完成前都不会释放对该block buffer的buffer pin

3. 由上述2点造成的buffer pin最终会影响select和其他insert/update/delete 形成buffer busy wait

4. 由于慢的lgwr写redo log会造成 kcrf_commit_force commit的缓慢，表现在等待事件上就是log file sync

5. 由于block cleanout时pin block buffer且commit 慢，则会导致更长时间的buffer busy wait

6. 若log file sync是由lgwr 写redo log慢(log file parallel write)引起的，则它的另一个效应就是buffer busy wait增多

7. 若看到AWR中log file sync+buffer busy wait是主要等待事件，则优先解决log file sync ，因为buffer busy wait实际可能是受害者

若争用contention缘起于共享shared TX 队列申请，则有可能是一个或多个数据块无法扩展事务表所导致的。在这种情况下可以考虑增加更大的INITTRANS来解决问题：

对于该问题建议做10046 level 8+ buffer cache dump配合V$SESSION fileid/blockid/objno来诊断问题。

从版本9iR2开始可以通过动态性能视图的V$SEGMENT_STATISTICS来判断对象是否经历ITL 等待，但是实例参数 STATISTICS_LEVEL必须设置为typical 以上，可以参考statspack中segment by ITL的环节，当然AWR里也有类似的信息：

select owner,object_name from v$segment_statistics where statistic_name='ITL waits' and value>0;

TX锁的 ID1/ID2解释

ID1 是用来存放 USN==> Undo Segment Number以及相关的undo segment transaction slot，通过以下转换获得：

(undo_segment_number << 16) + slot

ID2是事务槽位序列号 transaction slot sequence number 或曰wrap number。

另外一些以共享模式share mode mode=4/request=4(V$LOCK)去申请、获得TX enqueue锁的场景：

1. 等待块上的ITL slot

2. 由于主键、唯一索引约束而等待

3. 由于位图索引bitmap index而等待

4. 因为2 phase commit，2段提交而等待

其他的一些官方调优手册中也提出了一些可能引起TX V$LOCK. lmode/request =4的shared lock contention的情况：

1. 当2个session试图向一个unique index插入同一个索引键值时，后插入的那个session如此等待，知道先插入的session commit/rollback，则后一个session对应的ORA-0001或插入成功。

2. 当一个session因为bitmap index fragment而等待。位图索引bitmap index是一些索引键值加上ROWID的范围。位图索引中的每一条记录可以映射为实际表上的多条记录。若2个session试图更新的行映射到同样的位图索引记录，则后者的session需要在前者commit/rollback之前一直等request=4的TX lock。

3. 当等待一个PREPARED transaction时也会出现mode 4的TX

4. 还有就是使用dbms_repair时也可能看到mode 4的TX

一些相关的BUG

Bug 3159414 documents another case where we might see a TX request in mode 4 when constraints are involved.
Bug 5849679 documents an undetected deadlock case with RAC.

Occurs when attempting to access a row that is locked in exclusive mode. more…

Solutions

1) Waits due to row locked by an active transaction

See if certain jobs can be run at other times; Investigate application code problems such as commits occurring later than necessary or unnecessary repetitive updates.

2) Waits due to Unique or Primary Key Constraint enforcement

A possible, but risky solution is to drop the constraint and possibly check constraints at a later point (perhaps via a batch job).

3) Waits due to Insufficient ‘ITL’ slots in the Block(INITRANS and MAXTRANS)

Increase the INITRANS and MAXTRANS (9i and below) settings

4) Waits due to rows being covered by the same BITMAP index fragment

Rebuild index

Compressed Indexes

Expanded Definition

A TX lock is acquired exclusive (X) when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. This ensures a read consistent image of the block before the transaction started. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete.

5> enq: TT – contention等待事件

TT 队列锁在官方文档中介绍为TT, Temporary Table，但是实际在版本8i之后该队列锁更多参与在表空间管理事务中。也可以称enqueue TT为tablespace lock。

作用

该enqueue TT队列锁用以在各种类型的表空间操作执行过程中避免出现死锁dead lock。该enqueue lock的 ID2表明正在执行的操作种类，ID1表明该操作对应的表空间号V$Tablespace.TS#。

ID1/ ID2的含义

id1是tablespace number V$Tablespace.TS#，ID2表明执行中的操作类型。以下是操作类型对应代码;

0- 用以避免在drop tablespace和创建rollback segment之间发生死锁

1- 用以串行化在给定表空间上创建数据文件

2- 用以避免在TSPITR tablespace point in time recovery期间发生其他类型操作

4- 用以在创建tablespace时锁住该tablespace id

8- 用以避免在ALTER TABLESPACE期间发生死锁

16- 即16进制的0x10，用以同步分配和回收盘区，allocation(分配) and deallocation(释放) of extents.

32+ 用在增加数据文件add datafile和创建表空间时，其中ID2为 32(十进制的)+相对文件号relative file number

最常见的TT enqueue争用是 TT-00000x-0000010 即allocation and deallocation of extents.

如果10g以后遇到类似问题，建议先了解一下extent management、segment management(ASSM、MSSM?)管理方式等，是否打开了回收站recyclebin功能，以及该表空间上回收站对象的extent数量。

enqueue TT相关的一些BUG列表如下：

Bug 8313598 – ORA-60 on TT enqueue from DBMS_SPACE.ISDATAFILEDROPPABLE_NAME [ID 8313598.8]

Bug 4732503 – Self-deadlock on TT enqueue [ID 4732503.8]

Bug 6809093 – Hang due to TT enqueue waits on an UNDO tablespace [ID 6809093.8]

Bug 14055559 System hang due to TT enqueue contention with BIGFILE tablespace resize

Bug 3833893 SMON may hold the TT enqueue preventing alter of TEMPORARY TABLESPACE

Sessions Hang Due to Self Deadlock on TT Enqueue [ID 948668.1]

Bug 3397983 – Sessions may hang waiting for TT enqueue [ID 3397983.8]

Bug 3467364 – Adding datafiles serializes on the TT enqueue [ID 3467364.8]

Bug 8332021 – Cannot add a datafiles when sessions reporting ORA-1653 / TT enqueue contention between datafile addition and other segment extension operations [ID 8332021.8]

Bug 2272671 – DEADLOCK possible on CREATE INDEX ONLINE (KGL lock v. TT enqueue) [ID 2272671.8]

Bug 6762619 : ENQUEUE “TT” REMAINS WHEN ACCESSING DBA_FREE_SPACE

Bug 6858962 : TT AND US ENQUEUE DEADLOCK

Bug 9439759 : TT ENQUEUE IS NOT RELEASED WHEN CANCEL QUERY

Bug 6265482 : TT ENQUEUE REMAINS AFTER CANCEL “SELECT * FROM DBA_EXTENTS”

Bug 7217723 : ORA-00060 DEADLOCK DETECTED DUE TO TT AND US ENQUEUE

Bug 4732503 : SELF-DEADLOCK TT ENQUEUE ON 9.2.0.7 SIMILAR TO 3425298

Bug 9403168 : WAIT FOR TT-ENQUEUE FOR INSERT TO BASCIFILES DURING ADDING DATAFILE TO SAME TBS

Bug 9451566 : DEADLOCK ON TT-ENQUEUE

Bug 3833893 : SMON GETS “TT ENQUEUE” OF TEMP IF DATEFILE OF TEMP DOESN’T EXIST.

Bug 9948775 : SELF-DEADLOCK ON TT ENQUEUE

The TT portion of the wait event name stands for Temporary Table and typically indicates issues with the temporary tablespace or related storage. Look for other process that are manipulating temporary tablespaces or tempfiles during the time when these waits were noticed.

Solutions

If an ALTER DATABASE TEMPFILE DROP hangs due to a TT enqueue that is retained by the SMON process in Oracle 9.2.0.6 or lower, Oracle bug 3833893 is responsible. This problem has been solved in Oracle 9.2.0.7. For more information, see Note 867681.

Longer waits for the TT enqueue are generally caused by Oracle bugs. If you experience problems with this enqueue type, you should contact Oracle Support for further assistance.

6> Oracle队列锁enq:TS,Temporary Segment (also TableSpace)

有多少TS,Temporary Segment资源？

每个临时段(例如排序端)对应2个资源

正被drop掉的tablespace或CREATE ROLLBACK SEGMENT对应一个TS资源

有多少个TS lock锁资源？

1个锁资源/对应每一个进程正在执行的如下类操作

该资源有多少用户？

所有正在使用临时空间、DROP TABLESPACE 、或CREATE ROLLBACK SEGMENT的用户进程

谁使用该资源？

所有正在使用临时空间、DROP TABLESPACE 、或CREATE ROLLBACK SEGMENT的用户进程

何时被使用？

对于每一个临时段的2个临时段锁TS enqueue resource有2种不同的用途。第一目的是为了串行化”High Water Mark”的上涨。第二目的是为了串行化创建、使用和删除一个临时段。当dropping tablespace或创建一个回滚段时将需要tablespace enqueue，目的是避免在row cache字典缓存层面出现的死锁。

ID1,ID2的组合：

ID1 ID2 含义

segment dba 0 串行控制高水位线

segment dba 1 创建、使用、删除临时段

tablespace number 2 避免发生在create rollback segment和drop tablespace时的死锁

Lock Value Block:

Not Used.

Init.ora Parameters:

_bump_highwater_mark_count (7.3).

Scope:

Local, Global

Deadlock Sensitive:

No.

Operation:

Synchronous.

7> enq: JI – contention等待事件

sys@ORCL>select name,parameter1,parameter2,parameter3 from v$event_name where name like 'enq: JI - contention';

NAME PARAMETER1 PARAMETER2 PARAMETER3

------------------------- --------------- --------------- ---------------

enq: JI - contention name|mode view object # 0

在物化视图操作（如刷新、更改）中保持的锁，以防止在同一个物化视图上的并发操作。

解决：确保一次只有一个会话执行刷新。如果有一个以上的会话，第一次会话将正常工作，但随后的会话将等待“enq: JI – contention”。

A materialized view cannot be fast refreshed more than once in a given period because it is serialized during the commit phase. Ensure that only one session at a time is performing the refreshes. If there is more than one session, the first session will work normally but the subsequent sessions will wait on “enq: JI – contention”.

Waits on this event can also be caused by on-commit time logic within the materialized view. Normally when a session updates record 1 and commits and then another session updates record 2 and commits, they do not have to wait for each other. However, when using an on commit-time fast refreshable materialized view on top of the table, we do have to wait when two sessions do totally unrelated transactions concurrently against the same table. This is not a problem when the table is modified infrequently or only by a single session, but it can be a big problem when applied to a table that performs a lot of modifications concurrently. Be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed.

8> enq: US – contention等待事件

reference http://blog.csdn.net/duanning397/article/details/7668703

与UNDO的设置有关。首先来介绍下undo_retention参数，该参数是撤销段的最短保留时间，而在默认情况下Oracle将根据表空间的大小和历史使用情况，自动调整undo信息保存时间，同时忽略 undo_retention的值,除非undo_retention的guarantee 特性被启用.也就是执行以下命令：

ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE;

在自动调整启用的情况下，实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。

select TUNED_UNDORETENTION from V$UNDOSTAT;

往往最短保存时间远远大于设定的UNDO_RETENTION。UNDO自动优化功能能够最大限度的使用undo表空间,满足大部分的sql执行,但是也带来一个问题：很多事务执行完毕之后，发现UNDO表空间会在很长时间都一直保持着使用率是接近100%的状态，active 状态的很少。这种接近状态还无法手工的收缩，甚至于重启数据库实例也无法缓解，而此时常常会收到undo表空间的监控报警。

再来说说enq: US - contention问题

这是oracle10g中开始出现的bug(在11.1.0.7中仍有这个BUG)，当因为系统activity增加或者降低的时候，oracle SMON进程会自动ONLINE或者OFFLINE rollback segments。这样导致某些与undo segments相关的latch或者enqueue被hold住太长时间，导致系统很多活跃session都开始等待enq: US - contention。

可以同时使用以下解决方法:

1. 设置event让SMON不自动OFFLINE回滚段。

alter system set events '10511 trace name context forever, level 1';

2. 设置参数_rollback_segment_count ：表示有多少rollback segment要处于online的状态；可以将该数值设置为数据库最繁忙的时候的回滚段数目。

alter system set "_rollback_segment_count"=;

这里以‘_’开头的为隐藏参数，通过show parameter 是看不到的，可以通过以下语句：

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

from x$ksppi a, x$ksppcv b

where a.indx = b.indx

and a.ksppinm like '%_rollback_segment_count%';

3. undo autotune bug多多。最好disable。

alter system set "_undo_autotune"= false;

这种方法就是关闭了UNDO的自动调整功能，同时也能解决掉UNDO表空间会在很长时间都一直保持着使用率是接近100%的问题。

4. 有一个patch: A fix to bug 7291739 is to set a new hidden parameter, _highthreshold_undoretention to set a high threshold for undo retention completely distinct from maxquerylen.

alter system set "_highthreshold_undoretention"=;

9> enq: TM – contention等待事件

reference http://blog.itpub.net/7194105/viewspace-704114/

sys@ORCL>select name,parameter1,parameter2,parameter3 from v$event_name where name like 'enq: TM - contention';

NAME PARAMETER1 PARAMETER2 PARAMETER3

------------------------- --------------- --------------- ---------------

enq: TM - contention name|mode object # table/partition

执行dml期间，为防止对与dml相关的对象进行修改，执行dml的进程必须对该表获得TM锁，若在获得TM锁的过程中发生争用，则等待enq:TM-contention事件。

TM锁其用途十分明确，但是准确的概念及定义方面有容易混淆的一面，例如:oracle的concept手册上，关于锁的分类说明如下：

DML锁：data lock，执行dml时保护数据的锁，rowlock (tx)保护特定的行，table lock(tm)保护整个表，可以通过DBA_DML_LOCKS观察

DDL锁：data dictionary lock,保护user/table/view/procedure等定义，可以通过dba_ddl_locks观察。

internal(内部) locks and latches.

实际上，DML和DDL锁只是为了合理分配锁而赋予的名称，请注意这一点

DML锁实际上与TM锁一致，DML锁可以通过DBA_DML_LOCKS视图观察，这个视图的作用是从V$LOCK视图上筛选类型的TM的，数据库上运行的TM锁数量可以利用DML_LOCKS参数知道，若DML_LOCKS这个参数设为0，则对于表无法获得TM锁，这时，oracle为了保障表定义保护，对于表根本上不运行执行DDL操作。因此，即便不获得TM锁，也运行修改表的特定行，如OPS环境下，为了减少在群集范围内获得TM锁中发生的附加资源消耗，有时也将 DML_LOCKS值修改为0.

DDL锁实际上和Library cache lock一致，DDL锁可以通过DBA_DDL_LOCKS视图观察，这个视图实际上起到加工X$KGLLK视图后显示的作用。DDL锁除DBA_DDL_LOCKS视图以外，还可以通过X$KGLLK,DBA_KGLLOCK等视图观察。

对于表，普通DML语句以SUB-Exclusive(SX-意向排他锁)获得TM锁，SUB-exclusive模式之间存在共享性，所以多个会话可以对相同的表执行DML。已执行DML的会话对于表，以sub-exclusive模式获得TM锁，对于已修改的数据以EXCLUSIIVE模式获得TX锁。

所以如下所示，可以通过V$LOCK视图，可以观察执行DML的会话以何种模式获得了哪些锁。

enq:TM-contention等待的发生，发生TM锁争用的情况如下：

1、修改无索引外键(foreign key)的父键时

2、DML与DDL之间的TM争用

3、LOCK TABLE 引起的TM锁争用

4、direct(直接) load工作引起的TM锁争用

无索引的外键

oralce 9i之前，没有索引的外键列是TM锁争用的主要原因，oracle 9i之前的版本，子表的外键列没有索引的状态下，若父表的KEY被修改，则对子表以shared模式或shared -sub-exclusive模式获得TM锁。已获得TM锁一直拥有直到父表修改key的事务结束为止(commit或rollback),因此经常发生严重的性能下降的现象，但是从oracle 9i开始，算法大幅改善，不再发生类似的争用现象，若还是使用oracle9i之前的版本，则需要养成在外键列上创建索引的习惯。

不当的DDL引起的TM争用

对于事务正在运行的表，基本上不可能执行DDL,因此，这时不会发生争用引起的性能问题。

减少DDL引起的TM锁争用的方法如下：

1、若对于数据多的表执行不当的DDL,则访问此表的所有DDL都会陷入等待状态，可能发展至故障状态，通过合理的管理，从根本上防止才是最好的方法。

2、执行DDL时，最好使用ONLINE选项，随着oracle版本升级，online状态下可执行的DDL逐步增加，大部分普通DDL上，可以使用ONLINE选项，例如：

online选项执行create index命令，不是以shared模式，而是以sub-shared(ss)（意向共享锁）模式获得TM锁的。这句话的解释是：创建索引的时候获取的TM锁的模式是SS模式(sub-shared) v$lock中lmod 是2，对表做DML获取的TM锁的模式是SX(Sub-exclusive) v$lock 中lmod是4，两种模式sub-shared 和 sub-exclusive 可以共享性。所以在创建索引的过程中，可以执行DML，即，不会发生enq:TM-contention等待。

3、使用并行parallel DDL将DDL的执行速率最大化，对拥有大量数据的表执行DDL时，若恰当使用Parallel选项，可将DDL本事性能最大化，而且同时使用nologging选项比较好，如果提升了DDL执行速度，TX锁争用引起的等待时间相应地也会下降。

利用lock table 主动获取TM锁

利用lock table...语句有意获取TM锁时，可能发生TM锁争用。

会话1 update test set id1=where rownum=1;

会话2 lock table test in exclusive mode;

如上所示，会话1 上因update以sub-exclusive模式获得TM锁的状态下，会话2利用lock table命令试图以exclusive模式获得TM锁，如果发生争用，则等待enq:TM-contention事件。

发生TM锁引起的争用时，收集锁拥有者(lock holder),在会话上执行的sql语句尤为重要，发生TM锁的情况非常多样，因此没有SQL语句，就很难做出准确判断的情况较多，引发问题的SQL语句是LOCK table语句，因此如果发生大量的TM锁争用，可以通过对应用程序的适当修正解决争用。

与其为了同步整个表上锁，不如考虑使用dbms_lock成包或使用select for update等，减少范围的方法。

select .. for update 对整个TM锁使用sub exclusive 模式，对TX锁使用记录行上的exclusive.

lock table test in exclusive mode;对整个表TM锁获取exclusive模式。

执行direct/parallel load工作时

insert/*+*append/ into或sql*loader的direct path load之类的部分功能，对于相应表以exclusive模式获得tm锁，direct load 工作不经过sga,而是直接写入到数据文件里，所以在执行工作期间不运行对表进行任何修改。得到保障，工作才能得以继续。direct load工作在执行期间，不运行对于表执行任何DDL或DML.因此事务多的时刻执行direct load工作时，需要确认TM锁争用是否可能引发的问题。

10> enq: RO fast object reuse等待事件

truncate一个分区时比较慢，查看进程的等待事件是enq: RO - fast object reuse

dbw0进程写回buffer cache中的脏数据块, 出现这个等待事件时或许可以考虑运行alter system flush buffer_cache; 对DB性能有很大影响，如果出现该问题，一般是BUG导致，最后应用合适的patch

关于该等待事件BUG说明：

reference http://blog.csdn.net/tianlesoftware/article/details/7734717

11> enq: HW – contention等待事件

reference http://blog.itpub.net/12679300/viewspace-1205087/

为防止多个进程同时修改HWM而提供的锁称为HW锁。想要移动HWM的进程必须获得HW锁。若在获取HW锁过程中发生争用，则等待enq: HW - contention事件。HW锁争用大部分是因大量执行insert所引发的，偶尔也会因大量执行update在回滚段中发生HW锁争用现象。若是update，表中段的扩展的大小虽然不多，但在创建回滚数据的过程中，需要回滚段的急速扩张。HW锁争用是在急速空间扩张时普遍出现的等待现象，有时也会引发严重的性能下降。

sys@ORCL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'enq: HW - contention';

EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3

---------- ------------------------- --------------- --------------- ---------------

254 enq: HW - contention name|mode table space # block

众所周知，Oracle高水位线标志着该线以下的block均被Oracle格式过，通俗一点讲就是该高水位线以下的block都被Oracle使用过。通常在执行insert操作时，当高水位线以下block不够用时，Oracle将会推进高水位线。更进一步讲，当有多个进程在同时进行insert操作时，比较容易引起高水位线争用，主要表现为enq: HW - contention。

如何找到事件：'enq: HW - contention' 热点对象：

1、查看v$session_wait，应该会有如下等待事件：

SQL> select p1, p2, p3 from v$session_wait where event = 'enq: HW - contention';

P1 P2 P3

---------- ---------- ----------

1213661190 7 140003563

2、通过P3进行DBMS_UTILITY转换可以获知发生争用的文件和block：

SQL> select dbms_utility.data_block_address_block(140003563),dbms_utility.data_block_address_file(140003563) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(140003563) DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(140003563)

------------------------------------------------ -----------------------------------------------

1591531 33

3、进而通过file#和block#定位对象：

SQL> select owner, segment_type, segment_name

from dba_extents

where file_id = 33 and 1591531 between block_id and block_id + blocks - 1;

减少HW锁争用的方法如下：

If this is a point of contention for an object, then manual allocation of extents solves the problem.

脚本：alter table allocate extent，提前分配空间；

2.5.5 free buffer waits

free buffer waits：是由于无法找到可用的buffer cache 空闲区域，需要等待DBWR 写入完成引起

一般是由于：

§ 低效的sql

§ 过小的buffer cache

§ DBWR 工作负荷过量

2.5.6 buffer busy wait/ read by other session

buffer busy wait/ read by other session 一般以上2个等待事件可以归为一起处理，建议客户都进行监控。以上等待时间可以由如下操作引起

§ select/select —- read by other session: 由于需要从数据文件中将数据块读入 buffer cache 中引起，有可能是大量的逻辑/物理读 ;或者过小的 buffer cache 引起

§ select/update —- buffer busy waits/ read by other session 是由于更新某数据块后需要在undo 中重建构建过去时间的块，有可能伴生 enq:cr-contention 是由于大量的物理读/逻辑读造成。

§ update/update —- buffer busy waits 由于更新同一个数据块（非同一行，同一行是enq:TX-contention）此类问题是热点块造成

§ insert/insert —- buffer busy waits 是由于freelist 争用造成，可以将表空间更改为ASSM 管理或者加大freelist 。

2.5.7 write complete waits

write complete waits :一般此类等待事件是由于 DBWR 将脏数据写入数据文件，其他进程如果需要修改 buffer cache会引起此等待事件，一般是 I/O 性能问题或者是DBWR 工作负荷过量引起

Wait time 1 Seconds.

2.5.8 control file parallel write

control file parallel write：频繁的更新控制文件会造成大量此类等待事件，如日志频繁切换，检查点经常发生，nologging 引起频繁的数据文件更改，I/O 系统性能缓慢。

2.5.9 log file sync

log file sync：一般此类等待时间是由于 LGWR 进程讲redo log buffer 写入redo log 中发生。如果此类事件频繁发生，可以判断为：

§ commit 次数是否过多

§ I/O 系统问题

§ 重做日志是否不必要被创建

§ redo log buffer 是否过大

2.6 Time Model Statistics

Time Model Statistics(时间模型统计)

Total time in database user-calls (DB Time): 90.5s
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	65.38	72.26
sql execute elapsed time	58.55	64.71
parse time elapsed	26.50	29.29
hard parse elapsed time	24.28	26.84
PL/SQL compilation elapsed time	5.97	6.59
connection management call elapsed time	5.91	6.54
PL/SQL execution elapsed time	3.62	4.00
hard parse (sharing criteria) elapsed time	1.65	1.83
failed parse elapsed time	0.25	0.28
sequence load elapsed time	0.09	0.10
repeated bind elapsed time	0.02	0.02
hard parse (bind mismatch) elapsed time	0.00	0.00
DB time	90.48
background elapsed time	115.64
background cpu time	41.35

Time Model Statistics几个特别有用的时间指标：

1> parse time elapsed(过去的)、hard parse elapsed time 结合起来看解析是否是主要矛盾，若是则重点是软解析还是硬解析

2> sequence load elapsed time sequence序列争用是否是问题焦点

3> PL/SQL compilation elapsed time PL/SQL对象编译的耗时

4> 注意PL/SQL execution elapsed time 纯耗费在PL/SQL解释器上的时间。不包括花在执行和解析其包含SQL上的时间

5> connection management call elapsed time 建立数据库session连接和断开的耗时

6> failed parse elapsed time 解析失败，例如由于ORA-4031

7> hard parse (sharing criteria) elapsed time 由于无法共享游标造成的硬解析

8> 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.7 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 94.6% of Total DB time 90.48 (s)
Total FG Wait Time: 20.25 (s) DB CPU time: 65.38 (s)

Wait Class	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	%DB time
DB CPU			65		72.26
Commit	1,163	0	13	11	14.51
User I/O	507	0	5	10	5.71
Concurrency	47	0	1	22	1.12
System I/O	931	0	1	1	0.65
Other	57	0	0	4	0.23
Application	263	0	0	0	0.13
Network	12,469	0	0	0	0.03
Configuration	1	0	0	2	0.00

sys@ORCL>select distinct wait_class from v$event_name;

WAIT_CLASS

----------------------------------------------------------------

Concurrency

User I/O

System I/O

Administrative

Other

Configuration

Scheduler

Cluster

Application

Queueing

Idle

Network

Commit

13 rows selected.

列释义：

Wait Class: 等待事件的类型，如上查询所示，被分作13个类型。 10.2.0.5有916个等待事件，其中Other类型占622个。

Waits: 该类型所属等待事件在快照时间内的等待次数

%Time Out : 等待超时的比率，未超时次数/waits * 100 (%)

Total Wait Time: 该类型所属等待事件总的耗时，单位为秒

Avg Wait(ms) : 该类型所属等待事件的平均单次等待时间，单位为ms ，实际这个指标对commit 和 user i/o 以及system i/o类型有点意义，其他等待类型由于等待事件差异较大所以看平均值的意义较小

waits / txn: 该类型所属等待事件的等待次数和事务比

class释义：

Other 类型：遇到该类型等待事件的话常见的原因是Oracle Bug或者网络、I/O存在问题。

Concurrency 类型：并行争用类型的等待事件，典型的如 latch: shared pool、latch: library cache、row cache lock、library cache pin/lock

Cluster 类型：为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 completion (日志文件大小/数目不够)，sequence的enq: SQ – contention (Sequence 使用nocache) ； Oracle认为它们是由于配置不当引起的，但实际未必真是这样的配置引起的。

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等待事件。

Commit：仅log file sync ，log file sync的影响十分广泛，值得我们深入讨论。

Network : 网络类型的等待事件例如 SQL*Net more data to client 、SQL*Net more data to dblink

Idle：空闲等待事件，最为常见的是rdbms ipc message (等待实例内部的ipc通信才干活，即别人告知我有活干，我才干，否则我休息==》Idle)， SQL*Net message from client(等待SQL*NET传来信息，否则目前没事干)

2.8 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	1,163	0	13	11	0.86	14.51
db file sequential read	368	0	2	6	0.27	2.48
Data file init write	26	0	2	77	0.02	2.22
latch: shared pool	30	0	1	28	0.02	0.94
control file parallel write	24	0	1	24	0.02	0.63
db file scattered read	17	0	0	26	0.01	0.48
Disk file operations I/O	86	0	0	3	0.06	0.30
db file single write	8	0	0	26	0.01	0.23

Foreground Wait Events 前台等待事件，数据主要来源于DBA_HIST_SYSTEM_EVENT

Event：等待事件名字

Waits：该等待事件在快照时间内等待的次数

%Timeouts：每一个等待事件有其超时的设置，例如buffer busy waits 一般为3秒， Write Complete Waits：的 timeout为1秒，如果等待事件单次等待达到timeout的时间，则会进入下一次该等待事件

Total Wait Time：该等待事件总的消耗的时间，单位为秒

Avg wait(ms)：该等待事件的单次平均等待时间，单位为毫秒

Waits/Txn：该等待事件的等待次数和事务比

2.9 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	2,631	0	23	9	1.94	19.61
os thread startup	144	0	12	84	0.11	10.45
db file async I/O submit	1,468	0	11	8	1.08	9.84
control file parallel write	1,324	0	7	5	0.97	5.85
Data file init write	15	0	1	92	0.01	1.20
db file sequential read	155	0	0	3	0.11	0.34
log file sync	2	0	0	167	0.00	0.29
latch: object queue header operation	1	0	0	194	0.00	0.17
Disk file operations I/O	68	0	0	2	0.05	0.14
Parameter File I/O	16	0	0	10	0.01	0.13

Background Wait Events 后台等待事件，数据主要来源于DBA_HIST_BG_EVENT_SUMMARY

Event：等待事件名字

Waits：该等待事件在快照时间内等待的次数

%Timeouts：每一个等待事件有其超时的设置，例如buffer busy waits 一般为3秒， Write Complete Waits的 timeout为1秒，如果等待事件单次等待达到timeout的时间，则会进入下一次该等待事件

Total Wait Time：该等待事件总的消耗的时间，单位为秒

Avg wait(ms)：该等待事件的单次平均等待时间，单位为毫秒

Waits/Txn：该等待事件的等待次数和事务比

2.10 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	7,647
IDLE_TIME	694,966
IOWAIT_TIME	2,863
SYS_TIME	3,181
USER_TIME	3,767
LOAD	0	1
VM_IN_BYTES	19,857,408
VM_OUT_BYTES	4,653,056
PHYSICAL_MEMORY_BYTES	2,087,579,648
NUM_CPUS	2
NUM_CPU_CORES	2
NUM_CPU_SOCKETS	1
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 操作系统统计信息

数据来源于V$OSSTAT / DBA_HIST_OSSTAT， 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时间片，单位为百分之一秒
所有CPU所能提供总的时间片	BUSY_TIME + IDLE_TIME = ELAPSED_TIME * CPU_COUNT
OS_CPU_WAIT_TIME	进程等OS调度的时间，cpu queuing
VM_IN_BYTES	换入页的字节数
VM_OUT_BYTES	换出页的字节数，部分版本下并不准确，例如Bug 11712010 Abstract: VIRTUAL MEMORY PAGING ON 11.2.0.2 DATABASES，仅供参考
IOWAIT_TIME	所有CPU花费在等待I/O完成上的时间单位为百分之一秒
RSRC_MGR_CPU_WAIT_TIME	是指当resource manager控制CPU调度时，需要控制对应进程暂时不使用CPU而进程到内部运行队列中，以保证该进程对应的consumer group(消费组)没有消耗比指定resource manager指令更多的CPU。RSRC_MGR_CPU_WAIT_TIME指等在内部运行队列上的时间，在等待时不消耗CPU

2.11 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	498	5	37	1	0	0	6851	0
orcl.test.com	9	0	10	0	0	0	5618	0
SYS$BACKGROUND	274	2	171	12	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.12 Host CPU

Host CPU

CPUs	Cores	Sockets	Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
2	2	1	0.22	1.31	0.5	0.5	0.4	98.9

“Load Average” begin/end值代表每个CPU的大致运行队列大小。上例中快照开始到结束，平均 CPU负载增加了；与《7. Operating System Statistics》中的LOAD相呼应。

%User+%System=> 总的CPU使用率，在这里是1.0%

《WORKLOAD REPOSITORY report for》《Operating System Statistics》《Host Cpu》--->《Operating System Statistics》

Elapsed Time * NUM_CPUS * CPU utilization= 60.13 (mins) * 2 * 1.0% = 1.2026 mins * 60s * 100=Busy Time

(Busy_Time=SYS_TIME+USER_TIME 消耗的CPU时间片，单位为百分之一秒)

2.13 Instance CPU

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
1.5	139.6	0.0

%Total CPU，该实例所使用的CPU占总CPU的比例 % of total CPU for Instance

%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到底是否是本实例消耗的，还是主机上其他程序

% of busy CPU for Instance= （DB CPU+ background cpu time) / (BUSY_TIME /100)

% of Total CPU for Instance = ( DB CPU+ background cpu time)/( BUSY_TIME+IDLE_TIME/100)

%DB time waiting for CPU (Resource Manager)= (RSRC_MGR_CPU_WAIT_TIME/100)/DB TIME

3.TOP SQL

TOP SQL 的数据部分来源于 dba_hist_sqlstat

3.1 SQL ordered by Elapsed Time按照SQL消耗的时间来排列TOP SQL

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 34.6% of Total DB Time (s): 90

· Captured PL/SQL account for 11.8% of Total DB Time (s): 90

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
7.23	696	0.01	7.99	56.73	0.00	7ng34ruy5awxq		select i.obj#, i.ts#, i.file#,...
3.82	13	0.29	4.22	90.09	1.42	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...

注意对于PL/SQL，SQL Statistics不仅会体现该PL/SQL的执行情况，还会包括该PL/SQL包含的SQL语句的情况。如上例一个TOP PL/SQL执行了448s，而这448s中绝大多数是这个PL/SQL下的一个SQL执行500次耗费的。

则该TOP PL/SQL和TOP SQL都上榜，一个执行一次耗时448s，一个执行500次耗时448s。如此情况则Elapsed Time加起来可能超过100%的Elapsed Time，这是正常的。

对于鹤立鸡群的SQL很有必要一探究竟，跑个@?/rdbms/admin/awrsqrpt看看吧！这个脚本生成SQL语句的AWR报告，可以看到语句消耗cpu时间，执行次数，逻辑读，物理读，sql的执行计划以及sql的full sql text，对调优非常方便

Elapsed Time (s): 该SQL累计运行所消耗的时间

Executions : 该SQL在快照时间内总计运行的次数；注意，对于在快照时间内还没有执行完的SQL 不计为1一次，所以如果看到executions=0而 又是TOP SQL，则很有可能是因为该SQL 运行较旧还没执行完，需要特别关注一下。

Elapsed Time per Exec (s)：平均每次执行该SQL耗费的时间，对于OLTP类型的SELECT/INSERT/UPDATE/DELETE而言平均单次执行时间应当非常短，如0.1秒或者更短才能满足其业务需求，如果这类轻微的OLTP操作单次也要几秒钟的话，是无法满足对外业务的需求的；例如你在ATM上提款，并不仅仅是对你的账务库的简单UPDATE，而需要在类似风险控制的前置系统中记录你本次的流水操作记录，实际取一次钱可能要有几十乃至上百个OLTP类型的语句被执行，但它们应当都是十分快速的操作；如果这些操作也变得很慢，则会出现大量事务阻塞，系统负载升高，DB TIME急剧上升的现象。对于OLTP数据库而言如果执行计划稳定，那么这些OLTP操作的性能应当是铁板钉钉的，但是一旦某个因素发生变化，例如存储的明显变慢、内存换页的大量出现时则上述的这些transaction操作很可能成数倍到几十倍的变慢，这将让此事务系统短期内不可用。

对于维护操作，例如加载或清除数据，大的跑批次、报表而言 Elapsed Time per Exec (s)高一些是正常的。

%Total 该SQL所消耗的时间占总的DB Time的百分比，即 (SQL Elapsed Time / Total DB TIME)

% CPU 该SQL 所消耗的CPU 时间占该SQL消耗的时间里的比例，即 (SQL CPU Time / SQL Elapsed Time) ，该指标说明了该语句是否是CPU敏感的

%IO 该SQL 所消耗的I/O 时间占该SQL消耗的时间里的比例，即(SQL I/O Time/SQL Elapsed Time) ，该指标说明了该语句是否是I/O敏感的

SQL Id : 通过计算SQL 文本获得的SQL_ID ，不同的SQL文本必然有不同的SQL_ID，对于10g~11g而言只要SQL文本不变那么在数据库之间该SQL 对应的SQL_ID应当不不变的， 12c中修改了SQL_ID的计算方法

Captured SQL account for 53.9% of Total DB Time (s) 对于不绑定变量的应用来说Top SQL有可能失准，所以要参考本项

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 32.2% of Total CPU Time (s): 65

· Captured PL/SQL account for 12.4% of Total CPU Time (s): 65

CPU Time (s)	Executions	CPU per Exec (s)	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
4.10	696	0.01	6.27	7.23	56.73	0.00	7ng34ruy5awxq		select i.obj#, i.ts#, i.file#,...
3.44	13	0.26	5.26	3.82	90.09	1.42	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...

CPU TIME : 该SQL 在快照时间内累计执行所消耗的CPU 时间片，单位为s

Executions : 该SQL在快照时间内累计执行的次数

CPU per Exec(s)：该SQL 平均单次执行所消耗的CPU时间，即( SQL CPU TIME / SQL Executions )

%Total:该SQL累计消耗的CPU时间占该时段总的DB CPU的比例，即(SQL CPU TIM / Total DB CPU)

% CPU 该SQL 所消耗的CPU 时间占该SQL消耗的时间里的比例，即 (SQL CPU Time / SQL Elapsed Time) ，该指标说明了该语句是否是CPU敏感的

%IO 该SQL 所消耗的I/O 时间占该SQL消耗的时间里的比例，即(SQL I/O Time/SQL Elapsed Time) ，该指标说明了该语句是否是I/O敏感的

3.3 Buffer Gets SQL ordered by Gets

SQL ordered by Gets

· Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.

· %Total - Buffer Gets as a percentage of Total Buffer Gets

· %CPU - CPU Time as a percentage of Elapsed Time

· %IO - User I/O Time as a percentage of Elapsed Time

· Total Buffer Gets: 382,388

· Captured SQL account for 42.8% of Total

Buffer Gets	Executions	Gets per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
24,662	13	1,897.08	6.45	3.82	90.1	1.4	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
16,554	6,090	2.72	4.33	0.28	89.5	0	96g93hntrzjtr		select /+ rule / bucket_cnt,...

注意 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 完全不一样也是有可能的，需要因地制宜来探究到底是什么问题导致的High CPU，进而裁度解决之道。

Buffer Gets : 该SQL在快照时间内累计运行所消耗的buffer gets，包括了consistent read 和 current read

Executions : 该SQL在快照时间内累计执行的次数

Gets per Exec : 该SQL平均单次的buffer gets ，对于事务型transaction操作而言一般该单次buffer gets小于2000

% Total 该SQL 累计运行所消耗的buffer gets占总的db buffer gets的比率， (SQL buffer gets / DB total buffer gets)

3.4 Physical Reads SQL ordered by Reads

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: 605

· Captured SQL account for 24.0% of Total

Physical Reads	Executions	Reads per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
40	98	0.41	6.61	0.33	92.74	93.29	97c1jzd7mdg2k		insert into idl_ub1$(obj#, par...
30	13	2.31	4.96	3.82	90.09	1.42	6gvch1xu9ca3g		DECLARE job

Physical reads : 该SQL累计运行所消耗的物理读

Executions : 该SQL在快照时间内累计执行的次数

Reads per Exec : 该SQL 单次运行所消耗的物理读， (SQL Physical reads/Executions) ，对于OLTP transaction 类型的操作而言单次一般不超过100

%Total:该SQL累计消耗的物理读占该时段总的物理读的比例，即 ( SQL physical read / Total DB physical read )

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: 72,985

· Captured SQL account for 50.0% of Total

Executions	Rows Processed	Rows per Exec	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module	SQL Text
6,090	4,373	0.72	0.28	89.5	0	96g93hntrzjtr		select /+ rule / bucket_cnt,...
3,513	5,419	1.54	0.35	64.1	0	cm5vu20fhtnq1		select /*+ connect_by_filterin...

按照执行次数来排序的话，也是性能报告对比时一个重要的参考因素，因为如果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: 30,583

· Captured SQL account for 45.4% of Total

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
3,513	3,513	11.49	cm5vu20fhtnq1		select /*+ connect_by_filterin...
835	107	2.73	0v3dvmc22qnam		insert into sys.col_usage$ (ob...

Parse Calls : 解析调用次数，与上文的 Load Profile中的Parse 数一样包括软解析soft parse和硬解析hard parse

Executions : 该SQL在快照时间内累计执行的次数

%Total Parses : 本SQL 解析调用次数占该时段数据库总解析次数的比率，为 (SQL Parse Calls / Total DB Parse Calls)

3.7 SQL ordered by Sharable Memory

SQL ordered by Sharable Memory Snaps: 70719-70723

-> Only Statements with Sharable Memory greater than 1048576 are displayed

Sharable Mem (b) Executions % Total SQL Id

---------------- ------------ -------- -------------

8,468,359 39 0.08 au89sasqfb2yn

Module: MZContentBridge

SELECT t0.ASPECT_RATIO, t0.CREATED, t0.FILE_EXTENSION, t0.HEIGHT, t0.VIDEO_FILE_

DIMENSIONS_ID, t0.LAST_MODIFIED, t0.NAME, t0.WIDTH FROM MZ_VIDEO_FILE_DIMENSIONS

t0 WHERE (t0.HEIGHT = :1 AND t0.WIDTH = :2 )

SQL ordered by Sharable Memory , 一般该部分仅列出Sharable Mem (b)为1 MB以上的SQL 对象 (Only Statements with Sharable Memory greater than 1048576 are displayed) 数据来源是 DBA_HIST_SQLSTAT.SHARABLE_MEM

Shareable Mem(b): SQL 对象所占用的共享内存使用量

Executions : 该SQL在快照时间内累计执行的次数

%Total : 该SQL 对象锁占共享内存占总的共享内存的比率

3.8 SQL ordered by Version Count

Version Count Oracle中的执行计划可以是多版本的，即对于同一个SQL语句有多个不同版本的执行计划，这些执行计划又称作子游标，而一个SQL语句的文本可以称作一个父游标。一个父游标对应多个子游标，产生不同子游标的原因是 SQL在被执行时无法共享之前已经生成的子游标，原因是多种多样的，例如在本session中做了一个优化器参数的修改例如optimizer_index_cost_adj 从100 修改到99，则本session的优化环境optimizer env将不同于之前的子游标生成环境，这样就需要生成一个新的子游标，例如：

sys@ORCL>create table emp as select * from scott.emp;

Table created.

sys@ORCL>select * from emp where empno=1;

no rows selected

sys@ORCL>select /*+shall*/ * from emp where empno=1;

no rows selected

sys@ORCL>select SQL_ID,version_count from V$SQLAREA WHERE SQL_TEXT like '%shall%' and SQL_TEXT not like '%like%';

SQL_ID VERSION_COUNT

------------- -------------

4nk6yn1unaucc 1

sys@ORCL>select count(*) from v$sql where sql_id='4nk6yn1unaucc';

COUNT(*)

----------

1

sys@ORCL>alter session set optimizer_index_cost_adj=99;

Session altered.

sys@ORCL>select /*+shall*/ * from emp where empno=1;

no rows selected

sys@ORCL>select SQL_ID,version_count from V$SQLAREA WHERE SQL_TEXT like '%shall%' and SQL_TEXT not like '%like%';

SQL_ID VERSION_COUNT

------------- -------------

4nk6yn1unaucc 2

sys@ORCL>select count(*) from v$sql where sql_id='4nk6yn1unaucc';

COUNT(*)

----------

2

sys@ORCL>select child_number ,OPTIMIZER_ENV_HASH_VALUE,PLAN_HASH_VALUE from v$SQL where SQL_ID='4nk6yn1unaucc';

CHILD_NUMBER OPTIMIZER_ENV_HASH_VALUE PLAN_HASH_VALUE

------------ ------------------------ ---------------

0 1528205102 3956160932

1 2406422218 3956160932

可以看到上述演示中修改optimizer_index_cost_adj=99 （这个初始化参数代表一个百分比，取值范围在1到10000之间，该参数表示索引扫描和全表扫描成本的比较。）导致CBO 优化器的优化环境发生变化，表现为不同的OPTIMIZER_ENV_HASH_VALUE，之后生成了2个子游标，但是这2个子游标的PLAN_HASH_VALUE同为3956160932，则说明了虽然是不同的子游标但实际子游标里包含了的执行计划是一样的；所以请注意任何一个优化环境的变化 (V$SQL_SHARED_CURSOR)以及相关衍生的BUG 都可能导致子游标无法共享，虽然子游标无法共享但这些子游标扔可能包含完全一样的执行计划，这往往是一种浪费。

注意V$SQLAREA.VERSION_COUNT 未必等于select count(*) FROM V$SQL WHERE SQL_ID=” ，即 V$SQLAREA.VERSION_COUNT 显示的子游标数目未必等于当前实例中还存有的子游标数目，由于shared pool aged out算法和其他一些可能导致游标失效的原因存在，所以子游标被清理掉是很常见的事情。 V$SQLAREA.VERSION_COUNT只是一个计数器，它告诉我们曾经生成了多少个child cursor，但不保证这些child 都还在shared pool里面。

此外可以通过v$SQL的child_number字段来分析该问题，如果child_number存在跳号则也说明了部分child被清理了。

子游标过多的影响，当子游标过多(例如超过3000个时),进程需要去扫描长长的子游标列表child cursor list以找到一个合适的子游标child cursor，进而导致cursor sharing 性能问题现大量的Cursor: Mutex S 和 library cache lock等待事件。

关于子游标的数量控制，可以参考《11gR2游标共享新特性带来的一些问题以及_cursor_features_enabled、_cursor_obsolete_threshold和106001 event》。

Executions : 该SQL在快照时间内累计执行的次数

Hash Value : 共享SQL 的哈希值

Only Statements with Version Count greater than 20 are displayed 注意该环节仅列出version count > 20的语句

3.9 Cluster Wait Time SQL ordered by Cluster Wait Time

SQL ordered by Cluster Wait Time DB/Inst: ITSCMP/itscmp2 Snaps: 70719-70723

-> %Total - Cluster Time as a percentage of Total Cluster Wait Time

-> %Clu - Cluster Time as a percentage of Elapsed Time

-> %CPU - CPU Time as a percentage of Elapsed Time

-> %IO - User I/O Time as a percentage of Elapsed Time

-> Only SQL with Cluster Wait Time > .005 seconds is reported

-> Total Cluster Wait Time (s): 525,480

-> Captured SQL account for 57.2% of Total

Cluster Elapsed

Wait Time (s) Executions %Total Time(s) %Clu %CPU %IO SQL Id

-------------- ------------ ------ ---------- ------ ------ ------ -------------

132,639.3 38,848 25.2 181,411.3 73.1 .0 .1 g0yc9szpuu068

Only SQL with Cluster Wait Time > .005 seconds is reported 这个环节仅仅列出Cluster Wait Time > 0.005 s的SQL

该环节的数据主要来源于DBA_HIST_SQLSTAT.CLWAIT_DELTA Delta value of cluster wait time

Cluster Wait Time : 该SQL语句累计执行过程中等待在集群等待上的时间，单位为秒，你可以理解为当一个SQL 执行过程中遇到了gc buffer busy、gc cr multi block request 之类的Cluster等待，则这些等待消耗的时间全部算在 Cluster Wait Time里。

Executions : 该SQL在快照时间内累计执行的次数

%Total: 该SQL所消耗的Cluster Wait time 占总的Cluster Wait time的比率，为(SQL cluster wait time / DB total cluster Wait Time)

%Clu: 该SQL所消耗的Cluster Wait time 占该SQL 总的耗时的比率，为(SQL cluster wait time / SQL elapsed Time),该指标说明了该语句是否是集群等待敏感的

% CPU 该SQL 所消耗的CPU 时间占该SQL消耗的时间里的比例，即 (SQL CPU Time / SQL Elapsed Time) ，该指标说明了该语句是否是CPU敏感的

%IO 该SQL 所消耗的I/O 时间占该SQL消耗的时间里的比例，即(SQL I/O Time/SQL Elapsed Time) ，该指标说明了该语句是否是I/O敏感的

4.Instance Activity Stats

Instance Activity Statistics

Back to Top

Key Instance Activity Stats

Ordered by statistic name

Statistic	Total	per Second	per Trans
db block changes	73,797	20.46	54.30
execute count	72,985	20.23	53.70

Instance Activity Stats 的数据来自于 DBA_HIST_SYSSTAT，DBA_HIST_SYSSTAT来自于V$SYSSTAT。

这里每一个指标都代表一种数据库行为的活跃度，例如redo size 是指生成redo的量，sorts (disk) 是指磁盘排序的次数，table scans (direct read) 是指直接路径扫描表的次数。

虽然这些指标均只有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 几个指标，具体可以见文档《Oracle索引块分裂split信息汇总》

3> 系统出现IO类型的等待事件为TOp Five 例如 db file sequential/scattered read ，我们需要通过AWR来获得系统IO吞吐量和IOPS:

physical read bytes 主要是应用造成的物理读取(Total size in bytes of all disk reads by application activity (and not other instance activity) only.) 而physical read total bytes则包括了 rman备份恢复和后台维护任务所涉及的物理读字节数，所以我们在研究IO负载时一般参考 physical read total bytes；以下4对指标均存在上述的关系

physical read bytes	physical read total bytes	物理读的吞吐量/秒
physical read IO requests	physical read total IO requests	物理读的IOPS
physical write bytes	physical write total bytes	物理写的吞吐量/秒
physical write IO requests	physical write total IO requests	物理写的IOPS

总的物理吞吐量/秒=physical read total bytes+physical write total bytes

总的物理IOPS= physical read total IO requests+ physical write total IO requests

IO的主要指标吞吐量、IOPS和延迟均可以从AWR中获得了， IO延迟的信息可以从 User I/O的Wait Class Avg Wait time获得，也可以参考11g出现的IOStat by Function summary

Instance Activity Stats有大量的指标，但是对于这些指标的介绍没有那一份文档有完整详尽的描述，即便在Oracle原厂内部要没有(或者是Maclean没找到)，实际是开发人员要引入某一个Activity Stats是比较容易的，并不像申请引入一个新后台进程那样麻烦，Oracle对于新版本中新后台进程的引入有严格的要求，但Activity Stats却很容易，往往一个one-off patch中就可以引入了，实际上Activity Stats在源代码层仅仅是一些计数器。

较为基础的statistics，大家可以参考官方文档的Statistics Descriptions描述，地址在这里。

对于深入的指标例如 “Batched IO (space) vector count”这种由于某些新特性被引入的，一般没有很详细的材料，需要到源代码中去阅读相关模块才能总结其用途，对于这个工作一般原厂是很延迟去完成的，所以没有一个完整的列表。如果大家有对此的疑问，请去t.askmaclean.com 发一个帖子提问。

Instance Activity Stats - Absolute Values Snaps: 7071

-> Statistics with absolute values (should not be diffed)

Statistic Begin Value End Value

-------------------------------- --------------- ---------------

session pga memory max 1.157882826E+12 1.154290304E+12

session cursor cache count 157,042,373 157,083,136

session uga memory 5.496429019E+14 5.496775467E+14

opened cursors current 268,916 265,694

workarea memory allocated 827,704 837,487

logons current 2,609 2,613

session uga memory max 1.749481584E+13 1.749737418E+13

session pga memory 4.150306913E+11 4.150008177E+11

Instance Activity Stats – Absolute Values是显示快照起点和终点的一些指标的绝对值

logon current 当前时间点的登录数

opened cursors current 当前打开的游标数

session cursor cache count 当前存在的session缓存游标数

Instance Activity Stats - Thread ActivityDB/Inst: G10R25/G10R25 Snaps: 3663-3

-> Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic Total per Hour

-------------------------------- ------------------ ---------

log switches (derived) 17 2,326.47

log switches (derived) 日志切换次数，见《理想的在线重做日志切换时间是多长？》

一般认为一小时切换3-4次，或者15分钟切换一次是理想的

但是实际上这对于每秒产生 5~10M的大型OLTP 系统而言不太可能保持这个切换率

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)
SYSAUX	249	0	3.17	1.12	729	0.07	3	0	0	0.00
SYSTEM	272	0	8.35	1.20	381	0.07	7	0	1	0.00
UNDOTBS1	0	0	0.00	0.00	255	0.00	0	0	1	0.00
USERS	0	0	0.00	0.00	119	0.00	0	0	1	0.00
TEMP	1	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)
SYSAUX	/u01/app/oracle/oradata/orcl/sysaux01.dbf	249	0	3.17	1.12	0		2.78		729		0		0		0.00
SYSTEM	/u01/app/oracle/oradata/orcl/system01.dbf	272	0	8.35	1.20	0		7.47		381		0		1		0.00
TEMP	/u01/app/oracle/oradata/orcl/temp01.dbf	1	0	0.00	1.00	0		0.00		1		0		0
UNDOTBS1	/u01/app/oracle/oradata/orcl/undotbs01.dbf	0	0			0				255		0		1		0.00
USERS	/u01/app/oracle/oradata/orcl/users01.dbf	0	0			0				119		0		1		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的次数( 9i中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

Back to Top

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	43,610	100	382,361	605	4,343	0	0	3

该环节的数据主要来源于WRH$_BUFFER_POOL_STATISTICS,而WRH$_BUFFER_POOL_STATISTICS是定期汇总v$SYSSTAT中的数据

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.Advisory Statistics

7.1 CheckpointActivity&InstanceRecovery Stats

检查点与实例恢复

Checkpoint Activity

Total Physical Writes: 4,366

MTTR Writes	Log Size Writes	Log Ckpt Writes	Other Settings Writes	Autotune Ckpt Writes	Thread Ckpt Writes
0	0	0	0	4,343	0

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	8	315	1485	5123	165888	5123
E	0	8	307	2049	42869	165888	42869

该环节的数据来源于WRH$_INSTANCE_RECOVERY

MTTR Writes : 为了满足FAST_START_MTTR_TARGET 指定的MTTR值而做出的物理写 WRITES_MTTR

Log Size Writes ：由于最小的redo log file而做出的物理写 WRITES_LOGFILE_SIZE

Log 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_CKPT

B 代表开始点， E 代表结尾

Targt MTTR (s) : 目标MTTR (mean time to recover)意为有效恢复时间，单位为秒。 TARGET_MTTR 的计算基于给定的参数FAST_START_MTTR_TARGET，而TARGET_MTTR作为内部使用。实际在使用中 Target MTTR未必能和FAST_START_MTTR_TARGET一样。如果FAST_START_MTTR_TARGET过小，那么TARGET_MTTR 将是系统条件所允许的最小估算值；如果FAST_START_MTTR_TARGET过大，则TARGET_MTTR以保守算法计算以获得完成恢复的最长估算时间。

estimated_mttr (s): (估计_mttr) 当前基于脏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；数据来源select LOGFILESZ from X$targetrba; select LOG_FILE_SIZE_REDO_BLKS from v$instance_recovery;

Log Ckpt Timeout RedoBlks ：为了满足LOG_CHECKPOINT_TIMEOUT 所需要处理的redo block数，lag for checkpoint timeout ；数据来源select CT_LAG from x$targetrba;

Log Ckpt Interval RedoBlks ：为了满足LOG_CHECKPOINT_INTERVAL 所需要处理的redo block数， lag for checkpoint interval；数据来源select CI_LAG from x$targetrba;

Opt Log Sz(M) : 基于FAST_START_MTTR_TARGET 而估算出来的redo logfile 的大小，单位为MB 。 Oracle官方推荐创建的重做日志大小至少大于这个估算值

Estd RAC Avail Time ：指评估的 RAC中节点失败后集群从冻结到部分可用的时间，这个指标仅在RAC中可用，单位为秒。 ESTD_CLUSTER_AVAILABLE_TIME

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	32	0.09	4	2.86	56	1	116.00
D	64	0.18	8	1.99	39	1	77.00
D	96	0.27	12	1.40	27	1	50.00
D	128	0.36	16	1.16	23	1	39.00
D	160	0.45	20	1.07	21	1	35.00
D	192	0.54	24	1.01	20	1	32.00
D	224	0.63	27	1.00	20	1	32.00
D	256	0.72	31	1.00	20	1	32.00

缓冲池的颗粒大小可以参考 SELECT * FROM V$SGAINFO where name='Granule Size';

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大小对当前设置的比例因子，例如current_size是 100M，则如果评估值是109M 那么 size Factor 就是 1.09

Buffers (thousands) ：指这个buffer pool 尺寸下的buffer 数量，要乘以1000才是实际值

Est Phys Read Factor：评估的物理读因子，例如当前尺寸的BufferPool会引起100个物理读,则别的尺寸的BufferPool如果引起120个物理读,那么对应尺寸的EstPhysReadFactor就是1.2

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

注意 Size Factor 和 Est Phys Read Factor之间不是简单的 线性关系，所以需要人为介入评估得失

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	178	0

PGA Cache Hit % : 指 W/A WorkArea工作区的数据仅在内存中处理的比率，PGA缓存命中率

workarea是PGA中负责处理排序、哈希连接和位图合并操作的区域; workarea 也叫做 SQL 作业区域

W/A MB processes: 指在Workarea中处理过的数据的量，单位为MB

Extra W/A MB Read/Written : 指额外从磁盘上读写的工作区数据，单位为 MB

7.4 PGA Aggr Target Stats

Warning: pga_aggregate_target was set too low for current workload, as this

value was exceeded during this interval. Use the PGA Advisory view

to help identify a different value for pga_aggregate_target.

PGA Aggr Target Stats Snaps: 70719-70723

-> B: Begin Snap E: End Snap (rows dentified with B or E contain data

which is absolute i.e. not diffed over the interval)

-> Auto PGA Target - actual workarea memory target

-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)

-> %PGA W/A Mem - percentage of PGA memory allocated to workareas

-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt

-> %Man W/A Mem - percentage of workarea memory under manual control

%PGA %Auto %Man

PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem

Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)

- ---------- ---------- ---------- ---------- ------ ------ ------ ----------

B 8,192 512 23,690.5 150.1 .6 100.0 .0 838,860

E 8,192 512 23,623.6 156.9 .7 100.0 .0 838,860

-------------------------------------------------------------

此环节的数据来源主要是 WRH$_PGASTAT

PGA Aggr Target(M)：本质上就是pga_aggregate_target，当然在AMM(memory_target)环境下这个值可能会自动变化

Auto PGA Target(M) : 在自动PGA 管理模式下实际可用的工作区内存 “aggregate PGA auto target “，因为PGA还有其他用途，不能全部作为workarea memory

PGA Mem Alloc(M)：目前已分配的PGA内存，alloc不等于inuse即分配的内存不等于在使用的内存，理论上PGA会将确实不使用的内存返回给OS(PGA memory freed back to OS) ，但是存在PGA占用大量内存而不释放的场景

在上例中 pga_aggregate_target 仅为8192M ，而实际processes 在 2,615~ 8000之间，如果一个进程耗费5MB的PGA 也需要 10000M的PGA ，而实际这里 PGA Mem Alloc(M)是23,690 M ，这说明存在PGA 的过载，需要调整pga_aggregate_target

W/A PGA Used(M)：所有的工作区workarea(包括manual和 auto)使用的内存总和量，单位为MB

%PGA W/A Mem: 分配给workarea的内存量占总的PGA的比例， (W/A PGA Used)/PGA Mem Alloc

%Auto W/A Mem : AUTO 自动工作区管理所控制的内存(workarea_size_policy=AUTO) 占总的workarea内存的比例

%Man W/A Mem : MANUAL 手动工作区管理所控制的内存(workarea_size_policy=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	8,859	8,859	0	0
64K	128K	26	26	0	0
256K	512K	2	2	0	0
512K	1024K	61	61	0	0
1M	2M	49	49	0	0
2M	4M	3	3	0	0
4M	8M	6	6	0	0

数据来源：WRH$_SQL_WORKAREA_HISTOGRAM

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 Advisory

PGA 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
47	0.13	1,013.60	103.75	91.00	6	155,683
94	0.25	1,013.60	103.75	91.00	5	155,683
188	0.50	1,013.60	0.00	100.00	0	141,227
282	0.75	1,013.60	0.00	100.00	0	141,227
376	1.00	1,013.60	0.00	100.00	0	141,227
451	1.20	1,013.60	0.00	100.00	0	141,227
526	1.40	1,013.60	0.00	100.00	0	141,227
602	1.60	1,013.60	0.00	100.00	0	141,227
677	1.80	1,013.60	0.00	100.00	0	141,227
752	2.00	1,013.60	0.00	100.00	0	141,227
1,128	3.00	1,013.60	0.00	100.00	0	141,227
1,504	4.00	1,013.60	0.00	100.00	0	141,227
2,256	6.00	1,013.60	0.00	100.00	0	141,227
3,008	8.00	1,013.60	0.00	100.00	0	141,227

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 PGA 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)
136	0.77	0	306	2,253	0.58	1,703	21.29	492
140	0.80	9	792	2,730	0.70	1,226	15.33	565
144	0.82	15	1,150	3,054	0.79	902	11.28	617
148	0.84	18	1,298	3,283	0.85	673	8.41	647
152	0.86	21	1,390	3,426	0.88	530	6.63	664
156	0.89	24	1,559	3,557	0.92	399	4.99	678
160	0.91	26	1,714	3,662	0.94	294	3.68	690
164	0.93	29	1,864	3,747	0.97	209	2.61	697
168	0.95	32	2,068	3,817	0.98	139	1.74	697
172	0.98	36	2,230	3,852	0.99	104	1.30	697

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

Est LC Mem Obj 指评估的指定大小的共享池内的library cache memory object的数量 ESTD_LC_MEMORY_OBJECTS

Est LC Time Saved(s): 指在指定的共享池大小情况下可找到需要的library cache memory objects，从而节约的解析时间。这些节约的解析时间也是花费在共享池内重复加载需要的对象(reload)，这些对象可能因为共享池没有足够的free memory而被aged out. ESTD_LC_TIME_SAVED

Est LC Time Saved Factr : Est LC Time Saved(s)的比例因子，( Est LC Time Saved(s)/ Current LC Time Saved(s) ) ESTD_LC_TIME_SAVED_FACTOR

Est LC Load Time (s): 在指定的共享池大小情况下解析的耗时

Est LC Load Time Factr：Est LC Load Time (s)的比例因子， (Est LC Load Time (s)/ Current LC Load Time (s)) ESTD_LC_LOAD_TIME_FACTOR

Est LC Mem Obj Hits (K) : 在指定的共享池大小情况下需要的library cache memory object正好在共享池中被找到的次数 ESTD_LC_MEMORY_OBJECT_HITS；

对于想缩小 shared_pool_size 共享池大小的需求，可以关注Est LC Mem Obj Hits (K) ，如上例中共享池为164M时Est LC Mem Obj Hits (K) 就为697且之后不动，则可以考虑缩小shared_pool_size到该值，但要注意每个版本/平台上对共享池的最低需求，包括RAC中gcs resource 、gcs shadow等资源均驻留在shared pool中，增大db_cache_size时要对应关注。

7.8 SGA Target Advisory

SGA Target Advisory

SGA Target Size (M)	SGA Size Factor	Est DB Time (s)	Est Physical Reads
280	0.50	279	38,827
420	0.75	222	22,756
560	1.00	209	19,562
700	1.25	209	19,550
840	1.50	209	19,550
980	1.75	209	19,550
1,120	2.00	209	19,550

该环节数据来源于WRH$_SGA_TARGET_ADVICE

SGA target Size : 用以评估的sga target大小 (sga_target)

SGA Size Factor: SGA Size的比例因子，(est SGA target Size / Current SGA target 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)
4	1.00	0	0	0	0
8	2.00	0	0	0	0
12	3.00	0	0	0	0
16	4.00	0	0	0	0
20	5.00	0	0	0	0
24	6.00	0	0	0	0
28	7.00	0	0	0	0
32	8.00	0	0	0	0
36	9.00	0	0	0	0
40	10.00	0	0	0	0

该环节只有当使用了Streams 流复制时才会有必要数据，数据来源 WRH$_STREAMS_POOL_ADVICE

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

7.10 Java Pool Advisory

java pool的相关指标与shared pool相似，不再阐述

8.Wait Statistics

8.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	1	0	0
file header block	1	0	0
undo header	1	0	0

数据来源： WRH$_WAITSTAT

该环节是对缓冲池中各类型(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

如果用户正使用 undo_management=AUTO 的SMU 则一般不会因为rollback segment过少而引起undo header block类块的等待

对于INSERT 而引起的 buffer争用等待：

1> 对于手动segment 管理MSSM 考虑增加Freelists、Freelist Groups

2> 使用ASSM ，当然ASSM本身没什么参数可调

对于INSERT ON INDEX 引起的争用：

1> 使用反向索引key

2> 使用HASH分区和本地索引

3> 可能的情况下减少index的density(密度)

8.2 Enqueue Activity

enqueue 队列锁等待

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)
CF-Controlfile Transaction	1,410	1,410	0	1	0	10.00
SQ-Sequence Cache	13	13	0	1	0	10.00

Enqueue Type (Request Reason)： enqueue 队列的类型，大家在研究 enqueue 问题前至少搞清楚enqueue type 和enqueue mode ， 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 ,单位为ms

主要的enqueue 等待事件：reference 2.5.4

enq: TX – row lock/index contention、allocate ITL等待事件

enq: TM – contention等待事件

Oracle队列锁enq:TS,Temporary Segment (also TableSpace)

9.Undo Statistics

9.1 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	0.64	3,010	945	5	19.7/29.8	0/0	0/0/0/0/0/0

9.2 Undo Segment Stats

Undo Segment Stats

Most recent 35 Undostat rows, ordered by Time desc

End Time	Num Undo Blocks	Number of Transactions	Max Qry Len (s)	Max Tx Concy	Tun Ret (mins)	STO/ OOS	uS/uR/uU/ eS/eR/eU
23-Apr 00:51	376	1,254	943	5	30	0/0	0/0/0/0/0/0
23-Apr 00:41	33	158	341	2	20	0/0	0/0/0/0/0/0
23-Apr 00:31	25	367	943	2	30	0/0	0/0/0/0/0/0
23-Apr 00:21	32	167	342	2	20	0/0	0/0/0/0/0/0
23-Apr 00:11	52	703	945	3	30	0/0	0/0/0/0/0/0
23-Apr 00:01	123	361	343	4	20	0/0	0/0/0/0/0/0

数据来源：WRH$_UNDOSTAT，undo相关的使用信息每10分钟刷新到v$undostat中

Undo Extent有三种状态 active 、unexpired(未到期的) 、expired(期满)

active => 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 tablespace

Num 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被其他事务重用的块数目

undo自动调优介绍

Oracle 10gr2的后续版本中添加了撤销(UNDO)信息最短保留时间段自动调优的特性，不再仅仅依据参数UNDO_RETENTION的设定，其调优原则如下：

1> 当撤销表空间(UNDO TABLESPACE)大小固定，Oracle将根据表空间的大小和实际的系统负载动态调整撤销信息保存时间，该最短保存时间的具体长短基于撤销表空间大小的一定比例值公式换算后获得；它总是比设定的UNDO_RETENTION大，当撤销表空间大量空闲情况下可能远远大于UNDO_RETENTION。

2> 当撤销表空间设定为自动扩展空间情况下，Oracle将动态调整撤销信息最短保留时间为该时段最长查询时间(MAXQUERYLEN)加上300秒或参数UNDO_RETENTION间的较大者，即MAX((MAXQUERYLEN+300),UNDO_RENTION)；同样的，该最短保存时间可能远远大于设定的UNDO_RETENTION。

在自动调整情况下，实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。

在无法就撤销表空间做相应修改的情况，我们可以通过修改隐式参数” _UNDO_AUTOTUNE”为FALSE关闭该自动调优特性。以上设定生效后，V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新，且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。

10.Latch Statistics

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	121	0.00		0	0
AQ deq log cmt cbk chunk latch	8	0.00		0	0
AQ deq log statistics latch	4	0.00		0	0
AQ dequeue txn counter latch	3,136	0.00		0	0
AQ disk delete txn counter latch	4	0.00		0	0
AQ ht cmt cbk chunk latch	16	0.00		0	0
ASM db client latch	2,438	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

一般的调优原则：

1> 如果latch : cache buffers chains是 Top 5 事件，则需要考虑优化SQL减少全表扫描并减少Top buffer gets SQL语句的逻辑读

2> 如果latch : redo copy 、redo allocation(分配) 等待较多，则可以考虑增大LOG_BUFFER

3> 如果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
shared pool	285,112	356	62	306
space background task latch	6,033	64	3	61
row cache objects	715,246	29	3	26
redo allocation	14,901	15	6	9

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

only latches with sleeps are shown
ordered by name, sleeps desc

Latch Name	Where	NoWait Misses	Sleeps	Waiter Sleeps
JS slv state obj latch	jslvStateObjDel:1	0	1	1
cache buffers chains	kcbchg1: mod cr pin	0	1	0
call allocation	ksudlp: top call	0	2	1
call allocation	ksuxds	0	1	2
object queue header operation	kcbo_switch_cq	0	1	0
parameter table management	kspcpy	0	1	0
qmn task queue latch	kwqmnmvtsks: delay to ready list	0	1	0
redo allocation	kcrfw_redo_gen: redo allocation 1	0	6	0

数据来源为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 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	kkslce [KKSCHLPIN2]	15	0
Library Cache	kglhdgh1 64	4	0
Library Cache	kgllkdl1 85	3	0
Library Cache	kglhdgn2 106	2	0

Mutex是10.2.0.2以后引入的新的内存锁机制，具体对Mutex的描述见《深入理解Oracle中的Mutex》：http://www.askmaclean.com/archives/understanding-oracle-mutex.html

Mutex Type：Mutex的类型其实就是 mutex对应的客户的名字，在版本10.2中基本只有KKS使用Mutex，所以仅有3种:

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

Total Logical Reads: 382,388
Captured Segments account for 69.1% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
SYS	SYSTEM	TAB$		TABLE	30,192	7.90
SYS	SYSTEM	I_OBJ2		INDEX	17,504	4.58
SYS	SYSTEM	I_SYSAUTH1		INDEX	16,464	4.31
SYS	SYSTEM	I_OBJ1		INDEX	15,792	4.13
SYS	SYSTEM	OBJ$		TABLE	14,816	3.87

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: 605
Captured Segments account for 32.1% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Reads	%Total
SYS	SYSTEM	CCOL$		TABLE	67	11.07
SYS	SYSTEM	IDL_UB1$		TABLE	66	10.91
SYS	SYSTEM	I_OBJAUTH1		INDEX	18	2.98
SYS	SYSTEM	KOTAD$		TABLE	9	1.49
SYS	SYSTEM	ACCESS$		TABLE	8	1.32

Physical Reads: 该数据段上发生过的物理读，单位为块数*次数

%Total : 占总的物理读的百分比， (当前对象上发生过的逻辑读/ Total DB 逻辑读)

11.3 Segments by Physical Read Requests

Segments by Physical Read Requests

Total Physical Read Requests: 521
Captured Segments account for 27.6% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Phys Read Requests	%Total
SYS	SYSTEM	IDL_UB1$		TABLE	66	12.67
SYS	SYSTEM	I_OBJAUTH1		INDEX	18	3.45
SYS	SYSTEM	CCOL$		TABLE	17	3.26
SYS	SYSTEM	KOTAD$		TABLE	9	1.73
SYS	SYSTEM	ACCESS$		TABLE	8	1.54

Phys Read Requests ：物理读的申请次数

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

11.4 Segments by UnOptimized Reads

Segments by UnOptimized Reads

Total UnOptimized Read Requests: 521
Captured Segments account for 27.6% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	UnOptimized Reads	%Total
SYS	SYSTEM	IDL_UB1$		TABLE	66	12.67
SYS	SYSTEM	I_OBJAUTH1		INDEX	18	3.45
SYS	SYSTEM	CCOL$		TABLE	17	3.26
SYS	SYSTEM	KOTAD$		TABLE	9	1.73
SYS	SYSTEM	ACCESS$		TABLE	8	1.54

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. Optimized

Owner Name Object Name Name Type Reads %Total

---------- ---------- -------------------- ---------- ----- ------------ -------

CONTENT_OW DATA_TS MZ_CONTENT_PROVIDER_ TABLE 2,995,766 9.04

CONTENT_OW DATA_TS MZ_PRODUCT_ATTRIBUTE TABLE 1,489,000 4.50

CONTENT_OW DATA_TS MZ_PRODUCT TABLE 1,276,350 3.85

CONTENT_OW DATA_TS MZ_AUDIO_FILE TABLE 890,775 2.69

CONTENT_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 Writes

Total Direct Physical Writes: 23
Captured Segments account for 95.7% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Direct Writes	%Total
SYS	SYSAUX	SYS_LOB0000006417C00038$$		LOB	12	52.17
SYS	SYSAUX	SYS_LOB0000006409C00004$$		LOB	7	30.43
HCT	USERS	I_TEMP_TYPECHANGE_N001		INDEX	3	13.04

Direct reads 直接路径物理读，单位为块数*次数

%Total (该段上发生的direct path reads /Total physical reads direct )

11.7 Segments by Physical Writes

Segments by Physical Writes

Total Physical Writes: 4,366
Captured Segments account for 52.7% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Writes	%Total
HCT	USERS	TEMP_LOG		TABLE	1,218	27.90
HCT	USERS	TEMP_STOCKMIDID		TABLE	256	5.86
SYS	SYSTEM	TAB$		TABLE	168	3.85
SYSMAN	SYSAUX	MGMT_METRICS_RAW_PK		INDEX	117	2.68
SYS	SYSTEM	IDL_UB1$		TABLE	44	1.01

Physical Writes ，物理写单位为块数*次数

Total % (该段上发生的物理写 /Total physical writes )

11.8 Segments by Physical Write Requests

Segments by Physical Write Requests

Total Physical Write Requestss: 1,485
Captured Segments account for 31.9% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Phys Write Requests	%Total
SYS	SYSTEM	TAB$		TABLE	103	6.94
HCT	USERS	TEMP_LOG		TABLE	80	5.39
SYS	SYSTEM	IDL_UB1$		TABLE	40	2.69
SYSMAN	SYSAUX	MGMT_METRICS_RAW_PK		INDEX	26	1.75
SYSMAN	SYSAUX	MGMT_SYSTEM_PERF_LOG_IDX_01		INDEX	22	1.48

Phys Write Requests 物理写的请求次数，单位为次数

%Total (该段上发生的物理写请求次数 /physical write IO requests )

11.9 Segments by Direct Physical Writes

Segments by Direct Physical Writes

Total Direct Physical Writes: 23
Captured Segments account for 95.7% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Direct Writes	%Total
SYS	SYSAUX	SYS_LOB0000006417C00038$$		LOB	12	52.17
SYS	SYSAUX	SYS_LOB0000006409C00004$$		LOB	7	30.43
HCT	USERS	I_TEMP_TYPECHANGE_N001		INDEX	3	13.04

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

11.10 Segments by Table Scans

Segments by Table Scans

Total Table Scans: 39
Captured Segments account for 64.1% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Table Scans	%Total
SYS	SYSTEM	I_OBJ1		INDEX	24	61.54
SYS	SYSTEM	I_OBJ2		INDEX	1	2.56

Table Scans 来源为表dba_hist_seg_stat中列table_scans_delta 不过这个指标并不十分精确

11.11 Segments by DB Blocks Changes

Segments by DB Blocks Changes

% of Capture shows % of DB Block Changes for each top segment compared
with total DB Block Changes for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	DB Block Changes	% of Capture
HCT	USERS	TEMP_LOG		TABLE	7,968	42.56
SYSMAN	SYSAUX	MGMT_CURRENT_METRICS_PK		INDEX	1,808	9.66
HCT	USERS	TEMP_STOCKMIDID		TABLE	1,424	7.61
SYSMAN	SYSAUX	MGMT_METRICS_RAW_PK		INDEX	1,120	5.98
SYS	SYSTEM	TAB$		TABLE	944	5.04

DB Block Changes ，单位为块数*次数

%Total : (该段上发生block changes / db block changes )

11.12 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 % of

Owner Name Object Name Name Type Waits Capture

---------- ---------- -------------------- ---------- ----- ------------ -------

CONTENT_OW LOB_8K_TS MZ_ASSET_WORK_EVENT_ INDEX 72,005 43.86

CONTENT_OW LOB_8K_TS MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX 13,795 8.40

CONTENT_OW LOB_8K_TS MZ_CS_WORK_INFO_PART _2013_5_35 INDEX 12,383 7.54

CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 8,937 5.44

CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 8,531 5.20

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

11.13 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 % of

Owner Name Object Name Name Type Waits Capture

---------- ---------- -------------------- ---------- ----- ------------ -------

CONTENT_OW LOB_8K_TS MZ_ASSET_WORK_EVENT_ INDEX 95 30.16

CONTENT_OW LOB_8K_TS MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX 48 15.24

CONTENT_OW LOB_8K_TS MZ_CS_WORK_INFO_PART _2013_5_35 INDEX 21 6.67

CONTENT_OW INDEX_TS MZ_SALABLE_FIRST_AVA INDEX 21 6.67

CONTENT_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.html

ITL Waits 等待 ITL 的次数，数据来源为 dba_hist_seg_stat.itl_waits_delta

11.14 Segments by Buffer Busy Waits

Segments by Buffer Busy Waits

% of Capture shows % of Buffer Busy Waits for each top segment compared
with total Buffer Busy Waits for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Buffer Busy Waits	% of Capture
SYS	SYSTEM	JOB$		TABLE	1	100.00

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 % of

Owner Name Object Name Name Type Busy Capture

---------- ---------- -------------------- ---------- ----- ------------ -------

CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3 INDEX 2,135,528 50.07

CONTENT_OW DATA_TS MZ_CONTENT_PROVIDER_ TABLE 652,900 15.31

CONTENT_OW LOB_8K_TS MZ_ASSET_WORK_EVENT_ INDEX 552,161 12.95

CONTENT_OW LOB_8K_TS MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX 113,042 2.65

CONTENT_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.16 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. Blocks

Owner Name Object Name Name Type Received %Total

---------- ---------- -------------------- ---------- ----- ------------ -------

CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 69,100 9.06

CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 44,491 5.83

CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 36,830 4.83

CONTENT_OW DATA_TS MZ_PODCAST TABLE 36,632 4.80

CONTENT_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.17 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. Blocks

Owner Name Object Name Name Type Received %Total

---------- ---------- -------------------- ---------- ----- ------------ -------

CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3 INDEX 56,287 7.99

CONTENT_OW INDEX_TS MZ_AM_REQUEST_IX3A INDEX 45,139 6.41

CONTENT_OW DATA_TS MZ_AM_REQUEST TABLE 40,350 5.73

CONTENT_OW DATA_TS MZ_CS_WORK_PENDING_R TABLE 22,808 3.24

CONTENT_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)

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	1.54	0		2	1
dc_constraints	4	75.00	0		4	1
dc_files	4	100.00	0		0	0
dc_global_oids	4,329	1.78	0		0	31
dc_histogram_data	8,154	9.33	0		0	455
dc_histogram_defs	85,893	7.09	0		236	1,702
dc_object_grants	1,937	6.14	0		0	114
dc_objects	41,399	6.55	0		317	1,161
dc_profiles	296	0.34	0		0	2
dc_rollback_segments	880	0.00	0		0	22
dc_segments	8,603	10.18	0		104	328
dc_sequences	15	100.00	0		15	1
dc_tablespaces	28,348	0.01	0		0	7
dc_users	60,322	0.19	0		3	106
global database name	2,629	0.00	0		0	1
outstanding_alerts	22	40.91	0		4	4
sch_lj_oids	4	100.00	0		0	0

Dictionary Cache 字典缓存也叫row cache 数据来源为dba_hist_rowcache_summary

Cache 字典缓存类名kqrstcid <=> kqrsttxt cid=3(dc_rollback_segments)

Get Requests 申请获取该数据字典缓存对象的次数 gets

Miss : 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/SCANS

Mod 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 GES

Cache Requests Conflicts Releases

------------------------- ------------ ------------ ------------

dc_awr_control 14 2 0

dc_global_oids 88 0 102

dc_histogram_defs 43,518 0 43,521

dc_objects 21,608 17 21,176

dc_profiles 1 0 1

dc_segments 24,974 14 24,428

dc_sequences 25,178 10,644 347

dc_table_scns 2 0 2

dc_tablespaces 165 0 166

dc_users 119 0 119

outstanding_alerts 478 8 250

sch_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

"Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
ACCOUNT_STATUS	175	5.71	0		0	0
APP CONTEXT	3	0.00	21	9.52	2	0
BODY	4,562	1.10	13,767	1.30	105	10
CLUSTER	364	2.20	364	2.20	0	0
DBLINK	194	2.58	0		0	0
EDITION	149	1.34	280	2.50	1	0
INDEX	64	43.75	58	60.34	1	0
OBJECT ID	80	100.00	0		0	0
QUEUE	93	0.00	10,357	0.04	4	0
RULESET	0		4	50.00	2	0
SCHEMA	2,144	0.56	0		0	1
SQL AREA	13,838	38.47	97,240	7.43	900	472
SQL AREA BUILD	2,355	92.48	0		0	0
SQL AREA STATS	2,301	87.22	2,301	87.22	0	0
SUBSCRIPTION	6	0.00	6	33.33	2	0
TABLE/PROCEDURE	15,977	12.20	43,306	10.97	1,663	8
TRIGGER	1,916	1.98	2,180	5.05	20	0

NameSpace library cache 的命名空间

GETS Requests 该命名空间所包含对象的library cache lock被申请的次数

GETHITS 对象的 library cache handle(手柄) 正好在内存中被找到的次数

Pct Misses : ( 1- ( GETHITS /GETS Requests)) *100

Pin Requests 该命名空间所包含对象上pin被申请的次数

PINHITS 要pin的对象的heap metadata正好在shared pool中的次数

Pct Miss ( 1- ( PINHITS /Pin Requests)) *100

Reloads 指从object handle 被重建开始不是第一次PIN该对象的PIN ，且该次PIN要求对象从磁盘上读取加载的次数 ;Reloads值较高的情况建议增大shared_pool_size

INVALI DATIONS (建议) 由于以来对象被修改导致该命名空间所包含对象被标记为无效的次数

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 0

BODY 0 15,497 15,497 0 0

CLUSTER 321 321 321 0 0

DBLINK 9,212 0 0 0 0

EDITION 4,431 4,431 4,431 0 0

HINTSET OBJECT 1,027 1,027 1,027 0 0

INDEX 792 792 792 0 0

QUEUE 8 1,733 1,733 0 0

RULESET 0 8 8 0 0

SCHEMA 4,226 0 0 0 0

TABLE/PROCEDURE 373,163 704,816 704,816 0 0

TRIGGER 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(总结)

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

	Category	Alloc (MB)	Used (MB)	Avg Alloc (MB)	Std Dev Alloc (MB)	Max Alloc (MB)	Hist Max Alloc (MB)	Num Proc	Num Alloc
B	Other	87.96		2.31	2.36	12	24	38	38
B	Freeable	22.31	0.00	1.17	0.93	4		19	19
B	PL/SQL	16.06	14.37	0.45	0.66	2	2	36	36
B	SQL	1.85	0.21	0.07	0.06	0	7	25	19
E	Other	90.69		2.27	2.34	12	25	40	40
E	Freeable	20.69	0.00	1.03	0.72	3		20	20
E	PL/SQL	15.99	13.95	0.42	0.65	2	2	38	38
E	SQL	1.97	0.21	0.08	0.06	0	7	26	21

数据来源为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 time

Hist Max Alloc (MB) MAX_ALLOCATED_MAX: 目前仍链接着的进程该分类最大分配过的内存量：Hist Max Alloc is the Historical Max Allocation for still-connected processes

Num Proc num_processes 进程数目

Num Alloc NON_ZERO_ALLOCS 分配了该类型内存的进程数目

15.SGA Memory Summary

15.1 SGA Memory Summary

SGA Memory Summary

SGA regions	Begin Size (Bytes)	End Size (Bytes) (if different)
Database Buffers	381,681,664	373,293,056
Fixed Size	2,259,200
Redo Buffers	5,931,008
Variable Size	587,204,352	595,592,960

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

15.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	4.00	4.00	0.00
large	PX msg pool	0.47	0.47	0.00
large	free memory	7.53	7.53	0.00
shared	ASH buffers	4.00	4.00	0.00
shared	FileOpenBlock	1.88	1.88	0.00
shared	KCB Table Scan Buffer	3.80	3.80	0.00
	buffer_cache	364.00	356.00	-2.20
	fixed_sga	2.15	2.15	0.00
	log_buffer	5.66	5.66	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为宜

16.Streams统计

Streams CPU/IO Usage

Streams processes ordered by CPU Time, descending

Session Type	First Logon	CPU time(s)	User IO Wait time(s)	SYS IO Wait time(s)
QMON Slaves	0422 22:11:41	0.71	0.00	0.00
QMON Coordinator	0422 22:11:31	0.12	0.00	0.00

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 Pause

Capture 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收到的延迟

17.Resource Limit Stats

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 Initial

Resource Name Utilization Utilization Allocation Limit

------------------------------ ------------ ------------ ---------- ----------

ges_procs 2,612 8,007 10003 10003

processes 2,615 8,011 10000 10000

数据源于dba_hist_resource_limit

注意这里仅列出当前使用或最大使用量>80% *最大限制的资源名，如果没有列在这里则说明资源使用量安全

Current Utilization(利用) 当前对该资源(包括Enqueue Resource、Lock和processes)的使用量

Maximum Utilization 从最近一次实例启动到现在该资源的最大使用量

Initial Allocation 初始分配值，一般等于参数文件中指定的值

Limit 实际上限值

18.init.ora Parameters

init.ora Parameters

Parameter Name	Begin value	End value (if different)
audit_file_dest	/u01/app/oracle/admin/orcl/adump
audit_trail	DB
compatible	11.2.0.4.0
control_files	/u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
db_block_size	8192
db_domain	test.com
db_name	orcl
db_recovery_file_dest	/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	4385144832
diagnostic_dest	/u01/app/oracle
dispatchers	(PROTOCOL=TCP) (SERVICE=testXDB)
memory_target	981467136
open_cursors	300
plsql_warnings	DISABLE:ALL
processes	150
remote_login_passwordfile	EXCLUSIVE
undo_tablespace	UNDOTBS1

Parameter Name 参数名

Begin value 开始快照时的参数值

End value 结束快照时的参数值 (仅在发生变化时打印)

19.Global Messaging Statistics

Global Messaging Statistics DB/Inst: MAC/MAC2 Snaps: 70719-70723

Statistic Total per Second per Trans

--------------------------------- ---------------- ------------ ------------

acks for commit broadcast(actual) 53,705 14.9 0.2

acks for commit broadcast(logical 311,182 86.1 1.3

broadcast msgs on commit(actual) 317,082 87.7 1.3

broadcast msgs on commit(logical) 317,082 87.7 1.3

broadcast msgs on commit(wasted) 263,332 72.9 1.1

dynamically allocated gcs resourc 0 0.0 0.0

dynamically allocated gcs shadows 0 0.0 0.0

flow control messages received 267 0.1 0.0

flow control messages sent 127 0.0 0.0

gcs apply delta 0 0.0 0.0

gcs 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-70723

Statistic Total

------------------------------ ------------------

CR Block Requests 403,703

CURRENT Block Requests 444,896

Data Block Requests 403,705

Undo Block Requests 94,336

TX Block Requests 307,896

Current Results 652,746

Private results 21,057

Zero Results 104,720

Disk Read Results 69,418

Fail Results 508

Fairness Down Converts 102,844

Fairness Clears 15,207

Free GC Elements 0

Flushes 105,052

Flushes Queued 0

Flush Queue Full 0

Flush Max Time (us) 0

Light Works 71,793

Errors 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 Operations

Statistic Total % <1ms % <10ms % <100ms % <1s % <10s

---------- ------------ -------- -------- -------- -------- --------

Pins 73,018 12.27 75.96 8.49 2.21 1.08

Flushes 79,336 5.98 50.17 14.45 19.45 9.95

Writes 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存在问题，延迟较高

22.Global Cache Transfer Stats

Global Cache Transfer Stats DB/Inst: MAC/MAC2 Snaps: 70719-70723

-> Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays

-> Busy (Busy) - Block Transfer impacted by Remote Contention

-> Congested (Congst) - Block Transfer impacted by Remote System Load

-> ordered by CR + Current Blocks Received desc

CR Current

----------------------------- -----------------------------

Inst Block Blocks % % % Blocks % % %

No Class Received Immed Busy Congst Received Immed Busy Congst

---- ----------- -------- ------ ------ ------ -------- ------ ------ ------

1 data block 133,187 76.3 22.6 1.1 233,138 75.2 23.0 1.7

4 data block 143,165 74.1 24.9 1.0 213,204 76.6 21.8 1.6

3 data block 122,761 75.9 23.0 1.1 220,023 77.7 21.0 1.3

1 undo header 104,219 95.7 3.2 1.1 941 93.4 5.8 .7

4 undo header 95,823 95.2 3.7 1.1 809 93.4 5.3 1.2

3 undo header 95,592 95.6 3.3 1.1 912 94.6 4.5 .9

1 undo block 25,002 95.8 3.4 .9 0 N/A N/A N/A

4 undo block 23,303 96.0 3.1 .9 0 N/A N/A N/A

3 undo block 21,672 95.4 3.7 .9 0 N/A N/A N/A

1 Others 1,909 92.0 6.8 1.2 6,057 89.6 8.9 1.5

4 Others 1,736 92.4 6.1 1.5 5,841 88.8 9.9 1.3

3 Others 1,500 92.4 5.9 1.7 4,405 87.7 10.8 1.6

数据来源DBA_HIST_INST_CACHE_TRANSFER

Inst No 节点号

Block Class 块的类型

CR Blocks Received(收到) 该节点上该类型CR 块的接收数量

CR Immed %: CR块请求立即接收到的比例

CR Busy%：CR块请求由于远端争用而没有立即接收到的比例

CR Congst%: CR块请求由于远端负载高而没有立即接收到的比例

Current Blocks Received 该节点上该类型Current 块的接收数量

Current Immed %: Current块请求立即接收到的比例

Current Busy%：Current块请求由于远端争用而没有立即接收到的比例

Current Congst%: Current块请求由于远端负载高而没有立即接收到的比例

Congst%的比例应当非常低不高于2%， Busy%很大程度受到IO的影响，如果超过10% 一般会有严重的gc buffer busy acquire(获得)/release

Memory Configuration and Use http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm

Library Cache Hit (%) http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_instance_efficiency_libcache_hit_pct.html

Oracle? Database Performance Tuning Guide 12c Release 1 (12.1)

How to Interpret the “SQL ordered by Physical Reads (UnOptimized)” Section in AWR Reports (11.2 onwards) [ID 1466035.1]

reference Maclean_liu http://www.askmaclean.com/archives/performance-tuning-oracle-awr.html

http://www.askmaclean.com/archives/oracle-latch.html

ORA-4031 error：http://www.askmaclean.com/archives/ora-4031-common-analysisdiagnostic-scripts.html

Statistics Descriptions http://docs.oracle.com/cd/B19306_01/server.102/b14237/stats002.htm

Memory Configuration and Use http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm

Library Cache Hit (%)

http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_instance_efficiency_libcache_hit_pct.html

Oracle? Database Performance Tuning Guide 12c Release 1 (12.1)

How to Interpret the “SQL ordered by Physical Reads (UnOptimized)” Section in AWR Reports (11.2 onwards) [ID 1466035.1]

ITPub博客

AWR报告指标解析——reference Maclean_liu

1.什么是AWR

1.1 AWR (Automatic Workload Repository)

1.2 谁维护AWR?

1.3 AWR小技巧

2.WORKLOAD REPOSITORY report for

2.1 内存参数大小

2.2 Load Profile

2.3 Instance Efficiency Percentages (Target 100%)

2.3.1 Buffer Nowait %

2.3.2 buffer HIT%

2.3.3 Redo nowait%

2.3.4 In-memory Sort%

2.3.5 Library Hit%

2.3.6 Soft Parse%

2.3.7 Execute to Parse%

2.3.8 Latch Hit%

2.3.9 Parse CPU To Parse Elapsd

2.3.10 %Non-Parse CPU

2.4 Shared Pool Statistics

2.4.1 % SQL with executions>1

2.4.2 Memory Usage %

2.4.3 % Memory for SQL w/exec>1

2.5 Top5 Timed Events

2.5.1 db file scattered read

2.5.2 db file sequential read

2.5.3 latch free

2.5.4 enq:XX

1> 你有多了解Oracle Enqueue lock队列锁机制？

2> Oracle队列锁: Enqueue HW

3> Oracle队列锁enq:US,Undo Segment

4> enq: TX – row lock/index contention、allocate ITL等待事件

5> enq: TT – contention等待事件

6> Oracle队列锁enq:TS,Temporary Segment (also TableSpace)

7> enq: JI – contention等待事件

8> enq: US – contention等待事件

9> enq: TM – contention等待事件

10> enq: RO fast object reuse等待事件

11> enq: HW – contention等待事件

2.5.5 free buffer waits

2.5.6 buffer busy wait/ read by other session

2.5.7 write complete waits

2.5.8 control file parallel write

2.5.9 log file sync

2.6 Time Model Statistics

2.7 Foreground Wait Class

2.8 Foreground Wait Events

2.9 Background Wait Events

2.10 Operating System Statistics

2.11 Service Wait Class Stats

2.12 Host CPU

2.13 Instance CPU

3.TOP SQL

3.1 SQL ordered by Elapsed Time按照SQL消耗的时间来排列TOP SQL

3.2 SQL ordered by CPU Time

3.3 Buffer Gets SQL ordered by Gets

3.4 Physical Reads SQL ordered by Reads

3.5 Executions SQL ordered by Executions

3.6 Parse Calls SQL ordered by Parse Calls

3.7 SQL ordered by Sharable Memory

3.8 SQL ordered by Version Count

3.9 Cluster Wait Time SQL ordered by Cluster Wait Time

4.Instance Activity Stats

5.IO统计

5.1 Tablespace IO Stats 基于表空间分组的IO信息

5.2 File I/O

6.缓冲池统计 Buffer Pool Statistics

7.Advisory Statistics

7.1 CheckpointActivity&InstanceRecovery Stats

7.2 Buffer Pool Advisory 缓冲池建议

7.3 PGA Aggr Summary

7.4 PGA Aggr Target Stats

7.5 PGA Aggr Target Histogram

7.6 PGA Memory Advisory

7.7 Shared Pool Advisory

7.8 SGA Target Advisory

7.9 Streams Pool Advisory

7.10 Java Pool Advisory

8.Wait Statistics