Prev: So is 11.2.0.2 going to be out anytime soon?
Next: Ora-19906 when restoring user managed backup - stumped!
From: John Hurley on 5 Sep 2009 18:23
On Sep 4, 9:04 pm, Charles Hooper <hooperc2...(a)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?

From: Charles Hooper on 5 Sep 2009 22:19
On Sep 5, 6:23 pm, John Hurley <johnbhur...(a)sbcglobal.net> 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 11.1.0.7 and 10.2.0.4 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 11.1.0.6 and
11.2.0.1 but I have not reviewed those test runs). Oracle 10.2.0.4
which had CPU statistics collected still deciided to select a full
tablescan with OPTIMIZER_INDEX_COST_ADJ set to 100, while 11.1.0.7
with NOWORKLOAD statistics selected to perform an index range scan
regardless of the value of OPTIMIZER_INDEX_COST_ADJ. Oracle 10.2.0.4
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 11.2.0.1 - 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 11.1.0.7 to 11.2.0.1 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.
From: Robert Klemme on 6 Sep 2009 11:14
On 05.09.2009 22:26, Charles Hooper wrote:

> * Direct I/O and Asynch I/O, which seem to be frequently recommended
> to improve performance, do not always improve performance, and may in
> fact drastically affect performance.

Wouldn't you have to increase SGA target when switching to direct IO for
a fair comparison? Jonathan pointed out that without direct IO you are
actually increasing disk buffer size (Oracle's SGA + OS level cache).
So, assuming a dedicated machine (so not much OS buffer needed for other
tasks), the direct IO test would have to run with the SGA increased so
you get a similar amount of memory used for caching blocks.

> So, 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 by far not as expert as Jonathan but I have a bad gut feeling about
such a "global optimization". It is not very targeted and I have seen
too many cases where an optimization which looked good on first sight
hat bad effects overall...

Charles, thank you for sharing this!

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
From: Robert Klemme on 6 Sep 2009 11:18
On 05.09.2009 19:00, Mladen Gogala wrote:
> On Fri, 04 Sep 2009 18:04:37 -0700, Charles Hooper wrote:
>
>> Obviously from the above, 11.2.0.1 is significantly faster at the index
>> access than is 11.1.0.6 on the same platform. But wait, I forgot
>> something. On 11.1.0.6 on Linux I had enabled direct I/O and
>> asynchronous I/O by setting the FILESYSTEMIO_OPTIONS parameter to
>> SETALL. I did not change that parameter on 11.2.0.1, so it defaulted to
>> NONE. What happens when the FILESYSTEMIO_OPTIONS parameter is set to
>> SETALL?
>> * Oracle 11.2.0.1 now required 42.45 seconds for the full tablescan
>> while the index access path required one hour, 16 minutes and 46
>> seconds.
>
> Charles, that is to be expected, although this is a bit drastic
> difference. Most file systems do read prefetch and even asynchronous
> reads of the blocks when using buffer cache. Also, just as Oracle RDBMS,
> Unix derivatives also have smart strategies for keeping the hot blocks in
> the cache, which results in rather drastic performance gains. If you
> start doing direct I/O, lose all the help from the system buffer cache
> and OS. You can help things by using readahead* RH EL services and
> "blockdev" command to increase read-ahead.

Mladen, you make it sound as if OS level caching was smarter than
Oracle's own buffer cache strategies. In theory Oracle's code would
have better knowledge about access patterns so my expectation would be
that taking memory from OS buffer cache and giving it to the Oracle
instance would lead to better performance than using OS level caching.
Otherwise we should switch off Oracle's own buffering and use only OS
features.

What am I missing?

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
From: Charles Hooper on 6 Sep 2009 11:55
On Sep 6, 11:14 am, Robert Klemme <shortcut...(a)googlemail.com> wrote:
> On 05.09.2009 22:26, Charles Hooper wrote:
>
> > * Direct I/O and Asynch I/O, which seem to be frequently recommended
> > to improve performance, do not always improve performance, and may in
> > fact drastically affect performance.
>
> Wouldn't you have to increase SGA target when switching to direct IO for
> a fair comparison?  Jonathan pointed out that without direct IO you are
> actually increasing disk buffer size (Oracle's SGA + OS level cache).
> So, assuming a dedicated machine (so not much OS buffer needed for other
> tasks), the direct IO test would have to run with the SGA increased so
> you get a similar amount of memory used for caching blocks.

That is a good question. The machine in this setup had 12GB of
memory. The SGA_TARGET was set to 8GB, the PGA_AGGREGATE_TARGET was
set to 1.8GB, and the KEEP buffer pool was set to 6GB. Since the
actual table which the test table mimics will likely be infrequently
queried in full, such as a report which attempts to indicate the
change in the size measured for the left side of the cylinder wall
over the production lifetime of the part, it probably would not be a
good idea to optimize the instance and operating system performance
for this one query. Other data from other tables would likely be
occupying the KEEP buffer pool, which means that in production the
available RAM for caching of data blocks at the operating system level
might be quite limited. It might be interesting to test what happens
when the KEEP buffer pool is fully utilized - will Linux start
swapping other memory out to disk to buffer the Oracle blocks in the
file system cache? What happens when a 512MB (or larger) redo log
needs to be archived, will it hinder the effects of the operating
system level caching of Oracle blocks? It might come down to how
closely the test environment is able to mimic the production
environment.

> > So, 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 by far not as expert as Jonathan but I have a bad gut feeling about
> such a "global optimization".  It is not very targeted and I have seen
> too many cases where an optimization which looked good on first sight
> hat bad effects overall...
>
> Charles, thank you for sharing this!
>
> Kind regards
>
>         robert

This test case is a light-weight example from the book "Expert Oracle
Practices: Oracle Database Administration from the Oak Table". The
test case took a different twist while testing on 11.2.0.1.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: So is 11.2.0.2 going to be out anytime soon?
Next: Ora-19906 when restoring user managed backup - stumped!