Skip to Main Content
  • Questions

  • Too many inactive sessions through Database Link

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 18, 2011 - 10:00 pm UTC

Last updated: December 19, 2011 - 5:04 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Tom,

One of the external systems executes a package in our DB through a db-link.
They invoke this package through a trigger in one of their tables. They are using autonomous transactions in this trigger.
However, over a period of time, we see too many inactive sessions for this call and the database performance degrades significantly. When we kill these sessions manually, the performance restores to normal.

How do we control this continuous increase in number of sessions through DB Link?
- We tried using idle_time, but it just sets status to SNIPED? Although we can kill sniped processes, but I think we may be killing the messenger here rather than attacking the root cause.


and Tom said...

They are using autonomous transactions in this trigger.

three things I'd love to get rid of in the database:

a) triggers
b) autonomous transactions
c) when others

I'll be we have a trifecta here - I'll be their trigger has a when others exception handler that doesn't re-raise the exception either!

Using an autonomous transaction in a trigger is as near to being 100% a bug as you can get. I am 99.999999999999(infinitely repeat that 9)% sure that it is a bug. There is one, exactly one, use of an autonomous transaction. That is in writing an error logging package.



The open database link connections are caused by the remote system starting some distributed work - and then never logging out itself. Does the remote system keep its sessions open forever? Do they use hundreds or thousands of concurrent sessions?


You can:

a) reduce the number of concurrent sessions on the remote system

b) ask the remote system to close the database link when they are done with it (alter session)

c) configure shared database links http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_admin003.htm#ADMIN12161



(c) is probably what you are looking for if you cannot convince the remote system to have far far fewer sessions (they should have far far fewer sessions - then their own database would likely perform a whole bunch better than it currently does)

Rating

  (2 ratings)

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

Comments

Another use for Auto. transaction

leor, December 19, 2011 - 12:30 pm UTC

Tom, we have another valid use for an autonomous transaction. We have a case where a caller may call a function that enqueues a message to an AQ. We want the message to get sent to the queue regardless of whether the calling context does a commit or not. So, in this case we have the function enqueue call within an autonomous transaction.

Thanks,
Tom Kyte
December 19, 2011 - 5:04 pm UTC

are you sure you do - or did you just program it that way. Give us a bit of context - why would you want to send a message about an action that might not happen?


visibility DBMS_AQ.immediate

A reader, December 19, 2011 - 8:47 pm UTC

leor,

If you enqueue with visibility DBMS_AQ.immediate then you the transaction is immediately commited to the queue, and you don't need an autonomous transaction.
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.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.