Re: same query on similar databases differ by factor 30
Date: Tue, 22 Jul 2008 04:23:45 -0700 (PDT)
Message-ID: <c335313d-95bb-46b1-a687-2805fdd08698@f63g2000hsf.googlegroups.com>
On Jul 22, 4: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
You might try generating a 10046 trace at level 8 or 12 on both
databases, and manually compare the contents of the trace files. I
created a couple posts in a couple threads on Oracle's forums that
might be helpful.
Enabling a 10046 trace:
http://forums.oracle.com/forums/thread.jspa?messageID=2384639�
Reading the raw contents of a 10046 trace: http://forums.oracle.com/forums/thread.jspa?messageID=2549168�
If you are not looking for that level of detail, you might try sending the 10046 trace file through tkprof for a quick summary.
Compare the parameters used by the two database instances (query V
$PARAMETER), and also compare the statistics on the tables and indexes
(use DBMS_STATS to gather statistics with the CASCADE option set to
TRUE).
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Jul 22 2008 - 06:23:45 CDT