oracle数据库访问超高延时,11g中direct path read事件等待很高的一个案例

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

主机性能数据

Operating System Statistics - Detail

Snap TimeLoad%busy%user%sys%idle%iowait

22-1月18:00:280.00

22-1月19:00:450.001.561.200.360.0098.44

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

数据库性能

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

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

Load Profile

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

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

physical IO disk bytes153,422,859,26442,421,637.913,692,754.21

physical read IO requests800,647221.3819.27

physical read bytes153,040,289,79242,315,856.913,683,546.10

physical read total IO requests805,412222.7019.39

physical read total bytes153,165,194,75242,350,393.313,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.00Redo NoWait %:99.99

Buffer Hit %:99.22In-memory Sort %:100.00

Library Hit %:93.90Soft Parse %:91.50

Execute to Parse %:60.56Latch 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)

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

EventWaitsTime(s)Avg wait (ms)% DB timeWait Class

direct path read790,3667,376992.45User I/O

DB CPU4555.70

db file sequential read8,34297121.21User I/O

db file scattered read2595200.06User I/O

enq: KO - fast object checkpoint1,271430.05Application

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

指标：direct path read较高的可能原因有：大量的磁盘排序操作，无法在排序区中完成排序，需要利用temp表空间进行排序.

大量的Hash Join操作，利用temp表空间保存hash区。

SQL语句的并行处理

大表的全表扫描，在Oracle11g中，全表扫描的算法有新的变化，根据表的大小、高速缓存的大小等信息，决定是否绕过SGA直接从磁盘读取数据。而10g则是全部通过高速缓存读取数据，称为table scan(large)。11g认为大表全表时使用直接路径读，可能比10g中的数据文件散列读(db filescattered reads)速度更快，使用的latch也更少。

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

table scans (direct read)1,2720.350.03

table scans (long tables)1,2730.350.03

table scans (rowid ranges)00.000.00

table scans (short tables)25,6377.090.62

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

TOP sql分析

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

SQL ordered by Reads

Physical ReadsExecutionsReads per Exec%TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text

13,003,30669818,629.3869.60237.485397.86SELECT pub_patient_arch.pbirth...

5,658,56919329,319.0130.29120.502288.77Select Pub_Patient_Arch.Pbirth...

2,0192872.110.010.168.05SELECT Id , Parentid , Icon ...

1,52211,522.000.010.053.59select o.obj#, u.name, o.nam...

1,4676024.450.010.478.94DECLARE job BINARY_INTEGER := ...

1,0821377.900.010.2510.15UPDATE Resourceinfo ...

9811079.170.010.093.56