Re: Incremental Commits and Disappearing Sessions

On Feb 25, 7:54 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.3.0
> Windows 2003 Server
> 16GB Memory (93% SGA( 93% Buffer Cache,<3% Shared Pool, <3% Large Pool), 7%
> PGA)
>
> In my continuing saga of disappearing sessions our off-site DBA has told us
> to do incremental commits.
>
> It's always been my understanding that you only want to commit at the end of
> your logical transaction.  After reading Tom Kyte's book that only
> reinforced that understanding.
>
> Could anybody shed any light on why he would suggest this?

Just wondering, can you post a copy of the server's SQLNET.ORA file? In one of your previous posts, you indicated that you saw "Got ORA-3135 while running PLSQL" in one of Oracle's trace files. ORA-3135, I believe, means that the server lost contact with the session. Settings in the SQLNET.ORA file, such as sqlnet.expire_time, may contribute to that error message, possibly due to a bug (see Metalink to see if it applies in your case).

I believe that you also mentioned that you were using a shared server connection, and your operation is a long running operation in a PL/SQL package. In 10g, "new resource plan directives let you set idle timeouts, cancel long-running SQL statements, terminate long-running sessions, and restore sessions to their original consumer group at the end of a top call." (http://download-uk.oracle.com/docs/cd/B19306_01/ server.102/b14231/whatsnew.htm) Is it possible that someone has been experimenting with resource plans, trying to make certain that shared server connections are not used for more than x number of minutes, or something similar?

Incremental commits - if you are holding a lock on a row or table for a long period of time, I supposed that I can understand the DBA's concern. However, for data integrity, it is best to commit only when the transaction is done. Ask yourself if you are committing when a transaction is done - you define what makes up the complete transaction. Have you tried checking V$LOCK when the session appears to hang or quietly terminate? Also, take a look at the wait events at the session and database level while the session is active.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Feb 26 2008 - 12:35:59 CST