11g中direct path read事件等待很高的一个案例

转自 http://blog.itpub.net/7839206/viewspace-1031102/

在11g中，全表扫描可能使用direct path read方式，绕过buffer cache，这样的全表扫描就是物理读了．最近遇到了这样一个测试环境的案例，表现为direcy path read事件等特很高．

[@more@]

主机性能数据

Operating System Statistics - Detail

Snap Time	Load	%busy	%user	%sys	%idle	%iowait
22-1月 18:00:28	0.00
22-1月 19:00:45	0.00	1.56	1.20	0.36	0.00	98.44

 

Cpu的的sys和user调用都比较低，cpu主要是消耗在io的等待上。

数据库性能

性能情况通过主要通过AWR报告来体现. 后台的job每1小时对数据库的性能数据进行采样，本报告中选取了系统工作峰值的两个时间段进行分析，时间分别是： 系统负载

 

 

系统负载数据，主要反映数据库的压力情况：

Load Profile

 

从以上数据可以得出以下二点结论：

1. 数据库产生的Redo size很小，说明数据的修改和插入非常少;一小时仅仅产生2.2*3600=7920k日志。说明数据库的性能瓶颈不在于Insert和Update的处理上,经过了解这个库本身为测试用。

 

1. 物理读很高，物理读就是直接从磁盘读取的数据块数，由于磁盘速度与内存速度的差异，物理读的速度是很低的;通过统计信息中的physical read total bytes(物理读字节数)可计算出每秒从磁盘读取的io量,相关的统计信息如下：

physical IO disk bytes	153,422,859,264	42,421,637.91	3,692,754.21
physical read IO requests	800,647	221.38	19.27
physical read bytes	153,040,289,792	42,315,856.91	3,683,546.10
physical read total IO requests	805,412	222.70	19.39
physical read total bytes	153,165,194,752	42,350,393.31	3,686,552.45

 

每秒磁盘读IO = 153,165,194,752/3600/1024/1024=40.7M

以40.7M/s的速度从磁盘读取数据，可能是造成数据库性能下降的原因之一。

数据库实例性能命中率

以下列出的是数据库实例性能的各项的命中率，它们的最佳值是100%

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	99.99
Buffer Hit %:	99.22	In-memory Sort %:	100.00
Library Hit %:	93.90	Soft Parse %:	91.50
Execute to Parse %:	60.56	Latch Hit %:	100.00
Parse CPU to Parse Elapsd %:	0.00	% Non-Parse CPU:	99.21

Shared Pool Statistics

 

• Buffer NowWait和Buffer Hit都在99%以上，说明目前系统中的数据库高速缓存基本够用。

 

• Redo Nowait在99%以上，表示参数log_buffer 的设置满足系统要求。
• In-mem sort 为100%，表示不存在磁盘排序。
• Soft Parse在90%以上，Exe-to-parse 为负值，说明应用系统中的sql的有一定的共享性，但shared pool 偏小了一些，导致共享池紧张的原因可能是采用连接池，不使用常连接的原因。
  • Memory Usage %在80%左右，说明共享池相对比较紧张

 

共享池相对比较紧张，由于是开启了SGA自动管理，由于Oracle分配sga的各个部分，这部分的设置不必过分关注，但为了解决后面提及的全表扫描问题，可能需要做一些调整。

等待事件(Top Wait Events)

以下列出的数据库主要的等待事件

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
direct path read	790,366	7,376	9	92.45	User I/O
DB CPU		455		5.70
db file sequential read	8,342	97	12	1.21	User I/O
db file scattered read	259	5	20	0.06	User I/O
enq: KO - fast object checkpoint	1,271	4	3	0.05	Application

 

从上述信息发现占总的等待时间92.45%的事件是direct path read 。

指标：direct path read较高的可能原因有：

1. 大量的磁盘排序操作，无法在排序区中完成排序，需要利用temp表空间进行排序.
2. 大量的Hash Join操作，利用temp表空间保存hash区。
3. SQL语句的并行处理
4. 大表的全表扫描，在Oracle11g中，全表扫描的算法有新的变化，根据表的大小、高速缓存的大小等信息，决定是否绕过SGA直接从磁盘读取数据。而10g则是全部通过高速缓存读取数据，称为table scan(large)。11g认为大表全表时使用直接路径读，可能比10g中的数据文件散列读(db file scattered reads)速度更快，使用的latch也更少。

 

从In-memory Sort来看，内存排序的比率为100%，不存在磁盘排序，排除了原因1.在NECHIS中也没有使用并行sql的特性，可能原因就只有是2和4了。我们继续在统计信息中发现了有关全表扫描的统计信息：

table scans (direct read)	1,272	0.35	0.03
table scans (long tables)	1,273	0.35	0.03
table scans (rowid ranges)	0	0.00	0.00
table scans (short tables)	25,637	7.09	0.62

从这里可以看到，1273个"大"表扫描，1272个都使用了直接路径读取，由此可基本判断，是由于全表扫描引起的大量的direct path read等待.

 

TOP sql分析

由于前面的判断是物理读很高引起了数据库的性能问题，需要关注引起大量物理读的SQL语句,以下列出的是Physical reads 最高的SQL语句:

SQL ordered by Reads

 

 

Physical Reads	Executions	Reads per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
13,003,306	698	18,629.38	69.60	237.48	5397.86	124pcd3sy5dfp		SELECT pub_patient_arch.pbirth...
5,658,569	193	29,319.01	30.29	120.50	2288.77	fyg2nqd39pqry		Select Pub_Patient_Arch.Pbirth...
2,019	28	72.11	0.01	0.16	8.05	fukcc6ykr4q1j		SELECT Id , Parentid , Icon ...
1,522	1	1,522.00	0.01	0.05	3.59	cfz686a6qp0kg		select o.obj#, u.name, o.nam...
1,467	60	24.45	0.01	0.47	8.94	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
1,082	137	7.90	0.01	0.25	10.15	1pp1juwymxsxs		UPDATE Resourceinfo ...
981	107	9.17	0.01	0.09	3.56	fyqgj0bjk6b6m		<span style="#0000