【故障处理】序列cache值过小导致CPU利用率过高

【故障处理】序列cache值过小导致CPU利用率过高

 BLOG文档结构图

 

wps3FDC.tmp 

 

 前言部分

2.1  导读和注意事项

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

① enq: SQ - contention等待事件的解决

② 一般等待事件的解决办法

③ DFS lock handle等待事件

④ 与序列有关的等待事件

  Tips:

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

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

③ 若文章代码格式有错乱,推荐使用搜狗360QQ浏览器,也可以下载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皆可,您的批评指正是我写作的最大动力。

 

 

 故障分析及解决过程

 

3.1  故障环境介绍

 

项目

source db

db 类型

RAC

db version

10.2.0.5.0

db 存储

ASM

OS版本及kernel版本

AIX 64位 6.1.0.0

 

3.2  故障发生现象及报错信息

早上同事过来跟我说昨天有一套数据库做测试的时候,CPU利用率很高,他已经抓取了CPUAWR,让我帮忙分析分析,首先发生问题的时间段是19点到23点,nmon数据截图如下:

wps3FED.tmp 

可以看到CPU的利用率是非常高的,下边我们来看看AWR中的数据:

wps3FEE.tmp 

wps3FEF.tmp 

其它的项目就不列出了,从等待事件中可以很明显的看出enq: SQ - contentionDFS lock handle2个等待事件异常。Top 5 Timed Events这个部分也是AWR报告中非常重要的部分,从这里可以看出等待时间在前五位的是什么事件,基本上就可以判断出性能瓶颈在什么地方。通常,在没有问题的数据库中,CPU time总是列在第一个。在这里,enq: SQ - contention等待了172254次,等待时间为69652秒,平均等待时间为69652/172254=404毫秒,等待类别为Configuration即配置上的等待问题。

3.3  故障分析

根据AWR报告的内容,我们知道只要解决了enq: SQ - contention和DFS lock handle2个等待事件即可解决问题。那么我们首先来了解一些关于这2个等待事件的知识。

===============================================================================

enq: SQ - contention/row cache lock/DFS lock handle这三个等待事件都与Oracle Sequence 有关。

 

SELECT *

  FROM V$EVENT_NAME

 WHERE NAME IN

       ('row cache lock', 'enq: SQ - contention', 'DFS lock handle');

wps4000.tmp 

使用如下的SQL我们可以查询到锁的名称和请求的MODE,表的mode值参考表格:

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';

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

 

SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('SV','SQ');

wps4001.tmp 

Oracle 为了管理Sequence 使用了以下三种锁。

① row cache lock:在调用SEQUNECE.NEXTVAL过程中,将数据字典信息进行物理修改时获取。赋予了NOCACHE属性的SEQUENCE上发生,等待事件为row cache lock

② SQ:在内存上缓存(CACHE)的范围内,调用SEQUENCE.NEXTVAL 期间拥有此锁。赋予了CACHE 属性的SEQUENCE 上发生。赋予了CACHE 属性的SEQUENCE 调用NEXTVAL 期间,应该以SSX 模式获得SQ 锁。许多会话同时为了获取SQ 锁而发生争用过程中,若发生争用,则等待enq: SQ - contention事件。enq: SQ - contention 事件的P2 值是Sequence OBJECT ID。因此,若利用P2 值与DBA_OBJECTS 的结合,就可以知道对哪个SEQUENCE 发生了等待现象。

③ SV:RAC上节点之间顺序得到保障的情况下,调用SEQUENCE.NEXTVAL期间拥有。赋予CACHE + ORDER属性的SEQUENCE 上发生,等待事件为DFS lock handle,解决办法为:尽量设置为NOORDER并增大其CACHE值。

根据创建Sequence时赋予的属性,整理等待事件的结果如下:

v NOCACHE: row cache lock

v CACHE + NOORDER: enq: SQ - contention

v CACHE + ORDER(RAC): DFS lock handle

 

创建SEQUENCE赋予的CACHE 值较小时,有enq: SQ - contention等待增加的趋势。CACHE值较小时,内存上事先CACHE的值很快被耗尽,这时需要将数据字典信息物理修改后,再次执行CACHE的工作。在此期间,因为一直拥有SQ 锁,相应的enq: SQ - contention 事件的等待时间也会延长。很不幸的是,在创建SEQUENCE 时,将CACHE 值的缺省值设定为较小的20。因此创建使用量多的SEQUENCE 时,CACHE 值应该取1000 以上的较大值。

另外,偶尔一次性同时创建许多会话时,有时会发生enq: SQ - contention 等待事件。其理由是V$SESSION.AUDSIDauditing session id)列值是利用Sequence创建的。Oracle 在创建新的会话后,利用名为SYS.AUDSES$Sequence nextval,创建AUDSID 值。SYS.AUDSES$ Sequence CACHE 大小的缺省值设定为20。许多会话同时连接时,可以将SYS.AUDSES$ Sequence CACHE大小扩大至1000,以此可以解决enq: SQ - contention 等待问题。 10g下默认20,11g下默认为10000,通过如下的SQL可以查询:

SELECT * FROM dba_sequences d WHERE d.sequence_name ='AUDSES$';

wps4002.tmp 

RAC 上创建SEQUENCE 时,在赋予了CACHE属性的状态下,若没有赋予ORDER 属性,则各节点将会把不同范围的SEQUENCE CACHE 到内存上。比如,拥有两个节点的RAC 环境下,创建CACHE 值为100 SEQUENCE 时,1号节点使用1100号节点使用101200。若两个节点之间都通过递增方式使用SEQUENCE,必须赋予如下ORDER 属性。

      SQL> CREATE SEQUENCE ORDERED_SEQUENCE CACHE 100 ORDER;

如果是已赋予了CACHE+ORDER 属性的SEQUENCEOracle 使用SV 锁进行行同步。即,对赋予了ORDER 属性的Sequence 调用nextval 时,应该以SSX模式拥有SV 锁。在获取SV 锁过程中,如果发生争用时,不是等待row cache lock 事件或enq: SQ - contention 事件,而是等待名为DFS lock handle 事件。正因如此,V$EVENT_NAME 视图上不存在类似"enq:SV-contention"的事件。DFS lock handle 事件是在OPS RAC 环境下,除了高速缓冲区同步之外,还有行高速缓冲区或库高速缓冲区的为了同步获取锁的过程中等待的事件。若要保障多个节点之间Sequence顺序,应该在全局范围内获得锁,在此过程中会发生DFS lock handle 等待。在获取SV 锁的过程中发生的DFS lock handle等待事件的P1 P2 值与enq: SQ - contention 等待事件相同( P1=mode+namespaceP2=object#)。因此从P1 值能确认是否是SV 锁,通过P2值可以确认对哪些Sequence 发生过等待。SV 锁争用问题发生时的解决方法与SQ 锁的情况相同,就是将CACHE 值进行适当调整,这也是唯一的方法。

在RAC 等多节点环境下,Sequence CACHE 值给性能带来的影响比单节点环境更严重。因此,尽量赋予CACHE+NOORDER 属性,并要给予足够大的CACHE值。如果需要保障顺序,必须赋予CACHE+ORDER 属性。但这时为了保障顺序,实例之间不断发生数据的交换。因此,与赋予了NOORODER属性的时候相比性能稍差。

有一点必须要注意,没有赋予CACHE属性时,不管ORDER 属性使用与否或RAC 环境与否,一直等待row cache lock 事件。row cache lock是可以在全局范围内使用的锁,单实例环境或多实例环境同样可以发生。

没有赋予CACHE属性时,不管ORDER属性是否或RAC环境是否,一直等待ROW CACHE事件,ROW CACHE LOCK是否可以在全局范围内使用的锁,单实例环境或多实例环境同时可以发生。

Oracle Sequence默认是NOORDER,如果设置为ORDER;在单实例环境没有影响,在RAC环境此时,多实例实际缓存相同的序列,此时在多个实例并发取该序列的时候,会有短暂的资源竞争来在多实例之间进行同步。因次性能相比noorder要差,所以RAC环境非必须的情况下不要使用ORDER,尤其要避免NOCACHE ORDER组合。

但是如果使用了Cache,如果此时DB 崩溃了,那么sequence会从cache之后重新开始,在cache中没有使用的sequence会被跳过。即sequence不连续。所以只有在多节点高峰并发量很大的情况且对连续性要求不高的情况下,才使用:noorder + cache

DFS lock handle

The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.

Wait Time: The session waits in a loop until it has obtained the lock handle from the DLM. Inside the loop there is a wait of 0.5 seconds.

Parameter

Description

name

See "name and type"

mode

See "mode"

id1

See "id1"

id2

See "id2"

The session needs to get the lock handle.

该等待事件的发生,若不是SV锁的话,多半为bug引起。

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).

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';

 

===============================================================================

有了以上的知识,我们知道,目前只需要找到产生等待的序列名称,然后设置其CACHE为比较大的一个值即可解决问题。

3.4  故障解决过程

3.4.1  enq: SQ - contention等待事件

我们查询出现问题时间段的ASH视图DBA_HIST_ACTIVE_SESS_HISTORY来找到我们需要的序列名称。

可以有多种查询方法:

SELECT D.SQL_ID, COUNT(1)

  FROM DBA_HIST_ACTIVE_SESS_HISTORY D

 WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000', 'YYYYMMDDHH24MISS') AND

       TO_DATE('20160823230000', 'YYYYMMDDHH24MISS')

   AND D.EVENT = 'enq: SQ - contention'

 GROUP BY D.SQL_ID;

wps4012.tmp 

可以看到SQL_ID为3jhvjgj7kbpmt的SQL最多,我们查看具体SQL内容:

SELECT * FROM V$SQL A WHERE A.SQL_ID IN ('3jhvjgj7kbpmt') ;

wps4013.tmp 

由此可以知道,产生等待的序列名称为ONLNID,另外,我们也可以从DBA_HIST_ACTIVE_SESS_HISTORY视图的P2值获取到序列的名称,如下:

 SELECT D.EVENT,

        D.P1TEXT,

        D.P1,

        D.P2TEXT,

        D.P2,

        CHR(BITAND(P1, -16777216) / 16777215) ||

        CHR(BITAND(P1, 16711680) / 65535) "Lock",

        BITAND(P1, 65535) "Mode",

        D.BLOCKING_SESSION,

        D.BLOCKING_SESSION_STATUS,

        D.BLOCKING_SESSION_SERIAL#,

        D.SQL_ID,

        TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDDHH24MISS') SAMPLE_TIME,

        D.*

   FROM DBA_HIST_ACTIVE_SESS_HISTORY D

  WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000', 'YYYYMMDDHH24MISS') AND

        TO_DATE('20160823230000', 'YYYYMMDDHH24MISS')

    AND D.EVENT = 'enq: SQ - contention';

wps4014.tmp 

由以上的查询结果可知,序列的object_id47989,由此也可以知道序列名称如下,另外,lock为SQ代表的是序列的cache锁(Sequence Cache),mode6代表Exclusive排他锁。

SELECT * FROM DBA_OBJECTS D WHERE D.object_id='47989';

wps4015.tmp 

知道了序列名称后,我们就可以查询序列的属性了:

SELECT * FROM DBA_SEQUENCES D WHERE D.sequence_name='ONLNID' ;

wps4026.tmp 

可以看到,该序列是NOORDER属性,CACHE值为默认的20,对于并发值很高的系统而言,该默认值太低,所以需要调整到1000,我们执行SQL:ALTER SEQUENCE NFXS.ONLNID CACHE 1000; 调整其cache值即可解决该问题。

 

3.4.2  DFS lock handle等待事件

我们查询出现问题时间段的ASH视图DBA_HIST_ACTIVE_SESS_HISTORY来找到我们需要的序列名称。

可以有多种查询方法:

SELECT D.SQL_ID, COUNT(1)

  FROM DBA_HIST_ACTIVE_SESS_HISTORY D

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
这两天做性能测试碰见一个问题,比较有意思。 一条SQL,使用了绑定变量,查看V$SQLAREA发现version_count是2,  查看V$SQL,发现有两条记录,分别对应了0和1两个child cursor:  再查看这两个child cursor对应的执行计划:  child cursor:0 -----------------------------------------------------------------------------------------------------| I

OCP如何查看历史成绩(2) - 2016-08-25 14:08:08

OCP如何查看历史成绩( 2 ) 之前写过一篇文章 OCP如何查看历史成绩: http://blog.itpub.net/26736162/viewspace-1624996/  , 但是现在界面好像有所变动,而且有的连接也找不见了,今天有朋友问如何查询历史的成绩,晚上小麦苗就抽了点时间来找找,顺便记录下来。最近心情不好,不想写技术文章了。       首先登陆网址: https://education.oracle.com/ https://education.oracle.com/pls/eval-e
下午的时候收到这么一条报警。 ZABBIX- 监控系统 : ------------------------------------ 报警内容 : Too many parallel sessions on xxxxx_ xx机房 _xxxxx ------------------------------------ 报警级别 : PROBLEM ------------------------------------ 监控项目 : parallel_session_cnt : 66 -----------

【MySQL】Tokudb安装测试初探 - 2016-08-24 14:08:02

一 前言    TokuDB 是一个高性能、支持MVCC的MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是数据压缩功能出色,对高写压力的支持,由美国TokuTek公司(http://www.tokutek.com/) 研发,该公司于2015年4月份被Percona收购,理所当然地提供了TokuDB版本的Percona Server。本文使用Peronca server 5.6.30 版本进行测试安装。 二 安装前的准备     请参考官方文档 Percona Server YUM源

通过shell脚本添加备库日志 - 2016-08-21 17:08:14

今天下午的时候,准备顺手写一个简单的脚本,但是发现很多事情较真起来真是寸步难行。在写脚本的过程中碰到了太多的问题,很多时候感觉像要实现的功能更通用,就得做更多的检查,更多的校验也就意味着有更多的预先条件,这些条件里面有些是规范和建议,有些是按照已有的配置情况,尽管如此,自己感觉还是缺少了太多的检查。   先来说说今天尝试的简单脚本,就是给主库添加standby logfile,这个需求听起来非常简单,都甚至在我的半自动化脚本中隐去了,但是把这个需求要落到纸面上来,简直了。 首先这个需求会涉及到下面的几个数据
今天在测试时遇到一个ORA-38706ORA-38707报错,乍一看到报错内容竟然没有回过神儿来。 ORA-38706ORA-38707报错 点击( 此处 )折叠或打开 SYS @ HOEGH select banner from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition R

Oracle之虚拟索引 - 2016-08-21 14:08:07

Oracle 之虚拟索引 1    BLOG 文档结构图     2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  Oracle 虚拟索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新 ②

MySQL 事务提交过程 - 2016-08-18 17:08:13

开发老大要求通过binlog查询一条被修改的数据,数据被查出后问我,有没有可能binlog中不会记录,回答不会,因为数据被修改,若失败直接回滚,不会在binlog中记录,此刻一个朋友用了洪荒之力告诉我,失败的话也会记录,坐地无语,因为他sqlserver dba,用sqlserver的思维考虑mysql,哈哈哈哈哈,用实验让他闭嘴! 简单测试步骤如下: root(yoon) flush logs; Query OK, 0 rows affected (0.01 sec) root((none)) show

Data Guard跳归档恢复的案例 - 2016-08-18 14:08:08

自前些天写了一个脚本之后,今天特意测试了一下,没想到一下子发现了一个大问题。有一套一主两备的10gR2环境,一个异机备库一直在READ ONLY状态,也就意味着数据库在打开之后一直忘了恢复应用归档,然后在某一天发现时,已经延迟了好几个月。无论怎样,还得庆幸发现了这个问题。 目前来看一种行之有效的方法就是重搭备库,但是这种修复方式需要大量的磁盘空间,而且需要恢复的时间较长,怎么改进呢,可以考虑通过基于SCN的增量备份来跳归档恢复。目前的环境是一主两备,再怎么改进呢,我们可以基于备库1来完成基于SCN的增量备份
背景 本文对 5.6 主备场景下,在备库做物理备份遇到死锁的case进行分析,希望对大家有所帮助。 这里用的的物理备份工具是 Percona-XtraBackup(PXB),有的同学可能不清楚其备份流程,所以这里先简单说下,PXB的备份步骤是这样的: 拷贝 InnoDB redo log,这是一个单独的线程在拷,直到备份结束; 拷贝所有InnoDB ibd文件; 加全局读锁,执行 FLUSH TABLES WITH READ LOCK(FTWRL); 拷贝 frm、MYD、MYI 等文件; 获取位点信息,