materialized view via link slow after upgrade from 8i to 9i

I'm testing an upgrade of a database from version 8.1.7.4 <http://8.1.7.4>to version
9.2.0.4 <http://9.2.0.4>

Test db LEGS Oracle version 9.2.0.4 <http://9.2.0.4> just created
Prod db ARMS Oracle version 8.1.7.4 <http://8.1.7.4>
Prod db AMPROD Oracle version 9.2.0.4 <http://9.2.0.4>

Test (LEGS) is creating a materialized view via a link from the AMPROD database.
This mview is created in less than 2 minutes when run from the prod ARMS 8i database.
The same mview takes 40 minutes in the newly-upgraded test LEGS 9i database.

IDENTICAL MVIEW; only difference is running out of 8i database versus 9i database.

All 3 databases are on the same VMS node. (OpenVMS 7.3-1)

Google/Metalink searches like "materialized view slow upgrade" yielded no useful info.

The mview from the 8i database is gathering the data in a different manner. For example, v8i:

SELECT "ACCT_NBR", . . . more stuff
FROM "ADMARC"."NAD" "NAD" WHERE "ACCT_NBR"=:1 call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
Execute 516 0.30 0.26 0 0 0
Fetch 516 0.18 0.65 73 2066 516
------- ------ -------- ---------- ---------- ---------- ----------
total 1033 0.48 0.92 73 2066 516

v9i: (I don't know what that sys_alias_1 thing is)

SELECT "ACCT_NBR, . . . more stuff
FROM "ADMARC"."NAD" "SYS_ALIAS_1" call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- ------
Parse 0 0.00 0.00 0 0 0
Execute 0 0.00 0.00 0 0 0
Fetch 8914 31.31 74.21 44894 63033 463481
------- ------ -------- ---------- ---------- ------ ----------
total 8914 31.31 74.21 44894 63033 463481

version 8i is doing this:

SELECT "ACCT_KEY","END_ISS","ACT_DATE" FROM "ADMARC"."CNR" "CNR" WHERE "ACT_DATE">=TRUNC(:1-1) AND "END_ISS">=TRUNC(:2-1) call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
Execute 1 0.01 0.00 0 0 0
Fetch 1 13.04 24.60 17226 93473 547
------- ------ -------- ---------- ---------- ---------- ----------
total 3 13.05 24.61 17226 93473 547

version 9i is doing this:

SELECT "ACCT_NBR","ACT_DATE" FROM
"ADMARC"."NAD" "NAD" WHERE "ACCT_NBR"=:1 AND "ACT_DATE">=TRUNC(:2-1) call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0
Execute 463500 244.01 262.98 0 0 0
Fetch 463769 109.76 130.75 20767 1854059 269
------- ------ -------- ---------- ---------- ---------- ----------
total 927269 353.77 393.74 20767 1854059 269

9i is clearly choosing a different method, but I don't know why.

Here's the mview creation:

CREATE MATERIALIZED VIEW barb.MV_barb_CDT TABLESPACE ARMS_DATA LOGGING BUILD IMMEDIATE REFRESH FORCE ON DEMAND
AS
select * FROM admarc.nad_at_amp2
where acct_nbr in
( select acct_nbr
from admarc.nad_at_amp2
where act_date >= trunc(sysdate-1)
union select acct_key
from admarc.cnr_at_amp2
where act_date >= trunc(sysdate-1)
and end_iss >= trunc(sysdate-1) )

I will be grateful for any ideas.
Thanks so much!
Barb

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 04 2005 - 09:33:59 CST