等待事件

1. 重要的等待事件

1-1. db file scattered read (数据文件离散读取)
与全表扫描或快速全索引扫描有关
一般是没有创建索引或没有合适的索引
归类于 UserI/O 等待

1-2. db file sequential read (数据文件顺序读取)
与读取索引块或通过索引读取数据块有关
归类于 UserI/O 等待

1-3. direct path read/write (直接路径读写), direct path read/write temp (直接路径读写临时数据文件)
直接路径读，直接读数据到 PGA ，不经过 SGA
与磁盘排序 IO，并行查询从属进程，预读处理有关
与直接路径加载，并行 DML，未缓存 LOB 段写入有关

1-4. log file switch (archiving needed) (日志切换需要归档)
日志组循环写满以后，在需要覆盖先前日志时发现归档尚未完成
有可能因为 IO 问题，归档进程写出缓慢，也有可能是日志组设置不合理
可以增大日志文件，增加日志组，移动归档文件到快速磁盘，调整 log_archive_max_processes 参数

1-5. log file switch (checkpoint incomplete) (日志切换检查点未完成)
日志组循环写满以后，在需要覆盖先前日志时发现数据库没完成写出此日志所保护的脏数据
dbwr 写出缓慢或 IO 问题
增加额外的dbwr，增加日志文件组或日志文件大小

1-6. log file syns (日志文件同步)
lgwr 进程写出效率低下或提交回滚过于频繁
提高 lgwr 性能，使用快速磁盘，使用批量提交，使用 nologging 和 unrecoverable 等选项

1-7. log file single write
与写日志文件头块有关，很少出现

1-8. log file parallel write
lgwr 将日志从 log buffer 写出到日志文件时写出是并行的 (多个日志成员)，全部完成后此操作才会完成

1-9. log buffer space
日志产生速度快于 lgwr 写出速度，或日志切换太慢，或 IO 瓶颈
增加日志文件大小或缓冲区大小，更换高速磁盘(裸设备)
不要将日志文件于数据文件方在一起，尽量用 raid10 而不是 raid5 来存储日志文件

1-10. enqueque (队列等待)

1-11. latch free (latch 释放)


2. 相关视图

2-1. v$session
数据库当前连接的会话信息，随会话消失而消失

2-2. v$session_wait
数据库当前连接的会话正在等待的资源或事件信息，随会话消失而消失

2-3. v$session_wait_history
记录数据库活动会话近 10 次的等待的资源或事件信息

2-4. v$system_event
数据库启动以来所以等待事件的汇总信息

2-5. v$session_longops
长时间运行 (超过 6 秒) 的事务

2-6. v$segstat_name
段级统计信息收集信息


3. 相关查询脚本

3-1. 事件主要分类和个数
column wait_class format a20
select
    wait_class#,
    wait_class_id,
    wait_class,
    count(*)
from
    v$event_name
group by
    wait_class#,
    wait_class_id,
    wait_class
order by
    wait_class#
/

3-2. 等待事件的参数意义描述
# 不同事件的参数意义不同
column parameter1 format a20
column parameter2 format a20
column parameter3 format a20
select
    name,
    parameter1,
    parameter2,
    parameter3
from
    v$event_name
where
    name = 'db file scattered read'
/

3-3. 各类等待事件的等待时间和等待次数统计
select * from v$system_wait_class order by time_waited;

3-4. TOP10 等待事件
select
    *
from (
        select
            event,
            total_waits,
            time_waited
        from
            v$session_event
        order by time_waited desc
      )
where rownum < 10;

3-5. 查看当前发生等待的会话信息
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A10
COLUMN event FORMAT A20
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM   v$session_wait sw,
       v$session s
WHERE  s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC
/

3-6. 根据SID查找造成等待的SQL
select
    a.sql_id,
    a.sql_text
from
    v$sqltext a,
    v$session b
where
    a.hash_value = b.sql_hash_value and
    b.sid = '&sid'
order by a.piece
/

3-7. 活动 session 的近 10 次的等待信息
column event format a30
column p1text format a15
column p2text format a15
column p3text format a15
select
    event,
    p1text,
    p1,
    p2text,
    p2,
    p3text,
    p3,
    wait_time,
    wait_count
from
    v$session_wait_history
where
    sid = &sid
/

3-8. 根据等待事件获得执行计划
SET linesize 120
COL operation format a55
COL cost format 99999
COL kbytes format 999999
COL object format a25
SELECT
    hash_value,
    child_number,
    LPAD (' ', 2 * DEPTH)
    || operation
    || ' '
    || options
    || DECODE (ID, 0, SUBSTR (optimizer, 1, 6) || ' Cost=' || TO_CHAR (COST)) operation,
    object_name OBJECT,
    COST,
    ROUND (BYTES / 1024) kbytes
FROM
    v$sql_plan
WHERE
    hash_value IN
    (
        SELECT a.sql_hash_value
        FROM v$session a, v$session_wait b
        WHERE a.SID = b.SID
        AND b.event = '&waitevent'
    )
ORDER BY
    hash_value,
    child_number,
    ID
/

3-9. 获取全描扫描的对象
select distinct
    object_name,
    object_owner
from
    v$sql_plan
where
    operation='TABLE ACCESS' and
    options='FULL' and
    object_owner = 'SYS'
/

3-10. 查找全表扫描的 SQL
SELECT
    t.sql_id,
    t.sql_text
FROM
    v$sqltext t,
    v$sql_plan p
WHERE
    t.hash_value = p.hash_value AND
    p.operation = 'TABLE ACCESS' AND
    p.options = 'FULL' AND
    p.object_owner = 'SYS'
ORDER BY
    p.hash_value,
    t.piece
/

3-11. 获得全索引扫描的对象
select distinct
    object_name,
    object_owner
from
    v$sql_plan
where
    operation='INDEX' and
    options='FULL SCAN'
/

3-12. 查找全索引扫描的 SQL
SELECT
    t.sql_id,
    t.sql_text
FROM
    v$sqltext t,
    v$sql_plan p
WHERE
    t.hash_value = p.hash_value AND
    p.operation = 'INDEX' AND
    p.options = 'FULL SCAN'
ORDER BY
    p.hash_value,
    t.piece
/

3-13. 全表扫描的统计信息
column name format a30
column value format 999,999,999
select
    name,
    value
from
    v$sysstat
where
    name in (
        'table scans (short tables)',
        'table scans (long tables)')
/

3-14. 查看高负载 SQL
SELECT
    ADDRESS,
    substr(SQL_TEXT,1,20) Text,
    buffer_gets,
    executions,
    buffer_gets/executions AVG
FROM
    v$sqlarea
WHERE
    executions>0 AND
    buffer_gets > 100000
ORDER BY 5
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22558114/viewspace-1097814/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22558114/viewspace-1097814/