Re: unexpected heavy buffered IO during partition bitmap index rebuild

The figures are consistent with a very large number of migrated or chained rows.

Can you think of any reason why one partition could get into a very different state from another - e.g one partition created with a few null columns but the other created with populated columns that don't result in row extension on updates ? Anything about lifecycle differences ?

Perhaps it's simply that the 2010 / 01 partition has been rebuilt at some time in the past already, tidying up migrated rows, while the 2012/08 partition is still a complete mess because it has an unsuitable pctfree and has been subject to a lot of row-lengthening updates.

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: Tuesday, September 25, 2012 7:19 PM Subject: unexpected heavy buffered IO during partition bitmap index rebuild

| alter index I_CLAIM_DAY_PAYER_KEY rebuild partition P201208
|
| call count cpu elapsed disk query current rows
| ------- ------ -------- ---------- ---------- ---------- ---------- ----------
| Parse 1 0.00 0.00 0 1 0 0
| Execute 1 329.99 5612.14 6620184 13310756 1996 0
| Fetch 0 0.00 0.00 0 0 0 0
| ------- ------ -------- ---------- ---------- ---------- ---------- ----------
| total 2 330.00 5612.14 6620184 13310757 1996 0
|
| Misses in library cache during parse: 1
| Optimizer mode: ALL_ROWS
| Parsing user id: 100
| Number of plan statistics captured: 1
|
| Rows (1st) Rows (avg) Rows (max) Row Source Operation
| ---------- ---------- ---------- ---------------------------------------------------
| 1 1 1 INDEX BUILD NON UNIQUE I_CLAIM_DAY_PAYER_KEY (cr=13311183
pr=6620185 pw=761 time=1317199179 us)(object id 0)
| 235902 235902 235902 BITMAP COMPACTION (cr=13310679 pr=6620183 pw=0
time=1316822714 us)
| 235902 235902 235902 SORT CREATE INDEX (cr=13310679 pr=6620183 pw=0
time=1316664028 us)
| 235902 235902 235902 PARTITION RANGE SINGLE PARTITION: 48 48 (cr=13310679
pr=6620183 pw=0 time=3517173222 us cost=6074 size=95026830 card=19005366)
| 235902 235902 235902 BITMAP CONSTRUCTION (cr=13310679 pr=6620183 pw=0
time=3517083617 us)
| 19005366 19005366 19005366 MAT_VIEW ACCESS FULL A_CLAIM_DAY PARTITION: 48
48 (cr=13310679 pr=6620183 pw=0 time=1321556021 us cost=6074 size=95026830 card=19005366)
|

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