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 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 Received on Tue Sep 21 1999 - 08:48:24 CDT