Oracle-L: Performace across a dblink

From: Kirsch, Walter J (Northrop Grumman) <walter.kirsch-eds_at_eds.com>
Date: Thu, 21 Nov 2002 09:08:38 -0800
Message-ID: <F001.00508FFE.20021121090838@fatcity.com>

Oracle 8i and HP-UX, 9i and SCO Unix

I have a customer who wants to MERGE into his 9i database from my 8i database across a dblink. On his side, the merge takes nearly an hour ( one SQL merge statement across 10Mbit Etherlink processing 2,600,000 records). I'd like to know how to test and measure the impact of his merge on my database. I surmised that contention for rollback, and ultimately a "snapshot too old", would be the make-or-break point; but nothing I've tried shows any anomaly there. Perhaps I'm looking in the wrong place or with the wrong tools. Can anybody help? I'm looking for test steps to provoke stress and script(s) to measure it.

Past Efforts: I've executed a "delete from my_table" while he was "MERGEing USING my_table_at_dblink". While these two were running, I repeated the following script, but it didn't recognize any trouble spot.

prompt
prompt Rollback Segment Usage

prompt ======================

set linesize 133

column name format a10
column usn format 99
column sid format 999
column xacts format 9999
column extents format 999999
column extends format 99999
column waits format 9999
column wraps format 9999
column shrnk format 9999
column name format a12
column osusr format a12

SELECT r.usn,      r.name,  s.osuser osusr,
        s.sid,   x.extents, x.xacts,
       x.extends,  x.waits, x.shrinks shrnk,
       x.wraps, x.writes
FROM   v$rollstat X,
       v$rollname R,
       v$session S,
       v$transaction T
WHERE  t.addr       = s.taddr (+)

AND x.usn (+) = r.usn
AND t.xidusn (+) = r.usn
ORDER BY r.usn;

--

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

Author: Kirsch, Walter J (Northrop Grumman) INET: walter.kirsch-eds_at_eds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 Thu Nov 21 2002 - 11:08:38 CST