Re: unexpected heavy buffered IO during partition bitmap index rebuild

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_at_burgess-consulting.com.au> To: <jonathan_at_jlcomp.demon.co.uk>; <JTornblad_at_emdeon.com> Cc: "Oracle List List" <oracle-l_at_freelists.org> Sent: Wednesday, September 26, 2012 1:51 PM Subject: Re: unexpected heavy buffered IO during partition bitmap index rebuild

Jonathan/John,

a while back (_at_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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 26 2012 - 07:58:12 CDT