Skip to Main Content
  • Questions

  • pending transactions and increase of rollback segments size

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Pierre.

Asked: October 14, 2006 - 9:38 am UTC

Last updated: October 14, 2006 - 7:36 pm UTC

Version: 9.2.0.

Viewed 1000+ times

You Asked

Hi Tom,

We have the following problem with Oracle 9.2.0.6 and with manual rollback segments : they keep on increasing their size (about 300 MB per day) and only get back to optimal size if you shutdown the 3-tier application (with or without shutting down the database). We have a lot of user connections (about 300) because they are never released by application server for possible reuse. This application is a 24h/7days application that should not be stopped. Database is only a few gigabytes large. Current rollback segment tablespace size is 1,6 Gb
and we have currently 16 segments. According to v$transaction view we have less than 100 concurrent transactions and many of them are running since more than 1 day... and their undo size is only a few blocks large.

Oracle support recommends that we first commit pending transactions: the problem is that we have seen that the corresponding Oracle sessions are orphaned sessions (the client is not there any more for many reasons but the application servers do not handle time-outs
and do not know it ...).

1. How can be sure that these pending transactions are causing rollback segments to constantly grow ? Is this an expected behaviour ?
2. Can we kill transactions (we cannot kill the sessions: the application server does not support this!) ?
3. If we move to automatic undo management, would the rollback segment use less space with these pending transactions ?

Many thanks.



and Tom said...

that is why it is growing!!!

You have transactions that are small - but uncommited- and uncommited for more than 1 day!!!!!

You have a bug in your developed code. Someone is NOT commiting. UNDO works in a circular buffer - think of a rollback segment as a list of extents connected in a circle (extent 1 points to 2, 2 points to 3, .... the last extent N points to 1)

If we get to extent "N" and extent 1 still has an active transaction in it (your one day old stuff), we cannot advance into it (we would overwrite data needed to do the rollback otherwise!!!)

Hence, we have no choice but to try and allocate extent N+1 and have N point to N+1 and N+1 points to 1.

When we fill N+1, same thing happens.


for you to have pending, uncommitted transactions for more than 1 day is certainly a BUG IN THE APPLICATION itself, it must be fixed.


You can certainly kill the sessions, however, that will result in the loss of whatever pending uncommited work they have.


Moving to automatic undo management would patch a BIT of the problem, but would certainly not "fix it" entirely.




Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Some precisions

Pierre Forstmann, October 14, 2006 - 11:10 am UTC

Actually we know some of the root cause:
- end users killing the client: not handlded by application servers
- end users may leave open application session for hours and application is session killed by Citrix time-outs(that seems only able to send a kill message and not a close message ...: this cannot be handled by application server).

We cannot kill the sessions because this can lead to big problems in the application servers... But I think we can ask the vendor to implement time-out in the application servers.

Thanks.

Tom Kyte
October 14, 2006 - 7:36 pm UTC

sqlnet DCD (dead client detection) is for you I think - read about it in the net admin guide.

we'll ping clients and if we don't hear back, we terminate them.

DCD

Roderick, October 15, 2006 - 1:25 am UTC

DCD does allow the database to detect if application server processes (the database clients) have died abnormally without closing the connection to the database. You will likely still need another timeout mechanism between the app server processes and its end user clients in addition to DCD.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.