一次awr报告分析(密码错误引发sql执行时间过长)

预备知识:
1)row cache lock事件  
--内存share pool分为library cache,dictionary cache;而row cache lock对象分布在dictionary cache中,是由于对于字典缓冲的访问造成的;  
--这类属于latch类的资源竞争,相当耗CPU,如果并发量大的话,容易down机
如果发现这个等待十分高,一般来说可能由于2种原因,一是共享池太小了,需要增加共享池,另外一种情况是SQL分析过于频繁,对于共享池的并发访问量过大。对于任何一种情况,绝大多数情况下加大共享池会有助于降低该等待,不过加大共享池的时候也要注意,并不一定所有的情况下增加共享池都会有明显的效果。特别是对于第二种情况,精确的分析十分重要。另外进一步分析,弄清楚哪些ROW CACHE的等待最为严重,有助于解决问题。
row cache lock事件的调优基于每一个队列锁类型的行为,常见的队列锁类型有: 
-------DC_SEQUENCES:在使用序列的时候将发生该行缓冲队列锁。调优方式是检查序列是否指定了缓冲选项并确定这个缓冲值可以承受预期的并发insert操作。
Check for appropriate caching of sequences for the application requirements.
-------DC_USED_EXTENTS和DC_FREE_EXTENTS:该行缓冲队列锁可能在空间管理碰到表空间分裂或者没有足够区大小时发生。调优方法是检查表空间是否分裂了、区大小是否太小或者表空间是人工管理。 
-------DC_TABLESPACES:该行缓冲队列锁会在分配新区是发生。如果区大小设置得过小,程序将经常申请新区,这将导致冲突。调优方法是快速地增加区的数量。
Probably the most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents. 
-------DC_OBJECTS:该行缓冲队列锁会在重编译对象的时候发生。当对象编译时将申请一个排他锁阻塞其他行为。通过检查非法对象和依赖关系来调优。 
-------DC_SEGMENTS:该行缓冲队列锁会在段分配的时候发生,观察持有这个队列锁的会话在做什么。
This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose. 
-------DC_USERS:Deadlock and resulting “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.
-------DB_ROLLBACK_SEGMENTS:This is due to rollback segment allocation. Just like dc_segments,identify what is holding the enqueue and also generate errorstacks. Remember that on a multi-node system (RAC) the holder may be on another node and so multiple systemstates from each node will be required.
-------DC_AWR_CONTROL:This enqueue is related to control of the Automatic Workload Repository. As such any operation manipulating the repository may hold this so look for processes blocking these. 
2)相关视图字段说明:
ROW CACHE LOCK基础说明
    P1 – Cache Id
    P2 – Mode Held
    P3 – Mode Requested
mode 和REQUEST的取值:
    KQRMNULL 0 null mode – not locked
    KQRMS 3 share mode
    KQRMX 5 exclusive mode
    KQRMFAIL 10 fail to acquire instance lock
3)SQL查询
-----查询row cache lock等待
    select *  from v$session_wait where wait_class = 'row cache lock'; 
    查出p1的值
-----查询rowcache 名称
    根据p1的值来查询
    select * from v$rowcache where cache# = &p1;
其他:
select event,p1 from v$session a where a.username is not null and a.status='ACTIVE';
4)dba_hist_active_sess_history视图
dba_hist_active_sess_history视图记录了内存中活动会话的历史信息,而动态性能视图V$ACTIVE_SESSION_HISTORY则记录了当前活动会话信息。 通过dba_hist_active_sess_history视图关联v$sqlarea和DBA_HIST_SNAPSHOT可以跟踪到某个时间段内的sql。当然能跟踪到的sql多少取决于v$sqlarea,毕竟只有还保留在v$sqlarea中的sql才能跟踪到。

AWR报告分析
数据库出现状况是8月30号下午3点到4点,于是拉取了两份AWR报告,一份是8月30号下午3点到4点,另一份是31号下午3点到4点,将两份报告比对着看,不同的地方应该就是有问题的地方。
1)发现top 5的前台事件中,排第一的不是DB CPU。默认正常情况下,DB CPU会排第一。

2)继续看Wait Events Statistics ,发现排第一也不是DB CPU,而是connection management call elapsed time,看名字感觉和数据库连接有关。

3)继续比对,发现dc_users的Pct Miss比较大,超过了20%

4)查看可疑的sql语句:

标红的sql执行速度很慢,有些异常。
SQL> select to_char(sample_time, 'YY-MM-DD HH24:MI:SS') sample_time,
  2         instance_number,
  3         sql_id,
  4         P1,
  5         event,
  6         wait_class
  7    from dba_hist_active_sess_history
  8   where sample_time between
  9         to_date('16-08-29 13:00:00', 'YY-MM-DD HH24:MI:SS') and
10         to_date('16-08-29 13:30:00', 'YY-MM-DD HH24:MI:SS')
11     and sql_id in ('bvtu633rnwrwv',
12                    '4a6uhr508t0p6',
13                    'fpm2zazkfqhy6',
14                    'bhrcaykh5tzsw',
15                    '9fxv1px768bd5')
16   order by 1
17  ;
SAMPLE_TIME       INSTANCE_NUMBER SQL_ID                P1 EVENT                 WAIT_CLASS           
----------------- --------------- ------------- ---------- --------------------- ---------------------
16-08-29 13:00:59               1 4a6uhr508t0p6         10 row cache lock        Concurrency
16-08-29 13:01:09               1 4a6uhr508t0p6         10 row cache lock        Concurrency
16-08-29 13:01:19               1 4a6uhr508t0p6         10 row cache lock        Concurrency
16-08-29 13:01:29               1 4a6uhr508t0p6          7 row cache lock        Concurrency
16-08-29 13:01:39               1 bvtu633rnwrwv         10 row cache lock        Concurrency
16-08-29 13:01:49               1 bvtu633rnwrwv         10 row cache lock        Concurrency
16-08-29 13:01:59               1 bvtu633rnwrwv         10 row cache lock        Concurrency
16-08-29 13:02:10               1 bvtu633rnwrwv          7 row cache lock        Concurrency
16-08-29 13:02:20               1 fpm2zazkfqhy6         10 row cache lock        Concurrency
16-08-29 13:02:30               1 fpm2zazkfqhy6         10 row cache lock        Concurrency
16-08-29 13:02:40               1 fpm2zazkfqhy6         10 row cache lock        Concurrency
16-08-29 13:02:41               2 bvtu633rnwrwv          7 row cache lock        Concurrency
16-08-29 13:02:50               1 fpm2zazkfqhy6          7 row cache lock        Concurrency
16-08-29 13:04:31               1 bhrcaykh5tzsw         10 row cache lock        Concurrency
16-08-29 13:04:41               1 bhrcaykh5tzsw         10 row cache lock        Concurrency
16-08-29 13:04:51               1 bhrcaykh5tzsw          7 row cache lock        Concurrency
16-08-29 13:06:32               1 9fxv1px768bd5         10 row cache lock        Concurrency
16-08-29 13:06:42               1 9fxv1px768bd5         10 row cache lock        Concurrency
18 rows selected
根据上面的sql,发现sql导致的等待事件是row cache lock。再根据P1=7或者10,查出等待事件是发生在具体哪个类别上
SQL> select cache#,parameter from v$rowcache where cache# in ('7','10');
    CACHE# PARAMETER
---------- --------------------------------
        10 dc_users
         7 dc_users
         7 dc_users
         7 dc_users
根据上面的sql,发现参数类别是dc_users,网上说dc_users是和用户用错误密码登陆有关:
In 11g there is an intentional delay between allowing failed logon attempts to retry. For some specific application types this can cause a problem as the row cache entry is locked for the duration of the delay . This can lead to excessive row cache lock waits for DC_USERS for specific users / schemas.

After 3 successive failures a sleep delay is introduced starting at 3 seconds and extending to 10 seconds max. During each delay the user X row cache lock is held in exclusive mode preventing any concurrent logon attempt as user X (and preventing any other operation which would need the row cache lock for user X).
验证一下,看是否在发生row cache lock期间,有用户登陆失败。
SQL> select username,
  2         userhost,
  3         to_char(timestamp, 'YY-MM-DD HH24:MI:SS') timestamp,
  4         action_name
  5    from dba_audit_trail
  6   where action_name = 'LOGON'
  7     and priv_used is null
  8     and timestamp between
  9         to_date('16-08-29 13:00:00', 'YY-MM-DD HH24:MI:SS') and
10         to_date('16-08-29 13:30:00', 'YY-MM-DD HH24:MI:SS'); 
USERNAME                       USERHOST              TIMESTAMP         ACTION_NAME
------------------------------ --------------------- ----------------- ----------------------------
MAPP_PLATFORM                  IDC-APP-02            16-08-29 13:03:51 LOGON
MAPP_PLATFORM                  IDC-APP-01            16-08-29 13:03:41 LOGON
MAPP_PLATFORM                  IDC-APP-02            16-08-29 13:03:31 LOGON
MAPP_PLATFORM                  IDC-APP-01            16-08-29 13:03:21 LOGON
MAPP_PLATFORM                  IDC-APP-02            16-08-29 13:03:11 LOGON
MAPP_PLATFORM                  IDC-APP-01            16-08-29 13:03:01 LOGON
MAPP_PLATFORM                  IDC-APP-02            16-08-29 13:02:51 LOGON
MAPP_PLATFORM                  IDC-APP-01            16-08-29 13:02:41 LOGON
MAPP_PLATFORM                  IDC-APP-02            16-08-29 13:02:31 LOGON
。。。。
发现用户MAPP_PLATFORM从8.14到8.31,一直以错误密码尝试登陆数据库,验证了以错误密码登陆会导致row cache lock同时sql执行慢的现象发生。
 进一步查询登录审计信息:
select * from
(
select os_username,userhost,terminal,username,count(*) failures
    from dba_audit_trail
   where returncode = 1017 and timestamp between to_date ('2016-8-29 11:30:00','yyyy-mm-dd hh24:mi:ss') and to_date ('2016-8-29 13:30:00','yyyy-mm-dd hh24:mi:ss')
   group by os_username,userhost,username,terminal
   order by 5 desc );
returncode = 1017 这里的1017就是oracle内部定义的错误返回代码值。

实际上不管用户提供的密码是否正确,Oracle都会为新的connection分配一个shadow process,该服务进程为了进一步验证用户信息都不得不获取少量资源;如果以此为契机,即便在无法正常登录的情况下依然能在短期内造成实例僵死。


本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
【故障处理】队列等待之 TX - allocate ITL entry 案例 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: TX - allocate ITL entry 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 Tips: ① 本文在 ITpub ( http://blog.itpub.net/267361
Oracle 一次缩小表空间的处理过程 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  收缩表空间的几种办法 ② 表空间大小查询 ③  AIX 下查询磁盘空间大小的 shell 脚本 ④ 删除数据文件的正确方法 ⑤  ORA-03262 处理 ⑥ 缩小数据文件 ⑦  su - grid asmcmd lsdg 的使用 ⑧ 其他常用命令   Ti
等待事件系列(1)--User I/O类型(上) 全文可参考: http://www.cnblogs.com/lhrbest/articles/5835420.html   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  等待事件系列(1)--User I/O类型     Tips: ① 本文在 ITpub ( http://blog.itpu
等待事件系列(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为主库,那么这个主库肯定是可以