Advanced Replication Performance problems

[Colin Casalis]

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.

[codewiz51]

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 <co...@rewards.co.za> wrote in message
news:375f7262...@news.is.co.za...

[OKuka...@hotmail.com]

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

> Colin Casalis <co...@rewards.co.za> wrote in message
> news:375f7262...@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...@ffx2nh3.news.uu.net>,

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[OKuka...@hotmail.com]

I'm sorry about my previous posting...
There are more details about this problem:

CREATE TABLE catalogpage
(
pageid NUMBER(9) NOT NULL,
catalogid NUMBER(9) NOT NULL,
image VARCHAR2(100) NOT NULL,
ocrtext VARCHAR2(100) NOT NULL
)

ALTER TABLE catalogpage
ADD CONSTRAINT pk_catalogpage PRIMARY KEY (pageid,catalogid)

Oracle generate those statements to perform replication:

delete from "CATALOGPAGE"
where (
decode(substr(column_sent$_varchar2, 1, 1),
'N', 'Y',
decode("CATALOGID1_o", "CATALOGID", 'Y', 'N')) = 'Y'
and
decode(substr(column_sent$_varchar2, 2, 1),
'N', 'Y',
decode("IMAGE2_o", "IMAGE", 'Y', 'N')) = 'Y'
and
decode(substr(column_sent$_varchar2, 3, 1),
'N', 'Y',
decode("OCRTEXT3_o", "OCRTEXT", 'Y', 'N')) = 'Y'
and
decode(substr(column_sent$_varchar2, 4, 1),
'N', 'Y',
decode("PAGEID4_o", "PAGEID", 'Y', 'N')) = 'Y'
);
I.e. it doesn't use PRIMARY KEY to delete
-----------------------------------------------------------------

(Look at last 2 rows)
update "CATALOGPAGE" set
"CATALOGID" =
decode(substr(column_changed$_varchar2, 1, 1),
'N', "CATALOGID",
'Y', "CATALOGID1_n",
NULL, decode("CATALOGID1_o",
"CATALOGID1_n","CATALOGID",
"CATALOGID1_n")),
"IMAGE" =
decode(substr(column_changed$_varchar2, 2, 1),
'N', "IMAGE",
'Y', "IMAGE2_n",
NULL, decode("IMAGE2_o",
"IMAGE2_n","IMAGE",
"IMAGE2_n")),
"OCRTEXT" =
decode(substr(column_changed$_varchar2, 3, 1),
'N', "OCRTEXT",
'Y', "OCRTEXT3_n",
NULL, decode("OCRTEXT3_o",
"OCRTEXT3_n","OCRTEXT",
"OCRTEXT3_n")),
"PAGEID" =
decode(substr(column_changed$_varchar2, 4, 1),
'N', "PAGEID",
'Y', "PAGEID4_n",
NULL, decode("PAGEID4_o",
"PAGEID4_n","PAGEID",
"PAGEID4_n"))
where (((1 = 1 and
decode(substr(column_changed$_varchar2, 2, 1),
'N', 'Y',
'Y', 'N',
decode("IMAGE2_o", "IMAGE2_n", 'Y', 'N')) = 'Y' and
decode(substr(column_changed$_varchar2, 3, 1),
'N', 'Y',
'Y', 'N',
decode("OCRTEXT3_o", "OCRTEXT3_n", 'Y', 'N')) = 'Y'
and
1 = 1)) or
(1 = 1 and
decode(substr(column_sent$_varchar2, 2, 1),
'N', 'Y',
decode("IMAGE2_o", "IMAGE", 'Y', 'N')) = 'Y' and
decode(substr(column_sent$_varchar2, 3, 1),
'N', 'Y',
decode("OCRTEXT3_o", "OCRTEXT", 'Y', 'N')) = 'Y' and
1 = 1))
and "CATALOGID1_o" = "CATALOGID"
and "PAGEID4_o" = "PAGEID";

I.e. it does use PRIMARY KEY to update

Is there any way to force Oracle Replication to use PRIMARY KEY to
perform delete?

OK

[Jared Hecker]

For starters, using a 'decode' in the join (the 'where' clause) negates
use of indexes.

> Sincerely,
> OK

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--
Jared Hecker | HWA Inc. - Oracle architecture and Administration
ja...@hwai.com | ** serving NYC and New Jersey **