Oracle Wait Interface学习笔记(1) Introduction

(OWI学习笔记索引)

 

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

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博客 ” ，链接：http://blog.itpub.net/55472/viewspace-425552/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-425552/