Re: Comparisons Oracle to Oracle

From: Charles Hooper <>
Date: Thu, 22 Oct 2009 07:02:49 -0700 (PDT)
Message-ID: <>

On Oct 22, 8:45 am, "Arne Ortlinghaus" <> wrote:
> 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 to The test with the same SQL statement was repeated on Oracle and on a different box running 64 bit Windows. A brief summary of the results follows: STATISTICS_LEVEL=TYPICAL                      6:56.73 STATISTICS_LEVEL=ALL                       1:06:44.31 STATISTICS_LEVEL=TYPICAL                      5:35.58 STATISTICS_LEVEL=ALL                          7:34.21 STATISTICS_LEVEL=TYPICAL (hinted access path)   14.39 STATISTICS_LEVEL=ALL     (hinted access path)   16.36 STATISTICS_LEVEL=TYPICAL (hinted access path)   16.73 STATISTICS_LEVEL=ALL     (hinted access path)   17.11 STATISTICS_LEVEL=TYPICAL (hinted,no phy read)    0.28 STATISTICS_LEVEL=ALL     (hinted,no phy read)    2.65 STATISTICS_LEVEL=TYPICAL (hinted,no phy read)    0.23 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 Depending on the environment, the above shows a performance spread of 0.23 seconds to 0 hours 7 minutes 34.21 seconds for Oracle

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 and

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Oct 22 2009 - 09:02:49 CDT

Original text of this message