10 使用性能视图调优

 

10.2.3表的等待事件及其潜在的原因

Wait Event	General Area	Possible Causes	Look for / Examine
buffer busy waits	Buffer cache, DBWR	Depends on buffer type. For example, waits for an index block may be caused by a primary key that is based on an ascending sequence.	Examine V$SESSION while the problem is occurring to determine the type of block in contention.
free buffer waits	Buffer cache, DBWR, I/O	Slow DBWR (possibly due to I/O?) Cache too small	Examine write time using operating system statistics. Check buffer cache statistics for evidence of too small cache.
db file scattered read	I/O, SQL statement tuning	Poorly tuned SQL Slow I/O system	Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.
db file sequential read	I/O, SQL statement tuning	Poorly tuned SQL Slow I/O system	Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.
enqueue waits (waits starting with enq: )	Locks	Depends on type of enqueue	Look at V$ENQUEUE_STAT .
library cache latch waits: library cache , library cache pin , and library cache lock	Latch contention	SQL parsing or sharing	Check V$SQLAREA to see whether there are SQL statements with a relatively high number of parse calls or a high number of child cursors (column VERSION_COUNT ). Check parse statistics in V$SYSSTAT and their corresponding rate for each second.
log buffer space	Log buffer, I/O	Log buffer small Slow I/O system	Check the statistic redo buffer allocation retries in V$SYSSTAT . Check configuring log buffer section in configuring memory chapter. Check the disks that house the online redo logs for resource contention.
log file sync	I/O, over- committing	Slow disks that store the online logs Un-batched commits	Check the disks that house the online redo logs for resource contention. Check the number of transactions (commits + rollbacks ) each second, from V$SYSSTAT .

 

10.2.4与解析相关的统计信息

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ( 'parse time cpu', 'parse time elapsed',
'parse count (hard)', 'CPU used by this session' );

1.比值parse time CPU / parse time elapsed越接近于1，表明解析过程中在等待高争用资源时的时间越少。

2.比值parse time CPU / CPU used by this session越接近于0，表明CPU用于解析SQL越少。

 

10.3 等待事件统计信息

1.SQL*Net Events

2.buffer busy waits

3.db file scattered read

4.db file sequential read

5.direct path read and direct path read temp

6.direct path write and direct path write temp

7.enqueue waits

8.events in wait class other

9.free buffer waits

在单CPU而无法使用多DBWR（通过系统变量DB_WRITER_PROCESSES 设置，参数类型：integer ），且异步（asynchronous）I/O不可用的情况下，多个I/O从进程（mutiple I/O slaves，通过系统变量DB_IO_SLAVES 设置，参数类型：integer ）是很有用的解决办法。I/O从进程只能用于单DBWR进程下。

一般DBWR遇到瓶颈问题时，首先考虑的是系统是否支持异步I/O，在肯定的情况下设置异步I/O（通过系统变量DISK_ASYNCH_IO 设置，参数类型：boolean ，可选值：true、false），然后观察问题是否得到缓解。其次，如果系统在异步I/O不可用，或者异步I/O已经设置时，DBWR仍存在瓶颈的情况下，那么再考虑使用设置多DBWR进程来解决。最后，仅当无法配置多DBWR进程的情况下，使用I/O slaves

10.latch events

Table 10-2 Latch Wait Event

Latch	SGA Area	Possible Causes	Look For:
Shared pool, library cache	Shared pool	Lack of statement reuse Statements not using bind variables Insufficient size of application cursor cache Cursors closed explicitly after each execution Frequent logon/logoffs Underlying object structure being modified (for example truncate) Shared pool too small	Sessions (in V$SESSTAT ) with high: parse time CPU parse time elapse d Ratio of parse count (hard) / execute coun t Ratio of parse count (total) / execute count Cursors (in V$SQLAREA /V$SQLSTATS ) with: High ratio of PARSE_CALLS / EXECUTIONS EXECUTIONS = 1 differing only in literals in the WHERE clause (that is, no bind variables used) High RELOADS High INVALIDATIONS Large (> 1mb) SHARABLE_MEM
cache buffers lru chain	Buffer cache LRU lists	Excessive buffer cache throughput. For example, inefficient SQL that accesses incorrect indexes iteratively (large index range scans) or many full table scans DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer Cache may be too small	Statements with very high logical I/O or physical I/O, using unselective indexes
cache buffers chains	Buffer cache buffers	Repeated access to a block (or small number of blocks), known as a hot block	Sequence number generation code that updates a row in a table to generate the number, rather than using a sequence number generator Index leaf chasing from very many processes scanning the same unselective index with very similar predicate Identify the segment the hot block belongs to
row cache objects

通过以下语句查询有可能未进行绑定变量的SQL

1. SELECT SQL_TEXT
  FROM V$SQL
 WHERE EXECUTIONS < 4
 ORDER BY SQL_TEXT;

2.SELECT SUBSTR(SQL_TEXT, 1, 60), COUNT(*)
  FROM V$SQL
 WHERE EXECUTIONS < 4
 GROUP BY SUBSTR(SQL_TEXT, 1, 60)
 HAVING COUNT(*) > 1;
执行次数（EXECUTIONS）越少，SQL越相似，未进行绑定变量的可能性越大。

3.SELECT SQL_TEXT FROM V$SQL WHERE PLAN_HASH_VALUE IN
  (SELECT PLAN_HASH_VALUE
     FROM V$SQL
    GROUP BY PLAN_HASH_VALUE HAVING COUNT(*) > 4)
  ORDER BY PLAN_HASH_VALUE;

以上当PARSE_CALLS越接近 于 EXECUTIONS时，表明你在持续的重新解析这个SQL， PARSE_CALLS较高的SQL意味着需要优化了。

11.log file parallel write

12.library cache pin

13.library cache lock

14.log buffer space

一般原因是重做日志的产生速度快于LGWR从日志缓存写出至日志文件的速度。解决办法：一：是否log buffer太小。二：系统是否存在I/O瓶颈。

15.log file switch

一般发生于日志文件转换（一：归档需求。二：检测点不完整）

16.log file sync

一、如果平均等待时间低，而等待次数较高时，查看应用程序中是否每次INSERT后执行了commit操作，使用批量提交（batching COMMITs）可降低等待次数。

二、如果平均等待时间较高，检查何问题造成大量的等待时间，例如，是缓慢的I/O，则可尝试以下办法解决。

1.减少重做日志日志文件所在硬盘的I/O操作，或者为重做日志更换专用硬盘。

2.为归档日志备用不通的磁盘，使日志写进程归档操作所带来的的影响最小华。

3.将重做日志转移至更快的磁盘设备或者I/O子系统（例如，将磁盘阵列RAID5换为RAID1方式）

4.考虑使用裸设备（或者磁盘厂商所提供的模拟裸设备）提高写入速度。

5.具体应用程序中，采用批量每N行数据提交，替代每行数据提交的做法。

17.rdbms ipc reply

等待一个后台进程的回复

 

10.4空闲等待进程

Table 10-3 Idle Wait Events

Wait Name	Background Process Idle Event	User Process Idle Event	Parallel Query Idle Event	Shared Server Idle Event	Oracle Real Application Clusters Idle Event
dispatcher timer	.	.	.	X	.
pipe get	.	X	.	.	.
pmon timer	X	.	.	.	.
PX Idle Wait	.	.	X	.	.
PX Deq Credit: need buffer	.	.	X	.	.
rdbms ipc message	X	.	.	.	.
smon timer	X	.	.	.	.
SQL*Net message from client	.	X	.	.	.
virtual circuit status	.	.	.	X	.

 

查询某个会话的各个等待事件

1.select  a.sid,
       a.event,
       a.time_waited,
       round(a.time_waited/c.sum_time_waited* 100  ,  2 ) ||  '%'  pct_wait_time,
       round(( sysdate  - b.LOGON_TIME) *  24 ) hours_connected
   from  v$session_event a,
       v$session b,
       ( select  sid,  sum (time_waited) sum_time_waited
           from  v$session_event
        
          where  event  not   in  ( 'null event' ,  'SQL*Net message to client' ,
                 'pmon timer' ,  'pipe get' ,  'smon timer' ,  'jobq slave wait' ,
                 'rdbms ipc message' ,  'rdbms ipc reply' ,  'PX Deq: Join ACK' ,
                 'PX Deq: Signal ACK' ) 
         having   sum (time_waited) >  0    -- 对group by 产生结果的挑选
          group   by  sid) c
  where  a.sid = b.sid
    and  a.sid = c.sid
    and  a.TIME_WAITED >  0
    and  a.sid = &1
  order   by  hours_connected  desc , pct_wait_time

2.查询各会话等待事件

select sid,event,p1,p1text from v$session_wait;

3.查询具体会话运行中的sql语句

SELECT   sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE b.SID = &sid)
ORDER BY piece ASC