oracle常见等待事件及处理方法
Oracle数据库buffer busy wait等待事件
当会话意图访问缓冲存储器中的数据块，而该数据块正在被其它会话使用时产生buffer busy waits事件。
其它会话可能正在从数据文件向缓冲区存储器度曲同样的数据块，或正在缓冲存储器中对其进行修改。
为了确保读取器会话拥有与获得所有更改或无更改的数据块一致的映像，正在修改该数据块的会话在其标题中标记一个标志，让其他会话知道有一个更改正在进行而等候更改的的完成。
视图v$waitstat不是OWI的组件，但其为没一类缓冲区提供了有用的等待统计。
遭遇buffer busy等待事件最常见的缓冲区类为块、段标题、撤消块、撤消标题。
显示一个查询v$waitstat视图的采样输出：具体示例如下：SELECT * FROM V$waitstat WHERE COUNT>0;CLASS COUNT TIME------------------ ---------- ----------data block 4170082 1668098segment header 116 98undo header 916 1134undo block 2087 16811、等待参数buffer wait busy的等待参数描述如下：P1 在Oracle 8及其以后版本的数据库里，P1显示询问数据块驻留的绝对文件号。
P2 进程需要访问的实际块号。
P3 在Oracle10g以前的版本中，着是表示等待原因的数字。
Oracle在内河代码中在多个地方用不同的原因码提交。
该原因码取决于版本。
2、等待时间100厘秒或1秒。
· Oracle会话正在等待钉住一个缓冲区。
必须在读取或修改缓冲区前将它钉住。
在任何时刻只有一个进程可以钉住一个缓冲区。
·buffer busy waits表明读/读、读/写、写/写争用。
·采取的适当措施取决于P3参数中的原因码。
在SGA中读取或修改缓冲区的会话必须首先获取cache buffers chains锁存器，并且遍历这个缓冲区链，直到他发现必需的缓冲区头。
Oracle 常见的33个等待事件
Oracle 常见的33个等待事件一．等待事件的相关知识：1.1 等待事件主要可以分为两类，即空闲(IDLE)等待事件和非空闲(NON-IDLE)等待事件。
1). 空闲等待事件指ORACLE正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。
2). 非空闲等待事件专门针对ORACLE的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是在调整数据库的时候需要关注与研究的。
在Oracle 10g中的等待事件有872个，11g中等待事件1116个。
我们可以通过v$event_name 视图来查看等待事件的相关信息。
1.2 查看v$event_name视图的字段结构：SQL> desc v$event_name;名称是否为空? 类型----------------------------------------- -------- --------------- EVENT# NUMBEREVENT_ID NUMBERNAME VARCHAR2(64)PARAMETER1 VARCHAR2(64)PARAMETER2 VARCHAR2(64)PARAMETER3 VARCHAR2(64)WAIT_CLASS_ID NUMBERWAIT_CLASS# NUMBERWAIT_CLASS VARCHAR2(64)1.3 查看等待事件总数：SQL> select count(*) from v$event_name;COUNT(*)----------11161.4 查看等待事件分类情况：/* Formatted on 2010/8/11 16:08:55 (QP5 v5.115.810.9015) */SELECT wait_class#,wait_class_id,wait_class,COUNT(* )AS"count"FROM v$event_nameGROUP BY wait_class#,wait_class_id,wait_classORDER BY wait_class#;WAIT_CLASS# WAIT_CLASS_IDWAIT_CLASS count----------- ------------- -------------------- ----------0 1893977003Other 7171 4217450380Application 172 3290255840Configuration 243 4166625743Administrative 544 3875070507Concurrency 325 3386400367Commit 26 2723168908Idle 947 2000153315Network 358 1740759767 UserI/O 459 4108307767 SystemI/O 3010 2396326234Scheduler 711 3871361733Cluster 5012 644977587Queueing 91.5 相关的几个视图：V$SESSION：代表数据库活动的开始，视为源起。
Oracle认证：Freebufferwaits等待事件总结
Oracle认证：Freebufferwaits等待事件总结Oracle认证：Freebufferwaits等待事件总结Free buffer waits1.简述：当数据库要在buffer cache中寻找空闲空间来放置数据，但发现空间不足时，就会产生这个等待;2.详述：a)在用户请求块的DBA上应用HASH函数，获得适当的hash bucket;b)检索bucket对应的chain,确认块头是否存在，若存在就使用;c)若不存在，用户进程在LRU链上按最近最少使用的顺序寻找空闲缓冲区。
若在此过程中发现脏块，则将其移到LRUW列。
找到空闲缓冲区后，就可以从数据文件将块读到该缓冲区上;d)在LRU列上寻找，一般扫描40%的比例，扫完后没有发现空闲缓冲区，就会停止扫描并驱使DBWR将脏块写到磁盘上;e)在等待dbwr写脏块的'过程中，用户进程在等待free buffer waits事件。
3.原因：三部分1.data buffer太小，导致空闲空间不够2.脏块写得慢。
a)内存中的脏数据太多，DBWR无法及时将这些脏数据写到磁盘中以释放空间，也就是可能有批量dml操作。
b)dbwr数太少，db_writer_processes参数是否设得过少，配合os上的ps -ef | grep | grep dbw查看dbwr数量c)缓慢的IO子系统，db file parallel write较多，vd)延迟块清除，即延迟块头事务标记清除。
3.要申请的空间过多a)低效率的SQL语句导致过量的物理读。
4.附录：前台进程扫描lru链表，看有没空闲的空间，一般要扫到预定义的限度才停止扫描，这个限度是LRU链表的百分比，9i中默认40%, 可以通过查询。
select * from x where kvittag = 'kcbfsp';或者查询参数 _db_block_max_scan_pct 也是40. 出现场景：imp,impdp 导数进库也可能出现这个等待。
ORACLE 数据库故障解决方案
ORACLE 数据库故障解决方案引言概述：ORACLE 数据库是目前企业常用的一种数据库管理系统，但在使用过程中难免会遇到各种故障。
本文将介绍一些常见的 ORACLE 数据库故障，并提供相应的解决方案，匡助读者更好地应对数据库故障。
一、数据库连接问题1.1 连接超时：当数据库连接超时时，可以通过增加连接超时时间的方式解决。
在 ORACLE 数据库中，可以通过修改 sqlnet.ora 文件中的SQLNET.INBOUND_CONNECT_TIMEOUT 参数来设置连接超时时间。
1.2 连接被拒绝：如果数据库连接被拒绝，可能是由于数据库实例未启动、监听器未启动或者网络故障等原因导致。
解决方案包括启动数据库实例、启动监听器以及检查网络连接是否正常。
1.3 连接池问题：当数据库连接池达到最大连接数时，新的连接请求会被拒绝。
解决方案包括增加连接池的最大连接数、释放闲置连接以及优化数据库连接的使用。
二、数据丢失问题2.1 意外删除数据：当数据被意外删除时，可以通过数据库备份和恢复的方式解决。
可以使用 RMAN 工具进行数据库备份，并在需要时使用备份进行恢复操作。
2.2 数据库文件损坏：当数据库文件损坏时，可以使用 RMAN 工具进行数据库文件的修复。
RMAN 提供了诊断和修复数据库文件的功能，可以匡助解决数据库文件损坏的问题。
2.3 数据库坏块：当数据库浮现坏块时，可以使用 RMAN 工具进行坏块的修复。
RMAN 提供了坏块检测和修复的功能，可以匡助解决数据库坏块问题。
三、性能问题3.1 慢查询：当数据库查询变慢时，可以通过优化查询语句、创建索引、增加硬件资源等方式解决。
可以使用 Explain Plan 工具来分析查询语句的执行计划，找出慢查询的原因，并进行相应的优化。
3.2 死锁：当数据库浮现死锁时，可以通过锁等待超时、死锁检测和解锁等方式解决。
可以使用 V$LOCK 和 V$SESSION 视图来查看当前的锁信息，并根据情况进行相应的解锁操作。
oracle锁等待处理方法
DECODE (l.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL)
LOCK_LEVEL,
o.owner,
o.object_name,
SELECT * FROM v$session_wait;
--1.查出锁定object的session的信息以及被锁定的object名
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
总结1：Oracle的锁表与解锁
SELECT ername,
DECODE (l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL)
lock_level,
o.owner,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
FROM v$sqlarea a, v$session s, v$locked_object l
6, 'Exclusive', 'Unknown') RequestMode,
ctime, block b
from v$lock, all_objects
where sid > 6
and v$lock.id1 = all_objects.object_id;
oracle常见等待事件及处理方法
oracle常见等待事件及处理方法Oracle是一种流行的关系型数据库管理系统，它被广泛应用于企业级应用程序中。
在使用Oracle时，我们经常会遇到等待事件，这些事件可能会导致性能下降。
本文将介绍一些常见的Oracle等待事件及其处理方法。
1. DB FILE SEQUENTIAL READDB FILE SEQUENTIAL READ是一种等待事件，它表示Oracle正在等待从磁盘读取数据块。
这种等待事件通常发生在全表扫描或索引扫描期间。
要解决这个问题，可以考虑增加缓存大小或优化查询语句。
2. DB FILE SCATTERED READDB FILE SCATTERED READ是一种等待事件，它表示Oracle正在等待从磁盘读取散布的数据块。
这种等待事件通常发生在使用I/O密集型操作时。
要解决这个问题，可以考虑增加缓存大小或优化查询语句。
3. LOG FILE SYNCLOG FILE SYNC是一种等待事件，它表示Oracle正在等待将日志文件写入磁盘。
这种等待事件通常发生在事务提交时。
要解决这个问题，可以考虑增加日志缓存大小或优化事务提交频率。
4. ENQUEUEENQUEUE是一种等待事件，它表示Oracle正在等待获取锁。
这种等待事件通常发生在并发访问数据库时。
要解决这个问题，可以考虑优化锁定策略或减少并发访问。
5. LATCHLATCH是一种等待事件，它表示Oracle正在等待获取内部数据结构的锁。
这种等待事件通常发生在高并发访问数据库时。
要解决这个问题，可以考虑增加内存大小或优化查询语句。
6. CPU TIMECPU TIME是一种等待事件，它表示Oracle正在等待CPU资源。
这种等待事件通常发生在CPU密集型操作时。
要解决这个问题，可以考虑增加CPU资源或优化查询语句。
总之，Oracle等待事件可能会导致性能下降，但我们可以通过优化查询语句、增加缓存大小、优化锁定策略等方法来解决这些问题。
Oracle11g等待事件解析
10.3 Wait Events StatisticsThe V$SESSION, V$SESSION_WAIT, V$SESSION_HISTORY, V$SESSION_EVENT, and V$SYSTEM_EVENT views provide information on what resources were waited for, and, if the configuration parameter TIMED_STATISTICS is set to true, how long each resource was waited for.See Also:∙"Setting the Level of Statistics Collection" for information about STATISTICS_LEVEL settings∙Oracle Database Reference for a description of the V$ views and the Oracle wait eventsInvestigate wait events and related timing data when performing reactive performance tuning. The events with the most time listed against them are often strong indications of the performance bottleneck.The following views contain related, but different, views of the same data:∙V$SESSION lists session information for each current session. It lists either the event currently being waited for, or the event last waited for on each session. This view also contains information about blocking sessions, the wait state, and the wait time.∙V$SESSION_WAIT is a current state view. It lists either the event currently being waited for, or the event last waited for on each session, the wait state, and the wait time.∙V$SESSION_WAIT_HISTORY lists the last 10 wait events for each current session and the associated wait time.∙V$SESSION_EVENT lists the cumulative history of events waited for on each session. After a session exits, the wait event statistics for that session are removed from this view.∙V$SYSTEM_EVENT lists the events and times waited for by the whole instance (that is, all session wait events data rolled up) since instance startup.Because V$SESSION_WAIT is a current state view, it also contains a finer-granularity of information than V$SESSION_EVENT orV$SYSTEM_EVENT. It includes additional identifying data for the current event in three parameter columns: P1, P2, and P3.For example, V$SESSION_EVENT can show that session 124 (SID=124) had many waits on the db file scattered read, but it does not show which file and block number. However, V$SESSION_WAIT shows the file number in P1, the block number read in P2, and the number of blocks read in P3 (P1 and P2 let you determine for which segments the wait event is occurring).This section concentrates on examples using V$SESSION_WAIT. However, Oracle recommends capturing performance data over an interval and keeping this data for performance and capacity analysis. This form of rollup data is queried from the V$SYSTEM_EVENT view by AWR. See "Overview of the Automatic Workload Repository".Most commonly encountered events are described in this chapter, listed in case-sensitive alphabetical order. Other event-related data to examine is also included. The case used for each event name is that which appears in the V$SYSTEM_EVENT view.Oracle Database 11g accumulates wait counts and time outs for wait events (such as in the V$SYSTEM_EVENT view) differently than in past releases. Continuous waits for certain types of resources (such as enqueues) are internally divided into a set of shorter wait calls. In prior releases, each individual internal wait call was counted as a separate wait. Starting with release 11.1, a single resource wait is recorded as a single wait, irrespective of the number of internal time outs experienced by the session during the wait.This change allows Oracle Database to display a more representative wait count, and an accurate total time spent waiting for the resource. Time outs now refer to the resource wait, instead of the individual internal wait calls. This change also affects the average wait time and the maximum wait time. For example, if a user session must wait for an enqueue in order for a transaction row lock to update a single row in a table, and it takes 10 seconds to acquire the enqueue, Oracle Database breaks down the enqueue wait into 3-second wait calls. In this example, there will be three 3-second wait calls, followed by a 1-second wait call. From the session's perspective, however, there is only one wait on an enqueue.In prior releases, the V$SYSTEM_EVENT view would represent this wait scenario as follows:∙TOTAL_WAITS: 4 waits (three 3-second waits, one 1-second wait)∙TOTAL_TIMEOUTS: 3 time outs (the first three waits time out and the enqueue is acquired during the final wait) ∙TIME_WAITED: 10 seconds (sum of the times from the 4 waits)∙AVERAGE_WAIT: 2.5 seconds∙MAX_WAIT: 3 secondsIn Oracle Database 11g, this wait scenario is represented as:∙TOTAL_WAITS: 1 wait (one 10-second wait)∙TOTAL_TIMEOUTS: 0 time outs (the enqueue is acquired during the resource wait)∙TIME_WAITED: 10 seconds (time for the resource wait)∙AVERAGE_WAIT: 10 seconds∙MAX_WAIT: 10 secondsThe following common wait events are affected by this change:∙Enqueue waits (such as enq: name - reason waits)∙Library cache lock waits∙Library cache pin waits∙Row cache lock waitsThe following statistics are affected by this change:∙Wait counts∙Wait time outs∙Average wait time∙Maximum wait timeThe following views are affected by this change:∙V$EVENT_HISTOGRAM∙V$EVENTMETRIC∙V$SERVICE_EVENT∙V$SERVICE_WAIT_CLASS∙V$SESSION_EVENT∙V$SESSION_WAIT∙V$SESSION_WAIT_CLASS∙V$SESSION_WAIT_HISTORY∙V$SYSTEM_EVENT∙V$SYSTEM_WAIT_CLASS∙V$WAITCLASSMETRIC∙V$WAITCLASSMETRIC_HISTORYSee Also:Oracle Database Reference for a description of the V$SYSTEM_EVENT view10.3.1 buffer busy waitsThis wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segment header, undo header, and undo block.Check the following V$SESSION_WAIT parameter columns:∙P1: File ID∙P2: Block IDP3: Class ID10.3.1.1 CausesTo determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:SELECT row_wait_obj#FROM V$SESSIONWHERE EVENT = 'buffer busy waits';To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned fromV$SESSION. For example:SELECT owner, object_name, subobject_name, object_typeFROM DBA_OBJECTSWHERE data_object_id = &row_wait_obj;10.3.1.2 ActionsThe action required depends on the class of block contended for and the actual segment.10.3.1.2.1 segment headerIf the contention is on the segment header, then this is most likely free list contention.Automatic segment-space management in locally managed tablespaces eliminates the need to specify the PCTUSED, FREELISTS, and FREELIST GROUPS parameters. If possible, switch from manual space management to automatic segment-space management (ASSM).The following information is relevant if you are unable to use ASSM (for example, because the tablespace uses dictionary space management).A free list is a list of free data blocks that usually includes blocks existing in several different extents within the segment. Free lists are composed of blocks in which free space has not yet reached PCTFREE or used space has shrunk below PCTUSED. Specify the number of process free lists with the FREELISTS parameter. The default value of FREELISTS is one. The maximum value depends on the data block size.To find the current setting for free lists for that segment, run the following:SELECT SEGMENT_NAME, FREELISTSFROM DBA_SEGMENTSWHERE SEGMENT_NAME = segment nameAND SEGMENT_TYPE = segment type;Set free lists, or increase the number of free lists. If adding more free lists does not alleviate the problem, then use free list groups (even in single instance this can make a difference). If using Oracle RAC, then ensure that each instance has its own free list group(s).See Also:Oracle Database Concepts for information about automatic segment-space management, free lists, PCTFREE, and PCTUSED10.3.1.2.2 data blockIf the contention is on tables or indexes (not the segment header):∙Check for right-hand indexes. These are indexes that are inserted into at the same point by many processes. For example, those that use sequence number generators for the key values.∙Consider using ASSM, global hash partitioned indexes, or increasing free lists to avoid multiple processes attempting to insert into the same block.10.3.1.2.3 undo headerFor contention on rollback segment header:∙If you are not using automatic undo management, then add more rollback segments.10.3.1.2.4 undo blockFor contention on rollback segment block:∙If you are not using automatic undo management, then consider making rollback segment sizes larger.10.3.2 db file scattered readThis event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called 'db file scattered read'. multiblock (up toDB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'.Check the following V$SESSION_WAIT parameter columns:∙P1: The absolute file number∙P2: The block being read∙P3: The number of blocks (should be greater than 1)10.3.2.1 ActionsOn a healthy system, physical read waits should be the biggest waits after the idle waits. However, also consider whether there are direct read waits (signifying full table scans with parallel query) or db file scattered read waits on an operational (OLTP) system that should be doing small indexed accesses.Other things that could indicate excessive I/O load on the system include the following:∙Poor buffer cache hit ratio∙These wait events accruing most of the wait time for a user experiencing poor response time10.3.2.2 Managing Excessive I/OThere are several ways to handle excessive I/O waits. In the order of effectiveness, these are as follows:∙Reduce the I/O activity by SQL tuning.∙Reduce the need to do I/O by managing the workload.∙Gather system statistics with DBMS_STATS package, allowing the query optimizer to accurately cost possible access paths that use full scans.∙Use Automatic Storage Management.∙Add more disks to reduce the number of I/Os for each disk.∙Alleviate I/O hot spots by redistributing I/O across existing disks.See Also:Chapter 8, "I/O Configuration and Design"The first course of action should be to find opportunities to reduce I/O. Examine the SQL statements being run by sessions waiting for these events and statements causing high physical I/Os from V$SQLAREA. Factors that can adversely affect the execution plans causing excessive I/O include the following:∙Improperly optimized SQL∙Missing indexes∙High degree of parallelism for the table (skewing the optimizer toward scans)∙Lack of accurate statistics for the optimizer∙Setting the value for DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter too high which favors full scans10.3.2.3 Inadequate I/O DistributionBesides reducing I/O, also examine the I/O distribution of files across the disks. Is I/O distributed uniformly across the disks, or are there hot spots on some disks? Are the number of disks sufficient to meet the I/O needs of the database?See the total I/O operations (reads and writes) by the database, and compare those with the number of disks used. Remember to include the I/O activity of LGWR and ARCH processes.10.3.2.4 Finding the SQL Statement executed by Sessions Waiting for I/OUse the following query to determine, at a point in time, which sessions are waiting for I/O:SELECT SQL_ADDRESS, SQL_HASH_VALUEFROM V$SESSIONWHERE EVENT LIKE 'db file%read';10.3.2.5 Finding the Object Requiring I/OTo determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for db file scattered read. For example:SELECT row_wait_obj#FROM V$SESSIONWHERE EVENT = 'db file scattered read';To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned fromV$SESSION. For example:SELECT owner, object_name, subobject_name, object_typeFROM DBA_OBJECTSWHERE data_object_id = &row_wait_obj;10.3.3 db file sequential readThis event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call because of extent boundaries, or buffers present in the buffer cache. These waits would also show up as db file sequential read.Check the following V$SESSION_WAIT parameter columns:∙P1: The absolute file number∙P2: The block being read∙P3: The number of blocks (should be 1)See Also:"db file scattered read" for information about managing excessive I/O, inadequate I/O distribution, and finding the SQL causing the I/O and the segment the I/O is performed on10.3.3.1 ActionsOn a healthy system, physical read waits should be the biggest waits after the idle waits. However, also consider whether there are db file sequential reads on a large data warehouse that should be seeing mostly full table scans with parallel query.Figure 10-1 depicts the differences between the following wait events:∙db file sequential read (single block read into one SGA buffer)∙db file scattered read (multiblock read into many discontinuous SGA buffers)∙direct read (single or multiblock read into the PGA, bypassing the SGA)Figure 10-1 Scattered Read, Sequential Read, and Direct Path ReadDescription of "Figure 10-1 Scattered Read, Sequential Read, and Direct Path Read"10.3.4 direct path read and direct path read tempWhen a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).Check the following V$SESSION_WAIT parameter columns:∙P1: File_id for the read call∙P2: Start block_id for the read call∙P3: Number of blocks in the read call10.3.4.1 CausesThis situation occurs in the following situations:∙The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.∙Parallel slaves are used for scanning data.∙The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.10.3.4.2 ActionsThe file_id shows if the reads are for an object in TEMP tablespace (sorts to disk) or full table scans by parallel slaves. This wait is the largest wait for large data warehouse sites. However, if the workload is not a Decision Support Systems (DSS) workload, then examine why this situation is happening. 10.3.4.2.1 Sorts to DiskExamine the SQL statement currently being run by the session experiencing waits to see what is causing the sorts. Query V$TEMPSEG_USAGE to find the SQL statement that is generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. See if it is possible to reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing theSORT_AREA_SIZE for the system (if the sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET. See "PGA Memory Management".10.3.4.2.2 Full Table ScansIf tables are defined with a high degree of parallelism, then this setting could skew the optimizer to use full table scans with parallel slaves. Check the object being read into using the direct path reads. If the full table scans are a valid part of the workload, then ensure that the I/O subsystem is adequate for the degree of parallelism. Consider using disk striping if you are not already using it or Oracle Automatic Storage Management (Oracle ASM).10.3.4.2.3 Hash Area SizeFor query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the HASH_AREA_SIZE for the system or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET.See Also:∙"Managing Excessive I/O"∙"PGA Memory Management"10.3.5 direct path write and direct path write tempWhen a process is writing buffers directly from PGA (as opposed to the DBWR writing them from the buffer cache), the process waits on this event for the write call to complete. Operations that could perform direct path writes include sorts on disk, parallel DML operations, direct-path INSERT s, parallel create table as select, and some LOB operations.Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session waits if it has processed all buffers in the PGA and cannot continue work until an I/O request completes.See Also:Oracle Database Administrator's Guide for information about direct-path insertsCheck the following V$SESSION_WAIT parameter columns:∙P1: File_id for the write call∙P2: Start block_id for the write call∙P3: Number of blocks in the write call10.3.5.1 CausesThis happens in the following situations:∙Sorts are too large to fit in memory and are written to disk∙Parallel DML are issued to create/populate objects∙Direct path loads10.3.5.2 ActionsFor large sorts see "Sorts to Disk".For parallel DML, check the I/O distribution across disks and ensure that the I/O subsystem is adequately configured for the degree of parallelism.10.3.6 enqueue (enq:) waitsEnqueues are locks that coordinate access to database resources. This event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is included as part of the wait event name, in the form enq:enqueue_type-related_details. In some cases, the same enqueue type can be held for different purposes, such as the following related TX types:∙enq:TX-allocate ITL entry∙enq:TX-contention∙enq:TX-index contention∙enq:TX-row lock contentionThe V$EVENT_NAME view provides a complete list of all the enq: wait events.You can check the following V$SESSION_WAIT parameter columns for additional information:∙P1: Lock TYPE (or name) and MODE∙P2: Resource identifier ID1 for the lock∙P3: Resource identifier ID2 for the lockSee Also:Oracle Database Reference for information about Oracle Database enqueues10.3.6.1 Finding Locks and Lock HoldersQuery V$LOCK to find the sessions holding the lock. For every session waiting for the event enqueue, there is a row in V$LOCK with REQUEST <> 0. Use one of the following two queries to find the sessions holding the locks and waiting for the locks.If there are enqueue waits, you can see these using the following statement:SELECT * FROM V$LOCK WHERE request > 0;To show only holders and waiters for locks being waited on, use the following:SELECT DECODE(request,0,'Holder: ','Waiter: ') ||sid sess, id1, id2, lmode, request, typeFROM V$LOCKWHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)ORDER BY id1, request;10.3.6.2 ActionsThe appropriate action depends on the type of enqueue.10.3.6.2.1 ST enqueueIf the contended-for enqueue is the ST enqueue, then the problem is most likely to be dynamic space allocation. Oracle Database dynamically allocates an extent to a segment when there is no more free space available in the segment. This enqueue is only used for dictionary managed tablespaces.To solve contention on this resource:∙Check to see whether the temporary (that is, sort) tablespace uses TEMPFILES. If not, then switch to using TEMPFILES.∙Switch to using locally managed tablespaces if the tablespace that contains segments that are growing dynamically is dictionary managed.See Also:Oracle Database Concepts for detailed information on TEMPFILE s and locally managed tablespaces∙If it is not possible to switch to locally managed tablespaces, then ST enqueue resource usage can be decreased by changing the next extent sizes of the growing objects to be large enough to avoid constant space allocation. To determine which segments are growing constantly, monitor the EXTENTS column of the DBA_SEGMENTS view for all SEGMENT_NAMEs. See Oracle Database Administrator's Guide for information about displaying information about space usage.∙Preallocate space in the segment, for example, by allocating extents using the ALTER TABLE ALLOCATE EXTENT SQL statement.10.3.6.2.2 HW enqueueThe HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment.∙V$SESSION_WAIT.P2 / V$LOCK.ID1 is the tablespace number.∙V$SESSION_WAIT.P3 / V$LOCK.ID2 is the relative data block address (dba) of segment header of the object for which space is being allocated.If this is a point of contention for an object, then manual allocation of extents solves the problem.10.3.6.2.3 TM enqueueThe most common reason for waits on TM locks tend to involve foreign key constraints where the constrained columns are not indexed. Index the foreign key columns to avoid this problem.10.3.6.2.4 TX enqueueThese are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.∙Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is held by another session. This occurs when one user is updating or deleting a row, which another session wants to update or delete. This type of TX enqueue wait corresponds to the wait event enq:TX-row lock contention.The solution is to have the first session holding the lock perform a COMMIT or ROLLBACK.∙Waits for TX in mode 4 can occur if the session is waiting for an ITL (interested transaction list) slot in a block. This happens when the session wants to lock a row in the block but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block. Usually, Oracle Database dynamically adds another ITL slot. This may not be possible if there is insufficient free space in the block to add an ITL. If so, the session waits for a slot with a TX enqueue in mode 4. This type of TX enqueue wait corresponds to the wait event enq:TX-allocate ITL entry.The solution is to increase the number of ITLs available, either by changing the INITRANS or MAXTRANS for the table (either by using an ALTER statement, or by re-creating the table with the higher values).∙Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq:TX-row lock contention.The solution is to have the first session holding the lock perform a COMMIT or ROLLBACK.∙Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.This type of TX enqueue wait corresponds to the wait event enq:TX-row lock contention.∙Waits for TX in Mode 4 can also occur waiting for a PREPARED transaction.∙Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq:TX-index contention.See Also:Oracle Database Advanced Application Developer's Guide for more information about referential integrity and locking data explicitly10.3.7 events in wait class otherThis event belong to Other wait class and typically should not occur on a system. This event is an aggregate of all other events in the Other wait class, such as latch free, and is used in the V$SESSION_EVENT and V$SERVICE_EVENT views only. In these views, the events in the Other wait class will not be maintained individually in every session. Instead, these events will be rolled up into this single event to reduce the memory used for maintaining statistics on events in the Other wait class.10.3.8 free buffer waitsThis wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make free buffers by writing out dirty buffers. A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk.10.3.8.1 CausesDBWR may not be keeping up with writing dirty buffers in the following situations:∙The I/O system is slow.∙There are resources it is waiting for, such as latches.∙The buffer cache is so small that DBWR spends most of its time cleaning out buffers for server processes.∙The buffer cache is so big that one DBWR process is not enough to free enough buffers in the cache to satisfy requests.10.3.8.2 ActionsIf this event occurs frequently, then examine the session waits for DBWR to see whether there is anything delaying DBWR.10.3.8.2.1 WritesIf it is waiting for writes, then determine what is delaying the writes and fix it. Check the following:∙Examine V$FILESTAT to see where most of the writes are happening.∙Examine the host operating system statistics for the I/O system. Are the write times acceptable?If I/O is slow:∙Consider using faster I/O alternatives to speed up write times.∙Spread the I/O activity across large number of spindles (disks) and controllers. See Chapter 8, "I/O Configuration and Design" for information about balancing I/O.10.3.8.2.2 Cache is Too SmallIt is possible DBWR is very active because the cache is too small. Investigate whether this is a probable cause by looking to see if the buffer cache hit ratio is low. Also use the V$DB_CACHE_ADVICE view to determine whether a larger cache size would be advantageous. See "Sizing the Buffer Cache". 10.3.8.2.3 Cache Is Too Big for One DBWRIf the cache size is adequate and the I/O is evenly spread, then you can potentially modify the behavior of DBWR by using asynchronous I/O or by using multiple database writers.。
Oracle常见等待事件说明
Oracle的等待事件是衡量Oracle运行状况的重要依据及指标。
等待事件的概念是在Oracle7.0.1.2中引入的，大致有100个等待事件。
在Oracle 8.0中这个数目增加到了大约150个，在Oracle8i中大约有200个事件,在Oracle9i中大约有360个等待事件。
主要有两种类别的等待事件，即空闲(idle)等待事件和非空闲(non-idle)等待事件。
空闲事件指Oracle正等待某种工作,在诊断和优化数据库的时候,我们不用过多注意这部分事件。
常见的空闲事件有:• dispatcher timer• lock element cleanup• Null event• parallel query dequeue wait• parallel query idle wait - Slaves• pipe get• PL/SQL lock timer• pmon timer- pmon• rdbms ipc message• slave wait• smon timer• SQL*Net break/reset to client• SQL*Net message from client• SQL*Net message to client• SQL*Net more data to client• virtual circuit status• client message非空闲等待事件专门针对Oracle的活动,指数据库任务或应用运行过程中发生的等待，这些等待事件是我们在调整数据库的时候应该关注与研究的。
一些常见的非空闲等待事件有:• db file scattered read• db file sequential read• buffer busy waits• free buffer waits• enqueue• latch free• log file parallel write• log file sync1. db file scattered read-DB 文件分散读取这种情况通常显示与全表扫描相关的等待。
Oracle等待事件详解
Oracle等待事件详解个人分类：体系结构篇一．等待事件的相关知识：1.1 等待事件主要可以分为两类：即空闲(IDLE)等待事件和非空闲(NON-I DLE)等待事件。
1). 空闲等待事件指ORAC LE正等待某种工作，在诊断和优化数据库的时候，不用过多注意这部分事件。
2).非空闲等待事件专门针对ORAC LE的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是在调整数据库的时候需要关注与研究的。
在Or acle10g中的等待事件有872个，11g中等待事件1116个。
我们可以通过v$ev ent_n ame 视图来查看等待事件的相关信息。
1.2查看v$e vent_name视图的字段结构：SQ L> de sc v$event_name;名称是否为空?类型----------------------------------------- -----------------------EVE NT# NU MBEREVEN T_ID NUM BERNAME VARC HAR2(64)PARAM ETER1 VARC HAR2(64)PARAM ETER2 VARC HAR2(64)PARAM ETER3 VARC HAR2(64)WAIT_CLASS_ID NUMB ERW AIT_C LASS#NUMBE RWA IT_CL ASS V ARCHA R2(64)1.3 查看等待事件总数：SQL> sel ect c ount(*) fr om v$event_name;C OUNT(*)----------11161.4查看等待事件分类情况：/*Forma ttedon 2010/8/11 16:08:55 (QP5 v5.115.810.9015) */SEL ECT wait_class#, wait_clas s_id,w ait_c lass,C OUNT( * ) AS "count"FRO M v$eve nt_na meGR OUP B Y w ait_c lass#, wai t_cla ss_id, wai t_cla ssOR DER B Y w ait_c lass#;WAI T_CLA SS# W AIT_C LASS_ID WA IT_CL ASS c ount----------- ------------- -------------------- ---------- 0 1893977003 O ther 717 1 4217450380 App licat ion 1723290255840Confi gurat ion 24 3 4166625743 Ad minis trati ve 544 3875070507 Conc urren cy 32 5 3386400367 C ommit2 6 2723168908 Idl e 9472000153315Netwo rk 35 8 1740759767 Us er I/O 459 4108307767 Syst em I/O 30 10 2396326234 S chedu ler 711 3871361733 Clu ster 50 12644977587 Q ueuei ng 91.5相关的几个视图：V$SES SION：代表数据库活动的开始，视为源起。
oracle中“ORA-00060：等待资源时检测到死锁”或存储过程编译卡死解决方法
--3、查看引起死锁会话-select ername,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
3、查看被阻塞的会话：
--3、查看被阻塞的会话-select * from dba_waiters;
4、释放锁或者杀掉ORACLE进程：
--4、释放锁或者杀掉Oracle进程-alter system kill sessi体情况根据每个人情况不一样，sid,serial#的值也不一样
博客园 用户登录 代码改变世界 密码登录 短信登录 忘记登录用户名 忘记密码 记住我 登录 第三方登录/注册 没有账户, 立即注册
oracle中 “ORA-00060：等待资源时检测到死锁 ”或存储过程编译 卡死解决方法
之前在调试存储过程时，出现卡死情况，无法插入数据
解决方法
1、查看那些表被锁住：
--1、查看那些表被锁住--select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
OracleLatchfree等待事件
Latch free等待事件原文：oracle wait interface—a practical guide to performance diagnostics & tuning Richmond sheeKirtikumar deshpandeK gopalakrishnanMcgraw-hill/osborne2100 powell street, 10th floorEmeryville, california 94608U.s.a.Chapter 6: interpreting locks-related wait events - latch free文档修订历史：本文只是对原文的一个大概的翻译，建议最好还是阅读原文。
如果你发现有任何问题，欢迎反馈****************Latch free等待事件的三个参数：p1－latch的地址；p2－latch编号；p3－请求次数。
从oracle10g起，latch free不再包含所有的latch等待，有些latch等待可能表现为单独的等待事件，这个后面有提到一些这样的等待事件，一般情况下我们还是统称为latch free等待事件。
在处理latch free等待事件时，需要注意以下几点：⏹Latch只是用来保护sga中的内存结构。
对数据库中的对象的保护，使用的lock而不是latch。
Oracle sga中有许多latch，用来保护sga中各种内存结构不会因为并发访问而损坏。
⏹等待latch的是oracle会话。
不同的latch类型会导致会话采取不同的策略。
⏹在oracle9i（包括9i）之前，latch free等待事件包括了所有的latch等待，但从oracle10g起，latch被分成不同的种类，并且某些latch表现为独立的等待事件。
什么是latchLatch是一种锁机制。
你应该已经熟悉latch的概念和用法，虽然可能你自己并没有意识到。
[ORACLE]oraclesql执行过程发生的等待事件
![[ORACLE]oraclesql执行过程发生的等待事件](https://img.taocdn.com/s3/m/e2cd8627a22d7375a417866fb84ae45c3b35c2ac.png)
[ORACLE]oraclesql执⾏过程发⽣的等待事件1.Parse 阶段常见的等待事件:Library cache pin这个等待事件和library cache lock ⼀样是发⽣在共享池中并发操作引起的事件。
通常来讲，如果Oracle 要对⼀些PL/SQL 或者视图这样的对象做重新编译，需要将这些对象pin到共享池中。
如果此时这个对象被其他的⽤户特有，就会产⽣⼀个library cache pin的等待。
这个等待事件也包含四个参数：Handle address: 被加载的对象的地址。
Lock address：锁的地址。
Mode：被加载对象的数据⽚段。
Namespace：被加载对象在v$db_object_cache 视图中namespace名称。
Library cache pin这个等待事件和library cache lock ⼀样是发⽣在共享池中并发操作引起的事件。
通常来讲，如果Oracle 要对⼀些PL/SQL 或者视图这样的对象做重新编译，需要将这些对象pin到共享池中。
如果此时这个对象被其他的⽤户特有，就会产⽣⼀个library cache pin的等待。
这个等待事件也包含四个参数：Handle address: 被加载的对象的地址。
Lock address：锁的地址。
Mode：被加载对象的数据⽚段。
Namespace：被加载对象在v$db_object_cache 视图中namespace名称latch:shared poolpin S wait on Xibrary cache: mutex X/cursor: pin S2.Execute阶段常见的等待事件:db file sequential read这个等待事件在实际⽣产库也很常见，当Oracle 需要每次I/O只读取单个数据块这样的操作时，会产⽣这个等待事件。
最常见的情况有索引的访问（除IFFS外的⽅式），回滚操作，以ROWID的⽅式访问表中的数据，重建控制⽂件，对⽂件头做DUMP等。
Oracle数据库发生等待事件：enq：TX-rowlockcontention，排查思路
Oracle数据库发⽣等待事件：enq：TX-rowlockcontention，排查思路⽬录前⾔最近看 awr 报告时，经常会看到⼀些enq: TX - row lock contention的等待事件，所以简单研究⼀下如何排查，仅为个⼈所见，如有异议或者修正还请评论指出，谢谢！通常，产⽣enq: TX - row lock contention事件的原因有以下⼏种可能：不同的session更新或删除同⼀条记录；唯⼀索引有重复索引；位图索引同时被更新或同时并发的向位图索引字段上插⼊相同字段值；并发的对同⼀个数据块上的数据进⾏update操作；等待索引块完成分裂；现象应⽤反馈系统使⽤存在延时，需要排查情况。
查看监控服务器，发现数据库存在enq: TX - row lock contention锁的情况。
排查⾸先确认发⽣问题的时间段，然后抓取问题时间段的报告来分析。
AWR 报告执⾏sqlplus / as sysdba @?/rdbms/admin/awrrpt.sql输⼊对应时间段的信息，获取 awr 报告。
Top 10 Foreground Events by Total Wait Time也可通过 awrcrt sqlplus / as sysdba @awrcrt.sql来获取多段性能指标信息：通过观察 awr 报告中段的统计信息章节Segments by Row Lock Waits项，可以发现发⽣锁的对象主要是两张表A和B和A表的索引：与应⽤确认后，发现其中⼀张表A为核⼼业务表，暂时怀疑另⼀张表可能存在问题，这⾥称之为表B，所以A表暂且不考虑。
SQL ordered by Elapsed Time通过 搜索关键字，查出 B 表对应的UPDATE语句，执⾏较为频繁，先记录待查看：sql_id 为：2xb71ufa5wmrh。
ASH 报告抓取对应时间段的 ash 报告，查看是否存在有⽤信息。
D3-03_RAC_等待事件
16/85
Oracle系统等待事件-跟踪
10046事件 : 10046 是Oracle系统性能分析一个重要的事件 当激活这个事件后，将通知Oracle kernel追踪会话 的相关即时信息，并写入到相应trace文件中 。 信息包括SQL语句解析，绑定变量的使用，会话中 发生的等待事件等 。 10046 Event 可分成不同的级别： level 1：跟踪sql语句，包括解析、执行、提取 等。 level 4：包括变量的详细信息 ； level 8：包括等待事件 ； level 12：包括绑定变量与等待事件 。
Waits
Lock Redo SQL Net
TX Row Lock
TX ITL Lock HW Lock
Log File Log Buffer
Log File Sync
22/85
等待事件接口
Oracle等待事件接口-视图
:
V$Sxxx
V$SESSION
& GV$xxx
V$SESSION_WAIT
Oracle RDBMS
RAC与等待事件
赵元杰 zyj5681@ 2012.11
2/85
内容提要
生活中的等待 Oracle 系统等待事件 Oracle 事件构成与接口 Oracle RAC常见等待事件 Interconnect 调整 参考资料
3/85
Waiting, Waiting, Waiting
搜集等待统计 : TIMED_STATISTICS参数被设置为TRUE时，等待 （wait）才会被计时。 设置统计为TRUE对系统影响不大，可设置一段时 间再取消。 如果TIMED_STATISTICS=FALSE，Oracle也会记录 每个等待的开始与结束原因及是否发生超时 。 如果TIMED_STATISTICS=TRUE，Oracle会检查每 个等待前后的时刻并记录等待所花的时间以百分之 一秒。
Oracle数据库缓冲区忙等待的原因解析
众多Oracle有关问题中，其中最重要的一个是缓冲区忙等待(buffer busy wait)事件。
缓冲区忙等待是I/O-bound Oracle系统中最常见的现象，尤其是在Oracle STATSPACK报告的前五个忙等待的读(顺序/分散)系统中，如前5个定时事件：% 总和事件等待时间(s)消逝时间--------------------------- ------------ ----------------------db文件顺序读 2,5987,146 48.54db文件分散读25,5193,246 22.04库缓冲区载入死锁6731,3639.26CPU时间2,1549347.83日志文件平行写 19,1578375.68减轻缓冲区忙等待的主要方式是减少系统中的I/O，这可以通过SQL使用更少的块读(block reads，比如添加索引)的方式得以实现。
即使对于一个比较大的db_cache_size，我们也可以减少缓冲区忙等待的时间。
为了能够查看整个系统的等待事件，我们可以查阅v$system_event性能视图。
这一性能视图提供了等待事件的名称，等待事件与时间的总和，以及每一事件的平均等待时间。
可以通过v$waitstat视图来查询导致等待的缓冲区的类型。
这一视图列出了每一缓冲区类型的等待，COUNT是类所有的等待总和，TIME是这一类所有等待的时间总和，如下所示：select * from v$waitstat;类 COUNTTIME------------------ ---------- ----------datablock19611131870278segment header34535 159082undoheader23363286239undo block 1886 1706当一个session访问缓冲区的块时，就有可能产生缓冲忙等待。
这一缓冲区忙等待的产生可能由以下的原因造成的：块可能被其它的session读到缓冲区，所以session必须等待块的读入结束。
Oracle出现大量CSS initialization 等待事件
oracle出现大量CSS initialization 等待事件1环境情况OS: AIX 5.3.0.0 ORACLE:10.2.0.5.0 RAW(裸设备) 架构：DataGuard 2问题描述在一次性能基线分析时发现数据库出现大量CSS initialization 等待事件（图一），通过sql_id 找到了对应sql语句（图二）。
语句主要功能是查询asm使用情况，是监控软件使用的sql语句，此语句执行时间在20秒左右。
该系统没有使用RAC和ASM，一般来说不会出现CSS的等待事件。
图一图二3问题分析问题主要原因在查询到的这条语句上，对语句做10046，查看语句执行情况。
图三是语句10046 trace 文件，从图中标红位置可以看出sql语句执行大量时间耗在了CSS initialization 等待事件，查询me talink，发现有个Bug 10024824 –Database/session hang with 'CSS initialization'（图四），版本是10.2.0.5，而我们的数据库版本也正好是这个版本。
从bug 信息中可以得到导致问题的原因和OH/log/<no de>/client 目录权限有关，检查目录有读写权限，可以排除权限问题。
检查文件个数发现已经接近2万个文件，测试发现每次执行语句就会在OH/log/<node>/client 目录下产生一个cssN.log 文件，使用truss追踪该进程发现进程需要将这两万文件都访问一遍（图五），猜想程序应是需要遍历整个client目录下的cssN.log 才知道最大数字是多少，才能生成N+1 的文件。
这种情况会导致随着cssN.log文件的增多，使查询越来越慢。
图三图四图五4处理建议定时清除OH/log/<node>/client 目录下cssN.log 文件，或者取消监控中的sql语句，因为此环境没有使用asm，所以没必要监控此项。
180_ORACLE常见等待事件
Oracle的等待事件是衡量Oracle运行状况的重要依据及指标。
等待事件的概念是在Oracle7.0.1.2中引入的，大致有100个等待事件。
在Oracle 8.0中这个数目增加到了大约150个，在Oracle8i中大约有200个事件,在Oracle9i中大约有360个等待事件。
主要有两种类别的等待事件，即空闲(idle)等待事件和非空闲(non-idle)等待事件。
空闲事件指Oracle正等待某种工作,在诊断和优化数据库的时候,我们不用过多注意这部分事件。
常见的空闲事件有:• disp atcher timer• lock element cleanup• Null event• parallel query dequeue wait• parallel query idle wait - Slaves• pipe get• PL/SQL lock timer• pmon timer- pmon• rdbms ipc message• slave wait• smon timer• SQL*Net break/reset t o client• SQL*Net message from client• SQL*Net message to client• SQL*Net more data to client• virtual circuit status• client message非空闲等待事件专门针对Oracle的活动,指数据库任务或应用运行过程中发生的等待，这些等待事件是我们在调整数据库的时候应该关注与研究的。
一些常见的非空闲等待事件有:• db file scatter ed read• db file sequential read• buffer busy waits• free buffer waits• enqueue• latch free• log file parallel write• log file sync查看整个系统的等待事件(rainny)：select*from v$system_event twhere t.WAIT_CLASS<>'Idle'order by t.TIME_WAITED desc；1. db file scattered read-DB 文件分散读取这种情况通常显示与全表扫描相关的等待。
性能优化：调整IO相关的等待
性能优化：调整IO相关的等待编辑手记:对Oracle数据库进行调整优化，基本上最终都可以归结到I/O调整上，因此，了解如何来优化Oracle数据库的I/O对于一个DBA来说就显得至关重要。
今天挑选此文与大家共勉。
I/O相关竞争等待简介当Oracle数据库出现I/O相关的竞争等待的时候，一般来说都会引起Oracle数据库的性能低下，发现数据库存在I/O相关的竞争等待一般可以通过以下的三种方法来查看Oracle数据库是否存在I/O相关的竞争等待：（1）Statpack报告中在"Top 5 WaitEvents"部分中主要都是I/O相关的等待事件。
（2）数据库的等待事件的SQL语句跟踪中主要都是I/O相关的等待事件的限制。
（3）操作系统工具显示存储数据库文件的存储磁盘有非常高的利用率。
数据库如果发现存在I/O竞争，那我们就必须要通过各种方法来调整优化Oracle数据库。
在调优数据库的过程中，其中一个重要的步骤就是对响应时间的分析，看看数据库消耗的时间究竟是消耗在具体什么上面了。
对于Oracle数据库来说，响应时间的分析可以用下面公式来计算：Response Time = Service Time + WaitTimeService Time是指'CPU used by this session'的统计时间。
Wait Time是指所有消耗在等待事件上的总的时间。
因为等待事件有很多，因此我们还需要去判定哪些是真的很重要的等待事件，很多调优工具比如说statpack都是列出最重要的等待事件，statpack工具的报告中的重要的等待事件都是包含在一个叫Top 5 Wait Events的部分中。
在某些情况下， Service Time会比WaitTime显得更加重要(例如CPU使用率),此时等待事件产生的影响就显得不是那么重要了，重点调整的目标应该放在Service Time上。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
我们可以通过视图v$session_wait来查看系统当前的等待事件，以及与等待事件相对应的资源的相关信息看书笔记db file scattered read DB ,db file sequential read DB,free buffer waits,log buffer space,log file switch,log file sync我们可以通过视图v$session_wait来查看系统当前的等待事件，以及与等待事件相对应的资源的相关信息，从而可确定出产生瓶颈的类型及其对象。
v$session_wait的p1、p2、p3告诉我们等待事件的具体含义，根据事件不同其内容也不相同，下面就一些常见的等待事件如何处理以及如何定位热点对象和阻塞会话作一些介绍。
<1> db file scattered read DB 文件分散读取(太多索引读,全表扫描-----调整代码,将小表放入内存)这种情况通常显示与全表扫描相关的等待。
当全表扫描被限制在内存时，它们很少会进入连续的缓冲区内，而是分散于整个缓冲存储器中。
如果这个数目很大，就表明该表找不到索引，或者只能找到有限的索引。
尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。
因为全表扫描被置于LRU(Least Recently Used，最近最少适用)列表的冷端（cold end），所以应尽量存储较小的表，以避免一次又一次地重复读取它们。
==================================================该类事件的p1text=file#,p1是file_id，p2是block_id,通过dba_extents即可确定出热点对象(表或索引)select owner,segment_name,segment_typefrom dba_extentswhere file_id = &file_idand &block_id between block_id and block_id + &blocks - 1;==================================================<2> db file sequential read DB 文件顺序读取(表连接顺序不佳-----调整代码,特别是表连接)这一事件通常显示单个块的读取(如索引读取)。
这种等待的数目很多时，可能显示表的连接顺序不佳，或者不加选择地进行索引。
对于大量事务处理、调整良好的系统，这一数值大多是很正常的，但在某些情况下，它可能暗示着系统中存在问题。
你应当将这一等待统计量与Statspack 报告中的已知问题（如效率较低的SQL）联系起来。
检查索引扫描，以保证每个扫描都是必要的，并检查多表连接的连接顺序。
DB_CACHE_SIZE 也是这些等待出现频率的决定因素。
有问题的散列区域（Hash-area）连接应当出现在PGA 内存中，但它们也会消耗大量内存，从而在顺序读取时导致大量等待。
它们也可能以直接路径读／写等待的形式出现。
=================================================== 该类事件的p1text=file#,p1是file_id，p2是block_id,通过dba_extents即可确定出热点对象(表或索引)select owner,segment_name,segment_typefrom dba_extentswhere file_id = &file_idand &block_id between block_id and block_id + &blocks - 1;==================================================<3> free buffer waits 释放缓冲区等待(增大DB_CACHE_SIZE,加速检查点,调整代码)这种等待表明系统正在等待内存中的缓冲，因为内存中已经没有可用的缓冲空间了。
如果所有SQL 都得到了调优，这种等待可能表示你需要增大DB_BUFFER_CACHE。
释放缓冲区等待也可能表示不加选择的SQL 导致数据溢出了带有索引块的缓冲存储器，没有为等待系统处理的特定语句留有缓冲区。
这种情况通常表示正在执行相当多数量的DML（插入／更新／删除），并且数据库书写器(DBWR)写的速度不够快，缓冲存储器可能充满了相同缓冲器的多个版本，从而导致效率非常低。
为了解决这个问题，可能需要考虑增加检查点、利用更多的DBWR 进程，或者增加物理磁盘的数量。
<4> buffer busy waits 缓冲区忙等待(BUFFER热块)这是为了等待一个以非共享方式使用的缓冲区，或者正在被读入缓冲存储器的缓冲区。
缓冲区忙等待不应大于1%。
检查缓冲等待统计部分（或V$WAITSTAT）:A、如果等待处于字段头部，应增加自由列表（freelist）的组数，或者增加pctused到pctfree 之间的距离。
B、如果等待处于回退段（undo）头部块，可以通过增加回滚段(rollback segment)来解决缓冲区的问题；C、如果等待处于回退段（undo）非头部块上，就需要降低驱动一致读取的表中的数据密度,或者增大DB_CACHE_SIZE；D、如果等待处于数据块，可以将数据移到另一数据块以避开这个"热"数据块、增加表中的自由列表或使用LMT表空间；E、如果等待处于索引块，应该重建索引、分割索引或使用反向键索引。
为了防止与数据块相关的缓冲忙等待，也可以使用较小的块：在这种情况下，单个块中的记录就较少，所以这个块就不是那么"繁忙"。
在执行DML(插入/更新/删除)时,Oracle DBWR 就向块中写入信息，包括所有对块状态"感兴趣"的用户(感兴趣的事务表，ITL)。
为减少这一区域的等待，可以增加initrans，这样会在块中创建空间，从而使你能够使用多个ITL槽。
你也可以增加该块所在表中的pctfree(当根据指定的initrans 建立的槽数量不足时，这样可以使ITL 信息数量达到maxtrans 指定的数量）。
<6> enqueueenqueue 是一种保护共享资源的锁定机制。
该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更新同一数据。
enqueue 包括一个排队机制，即FIFO(先进先出)排队机制。
注意：Oracle 的latch 机制不是FIFO。
Enqueue 等待通常指的是ST enqueue、HW enqueue、TX4 enqueue 和TM enqueue。
A、ST enqueue 用于空间管理和字典管理的表空间的分配。
利用LMT，或者试图对区域进行预分配，或者至少使下一个区域大于有问题的字典管理的表空间。
B、HW enqueue 与段的高水位标记一起使用；手动分配区域可以避免这一等待。
C、TX4 enqueue是最常见的enqueue 等待，通常是以下三个问题之一产生的结果:第一个问题是唯一索引中的重复索引，需要执行提交（commit）/回滚（rollback）操作来释放enqueue。
第二个问题是对同一位图索引段的多次更新。
因为单个位图段可能包含多个行地址(rowid),所以当多个用户试图更新同一段时,你需要执行提交或回滚操作,以释放enqueue。
第三个问题，也是最可能发生的问题是多个用户同时更新同一个块。
如果没有自由的ITL槽，就会发生块级锁定。
通过增大initrans 和/或maxtrans以允许使用多个ITL槽，或者增大表上的pctfree 值，就可以很轻松地避免这种情况。
D、TM enqueue 在DML 期间产生，以避免对受影响的对象使用DDL。
如果有外来关键字，一定要对它们进行索引，以避免这种常见的锁定问题。
<7> log buffer space 日志缓冲空间(写REDO慢-----增大log_buffer,redo log file放到快速磁盘上)当日志缓冲(log buffer)写入重做日志(redo log)的速度比LGWR 的写入速度慢，或者是当日志转换(log switch)太慢时，就会发生这种等待。
为解决这个问题，可以增大日志文件的大小，或者增加日志缓冲器的大小，或者使用写入速度更快的磁盘。
甚至可以考虑使用固态磁盘，因为它们的速度很高。
<8> log file switch 日志文件转换(归档慢-----增加或者扩大重做日志)有两种情况：A、log file switch (archiving needed)当日志切换的时候由于日志组循环使用了一圈但日志归档还没有完成，通常是io有严重问题，可增大日志文件和增加日志组，调整log_archive_max_processesB、log file switch (checkpoint incomplete)当日志切换的时候由于日志组循环使用了一圈但将被使用的日志组中的checkpoint还没有完成造成，通常是io有严重问题，可增大日志文件和增加日志组<9> log file sync 日志文件同步(提交太频繁----批量提交)当用户commit的时候通知lgwr写日志但lwgr正忙，造成的可能原因是commit太频繁或者lgwr一次写日志时间太长（可能是因为一次log io size 太大），可调整_log_io_size，结合log_buffer,使得(_log_io_size*db_block_size)*n = log_buffer,这样可避免和增大log_buffer引起冲突;放置日志文件于高速磁盘上<10> library cache pin该事件通常是发生在先有会话在运行PL/SQL,VIEW,TYPES等object时,又有另外的会话执行重新编译这些object,即先给对象加上了一个共享锁,然后又给它加排它锁,这样在加排它锁的会话上就会出现这个等待。
P1,P2可与x$kglpn和x$kglob表相关X$KGLOB (Kernel Generic Library Cache Manager Object)X$KGLPN (Kernel Generic Library Cache Manager Object Pins)-- 查询X$KGLOB,可找到相关的object,其SQL语句如下(即把V$SESSION_WAIT中的P1raw与X$KGLOB中的KGLHDADR相关连)select kglnaown,kglnaobj from X$KGLOBwhere KGLHDADR =(select p1raw from v$session_waitwhere event='library cache pin')-- 查出引起该等待事件的阻塞者的sidselect sid from x$kglpn , v$sessionwhere KGLPNHDL in(select p1raw from v$session_waitwhere wait_time=0 and event like 'library cache pin%')and KGLPNMOD <> 0and v$session.saddr=x$kglpn.kglpnuse-- 查出阻塞者正执行的SQL语句select sid,sql_textfrom v$session, v$sqlareawhere v$session.sql_address=v$sqlarea.addressand sid=<阻塞者的sid>这样,就可找到"library cache pin"等待的根源，从而解决由此引起的性能问题。