Re: same query on similar databases differ by factor 30

On Jul 22, 6:23 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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%ef¿½
>
> Reading the raw contents of a 10046 trace:
>  http://forums.oracle.com/forums/thread.jspa?messageID=2549168%ef¿½
>
> 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.- Hide quoted text -
>
> - Show quoted text -

You might also set event 10053 at level 1 on both databases, run the same query in each and see exactly the decision tree each optimizer is using. You may find your answer.

David Fitzjarrell Received on Tue Jul 22 2008 - 07:28:05 CDT