设置_smu_debug_mode实现指定session级别使用特定回滚段

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:设置_smu_debug_mode实现指定session级别使用特定回滚段

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

通过设置_smu_debug_mode值来实现指定session级别使用特定的回滚段
_smu_debug_mode为默认值

--测试数据库版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

--_smu_debug_mode值
SQL> select a.ksppinm name,b.ksppstvl value
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name;
Enter value for param: _smu_debug_mode
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_smu_debug_mode%')

NAME                             VALUE                   
-------------------------------- ------------------------ 
_smu_debug_mode                  0                        

--undo管理模式
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

--指定回滚段(查询dba_rollback_segs得到回滚段名称)
SQL>  set transaction use rollback segment "_SYSSMU7_1887299474$";

Transaction set.

SQL> delete from t where rownum<10;

9 rows deleted.

--查询使用回滚段
SQL> select XIDUSN from V$TRANSACTION;

    XIDUSN
----------
         9

这里可以看到在undo自动管理模式下,我们手工指定了回滚段但是被数据库给忽略,还是使用了系统自动分配的回滚段。例如这里我指定的回滚段7,但是使用了系统自动分配的回滚段9

_smu_debug_mode=45

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter undo;

NAME                                 TYPE                VALUE
------------------------------------ -------    -----------------
undo_management                      string       AUTO
undo_retention                       integer      900
undo_tablespace                      string       undo_new

SQL> select a.ksppinm name,b.ksppstvl value
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name;
Enter value for param: _smu_debug_mode
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_smu_debug_mode%')

NAME                             VALUE                    
-------------------------------- ------------------------ 
_smu_debug_mode                  45   

/*
使用alter system set "_smu_debug_mode" = 45;配置
注意:该参数只能在system级别配置
*/                   

--测试表
SQL> create table t_xifenfei
  2  as
  3  select * from dba_objects;

Table created.

--指定回滚段
SQL> set transaction use rollback segment "_SYSSMU15_1680736333$";

Transaction set.

SQL> delete from t_xifenfei where rownum<10;

9 rows deleted.

--查询事务回滚段
SQL> select XIDUSN from V$TRANSACTION;

    XIDUSN
----------
        15

SQL> commit;

Commit complete.

--再次指定回滚段
SQL> set transaction use rollback segment "_SYSSMU17_527554872$";

Transaction set.

SQL> delete from t_xifenfei where rownum<10;

9 rows deleted.

--查询事务回滚段
SQL> select XIDUSN from V$TRANSACTION;

    XIDUSN
----------
        17

这里可以看出来通过设置”_smu_debug_mode” = 45可以很好的实现在undo自动管理模式下,指定事务在特定的回滚段,在某些极限情况下,可以通过该操作来减少回滚段争用.

此条目发表在 Oracle 分类目录,贴了 标签。将固定链接加入收藏夹。

设置_smu_debug_mode实现指定session级别使用特定回滚段》有 1 条评论

  1. 惜分飞 说:

    undo manul管理也可以实现session级别指定回滚段

    SQL> show parameter undo;
    
    NAME                                 TYPE                              VALUE
    ------------------------------------ --------------------------------- ---------
    undo_management                      string                            MANUAL
    undo_retention                       integer                           900
    undo_tablespace                      string                            undo_new
    
    SQL> create rollback segment undo_xff2 tablespace undotbs1;
    
    Rollback segment created.
    
    SQL> create rollback segment undo_xff3 tablespace undotbs1;
    
    Rollback segment created.
    
    SQL> alter rollback segment undo_xff1 online;
    
    Rollback segment altered.
    
    SQL> alter rollback segment undo_xff2 online;
    
    Rollback segment altered.
    
    SQL> alter rollback segment undo_xff3 online;
    
    Rollback segment altered.
    
    SQL> conn chf/xifenfei
    Connected.
    SQL> set transaction use rollback segment undo_xff3;
    
    Transaction set.
    
    SQL> delete from t_xifenfei where rownum<10;
    
    9 rows deleted.
    
    SQL> select XIDUSN from V$TRANSACTION;
    
        XIDUSN
    ----------
            23
    
    SQL> select name from sys.undo$ where us#=23;
    
    NAME
    --------------------------------
    UNDO_XFF3
    
    SQL> commit;
    
    Commit complete.
    
    SQL> set transaction use rollback segment undo_xff1;
    
    Transaction set.
    
    SQL> delete from t_xifenfei where rownum<10;
    
    9 rows deleted.
    
    SQL> select XIDUSN from V$TRANSACTION;
    
        XIDUSN
    ----------
            21
    
    SQL> select name from sys.undo$ where us#=21;
    
    NAME
    --------------------------------
    UNDO_XFF1