Rollback Segments

[Thomasps]

Hi All

Creating the rollback segments


In my database, got 41 rollback segments with 480 users and value of transactions_per_rollback_segments is 5. The nature of the database is OLTP (Banking). Oracle version 7.3 in Sun Solaris 2.8. Now I need to have more rollback segments. What is the best method

1. Create more rollback segments or
2. increase the value of transactions_per_rollback_segments

Thanks


Thomas

[Calvin_Qiu]

I prefer more rollback segments to eliminate contentions.

[pando]

dp you see any contention at all?

[Calvin_Qiu]

As Oracle recommend to configure one rollback segments for each 4 concurrent transactions. Query V$UNDOSTAT and V$ROLLSTAT to see how well it perform.

[launchpad]

I think I'll ask same question as pando did, if there is any contention at all ?
and based on what facts have you decided to increase rollback segments ??
Obviously, once you are sure of increasing anything and you have proved that increase in rollback segments is really going to help( on development box), go for more rollback segments ( rather than transactions_per_rollback_segments ).

[grjohnson]

If you've got header and undo block contention add more Rollback.

select CLASS, COUNT, TIME
from v$waitstat
where CLASS like 'undo%';

You'll need to monitor the undo rate before making the decision.

Cheers,

[Thomasps]

Hi All

Thanks for your time!!

Yes, I found the contention on the undo block is gradully incresing. My question is not just based on the number of users(sorry i didn't mention the contention!! ). Also I am experiencing waits in the rollback segments. Also, every week 20-25 new users adding to the system and may go up to 1500. One important thing is, if you are not setting the (oracle 7.3... May be I am the only one DBA struggling with 7.3 in the earth!!) parameter transactions_per_rollback_segment is found ( default value) 72. Don't understand, what is the logic setting the value as 72.

Any how my ultimate plan to create more rollback segments as compared with the 'Concurrent Transactions' and contention on the RBS blocks.

Thanks everybody for your replies..


Thomas

[jmodic]

Originally posted by Thomasps
One important thing is, if you are not setting the (oracle 7.3... May be I am the only one DBA struggling with 7.3 in the earth!!) parameter transactions_per_rollback_segment is found ( default value) 72. Don't understand, what is the logic setting the value as 72.

That's strange - with every release I've worked so far, the default value for this parameter was 5. Also, I hope you are aware that this setting has absolutely nothing to do with the actual number of concurent transactions each rollback can handle. This parameter is only relevant at startup to help Oracle decide how many of the available rollback segments to acquire and put online.

[hacketta]

Originally posted by Thomasps


May be I am the only one DBA struggling with 7.3 in the earth!!

You're not alone. We have prod databases running 7.1 and 7.3!