Re: Comparisons Oracle 11.1.0.7 to Oracle 10.2.0.4

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 22 Oct 2009 07:02:49 -0700 (PDT)
Message-ID: <2712082e-c727-4f50-8882-3decd5192fbf_at_b18g2000vbl.googlegroups.com>

On Oct 22, 8:45 am, "Arne Ortlinghaus" <Arne.Ortlingh..._at_acs.it> wrote:
(snip)
> 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. Received on Thu Oct 22 2009 - 09:02:49 CDT