Re: Wierd Speed degradation in upgrade. 8.1.6 - 8.1.7

Mark Spritzler wrote:

> This might not be enough information for you guys to figure out my
> problem, but I figured to give it a shot.
>
> We had a consultant in our office for 3 weeks. We had him upgrade our
> 32 bit 8.1.6 Oracle database on HP-UX 11 to the 64 bit 8.1.7 version.
>
> He exported all of our data and data objects from the 8.1.6. He then
> installed 8.1.7 and imported everything in.
>
> Here is the problem. One of the materialized views refresh and
> creation has slowed 1000 fold. For instance when I used to have to
> drop the Materialized view and rerun the script file to recreate it,
> it would take less than a minute. Now it takes 50 minutes. So does a
> refresh.
>
> The script is as follows:
>
> CREATE MATERIALIZED VIEW mks08 NOLOGGING
> TABLESPACE DATA
> AS
> SELECT
> SUM(AR_OVER60) as AR_OVER60,
> SUM(AT_TOTAL) as AT_TOTAL,
> SUM(OVERBILL) as OVERBILL,
> SUM(UNDERBILL) as UNDERBILL,
> SUM(BILL_TM) as BILL_TM,
> SUM(SALES_YTD) as SALES_YTD,
> SUM(SALES_TM) as SALES_TM,
> SUM(BACKLOG_CUR) as BACKLOG_CUR,
> MONTHNUM,
> YEARNUM,
> MONTHDATE,
> REGION,
> REGION_NAME,
> SUM(COST_TOGO) as COST_TOGO,
> TRUNC(NODIV4(mcrt07.grp,mcrt07.monthyear),1) as PROJ_PER,
> TRUNC(NODIV5(mcrt07.grp,mcrt07.monthyear),1) as GP_YTD_PER,
> MONTHYEAR,
> SUM(CONT_ORIG) as CONT_ORIG,
> SUM(CONT_CUR) as CONT_CUR,
> SUM(GP_PROJ) as GP_PROJ,
> TRUNC(NODIV4(mcrt07.grp,mcrt07.monthyear)) as GP_PROJ_CUR_PER,
> SUM(GP_YTD) as GP_YTD,
> SUM(GP_TM) as GP_TM,
> SUM(GP_TOGO) as GP_TOGO,
> TRUNC(NODIV6(mcrt07.grp,mcrt07.monthyear),1) as GP_TOGO_PER,
> TRUNC(NODIV7(mcrt07.grp,mcrt07.monthyear)) as COST_TD_PER,
> SUM(COST_YTD) as COST_YTD,
> SUM(COST_TM) as COST_TM,
> SUM(VAR_OVER_JTD) as VAR_OVER_JTD,
> SUM(VAR_UNDER_JTD) as VAR_UNDER_JTD,
> SUM(VAR_OVER_YTD) as VAR_OVER_YTD,
> SUM(VAR_UNDER_YTD) as VAR_UNDER_YTD,
> SUM(VAR_OVER_TM) as VAR_OVER_TM,
> SUM(VAR_UNDER_TM) as VAR_UNDER_TM,
> SUM(CONT_ADJ) as CONT_ADJ,
> SUM(COST_ADJ) as COST_ADJ,
> SUM(CASH_POS) as CASH_POS,
> TRUNC(NODIV(mcrt07.grp,mcrt07.monthyear)) as cont_per,
> TRUNC(NODIV2(mcrt07.grp,mcrt07.monthyear)) as co_per,
> TRUNC(NODIV3(mcrt07.grp,mcrt07.monthyear)) as total_per,
> SUM(PROCEEDS) as PROCEEDS,
> GRP,
> GRP_TITLE,
> PRESIDENT
> FROM MCRT07
> GROUP BY grp,grp_title,president,region,region_name,monthyear,monthdate,yearnum,monthnum
>
> The NODIV(s) are stored procedures that we created.
> I have created indexes for all the group by's.
>
> We have compiled all the stored procedures, and analyzed the
> underlying table(s). But still none of this has helped. I can't
> imagine what is wrong. Now I am not an expert, or even intermediate. I
> am a beginner. So even beginner like suggestions are wanted, such as
> "Do you have data in your tables?".
>
> Speaking of which the mcrt07 table has a total of 1358 records, and
> the materialized view would have 710 records. the record size might be
> 255 bytes at most. and mostly numbers.
>
> Thank you in advance for your help
>
> Mark

Do you still have the access paths from the previous version ? If its changed, then that could explain your problem. Certainly with PL/SQL in there, it may be the case that previously the PL/SQL execution was deferred until after grouping, and the new one is doing it on the detail row (check recursive counts)

hth
connor

--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."