一. 诊断工具
1.1 OS性能诊断
AIX:
Linux:
1.2 OS性能套件
1.2.1 nmon
1.2.2 OSWatcher
1.3 数据库
1.3.1 AWR报告
13.2 ASH报告
13.3 V$ACTIVE_SESSION_HISTORY/DBA_HIST_ACTIVE_SESS_HISTORY
13.5 HangAnalyze
13.6 SystemState Dump
二 内存问题分析
三 CPU问题分析
四 系统IO问题分析
五 数据库资源等待分析
5.1 buffer busy waits
5.2 db file scatter read
5.3 db file scatter read
5.4 direct path read
5.5 enq: TM – contention
5.6 enq: TX – index contention
5.7 enq: TX – index contention
5.8 free buffer gets
5.9 global cache cr request
5.10 latch: cache buffers chains
5.11 latch: library cache
5.12 latch: shared pool
5.13 library cache lock
5.14 library cache pin
5.16 cursor pin: S
5.17 cursor pin: S wait on X
5.18 log buffer space
5.19 log file sync
5.20 read by other session
5.21 gc buffer busy
5.22 gc cr request
5.23 gc cr block 2-way
5.24 gc current block busy
5.25 gc current block 2-way
5.26 gc current block busy
一. 诊断工具
1.1 OS性能诊断
AIX:
--------------------------------------------------------------------------------
1. 查看逻辑CPU个数
# pmcycles -m
2. 查看物理CPU个数
# prtconf|grep Processors
3. 查看CPU属性
# lsattr -E -l proc0
4. CPU性能
# vmstat 2 20
r: 取样期间可运行的内核线程的平均数，包括正在运行的线程和指准备运行但尚在等待运行的进程。
1) 如果在processes中运行的序列(process r)是连续的大于在系统中的CPU的个数表示系统现在运行比较慢,有多数的进程等待CPU。
2) 如果r的输出数大于系统中可用CPU个数的4倍的话,则系统面临着CPU短缺的问题,或者是CPU的速率过低,系统中有多数的进程在等待CPU,造成系统中进程运行过慢。
3) 如果空闲时间(cpu id)持续为0并且系统时间(cpu sy)是用户时间的两倍(cpu us)系统则面临着CPU资源的短缺。
b：表示每秒VMM等待队列中的内核线程平均数(等待资源或I/O)，这里参考值为2，大于2表示被阻塞列线程数目太多。
CPU: 使用时间百分比细目分类
us 用户进程CPU占用,一个 UNIX 进程可以在用户方式下执行，也可以在系统（内核）方式下执行。当在用户方式下时，进程在它自己的应用程序代码中执行，不需要内核资源来进行计算、管理内存或设置变量。
sy 系统进程CPU占用 ,这包括内核进程（kprocs）和其它需要访问内核资源的进程所消耗的 CPU 资源。如果一个进程需要内核资源，它必须执行一个系统调用，并由此切换到系统方式从而使该资源可用。例如，对一个文件的读或写操作需要内核资源来打开文件、寻找特定的位置，以及读或写数据，除非使用内存映射文件。这里us + sy的参考值为80%，如果us+sy 大于 80%说明可能存在CPU不足。
id 系统空闲 CPU idle time
wa 表示IO等待时间，即系统等待未完成的 disk/NFS I/O 请求期间的CPU 空闲时间,
如果us与sy之和持续超过90%时，CPU出现了瓶颈。
如果wa长期很高>50，则表示IO太忙，具体看是应用IO多，还是交换分页多，如果是后者，则显示内存不足；如果是前者，则应关注应用的IO性能状况，优化应用与磁盘设备
pc 消耗物理处理器的数目。只在使用共享处理器运行的分区显示（只在微分区环境中显示）
ec 消耗授权容量的百分比。只在使用共享处理器运行的分区显示（只在微分区环境中显示）
###内存 -- TODO
# topas
a 键可显示所有正在受监视的变量子节（CPU、网络、磁盘、WLM、过程）。按 a 键总使 topas 命令返回到初始主显示屏幕。
c 键可使 CPU 子节在累积报告、关闭和最忙 CPU 列表这三者之间进行切换。所显示的最忙 CPU 数目将取决于屏幕上的可用空间。
d 键可使磁盘子节在最忙磁盘列表、关闭和系统全部磁盘活动的报告这三者之间进行切换。所显示的最忙磁盘数目将取决于屏幕上的可用空间。
h 显示帮助屏幕。
n 键可使网络接口子节在最忙接口列表、关闭和系统全部网络活动的报告这三者之间进行切换。所显示的最忙接口数目将取决于屏幕上的可用空间。
w 键可在打开与关闭工作负载管理（WLM）类子节之间进行切换。所显示的最忙 WLM 类数目取决于屏幕上的可用空间。
p 键可在打开与关闭热进程子节之间进行切换。所显示的最忙进程数目取决于屏幕上的可用空间。
大写 P 键用全屏幕进程显示来替换缺省显示。有关运行在系统上的进程，此显示提供了比主显示的进程部分更详细的的信息。再次按 P 键时，就会切换回缺省主显示。
W 大写 W 键用全屏幕 WLM 类显示来替换缺省显示。此显示提供了有关 WLM 类和指定给类的进程的更详细信息。再次按 W 键时，就会切换回缺省主显示。
f 将光标移动到一个 WLM 类上，并按 f 键就可在 WLM 屏幕底部显示该类中顶部进程的列表。该键功能只有 topas 在全屏幕 WLM 显示（通过使用 W 键或 -W 标志）时才有效。
q 退出程序。
r 刷新显示。
箭头键与跳格键 主显示中的各个子节，诸如 CPU、网络、磁盘、WLM 类、全屏幕 WLM 与进程显示的子节可按不同标准进行排序。将光标移动到一栏上，然后会按该栏进行排序。各项总是从最高值到最低值排序。使用跳格 键或箭头键来移动光标。排序只对 128 个磁盘和 16 个网络适配器有效。
5. IO性能
# iostat -T 2 10
% tm_act 物理磁盘活动的时间百分比
KBPS 某块磁盘传输数据的总量（读或写）
tps 某块物理磁盘每秒钟 IO 传输的数量
Kb_read 从磁盘上读取数据的总量
Kb_wrtn 写入磁盘的数据总量
# iostat -m -D
# iostat -A #异步IO
avgc 指定时间间隔内，每秒平均全局 non-fast 路径 AIO 请求计数
avfc 指定埋单间隔内，每秒平均全局 AIO fast 路径请求计数
maxg 自最后一次取值以来，最大的全局 non-fast 路径 AIO 请求计数
maxf 自最后一次取值以来，最大 fast 路径请求计数
maxr 容许的最大 AIO 请求。这是 AIO 设备的 maxreqs 属性
6. 网络性能
# netstat -s -f inet
#查看两个时间点内各个统计信息的变化。
Linux:
--------------------------------------------------------------------------------
1. 查看逻辑CPU个数
# cat /proc/cpuinfo |grep "processor"|wc -l
2. 查看物理CPU个数
# cat /proc/cpuinfo |grep "physical id"|sort |uniq|wc -l
3. 查看CPU属性
# cat /proc/cpuinfo |grep MHz|uniq
4. CPU性能
# vmstat 2 20
同Linux
# top
Ctrl+L 擦除并且重写屏幕。
h或者? 显示帮助画面，给出一些简短的命令总结说明。
k 终止一个进程。系统将提示用户输入需要终止的进程PID，以及需要发送给该进程什么样的信号。一般的终止进程可以使用15信号；如果不能正常结束那就使用信号9强制结束该进程。默认值是信号15。在安全模式中此命令被屏蔽。
i 忽略闲置和僵死进程。这是一个开关式命令。
q 退出程序。
r 重新安排一个进程的优先级别。系统提示用户输入需要改变的进程PID以及需要设置的进程优先级值。输入一个正值将使优先级降低，反之则可以使该进程拥有更高的优先权。默认值是10。
S 切换到累计模式。
s 改变两次刷新之间的延迟时间。系统将提示用户输入新的时间，单位为s。如果有小数，就换算成m s。输入0值则系统将不断刷新，默认值是5 s。需要注意的是如果设置太小的时间，很可能会引起不断刷新，从而根本来不及看清显示的情况，而且系统负载也会大大增加。
f或者F 从当前显示中添加或者删除项目。
o或者O 改变显示项目的顺序。
l 切换显示平均负载和启动时间信息。
m 切换显示内存信息。
t 切换显示进程和CPU状态信息。
c 切换显示命令名称和完整命令行。
M 根据驻留内存大小进行排序。
P 根据CPU使用百分比大小进行排序。
T 根据时间/累计时间进行排序。
W 将当前设置写入~/.toprc文件中。这是写top配置文件的推荐方法。
5. IO性能
# iostat -x -k -d 1
rrqm/s: 每秒对该设备的读请求被合并次数，文件系统会对读取同块(block)的请求进行合并
wrqm/s: 每秒对该设备的写请求被合并次数
r/s: 每秒完成的读次数
w/s: 每秒完成的写次数
rkB/s: 每秒读数据量(kB为单位)
wkB/s: 每秒写数据量(kB为单位)
avgrq-sz:平均每次IO操作的数据量(扇区数为单位)
avgqu-sz: 平均等待处理的IO请求队列长度
await: 平均每次IO请求等待时间(包括等待时间和处理时间，毫秒为单位)
svctm: 平均每次IO请求的处理时间(毫秒为单位)
%util: 采用周期内用于IO操作的时间比率，即IO队列非空的时间比率
6. 网络性能
# netstat -s -f inet
#查看两个时间点内各个统计信息的变化。
1.2 OS性能套件
1.2.1 nmon
运行:
# ./nmon
命令:
c 可显示CPU的信息
m 对应内存
n 对应网络
d 可以查看磁盘信息
t 可以查看系统的进程信息
数据采集:
# ./ nmon -f -t -s5 -c 40
每5秒一个采样，采集40次，共采信200秒。
1.2.2 OSWatcher
下载地址: OSWatcher (Includes: [Video]) (文档 ID 301137.1)
安装:
tar xvf oswbb.tar
启动：
./startOSWbb.sh #默认30秒收集一次，保留48小时
./startOSWbb.sh 60 10 gzip #60秒收集一次，保留10小时，结果生成成archive 目录下，以gzip的方式压缩.
./startOSWbb 60 48 NONE #60秒收集一次，保留48小时，不压缩
OSW会生成以下几种类型的监控日志
oswiostat
oswmpstat
oswnetstat
oswprvtnet
oswifconfig
oswps
oswtop
oswvmstat
oswiostat
字段描述:
r/s 平均每秒读的次数
w/s 平均每秒写的次数
kr/s 平均每秒读的字节数(KB)
kw/s 平均每秒写的字节数(KB)
wait 等待IO服务的平均(queue length)
actv 平均活动服务数
wsvc_t 等待队列中平均服务时间(ms)
asvc_t 平均服务时间(ms)
%w 等待IO的百分比
%b 磁盘忙的比例
device 设备名称
oswvmstat
字段描述:
Field Description
PROCS
r 处于等待状态的任务数
b 被中断，并且处于sleep模式的process数
w 被内存子系统swap出去的process数
MEMORY
swap 目前可用的swap空间
PAGE
re page reclaims
mf minor faults
pi paged in的字节数(KB)
po paged out的字节数(KB)
fr 释放的字节数(KB)
de anticipated short-term memory shortfall (Kbytes)
sr pages scanned by clock algorithm
DISK
Bi 每秒发送到IO设备上的Block数
FAULTS
In 每秒CPU中断数
Sy System calls
Cs 每秒内核上下文切换次数
CPU
Us 用户进程消耗的CPU cycles数
Sy 系统进程消耗的CPU cycles数
Id 空闲百分比
1.3 数据库
1.3.1 AWR报告
@?/rdbms/admin/awrrpt.sql
Specify Type: html
Num_days:
Begin_snapshot:
End_snapshot:
Report name:
13.2 ASH报告
SQL > @?/rdbms/admin/ashrpt.sql
13.3 V$ACTIVE_SESSION_HISTORY/DBA_HIST_ACTIVE_SESS_HISTORY
1. 转储ash表
SQL> create table t_ash as select * from v$active_session_history
# exp username/password file=t_ash.dmp
2. 常用ash表查询
查询在某个时段单次执行超过两秒的前10个SQL
SQL> select * from
(
select inst_id,session_id,session_serial#,sql_id,sql_plan_hash_value,SQL_CHILD_NUMBER,sql_exec_id,count(*) sample_count
from gv$active_session_history
where sample_time between to_date('&sdate','yyyy-mm-dd hh24:mi:ss') and to_date('&edate','yyyy-mm-dd hh24:mi:ss')
and
sql_id is not null
group by inst_id,session_id,session_serial#,sql_id,sql_plan_hash_value,SQL_CHILD_NUMBER,sql_exec_id
having count(*)>2 order by count(*) desc)
where rownum<11;
查询某个时段某个事件持续等待超过2秒（不会超时的等待事件）
SQL> select * from (
select inst_id,session_id,session_serial#,event,seq#,count(*) from gv$active_session_history
where sample_time between to_date('&sdate','yyyy-mm-dd hh24:mi:ss') and to_date('&edate','yyyy-mm-dd hh24:mi:ss') and event is not null
group by inst_id,session_id,session_serial#,event,seq#
having count(*) >2
order by count(*)
) where rownum<11;
查询某个时间点Session的阻塞关系
SQL> select level,decode(connect_by_isleaf,1,'root','') isleaf,INST_ID,SESSION_ID,SESSION_SERIAL#,event,blocking_session
from gv$active_session_history
where sample_id=500132
start with blocking_session is not null
connect by inst_id = prior BLOCKING_INST_ID
and session_id = prior BLOCKING_SESSION
and session_serial# = prior BLOCKING_SESSION_SERIAL#;
13.4 V$SESSION
查询Session间的阻塞关系
SQL> select level,decode(connect_by_isleaf,1,'root','') isleaf,INST_ID,SID,SERIAL#,event,blocking_session
from gv$session
start with blocking_session is not null
connect by inst_id = prior BLOCKING_INSTANCE
and sid= prior BLOCKING_SESSION;
取得某个session的OS PID
SQL> select spid from v$process where addr=(select paddr from v$session where sid=&sid);
13.5 HangAnalyze
SQL>sqlplus -prelim / as sysdba
SQL>oradebug setmypid
SQL>oradebug unlimit;
SQL>oradebug dump hanganalyze 3
--等待30秒钟
SQL>oradebug dump hanganalyze 3
--等待30秒钟
SQL>oradebug dump hanganalyze 3
13.6 SystemState Dump
sqlplus -prelim / as sysdba
SQL>oradebug setmypid
SQL>oradebug unlimit;
SQL>oradebug dump systemstate 266
--等待30秒钟
SQL>oradebug dump systemstate 266
--等待30秒钟
SQL>oradebug dump systemstate 266
13.7 10046 trace
收集某个session的10046 trace
sqlplus / as sysdba
SQL> oradebug setospid &ospid
SQL> oradebug unlimit
SQL> oradebug session_event 10046 trace name context forever ,level 12 --启用会话级10046
SQL> oradebug event 10046 trace name context off --关闭10046事件
SQL> oradebug tracefile_name --查看tracefile文件位置及文件名
用tkprof格式化10046 trace
tkprof
二 内存问题分析
Top命令->%MEM可以看到消耗CPU最多的process。
pmap命令查看占用内存的组件。
OS命令vmstat 判断是否存在memory swap问题。
内存对性能的影响在oracle database上一般表现为latch free的等待。
三 CPU问题分析
vmstat -> r可以看到runQ的个数，当这个列的值超过cpu个数时，说明存在wait CPU的现象。这时CPU资源不足。
查看AWR->Time Model Statistics，如果消耗sql execute elapsed time占90%以上，说明瓶颈在sql执行上。可以查看SQL statistics->SQL ordered by CPU Time这个章节，优化top SQL。
如果是其它原因导致的CPU消耗过高，可以参数Top 5等待事件，有针对性地优化。或者增加CPU来解决。
四 系统IO问题分析
Oracle Database在IO耗时，体现在相关的等待事件上。可以参考第五章与IO相关的等待进行分析。
五 数据库资源等待分析
5.1 buffer busy waits
阐明问题：
等待事件Buffer busy waits是发生在一个会话希望能够访问一个在buffer cache里的数据块的时候，发现这块buffer正处在busy，可能有其他并发的会话正在将同样的数据块从磁盘往buffer里读或者这些块正在做某种修改还没有完成。所以出现buffer busy waits等待的时候，就有这么两种情况：
1. 另外某个session正在将磁盘上数据块往buffer里读；Another session is reading the block into the buffer
2. 另外某个session正以非兼容模式持有了当前会话所要请求的块。
这个等待事件包含三个参数：
file# ：要读取的数据块所在数据文件的文件号。
block#：读取的数据块号。
class#：块的种类。
SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'buffer busy waits';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- -------------------- ---------------- ---------------- ----------------
94 buffer busy waits file# block# class#
一般可能出现buffer busy waits等待的数据块有这样几种：
? 数据块
? 段头块
? freelist块
? undo头块
确认问题：
如果遇到实际数据库环境中出现等待事件buffer busy waits，那么就需要先确认是否是由于buffer busy waits引起了数据库的性能问题。需要作如下确认：
? 等待事件'buffer busy waits' 是不是总共DB Time 的重要组成部分
那么为了验证这些情况，就需要搜集相关资料来确定问题：
1. 收集问题时刻awr报告
? 收集问题时刻的awr和正常业务时刻的awr进行对比，看看DB Time增长情况
? 确定问题时刻db file scattered read等待是否在TOP事件中占重要比例。这样可以判断是否是由于db file scattered read引起的性能问题
? 如果buffer busy waits等待占整个DB Time比率很高，可以进一步查看awr的buffer wait统计信息部分，正常等待应该是1秒以内确认是否有平均时间大量超过1秒的等待情况：
找到根因：
上面已经提及了buffer busy waits等待相关的对象一般有如下几种：
? 数据块
? 段头块
? freelist块
? undo头块
那么需要找到到底是什么块上发生buffer busy waits等待，可以通过awr报告或者10046跟踪session的方式获得。
1. 通过awr报告的方式，可以先在awr报告里找到段统计信息，关注下其中“Segments by Buffer Busy Waits”部分：
根据这个我们可以得到等待最多的那些段信息。
那么根据这部分内容，再继续查看相关的SQL语句，可以查看SQL统计信息部分：
关注“SQL ordered by Elapsed Time”部分，特别注意对应消耗CPU特别高的SQL语句，查看具体的SQL text，再对比上面查看的段信息进行互相验证，基本上就可以定位到导致问题的SQL语句。
2. 通过10046跟踪session的方式，在得到trace文件之后，通过tkprof的方式进行格式化。那么得到一个格式化后的trace文件，这时候需要关注trace文件中一些信息：
a) 在trace文件里，找到“Overall Totals”部分，确认下等待事件buffer busy waits是否有最长的等待时间
b) 确定下消耗时间最多的调用是什么：execute？fetch？
这里可以通过生成新的以调用类型排序的tkprof文件的方法来确定：
Execute calls:
tkprof trace_file_name output_file sort=exeela
Fetch calls:
tkprof trace_file_name output_file sort=fchela
c) 根据tkprof文件找到一些top消耗时间的SQL语句，然后根据这些SQL再去最原始trace文件中（未进行tkprof格式化）找到对应的cursor部分。
从最原始trace文件中（未进行tkprof格式化），搜索“WAIT #”部分在等待buffer busy waits的内容，如下所示：
WAIT #2: nam='buffer busy waits' ela= 222 file#=4 block#=78391 class#=1 obj#=57303 tim=1151844401945055
从这里可以得到buffer busy waits等待的p1、p2、p3，可能相关联p1、p2、p3一直在变，但是最终就是通过这些值来找到最终相关的段信息。
从上面的信息可以得到object_id（obj#），那么就很容易查看相对应的对象名和对象类型，如下：
SELECT owner, object_name, object_type FROM dba_objects WHERE object_id = 57303;
OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- -------------------
SCOTT STOCK_PRICES TABLE
d) 可以通过下面查询语句，根据已知的文件号和块号找到相应的段信息：
SELECT owner, segment_name, file_id, block_id starting_block_id, block_id + blocks ending_block_id, blocks
FROM dba_extents
WHERE file_id = &file_num AND ( block_id <= &block_id AND (&block_id < (block_id + blocks)) )
OWNER SEGMENT_NAME FILE_ID STARTING_BLOCK_ID ENDING_BLOCK_ID BLOCKS
---------- -------------------- ---------- ----------------- --------------- ----------
SCOTT STOCK_PRICES 4 78385 78393 8
解决方案：
1. 如果等待的是Freelist块或者段头块，那么这种情况一般是并发insert语句的情况下，多个session希望插入数据到同一个块里造成的。这种情况是由于没有使用段空间的自动管理，而是使用了freelist，那么一般建议使用段空间的自动管理方式。blocks/segment header:
2. 如果等待的是数据块，那么就需要关注下相关的SQL语句，看看SQL语句的性能是否存在问题，一般这种对SQL进行适当的调优，避免SQL语句读一些无用的数据块，这样就可以解决buffer busy waits的问题。实际生产中，大部分都是SQL性能问题导致扫描过多的数据块引起的热点块，从而导致大量的buffer busy waits等待。
3. 如果是undo段头，那么建议使用undo的自动管理方式，或者增加一些回滚段来解决等待问题。Undo header:
5.2 db file scatter read
阐明问题：
db file scatter read等待事情是典型的IO类等待。这种等待事件的出现，意味着数据库正在以多块读的方式把磁盘上的数据块读到非连续的内存buffer里。这样一次多块读的数量受到参数DB_FILE_MULTIBLOCK_READ_COUNT的影响。一般出现这类等待，是由于当下数据库再对表数据进行全表扫描或者对索引进行快速全扫描。
这个等待事件包含三个参数：
file# ：要读取的数据块所在数据文件的文件号。
block#：读取的起始数据块号。
blocks：需要读取的数据块数目。
SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'db file scattered read';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- ------------------------------ ---------------- ---------------- ----------------
147 db file scattered read file# block# blocks
确认问题：
如果遇到实际数据库环境中出现等待事件db file scatter read，那么就需要先确认是否是由于db file scatter read引起了数据库的性能问题。需要作如下确认：
? 花在db file scatter read等待上的时间是不是很多
? 等待事件'db file scattered read' 是不是总共DB Time 的重要组成部分
? 等待事件'db file scattered read' 是否很慢
? 平均一次IO时间是否超过一个标准值(少于20 ms) for I/O performance
? 是否存在大量的全表扫描或者索引快速全扫
那么为了验证这些情况，就需要搜集相关资料来确定问题：
2. 收集问题时刻awr报告
? 收集问题时刻的awr和正常业务时刻的awr进行对比，看看DB Time增长情况
? 确定问题时刻db file scattered read等待是否在TOP事件中占重要比例。这样可以判断是否是由于db file scattered read引起的性能问题
? 如果db file scattered read等待占整个DB Time比率很高，需要再次关注下IO是否存在性能问题，一般标准是平均一次IO时间应该少于20毫秒是比较正常的。
3. 收集问题时刻15分钟左右的ASH报告
4. 收集问题时刻的操作系统信息
5. 查询数据库视图来得到全表扫描和索引快速全扫：
For Full Table scans:
select event#,name,parameter1,parameter2,parameter3 from select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
order by p.hash_value, t.piece;
For Fast Full Index scans:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN'
order by p.hash_value, t.piece;
找到根因：
1. 根据awr报告中"Tablespace IO"和"File IO"部分，来找到问题时刻IO情况，然后再根据当时的操作系统信息来找到IO性能问题的根因：
2. 根据awr报告中的SQL统计信息部分，来找到导致db file scattered read等待的SQL语句，主要关注SQL ordered by Reads：
如果定位到了具体SQL的ID，需要具体确认问题时刻SQL的执行情况，可以通过SQL的awr报告来查询当时的执行计划，这样方便进一步进行SQL性能优化，生成SQL报告的方法：
解决方案：
1. 如果是IO本身响应慢，可以考虑把需要经常访问的数据放置到更快的disk上来减少响应时间；
2. 如果是SQL本身全表扫描或者索引快速扫描导致的性能问题，需要对SQL语句进一步优化；
3. 如果SQL本身无法进一步优化，而且业务上经常性需要进行这种全表扫描或者索引快速全扫，或许这种业务就是数据仓库类的业务，无可避免有大量数据的查询业务，那么也可以考虑将查询业务放到active dataguard数据库上，避免对实际的oltp业务造成影响；
4. 如果SQL涉及到的表数据量很大，也可以考虑根据业务查询需求对表进行一定的分区，避免每次查询都需要进行全表数据的扫描；
5.3 db file sequential read
阐明问题：
db file sequential read等待事情跟db file scattered read是都是特别常见的IO类等待。db file sequential read等待事件出现，说明是将数据块顺序地读到连续的内存空间里。但是scattered read则是一次读多个数据块，并且是离散的读到SGA不同的buffer里。所以一般情况下，我们都认为sequential read就是单块读（当然也有可能出现读多块的情况）。比较常见场景是在对索引的扫描或者是回表的时候，也有可能在读回滚段的时候出现单块读的情况。
这个等待事件同样包含三个参数：
file# ：要读取的数据块所在数据文件的文件号。
block#：读取的起始数据块号。
blocks：需要读取的数据块数目。
注意：一般情况下第三个参数blocks的值都是1.但是也有可能出现P3 > 1 的情况，这种就是多块读。“db file sequential read”出现多块读的情况，这种在oracle早期版本中读取排序段的时候可能会出现。
SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'db file sequential read';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- ------------------------------ ---------------- ---------------- ----------------
153 db file sequential read file# block# blocks
确认问题：
如果遇到实际数据库环境中出现等待事件db file sequential read，那么首先确认是否是由于db file sequential read引起了数据库的性能问题。需要作如下确认：
? 花在db file sequential read等待上的时间是不是很多
? 等待事件“db file sequential read” 是不是总共DB Time 的重要组成部分
? 等待事件'db file sequential read' 是否很慢
? 平均一次IO时间是否超过一个标准值(少于20 ms) for I/O performance
那么为了验证这些情况，就需要搜集相关资料来确定问题：
①收集问题时刻awr报告
? 收集问题时刻的awr和正常业务时刻的awr进行对比，看看DB Time增长情况
? 确定问题时刻db file sequential read等待是否在TOP事件中占重要比例。这样可以判断是否是由于db file sequential read引起的性能问题
? 如果db file sequential read等待占整个DB Time比率很高，需要再次关注下IO是否存在性能问题，一般标准是平均一次IO时间应该少于20毫秒是比较正常的。
? 还需要关注下SQL统计部分"Top SQL by Disk Reads"：
确认是否有执行时间很长的sql语句存在。
② 收集问题时刻15分钟左右的ASH报告
可以找出等待“db file sequential read”的会话以及相关的sql语句
③ 收集问题时刻的操作系统信息
iostat或vmstat观察是否存在IO繁忙或者iowait的情况的
找到根因：
1. 根据awr报告中"Tablespace IO"和"File IO"部分，来找到问题时刻IO情况，然后再根据当时的操作系统信息来找到IO性能问题的根因：
2. 根据awr报告中的SQL统计信息部分(或者ash报告)，来找到导致db file sequential read等待的SQL语句，主要关注SQL ordered by Reads：
如果定位到了具体SQL的ID，需要具体确认问题时刻SQL的执行情况，可以通过SQL的awr报告来查询当时的执行计划，这样方便进一步进行SQL性能优化，生成SQL报告的方法：
SQL> @?/rdbms/admin/awrsqrpt
解决方案：
1. 如果是IO本身响应慢，可以考虑把需要经常访问的数据放置到更快的disk上来减少响应时间；
2. 如果是SQL本身的性能问题（可能是走了不合适的索引或者回表次数太多等），需要对SQL语句进一步优化，基本上都可以解决掉这类等待的问题。
5.4 direct path read
阐明问题
在11g中有个新特性就是在对大表进行全表扫描的时候，等待事件不再是db file scattered read，而是这里提及的direct path read，就是直接路径读。那么同样是全表扫描，同样需要从磁盘上把数据块读到内存里，有什么区别呢？direct path read这个等待事件的发生，说明数据块并不是被直接读到sga的buffer cache里了，而是直接读取到server process的pga内存区域。也就是说数据块绕过共享内存，直接被读到每个服务器进程的私有内存了。采取这种方式，一定程度上，可以降低了对于latch的使用，进而避免了可能导致的latch竞争（比如cache buffers chains的 latch等），也可以避免全表扫描对sga里buffer cache的冲击。
该等待事件有三个参数：
? P1 = file#
? P2 = start block# ("first dba")
? P3 = num blocks
P1，表示要读取的数据块所在数据文件的文件号。
P2，表示要读取的起始数据块号。
P3，表示需要读取的数据块数目。
确定问题:
一般发生direct path read等待，可以通过收集问题时刻的awr或者ash来确定问题。
从awr报告里可以看出direct path read等待在top里占有了大部分的db time（而且平均等待事件在20毫秒以内，否则就需要同时关注系统IO的问题）：
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 13,916 42.1
direct path read 1,637,344 13,359 8 40.4 User I/O
db file sequential read 47,132 1,111 24 3.4 User I/O
DFS lock handle 301,278 1,028 3 3.1 Other
db file parallel read 14,724 554 38 1.7 User I/O
或者在线可以通过v$session确定当前活动会话中非空闲等待中大部分是direct path read造成的。
问题根源:
根据已有的awr信息，可以通过查看awr报告里的SQL ordered by reads部分的top SQL，然后再根据ash或者通过v$session里等待事件的参数P1，P2来定位到具体的数据库对象，或者查询awr报告中Segments by Direct Physical Reads部分的top对象。这样再对比top sql的部分，就可以确定最终导致问题的SQL语句，以及出现问题的具体对象。
然后打印出问题时刻相关SQL的执行计划，就可以很明确的看到SQL执行慢的地方，进而有针对性对该SQL进行调优操作。
解决方案:
1. 由于direct path read是因为oracle在做全表扫描时候的另外一种方式，所以这种情况下，一般最直接的办法，可以通过针对相关SQL进行优化，提高SQL的执行效率
2. 如果SQL本身无优化空间，可以考虑将相关全表扫描的表的大小降下来，通过分区表或者把历史数据归档等方式来实现。这样一定程度上也可以避免产生direct path read的等待。
3. 如果本身是IO问题导致，解决IO的性能问题
5.5 enq: TM – contention
定位问题（根据AWR报告）：
? 并非所有会话、查询或作业存在性能问题
? 等待事件：'eng:TM-contention' 在DB Time中占比较高
? 'CPU Busy Time' 没有超过 'CPU total time' 的80%
? 关于 'TM Lock' 和 'TM-contention'：
? 申请TM队列锁：
? 对表进行操作时，如INSERT、UPDATE、DELETE、MERGE、SELECT FOR UPDATE或者LOCK TABLE。
? 启用外键约束。
? 将约束从DIASABLE NOVALIDATE 改变成 DISABLE VALIDATE。
? 重建IOT表。
? 创建或修改视图。
? 收集表的统计信息或validate structure。
? TM队列锁会以 'TM-'作为资源名的前缀。
? 当多个会话同时申请同一个TM队列锁或申请的队列锁不兼容时，申请锁的会话便会等待直到获取，这个等待的过程就是'TM-contention'。
? TM队列锁的兼容：
LMODE DESCRIPTION NAME NULL SS SX S SSX X
0,1 NO LOCK NULL YES YES YES YES YES YES
2 ROW-SHARE SS YES YES YES YES YES NO
3 ROW-EXCLUSIVE SX YES YES YES NO NO NO
4 SHARE S YES YES NO YES NO NO
5 SHARE ROW-EXCLUSIVE SSX YES YES NO NO NO NO
6 EXCLUSIVE X YES NO NO NO NO NO
? TM锁的查看（V$LOCK）：
? TYPE是TM
? ID1为对象ID
? ID2固定为0
? LMODE和REQUEST为当前持有和申请的锁模式，表现形式为数字。
'TM-contention' 常见根因：
? 外键列上无索引
? 分析：
? 对父表的操作如果涉及到主键（删改），就会对子表申请一个4级锁。对子表申请的锁会在特定条件下升级为5级锁，比如同时对父表的主键（删改）和子表进行操作。
? 由于锁的兼容性导致当外键列上无索引时，容易出现TM锁等待事件。
? 当并行操作增加时，这种情况还会提高死锁产生的概率。
? 优化方案：
o 在外键列上创建索引。
? 并行DML（PDML）：
o 分析：
o 在对一张表或分区执行PDML操作时，需要申请TM锁。因此，在多个会话同时对一个对象进行DML操作时，会引起'TM-contention'等待事件。
o 优化方案：
o 调整执行PDML的时间。
可以考虑用脚本代替PDML，将PDML分散成数个DML语句，从而避免TM锁竞争。
5.6 enq: TX – index contention
5.8 free buffer waits
阐明问题：
free buffer waits等待事件是跟高速缓冲区的buffer有关的。当服务进程扫描LRU链表期望找到空闲的buffer的时候，如果一直没有找到空闲的，那么势必会请求DBWR进程写入脏的buffer到磁盘上，以便腾出空闲的buffer使用。当DBWR开始写脏buffer到磁盘的时候，那么进程一直等待的就是free buffer waits。
确认问题：
如果遇到等待事件free buffer waits，首先确认free buffer waitsread是否是引起据库性能问题的关键因素。需要作如下确认：
? 等待事件“free buffer waits” 是不是总共DB Time 的重要组成部分
那么为了验证这些情况，就需要搜集相关资料来确定问题：
收集问题时刻awr报告
? 收集问题时刻的awr，查看Top 5 Timed Foreground Events ，看free buffer waits占用DB Time的百分比是否超过20%以上。
? 关注SQL统计部分：SQL ordered by Gets，查看是否有请求buffer特别多的sql语句。
收集问题时刻15分钟左右的ASH报告
找出是否有大量session在等待“free buffer waits”
收集操作系统信息
关注vmstat/iostat，观察是否有io繁忙或者iowait，特别关注下dbwr进程的资源消耗情况
找到根因：
1. 根据awr报告中SQL Statistics部分，找到是否存在读取buffer过多的sql语句。如果有，减少这些sql语句读取不必要的buffer，势必会降低对free buffer的请求，也就可以减少free buffer waits的等待；
2. 根据操作系统信息，判断是否dbwr进程写脏数据到磁盘太慢导致的等待：
? 检查CPU资源是否出现饱和，如果出现饱和，数据库后台进程就不能够很快的获得足够的cpu资源进行处理；
? 检查IO是否读写慢。如果IO写太慢，会影响数据库写进程效率；
? 检查异步IO是否开启。异步IO可以提供db writer进程吞吐量；
? 优化checkpoint。这样可以更快的把脏块及时刷到磁盘上；
3. 根据awr可以得知数据库buffer cache大小设置。如果buffer cache设置太小并且被热块填满，那么其他会话在寻求空闲buffer的时候，将会耗费太多时间来找到空闲缓冲区，或者驱动DBWR进程写脏数据，这个过程都会持续等待free buffer。
解决方案：
1. 如果是DBWR写的慢，可以考虑从调优DBWR写的方式解决；
2. 如果是SQL语句本身的性能问题（导致读取的数据块过多，其实可以降低读块数据量的），那么，就需要对SQL语句进一步优化来解决掉这类等待的问题。
3. 如果是buffer cache太小，增加DB_CACHE_SIZE 的值来解决。
5.9 latch: cache buffers chains
阐明问题：
latch: cache buffers chains等待事情首先我们要知道它是发生在内存里的等待，而且是在从内存里找到某个block。也就是，一个session希望从内存buffer cache里读块，显然这是一个逻辑读的过程。我们都知道，内存里的block都是串在一个block chain上的。如果一个session想找到某个block，首先要获得这个chain上的latch。正常来讲，获取一个latch，迅速找到需要的block，这是不需要很长时间的等待。但是如果同时满足两个条件的情况下，那么就会出现很明显的latch: cache buffers chains等待：
? 一个session读了太多的block（本来应该可以少读一些块的，但是由于SQL本身效能的问题，导致多读了很多无效的block）；
? 多个session需要读相同的块（并发）
那么这两个条件总结下来，就是一个block反复被多个session同时读取，这种情况下，就可能会出现latch: cache buffers chains的等待。其实这种情况下，就造成某些block成为“热点块”，一直在不断地反复被读取。
另外这个latch等待有三个参数：
? P1 = latch地址
? P2 = Latch编号
? P3 = 尝试次数
第一个参数，latch地址，可以通过这个地址找到相对于的latch情况，查询如下：
SELECT name, 'Child '||child#, gets, misses, sleeps
FROM v$latch_children
WHERE addr='&P1RAW' ;
在Oracle里，每个child latch会用来保护一个或者多个 buffer hash chains，这个可想而知，如果sql效能不好的话，更加会导致session扫到同一个chain的可能性了。
第二个参数，latch编号，一般知道等待事件是“cache buffers chains”，就不需要这个参数了。
第三个参数，尝试次数，是指的session为了获得latch尝试的次数，这个其实是latch不断去get然后又sleep的次数。就是说，一个进程要去获得某个buffer所在的chain的latch的时候，它先去尝试获取，但是发现latch正在被其他进程持有，此时就陷入等待（sleep的状态），等到固定某个时间间隔之后，进程会自我唤醒，然后重新去申请获得latch，如果获得了，那么将继续下步工作，否则还是这样循环的等待下去。
确认问题：
在遇到数据库有latch: cache buffers chains等待事件出现的时候，要首先确认是否真的由于这个latch等待导致了数据库性能问题。可以做两方面的数据收集：
? 如果通过v$session或者ash来统计问题时刻主要的等待事件
? 收集问题时间区间内的awr
在收集到上述信息之后，查看latch: cache buffers chains等待是否占主要地位，比如AWR里的TOP是否类似如下：
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: cache buffers chains 74,642 35,421 475 6.1 Concurrenc
CPU time 11,422 2.0
log file sync 34,890 1,748 50 0.3 Commit
latch free 2,279 774 340 0.1 Other
db file parallel write 18,818 768 41 0.1 System I/O
-------------------------------------------------------------
那么这个时候，我们可以继续查看AWR的两个部分来进一步确认问题。
第一， 查看SQL部分：
SQL部分，我们关注下ordered by Gets部分，看下排在前面的SQL，对比下，查看下前面几个SQL语句buffer gets差距如何，是否出现某些SQL的buffer异常高于其他的SQL（而且执行次数肯定比较多），一般这种的SQL就是重点关注对象：
第二， 查看段统计信息部分：
得到buffer gets特别高的SQL之后，继续查看段统计信息：
查看逻辑读高的top，然后找到逻辑读区别于其他，显示的异常高的对象。
这样基本上就确认数据库的问题是由于latch: cache buffers chains导致的。
找到根因：
确认问题之后，需要找到问题出现的原因，那么根据上面确认过程得到的信息，其实这个时候方法很简单，就需要找到top的SQL语句在指定时刻的执行计划并打印出来，然后根据再对照段统计信息，根据逻辑读最高的那个对象，去执行计划里找到对应的位置，就可以分析出来为什么这个SQL语句慢或者说产生了大量的逻辑读。
解决方案：
因为我们已经明白了latch: cache buffers chains是如何导致的。所以解决办法就有如下几个：
? 通过DBA优化手段，相对应的SQL效率是否有提升的可能性，这种的就是直接优化SQL；
? 跟应用确认，相对应的SQL语句高并发是否正常，能否避免高并发的发生
5.11 latch: library cache
5.12 latch: shared pool
确定问题：
1.1. AWR
AWR-> Top 5 Timed Foreground Events
在收集到的问题时刻的AWR报告里，latch: shared pool等待事件排在top5等待的前一两位，并占用大量的DB Time，并且像Library Cache相关的等待事件也同时出现在top事件里。
AWR-> Load Profile
并且Load Profile里，每秒硬解析的次数很多，达到了上万或者更多次。
这样从awr来看，基本上就可以确定问题是出在latch: shared pool等待上。
1.2. V$SESSION&ASH
根据查询的问题时刻的v$session或者ash信息，在非idle等待中，所有会话等待最多的就是latch: shared pool 等待事件，并且伴随着shared pool其他组件的等待事情，比如Library Cache之类。
数据收集及分析：
2.1. AWR报告
收集到问题时刻的awr报告。
需要关注awr这样一些模块的信息：
AWR->Load Profile
AWR->Time Model Statistics
2.2. v$session&ash
查看视图v$session或者v$active_session_history或者dba_hist_active_sess_history
确定原因及方案：
3.1. SQL硬解析太多
3.1.1. 原因判定
AWR->Library Cache Activity
SQL AREA部分的Pct Miss超过50%左右
AWR->Time Model Statistics
parse time elapsed占用了大部分的DB Time，而且明显超过了sql execute elapsed time
v$sql
在问题时刻，查看当前shared pool里不能共享，经常被解析的SQL语句：
SET pages 10000 SET linesize 250 column FORCE_MATCHING_SIGNATURE format 99999999999999999999999 WITH c AS (SELECT FORCE_MATCHING_SIGNATURE, COUNT(*) cnt FROM v$sqlarea WHERE FORCE_MATCHING_SIGNATURE!=0 GROUP BY FORCE_MATCHING_SIGNATURE HAVING COUNT(*) > 20 ) , sq AS (SELECT sql_text , FORCE_MATCHING_SIGNATURE, row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p FROM v$sqlarea s WHERE FORCE_MATCHING_SIGNATURE IN (SELECT FORCE_MATCHING_SIGNATURE FROM c ) ) SELECT sq.sql_text , sq.FORCE_MATCHING_SIGNATURE, c.cnt "unshared count" FROM c, sq WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE AND sq.p =1 ORDER BY c.cnt DESC
注：
所以很多可能导致更多硬解析的原因，都有可能会导致出现大量的latch: shared pool。比如version count都有可能出现latch: shared pool。
3.1.2. 解决方案
改写应用程序，尽量使用绑定变量，减少硬解析
比如SQL语句：
select count(*) from scott.emp where ename=’jack’; select count(*) from scott.emp where ename=’lucy’; select count(*) from scott.emp where ename=’lily’;
可以用类似绑定变量的方式改写成：
select count(*) from scott.emp where ename=:name;
谨慎修改CURSOR_SHARING初始化参数
如果应用程序无法修改SQL成绑定变量的方式，还可以考虑修改数据库参数CURSOR_SHARING的值为Force。那么Oracle会对所有在数据库内执行的SQL语句强制使用绑定变量的方式。但是这种修改需要经过一定的测试，因为会带来一些SQL语句执行计划的不稳定。
3.2. shared pool太小
3.2.1. 原因判定
AWR->Library Cache Activity
reloads列出现很高的数值，通常一个小时有2000以上就算比较高的情况。但是相对应的invalidations却是很小甚至没有数值的情况。
AWR-> Shared Pool Statistics
“% SQL with executions>1”的值超过了60%左右
AWR-> Memory Resize Ops
关注shared pool部分是否有频繁性的resize的情况发生
v$session
从session信息里看基本上是相同的SQL发生了latch: shared pool等待
3.2.2. 解决方案
增加shared pool的值
在asmm或者amm内存管理方式下，可以设定shared pool的值，以保证shared pool有一个最小的值存在。
keep住相关对象到shared poll
可以根据需要将需要的SQL语句keep到内存里，尽量不让其被刷出内存，使用Oracle提供的DBMS_SHARED_POOL.KEEP()可以实现。
5.13 library cache lock
确定问题
1.1. AWR
AWR-> Top 5 Timed Foreground Events
在收集到的问题时刻的AWR报告里，library cache lock等待排在top5等待的前一两位，并占用大量的DB Time，一般在20以上就属于比较严重的情况。问题就可以锁定在library cache lock等待上。
1.2. V$SESSION&ASH
根据查询的问题时刻的v$session或者ash信息，在非idle等待中，所有会话等待最多的就是library cache lock等待事件。
数据收集及分析
2.1. AWR报告
收集到问题时刻的awr报告。
需要关注awr这样一些模块的信息：
AWR->Library Cache Activity
AWR->Time Model Statistics
AWR-> Shared Pool Statistics
AWR-> Memory Resize Ops
AWR-> Dictionary Cache Statistics
AWR-> SQL ordered by Version Count
2.2. v$session&ash
查看视图v$session或者v$active_session_history或者dba_hist_active_sess_history
确定原因及方案
3.1. SQL无法共享
3.1.1. 原因判定
AWR->Library Cache Activity
SQL AREA部分的Pct Miss超过50%左右
AWR->Time Model Statistics
parse time elapsed占用了大部分的DB Time，而且明显超过了sql execute elapsed time
v$sql
查看数据库里不能共享的sql语句：
SELECT FORCE_MATCHING_SIGNATURE，COUNT(*) FROM V$SQL GROUP BY FORCE_MATCHING_SIGNATURE HAVING COUNT(*)>2 ORDER BY 2 DESC
3.1.2. 解决方案
改写应用程序，对于where判断条件相同但是具体值不同的SQL语句建议使用绑定变量的方式替换之
比如SQL语句：
select count(*) from scott.emp where ename=’jack’; select count(*) from scott.emp where ename=’lucy’; select count(*) from scott.emp where ename=’lily’;
可以用类似绑定变量的方式改写成：
select count(*) from scott.emp where ename=:name;
谨慎修改CURSOR_SHARING初始化参数
如果应用程序无法修改SQL成绑定变量的方式，还可以考虑修改数据库参数CURSOR_SHARING的值为Force。那么Oracle会对所有在数据库内执行的SQL语句强制使用绑定变量的方式。但是这种修改需要经过一定的测试，因为会带来一些SQL语句执行计划的不稳定。
3.2. 共享SQL被age out
3.2.1. 原因判定
AWR->Library Cache Activity
reloads列出现很高的数值，通常一个小时有2000以上就算比较高的情况。但是相对应的invalidations却是很小甚至没有数值的情况。
AWR-> Shared Pool Statistics
“% SQL with executions>1”的值超过了60%左右
AWR-> Memory Resize Ops
关注shared pool部分是否有频繁性的resize的情况发生
v$session
从session信息里看基本上是相同的SQL发生了library cache lock等待
3.2.2. 解决方案
增加shared pool的值
在asmm或者amm内存管理方式下，可以设定shared pool的值，以保证shared pool有一个最小的值存在。
keep住相关对象到shared poll
可以根据需要将需要的SQL语句keep到内存里，尽量不让其被刷出内存，使用Oracle提供的DBMS_SHARED_POOL.KEEP()可以实现。
3.3. Library Cache对象失效
3.3.1. 原因判定
AWR->Library Cache Activity
reloads列出现很高的数值，通常一个小时有2000以上就算比较高的情况。同时，相对应的invalidations也很高，一般在1000左右或以上。
AWR-> Shared Pool Statistics
“% SQL with executions>1”的值超过了60%左右
AWR-> Dictionary Cache Statistics
其中Mod Reqs 指标出现了很多非0的列，尤其是dc_objects 这样的cache部分出现了非零值。那么说明在一些对象上发生了DDL操作。
3.3.2. 解决方案
不要在业务高峰期进行DDL操作
比如在业务高峰期增加表的字段，修改表的字段类型等等
不要在业务高峰期收集统计信息
不要在业务高峰期进行truncate操作
3.4. 大量的Child Cursors
3.4.1. 原因判定
AWR-> SQL ordered by Version Count
在“SQL ordered by Version Count”部分，如果存在超过500个version的SQL语句，那么基本上可以判定跟version count有关系。可以通过v$sqlarea找到任何一个超过500version的SQL语句
V$SQL_SHARED_CURSOR
根据得到的SQL语句再去v$sql_shared_cursor里找出为什么不能被共享的原因。
3.4.2. 解决方案
尽量修改应用程序使用绑定变量，不要设置CURSOR_SHARING为force或者SIMILAR
可能version count的问题跟某些bug相关，需要打上特定的psu或者patch
3.5. 用户密码错误
3.5.1. 原因判定
用户反映连接数据库很慢，而且排查了网络和监听都没有问题
v$session
通过v$session或ash查看到数据库里大量的会话在问题时刻的等待事件都是library cache lock。但并没有阻塞，很快就结束了。查看library cache lock事件的P3参数，将其转成16进制，取出前两位的并转成10进制，得到的数字是79。这个表示就跟ACCOUNT_STATUS有关系。基本上判定是账号密码错误导致的。
DDL锁的type是79
检查DBA_DDL_LOCKS视图里以独占exclusive方式持有的session的type是79：
SQL> select * from dba_ddl_locks where mode_held='Exclusive'; SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU ---------- --------- ---------- ---------- --------- --------- 612 5 79 Exclusive None
设置event找到具体的连接数据库的机器
一般遇到这种账号问题，都需要找到具体的连接机器。可以设置event：
alter system set events '1017 trace name errorstack level 3'; alter system set events '1017 trace name errorstack off';
这样就可以根据得到的trace文件，找到具体连接数据库的主机名和IP地址及用户信息。
select * from dba_ddl_locks where mode_held='Exclusive';
3.5.2. 解决方案
重新修改连接数据库用户的密码
3.6. 同一个对象被多个会话编译
3.6.1. 原因判定
v$session&ash
查看问题时刻的session信息，除了library cache lock等待之外，还相间产生了很多的library cache pin的等待。
另外根据session里的阻塞关系找到最终的阻塞者。
v$sql
查看最终阻塞者在session中的信息，得到的SQL语句都是跟PL/SQL存储过程等相关的。这个时
候就需要关注是否是同一个PL/SQL对象同时被多个session编译或者执行。这种高并发情况下会导致大量的library cache lock的等待。
3.6.2. 解决方案
避免业务高峰期不同的session同时编译相关的对象
3.7. 大量使用行级trigger
3.7.1. 原因判定
AWR->Library Cache Activity
SQL AREA部分的Pct Miss超过50%左右
AWR->Time Model Statistics
parse time elapsed占用了大部分的DB Time，而且明显超过了sql execute elapsed time
v$session&ash
根据v$session或者ash得到library cache lock等待事件的sql都是跟trigger有关系的递归SQL语句
3.7.2. 解决方案
评估下触发器是否一定需要，是否考虑用其他方案代替
5.14 library cache pin
5.16 cursor pin: S
等待事件说明：
当一个会话尝试以共享模式（S - Share）来获得一个游标时，需要修改相应的Mutex结构的引用计数（reference count），或者增加该计数，或者减少。修改引用技术的原子操作很快（其实和Latch的获取释放类似），但是在频繁解析的情况下，仍然产生了竞争和等待，由此就产生了 cursor : pin S 的等待。
Mutex机制在Oracle 10g引入，用于替代Library cache pin操作，其性能更高，其原理为在每个Child Cursor上分配一个地址空间记录Mutex，当该Cursor被共享执行时，通过将该位进行加一处理来实现。虽然是指游标共享，但是更新Mutex结构的操作需要排他，当某一个SQL被频繁共享执行时，可能就会出现Pin S的等待。
每个Library Cache对象都有一个reference count （引用计数），用来表明有多少其他对象目前正保留一个对它的引用（reference). 对象A 想要引用对象B, A 就把B 的 reference count 加 1。 当A 结束了对B 的引用， A 就把 B 的reference count 减 1. 当没有任何对象再引用 B 时， B 的 reference count就减为0， B 就被清除（deallocated）, 内存就被释放。清除B的时候， 被B所用的对象的 reference count 也可能减小， 也可能使它们被清除。
参数说明：
版本 (10.2.0.1 - 11.2.0.2)
P1 = idn
P2 = value
P3 = where (where|sleeps in 10.2)
idn: Mutex号,与等待mutex的SQL的hash_value 相同。可以通过IDN查询到该条SQL的信息
SELECT sql_id, sql_text, version_count
FROM V$SQLAREA where HASH_VALUE=&IDN;
如果SQL_TEXT列显示的为"table_x_x_x_x"格式的值，这是一种特殊的内部游标—伪游标(Pseudo cursor)。可通过以下语句查询该SQL涉及到的对象
select * from h$pseudo_cursor where sql_id='&sql_id';
P1raw是p1的十六进制值，可以用p1raw值去搜索tracefile里的sql hash value值。
value: mutex值，包含两部分。
高阶位(high order bits)包含持有mutex的会话号(如果在等待"cursor: pin S"即为0，如果有其他会话持有X模式，该会话就在等待"cursor: pin S wait on X")
低阶位(low order bits)包含reference count(即其他持有S模式的计数)
Where: 申请mutex的代码位置。P3的高阶位(high order bits)是一个数字，表示Oracle代码中请求mutex的位置。在10.2版本中低阶位(low order bits) 给出睡眠时间，在11g中 low order bits全部为0。
警告：在10.2版本中，低阶位睡眠值可能溢出到高阶位，特别是在32位平台上，给出了错误的位置值
用一下语句P3的高阶位可以映射出位置名称
SELECT decode(trunc(&&P3/4294967296),
0,trunc(&&P3/65536),
trunc(&&P3/4294967296)) LOCATION_ID
FROM dual;
SELECT MUTEX_TYPE, LOCATION
FROM x$mutex_sleep
WHERE mutex_type like 'Cursor Pin%'
and location_id=&&LOCATION_ID;
收集信息：
V$ACTIVE_SESSION_HISTORY / ASH报告
V$MUTEX_SLEEP_HISTORY
AWR 报告 / V$SQLAREA
确定原因：
多个会话等待的是同一个idn：这很可能是由于同一个mutex的高并发引起的。使用ASH数据或采样等待/ 10046跟踪来查找热SQL语句。 通常这些SQL将在AWR报告中显示为高的逻辑读(Gets) / 高的执行次数(Executions) / 高的软解析次数(parse calls)的SQL。 对于以增加特定SQL的并发性来调优的应用程序，该等待事件实际上可能会更糟。
系统负荷高，等待的是非常多不同的idn号：如果CPU已经用尽了，那么“cursor: pin S”等待可能会发生在很多游标上，并且问题可能会越来越严重，因为等待该事件的会话本身也会非常消耗CPU。
建议：
11.2版本之前(不含11.2)的数据库打修复Bug:6904068的补丁并设置睡眠选项参数为1。11.2版本安装修复bug 10411618 的补丁。在多个会话竞争同一个mutex时，可以多一些呼吸空间，从而避免CPU使用增加并加剧该等待事件的发生。
对于由于同一条sql的高并发性引起的”cursor: pin S”，可以修改应用程序，降低并发访问数量。
或者通过用不同会话执行的某些变体(比如添加注释)替换一个SQL来减少特定游标上的并发性。
比如：语句"select name from acct where acctno=:1"是条热SQL，可以将会话1下的SQL改为"select /*A*/ name from acct where acctno=:1"，会话2下的SQL改为"select /*B*/ name from acct where acctno=:1"，会话3下的SQL改为"select /*C*/ name from acct where acctno=:1"等等。这样就可以降低该SQL的并发性。
增加cpu或购买更高配置服务器
5.17 cursor pin: S wait on X
等待事件说明：
当一个会话尝试以共享模式（S - Share）来获得一个游标(cursor)下的mutex pin时，其他会话正在以排他模式(X - exclusive)持有同一个游标下的mutex pin，此时该会话需要等待另一个会话释放X模式的mutex pin，这个等待事件即为”cursor: pin S wait on X”。
参数说明：
版本 (10.2.0.1 - 11.2.0.2)
P1 = idn
P2 = value
P3 = where (where|sleeps in 10.2)
idn: Mutex号,与等待mutex的SQL的hash_value 相同。可以通过IDN查询到该条SQL的信息
SELECT sql_id, sql_text, version_count
FROM V$SQLAREA where HASH_VALUE=&IDN;
如果SQL_TEXT列显示的为"table_x_x_x_x"格式的值，这是一种特殊的内部游标—伪游标(Pseudo cursor)。可通过以下语句查询该SQL涉及到的对象
select * from h$pseudo_cursor where sql_id='&sql_id';
P1raw是p1的十六进制值，可以用p1raw值去搜索tracefile里的sql hash value值。
value: mutex值，包含两部分。
高阶位(high order bits)包含持有mutex的会话号
低阶位(low order bits)包含reference count(如果存在X模式的持有者，该值为0)
对于32位平台，阻塞者的会话号(high order bits)为4字节的前两个字节；对于64位平台，为8字节的前4个字节。可通过p2值，用语句查询X模式持有者的会话号：
SELECT decode(trunc(&&P2/4294967296),
0,trunc(&&P2/65536),
trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
FROM dual;
P2raw是p2的十六进制值，可以手动拆分p2raw值然后转换为十进制 ，来获取阻塞者的会话号(blocking_session)。比如，对一个64位的平台，查询出的p2raw为：
P2RAW
----------------
0000001F00000000
前八位0000001F转换为十进制为31，即为blocking_session。
Where: 申请mutex的代码位置。P3的高阶位(high order bits)是一个数字，表示Oracle代码中请求mutex的位置。在10.2版本中低阶位(low order bits) 给出睡眠时间，在11g中 low order bits全部为0。
警告：在10.2版本中，低阶位睡眠值可能溢出到高阶位，特别是在32位平台上，给出了错误的位置值
用一下语句P3的高阶位可以映射出位置名称
SELECT decode(trunc(&&P3/4294967296),
0,trunc(&&P3/65536),
trunc(&&P3/4294967296)) LOCATION_ID
FROM dual;
然后使用location_id查询出位置名称：
SELECT MUTEX_TYPE, LOCATION
FROM x$mutex_sleep
WHERE mutex_type like 'Cursor Pin%'
and location_id=&&LOCATION_ID;
由于mutex只加在本地实例上，所以对rac来说，阻塞者在本地实例上。
收集信息：
收集发生”cursor: pin S wait on X”事件的时间段的AWR报告/ADDM报告，并收集一份正常的报告作为基线
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
收System state dump。有时System state dump是必要的，以匹配已知问题。例如，如果AWR中没有明显的候选SQL，则在system state中捕获持有者或服务器进程可以让您专注于潜在的问题。抓取当进程出现挂起在“cursor: pin S wait on X”等待时的system state。
(a) Non-Rac
sqlplus "/ as sysdba"
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
quit
(b) RAC
$ sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4
oradebug -g all dump systemstate 267
quit
错误堆栈(Errorstacks)。获取进程信息的另一种方法是使用errorstack。 假设您可以识别一个阻塞者，采取错误堆栈将提供与系统状态大致相同的信息，但是跟踪的磁盘空间大大减少。 一旦找到了阻塞程序的ospid，就可以生成一个错误堆栈：
$ sqlplus
SQL> oradebug setospid
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
exit
其他途径查询阻塞者：
11g(及以上版本)可以通过视图v$session的blocking_session查询阻塞者的会话号：
SELECT SQL_ID, ACTION, BLOCKING_SESSION, BLOCKING_SESSION_STATUS
FROM v$session
WHERE SID=&SID_OF_WAITING_SESSION;
10g,11g版本均可通过p2值的高阶位来计算阻塞者的会话号。
查询历史阻塞，可查询视图V$ACTIVE_SESSION_HISTORY中的blocking_session
通过视图V$MUTEX_SLEEP_HISTORY查询mutex的历史信息，其中MUTEX_IDENTIFIER列与v$session_wait里的p1(idn)值相同
确定原因：
多个会话等待同一个持有者的同一个mutex
这可能是由于硬解析耗时较长引起的。
找到阻塞者，并找出他们正在做的事情，花费很长时间并调查原因。
例如：如果许多会话发出相同的SQL语句，但由于某种原因，该语句需要花费相当长的时间来解析，因此许多并发会话可能会等待阻塞程序完成其解析。
多个会话等待同一个mutex，但是阻塞会话一直在改变
如果对于相同的P1（idn）值，阻塞会话频繁变化，则由于某种原因，SQL可能不会共享游标，那么多个会话必须构建新的游标。此时，可以查询该sql（具体参见p1）的version count是否过高。查看AWR报告的” Main Report”à” SQL Statistics”à” SQL ordered by Version Count”
针对version count高的sql从视图V$SQL_SHARED_CURSOR查询version count高的原因。
等待的mutex和会话均在变
该情况一般是由于shared pool 高负载或者是 shared pool大小设置过小导致的；
或者是由于cursor频繁的失效，或频繁被刷出内存，导致了大量会话并发对执行的大量SQL语句硬解析，从而导致该等待事件的发生。
此时，可以查询这段时间内的解析情况。查看AWR报告的”Load Profile”à”Parses”/”Hard Parses”
以及” Instance Efficiency Percentages (Target 100%)”à” Soft Parse %:”。
理想情况下软解析的比例应该接近100%。
如果使用了AMM或ASMM管理内存，则查询视图V$SGA_RESIZE_OPS，观察shared pool是否在shrink。
建议：
如果是由于解析时间过长引起的等待，尝试隔离该语句，通过加hint、outlines、plan management或其他途径来减少解析时间。
如果是由于version count过多引起的，可以减少对各个version的需求。在极端情况下，如果必须使用多个子游标，则可以通过使用不同客户端的SQL略有不同来减少争用。例如：过向SQL添加文字注释，以便每个会话（或一组会话）映射到不同的hash_value和不同的父游标。
如果是由于不同的SQL硬解析过多，对相似的SQL考虑使用绑定变量，将不同的SQL变成一条SQL，以减少硬解析次数。
如果未使用内存自动管理(AMM),而是单独设置shared_pool_size参数，则可适当增加shared pool大小。
如果使用AMM或ASMM内存管理，并且由于buffer cache扩展而导致shared pool收缩(shrink),则可以对参数shared_pool_size设置一个最小值。
建议数据库打到最新的补丁集。
5.18 log buffer space
等待事件说明：
欲向日志缓冲区(log buffer)写入重做日志时，如果日志缓冲区没有足够空间，等待日志缓冲区有足够的空间。
Lgwr周期性地从日志缓冲区写入重做日志文件中，使得那些日志缓冲区可以重复利用。该等待事件表示程序生成重做日志的速度比lgwr进程将其写入日志文件的速度快。
确认问题：
? 该事件的等待时间较长
? 某些会话，查询或作业等执行缓慢
? 参数Log_buffer 的值小于十分钟内产生的redo大小
? 'log buffer space' 等待事件占DB time的重要的一部分
确认原因：
从AWR报告里查看与I/O相关的等待事件（比如’log file parallel write’, ‘db file parallel write’等 ）的平均等待时间（Avg wait (ms)）
1. 如果’log file parallel write’的平均等待时间较长，则可能是由于redo file的I/O较慢，导致lgwr进程写入日志文件的速度较慢，从而导致’log buffer space’的产生。
2. 如果伴随着’db buffer space’事件，还有大量的’log file switch completion’等待事件，则需要分析log_buffer参数、redo log大小、redo log组数设置是否合理。
建议：
? 检查重做日志所在的文件系统的位置，以提高 IO 性能。
? 不要把重做日志放在 RAID 5
? 不要把重做日志放在 Solid State Disk (SSD)
? 虽然通常情况下，SSD 写入性能好于平均水平，他们可能会遇到写峰值,从而导致大量的增加'log file sync'等待
? 监控其他可能需要写到相同路径的进程，确保该磁盘具有足够的带宽，足以应付所要求的容量。如果不能满足,移动这些进程或 redo。
? 将datafile 打散在不同的磁盘上
? 适当增大log_buffer参数(注意：需考虑会不会增加’log file sync’事件，调整该参数需重启数据库)
? 增加redo log file的大小
? 增加redo log file的group数目
5.19 log file sync
等待事件说明：
当一个用户会话(前台进程)提交(commit)或回滚(roll back),该会话的所有redo信息需要被刷进redo logfile里。此时，用户会话将通知lgwr进程将redo buffer里的信息刷到redo logfile中。当lgwr进程完成任务后，将会返回信息给用户会话。Log file sync等待事件就是用户会话等待lgwr进程完成将redo buffer里的信息刷入redo logfile之后，返回信息给用户会话的过程。
该等待事件可以被分解为以下部分：
1. 唤醒空闲的lgwr进程
2. Lgwr进程收集需要写入磁盘的redo信息，并发出I/O
3. 完成写入
4. Lgwr进程I/O通知处理
5. 写入完成后，lgwr进程通知用户会话(前台进程)
6. 唤醒用户会话(前台进程)
收集信息：
? 相似时间段、未发生’log file sync’等待事件的AWR报告，用来作为比较的基线性能报告
? 'log file sync' 等待事件发生时间段的AWR报告
? Lgwr trace文件
如果'log file parallel wait'这个事件的等待时间过长，会将告警信息记录在lgwr trace文件里。
? 'log file sync' 等待事件发生时间段的系统I/O性能数据
参数说明：
? P1 = buffer#
? P2 = Not used / sync scn
? P3 = Not used
buffer#
将到此buffer号(redo log buffer)的所有改变刷新到磁盘里，以保证事务提交，即使实例crash掉，也能保证事务已提交。该事件就是等待LGWR进程刷新到此buffer号。
sync scn (10.2.0.5.0 及以上版本)
将此SCN号的值同步到磁盘里，该事件就是等待LGWR进程刷新到此SCN号。
确认原因：
从AWR报告里查看'log file sync'和'log file parallel wait'事件的平均等待时间（Avg wait (ms)）
1. 如果'log file sync'的时间消耗在'log file parallel write'上的比例高，那么大部分的等待时间是由于 I/O（等待 redo 写入）。应该检查 LGWR 在 IO 方面的性能。作为一个经验法则，'log file parallel write'平均时间超过 20 毫秒, 意味着 I/O 子系统可能有问题。
查看操作系统I/O 性能数据（iostat、sar –d等）
1) 如果磁盘性能指标数据很差(比如sar -d 中avwait和avserv值均很高)，则说明I/O确实有问题。此时，需要检查磁盘繁忙程度，以判断是由于磁盘繁忙引起的慢，还是由于存储本身出了问题（重点排查IO路径下的光纤线、SAN交换机、存储的报错和性能情况）。
2) 如果iostat或者sar –d没有性能问题，则可以truss(strace) lgwr进程，查看lgwr的系统调用情况，帮助确定时间消耗在什么地方，从而进一步分析问题（比如可以查看跟该函数有关的系统参数是否设置合理等）。
2. 如果'log file parallel write'的平均等待时间并不是很长，可查看lgwr的trace文件里是否有告警信息。尽管'log file parallel write'的平均等待时间可能在一个合理的区间范围内，在峰值时刻写操作时间还是可能会很长,进而影响’log file sync’的等待时间。（从10.2.0.4 开始如果写操作超过 500 毫秒我们会在 LGWR 的 trace 中写警告信息。这个阀值很高所以就算没有警告也不代表没有问题。）
注意：上面的峰值如果时间间隔的很远,可能不会对'log file parallel wait'有大的影响。 但是，如果有 100 个会话等待'log file parallel wait'完成，'log file sync'总等待可能就会很高，因为等待时间将被乘以会话的个数 100。因此，值得探讨日志写 IO 高峰的原因。
检查其他正在发生的可能会导致 LGWR 写磁盘峰值的操作
当 LGWR 进程慢的时候，对其进行 Truss（strace） 操作会帮助确定时间消耗在什么地方。
3. 如果确定与I/O没有关系(lgwr trace文件里没有告警，'log file parallel write'的平均等待时间在正常范围内)，则检查redo log file是否足够大。检查 alert日志文件，查看redo log切换的时间。
每次重做日志切换到下一个日志时，会执行'log file sync'操作，以确保下一个日志开始之前信息都写完。 标准建议是日志切换最多 15 至 20 分钟一次。 如果切换比这更频繁，那么将发生更多的'log file sync'操作，意味着更多的会话等待。
4. 如果’log file sync’的平均等待时间比’log file parallel write’高很多，这意味着大部分时间等待不是由于等待 redo 的写入，因而问题的原因不是 IO 慢导致。 剩余时间是 CPU 活动，而且是过多的 commit 导致的最常见的竞争。如果'log file sync'的平均等待时间低，但等待次数高，那么应用程序可能 commit 过于频繁。
比较 user commit/rollback 同 user calls 比值的平均值
在 AWR 或 Statspack 报告中，如果每次 commit/rollback 的平均 user calls（"user calls/(user commits+user rollbacks)"） 小于 30， 表明 commit 过于频繁
5. 如果既不是I/O问题，又不是提交过于频繁，或切换过于频繁，则极有可能是lgwr与前台进程通讯间出现了问题。
查看lgwr的trace文件是否有post/wait和polling机制之间的切换的日志。
ORACLE从11G开始，为lgwr写日志引入了polling机制，而在以前只有post/wait机制。
同时引入了一个隐含参数，"_use_adaptive_log_file_sync"，即在两个机制之间自适应的切换。在11.2.0.3以下，该参数的默认值为false,即只启用post/wait机制。
从11.2.0.3开始，该参数的默认值为true,即Oracle会在post/wait机制和polling机制自适应。
? Post/wait进制下，lgwr进程在写完log buffer中的改变向量后，立刻通知待commit的进程，因此log file sync等待时间短，但lgwr相对来说，负担要重一些。毕竟12C以下lgwr进程只有1个，当同时commit的进程比较多的时候，通知待commit的进程也是一种负担。
? Polling模式下，待commit的进程，通知lgwr进程进行写入操作后，会进入sleep环节，并在timeout后去看是否log buffer中的内容被写入了磁盘，lgwr进程不再单独通知待commit的进程写已经完成。Polling机制下，解放了一部分lgwr的工作，但是会带来待commit的进程长时间处于log file sync等待下。对于交易型的系统而言，该机制是极度不适用的！
在post/wait和polling机制之间的切换，ORACLE会记录到lgwr进程的trace当中。
当切换到polling模式下时，很容易引起log file sync等待而影响业务的响应时间！
建议：
针对I/O引起的’log file sync’ 建议：
? 检查重做日志所在的文件系统的位置，以提高 IO 性能。
? 不要把重做日志放在 RAID 5
? 不要把重做日志放在 Solid State Disk (SSD)
? 虽然通常情况下，SSD 写入性能好于平均水平，他们可能会遇到写峰值,从而导致大量的增加'log file sync'等待
? 监控其他可能需要写到相同路径的进程，确保该磁盘具有足够的带宽，足以应付所要求的容量。如果不能满足,移动这些进程或 redo。
? 确保 LOG_BUFFER 不要太大，一个非常大的 log_buffer 的不利影响就是刷新需要更长的等待时间。当缓冲区满了的时候，它必须将所有数据写入到重做日志文件。LGWR 将一直等待,直到最后的 I/O 完成。
针对redo log切换频繁，建议：增加redo logs的大小
针对user commits过多，建议：
? 如果有很多短事务，看是否可能把这些事务组合在一起，从而减少 commit 操作。 因为每一个 commit 都必须收到相关 REDO 已写到磁盘上的确认，额外的 commit 会显著的增加开销。虽然 Oracle 可以将某些 commit 组合在一起，通过事务的批处理来减少commit的总体数量还是可以带来非常有益的效果。
? 看看是否有操作可以使用 COMMIT NOWAIT 选项 （务必在使用前应明白语义）。
? 看看是否有操作可以安全地使用 NOLOGGING/ UNRECOVERABLE 选项完成。
针对11.2.0.3及以上版本自适应机制切换参数问题，建议：
关闭隐藏参数"_use_adaptive_log_file_sync"
alter system set "_use_adaptive_log_file_sync"=false sid='*';
5.20 read by other session
等待事件说明：
当会话1将数据块从磁盘读到缓冲池(buffer cache)中时，同时会话2也请求读取该数据块，会话2将等待会话1将该数据块完全读入内存中。此时会话2的等待事件即为read by other session。
该等待事件是oracle 10g(及以上版本)从oracle 9i的buffer busy waits中分离出来的。
收集更多信息：
1. 通过ADDM和ASH报告收集会话信息和SQL信息
2. 通过AWR中"Buffer Wait Statistics"部分查询造成该事件的更详细的信息
3. 查看AWR中表空间和数据文件信息中的"Buffer Waits"列
4. 检查该表空间，数据文件的I/O是否慢，或者当前I/O是否非常繁忙。
查询阻塞者：
SELECT SID mySID, blocking_session,
p1 "FILE#", p2 "BLOCK#", p3 "class#", row_wait_obj# OBJECT_ID
FROM v$session
WHERE event = 'read by other session'
AND STATE='WAITING';
其中，file# 等待会话请求的数据块，所在的数据文件的文件号；
Block# 等待会话请求的数据块的块号；
Class# 等待会话请求的数据块的种类：class 1 表明是数据块（data block），可能是表或索引； class 4 表明是段头(segment header)；class >=15 表明是回滚块(undo blocks)。
Object_id 等待的对象号。但是"ROW_WAIT_OBJ#"这一列查询出来的object_id不总是对的。可以通过文件号，块号确认等待对象名：
SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &FILE
AND &BLOCK BETWEEN block_id AND block_id + blocks – 1;
可能原因：
1. I/O慢。
确认原因：'read by other session'该事件占用了db time的大部分时间；
'read by other session'的平均等待时间过长（>40ms）;
2. 热块争用。
确认原因：伴随着’read by other session’等待事件，还有大量的其他热块等待事件；
多个会话同时访问同一张表或同一个索引。
解决方法：
1. I/O慢：a. 如果文件系统繁忙，大量的活动文件占用了I/O带宽，则尽量将活动的文件打散在不同的磁盘上；
b. 如果是硬件问题，则维修或更换硬件；
c. 如果是开并行(Parallel Execution)时，由于僵尸进程太多导致I/O子系统饱和，则尽量根据需求调整并行个数。
5.21 gc buffer busy
等待事件说明
gc buffer busy是RAC数据库中常见的等待事件，11g开始gc buffer busy分为gc buffer busy acquire和gc buffer busy release。
gc buffer busy acquire是当session#1尝试请求访问远程实例(remote instance) buffer，但是在session#1之前已经有相同实例上另外一个session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy acquire。
gc buffer busy release是在session#1之前已经有远程实例的session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy release。
参数说明
该等待事件包含三个参数：
file# ：请求的数据块所在数据文件的文件号。
block#：请求的数据块号。
class#：请求的块的种类。class 1 表明是数据块（data block），可能是表或索引； class 4 表明是段头(segment header)；class >=15 表明是回滚块(undo blocks)。
SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name like 'gc buffer busy%';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
96 gc buffer busy acquire file# block# class#
97 gc buffer busy release file# block# class#
确定问题
AWR
在收集到的问题时刻的AWR报告里，gc buffer busy acquire/release等待排在top5等待的前一两位，并占用大量的DB Time，一般在20%以上就属于比较严重的情况。问题就可以锁定在gc buffer busy等待上。
ASH & v$session
根据查询的问题时刻的v$session或者ash信息，在非idle等待中，所有会话等待最多的就是gc buffer busy等待事件。
数据收集及分析
AWR报告
收集到问题时刻的awr报告。 需要关注awr这样一些模块的信息：
AWR-> Segments by Global Cache Buffer Busy
AWR-> SQL Statistics
v$session & ash
查看视图v$session或者v$active_session_history或者dba_hist_active_sess_history
确定原因/解决方法
热点块(hot block)
在AWR中Segments by Global Cache Buffer Busy 记录了访问频繁的gc buffer。
解决方法：
可以根据热点块的类型采取不同的解决方法
如果等待的是数据块（表或索引）可采取分区表，分区索引，反向index，增大pctfree等等，来将数据打散在不同的块里，以减少同时访问一个块的可能。
如果等待的是undo块，建议使用undo自动管理方式，或增加回滚段。
如果等待的是段头块，那么这种情况一般是并发insert语句的情况下，多个session希望插入数据到同一个块里造成的。可以在大量insert之前预先分配extent。
低效SQL语句
低效SQL语句会导致不必要的buffer被请求访问，增加了buffer busy的机会。在AWR中可以找到TOP SQL。
解决方法
可以优化SQL语句减少buffer访问。
数据交叉访问。
RAC数据库，同一数据在不同数据库实例上被请求访问。
解决方法
如果应用程序可以实现，那么我们建议不同的应用功能/模块数据分布在不同的数据库实例上被访问，避免同一数据被多个实例交叉访问，可以减少buffer的争用，避免gc等待。
Oracle bug
建议安装Oracle推荐的最新Patch Set和PSU。
5.22 gc cr request
等待事件说明
当前会话发出一个CR块请求给本地节点的LMS进程，但是本地节点没有该cr块，申请者节点的LMS进程发送请求给主节点， 但是主节点的LMS进程还没有响应它的请求。该等待事件结束于所有的排队等待该块的请求拿到该块或该块的权限。
根据远程实例是否包含需要的块，本地实例接受的是：
CR块 会导致"global cache cr block received"增加
grant 会导致 "global cache gets"增加
当前块(current block) 会导致"global cache current blocks received" 增加
这是一个placeholder等待事件，因为真正的消息传输和数据传输还没有开始。
所谓的placeholder等待事件是指一个请求处于中间阶段，还没有收到任何的消息反馈，而当收到消息反馈之后，就会开始等待一个真正的等待事件，所以placeholder等待事件是指用户或后台进程想要做一件事件，但这件事情还没有被LMS进程相应，真正的消息和数据块交互过程还没有开始。
一般来说，placeholder等待事件没有意义，因为实际等待的时间被记录到另一个等待事件上。如果有0.5秒的等待(windows平台上是6秒)没有响应时，前台进程会将宣布该块已遗失，占一次db block lost等待事件，然后重新发送该消息。
参数说明
该等待事件包含三个参数：
file# ：请求的数据块所在数据文件的文件号。
block#：请求的数据块号。
class#：请求的块的种类。
class 1 表明是数据块（data block），可能是表或索引；
class 4 表明是段头(segment header)；
class >=15 表明是回滚块(undo blocks)。
SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name like 'gc cr request%';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
155 gc cr request file# block# class#
5.23 gc cr block 2-way
等待事件说明
该等待事件表示当前进程通过一个2路通信，向远程实例申请了一个CR块，而这个申请请求在整个申请过程中并没有出现过超时。
确定问题
AWR报告
在收集到的问题时刻的AWR报告里，"gc cr block 2-way" 等待排在top5等待的前一两位，并占用大量的DB Time，一般在20%以上就属于比较严重的情况。问题就可以锁定在"gc cr block 2-way"等待上。
ASH & v$session
根据查询的问题时刻的v$session或者ash信息，在非idle等待中，所有会话等待最多的就是"gc cr block 2-way"等待事件。
数据收集及分析
确定原因/解决方法
5.24 gc cr block busy
一个会话在申请访问cr块时，发现该块正在忙(在远程实例或当前实例)。
忙的可能原因有：
1） 该块被pin住
2） 该块被其他会话持有
3） 该块在等待远程实例redo flush
4） 同一个实例上的会话正在访问该块(从远程实例传过来的块)，并且该块正处于一个事务当中，当前会话需要等待该块被释放
该等待事件包含3部分时间(cr block build time+flush time+send time)
flush time 是Oracle为了保证Instance Recovery实例恢复机制，而要求每一个current block在本地节点local instance被修改后(modify/update) 必须要将该current block相关的redo 写入到logfile 后（要求LGWR必须完成写入后才能返回)，才能由LMS进程传输给其他节点使用。
确定问题
AWR报告
在收集到的问题时刻的AWR报告里，"gc cr block busy" 等待排在top5等待的前一两位，并占用大量的DB Time，一般在20%以上就属于比较严重的情况。问题就可以锁定在"gc cr block busy"等待上。
ASH & v$session
根据查询的问题时刻的v$session或者ash信息，在非idle等待中，所有会话等待最多的就是"gc cr block busy"等待事件。
数据收集及分析
AWR报告
收集到问题时刻的awr报告。 需要关注awr这样一些模块的信息：
AWR-> Report Summary -> RAC Statistics -> Global Cache and Enqueue Services - Workload Characteristics -> Avg global cache cr block build time (ms) 和 Avg global cache cr block flush time (ms)
AWR -> Wait Events Statistics -> Foreground Wait Events -> log file sync
v$instance_cache_transfer
如果没有AWR信息，则可查看v$instance_cache_transfer视图下与cr block相关的列的信息。
远程实例lgwr进程的跟踪日志文件
查看lgwr的trace文件里是否有告警信息。
远程实例的alert日志和dbwr的跟踪日志文件
查看远程实例dbwr的性能和checkpoint incomplete次数
确定原因/解决方法
从应用程序的角度来讲，出现gc cr block busy 的原因可能有：
热点块(hot block)
在AWR中Segments by Global Cache Buffer Busy 记录了访问频繁的gc buffer。
解决方法：
可以根据热点块的类型采取不同的解决方法
如果等待的是数据块（表或索引）可采取分区表，分区索引，反向index，增大pctfree等等，来将数据打散在不同的块里，以减少同时访问一个块的可能。
如果等待的是undo块，建议使用undo自动管理方式，或增加回滚段。
如果等待的是段头块，那么这种情况一般是并发insert语句的情况下，多个session希望插入数据到同一个块里造成的。可以在大量insert之前预先分配extent。
对于相同表的大并发量的DML语句
解决方法
对于带有主键索引（或唯一索引）表的大量并发insert
解决方法
Oracle bug
建议安装Oracle推荐的最新Patch Set和PSU。
5.25 gc current block 2-way
等待事件说明
该等待事件表示当前进程通过一个2路通信，向远程实例申请了一个当前块，而这个申请请求在整个申请过程中并没有出现过超时。
确定问题
AWR报告
在收集到的问题时刻的AWR报告里，"gc current block 2-way" 等待排在top5等待的前一两位，并占用大量的DB Time，一般在20%以上就属于比较严重的情况。问题就可以锁定在"gc current block 2-way"等待上。
ASH & v$session
根据查询的问题时刻的v$session或者ash信息，在非idle等待中，所有会话等待最多的就是"gc current block 2-way"等待事件。
数据收集及分析
AWR报告
收集到问题时刻的awr报告。 需要关注awr这样一些模块的信息：
AWR-> Report Summary -> RAC Statistics -> Global Cache and Enqueue Services - Workload Characteristics -> Avg global cache current block pin time (ms) 和 Avg global cache current block flush time (ms)
AWR -> Wait Events Statistics -> Foreground Wait Events -> log file sync
v$instance_cache_transfer
如果没有AWR信息，则可查看v$instance_cache_transfer视图下与current block相关的列的信息。
远程实例lgwr进程的跟踪日志文件
远程实例dbwr的性能和checkpoint incomplete次数
确定原因/解决方法
低效SQL语句
低效SQL语句会导致不必要的buffer被请求访问，增加了buffer busy的机会。在AWR中可以找到TOP SQL。
解决方法
可以优化SQL语句减少buffer访问。
数据交叉访问。
RAC数据库，同一数据在不同数据库实例上被请求访问。
解决方法
如果应用程序可以实现，那么我们建议不同的应用功能/模块数据分布在不同的数据库实例上被访问，避免同一数据被多个实例交叉访问，可以减少buffer的争用，避免gc等待。
网络通讯问题
5.26 gc current block busy
等待事件说明
一个会话在申请访问当前块时，发现该块正在忙(在远程实例或当前实例)。
忙的可能原因有：
1） 该块被pin住
2） 该块被其他会话持有
3） 该块在等待远程实例redo flush
4） 同一个实例上的会话正在访问该块(从远程实例传过来的块)，并且该块正处于一个事务当中，当前会话需要等待该块被释放
该等待事件包含3部分时间(pin time+flush time+send time)
flush time 是Oracle为了保证Instance Recovery实例恢复机制，而要求每一个current block在本地节点local instance被修改后(modify/update) 必须要将该current block相关的redo 写入到logfile 后（要求LGWR必须完成写入后才能返回)，才能由LMS进程传输给其他节点使用。
确定问题
AWR报告
在收集到的问题时刻的AWR报告里，"gc current block busy" 等待排在top5等待的前一两位，并占用大量的DB Time，一般在20%以上就属于比较严重的情况。问题就可以锁定在"gc current block busy"等待上。
ASH & v$session
根据查询的问题时刻的v$session或者ash信息，在非idle等待中，所有会话等待最多的就是"gc current block busy"等待事件。
数据收集及分析
AWR报告
收集到问题时刻的awr报告。 需要关注awr这样一些模块的信息：
AWR-> Report Summary -> RAC Statistics -> Global Cache and Enqueue Services - Workload Characteristics -> Avg global cache current block pin time (ms) 和 Avg global cache current block flush time (ms)
AWR -> Wait Events Statistics -> Foreground Wait Events -> log file sync
v$instance_cache_transfer
如果没有AWR信息，则可查看v$instance_cache_transfer视图下与current block相关的列的信息。
远程实例lgwr进程的跟踪日志文件
查看lgwr的trace文件里是否有告警信息。
远程实例的alert日志和dbwr的跟踪日志文件
查看远程实例dbwr的性能和checkpoint incomplete次数
确定原因/解决方法
热点块(hot block)
在AWR中Segments by Global Cache Buffer Busy 记录了访问频繁的gc buffer。
解决方法：
可以根据热点块的类型采取不同的解决方法
如果等待的是数据块（表或索引）可采取分区表，分区索引，反向index，增大pctfree等等，来将数据打散在不同的块里，以减少同时访问一个块的可能。
如果等待的是undo块，建议使用undo自动管理方式，或增加回滚段。
如果等待的是段头块，那么这种情况一般是并发insert语句的情况下，多个session希望插入数据到同一个块里造成的。可以在大量insert之前预先分配extent。
对于相同表的大并发量的DML语句
解决方法
对于带有主键索引（或唯一索引）表的大量并发insert
解决方法
Oracle bug
建议安装Oracle推荐的最新Patch Set和PSU。
