Re: Rollback segment error - in SELECT statement ???

A copy of this was sent to "Chetan Wagle" <cwagle_at_ctp.com> (if that email address didn't require changing) On Wed, 22 Sep 1999 10:50:58 +0530, you wrote:

>Hi Thomas,
>
> Firstly, thanks for your prompt reply and for correcting me about the
>function of rollback segments.
>
> I still have a doubt:
>
> Assume that I have only one single medium-sized rollback segment. When I
>fire my billing procedure, it starts using this segment. Assuming that
>maxextents is unlimited, the rollback segment will grow till 100 records
>have been processed. After this there occurs a commit and the extents in
>that segment are marked as free. I am assuming that optimal is not set, so
>there is no auto-shrinking.
>
> Now, the processing of the next 100 records has started in the same
>rollback segment. Since the extents are already marked as unused, they will
>be reused in cyclical fashion. While the processing is in progress, I fire
>my select count(*) query and the query will have to scan each row to
>determine the number of rows completed.
>
> Whatever records have been committed (the earlier 100) can be read from
>disk since the transaction was committed. The records to be used in future
>are untouched so no problems there either. The status of the 100 records
>which are getting billed can be taken from the rollback segment.
>
> Then where is the problem ?
>
>

you have a process doing this:

update 100 records
commit
update 100 records
commit
....
update 100 records
commit

Lets say you RBS has 25 extents. Lets say 100 records fit into an extent.

You have another process doing this:

select count(*) from T where x = 'U';

They are running concurrently. The update process starts first, it updates 100 records and fills extent 1.

The select process now starts. The results of this query are FIXED at the point in time the query begins (it will return the answer that was in the database AT THE POINT IN TIME the query was OPENED). It begins reading.

Meanwhile, the update process commits. It updates again and commits. It does this, oh say 100 times while the select count(*) is running. That means it has the opportunity to rewrite the rollback segment 4 times while the select is running.

Whenever the select hits a block that changed AFTER the query began (don't care if the data is committed or not -- if the block CHANGED after we began) it must to a read aside to the rollback segment to get the consistent read. Since we've wrapped so many times already -- its gone, the select will get a 1555.

I believe the best, most performant solution in your case will be to let the billing process put its "progress" into the v$session table. No more expensive select count(*)'s and very fast to instantly see how far the process it (becomes a simple "select * from v$session where module = 'BILLING'")

> I know I have gone wrong somewhere, but exactly where ?
>
>TIA,
>Chetan
>
>
>
>Thomas Kyte wrote in message <3IrnNyRz8Hn5TVq2APTRL0lbBS8+@4ax.com>...
>>A copy of this was sent to "Chetan Wagle" <cwagle_at_ctp.com>
>>(if that email address didn't require changing)
>>On Tue, 21 Sep 1999 17:05:42 +0530, you wrote:
>>
>>>Hi everybody,
>>>
>>> I am very clear about the functioning of rollback segments and the
>>>conditions in which you get errors in rollback segments. As we all know,
>>>rollback segments store the before and after images of data blocks.
>>>
>>
>>rollback = undo = Before images.
>>redo logs= redo = After images.
>>
>>> I have three tables that have a very large number of records :
>typically
>>>in the range of 2.5 to 2.8 million records per table since I work on a
>>>billing application. I have medium sized rollback segments and I have
>>>created one huge rollback segment which I bring online whenever I run my
>>>billing.
>>>
>>> In one of my tables, I have a column named status. Each record
>>>corresponds to one sale. Before I run billing, the status of the records
>is
>>>'U' meaning unbilled. As the tariff amount calcluation (a stored
>procedure)
>>>runs along, after every 100 records, it commits and changes the status to
>>>'B' meaning Billed successfully or 'P' meaning problem in billing.
>>>
>>> When billing is not running, I can do all operations without any
>>>problems. However, when I run billing (which typically takes a number of
>>>hours), I check the count(*) where status='U' to find out how many
>records
>>>are still remaining to be processed and this tells me the progress.
>>>
>>> A strange thing that I have noticed is that when I fire this select
>>>query when the billing is running, I get rollback segment errors. as per
>>>oracle, rollback segments are not used unless you perform some data
>>>manipulation - oracle says that a rollback segment gets allocated only
>when
>>>the first insert/update/delete statement comes along.
>>>
>>
>>the select count(*) is doing a consistent read. It is reconstructing bits
>and
>>pieces of the table you are counting records in as it goes along. This is
>why
>>you are getting the 1555.
>>
>>It is due to you medium sized rollback segments and the fact that as you
>run
>>along and do 100 records/commit/100 records/commit -- you are allowing
>that
>>rollback to be reused. At some point in time -- it is being reused as the
>>rollback segment wraps and thats the problem. The select count(*) can no
>longer
>>reconstruct the table as it appeared at the beginning of the query so it
>fails.
>>
>>Solution: have larger allocated rollback segments (that does not mean
>rollback
>>segments that could GROW to be large, that means rollback segments with
>many --
>>20 for example -- minextents that are permanently large). I myself
>typically
>>run with rollback segments that have minextents of 20-25 and extent sizes
>of
>>500k to 1m ( minextents 20 initial 1m next 1m is my current favorite).
>>
>>Solution: make the count(*) go faster. Use Parallel query to speed up the
>>count(*). Hopefully the query can finish before the rollback wraps.
>>
>>Solution: use dbms_application_info in the billing procedure and have the
>>billing procedure populate columns in the dynamic view V$SESSION (you can
>put
>>data in 3 of these columns) so that billing shows its process as it goes
>along
>>-- no more select count(*). Billing might put in there
>>
>>MODULE ACTION CLIENT_INFO
>>-------- --------- ------------
>>Billing 10,123 rows to do Processed 5,000 of them
>>
>>
>>for example.
>>
>>> Then why do I get this error when I fire a select ? The billing
>procedure
>>>takes a lot of the db buffers and constantly flushes data in and out. the
>>>machine also slows down considerably when billing is on. I have Oracle 7.3
>>>on a Sun UltraSPARC-2 machine running Solaris 2.6.
>>>
>>
>>It'll speed up if you don't count(*) during billing (less to do) so perhaps
>>solution 3 is your best bet.
>>
>>>
>>>Any ideas anyone ???
>>>
>>>TIA,
>>>Chetan
>>>
>>>
>>>
>>>
>>
>>
>>--
>>See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
>Oracle8i'...
>>Current article is "Part I of V, Autonomous Transactions" updated June
>21'st
>>
>>Thomas Kyte tkyte_at_us.oracle.com
>>Oracle Service Industries Reston, VA USA
>>
>>Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 22 1999 - 10:25:46 CDT