Re: Comparisons Oracle 18.104.22.168 to Oracle 10.2.0.4
Date: Thu, 22 Oct 2009 07:02:49 -0700 (PDT)
On Oct 22, 8:45 am, "Arne Ortlinghaus" <Arne.Ortlingh..._at_acs.it> 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 22.214.171.124 to 10.2.0.2. The test with the same SQL statement was repeated on Oracle 10.2.0.4 and 126.96.36.199 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 188.8.131.52 STATISTICS_LEVEL=TYPICAL 5:35.58 184.108.40.206 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 220.127.116.11 STATISTICS_LEVEL=TYPICAL (hinted access path) 16.73 18.104.22.168 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 22.214.171.124 STATISTICS_LEVEL=TYPICAL (hinted,no phy read) 0.23 126.96.36.199 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 188.8.131.52.
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 184.108.40.206.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Oct 22 2009 - 09:02:49 CDT