联系：手机/微信(+86 17813235971) QQ(107644445)
标题：设置_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('%¶m%')
7 order by name;
Enter value for param: _smu_debug_mode
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
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('%¶m%')
7 order by name;
Enter value for param: _smu_debug_mode
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
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自动管理模式下,指定事务在特定的回滚段,在某些极限情况下,可以通过该操作来减少回滚段争用.