Oracle-L: RE: db locking quandry

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Tue, 20 Aug 2002 11:43:27 -0800
Message-ID: <F001.004BA4CF.20020820114327@fatcity.com>

<http://metalink.oracle.com/help/usaeng/Search/search.html#file> Doc ID: Note:1057439.6

Subject:
ORA-03113 OR TNS-12571 - INCREASING TCP/IP RETRANSMISSIONS
-----Original Message-----

Sent: Tuesday, August 20, 2002 2:24 PM
To: Aponte, Tony; ORACLE-L_at_fatcity.com

Can you point me to the note on MetaLink that had this TCP setting?

thx
bill

-----Original Message-----

Sent: Tuesday, August 20, 2002 2:15 PM
To: ORACLE-L_at_fatcity.com
Cc: Bill.Magaliff_at_lendware.com

We had a similar situation that ended up being a network setting issue. The server was showing blocking locks. It turned out to be that the client application was getting a network error. After the error it re-established the database connection and re-submitted the transaction. The problem on the server side was that the Oracle process had not detected the client's death and was waiting for 'SQL*Net message from client'. We changed the Windows TCP setting for the number of retry attempts to 15 (as per Metalink) and the so-called locking problem has not been seen since. I guess we could have enabled Dead Connection Detection but we decided to fix it at the source.

HTH
Tony Aponte
Home Shopping Network, Inc.

-----Original Message-----

Sent: Tuesday, August 20, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L

We have a client running OPS (no load balancing or transparent failover enabled due to middle-tier software limitation) who is running into db locking issues. Not sure they're related to OPS but pursuing that line of thought.

Here's the basic scenario:

application (ours) access Oracle 8.1.7 via standard Net8 . . . had been divided so that different userid's go through different OPS nodes, but we disabled that for testing
multiple sessions each running lengthly transactions involving many tables (up to 20) - each txn inserts one or several rows into each of these 20 tables and then commits at the end
application log files showed txn's hanging while inserting into the n'th table in the list. Realized that for each of these tables INITRANS had been set to 1. bumped that up on most of these tables (to either 8 or 16, depending on how much we anticipate each table being hit) and that seemed to get us further along in the list. But they still encounter locking. Oracle recommended changing GC_FILES_TO_LOCK to 0, and channelling all connections through a single node, which they did but the locking still occurs. System state dumps and trace files show a variety of things, but rather inconsistent - sometime waiting on a high water mark enqueue, sometimes (today) waiting on SQLNet message from client (in this case it appears that Oracle is waiting for the app, but the app logs indiate it's waiting for Oracle). Our client is trying to get a sniffer to evaluate potential network issues.

I've been reading about OPS locking issues - and they might try disabling OPS for a day just to see if this keeps happening.

Oh yeah - and of course this is occurring in production and is not reproducable on any other system!

Wanted to throw this out for thoughts of where to look next . . .

thanks
-bill
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Magaliff, Bill
INET: Bill.Magaliff_at_lendware.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 


-------------------------------------------------------------------- 

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Aponte, Tony
INET: AponteT_at_hsn.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message