oracle 11g不同会话产生的事务会使用相同的undo segment吗--undo系列之一
背景
不同会话产生的事务会不会同时使用相同的undo segment呢，本文我们测试这个问题，加深对于undo概念的理解。结论
1,dba_rollback_segs初始时即数据库启动有创建产生11个回滚段，后期随着数据库事务的增加，会动态调整增加但是如果已经创建的回滚段，不会因为事务已提交而回收
2,不同的事务会分配不同的undo segment,而不会出现多个事务分配相同的undo segment
3,隐含参数_rollback_segment_count不是控制或限制数据库创建多少个回滚段
4,对于undo机制的理解仍停留在表层，还要很多工作和知识要补充
测试
数据库版本
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
1,数据库目前的回滚段信息，共计11个
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
11 rows selected.
会话1
SQL> update scott.t_undo set a=3 where rownum=1;
1 row updated.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU31_4292867569$
会话2
SQL> create table t_oun(a int);
Table created.
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU24_2463137615$
会话3
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU27_3263502189$
会话4
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU28_1986380219$
会话5
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU29_193706344$
会话6
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU26_2187890310$
会话7
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU23_2210640156$
会话8
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU22_185849310$
会话9
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU30_4054687248$
会话10
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU25_848072715$
会话11
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU1_2507315526$
可见undo segment的数量是随着数据库的事务情况动态变化的，不会一成不变
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
13 rows selected.
会话12
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU2_995037188$
会话13
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
14 rows selected.
回滚所有上述的13个会话的事务，发现已产生的14个回滚段没有回收
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
14 rows selected.
获取与udno segment相关的隐含参数
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
transactions_per_rollback_segment 5 number of active transactions per rollback segment
_rollback_segment_count 0 number of undo segments
可见每个undo segment目前配置最大的活动事务个数为5个，而可以创建的undo segment个数为0，即表示不受限制
当前占用回滚段个数为13，刚好和dba_rollback_segs一致（因为还有有一个system rollback segment）
SQL> select status,count(*) from dba_undo_extents group by status;
STATUS COUNT(*)
------------------ ----------
UNEXPIRED 13
EXPIRED 34
SQL> select count(*) from dba_rollback_segs;
COUNT(*)
----------
14
显式配置_rollback_segment_count为5个,限制回滚段个数
SQL> alter system set "_rollback_segment_count"=5;
System altered.
会话1
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU2_995037188$
会话2
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU1_2507315526$
会话3
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU27_3263502189$
会话4
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU28_1986380219$
会话5
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU29_193706344$
会话6
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU26_2187890310$
会话7
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU23_2210640156$
会话8
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU22_185849310$
会话9
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
会话10
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU30_4054687248$
会话11
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU25_848072715$
会话12
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU24_2463137615$
会话13
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU31_4292867569$
会话14
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU4_2259594378$
会话15
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU5_3454406355$
可见隐含参数_rollback_segment_count不能控制或限制数据库创建多少个回滚段
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU5_3454406355$
_SYSSMU4_2259594378$
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
16 rows selected.
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-1814233/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
