回滚段头事务表的大小

yaguo_96

原帖由 Yong Huang 于 2008-5-21 01:10 发表
晶晶小妹, I think your answer is misleading. That parameter is used to calculate how many rollback segments should be brought online on instance startup (transactions/transactions_per_rollback_segment). I don't think there's any more use of the parameter anywhere else.

yaguo_96, where did you see 0x2f? Please show us the exact step you did and the part of the dump file with that number.

Yong Huang

你好我是这样做的：
alter system dump undo header '_SYSSMU1$';
导出结果在附件显示0x2f 个事务项。这是不是代表这个回滚段最多只能支持这么多事务。这个值可以设置吗？ 复件 orcl_ora_4036.txt (9.41 KB, 下载次数: 31)

2008-5-25 11:27 上传

点击文件名下载附件

sqysl

我觉得，你在这里的0x2f只是说明目前段的事务表里总共有这么多事务表项，并没说明它可以容纳多少事务表项，这些东西，我建议你看一下相关资料，没必要在这里问，毕竟没太多人专门研究这方面，这是一个方向，但会花费掉很多时间和精力。

Yong Huang

原帖由 yaguo_96 于 2008-5-24 21:27 发表

你好我是这样做的：
alter system dump undo header '_SYSSMU1$';
导出结果在附件显示0x2f 个事务项。这是不是代表这个回滚段最多只能支持这么多事务。这个值可以设置吗？532059

According to
http://freelists.spaces.live.com ... 5B281E6B4!177.entry
chd means "commit head". So it's different from the number of transaction slots in the segment. In your case it just happens to be the same as the last slot number (the bottom index) but it may well be different.

Now the question becomes, Does the last index i.e. slot number 0x2f mean we can only have 48 (2F in hex) concurrent transactions in this undo segment? Metalink thread 177926.999 says (as many other documents) that the max number of concurrent transactions one segment can support is determined by db_block_size. If you dump all your undo headers (or simply select max(KTUXESLT), KTUXEUSN from x$ktuxe group by KTUXEUSN order by 2), you'll see your system undo segment (USN=0) has max slot# 97 and so can support 98 slots (index starts with 0) if your db_block_size is 8k. I didn't do a test. But suppose you have 48 sessions all set to use the same undo segment and all do a transaction each, and then you open another session doing a transaction, I think it still works and will not wait. Test it and let us know.

To use a specific undo segment in AUM, do something like this on a test box:
alter system set "_smu_debug_mode" = 45 scope = memory;
set transaction use rollback segment "_SYSSMU1$";

Yong Huang

晶晶小妹

对的，是代表你这个回滚段只能同时有2F个事务。这个数字是够用的。当你事务比较多时，ORACLE倾向于创建更多的回滚段。

sqysl

晶晶女侠的这个回答不太明确，能具体点吗？是一个回滚段只能有48个并发事务吗？也就是不能再多了吗？这个数字是怎末产生的？是来自资料还似乎您的试验或计算？如果可能请告知，谢谢。

晶晶小妹

我只试验过在4K的块和8K的块下，这个数据不一样。块越大，这个数字就越大。具体怎么计算的，我不知道。
Yong Huang  的那段英文我没看，我阅读英文的速度比较慢，而且我看英文比较费心，现在正在修养身体阶段，不想这么费心。是不是说有个急藏参数：_smu_debug_mode可以设置这个值啊？

Yong Huang的很多回答都很精彩，但就是英文看起来太吃力。

sqysl

哦，不好意思，俺打扰你了，yong huang在这方面的造诣很深，涉猎也很广，使我受益匪浅。
顺便善意提醒您，IT领域虽然很吸引人，但也很耗费精力，希望您能注意身体，早日康复。

我顺便把yonghuang的那段翻译如下：

    根据http://freelists.spaces.live.com ... 5B281E6B4!177.entry，chd的意思是“提交头”的意思。因此，它和段中事务槽的数目是不同的。你的情况中，它仅仅碰巧和最后槽号（底部索引）相同，但他们也许是完全不同的。
    现在，问题出现了，最后索引即槽号0x2f意味着在这个回滚段中我们只能有48个并发事务吗？Metalink thread 177926.999 说（象很多其他文档一样）一个段能支持的最大并发事务数由db_block_size来确定。如果你导出你所有的回滚头（或者仅仅运行select max(KTUXESLT), KTUXEUSN from x$ktuxe group by KTUXEUSN order by 2），如果你的db_block_size也是8k的话，你将看到你的系统回滚段（usn=0）有最大槽号97，因此能支持98个槽（索引从0开始）。我没做测试。但是，假设你把48个会话都被设置为使用同样的回滚段，并且每个会话运行一个事务，接着，你打开另外一个会话开始一个事务，我认为它还是可以工作并不会发生等待。测试并告诉我们。
为了在AUM（自动回滚管理）模式下使用一个确定的回滚段，测试时可以做如下事情：
alter system set "_smu_debug_mode" = 45 scope = memory;
set transaction use rollback segment "_SYSSMU1$";
YONG HUANG

（鄙人的话，呵呵）这是两个命令，第一条是设置"_smu_debug_mode" 的值为45，由此可见，该参数是有关控制回滚段使用的隐含参数，然后，第二条命令就是设置用户事务使用哪个确定的回滚段，这里为"SYSSMU1$"

[ 本帖最后由 sqysl 于 2008-5-28 23:03 编辑 ]

晶晶小妹

Thanks sqysl ，也要谢谢YONG HUANG 。
我以前以为“set transaction use rollback segment”命令只能用于手动回滚段管理呢。

Yong Huang

[quote]原帖由 [i]Yong Huang[/i] 于 2008-5-27 15:50 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10498952&ptid=978455][img]http://www.itpub.net/images/common/back.gif[/img][/url]

...
I didn't do a test. But suppose you have 48 sessions all set to use the same undo segment and all do a transaction each, and then you open another session doing a transaction, I think it still works and will not wait. Test it and let us know.
...
[/quote]

I did a test and proved myself wrong. 48 transactions is the maximum a rollback (undo) segment can support when the db_block_size is 8k. Since I don't have UltraEdit, I edited the files the old fashioned way:

alter system set "_smu_debug_mode" = 45 scope = memory;
create table t as select rownum x from (select * from dual connect by 1=1) where rownum <= 100;

cat 1.sql:
set transaction use rollback segment "_SYSSMU5$";
delete from t where x = 1;

i=2; while (( i <= 48 )); do cp 1.sql $i.sql; let "i+=1"; done

#Change x=1 to x=[a higher number] for each file
i=1; while (( i <= 48 )); do perl -pi -e "s/1/$i/" $i.sql; let "i+=1"; done

cat test.sh:
sqlplus -S yong/yong @1 &
sqlplus -S yong/yong @1 &
...[total 48 lines]...

perl -pi -e '$x=$.; s/1/$x/' test.sh

cat test.sh
sqlplus -S yong/yong @1 &
sqlplus -S yong/yong @2 &
sqlplus -S yong/yong @3 &
...

. ./test.sh

select * from v$rollstat where xacts > 0;

I see 48 under xacts for USN 5. Now I do one more delete on a non-interfering row:

SQL> set transaction use rollback segment "_SYSSMU5$";

Transaction set.

SQL> delete from t where x = 60;
delete from t where x = 60
            *
ERROR at line 1:
ORA-01554: out of transaction slots in transaction tables

It would be easier if I created an undo tablespace using 2k block size (even just for the undo tablespace i.e. after setting db_2k_cache_size).

Yong Huang

[[i] 本帖最后由 Yong Huang 于 2008-5-28 22:36 编辑 [/i]]

sqysl

谢谢了yonghuang，学习。
草草的看了一遍，没完全看明白，尤其是PERL脚本的部分，为什么用到PERL脚本呢，都用SHELL不可以完成吗？看来SHELL在文件内容的操作上远没PERL方便啊。

[ 本帖最后由 sqysl 于 2008-5-29 15:46 编辑 ]