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

From: Terry Sutton <terrysutton_at_usa.net>
Date: Fri, 4 Nov 2005 10:58:47 -0800
Message-ID: <006f01c5e171$da470a50$61f5a8c0@TerrySutton>

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_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 - 13:01:49 CST