加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

oracle_lath free - contention on dc_rollback_segments

(2014-11-01 23:19:17)
标签：

latchfree

undo

ora-1628

it

 分类： oracleops


 ENV :AIX  9i  RAC 

 
 系统报大量latch free , 导致用户操作收到影响。

  因为是9i的数据库，latch 等待事件，还没有很细的分开。

  查询latch free对应的latch.

SQL>SELECT name
  FROM v$latchname
 WHERE latch# IN (SELECT p2 FROM v$session_wait WHERE event = 'latch free');
row cache objects;
 
SQL>SELECT sql_text
  FROM v$sqlarea
 WHERE address IN
       (SELECT sql_address
          FROM v$session
         WHERE sid IN
               (SELECT sid FROM v$session_wait WHERE event = 'latch free'));
大量DML操作可以看到。
               

查询row cache object对应的对象：

SQL> SELECT name, 'Child '||child#, gets, misses, sleeps
        FROM v$latch_children 
       WHERE addr='07000001EA5171B8'
      UNION
      SELECT name, null, gets, misses, sleeps
        FROM v$latch
      WHERE addr='07000001EA5171B8';
NAME                                          'CHILD'||CHILD#                                      GETS     MISSES     SLEEPS
---------------------------------------------------------------- -----------------------------------
row cache objects                             Child 8      


row cache 对应latch号
set pages 1000
 column cache# format 99999
 column name format a33
 column latch# format 999999
 select distinct s.kqrstcln latch#,r.cache#,r.parameter name,r.type,r.subordinate#
   from v$rowcache r,x$kqrst s (cache statistics)
    where r.cache#=s.kqrstcid
    order by 1,4,5;

 LATCH# CACHE# NAME                              TYPE        SUBORDINATE#
------- ------ --------------------------------- ----------- ------------
      1      1 dc_free_extents                   PARENT
      2      4 dc_used_extents                   PARENT
      3      2 dc_segments                       PARENT
      4      0 dc_tablespaces                    PARENT
      5      5 dc_tablespace_quotas              PARENT
      6      6 dc_files                          PARENT
      7      7 dc_users                          PARENT
      7      7 dc_users                          SUBORDINATE            0
      7      7 dc_user_grants                    SUBORDINATE            1
      7      7 dc_app_role                       SUBORDINATE            2
      8      3 dc_rollback_segments              PARENT
      9      8 dc_objects                        PARENT
     10     17 dc_global_oids                    PARENT
     11     12 dc_constraints                    PARENT
     12     11 dc_object_ids                     PARENT
     13     13 dc_sequences                      PARENT
     14     10 dc_usernames                      PARENT
     15     15 dc_database_links                 PARENT
     16     16 dc_histogram_defs                 PARENT
     16     16 dc_histogram_data                 SUBORDINATE            0
     16     16 dc_histogram_data_values          SUBORDINATE            1
     17     21 dc_table_scns                     PARENT
     17     21 dc_partition_scns                 SUBORDINATE            0
     18     18 dc_outlines                       PARENT
     19     14 dc_profiles                       PARENT
     20     19 dc_encrypted_objects              PARENT
     21     20 dc_encryption_profiles            PARENT
     22      9 dc_qmc_cache_entries              PARENT
     23     22 dc_qmc_ldap_cache_entries         PARENT

 可以看到对应的row cache为dc_rollback_segments  对回滚段的争用，当会话做DML操作时
 会申请回滚段， 修改row cache里的回滚数据字典的latch来记录当前申请情况。

  想到这里会想如何缓解或消除这种争用：
 1. 减少并发DML操作。
 2. 增加需要的latch数量。增加可用资源。




  I. 基本比较困难
  II.  
  增加ONLINE undo块的数量，undo块的创建数量是UNDO自动管理来维护。
As you know, with automatic undo, you have no control over the extent sizes,
 nor do you have the ability to shrink them. This all happens automatically.
  而ONLINE的 数量也是根据当前系统需求量由系统自动维护。
  我们做的就是要增大ONLINE UNDO的数量。

  方式1：    手工增加ONLINE回滚段，禁止SMON将回滚段OFFLINE。
        alter system set "_smu_debug_mode" = 33554432 scope = memory sid = '';
       alter system set events '10511 trace name context forever ,level 1';
        alter system set "_smu_debug_mode" = 4 scope = memory sid = '';
        alter rollback segment "xx" online;
       alter system set "_smu_debug_mode" = ;
   方式2：  增加KEEP ONLINE回滚段数量。
       alter system set "_rollback_segment_count" = 1000 scope=spfile; ( specified number of UNDO's online)
       



10 and above
Find which cache is being waited for:

 

SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event='row cache lock';

P1TEXT P1 P2TEXT P2 P3TEXT P3

cache id 8 mode 0 request 3

SQL> select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8;

PARAMETER COUNT GETS GETMISSES MODIFICATIONS

DC_SEQUENCES    869    76843 508432   4500

 

For DC_SEQUENCES, consider caching sequences using the cache option.

Other dictionary locks:

DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities

DC_SEGMENTS
This is most likely due to segment allocation. 

DC_USERS

This may occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

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.














PS:  如果处理UNDO碎片以及收缩空间大小
   Before/after running large transactions, Shrink undo segments when reaching certain threshold (Ex: 15000 extents) do not wait to reach its maximum (32765) to be able to bring it below certain threshold so that this undo segment can qualify for binding again.

a) select a.inst_id, a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.inst_id=b.inst_id and b.inst_id=c.inst_id
and a.ksppinm in ('_smu_debug_mode')
order by 2;

select segment_name,
round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
NO_OF_EXTENTS
from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='ACTIVE' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='UNEXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='EXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
) group by segment_name, NO_OF_EXTENTS order by 5 desc;

select sum(blocks),count(*) extents,segment_name from DBA_EXTENTS
where tablespace_name = 'UNDOTBS1' group by segment_name order by 2 desc;

b) alter system set "_smu_debug_mode" = 4 scope=memory;

c) alter rollback segment "_SYSSMU$" shrink;

d) alter system set "_smu_debug_mode" = scope=memory;

Then you can check the result of this measure by running the query in step a again before and after the above three steps.

4) Drop and recreate undo tablespace (due to it's fragmentation) 

The steps for recreating an undo tablespace are in Note 268870.1 Ext/Pub How to Shrink the datafile of Undo Tablespace.

5) Minimize the generated undo as much as possible :

Example:

- split large transactions into smaller one

- commit more often

- use direct path load rather than conventional path load to significantly reduce the amount of undo and thus also avoid a too high fragmentation of undo tablespace.

喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有