RE: db locking quandry

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 20 Aug 2002 10:43:57 -0800
Message-ID: <F001.004BA343.20020820104357@fatcity.com>

Any indexes on these tables? what are the settings of PCTFREE , INITRANS on these indexes?

Regards,

Waleed

-----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:

1. 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
2. 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
3. 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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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). Received on Tue Aug 20 2002 - 13:43:57 CDT