等待事件系列(1)--User I/O类型(上)


等待事件系列(1)--User I/O类型(上)

全文可参考:http://www.cnblogs.com/lhrbest/articles/5835420.html 

 BLOG文档结构图

wps2D38.tmp 

 

 前言部分

 

2.1  导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~

①  等待事件系列(1)--User I/O类型

 

  Tips:

① 本文在ITpubhttp://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaimiaolhr)有同步更新

② 文章中用到的所有代码,相关软件,相关资料请前往小麦苗的云盘下载(http://blog.itpub.net/26736162/viewspace-1624453/

③ 若文章代码格式有错乱,推荐使用搜狗360或QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/,另外itpub格式显示有问题,可以去博客园地址阅读

④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体注;对代码或代码输出部分的注释一般采用蓝色字体表示

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

 

---------------------------------------------------------------------------------------------------------------------

 等待事件的源起

谈到等待事件,必然会提到一种流行的诊断方法论OWI,即Oracle Wait Interface.

等待事件的概念大概是从Oracle 7.0.12中引入的,刚引入的时候100个等待事件Oracle  8.0中这个数目增大到了大约150个,在Oracle 8i中大约有220个事件,在Oracle 9i中大约有400个等待事件,在Oracle 10gR2中,大约有800多个等待事件,在11gR2中约有1000多个等待事件随着等待事件的逐步完善,也能够反映出对于问题的诊断粒度越来越细化。

虽然不同版本和组件安装可能会有不同数目的等待事件,但是这些等待事件都可以通过查V$EVENT_NAME视图获得:

10.2.0.5版本:

select count(1) from v$event_name;

wpsB7F0.tmp 

11g

select count(1) from v$event_name;

wpsB7F1.tmp 

 分类

 

ORACLE的等待事件,主要可以分为两类,即空闲(IDLE)等待事件非空闲(NON-IDLE)等待事件

1). 空闲等待事件指ORACLE正等待某种工作,在诊断和优化数据库的时候,不用过多注意这部分事件。

2). 非空闲等待事件专门针对ORACLE的活动,指数据库任务或应用运行过程中发生的等待,这些等待事件是在调整数据库的时候需要关注与研究的。

 

下面来看一下ORACLE等待事件的主要分类及各类等待事件的个数:

 

SELECT a.INST_ID, A.EVENT, COUNT(1)

  FROM gv$session a

where a.username is not null

   and a.STATUS = 'ACTIVE'

  AND A.WAIT_CLASS<>'Idle'

GROUP BY a.INST_ID,A.EVENT;

 

SELECT wait_class#,

       wait_class_id,

       wait_class,

       COUNT(*) AS "count"

FROM   v$event_name

GROUP  BY wait_class#,

          wait_class_id,

          wait_class

ORDER  BY wait_class#;

 

wpsB801.tmp 

11g

wpsB802.tmp 

 

 

 常见的空闲事件有:

? 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

 

一些常见的非空闲等待事件有:

? db file scattered read

? db file sequential read

? buffer busy waits

? free buffer waits

? enqueue

? latch free

? log file parallel write

? log file sync

 

 

 

This appendix contains the following topics:

■ Classes of Wait Events

■ Descriptions of Common Wait Event Parameters

■ Descriptions of Wait Events

Information about wait events is displayed in three dynamic performance views:

■ V$SESSION_WAIT displays the events for which sessions have just completed waiting or are currently waiting.

■ V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.

■ V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each

session.

Many of these wait events are tied to the internal implementation of Oracle and

therefore are subject to change or deletion without notice. Application developers

should be aware of this and write their codeto tolerate missing or extra wait events.

The following SQL statement displays an alphabetical list of all Oracle wait events and the wait class to which they belong:

SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;

 

4.1  Classes of Wait Events

Every wait event belongs to a class of wait event. The following list describes each of the wait classes.

Administrative

Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)

Application

Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

Cluster

Waits related to Real Application Clusters resources (for example, global cache resources such as 'gc cr block busy')

Commit

This wait class only comprises one wait event - wait for redo log write confirmation after a commit (that is, 'log file sync')

Concurrency

Waits for internal database resources (for example, latches)

Configuration

Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)

Idle

Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net message from client')

Network

Waits related to network messaging (for example, 'SQL*Net moredata to dblink')

Other

Waits which should not typically occur on a system (for example, 'wait for EMON to spawn')

Queue

Contains events that signify delays in obtaining additional data in a pipelined environment. The time spent on these wait events indicates inefficiency or other problems in the pipeline. It affects features such as Oracle Streams, parallel queries, or DBMS_PIPEPL/SQL packages.

Scheduler

Resource Manager related waits (for example, 'resmgr: become active')

System I/O

Waits for background process I/O (for example, DBWR wait for 'db file parallel write')

User I/O

Waits for user I/O (for example 'db file sequential read')

 

4.2  Descriptions of Common Wait Event Parameters

Oracle? Database Reference 11g Release 2 (11.2) E40402-09 

Descriptions of Common Wait Event Parameters

 

This section provides descriptions of some of the more common wait event parameters.

block#

This is the block number of the block for which Oracle needs to wait. The block number is relative to the start of the file. Tofind the object to which this block belongs, issue the following SQL statement:

select segment_name, segment_type, owner, tablespace_name

from dba_extents

where file_id = file#

and block#

between block_id and block_id + blocks - 1;

blocks

The number of blocks that is being either read from or written to the file. The block

size is dependent on the file type:

■ Database files have a block size of  DB_BLOCK_SIZE

■ Logfiles and control files have a block size that is equivalent to the physical block size of the platform 

break?

If the value for this parameter equals 0, a reset was sent to the client. A nonzero value indicates that a break was sent to the client.

class

The class of the block describes how the contents of the block are used. For example, class 1 represents data block, and class 4 represents segment header.

dba

The initials "dba" represents the data block address, which consists of a file number and a block number.

driver id

The address of the disconnect function of the driver that is currently being used.

file#

The following query returns the name of the database file:

select *

from v$datafile

where file# = file#;

id1

The first identifier (id1) of the enqueue or global lock takes its value from P2 or P2RAW. The meaning of the identifier depends on the name (P1).

id2

The second identifier (id2) of the enqueue or global lock takes its value from P3 or P3RAW. The meaning of the identifier depends on the name (P1).

le

The relative index number into V$GC_ELEMENT.

mode

The mode is usually stored in the low order bytes of P1 or P1RAW and indicates the mode of the enqueue or global lock request.This parameter has one of the following values:

Table C-1 Lock Mode Values

Mode Value

Description

1

Null mode

2

Sub-Share

3

Sub-Exclusive

4

Share

5

Share/Sub-Exclusive

6

Exclusive

 

Use the following SQL statement to retrieve the name of the lock and the mode of the lock request:

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) "Lock",

bitand(p1, 65535) "Mode"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

name and type

The name or "type" of the enqueue or globallock can be determined by looking at the two high order bytes of P1 or P1RAW. The name is always two characters. Use the following SQL statement to retrieve the lock name.

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1,16711680)/65535) "Lock"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

namespace

The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view.

requests

The number of I/Os that are "requested." This differs from the number of blocks in that one request could potentially contain multiple blocks.

session#

The number of the inactive session. Use the following SQL statement to find more information about the session:

select *

from v$session

where sid = session#;

waited

This is the total amount of time the sessionhas waited for this session to terminate.

 重要等待事件

一些常见的重要的等待事件:

1)数据文件I/O相关的等待事件:

l db file sequential read

l db file scattered read

l db file parallel read

l direct path read

l direct path write

2)控制文件I/O相关的等待事件:

l control file parallel write

l control file sequential read

l control file single write

3)重做日志文件I/O相关的等待事件:

l log file parallel write

l log file sync

l log file sequential read

l log file single write

l switch logfile command

l log file switch completion

l log file switch (clearing log file)

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
等待事件系列(1) --User I/O 类型(下) 全文可参考: http://www.cnblogs.com/lhrbest/articles/5835420.html   1    BLOG 文档结构图   6.3      d b file parallel read SELECT   * FROM    v$event_name WHERE    NAME   IN   ( 'db file parallel read' );   在 V$SESSION_WAIT这个视图里面,这个等待事件有三个

mysql存储过程小试牛刀 - 2016-09-03 14:09:13

(1). 格式 MySQL 存储过程创建的格式: CREATE PROCEDURE 过程名 ([ 过程参数 [,...]]) [ 特性 ...] 过程体 这里先举个例子: mysql DELIMITER //   mysql CREATE PROCEDURE proc1(OUT s int)     - BEGIN      - SELECT COUNT(*) INTO s FROM user;       - END      - //   mysql DELIMITER ;    注: ( 1 )这里
【故障处理】队列等待之 enq: TX - row lock contention 1    BLOG 文档结构图   2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: TX - row lock contention 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 ④  ADDM 的使用 ⑤ 如何获取历史执行计划 ⑥ 查询绑定变量的具体值 ⑦ 很多有用的查询性
       ORA-01408:such column list already indexed问题的原因是当新建一个索引时,索引对应的字段和字段顺序和已经存在的索引相同。        最近一个需求,是将主键索引转成HASH分区的主键索引,需要新建一个索引,然后将现有的主键索引去掉。如果直接新建一个字段相同且字段顺序相同的索引,就会报ORA-01408。当然,可以将现有的主键索引先删掉,但这样在新索引建立之前,无法保证数据的唯一性,并且按主键的更新删除操作会因为无索引而变的很慢。        我们可
继续上次分析的一个问题,一个简单的SQL语句执行计划有些奇怪,明明可以走唯一性索引但是却走了另外一个索引。 当然了,最后逐步定位,发现是在直方图的地方有一些差别。取消直方图之后,执行计划立刻恢复了正常。 当然问题来了,这个是为什么呢,收集统计信息中的auto选项是什么含义呢。为什么两个数据类型一样的(varchar2(64))的列,境遇却大大不同。 我们来看看一些统计信息的数据。 为了跟进一步验证数据的分布律和选取代价,我们查询它的直方图信息。 SQL   select to_char(endpoint_v
首先说明_fairness_threshold参数仅在RAC环境下才有意义,让我对这个参数引起关注是在某一次查询v$cr_block_server时,发现该视图有一个名为FAIRNESS_DOWN_CONVERTS的字段,官方文档里将其解释为:Number of times an instance receiving a request has down-converted an X lock on a block because it was not modifying the block 该参数相关性
    今天看到有一个网友提了一个问题,描述很简短     测试DG时,主库不能宕机,如何测试failover?     其实这个需求从业务层面来说是合理的,一个数据量很大的核心数据库,如果需要做灾难演练,就希望在备库上做一下演练工作,而这个演练其实又不想影响到目前的主库,而且又希望能够尽可能模拟真实的情况,我想这样对于运维部门来说是最具有考核力度,而对于开发业务部门来说是最受欢迎的,因为他们什么都不需要改动。 而从技术角度来看,似乎有一些地方需要考量,如果备库Failover为主库,那么这个主库肯定是可以

简单分析percona-zabbix-templates - 2016-09-01 04:09:15

    当Zabbix和Percona两者相遇,会擦出不少的开源火花来,众人拾柴火焰高,最终受益的还是大部分运维人员。     我很早就用过Percona提供的MySQL监控模板,但是却没有刨根问底,只是简单使用而已,自从定制了Orabbix之后,我还是信心满满,MySQL的数据字典相对要少很多,监控起来可能想必Oracle要少很多,不过关于Percona的这个插件,我还是带着好奇之心,内部是否有很多独门秘籍,我想好好学学那些监控项对应的SQL,好好弥补我对于MySQL监控的一些空缺,所以简单分析这个模板就
问题描述 : 近期的 rman 备份中,归档日志的备份没有被删除, rman 的脚本和策略都没变过,归档的备份一直保留,每过一段时间就要物理删除备份,很是奇怪。 rman的 configure 如下 RMAN show all; RMAN configuration parameters for database withdb_unique_name HUBSRAC are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE BACKUP OPTI
一 前言  纳西姆.尼古拉斯.塔勒布的经典著作《黑天鹅》中对“黑天鹅现象”的定义是 - 不可预测,人们事前往往低估其发生的可能性 - 造成极大影响 - 事后回头再看,又觉得此事发生的有理 二 分析   稳定性 是一项衡量基础系统是否永续服务的绝对指标,作为资深DBA从业人员,相信大多数公司运维团队都会制定稳定性的SLA指标达到N个9,为用户提供Full-Time 服务。然而前一段时间各种"黑天鹅”式的因素导致一系列的系统故障,严重影响了C端B端的用户的使用体验。 故 障 是数据库系统或者说业务系统的“脆弱