Re: Rollback Problem (HELP!)

From: Joseph Sumalbag <joseph_sumalbag_at_bose.com>
Date: 1998/01/06
Message-ID: <34B290E7.19C9@bose.com>#1/1

Snapshot too Old is a typical problem .... Using a larger rollback segment only delays the appearance of the error ( and hope your transaction finishes before the error occurs ) ...

A few "solution" to this are

1. use larger rollback segment , You could set small rollback segment offline to insure that you are using the very very large rollback segments while this querry executes ...

or

2. Schedule the transaction during evening when there are few users using the system . Few commits occuring means a greater chance of success ....

3. Break the transactions into pieces ....

#############################################################

The REAL SOLUTION to this is to recode your sql or TUNE it so the querry executes faster ... Make sure it is not doing full table scans ..Use indexes if you must .

As far as I can see your query can still be improved by using EXISTS instead of joins that you are doing( since you are getting values only from one table and the reast are only for conditions).....

############################################################

ALTER TRANSACTION USE ROLLBACK SEGMENT ...... Does NOT WORK for this problem since your problem is not with the ACTIVE and in use transactions in the rollback segments but with INACTIVE in use entries that your transaction uses for point in time consistency ...

Alter transaction works well for (INSERT, UPDATE, DELETE) not with (SELECT) which is really your problem SQL . A long running SELECT SQL still needs the block in your Rollback segment if the block it was trying to query has changed before the SELECT finishes ..

Since he(The select transaction) found out that the block he was querying had already change (by another committed transaction) , he then locates a copy of the unchanged data in the rollback segment .....
This is how Oracle enforces consistency with in reference to the time the select statement starts .

If the block it was trying to access from the rollback segment has already been overwritten by another (INSERT,DELETE,UPDATE) statement then you'll have snapshot too old !!!

I hope this helps ...

Kurt Tucker wrote:
>
> I'm using OWGS 7.3.3.0 for NT and recently we have found the need for both
> Large and small RBS's (We used to only need very small) I have tried to set
> transaction to use the newly created LARGE_RBS but at some point The query
> bounces out to the older Small RBS's. I feel that the part of the query
> that calls function FOXUTL_PACK.DTFTMIN & DTFTMAX may be causing this.
> Below is an explanation of the utiliy and below that is the actual query.
> Of course the real problem is (Snapshot too old! ) :-( Any help with this
> newby question would be greatly appreciated. Thanx...
>
> The Package.
>
> The package foxutil_pack.dtftmin and dtftmax picks the minimum of dates in
> 2 tables magprod(for given mid,product), join of proddef and pdocsview (for
> the product) or default minimum (1-1-84) and maximum date (current date).
>
> The Query.
>
> Select distinct TO_CHAR(Delc.Deid,'B999999999') as Deid1, Delc.DE as DE1,
> Delc.accno,
> Delc.offset from MFS.DELC, MFS.ARTLC, MFS.ARTICLE, MFS.MAGPROD
> WHERE Delc.TagNo like '1%' AND Artlc.DEID = Delc.DEID AND
> Artlc.AN = Article.AN and (article.UPDATETYPE IS NULL OR
> NOT(article.updatetype = 'D' or article.ABSTYPE = 'B')) and
> Article.DtFormat between
>
> MFS.foxutil_pack.DTFTMIN(MAGPROD.PRODCODE,MAGPROD.CHANNELID,MAGP
 ROD.MID)
> and
>
> MFS.foxutil_pack.DTFTMAX(MAGPROD.PRODCODE,MAGPROD.CHANNELID,MAGP
 ROD.MID,'01-
> FEB-98') and
> Article.MID = MagProd.MID and
> MagProd.ProdCode = 'F2L' and
> MagProd.ChannelId = 'LOCAL'
> Order by deid1

-- 
================================================================
============================
| Joseph Sumalbag                                                                           
|
| Oracle DBA                                                                                
|
|                                                                                           
|
| The opinions expressed above are my own and doesn't 
 necessarily                           |
|reflect the opinion of any of my client company or my employer.                            
|
================================================================
============================

Received on Tue Jan 06 1998 - 00:00:00 CST