Re: same query on similar databases differ by factor 30

From: joel garry <>
Date: Tue, 22 Jul 2008 11:18:18 -0700 (PDT)
Message-ID: <>

On Jul 22, 1:15 am, Andreas Mosmann <> wrote:
> hi ng,
> sorry for the subject, it is a difficult to write it in two words.
> <oracle; 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
> 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?


-- is bogus.
That darn gmail...
Received on Tue Jul 22 2008 - 13:18:18 CDT

Original text of this message