通过设置_smu_debug_mode值来实现指定session级别使用特定的回滚段
_smu_debug_mode为默认值
--测试数据库版本 SQL>select* fromv$version;
BANNER -------------------------------------------------------------------------------- OracleDatabase11g 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 TNSforSolaris: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
--_smu_debug_mode值 SQL>selecta.ksppinm name,b.ksppstvl value 2 fromx$ksppi a,x$ksppcv b 3 wherea.inst_id = USERENV ('Instance') 4 andb.inst_id = USERENV ('Instance') 5 anda.indx = b.indx 6 andupper(a.ksppinm)LIKEupper('%¶m%') 7 orderby name; Enter value forparam: _smu_debug_mode old 6: andupper(a.ksppinm)LIKEupper('%¶m%') new 6: andupper(a.ksppinm)LIKEupper('%_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> settransaction use rollbacksegment "_SYSSMU7_1887299474$";
Transactionset.
SQL>deletefrom t whererownum<10;
9rowsdeleted.
--查询使用回滚段 SQL>selectXIDUSN fromV$TRANSACTION;
XIDUSN ---------- 9 |
这里可以看到在undo自动管理模式下,我们手工指定了回滚段但是被数据库给忽略,还是使用了系统自动分配的回滚段。例如这里我指定的回滚段7,但是使用了系统自动分配的回滚段9
_smu_debug_mode=45
SQL>select* fromv$version;
BANNER -------------------------------------------------------------------------
OracleDatabase11g 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 TNSfor32-bitWindows: 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>selecta.ksppinm name,b.ksppstvl value 2 fromx$ksppi a,x$ksppcv b 3 wherea.inst_id = USERENV ('Instance') 4 andb.inst_id = USERENV ('Instance') 5 anda.indx = b.indx 6 andupper(a.ksppinm)LIKEupper('%¶m%') 7 orderby name; Enter value forparam: _smu_debug_mode old 6: andupper(a.ksppinm)LIKEupper('%¶m%') new 6: andupper(a.ksppinm)LIKEupper('%_smu_debug_mode%')
NAME VALUE -------------------------------- ------------------------ _smu_debug_mode 45
/* 使用altersystem set"_smu_debug_mode" = 45;配置 注意:该参数只能在system级别配置 */
--测试表 SQL>createtable t_xifenfei 2 as 3 select* fromdba_objects;
Tablecreated.
--指定回滚段 SQL>settransaction use rollbacksegment "_SYSSMU15_1680736333$";
Transactionset.
SQL>deletefrom t_xifenfei whererownum<10;
9rowsdeleted.
--查询事务回滚段 SQL>selectXIDUSN fromV$TRANSACTION;
XIDUSN ---------- 15
SQL>commit;
Commitcomplete.
--再次指定回滚段 SQL>settransaction use rollbacksegment "_SYSSMU17_527554872$";
Transactionset.
SQL>deletefrom t_xifenfei whererownum<10;
9rowsdeleted.
--查询事务回滚段 SQL>selectXIDUSN fromV$TRANSACTION;
XIDUSN ---------- 17 |
这里可以看出来通过设置”_smu_debug_mode” = 45可以很好的实现在undo自动管理模式下,指定事务在特定的回滚段,在某些极限情况下,可以通过该操作来减少回滚段争用.
转自:http://www.xifenfei.com/2013/01/设置_smu_debug_mode实现指定session级别使用特定回滚段.html