Re: Comparisons Oracle 11.1.0.7 to Oracle 10.2.0.4

From: Randolf Geist <mahrah_at_web.de>
Date: Fri, 23 Oct 2009 07:16:58 -0700 (PDT)
Message-ID: <0d5f7479-b657-42a1-9a55-f9bab2c2849c_at_r31g2000vbi.googlegroups.com>

On Oct 23, 8:34 am, "Arne Ortlinghaus" <Arne.Ortlingh..._at_acs.it> wrote:
> 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/ Received on Fri Oct 23 2009 - 09:16:58 CDT