等待事件分为空闲等待事件和非空闲(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试图覆盖某个日志文件,如果这时数据库没有完成写出由这个日志文件所保护的脏数据时(检查点未完成),该等待事件出现,这两种情况数据库都会陷于停顿状态。
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试图覆盖某个日志文件,如果这时数据库没有完成写出由这个日志文件所保护的脏数据时(检查点未完成),该等待事件出现,这两种情况数据库都会陷于停顿状态。