Re: same query on similar databases differ by factor 30

On Jul 22, 7: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 -

The links are corrupted by the ending garbage character. Perhaps these will work better.

http://forums.oracle.com/forums/thread.jspa?messageID=2384639

http://forums.oracle.com/forums/thread.jspa?messageID=2549168

HTH -- Mark D Powell -- Received on Tue Jul 22 2008 - 08:59:15 CDT