| Computers Forum Index » Computer - Databases - Oracle (Server) » Comparisons Oracle 11.1.0.7 to Oracle 10.2.0.4... |
|
Page 1 of 1 |
|
| Author |
Message |
| Arne Ortlinghaus... |
 Posted: Thu Oct 22, 2009 1:37 pm |
|
|
|
Guest
|
I have made some performance comparison tests on test computers and it has
turned out that the Oracle 11 databases are in general slower compared to
the Oracle 10 databases by 10 to 30%. The databases are running on Windows
Vista and Windows 2003 64 Bit. We hope that the release 2 of Oracle 11 gives
some enhancements because in general there are not many advantages regarding
new features useable for us.
Has someone made similar comparison results?
Arne Ortlinghaus
ACS Data Systems |
|
|
| Back to top |
|
|
|
| Charles Hooper... |
| Posted: Thu Oct 22, 2009 1:37 pm |
|
|
|
Guest
|
On Oct 22, 5:37 am, "Arne Ortlinghaus" <Arne.Ortlingh... at (no spam) acs.it>
wrote:
Quote: I have made some performance comparison tests on test computers and it has
turned out that the Oracle 11 databases are in general slower compared to
the Oracle 10 databases by 10 to 30%. The databases are running on Windows
Vista and Windows 2003 64 Bit. We hope that the release 2 of Oracle 11 gives
some enhancements because in general there are not many advantages regarding
new features useable for us.
Has someone made similar comparison results?
Arne Ortlinghaus
ACS Data Systems
I have tested the performance of 10.2.0.4 and 11.1.0.7 on 64 bit
Windows. If I recall the test results correctly, you will find that
with STATISTICS_LEVEL set to ALL you will see that 11.1.0.7 is
significantly faster than 10.2.0.4 in all but the most simple SQL
statements - in such a case 10.2.0.4 might consume a lot of system
(kernel) mode CPU time, while 11.1.0.7 consumes considerably less.
While testing 10.2.0.4 and 11.1.0.7, I found that 10.2.0.4 was
typically slightly faster than 11.1.0.7 when the exact same execution
plan was obtained for a SQL statement and the STATISTICS_LEVEL was set
to TYPICAL. 11.1.0.7 occasionally found a different execution plan
than 10.2.0.4, partially influenced by the presence (or absence) of
system WORKLOAD statistics.
If you would, please determine if the execution plans are identical
between the two Oracle releases. You might also check the system
statistics in SYS.AUX_STATS$, and the various initialization
parameters to make certain you are comparing the two Oracle releases
with the same inputs. The location of the data files for the two
releases could also influence performance - more data passes under the
read-write heads per revolution on the outer tracks of the hard disks,
so that could yield better performance for the Oracle release with
data files located closer to the outer hard disk tracks.
What do you find is slower? Are you checking the execution time of a
process with a stop watch, checking the wait events, monitoring the
CPU utilization, creating a Statspack Report, or something else?
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. |
|
|
| Back to top |
|
|
|
| hpuxrac... |
| Posted: Thu Oct 22, 2009 1:37 pm |
|
|
|
Guest
|
On Oct 22, 5:37 am, "Arne Ortlinghaus" <Arne.Ortlingh... at (no spam) acs.it>
wrote:
snip
Quote: I have made some performance comparison tests on test computers and it has
turned out that the Oracle 11 databases are in general slower compared to
the Oracle 10 databases by 10 to 30%. The databases are running on Windows
Vista and Windows 2003 64 Bit. We hope that the release 2 of Oracle 11 gives
some enhancements because in general there are not many advantages regarding
new features useable for us.
Has someone made similar comparison results?
That has not been the case for us but we are running on Linux 64 bit
systems.
When you say "on test computers" that makes me nervous. One has to be
very careful to get identical environments. One has to look very
carefully at oracle settings between the environments. |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Thu Oct 22, 2009 1:37 pm |
|
|
|
Guest
|
On Thu, 22 Oct 2009 11:37:25 +0200, Arne Ortlinghaus wrote:
Quote: I have made some performance comparison tests on test computers and it
has turned out that the Oracle 11 databases are in general slower
compared to the Oracle 10 databases by 10 to 30%. The databases are
running on Windows Vista and Windows 2003 64 Bit. We hope that the
release 2 of Oracle 11 gives some enhancements because in general there
are not many advantages regarding new features useable for us.
Has someone made similar comparison results?
Arne Ortlinghaus
ACS Data Systems
This is meaningless unless you explain the exact meaning of the word
"test". I still remember the fuss around the so called "Marketing
Invention for Pushing Sales" or MIPS for short. It was a number,
determined by running something called "Dhrystone test". It turned out
that computer manufacturers were even putting special options into the
compilers, to detect if they were compiling the Dhrystone test. As a
result, the number produced was quite meaningless and did not have
anything to do with the actual performance.
The statement that 11.1.0.7 is slower than 10.2.0.4 doesn't mean anything
unless you tell us what exactly is slow? Oracle 11g has different memory
management, it does full table scans differently, sometimes using direct
reads into PGA, and has significantly improved optimizer. I dislike the
new memory management and moving memory from SGA to PGA and back. I don't
have any numbers to substantiate my disliking it, but I think that the
two kinds of memory should be kept separately as they're being used in
completely different ways.
Did you optimize the SQL in question on both 11g and 10g? Did you run the
10046 trace to see what is it that the process is waiting on and
addressed the gripe?
--
http://mgogala.freehostia.com |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Thu Oct 22, 2009 1:37 pm |
|
|
|
Guest
|
On Thu, 22 Oct 2009 14:45:08 +0200, Arne Ortlinghaus wrote:
Quote: I will try STATISTICS_LEVEL set to ALL and will tell you the results.
Oh my god, you're playing a guessing game!
--
http://mgogala.freehostia.com |
|
|
| Back to top |
|
|
|
| Charles Hooper... |
| Posted: Thu Oct 22, 2009 2:02 pm |
|
|
|
Guest
|
On Oct 22, 8:45 am, "Arne Ortlinghaus" <Arne.Ortlingh... at (no spam) acs.it>
wrote:
(snip)
Quote: I will try STATISTICS_LEVEL set to ALL and will tell you the results.
Arne Ortlinghaus
ACS Data Systems
Just for the record, I only mentioned STATISTICS_LEVEL as a
demonstration of how a parameter could affect the execution time.
Setting the parameter to ALL at the SYSTEM level is not recommended.
I found one of my test results which is specific to a SQL statement
that had performance problems when I migrated the databases from
Oracle 8.1.7.3 to 10.2.0.2. The test with the same SQL statement was
repeated on Oracle 10.2.0.4 and 11.1.0.7 on a different box running 64
bit Windows. A brief summary of the results follows:
10.2.0.4 STATISTICS_LEVEL=TYPICAL 6:56.73
10.2.0.4 STATISTICS_LEVEL=ALL 1:06:44.31
11.1.0.7 STATISTICS_LEVEL=TYPICAL 5:35.58
11.1.0.7 STATISTICS_LEVEL=ALL 7:34.21
10.2.0.4 STATISTICS_LEVEL=TYPICAL (hinted access path) 14.39
10.2.0.4 STATISTICS_LEVEL=ALL (hinted access path) 16.36
11.1.0.7 STATISTICS_LEVEL=TYPICAL (hinted access path) 16.73
11.1.0.7 STATISTICS_LEVEL=ALL (hinted access path) 17.11
10.2.0.4 STATISTICS_LEVEL=TYPICAL (hinted,no phy read) 0.28
10.2.0.4 STATISTICS_LEVEL=ALL (hinted,no phy read) 2.65
11.1.0.7 STATISTICS_LEVEL=TYPICAL (hinted,no phy read) 0.23
11.1.0.7 STATISTICS_LEVEL=ALL (hinted,no phy read) 0.29
Depending on the environment, the above shows a performance spread of
0.28 seconds to 1 hour 6 minutes 44.31 seconds for Oracle 10.2.0.4.
Depending on the environment, the above shows a performance spread of
0.23 seconds to 0 hours 7 minutes 34.21 seconds for Oracle 11.1.0.7.
Note that the above is for a single SQL statement on the same server
with the same initialization parameters in both Oracle releases. The
execution plan and wait events, however, differed for 10.2.0.4 and
11.1.0.7.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. |
|
|
| Back to top |
|
|
|
| Arne Ortlinghaus... |
| Posted: Thu Oct 22, 2009 4:45 pm |
|
|
|
Guest
|
John and Charles,
thank you for your answers.
Testcomputers mean in this case that I installed on each of these computers
two databases with identical data. Then I made performance tests against
both databases one or multiple times. For the performance tests I used
evaluations on own data and I took "Swingbench" from Dominic Giles using the
"Order entry benchmark". Then without looking at the exact statement
executions I compared the overall times. Of course there are so many
possible parameters that the results may depend from the type we use the
databases. So it is interesting to hear your results.
I will try STATISTICS_LEVEL set to ALL and will tell you the results.
Arne Ortlinghaus
ACS Data Systems |
|
|
| Back to top |
|
|
|
| joel garry... |
| Posted: Thu Oct 22, 2009 5:55 pm |
|
|
|
Guest
|
|
| Back to top |
|
|
|
| Arne Ortlinghaus... |
| Posted: Fri Oct 23, 2009 10:34 am |
|
|
|
Guest
|
Mladen,
yes, you are right. It is true that with the tests I make I can see only few
aspects. Since I try to use the new initialization parameters if possible it
is also important to get some feedback.
For example until Oracle 10 we needed the two parameters
optimizer_index_cost_adj = 10
optimizer_index_caching = 99
in our database to have good performance. Until now the database
underestimated always the benefits of indexes. In my tests until now it
seems that Oracle 11 is predicting better without explicitly setting these
parameters. But I have to make some further tests to be sure about that.
The results Charles has given in the email above are also interesting. In my
eyes they show that there are no disadvantages moving to Oracle 11.
Our databases are a mixed type OLTP and Datawarehouse. Since we started with
Oracle 8 we are used to try to simplify statements and to add indexes where
possible. Surely every new version of Oracle gives new capabilities in
executing complex queries better but possibly needs more overhead in
processing simple statements. We will move to Oracle 11, but it depends a
little bit on the actual results of our (subjective) tests when we will do
this: in the following months or may be in half a year. Our problem is that
with the same applications we have many small installations (one or two
workplaces), where the database is on the pc of the user. We have many
installations with 5 to 10 workplaces, and few important installations with
50 workplaces or more. The smallest databases have few GB of data, the
biggest are still below 1 Tera Byte of data and all of them should work with
the same set of tables/indexes without optimizing each database differently.
Arne Ortlinghaus |
|
|
| Back to top |
|
|
|
| Arne Ortlinghaus... |
| Posted: Fri Oct 23, 2009 10:40 am |
|
|
|
Guest
|
Interesting,
so it seems that we can expect new possibilities in release 2?
Arne Ortlinghaus
"joel garry" <joel-garry at (no spam) home.com> schrieb im Newsbeitrag
news:f627ce99-0452-4db5-8bca-170fc1932f60 at (no spam) w37g2000prg.googlegroups.com...
On Oct 22, 2:37 am, "Arne Ortlinghaus" <Arne.Ortlingh... at (no spam) acs.it>
wrote:
Quote: I have made some performance comparison tests on test computers and it has
turned out that the Oracle 11 databases are in general slower compared to
the Oracle 10 databases by 10 to 30%. The databases are running on Windows
Vista and Windows 2003 64 Bit. We hope that the release 2 of Oracle 11
gives
some enhancements because in general there are not many advantages
regarding
new features useable for us.
Has someone made similar comparison results?
Arne Ortlinghaus
ACS Data Systems
See
http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_sql_plan_management_11gr2.pdf
jg
--
at (no spam) home.com is bogus.
http://www3.signonsandiego.com/stories/2009/oct/17/hedge-fund-billionaire-indicted-stock-fraud/?uniontrib |
|
|
| Back to top |
|
|
|
| Randolf Geist... |
| Posted: Fri Oct 23, 2009 2:16 pm |
|
|
|
Guest
|
On Oct 23, 8:34 am, "Arne Ortlinghaus" <Arne.Ortlingh... at (no spam) acs.it>
wrote:
Quote: For example until Oracle 10 we needed the two parameters
optimizer_index_cost_adj = 10
optimizer_index_caching = 99
in our database to have good performance. Until now the database
underestimated always the benefits of indexes. In my tests until now it
seems that Oracle 11 is predicting better without explicitly setting these
parameters. But I have to make some further tests to be sure about that.
What kind of System Statistics have you been using since Oracle 9i?
Instead of using optimizer_index_cost_adj (OICA) you should have a
look at System Statistics, which are enabled by default from Oracle
10g on, although their NOWORKLOAD defaults might not be appropriate
for your specific environment.
The optimizer_index_caching (OIC) is set to an extreme value, this
shouldn't be required under normal circumstances, in particular
together with OICA (which will lower the cost of certain operations
even more). You basically should see mostly NESTED LOOP joins with
index access. The question is then why Oracle with default settings
doesn't favor these access paths automatically.
Can you provide an example of an execution plan with default settings
and above custom settings that requires the settings to get the
desired "good" performance?
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
| Back to top |
|
|
|
| joel garry... |
| Posted: Fri Oct 23, 2009 4:33 pm |
|
|
|
Guest
|
|
| Back to top |
|
|
|
| Arne Ortlinghaus... |
| Posted: Fri Oct 23, 2009 8:12 pm |
|
|
|
Guest
|
Yes, it is true, there are normally nested loops in these execution plans.
But nevertheless the results are coming faster then without. And this
happens in many cases. I will furnish some execution plans next week.
The system statistics should be the standard ones. Attached I have one
initora for Oracle 10 (InitA10...) and one for Oracle 11 (InitA11...) as we
use it typically for the creation of databases only with the difference that
the sga_target is much greater.
Arne Ortlinghaus
ACS Data System
"Randolf Geist" <mahrah at (no spam) web.de> schrieb im Newsbeitrag
news:0d5f7479-b657-42a1-9a55-f9bab2c2849c at (no spam) r31g2000vbi.googlegroups.com...
On Oct 23, 8:34 am, "Arne Ortlinghaus" <Arne.Ortlingh... at (no spam) acs.it>
wrote:
Quote: For example until Oracle 10 we needed the two parameters
optimizer_index_cost_adj = 10
optimizer_index_caching = 99
in our database to have good performance. Until now the database
underestimated always the benefits of indexes. In my tests until now it
seems that Oracle 11 is predicting better without explicitly setting these
parameters. But I have to make some further tests to be sure about that.
What kind of System Statistics have you been using since Oracle 9i?
Instead of using optimizer_index_cost_adj (OICA) you should have a
look at System Statistics, which are enabled by default from Oracle
10g on, although their NOWORKLOAD defaults might not be appropriate
for your specific environment.
The optimizer_index_caching (OIC) is set to an extreme value, this
shouldn't be required under normal circumstances, in particular
together with OICA (which will lower the cost of certain operations
even more). You basically should see mostly NESTED LOOP joins with
index access. The question is then why Oracle with default settings
doesn't favor these access paths automatically.
Can you provide an example of an execution plan with default settings
and above custom settings that requires the settings to get the
desired "good" performance?
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
| Back to top |
|
|
|
|
