Re: materialized view via link slow after upgrade from 8i to 9i
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Nov 2005 10:58:47 -0800
Barbara,
I can't tell you the specific action to take to fix the process, but it's root
cause is probably because of the different default values in 9i vs. 8i for many
underscore parameters related to the optimizer. These particularly affect
subqueries. Take a look in Metalink for terms like "unnest".
--Terry
I'm testing an upgrade of a database from version 8.1.7.4 to version 9.2.0.4
Test db LEGS Oracle version 9.2.0.4 just created
Prod db ARMS Oracle version 8.1.7.4
Prod db AMPROD Oracle version 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@amp2
where acct_nbr in
( select acct_nbr
from admarc.nad@amp2
where act_date >= trunc(sysdate-1)
union select acct_key
from admarc.cnr@amp2
where act_date >= trunc(sysdate-1)
and end_iss >= trunc(sysdate-1) )
I will be grateful for any ideas.
Thanks so much!
Barb
Other related posts:
