topic Re: Rollback segment issue !! in Operating System - HP-UX

Rollback segment issue !!

Chris Fung — Mon, 22 Jul 2002 12:24:39 GMT

Hi all,

I have a question for Rollback segment!!

Suppose I have a table "ABC" which contains 100M data. If my rollback segment tablespace contains 1 datafile which is 50MB. There are 2 rollback segments (Non-System) defined with autoextend off. Each with storage paramter (inital 1M next 1M minextent 10 maxextent 50)

What happen if:
1. I issue a sql command "select * from ABC'"??

2. I issue a sql command "select * from ABC where x='123';" Suppose the result set contains only 30 MB of data.

Cheers,

Chris,

Re: Rollback segment issue !!

Jeanine Kone — Mon, 22 Jul 2002 12:48:43 GMT

Rollback segments are used when you are updating, deleting, or inserting data. The result set of an insert statement will not be affected by the size of your rollback segements.

The problem you need to watch out for is if you are running select statements while also performing inserts, updates, and deletes. Once the rollback segement is filled up with transactional data that has not yet been committed or rolled back - your dml may fail, or your queries could die with snapshot too old.

Re: Rollback segment issue !!

Brian Crabtree — Mon, 22 Jul 2002 20:55:07 GMT

Jeanie is correct. You are thinking of sorting, which is done by the temporary tablespace. This is defined by the "temporary_tablespace" setting under "dba_users" for your user.

Rollback segments would be used in something like the following:

insert into DEF select * from ABC where x='123'

Brian

Re: Rollback segment issue !!

Jean-Luc Oudart — Tue, 23 Jul 2002 07:52:23 GMT

Hi Chris,

if you have access to Oracle Metalink (you should if you have an active support contract), you can search for "optimizing rollback segments" - Select doc id 107085.1
This doc contains pointers to others documents on same subject.

Jean-Luc