same query on similar databases differ by factor 30
Date: Tue, 22 Jul 2008 10:15:13 +0200
sorry for the subject, it is a difficult to write it in two words.
<oracle 220.127.116.11/18.104.22.168; 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
-- wenn email, dann AndreasMosmann <bei> web <punkt> deReceived on Tue Jul 22 2008 - 03:15:13 CDT