Re: Surprising Performance Changes with Oracle (Long Post)

From: Charles Hooper <>
Date: Sat, 5 Sep 2009 19:19:44 -0700 (PDT)
Message-ID: <>

On Sep 5, 6:23 pm, John Hurley <> wrote:
> 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?

I will have to defer the question to someone else.

Too many recommendations may be found through Internet searches and book searches suggesting to blindly set OPTIMIZER_INDEX_COST_ADJ to a low number such as 10, 5, or even 1 without giving any consideration to what just might happen with certain types of data. A nice 30 second report might become a nice 80 minute report, for example. (This test case surprised me too.)

I used NOWORKLOAD statistics in this case to improve the chances that if someone else ran the test case, they would also see a full tablescan when OPTIMIZER_INDEX_COST_ADJ was set to 100 and an index range scan when the parameter was set to 5. Incidentally I also ran the SQL statement on and on 64 bit Windows with a WHERE clause of "ID BETWEEN 1 AND 10" which selected 0.06% of the table (I think that I also ran this test on Linux with and but I have not reviewed those test runs). Oracle which had CPU statistics collected still deciided to select a full tablescan with OPTIMIZER_INDEX_COST_ADJ set to 100, while with NOWORKLOAD statistics selected to perform an index range scan regardless of the value of OPTIMIZER_INDEX_COST_ADJ. Oracle had auto-tuned the MBRC value in SYS.AUX_STATS$ to 47 - changing it to the NOWORKLOAD default of 8 resulted in an index range scan regardless of the value of OPTIMIZER_INDEX_COST_ADJ with the modified WHERE clause. One would think that when selecting 0.06% of a table, a full table scan of a table containing more than 5GB of data would be a bad idea. The results? 34.61 seconds (with 813,876 consistent gets, 813,234 physical block reads) for the full tablescan, while the plan using the index access required 2 minutes and 40.16 seconds (with 64,425 consistent gets and 63,789 physical block reads). One of those plans will severely hurt the BCHR... but that is a another topic for the chapter.

In one of the production databases I had an issue where I attempted to leave OPTIMIZER_INDEX_COST_ADJ set at 100 while relying on the collected CPU statistics (OK, I actually set MBRC to 128, up from the calculated value of 8 based on the an 1MB multi-block read size which should be easily reached given the ASSM assigned extent sizes). With this setup Oracle selected to perform full tablescans over index range scans of fairly selective indexes, even when much of the table blocks were cached in the KEEP pool (this is expected as all block reads are calculated as physical block reads, unless that changed in 11.2). While it might seem like a good idea, it resulted in excessive CPU utilization and very long query execution times (about 0.2 seconds for the index range scan and about 35 seconds for the plan using the full tablescan if I remember correctly). I compromised a bit on the two approaches after that.

I guess the point is that one could poke their eye out by blindly pushing buttons without understanding the potential side effects.

I was, for about 30 minutes any way, excited about the performance improvement offered by - until I realized that I forgot to set the parameter enabling direct I/O and async I/O when I started examining the reason for the difference. I hope that does not happen to any one else, otherwise there will be articles on the web stating "Amazing guru ____ improves Oracle performance by a factor of 236 (4474.545172/18.980762) by upgrading from to after tweaking a couple guru level mystery settings."

I guess the answer is test. I think that Jonathan would likely be a better source than myself when discussing system statistics strategies.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Sep 05 2009 - 21:19:44 CDT

Original text of this message