Re: same query on similar databases differ by factor 30

On Jul 22, 1:15 am, Andreas Mosmann <mosm..._at_expires-31-07-2008.news- group.org> wrote:
> hi ng,
> sorry for the subject, it is a difficult to write it in two words.
>
> <oracle 9.2.0.7/9.2.0.8; cost based optimizer; VMWare ESX- server; MS
> Windows>
>
> we write software for a database and for this we copy the hot database
> and use this as a testdatabase. The typical way is to build an empty
> database, to create the users and then to export/import all schemas.
>
> That way we followed all the time and in this special situation there
> are a few weeks between both versions. Of course we changed some
> database objects as triggers, tables, indexes ...
>
> Now we wanted to roll out the new db- version and therefor we copied the
> hot db and built a new one from this and put all modifications to this.
> the problem is the following:
>
> A simple query as
> SELECT <maybe a optimizer int> X
> FROM A
> JOIN B ..
> JOIN C ..
> JOIN D ..
> .
> WHERE F.A=4711
>
> takes in our actual test- DB about 0.5 to 2 seconds and in the new DB
> about 65 seconds. If I create new indexes this time jumpes up and down,
> at least 5 seconds, 30 seconds, 90 seconds ...
> I got it to give it the same execution plan as in the original test db
> and in this case this query takes 65 seconds.
> of course in both tables are actual staistics avilable.
> In all combined tables the data for the query differ less than 5%, there
> are 5 tables and a materialized view (twice) involved.
> As far as I understand the optimizer uses the correct driving table and
> index.
>
> I know that it is impossible to find this problem from outside, but can
> anyone give me hints what parts of database is to compare (which DB-
> objects, parameters ...)
> Both VMs run on the same server, I could easy compare, but WHAT can make
> THAT difference?
> btw: the CBO of the slow database tells that it spends much time on
> nested loops
>
> Hope anyone can help
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

Just speculating way outside the box - could your VM be driven to swapping when you access the second db? Can you try shutting down the fast one and only run the slow one?

jg

--
@home.com is bogus.
That darn gmail... http://catless.ncl.ac.uk/Risks/25.23.html#subj6