10 使用性能视图调优

来源：互联网发布：章燎原的学历知乎编辑：程序博客网时间：2024/04/30 09:37

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

Wait EventGeneral AreaPossible CausesLook 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

LatchSGA AreaPossible CausesLook 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 elapsed
Ratio of parse count (hard) / execute count
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 NameBackground Process Idle EventUser Process Idle EventParallel Query Idle EventShared Server Idle EventOracle Real Application Clusters Idle Event

dispatcher timer

pipe get

pmon timer

PX Idle Wait

PX Deq Credit: need buffer

rdbms ipc message

smon timer

SQL*Net message from client

virtual circuit status

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

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