Re: unexpected heavy buffered IO during partition bitmap index rebuild
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "Oracle List List" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Sep 2012 13:58:12 +0100
Mark,
That's certainly a possibility - COMPRESS creates data with pctfree 0 by
default, so almost any update will cause migration.
However, to get 6.7M random I/Os in 19M rows my initial thought was perhaps
the data load does something like:
insert a batch of row stubs
update
update
update
update
This type of behaviour could produce a large number of very short rows in a
block, then migrate a large fraction of them (in this case some 30%) out of
the block
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Mark Burgess" <mark@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>; <JTornblad@xxxxxxxxxx>
Cc: "Oracle List List" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, September 26, 2012 1:51 PM
Subject: Re: unexpected heavy buffered IO during partition bitmap index
rebuild
Jonathan/John,
a while back (@18months +) I observed a similar type of problem on a client
system that had been upgraded from 10g to 11g. Some of the older partitions
had been exchanged from tables created by CTAS with COMPRESS option and
then subsequently updated (which from my understanding the update of the
compressed rows introduced the row chaining) on 10g. The data in these
partitions was not heavily accessed at all. Fast forward in time this data
was required to be accessed and updated under 11g and the time and number
of LIO's increased significantly compared to the partitions that had not
been updated. Fix was to 'ALTER PARTITION...MOVE COMPRESS' which corrected
the row chaining. MOS note # 1204203.1 discusses this behaviour when
compressed tables/partitions are involved.
Regards,
Mark Burgess
--
//www.freelists.org/webpage/oracle-l
Other related posts:
