Re: Advanced Replication Performance problems

From: <OKukartsev_at_hotmail.com>
Date: Tue, 22 Jun 1999 15:10:31 GMT
Message-ID: <7ko90s$baq$1@nnrp1.deja.com>

Hi Everyone,
Does somebody know how to force replication packages (*$RP) use indexes/primary keys?

> Colin Casalis <colin_at_rewards.co.za> wrote in message
> news:375f7262.8548526_at_news.is.co.za...
> > Hi Everyone,
> > I perform a delete of 6 records on a replicated table containing 6
> > million rows. The delete at the source replication site runs in
> > seconds, whereas the propagated delete at the remote site takes over
> > an hour! An explain plan of the original sql (at the source site)
uses
> > the appropriate index. However, the explain plan of the replicated
sql
> > (at the remote site) does a full table scan. The table structure
> > and indexes at both sites are identical. How can I get
> > the replicated sql to use the index at the remote site?
In this case (multi master replication) to apply this delete $RP will perform
6 full table scans (on table containing 6 million rows :-). The statement to perform each delete will look like: DELETE FROM "CATALOG" WHERE
(DECODE(SUBSTR(:b1,1,1),'N','Y',DECODE(:b2,"CATALOGID",'Y','N')) = 'Y' AND
DECODE(SUBSTR(:b1,2,1),'N','Y',DECODE(:b4,"CATALOGNAME",'Y','N')) = 'Y' AND

DECODE(SUBSTR(:b1,3,1),'N','Y',DECODE(:b6,"CITY",'Y','N')) = 'Y'  AND
DECODE(SUBSTR(:b1,4,1),'N','Y',DECODE(:b8,"CONTACT",'Y','N')) = 'Y'  AND
DECODE(SUBSTR(:b1,5,1),'N','Y',DECODE(:b10,"COUNTRY",'Y','N')) = 'Y'

AND
DECODE(SUBSTR(:b1,6,1),'N','Y',DECODE(:b12,"DATEENTERED",'Y','N')) = 'Y' AND
DECODE(SUBSTR(:b1,7,1),'N','Y',DECODE(:b14,"DATERECEIVED",'Y','N')) = 'Y' AND

DECODE(SUBSTR(:b1,8,1),'N','Y',DECODE(:b16,"EDITION",'Y','N')) = 'Y' AND
DECODE(SUBSTR(:b1,9,1),'N','Y',DECODE(:b18,"EMAIL",'Y','N')) = 'Y' AND
DECODE(SUBSTR(:b1,10,1),'N','Y',DECODE(:b20,"FAX",'Y','N')) = 'Y' ) AND

(DECODE(SUBSTR(:b1,11,1),'N','Y',DECODE(:b22,"MANID",'Y','N')) = 'Y' AND DECODE(SUBSTR(:b1,12,1),'N','Y',DECODE(:b24,"NUMBEROFPAGES",'Y','N')) = 'Y' AND
DECODE(SUBSTR(:b1,13,1),'N','Y',DECODE(:b26,"PHONE",'Y','N')) = 'Y' )

which apparently wouldn't use any indexes... It should look like:
DELETE FROM "CATALOG" WHERE
(DECODE(SUBSTR(:b1,1,1),'N','Y',DECODE(:b2,"CATALOGID",'Y','N')) = 'Y' AND
...
DECODE(SUBSTR(:b1,13,1),'N','Y',DECODE(:b26,"PHONE",'Y','N')) = 'Y' ) and ("CATALOG".primary key = bla-bla-bla)

I can change all $RP packages manually or use procedural replication, but...
Is there any way to force Oracle generate replication statements which can
use indexes?

Sincerely,
OK

In article <7jou25$e4j$1_at_ffx2nh3.news.uu.net>,   "codewiz51" <codewiz51_at_hotmail.com> wrote:
> You might want to make sure you have run an ANALYZE with COMPUTE
STATISTICS
> on your remote sight. Don't assume the optimizer has all the
information it
> needs on your remote site.
>
> I hope this helps,
> Gene Harris
>
> Colin Casalis <colin_at_rewards.co.za> wrote in message
> news:375f7262.8548526_at_news.is.co.za...
> > Hi Everyone,
> >
> > I'm running Oracle 8.0.5.1.0 on a SUN Solaris 2.6 Unix platform.
> >
> > I perform a delete of 6 records on a replicated table containing 6
> > million rows. The delete at the source replication site runs in
> > seconds, whereas the propagated delete at the remote site takes over
> > an hour! An explain plan of the original sql (at the source site)
uses
> > the appropriate index. However, the explain plan of the replicated
sql
> > (at the remote site) does a full table scan. The table structure
> > and indexes at both sites are identical. How can I get
> > the replicated sql to use the index at the remote site?
> >
> > Note that the delete statement at the remote site originates from
> > the $RP replication package procedure REP_DELETE. This statement is
> > different from the original delete statement at the source site.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 22 1999 - 10:10:31 CDT