RE: db locking quandry

Solaris, Oracle 8.1.7
doubt pinging is the issue since all txn's against these tables go through a single node, and the problem persisited even when all sessions were consolidated into a single node.

-b

-----Original Message-----
Sent: Tuesday, August 20, 2002 3:58 PM
To: Multiple recipients of list ORACLE-L

What OS? What Oracle version?
Are you pinging?? (Waiting on one of the ops nodes for transactions on the other node?)
Try running these pinging queries to see if there might be problems there:

ttitle left "Ping Ratio"

SELECT a.value cross, b.value physical, (a.value / b.value) * 100   FROM v$sysstat a, v$sysstat b
 WHERE a.name = 'DBWR cross instance writes'

    AND b.name = 'physical writes';

break on report
compute sum of pings on report
ttitle "Pings by DataFile"

SELECT df.tablespace_name, df.file_name, SUM(NVL(buf.xnc,0)) "PINGS"   FROM dba_data_files df, v$bh buf
  WHERE df.file_id = buf.file#
GROUP BY df.tablespace_name, df.file_name;

Barb

> ----------
> From: Magaliff, Bill[SMTP:Bill.Magaliff_at_lendware.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Tuesday, August 20, 2002 12:19 PM
> To: Multiple recipients of list ORACLE-L
> Subject: db locking quandry
>
> 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: Baker, Barbara
  INET: bbaker_at_denvernewspaperagency.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: 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).