Oracle 等待事件

等待事件分为空闲等待事件和非空闲（non-idle）等待事件。空闲事件指Oracle正等待某种工作，不用过多注意这部分事件。非空闲等待事件专门针对Oracle的活动，指数据任务或应用运行过程发生的等待。


v$event_name是一个很好的学习入口。


SYS@ orcl> select name, parameter1, parameter2, parameter3 from v$event_name where name = 'db file scattered read';
NAME                 PARAMETER1           PARAMETER2           PARAMETER3
-------------------- -------------------- -------------------- --------------------
db file scattered re file#                block#               blocks


se


SYS@ orcl> select * from v$system_wait_class order by time_waited;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS           TOTAL_WAITS TIME_WAITED TOTAL_WAITS_FG TIME_WAITED_FG
------------- ----------- -------------------- ----------- ----------- -------------- --------------
   2000153315           7 Network                       26           0             17              0
   4166625743           3 Administrative                 2          60              1             10
   3386400367           5 Commit                        16          60             14             53
   4108307767           9 System I/O                  5082        1162           2764             50
   3875070507           4 Concurrency                   67        1548             32            479
   1893977003           0 Other                        279        1596            145            977
   2396326234          10 Scheduler                    143        1649            143           1649
   1740759767           8 User I/O                    6418       11155           4661           7426
   2723168908           6 Idle                        2294      279039            527          36568


SYS@ orcl> 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#;
WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS             COUNT(*)
----------- ------------- -------------------- ----------
          0    1893977003 Other                       719
          1    4217450380 Application                  17
          2    3290255840 Configuration                24
          3    4166625743 Administrative               54
          4    3875070507 Concurrency                  32
          5    3386400367 Commit                        2
          6    2723168908 Idle                         94
          7    2000153315 Network                      35
          8    1740759767 User I/O                     45
          9    4108307767 System I/O                   30
         10    2396326234 Scheduler                     7
         11    3871361733 Cluster                      50
         12     644977587 Queueing                      9


SYS@ orcl> select name, wait_class from v$event_name where wait_class = 'Idle';
NAME                                                         WAIT_CLASS
------------------------------------------------------------ --------------------
pmon timer                                                   Idle
VKTM Logical Idle Wait                                       Idle
VKTM Init Wait for GSGA                                      Idle
IORM Scheduler Slave Idle Wait                               Idle
rdbms ipc message                                            Idle
i/o slave wait                                               Idle
VKRM Idle                                                    Idle
wait for unread message on broadcast channel                 Idle
wait for unread message on multiple broadcast channels       Idle
class slave wait                                             Idle
KSV master wait                                              Idle
PING                                                         Idle
watchdog main loop                                           Idle
DIAG idle wait                                               Idle
ges remote message                                           Idle
gcs remote message                                           Idle
heartbeat monitor sleep                                      Idle
SGA: MMAN sleep for component shrink                         Idle
MRP redo arrival                                             Idle
LNS ASYNC archive log                                        Idle
LNS ASYNC dest activation                                    Idle
LNS ASYNC end of log                                         Idle
simulated log write delay                                    Idle
LGWR real time apply sync                                    Idle
parallel recovery slave idle wait                            Idle
LogMiner builder: idle                                       Idle
LogMiner builder: branch                                     Idle
LogMiner preparer: idle                                      Idle
LogMiner reader: log (idle)                                  Idle
LogMiner reader: redo (idle)                                 Idle
LogMiner client: transaction                                 Idle
LogMiner: other                                              Idle
LogMiner: activate                                           Idle
LogMiner: reset                                              Idle
LogMiner: find session                                       Idle
LogMiner: internal                                           Idle
Logical Standby Apply Delay                                  Idle
parallel recovery coordinator waits for slave cleanup        Idle
parallel recovery control message reply                      Idle
parallel recovery slave next change                          Idle
PX Deq: Txn Recovery Start                                   Idle
PX Deq: Txn Recovery Reply                                   Idle
fbar timer                                                   Idle
smon timer                                                   Idle
PX Deq: Metadata Update                                      Idle
Space Manager: slave idle wait                               Idle
PX Deq: Index Merge Reply                                    Idle
PX Deq: Index Merge Execute                                  Idle
PX Deq: Index Merge Close                                    Idle
PX Deq: kdcph_mai                                            Idle
PX Deq: kdcphc_ack                                           Idle
shared server idle wait                                      Idle
dispatcher timer                                             Idle
cmon timer                                                   Idle
pool server timer                                            Idle
JOX Jit Process Sleep                                        Idle
jobq slave wait                                              Idle
pipe get                                                     Idle
PX Deque wait                                                Idle
PX Idle Wait                                                 Idle
PX Deq: Join ACK                                             Idle
PX Deq Credit: need buffer                                   Idle
PX Deq Credit: send blkd                                     Idle
PX Deq: Msg Fragment                                         Idle
PX Deq: Parse Reply                                          Idle
PX Deq: Execute Reply                                        Idle
PX Deq: Execution Msg                                        Idle
PX Deq: Table Q Normal                                       Idle
PX Deq: Table Q Sample                                       Idle
Streams fetch slave: waiting for txns                        Idle
Streams: waiting for messages                                Idle
Streams capture: waiting for archive log                     Idle
single-task message                                          Idle
SQL*Net message from client                                  Idle
SQL*Net vector message from client                           Idle
SQL*Net vector message from dblink                           Idle
PL/SQL lock timer                                            Idle
Streams AQ: emn coordinator idle wait                        Idle
EMON slave idle wait                                         Idle
Streams AQ: waiting for messages in the queue                Idle
Streams AQ: waiting for time management or cleanup tasks     Idle
Streams AQ: delete acknowledged messages                     Idle
Streams AQ: deallocate messages from Streams Pool            Idle
Streams AQ: qmn coordinator idle wait                        Idle
Streams AQ: qmn slave idle wait                              Idle
Streams AQ: RAC qmn coordinator idle wait                    Idle
HS message to agent                                          Idle
ASM background timer                                         Idle
auto-sqltune: wait graph update                              Idle
WCR: replay client notify                                    Idle
WCR: replay clock                                            Idle
WCR: replay paused                                           Idle
JS external job                                              Idle
cell worker idle                                             Idle


v$session中还增加了BLOCKING_SESSION等字段，以前需要通过dba_waiters等视图才能获得的信息。


SYS@ orcl> select sid, username, sql_exec_start, sql_exec_id from v$session;
       SID USERNAME                       SQL_EXEC_START      SQL_EXEC_ID
---------- ------------------------------ ------------------- -----------
......
        49 SYS                            2013-03-01 23:30:28    16777216


一个活动事务可能经历很多等待，v$session_wait视图记录的是累积信息。
SYS@ orcl> select sid, event, time_waited, time_waited_micro from v$session_event where sid = 49 order by 3;
       SID EVENT                          TIME_WAITED TIME_WAITED_MICRO
---------- ------------------------------ ----------- -----------------
        49 Disk file operations I/O                 0               557
        49 SQL*Net message to client                0               245
        49 SQL*Net message from client          35706         357060895


v$session_event的信息和会话生命周期相关，这些信息同时会被累积到v$system_event视图作为数据库整体等待数据保存。


SYS@ orcl> select event, total_waits, time_waited, average_wait from v$system_event where event = 'latch: shared pool';
EVENT                          TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- ----------- ------------
latch: shared pool                      16          29         1.82


v$event_histogram视图可以看到等待事件的柱状图分布。
 SYS@ orcl> select event, wait_time_milli, wait_count from v$event_histogram where event = 'latch: shared pool';
EVENT                          WAIT_TIME_MILLI WAIT_COUNT
------------------------------ --------------- ----------
latch: shared pool                           1          5
latch: shared pool                           2          2
latch: shared pool                           4          2
latch: shared pool                           8          2
latch: shared pool                          16          2
latch: shared pool                          32          0
latch: shared pool                          64          1
latch: shared pool                         128          2




捕获相关SQL
SYS@ orcl> select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid='&sid');




10g的增强
为了重现（Reproduce）问题，有效地保留Session信息，Oracle新增v$session_wait_history视图，该视图用以记录活动Session的最近10次等待事件。以下查询输出了SID


SYS@ orcl> select event, p1text, p1, p2text, p2, wait_time from v$session_wait_history where sid = 33;
EVENT                               P1TEXT             P1 P2TEXT             P2  WAIT_TIME
----------------------------------- ---------- ---------- ---------- ---------- ----------
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2802
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2804
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2803
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2803
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2802
10 rows selected.


缺省记录活动会话最近10次等待，这个约束受到一个隐含参数的影响，这个参数就是_session_wait_history
NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_session_wait_history          10              enable session wait history collection




ASH新特性
从Oracle 10g开始，Oracle引入了ASH新特性，也就是活动Session历史信息记录（Active Session History，ASH）。ASH以v$session为基础，每秒采样一次，记录活动会话等待的事件。因为记录所有会话的活动是非常昂贵的，所以不活动的会话不会被采样。采样工作由10g新引入的一个后台进程MMNL来完成


NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_ash_enable                    TRUE            To enable or disable Active Session sampling and f
                                               lushing


采样时间同样由另一个参数决定
NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_ash_sampling_interval         1000            Time interval between two successive Active Sessio
                                               n samples in millisecs


采样的性能影响无疑是存在的，但是因为Oracle的采样工具可以直接访问Oracle内部结构，所以是极其高效的，对于性能的影响也是非常小，这也正是Oracle提供优化或诊断工具的优势所在。


ASH信息被设计为在内存中滚动的，在需要的时候早期的信息会被覆盖。ASH记录的信息可以通过v$active_session_history视图来访问，对于每一个活动Session，每次采样会在这个视图中记录一行。


这部分内存在SGA中分配
SYS@ orcl> select * from v$sgastat where name like '%ASH%';
POOL         NAME                                BYTES
------------ ------------------------------ ----------
shared pool  ASH buffers                       2097152
shared pool  UNDO INFO HASH                       5664


ASH buffers的最小值为1MB，最大值不超过30MB，大小按照以下算法分配：
Max(Min(cpu_count * 2MB,5% * SHARED_POOL_SIZE,30MB),1MB)




生成ASH报告
脚本方式
调用$ORACLE_HOME/rdbms/admin/ashrpt.sql脚本，回答一系列问题之后




自动负载信息库：AWR的引入
内存中记录的ASH信息始终是有限的，为了保存历史数据，这些信息最终需要写入磁盘。这些历史信息的存储，引出了Oracle 10g的另外一个新特性：自动负载信息库（Automatic Workload Repository，AWR）。


AWR收集关于该特定数据库的操作统计信息和其他统计信息，Oracle以固定的时间间隔（默认为每上时一次）为其所有重要统计信息和负载信息执行一次快照，并将这些快照存储在AWR中。这些信息在AWR中保留给定的时间（默认为一周），然后被清除。执行快照的频率及其保持时间都可以自定义，以满足不同环境的需要。


AWR的采样工作由后台进程MMON每60分钟执行一次，ASH信息同样会被采样写出到AWR负载库。虽然ASH buffers被设计为保留1小时的信息，但是很多时候这个内存是不足够的，当ASH buffers写满之后，另外一个后台进程MMNL将会主动将ASH信息写出。由于数据量巨大，把所有ASH数据写到磁盘上是不可接受的。一般是在写到磁盘的时候过滤这个数据，写出的数据占采样数据的10%，写出时通过direct-path insert完成，尽量减少日志生成，从而最小化数据库性能影响。


 


AWR的行为受到数据库另外一个重要初始化参数STATISTICS_LEVEL的影响。


ASH信息的写出比例受一个隐含参数控制
SYS@ orcl> @GetHidPar 
Enter value for par: filter_ratio
NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_ash_disk_filter_ratio         10              Ratio of the number of in-memory samples to the nu
                                               mber of samples actually written to disk


 




AWR的采样数据存储
AWR的信息需要独立存储，在Oracle 10g中，新增的SYSAUX表空间是这类信息的存储地：


SYS@ orcl> select occupant_name, occupant_desc, schema_name, space_usage_kbytes/1024 "MB" from v$sysaux_occupants where occupant_name like '%AWR%';
OCCUPANT_NAME        OCCUPANT_DESC                                           SCHEMA_N         MB
-------------------- ------------------------------------------------------- -------- ----------
SM/AWR               Server Manageability - Automatic Workload Repository    SYS           59.75


AWR收集的信息通过一系列的视图展现出来，可以查询这些视图获得数据库的信息采样
SYS@ orcl> select object_name, object_type from dba_objects where object_name like 'DBA_HIST%' and object_type = 'VIEW' and rownum < 5;
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
DBA_HIST_DATABASE_INSTANCE                         VIEW
DBA_HIST_SNAPSHOT                                  VIEW
DBA_HIST_SNAP_ERROR                                VIEW
DBA_HIST_COLORED_SQL                               VIEW


这些系统视图的底层大致有3类WRM$存储AWR的元数据（Workload Repository Matedata），WRH$表存储采样快照的历史数据（Workload Repository Historical），WRI$表存储同数据库建议功能相关的数据。WRR$类代表的是Oracle 11g新功能Workload Capture以及Workload Replay相关信息。


11.2
SYS@ orcl> select substr(table_name, 1, 4), count(*) from dba_tables where table_name like 'WR%' group by substr(table_name, 1, 4);
SUBSTR(T   COUNT(*)
-------- ----------
WRM$              9
WRH$            121
WRR$             20
WRI$             88


AWR的历史数据表主要通过分区表进行存储，可以通过DBA_TAB_PARTITIONS视图进行查询


$ORACLE_HOME/rdbms/admin/awrrpt.sql


为了能够通过比较机制对数据库不同阶段的性能情况进行比较，可以为AWR创建基线（Base Line），创建的基线不会被清除，以后生成采样数据或者优化后采样可以同保留的基线进行对比，以确定数据的性能变化。创建Base Line可以通过Database Control进行，也可以通过命令完成，在内部都是通过DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE来完成BASE Line的创建：


创建的Base Line可以通过数据字典视图dba_hist_baseline查询。类似以前的Statspace，DBMS_SWRF_INTERNAL.AWR_EXTRACT可以用为导出数据，awrextr.sql脚本就是用来完成这个工作的，而导入工作可以通过DBMS_SWRF_INTERNAL.AWR_LOAD和MOVE_TO_AWR过程来完成，awrload.sql用来完成这个工作。




AWR使用信息报告
Oracle还随软件提供一个脚本用于输出AWR的使用信息，这个脚本是awrinfo.sql，运行这个脚本，将会输出AWR的空间使用、快照采样、ASH及ADDM等元数据信息。




自动数据库诊断监控：ADDM的引入
自动数据库诊断监控（Automatic Database Diagnostic Monitor，ADDM）


ADDM可以定期检查数据库的状态，根据内建的专家系统，自动确定潜在的数据库性能瓶颈。由于这一切都是内建在Oracle数据库系统之内的，其执行效率很高，几乎不影响数据库的总体性能。
 




顶级等待事件
v$system_event，该视图记录的是数据库自启动以来等待事件的汇总。通过查询该视图，就可以快速获得数据库等待事件的总体概况。


SYS@ orcl> select * from (select event, time_waited from v$system_event order by time_waited desc) where rownum < 11;
EVENT                                                            TIME_WAITED
---------------------------------------------------------------- -----------
rdbms ipc message                                                      90968
DIAG idle wait                                                         17183
jobq slave wait                                                        15107
pmon timer                                                              8418
shared server idle wait                                                 7317
db file sequential read                                                 6761
dispatcher timer                                                        6002
SQL*Net message from client                                             5988
Streams AQ: qmn slave idle wait                                         5327
Streams AQ: qmn coordinator idle wait                                   3804


Top 5 Time Events，这部分信息就是来自v$system_event视图的采样。




重要的等待事件
db file sequential read


SYS@ orcl> select name, wait_class,  parameter1, parameter2, parameter3 from v$event_name where name = 'db file sequential read';
NAME                      WAIT_CLASS      PARAMETER1 PARAMETER2 PARAMETER3
------------------------- --------------- ---------- ---------- --------------------
db file sequential read   User I/O        file#      block#     blocks


如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，没有正确的使用驱动表；或者可能索引的使用存在问题。有时候这个等待过高 和存储分部不连续、连续数据块中部分被缓存有关，定期的数据整理和空间回收有时候是必须的。


可以通过查询v$segment_statistics视图，找出物理读取显著的索引段或都是表段，研究其数据结构，


SYS@ orcl> select * from v$segstat_name;


STATISTIC# NAME                           SAM
---------- ------------------------------ ---
         0 logical reads                  YES
         1 buffer busy waits              NO
         2 gc buffer busy                 NO
         3 db block changes               YES
         4 physical reads                 NO
         5 physical writes                NO
         6 physical read requests         NO
         7 physical write requests        NO
         8 physical reads direct          NO
         9 physical writes direct         NO
        11 optimized physical reads       NO
        12 gc cr blocks received          NO
        13 gc current blocks received     NO
        14 ITL waits                      NO
        15 row lock waits                 NO
        17 space used                     NO
        18 space allocated                NO
        20 segment scans                  NO




db file scattered read等待事件
这个事件表明用户进程正在读数据到Buffer Cache中，等待直到物理I/O调用返回。将存储上连续的数据块离散的讲稿到多个不连续的内存位置。通常是多块读，在Full Table Scan或Fast Full Scan等访问方式下使用。


Scattered Read代表Full Scan，当执行Full Scan读取数据到Buffer Cache时，通常连续的数据在内存中的存储位置并不连续，所以这个等待被命名为Scattered Read（离散读）。每次多块读读取的数据块数据量受参数DB_FILE_MULTIBLOCK_READ_COUNT限制。


该等待可能和全表扫描（Full Table Scan）或都快速全索引扫描（Index Fast Full Scan）的连续读取相关，根据经验，通常大量的db file scattered read等待可能意味着应用问题或都索引缺失。


9i开始，Oracle新增加了一个视图v$sql_plan用于记录当前系统Library Cache中语句的执行计划。


SYS@ orcl> select distinct object_name, object_owner from v$sql_plan p where p.operation = 'TABLE ACCESS' and p.options = 'FULL' and object_owner = '&user';


SYS@ orcl> select distinct object_name, object_owner from v$sql_plan p where p.operation = 'INDEX' and p.options = 'FULL SCAN';




direct path read/write（直接路径读/写）
直接路径读通常发生在Oracle直接读数据到进程PGA时，这个读取不需要经过SGA。在10g中，这个等待事件被归于User I/O一类。


这是常见的集中数据读方式。
 


这类读取常在以下情况被使用：
磁盘排序IO操作
并行查询从属进程
预读操作


最为常见的是第一种情况。在DSS系统中，存在大量direct path Read是很正常的，但是在OLTP系统中，通常显著的直接路径读（direct path read）都意味着系统应用酆问题，从而导致大量的磁盘排序读取操作。


直接路径写通常发生在Oracle直接从PGA写数据文件或临时文件，这个操作可以绕过SGA。10g中，这个等待事件同direct path read一样被归于User I/O一类。


这类操作通常在以下情况使用
直接路径加载；
并行DML操作；
磁盘排序；
对于缓存的“LOB”段的写入，随后会记录为direct path write(lob)等待。


最为常见的直接路径写，多数因为磁盘排序导致。对于这一写入等待，我们应该找到I/O操作最为频繁的数据文件（如果有过多的排序操作，很有可能就是临时文件），分散负载，加快写入操作


SYS@ orcl> select tablespace_name, extent_management from dba_tablespaces;
TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMP                           LOCAL
USERS                          LOCAL




磁盘排序与临时文件
10g中，为了区分特定的对于临时文件的直接读写操作，Oracle对direct path read/write进行了分离，将这类操作分列出来：
SYS@ orcl> select event#, name, wait_class from v$event_name where name like 'direct%';
    EVENT# NAME                                     WAIT_CLASS
---------- ---------------------------------------- ---------------
        15 direct path sync                         User I/O
       194 direct path read                         User I/O
       195 direct path read temp                    User I/O
       196 direct path write                        User I/O
       197 direct path write temp                   User I/O


SYS@ orcl> select * from dba_synonyms where synonym_name = 'V$TEMPSEG_USAGE';
OWNER      SYNONYM_NAME       TABLE_OWNE TABLE_NAME       DB_LINK
---------- ------------------ ---------- ---------------- --------
PUBLIC     V$TEMPSEG_USAGE    SYS        V_$SORT_USAGE


为了分离临时文件号和数据文件号，Oracle对临时文件的编号以db_files为起点，所以临时文件的绝对文件号应该等于db_files + file#。


SYS@ orcl> select indx, tfnum, tfafn, tfcsz from x$kcctf;
      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1        201       2560


SYS@ orcl> show parameter db_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200


SYS@ orcl> select file#, rfile#, name from v$tempfile;
     FILE#     RFILE# NAME
---------- ---------- ----------------------------------------
         1          1 /oradata/orcl/temp01.dbf




日志文件相关等待
SYS@ orcl> select name from v$event_name where name like '%log%';


NAME
--------------------------------------------------------------------------------
logout restrictor
LNS ASYNC archive log
LNS ASYNC end of log
log file sequential read
log file single write
log file parallel write
log buffer space
log file switch (checkpoint incomplete)
log file switch (private strand flush incomplete)
log file switch (archiving needed)
switch logfile command
log file switch completion
log file sync
simulated log write delay
LogMiner reader: log (idle)
flashback log file write
flashback log file read
flashback log file sync
Streams capture: waiting for archive log
ges RMS0 retry add redo log
gcs log flush sync
ARCH wait for archivelog lock
MRP wait on archivelog delay
MRP wait on archivelog arrival
MRP wait on archivelog archival
log switch/archive
log file switch (clearing log file)
enq: XR - database force logging
recovery area: computing applied logs
enq: FL - Flashback database log
enq: FD - Flashback logical operations
flashback free VI log
flashback log switch
log write(odd)
log write(even)


log file switch
log file switch主要包含两个子事件log file switch(archiving needed)和log file switch(checkpoint incomplete)。


og file switch(archiving needed)，这个等待事件出现时通常是因为日志组循环写满以后，在需要覆盖先前日志时。


log file switch(checkpoint incomplete)。当所有日志组都写满之后，LGWR试图覆盖某个日志文件，如果这时数据库没有完成写出由这个日志文件所保护的脏数据时（检查点未完成），该等待事件出现，这两种情况数据库都会陷于停顿状态。
等待事件分为空闲等待事件和非空闲（non-idle）等待事件。空闲事件指Oracle正等待某种工作，不用过多注意这部分事件。非空闲等待事件专门针对Oracle的活动，指数据任务或应用运行过程发生的等待。


v$event_name是一个很好的学习入口。


SYS@ orcl> select name, parameter1, parameter2, parameter3 from v$event_name where name = 'db file scattered read';
NAME                 PARAMETER1           PARAMETER2           PARAMETER3
-------------------- -------------------- -------------------- --------------------
db file scattered re file#                block#               blocks


se


SYS@ orcl> select * from v$system_wait_class order by time_waited;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS           TOTAL_WAITS TIME_WAITED TOTAL_WAITS_FG TIME_WAITED_FG
------------- ----------- -------------------- ----------- ----------- -------------- --------------
   2000153315           7 Network                       26           0             17              0
   4166625743           3 Administrative                 2          60              1             10
   3386400367           5 Commit                        16          60             14             53
   4108307767           9 System I/O                  5082        1162           2764             50
   3875070507           4 Concurrency                   67        1548             32            479
   1893977003           0 Other                        279        1596            145            977
   2396326234          10 Scheduler                    143        1649            143           1649
   1740759767           8 User I/O                    6418       11155           4661           7426
   2723168908           6 Idle                        2294      279039            527          36568


SYS@ orcl> 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#;
WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS             COUNT(*)
----------- ------------- -------------------- ----------
          0    1893977003 Other                       719
          1    4217450380 Application                  17
          2    3290255840 Configuration                24
          3    4166625743 Administrative               54
          4    3875070507 Concurrency                  32
          5    3386400367 Commit                        2
          6    2723168908 Idle                         94
          7    2000153315 Network                      35
          8    1740759767 User I/O                     45
          9    4108307767 System I/O                   30
         10    2396326234 Scheduler                     7
         11    3871361733 Cluster                      50
         12     644977587 Queueing                      9


SYS@ orcl> select name, wait_class from v$event_name where wait_class = 'Idle';
NAME                                                         WAIT_CLASS
------------------------------------------------------------ --------------------
pmon timer                                                   Idle
VKTM Logical Idle Wait                                       Idle
VKTM Init Wait for GSGA                                      Idle
IORM Scheduler Slave Idle Wait                               Idle
rdbms ipc message                                            Idle
i/o slave wait                                               Idle
VKRM Idle                                                    Idle
wait for unread message on broadcast channel                 Idle
wait for unread message on multiple broadcast channels       Idle
class slave wait                                             Idle
KSV master wait                                              Idle
PING                                                         Idle
watchdog main loop                                           Idle
DIAG idle wait                                               Idle
ges remote message                                           Idle
gcs remote message                                           Idle
heartbeat monitor sleep                                      Idle
SGA: MMAN sleep for component shrink                         Idle
MRP redo arrival                                             Idle
LNS ASYNC archive log                                        Idle
LNS ASYNC dest activation                                    Idle
LNS ASYNC end of log                                         Idle
simulated log write delay                                    Idle
LGWR real time apply sync                                    Idle
parallel recovery slave idle wait                            Idle
LogMiner builder: idle                                       Idle
LogMiner builder: branch                                     Idle
LogMiner preparer: idle                                      Idle
LogMiner reader: log (idle)                                  Idle
LogMiner reader: redo (idle)                                 Idle
LogMiner client: transaction                                 Idle
LogMiner: other                                              Idle
LogMiner: activate                                           Idle
LogMiner: reset                                              Idle
LogMiner: find session                                       Idle
LogMiner: internal                                           Idle
Logical Standby Apply Delay                                  Idle
parallel recovery coordinator waits for slave cleanup        Idle
parallel recovery control message reply                      Idle
parallel recovery slave next change                          Idle
PX Deq: Txn Recovery Start                                   Idle
PX Deq: Txn Recovery Reply                                   Idle
fbar timer                                                   Idle
smon timer                                                   Idle
PX Deq: Metadata Update                                      Idle
Space Manager: slave idle wait                               Idle
PX Deq: Index Merge Reply                                    Idle
PX Deq: Index Merge Execute                                  Idle
PX Deq: Index Merge Close                                    Idle
PX Deq: kdcph_mai                                            Idle
PX Deq: kdcphc_ack                                           Idle
shared server idle wait                                      Idle
dispatcher timer                                             Idle
cmon timer                                                   Idle
pool server timer                                            Idle
JOX Jit Process Sleep                                        Idle
jobq slave wait                                              Idle
pipe get                                                     Idle
PX Deque wait                                                Idle
PX Idle Wait                                                 Idle
PX Deq: Join ACK                                             Idle
PX Deq Credit: need buffer                                   Idle
PX Deq Credit: send blkd                                     Idle
PX Deq: Msg Fragment                                         Idle
PX Deq: Parse Reply                                          Idle
PX Deq: Execute Reply                                        Idle
PX Deq: Execution Msg                                        Idle
PX Deq: Table Q Normal                                       Idle
PX Deq: Table Q Sample                                       Idle
Streams fetch slave: waiting for txns                        Idle
Streams: waiting for messages                                Idle
Streams capture: waiting for archive log                     Idle
single-task message                                          Idle
SQL*Net message from client                                  Idle
SQL*Net vector message from client                           Idle
SQL*Net vector message from dblink                           Idle
PL/SQL lock timer                                            Idle
Streams AQ: emn coordinator idle wait                        Idle
EMON slave idle wait                                         Idle
Streams AQ: waiting for messages in the queue                Idle
Streams AQ: waiting for time management or cleanup tasks     Idle
Streams AQ: delete acknowledged messages                     Idle
Streams AQ: deallocate messages from Streams Pool            Idle
Streams AQ: qmn coordinator idle wait                        Idle
Streams AQ: qmn slave idle wait                              Idle
Streams AQ: RAC qmn coordinator idle wait                    Idle
HS message to agent                                          Idle
ASM background timer                                         Idle
auto-sqltune: wait graph update                              Idle
WCR: replay client notify                                    Idle
WCR: replay clock                                            Idle
WCR: replay paused                                           Idle
JS external job                                              Idle
cell worker idle                                             Idle


v$session中还增加了BLOCKING_SESSION等字段，以前需要通过dba_waiters等视图才能获得的信息。


SYS@ orcl> select sid, username, sql_exec_start, sql_exec_id from v$session;
       SID USERNAME                       SQL_EXEC_START      SQL_EXEC_ID
---------- ------------------------------ ------------------- -----------
......
        49 SYS                            2013-03-01 23:30:28    16777216


一个活动事务可能经历很多等待，v$session_wait视图记录的是累积信息。
SYS@ orcl> select sid, event, time_waited, time_waited_micro from v$session_event where sid = 49 order by 3;
       SID EVENT                          TIME_WAITED TIME_WAITED_MICRO
---------- ------------------------------ ----------- -----------------
        49 Disk file operations I/O                 0               557
        49 SQL*Net message to client                0               245
        49 SQL*Net message from client          35706         357060895


v$session_event的信息和会话生命周期相关，这些信息同时会被累积到v$system_event视图作为数据库整体等待数据保存。


SYS@ orcl> select event, total_waits, time_waited, average_wait from v$system_event where event = 'latch: shared pool';
EVENT                          TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- ----------- ------------
latch: shared pool                      16          29         1.82


v$event_histogram视图可以看到等待事件的柱状图分布。
 SYS@ orcl> select event, wait_time_milli, wait_count from v$event_histogram where event = 'latch: shared pool';
EVENT                          WAIT_TIME_MILLI WAIT_COUNT
------------------------------ --------------- ----------
latch: shared pool                           1          5
latch: shared pool                           2          2
latch: shared pool                           4          2
latch: shared pool                           8          2
latch: shared pool                          16          2
latch: shared pool                          32          0
latch: shared pool                          64          1
latch: shared pool                         128          2




捕获相关SQL
SYS@ orcl> select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid='&sid');




10g的增强
为了重现（Reproduce）问题，有效地保留Session信息，Oracle新增v$session_wait_history视图，该视图用以记录活动Session的最近10次等待事件。以下查询输出了SID


SYS@ orcl> select event, p1text, p1, p2text, p2, wait_time from v$session_wait_history where sid = 33;
EVENT                               P1TEXT             P1 P2TEXT             P2  WAIT_TIME
----------------------------------- ---------- ---------- ---------- ---------- ----------
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2802
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2804
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2803
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2803
latch free                          address     537006752 number            437          0
Streams AQ: qmn slave idle wait     Type                1                     0       2802
10 rows selected.


缺省记录活动会话最近10次等待，这个约束受到一个隐含参数的影响，这个参数就是_session_wait_history
NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_session_wait_history          10              enable session wait history collection




ASH新特性
从Oracle 10g开始，Oracle引入了ASH新特性，也就是活动Session历史信息记录（Active Session History，ASH）。ASH以v$session为基础，每秒采样一次，记录活动会话等待的事件。因为记录所有会话的活动是非常昂贵的，所以不活动的会话不会被采样。采样工作由10g新引入的一个后台进程MMNL来完成


NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_ash_enable                    TRUE            To enable or disable Active Session sampling and f
                                               lushing


采样时间同样由另一个参数决定
NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_ash_sampling_interval         1000            Time interval between two successive Active Sessio
                                               n samples in millisecs


采样的性能影响无疑是存在的，但是因为Oracle的采样工具可以直接访问Oracle内部结构，所以是极其高效的，对于性能的影响也是非常小，这也正是Oracle提供优化或诊断工具的优势所在。


ASH信息被设计为在内存中滚动的，在需要的时候早期的信息会被覆盖。ASH记录的信息可以通过v$active_session_history视图来访问，对于每一个活动Session，每次采样会在这个视图中记录一行。


这部分内存在SGA中分配
SYS@ orcl> select * from v$sgastat where name like '%ASH%';
POOL         NAME                                BYTES
------------ ------------------------------ ----------
shared pool  ASH buffers                       2097152
shared pool  UNDO INFO HASH                       5664


ASH buffers的最小值为1MB，最大值不超过30MB，大小按照以下算法分配：
Max(Min(cpu_count * 2MB,5% * SHARED_POOL_SIZE,30MB),1MB)




生成ASH报告
脚本方式
调用$ORACLE_HOME/rdbms/admin/ashrpt.sql脚本，回答一系列问题之后




自动负载信息库：AWR的引入
内存中记录的ASH信息始终是有限的，为了保存历史数据，这些信息最终需要写入磁盘。这些历史信息的存储，引出了Oracle 10g的另外一个新特性：自动负载信息库（Automatic Workload Repository，AWR）。


AWR收集关于该特定数据库的操作统计信息和其他统计信息，Oracle以固定的时间间隔（默认为每上时一次）为其所有重要统计信息和负载信息执行一次快照，并将这些快照存储在AWR中。这些信息在AWR中保留给定的时间（默认为一周），然后被清除。执行快照的频率及其保持时间都可以自定义，以满足不同环境的需要。


AWR的采样工作由后台进程MMON每60分钟执行一次，ASH信息同样会被采样写出到AWR负载库。虽然ASH buffers被设计为保留1小时的信息，但是很多时候这个内存是不足够的，当ASH buffers写满之后，另外一个后台进程MMNL将会主动将ASH信息写出。由于数据量巨大，把所有ASH数据写到磁盘上是不可接受的。一般是在写到磁盘的时候过滤这个数据，写出的数据占采样数据的10%，写出时通过direct-path insert完成，尽量减少日志生成，从而最小化数据库性能影响。


 


AWR的行为受到数据库另外一个重要初始化参数STATISTICS_LEVEL的影响。


ASH信息的写出比例受一个隐含参数控制
SYS@ orcl> @GetHidPar 
Enter value for par: filter_ratio
NAME                           VALUE           PDESC
------------------------------ --------------- --------------------------------------------------
_ash_disk_filter_ratio         10              Ratio of the number of in-memory samples to the nu
                                               mber of samples actually written to disk


 




AWR的采样数据存储
AWR的信息需要独立存储，在Oracle 10g中，新增的SYSAUX表空间是这类信息的存储地：


SYS@ orcl> select occupant_name, occupant_desc, schema_name, space_usage_kbytes/1024 "MB" from v$sysaux_occupants where occupant_name like '%AWR%';
OCCUPANT_NAME        OCCUPANT_DESC                                           SCHEMA_N         MB
-------------------- ------------------------------------------------------- -------- ----------
SM/AWR               Server Manageability - Automatic Workload Repository    SYS           59.75


AWR收集的信息通过一系列的视图展现出来，可以查询这些视图获得数据库的信息采样
SYS@ orcl> select object_name, object_type from dba_objects where object_name like 'DBA_HIST%' and object_type = 'VIEW' and rownum < 5;
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
DBA_HIST_DATABASE_INSTANCE                         VIEW
DBA_HIST_SNAPSHOT                                  VIEW
DBA_HIST_SNAP_ERROR                                VIEW
DBA_HIST_COLORED_SQL                               VIEW


这些系统视图的底层大致有3类WRM$存储AWR的元数据（Workload Repository Matedata），WRH$表存储采样快照的历史数据（Workload Repository Historical），WRI$表存储同数据库建议功能相关的数据。WRR$类代表的是Oracle 11g新功能Workload Capture以及Workload Replay相关信息。


11.2
SYS@ orcl> select substr(table_name, 1, 4), count(*) from dba_tables where table_name like 'WR%' group by substr(table_name, 1, 4);
SUBSTR(T   COUNT(*)
-------- ----------
WRM$              9
WRH$            121
WRR$             20
WRI$             88


AWR的历史数据表主要通过分区表进行存储，可以通过DBA_TAB_PARTITIONS视图进行查询


$ORACLE_HOME/rdbms/admin/awrrpt.sql


为了能够通过比较机制对数据库不同阶段的性能情况进行比较，可以为AWR创建基线（Base Line），创建的基线不会被清除，以后生成采样数据或者优化后采样可以同保留的基线进行对比，以确定数据的性能变化。创建Base Line可以通过Database Control进行，也可以通过命令完成，在内部都是通过DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE来完成BASE Line的创建：


创建的Base Line可以通过数据字典视图dba_hist_baseline查询。类似以前的Statspace，DBMS_SWRF_INTERNAL.AWR_EXTRACT可以用为导出数据，awrextr.sql脚本就是用来完成这个工作的，而导入工作可以通过DBMS_SWRF_INTERNAL.AWR_LOAD和MOVE_TO_AWR过程来完成，awrload.sql用来完成这个工作。




AWR使用信息报告
Oracle还随软件提供一个脚本用于输出AWR的使用信息，这个脚本是awrinfo.sql，运行这个脚本，将会输出AWR的空间使用、快照采样、ASH及ADDM等元数据信息。




自动数据库诊断监控：ADDM的引入
自动数据库诊断监控（Automatic Database Diagnostic Monitor，ADDM）


ADDM可以定期检查数据库的状态，根据内建的专家系统，自动确定潜在的数据库性能瓶颈。由于这一切都是内建在Oracle数据库系统之内的，其执行效率很高，几乎不影响数据库的总体性能。
 




顶级等待事件
v$system_event，该视图记录的是数据库自启动以来等待事件的汇总。通过查询该视图，就可以快速获得数据库等待事件的总体概况。


SYS@ orcl> select * from (select event, time_waited from v$system_event order by time_waited desc) where rownum < 11;
EVENT                                                            TIME_WAITED
---------------------------------------------------------------- -----------
rdbms ipc message                                                      90968
DIAG idle wait                                                         17183
jobq slave wait                                                        15107
pmon timer                                                              8418
shared server idle wait                                                 7317
db file sequential read                                                 6761
dispatcher timer                                                        6002
SQL*Net message from client                                             5988
Streams AQ: qmn slave idle wait                                         5327
Streams AQ: qmn coordinator idle wait                                   3804


Top 5 Time Events，这部分信息就是来自v$system_event视图的采样。




重要的等待事件
db file sequential read


SYS@ orcl> select name, wait_class,  parameter1, parameter2, parameter3 from v$event_name where name = 'db file sequential read';
NAME                      WAIT_CLASS      PARAMETER1 PARAMETER2 PARAMETER3
------------------------- --------------- ---------- ---------- --------------------
db file sequential read   User I/O        file#      block#     blocks


如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，没有正确的使用驱动表；或者可能索引的使用存在问题。有时候这个等待过高 和存储分部不连续、连续数据块中部分被缓存有关，定期的数据整理和空间回收有时候是必须的。


可以通过查询v$segment_statistics视图，找出物理读取显著的索引段或都是表段，研究其数据结构，


SYS@ orcl> select * from v$segstat_name;


STATISTIC# NAME                           SAM
---------- ------------------------------ ---
         0 logical reads                  YES
         1 buffer busy waits              NO
         2 gc buffer busy                 NO
         3 db block changes               YES
         4 physical reads                 NO
         5 physical writes                NO
         6 physical read requests         NO
         7 physical write requests        NO
         8 physical reads direct          NO
         9 physical writes direct         NO
        11 optimized physical reads       NO
        12 gc cr blocks received          NO
        13 gc current blocks received     NO
        14 ITL waits                      NO
        15 row lock waits                 NO
        17 space used                     NO
        18 space allocated                NO
        20 segment scans                  NO




db file scattered read等待事件
这个事件表明用户进程正在读数据到Buffer Cache中，等待直到物理I/O调用返回。将存储上连续的数据块离散的讲稿到多个不连续的内存位置。通常是多块读，在Full Table Scan或Fast Full Scan等访问方式下使用。


Scattered Read代表Full Scan，当执行Full Scan读取数据到Buffer Cache时，通常连续的数据在内存中的存储位置并不连续，所以这个等待被命名为Scattered Read（离散读）。每次多块读读取的数据块数据量受参数DB_FILE_MULTIBLOCK_READ_COUNT限制。


该等待可能和全表扫描（Full Table Scan）或都快速全索引扫描（Index Fast Full Scan）的连续读取相关，根据经验，通常大量的db file scattered read等待可能意味着应用问题或都索引缺失。


9i开始，Oracle新增加了一个视图v$sql_plan用于记录当前系统Library Cache中语句的执行计划。


SYS@ orcl> select distinct object_name, object_owner from v$sql_plan p where p.operation = 'TABLE ACCESS' and p.options = 'FULL' and object_owner = '&user';


SYS@ orcl> select distinct object_name, object_owner from v$sql_plan p where p.operation = 'INDEX' and p.options = 'FULL SCAN';




direct path read/write（直接路径读/写）
直接路径读通常发生在Oracle直接读数据到进程PGA时，这个读取不需要经过SGA。在10g中，这个等待事件被归于User I/O一类。


这是常见的集中数据读方式。
 


这类读取常在以下情况被使用：
磁盘排序IO操作
并行查询从属进程
预读操作


最为常见的是第一种情况。在DSS系统中，存在大量direct path Read是很正常的，但是在OLTP系统中，通常显著的直接路径读（direct path read）都意味着系统应用酆问题，从而导致大量的磁盘排序读取操作。


直接路径写通常发生在Oracle直接从PGA写数据文件或临时文件，这个操作可以绕过SGA。10g中，这个等待事件同direct path read一样被归于User I/O一类。


这类操作通常在以下情况使用
直接路径加载；
并行DML操作；
磁盘排序；
对于缓存的“LOB”段的写入，随后会记录为direct path write(lob)等待。


最为常见的直接路径写，多数因为磁盘排序导致。对于这一写入等待，我们应该找到I/O操作最为频繁的数据文件（如果有过多的排序操作，很有可能就是临时文件），分散负载，加快写入操作


SYS@ orcl> select tablespace_name, extent_management from dba_tablespaces;
TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMP                           LOCAL
USERS                          LOCAL




磁盘排序与临时文件
10g中，为了区分特定的对于临时文件的直接读写操作，Oracle对direct path read/write进行了分离，将这类操作分列出来：
SYS@ orcl> select event#, name, wait_class from v$event_name where name like 'direct%';
    EVENT# NAME                                     WAIT_CLASS
---------- ---------------------------------------- ---------------
        15 direct path sync                         User I/O
       194 direct path read                         User I/O
       195 direct path read temp                    User I/O
       196 direct path write                        User I/O
       197 direct path write temp                   User I/O


SYS@ orcl> select * from dba_synonyms where synonym_name = 'V$TEMPSEG_USAGE';
OWNER      SYNONYM_NAME       TABLE_OWNE TABLE_NAME       DB_LINK
---------- ------------------ ---------- ---------------- --------
PUBLIC     V$TEMPSEG_USAGE    SYS        V_$SORT_USAGE


为了分离临时文件号和数据文件号，Oracle对临时文件的编号以db_files为起点，所以临时文件的绝对文件号应该等于db_files + file#。


SYS@ orcl> select indx, tfnum, tfafn, tfcsz from x$kcctf;
      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1        201       2560


SYS@ orcl> show parameter db_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200


SYS@ orcl> select file#, rfile#, name from v$tempfile;
     FILE#     RFILE# NAME
---------- ---------- ----------------------------------------
         1          1 /oradata/orcl/temp01.dbf




日志文件相关等待
SYS@ orcl> select name from v$event_name where name like '%log%';


NAME
--------------------------------------------------------------------------------
logout restrictor
LNS ASYNC archive log
LNS ASYNC end of log
log file sequential read
log file single write
log file parallel write
log buffer space
log file switch (checkpoint incomplete)
log file switch (private strand flush incomplete)
log file switch (archiving needed)
switch logfile command
log file switch completion
log file sync
simulated log write delay
LogMiner reader: log (idle)
flashback log file write
flashback log file read
flashback log file sync
Streams capture: waiting for archive log
ges RMS0 retry add redo log
gcs log flush sync
ARCH wait for archivelog lock
MRP wait on archivelog delay
MRP wait on archivelog arrival
MRP wait on archivelog archival
log switch/archive
log file switch (clearing log file)
enq: XR - database force logging
recovery area: computing applied logs
enq: FL - Flashback database log
enq: FD - Flashback logical operations
flashback free VI log
flashback log switch
log write(odd)
log write(even)


log file switch
log file switch主要包含两个子事件log file switch(archiving needed)和log file switch(checkpoint incomplete)。


og file switch(archiving needed)，这个等待事件出现时通常是因为日志组循环写满以后，在需要覆盖先前日志时。


log file switch(checkpoint incomplete)。当所有日志组都写满之后，LGWR试图覆盖某个日志文件，如果这时数据库没有完成写出由这个日志文件所保护的脏数据时（检查点未完成），该等待事件出现，这两种情况数据库都会陷于停顿状态。