same query on similar databases differ by factor 30
in [Oracle Help]
|
Prev: hot sexy puictures
Next: NLS_LANG
From: Andreas Mosmann on 22 Jul 2008 04:15 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
From: Charles Hooper on 22 Jul 2008 07:23 On Jul 22, 4:15 am, Andreas Mosmann <mosm...(a)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.
From: fitzjarrell on 22 Jul 2008 08:28 On Jul 22, 6:23 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > On Jul 22, 4:15 am, Andreas Mosmann <mosm...(a)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.- 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
From: Mark D Powell on 22 Jul 2008 09:59 On Jul 22, 7:23 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > On Jul 22, 4:15 am, Andreas Mosmann <mosm...(a)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.- 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 --
From: joel garry on 22 Jul 2008 14:18 On Jul 22, 1:15 am, Andreas Mosmann <mosm...(a)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 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? jg -- @home.com is bogus. That darn gmail... http://catless.ncl.ac.uk/Risks/25.23.html#subj6
|
Pages: 1 Prev: hot sexy puictures Next: NLS_LANG |