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

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 ?

   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 Received on Wed Sep 22 1999 - 00:20:58 CDT