Increased IO Overhead in 10g?

[Database Administrator]

Hi,

I know this question is going to sound a bit fuzzy as I cannot disclose
a lot of detail. However my intention is to get a rather general
statement about your experience. My question is basically: is anybody
here aware of higher overhead in Ora 10g disk IO vs., say 8.1.7? One
thing that came to mind are all the management features; I know that
they are using functionality that has been available for several
versions but maybe more of these introspecting features are enabled by
default.

Thanks for any feedback!

Kind regards

robert

[Database Administrator]

On Mar 28, 10:21 am, Robert Klemme wrote:
> Hi,
>
> I know this question is going to sound a bit fuzzy as I cannot disclose
> a lot of detail. However my intention is to get a rather general
> statement about your experience. My question is basically: is anybody
> here aware of higher overhead in Ora 10g disk IO vs., say 8.1.7? One
> thing that came to mind are all the management features; I know that
> they are using functionality that has been available for several
> versions but maybe more of these introspecting features are enabled by
> default.
>
> Thanks for any feedback!
>
> Kind regards
>
> robert

Hi Robert,
i've little experience with 8.1.7, but with 9.2. we have our
application running both on 9.2 and 10.1
and 10.2. I've not seen appreciable differences in disk I/O. moreover
i i think taht management options
do not put any overhead on disk i/o, may be a little on CPU, but it is
nothing to bother with.

[Database Administrator]

On 28.03.2007 10:36, Cristian Cudizio wrote:
> On Mar 28, 10:21 am, Robert Klemme wrote:
>> I know this question is going to sound a bit fuzzy as I cannot disclose
>> a lot of detail. However my intention is to get a rather general
>> statement about your experience. My question is basically: is anybody
>> here aware of higher overhead in Ora 10g disk IO vs., say 8.1.7? One
>> thing that came to mind are all the management features; I know that
>> they are using functionality that has been available for several
>> versions but maybe more of these introspecting features are enabled by
>> default.
>
> i've little experience with 8.1.7, but with 9.2. we have our
> application running both on 9.2 and 10.1
> and 10.2. I've not seen appreciable differences in disk I/O. moreover
> i i think taht management options
> do not put any overhead on disk i/o, may be a little on CPU, but it is
> nothing to bother with.

Thanks for the quick reply! This is what I have expected.

Kind regards

robert

[Database Administrator]

Robert Klemme wrote:
>
> Hi,
>
> I know this question is going to sound a bit fuzzy as I cannot disclose
> a lot of detail. However my intention is to get a rather general
> statement about your experience. My question is basically: is anybody
> here aware of higher overhead in Ora 10g disk IO vs., say 8.1.7? One
> thing that came to mind are all the management features; I know that
> they are using functionality that has been available for several
> versions but maybe more of these introspecting features are enabled by
> default.
>
> Thanks for any feedback!
>
> Kind regards
>
> robert

I/O patterns do change a bit. For one thing, in 10g you should be using
Automated Undo instead of manually managed rollback segments. I can't
remember if tempfiles were introduced in 8i or 9i, but in 10g, your TEMP
tablespace should be using tempfiles. This has a different I/O
requirement than using datafiles for TEMP. Most importantly, redo is not
logged.

In a well-tuned system, you should not notice the I/O overhead of
running Oracle 10g. But if you put all data on one disk spindle, the I/O
impacts may be noticed. This can be true of previous Oracle versions too.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

[Database Administrator]

On Mar 28, 4:21 am, Robert Klemme wrote:
> Hi,
>
> I know this question is going to sound a bit fuzzy as I cannot disclose
> a lot of detail. However my intention is to get a rather general
> statement about your experience. My question is basically: is anybody
> here aware of higher overhead in Ora 10g disk IO vs., say 8.1.7? One
> thing that came to mind are all the management features; I know that
> they are using functionality that has been available for several
> versions but maybe more of these introspecting features are enabled by
> default.
>
> Thanks for any feedback!
>
> Kind regards
>
> robert

One has to wonder if AWR (Automatic Workload Repository) data
collection has any impact on IO overhead as it is flushed to disk.
Even if you have not purchased the rights to use AWR, it is still
collecting data. If you monitor the changes to extents for roughly
two minutes and compare the delta values for the extents, you may see
900+ changes like this:
Owner.Name Type Byte Change Block Change Extent Change
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 262144 32 4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 262144 32 4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 262144 32 4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 262144 32 4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 393216 48 6
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -393216 -48 -6
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -393216 -48 -6
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 262144 32 4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 327680 40 5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 393216 48 6
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 262144 32 4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -262144 -32 -4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -262144 -32 -4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -262144 -32 -4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -327680 -40 -5
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -262144 -32 -4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -262144 -32 -4
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 65536 8 1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION -262144 -32 -4
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION 65536 8 1
SYS.WRH$_DB_CACHE_ADVICE_PK INDEX PARTITION -65536 -8 -1
....
SYS.WRH$_SYSTEM_EVENT TABLE PARTITION 65536 8 1
SYS.WRH$_SYSTEM_EVENT TABLE PARTITION 65536 8 1
SYS.WRH$_SYSTEM_EVENT TABLE PARTITION -65536 -8 -1
SYS.WRH$_SYSTEM_EVENT TABLE PARTITION 65536 8 1

If your IO system cannot handle the increased work load, then this
might be part of the problem. Jonathan Lewis had something on his
blog a while ago that described how to disable AWR.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

[Database Administrator]

On 28.03.2007 17:09, Charles Hooper wrote:
> On Mar 28, 4:21 am, Robert Klemme wrote:
>> I know this question is going to sound a bit fuzzy as I cannot disclose
>> a lot of detail. However my intention is to get a rather general
>> statement about your experience. My question is basically: is anybody
>> here aware of higher overhead in Ora 10g disk IO vs., say 8.1.7? One
>> thing that came to mind are all the management features; I know that
>> they are using functionality that has been available for several
>> versions but maybe more of these introspecting features are enabled by
>> default.
>
> One has to wonder if AWR (Automatic Workload Repository) data
> collection has any impact on IO overhead as it is flushed to disk.
> Even if you have not purchased the rights to use AWR, it is still
> collecting data. If you monitor the changes to extents for roughly
> two minutes and compare the delta values for the extents, you may see
> 900+ changes like this:



> If your IO system cannot handle the increased work load, then this
> might be part of the problem. Jonathan Lewis had something on his
> blog a while ago that described how to disable AWR.

http://jonathanlewis.wordpress.com/2...disabling-awr/

Thanks for that info! This sounds interesting and worthy of further
research.

Kind regards

robert

[Database Administrator]

On Mar 28, 4:21 am, Robert Klemme wrote:
> Hi,
>
> I know this question is going to sound a bit fuzzy as I cannot disclose
> a lot of detail. However my intention is to get a rather general
> statement about your experience. My question is basically: is anybody
> here aware of higher overhead in Ora 10g disk IO vs., say 8.1.7? One
> thing that came to mind are all the management features; I know that
> they are using functionality that has been available for several
> versions but maybe more of these introspecting features are enabled by
> default.
>
> Thanks for any feedback!
>
> Kind regards
>
> robert

The biggest IO impact I have observed following a 10g upgrade is in
materialized view data replication. Where 8i and 9i truncates a
materialized view table when performing a full refresh, 10g uses
delete to remove all the rows in the table; increasing the amount of
redo and undo written, sometimes dramatically. Using the
atomic_refresh=>FALSE parameter in the dbms_mview.refresh call will
revert 10g back to the 8i/9i behavior. See Metalink Note:306502.1 for
more info.