Re: unexpected heavy buffered IO during partition bitmap index rebuild
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Sep 2012 23:12:24 +0100
Notes in-line
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Tornblad, John" <JTornblad@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, September 26, 2012 9:43 PM
Subject: RE: unexpected heavy buffered IO during partition bitmap index
rebuild
| I would
| have thought (naively, I'm sure) that even with 100% of the rows
| migrated-and only one hop away-would have been at worst 2 times the IO
| to scan the table.
Hre's a link to a comment I made after posting this type of problem as a
puzzle, it may help you understand what's going on
Oracle follows each migrated row as it hits the row header, so you have a
basic direct path tablescan, but then every migrated row may result in a
single block read - especially if the db cache is significantly smaller
than (twice) the table size, as you read one random block you knock another
one out of memory and may have to re-read it later.
| In fact, after CTAS our recreated segment is 1/2 the
| size (in blocks) of the segment with 35% of its rows migrated.
Not surprising if your CTAS uses COMPRESS, which is implicitly pctfree zero
and you started with a fairly large pctfree; but there's another
possibility if you have 32KB blocks , ASSM, and transactions that update
and migrate a lot of rows in one update statement. There's a bug in some
versions of Oracle which can waste a lot of space to ITLs as the migration
takes place. There's a description buried in the pdf file at this blog -
but it's a long pdf and the reference points in the blog item are out of
sync: http://jonathanlewis.wordpress.com/2008/07/19/block-sizes/
Basic demo/test here: http://structureddata.org/files/jl_test_case.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
