Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Increased IO Overhead in 10g?
On Mar 28, 4:21 am, Robert Klemme <shortcut..._at_googlemail.com> 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.
Received on Wed Mar 28 2007 - 10:09:17 CDT