Link to home
Start Free TrialLog in
Avatar of Rizla1972
Rizla1972

asked on

*** URGENT *** ora-01555 rollback segment too small

Background:
.VB client, Weblogic5.1 app server, and Oracle 8.1.6 back end.

Problem:
.Whenever I get a user base of circa 50 users I get the error :- java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number string with name "string" too small.

Solutions already tried:
.Increassed size of rollback segment
.Made rollback segment space unlimited
.Deleted and replace rollback segments
.Monitored the rollback segment useage when error is produced and useage is only at about 35%

Has anyone any idea what else could be causing this, ITS DRIVING ME ROUND THE BEND :)
Avatar of Rizla1972
Rizla1972

ASKER

I have also tried preventing the rollback segment size from shrinking, which allowed the users to run for a little longer before the error was produced.
I have also tried preventing the rollback segment size from shrinking, which allowed the users to run for a little longer before the error was produced.
I have also tried preventing the rollback segment size from shrinking, which allowed the users to run for a little longer before the error was produced.
Hello Rizla

From you error. I just went to the Oracle Errors page and got the info.

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: Use larger rollback segments.


Okay coming back to your probs.
How many user are allowed to query at a time. Please make sure ur processes of oracle is more and number of user connecting should not exceed and make sure when write is required used writable or open with readonly mode. Since if u open up the records in write. Use transaction mode properly .

I fear ur transaction mode has some probs.

Hey if am i wrong in understand ur probs. Please let me know what the exact probs and what u r looking for

Cheers

Shyam
shyamkumarreddy,

Thanks for your reply but I feel it's not a process problem.  Transaction isolation levels are set in the app server - we use read-consistent which is why we need rollback segments to last a while.  We do access the rows in read only mode unless we try to update them.

Anyone any other suggestions
Good Rizla

How clients trying to connect the application at a time.
And

I have a doubt. How many connection does a user open a connection during the application.

Please let me know that.


Shyam
Shyam,

We have one client ( the app server).  It creates 4 connections initially but can make more.  It should never have more than 15  active connections since that's how many threads it has.

Neil
Rizla

Good. When do u get this error once the client has reached more then 8 connection. Or on the begin of application itself.


Shyam
Shyam,

The Oracle client is the app server which itself also has a client (activex web app).

The error does not occur when I start the app server, it happens when about 50 web app clients connect to the app server, I can get about 40 to rn no problem at all but somewhere between 40 and 50 users (generated using mercury Loadrunner) is where this error is reported back from oracle to the app server and consequently back to the activex web client.

Hope this helps, much appreciate your time

Rizla
ASKER CERTIFIED SOLUTION
Avatar of shyamkumarreddy
shyamkumarreddy
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Shyam,

I have monitired the connections and they seem to be being closed down again, I left a run running overnight with a smaller number of users to prevent the error appearing (40), when I came back in this morning about half of the users had fell over (different errors, mostly due to web architecture), but with the remaining users still running I could see that the amount of connections had dropped from 43 to 19, therefore, I can conclude that from what I can see the connections are not being left open.

Any other ideas?

Rizla
Hi Rizzla

If not connection. Is any locks causing u the trouble.
Can u find out any of locks has been made to the tables during the writing and not released.

This is the only way i have. Nothing more to add
Just check up whether anything of ur tables have been locked during writing.


Shyam
mmmmm, no its definately not that, this is really getting on my nerves now, I MUST get to the bottom of this, its completely strange how I can be getting rollback segment too small when it is only about 1/3 utilized at the time the error is produced?  Looks like you are running out of solutions too, oh well thanks for your time, if you can think of anything else that may cause it then plz post another reply, in the meantime I am off to curl up in a corner to cry for the rest of the day :)

Rizla
Hey Rizla
I will try to find out from the DBA
Okay. If i get a result i will post it:)
You can solve it.
YOu can do it okay

Take Care Bye.
Cheers
Shyam
Ok, thanks again for your time, still no solution from me so I guess I should go thru into server room and kick the living daylight out of our Oracle box and see if that helps, if nothing else it will relieve my stress :o)

Rizla


No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:


--  points to shyamkumarreddy


Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
sudhakar_koundinya
EE Cleanup Volunteer
---------------------
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post your concern in THIS thread.