等待事件

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
/