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@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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
