Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)

From: John Hurley <johnbhurley_at_sbcglobal.net>
Date: Sat, 5 Sep 2009 15:23:47 -0700 (PDT)
Message-ID: <95c05492-0653-4af1-8881-77e4b46c584a_at_r9g2000yqa.googlegroups.com>



On Sep 4, 9:04 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:

snip

> I am working on an Oracle performance related project, so I thought
> that I would compare the performance of Oracle database 11.1.0.6 (on
> 64 bit Linux), 11.1.0.7 (on 64 bit Windows), and 11.2.0.1 (on 64 bit
> Linux).  I set up a test case with a table containing 100,000,000 rows
> having an average row length of 53 bytes.  A normal B*tree index
> exists on a numeric column which contains numbers ranging from 0 to
> 10,000.  A test query will be selecting 2,547,158 (2.55%) of the rows
> from the table.  Leaving OPTIMIZER_INDEX_COST_ADJ at the default of
> 100 with NOWORKLOAD system statistics results in a full table in all
> three releases of Oracle.  Setting OPTIMIZER_INDEX_COST_ADJ to 5, for
> instance, results in an index access path.  So, the question is:
> should the OPTIMIZER_INDEX_COST_ADJ parameter be set to the lower
> number to (quoting from a posting on the Internet) “immediately tune
> all of the SQL in your database to favor index scans over full-table
> scans”?  I am certain that I know the answer based on extensive
> testing, but what are your thoughts?

Hey Charles I lost a little of what your investigations found when trying to digest your posting here ( it has been a long week so probably my concentration is not up to task ).

It seems like at least several of ( possibly many ) oracle experts have de-emphasized making manual adjustments to some of the OPTIMIZER_* parameters now that system statistics are available. ( Not quite sure where Tom Kyte stands here or Jonathan Lewis for that matter ). Any ideas on what makes the most sense based on what you have discovered so far?

Is your test case a general argument that people should still be making manual adjustments to some of these settings even if running 11.2?

Is it a specific example that based on specific conditions shows 11.2 is better or worse than other releases when making certain manaul adjustments?

Is there a difference shown between NOWORKLOAD system statistics and "real workload" statistics under 11.2 for your test case? Received on Sat Sep 05 2009 - 17:23:47 CDT

Original text of this message