 | |
same query on similar databases differ by factor 30
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: same query on similar databases differ by factor 30 -
07-22-2008
, 08:59 AM
|
On Jul 22, 4:15Â*am, Andreas Mosmann <mosm... (AT) expires-31-07-2008 (DOT) 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/thre...eID=2384639� Reading the raw contents of a 10046 trace: Â*http://forums.oracle.com/forums/thre...eID=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 - |
these will work better.
http://forums.oracle.com/forums/thre...sageID=2384639
http://forums.oracle.com/forums/thre...sageID=2549168
HTH -- Mark D Powell --
#12
| |||
| |||
|
|
On Jul 22, 4:15Â*am, Andreas Mosmann <mosm... (AT) expires-31-07-2008 (DOT) 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/thre...eID=2384639� Reading the raw contents of a 10046 trace: Â*http://forums.oracle.com/forums/thre...eID=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 - |
these will work better.
http://forums.oracle.com/forums/thre...sageID=2384639
http://forums.oracle.com/forums/thre...sageID=2549168
HTH -- Mark D Powell --
#13
| |||
| |||
|
|
On Jul 22, 4:15Â*am, Andreas Mosmann <mosm... (AT) expires-31-07-2008 (DOT) 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/thre...eID=2384639� Reading the raw contents of a 10046 trace: Â*http://forums.oracle.com/forums/thre...eID=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 - |
these will work better.
http://forums.oracle.com/forums/thre...sageID=2384639
http://forums.oracle.com/forums/thre...sageID=2549168
HTH -- Mark D Powell --
#14
| |||
| |||
|
|
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 |
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
#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 |
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
#16
| |||
| |||
|
|
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 |
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
#17
| |||
| |||
|
|
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 |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.