以往性能调整常用的一些指标：

l Buffer cache>90%

l Data dictionary misses<10%

l Sql area gethitratio and pinhitratio>90% (ratio of reloads<1%)

l willing-to-wait latch hit ratios shalt be close to 1

但是光靠这些指标是不够的，In reality,high cache-hit ratio doesn’t always mean good performance. The Oracle Database 10g Release 1 Performance Tuning Guide states：

When tuning, it is common to compute a ratio that helps determine whether there is a problem. Such ratios include the buffer cache-hit ratio, the soft-parse ratio, and the latch-hit ratio. These ratios should not be used as ‘hard and fast’ identifiers of whether there is or is not a performance bottleneck. Rather, they should be used as indicators. In order to identify whether there is a bottleneck, other related evidence should be examined.

Oracle从7.0.12开始推出OWI,当时有104个等待事件，到9i发展到400个，到10g已发展到800个。事实上，10g的Enterprise Manager Performance中已经不再有buffer cache hit ratio。

Oracle认为阻碍DBA们掌握OWI的关键在于文档的不足。

OWI是一种用于定位process bottlenecks（即wait evennts）的方式，包括I/O，locks,latches,bk process activities,network latencies等等。它记录了所有这些事件的等待次数和总的等待时间。解除或者降低这些等待都会使系统性能得到提高。这些数据都被记录在动态视图中。

有了OWI可以快速的定位问题，而在OWI之前，要定位问题必须将checklist上的所有项目都执行一遍，再根据经验判断问题所在，这往往浪费大量的时间而且容易产生错误。

Database Response Time Tuning Model

Response Time = Service Time + WaitTime

The service time is the amount of time a process spends on the CPU. The wait time is the amount of time a process waits for specific resources to be available before continuing with processing.

Response Time = CPU used when call started + S TIME_WAITED

可使用如下SQL来获取用户进程的response time,将得到的结果累加得到的就是该用户进程的response time：

select event, time_waited as time_spent

from v$session_event

where sid = &&sid

and event not in (

'Null event',

'client message',

'KXFX: Execution Message Dequeue - Slave',

'PX Deq: Execution Msg',

'KXFQ: kxfqdeq - normal deqeue',

'PX Deq: Table Q Normal',

'Wait for credit - send blocked',

'PX Deq Credit: send blkd',

'Wait for credit - need buffer to send',

'PX Deq Credit: need buffer',

'Wait for credit - free buffer',

'PX Deq Credit: free buffer',

'parallel query dequeue wait',

'PX Deque wait',

'Parallel Query Idle Wait - Slaves',

'PX Idle Wait',

'slave wait',

'dispatcher timer',

'virtual circuit status',

'pipe get',

'rdbms ipc message',

'rdbms ipc reply',

'pmon timer',

'smon timer',

'PL/SQL lock timer',

'SQL*Net message from client',

'WMON goes to sleep')

union all

select b.name, a.value

from v$sesstata, v$statname b

where a.statistic# = b.statistic#

and b.name ='CPU used when call started'

and a.sid = &sid;

There are three key behavioral changes that need to happen before you can master the OWI method:

l You must stop measuring performance using non-throughput related ratios, such as the BCHR, sorts-to-disk ratio, and so on.

l You need to start measuring process response time and/or throughput.

l You must look at resource consumption from the response time perspective.

原来的方式教DBA如何去查找消耗了大量CPU，IO以及buffer的SQL，但是这样的SQL调整事先并不清楚调整后会带来多大的改善，因为之前的统计都没有时间信息。而使用OWI则可以预测所作的改动将对SQL带来多大的影响。

ITPub博客

Oracle Wait Interface学习笔记(1) Introduction

最新文章