Re: Performance issue

In article <3A77B3CE.21BAB04D_at_adelaide.edu.au>,   Steve Salvemini <steve.salvemini_at_adelaide.edu.au> wrote:
> We have a oracle database setup on a development platform and one on
 the
> pre-production platform, both of similar spec, but the pre-prod
 database
> is very slow. The cpu maxes out when queries are sent through to it.
 The
> link is a 100mb link from the client, so network speed is not expected
> to be the problem.
>
> The development db is probably slightly bigger in size.
>
> This is a very broad question I know, but perhaps there are several
 key
> things for me to check, ie
>
> on the apps side:
> - check the optimisation mode choose/rule
> - depending on mode, check indexes/statistics
> - test the qep in both databases for sample sql's to check results are
> the same on both the client and the server
> - check versions of oracle are the same and similarly patched
> - check operating system versions are the same and similarly patched
> - check settings in the v$parameter between the 2 databases
>
> on the server side:
> pretty limited in my knowledge here...
> - compare contents of oracle.ini
> - any ideas ???
>
> Thanks for any ideas
>

I encountered a possible similar problem. When initially loading the production system, it was size for the final volume. Only about 10% of the data was installed. Performance was extremely slow.

It turns out ( there are cryptic note in the Oracle press tuning book) that having more than 20% extra storage will cause problems. The tables should be sized within this 20%. We resized the tables and things returned to normal.

A second possible is that you have not run analysis on the tables. Again while loading data, because of the sequence, Oracle thought that the data was different than it actually was. For example, one field had only 1 values for the initial dataset. Oracle decided the index using this field was unnecessary. Performance was slow (no index). When more data and an analysis was done, Oracle again started to use the index.

How did I work through these problems? Basically selected a couple of queries that were running slow on the new system and worked at making them better. When they were better, all the learned techniques were applied to the remaining queries. Generally very tedious work.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/