Re: unexpected heavy buffered IO during partition bitmap index rebuild

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_at_emdeon.com> To: <oracle-l_at_freelists.org> 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

--
http://www.freelists.org/webpage/oracle-l