Re: Upgrade from 8.1.6 to 8.1.7 really slow

Pete Sharman <peter.sharman_at_oracle.com> wrote in message news:<abm9d002aru_at_drn.newsguy.com>...
> In article <abjrgl$ckq$1_at_knossos.btinternet.com>, "Craig says...
> >
> >Cheers
> >
> >I copied the init.ora from the live sys to the dev sys. also the db create
> >scripts used were the same. I have also used an identical FS layout although
> >on 8.1.7 i have 2 FS per disk and on the live sys all FS are on a RAID array
> >(Level 5)
> >
> >At the mo I am running 8.1.7.0.0 but memory does not seem to be an issue,
> >
>
> OK couple more things to check:
>
> 1. Are all the indexes the same as the 8.1.6 system? Remember the RBO will use
> an index before a full table scan, so if you're missing an index you may be
> swapping to full table scans instead. I believe (though I've never used it so I
> could be wrong) that the Change Management Pack in OEM has a schema comparison
> sort of tool that you can use to compare the two databases for missing objects.
>
> 2. Are all the indexes valid? Check for invalid status in DBA_OBJECTS.
>
> 3. Run an EXPLAIN PLAN on the offending (or should that be offensive?)
> statements and see if there are any differences.
>
> >
> >Cheers
> >"Pete Sharman" <peter.sharman_at_oracle.com> wrote in message
> >news:abjc1a02e24_at_drn.newsguy.com...
> >> In article <abiokd$823$1_at_paris.btinternet.com>, "Craig says...
> >> >
> >> >Hi all.
> >> >
> >> >I have just installed 8.1.7 64bit on our new SUN Fire V880 running
> Solaris
> >> >8. I have exported a db from our live 8.1.6 system and re-imported it
> into
> >> >the 8.1.7 and batchjobs that took around 5 miinutes now take hours.
> >> >
> >> >I have rebuilt all the indexes and analyzed the schema's (although we
> force
> >> >rule based optimisation)
> >> >
> >> >anyone seen this before ?
> >> >
> >> >--
> >> >Craig Ballingall
> >> >
> >> >
> >>
> >> Not really enough information here to help you, but here's a couple of
> >> suggestions:
> >>
> >> 1. Compare the init.ora files for the databases. Are the parameters
> >> equivalent?
> >> 2. Check the I/O spread for the datafiles. Do you have a roughly similar
> I/O
> >> spread?
> >> 3. Probably not enough to cause changes of this magnitude, but be aware
> that
> >> there are changes in the optimizer between 8.1.6 and 8.1.7 (can't remember
> >> exactly what they are now, but I have seen other clients run into minor
> >> performance issues until they understood how to take advantage of the
> >> differences in the way the optimizer works in 8.1.7).
> >> 4. What exact version are you on? From memory, there were some memory
> leaks in
> >> 8.1.7.0 (not that you would see problems like this for that reason, but
> it's
> >> probably better to be on 8.1.7.3)
> >>
> >> HTH. Additions and corrections welcome.
> >>
> >> Pete
> >>
> >> SELECT standard_disclaimer, witty_remark FROM company_requirements;
> >>
> >
> >
>
> HTH. Additions and corrections welcome.
>
> Pete
>
> SELECT standard_disclaimer, witty_remark FROM company_requirements;

Craig, you mentioned the 8.1.7 version is 64 bit. If the previous version was not also 64 bit then you should have increased the size of the shared pool to compensate for the additional overhead of all the internal pointers now requiring 8 bytes instead of 4. Generally 64 bit requires a 20 - 25% larger shared pool.

Also you said that you force Rule based optimization; however, are you sure that CBO is not in use for some of your SQL. Remember that since 7.3 the presence of any hint, except RULE, caused the CBO to be invoked even if no statistics exist on the underlying objects. And how were the statistics created? I have not personally verified this but I have read that the statistics created by dbms_stats cannot be replaced by an analyze, but either have to be deleted first or replaced by a new run of dbms_stats.

HTH -- Mark D Powell -- Received on Mon May 13 2002 - 08:40:09 CDT