RE: unexpected heavy buffered IO during partition bitmap index rebuild

Mark, thanks for this... you got us looking into root cause on chained (migrated) rows.

The note 1204203.1 was spot on and eye-opening. We are using COMPRESS FOR OLTP and that is the root of the migrated rows issue. Even with a "generous" PCTFREE of 40% for our table, this was not very close to the amount that is *really* needed to be for preventing row migration after many updates, due to "breaking the dictionary" as described in the note:

> When a row is updated if any of the compressed columns in the symbol
> table are changed, they will have to be removed from the symbol table
> and re-stored inline in the row. This will cause even larger row 
> expansion than in a non-compressed case, which will mean it is much 
> more likely that such an update will cause row migration.

I've put in our test cases below for anyone curious. By iterating these it turns out that for *our* table and *our* update pattern on 11.2.0.3 (your mileage may vary!) we need at least a PCTFREE of 61% (!) to prevent row migration in the COMPRESSed FOR OLTP segment to accommodate the small amount of actual changed data and the apparently much larger amount of "symbols re-stored inline with the row". A non-compressed table needed only PCTFREE 22% to accommodate the changed data. Unfortunately in our case, the net storage difference between a compressed (for OLTP) and non-compressed table after updates is 9% in the *wrong direction* (total blocks consumed are higher over time for the compressed table, we would have been better off not using COMPRESS FOR OLTP for *this* table).

Caveat emptor, or at least: "Idem remedium non curarent omnem aegros" which Google informs me is a machine translation for "The same remedy does not heal all patients". Currently trying to cheer up our DBAs by pointing out we still like COMPRESS FOR OLTP for (a) data that is never updated and (b) data that stops being updated after 'n' months. Hopefully that will continue to make sense to the cost accountants.

While this eliminates the root cause of the problem, the "spotted in the wild" *massive* and unreasonable increase in logical IOs and buffered reads (23,100%) for a table with ~33% migrated rows does seem a bit steep. Because migrated rows on the COMPRESS FOR OLTP table explains the root cause, the side effect of way too much IO will go on the pile of Doesn't Seem Like It Should Behave That Badly But No Further Justification Can Be Found To Create A Test Case. I successfully suppressed the desire to get into small table behavior, cached blocks from our actual table, db buffer size and so on but those were the best crumbs I thought I had to follow it further.

Probably my last post on this thread... mark this entry as the resolution to this problem (thanks again to all), with the "unexpected heavy buffered IO" just a glittery (blinding!) object along the path to the true root cause.... "stop, put it down... keep going..."

-john

• compressed table

  ---

drop table migtest_compressed purge;

create table migtest_compressed (
 key1 number(10), key2 number(10), key3 number(10), key4 number(10), key5 number(10), quantity1 number, quantity2 number, quantity3 number, quantity4 number, quantity5 number, quantity6 number,  quantity7 number, quantity8 number, amount1 number, quantity9 number, quantity10 number, quantity11 number, quantity12 number, quantity13 number, quantity14 number, quantity15 number ) tablespace payer1_tbs_p201208_1_48 compress for oltp pctfree &1;

select table_name, pct_free, compression from user_tables where table_name = 'MIGTEST_COMPRESSED';

insert into migtest_compressed
select rownum, rownum*2, rownum*3, rownum*5, rownum*5, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 from dual
connect by level <= 100000;

exec
dbms_stats.gather_table_stats(ownname=>'PAYER1',tabname=>'MIGTEST_COMPRE SSED',method_opt=>'for all columns size 1');

select table_name, num_rows, blocks, avg_row_len from user_tables where table_name = 'MIGTEST_COMPRESSED';

truncate table chained_rows;

analyze table migtest_compressed list chained rows into chained_rows;

select * from chained_rows where table_name = 'MIGTEST_COMPRESSED';

• update 100% of the rows update migtest_compressed set quantity1 = 100000, quantity2 = 100000, quantity3 = 100000, quantity4 = 100000, quantity5 = 100000, quantity6 = 100000, quantity7 = 100000, quantity8 = 100000, quantity9 = 100000, quantity10 = 100000, quantity11 = 100000, quantity12 = 100000, quantity13 = 100000, quantity14 = 100000, quantity15 = 100000, amount1 = 100000;

exec
dbms_stats.gather_table_stats(ownname=>'PAYER1',tabname=>'MIGTEST_COMPRE SSED',method_opt=>'for all columns size 1');

select table_name, num_rows, blocks, avg_row_len from user_tables where table_name = 'MIGTEST_COMPRESSED';

analyze table migtest_compressed list chained rows into chained_rows;

select count(*) from chained_rows where table_name = 'MIGTEST_COMPRESSED';

• NOT compressed table

  ---

drop table migtest_uncompressed purge;

create table migtest_uncompressed (
 key1 number(10), key2 number(10), key3 number(10), key4 number(10), key5 number(10), quantity1 number, quantity2 number, quantity3 number, quantity4 number, quantity5 number, quantity6 number,  quantity7 number, quantity8 number, amount1 number, quantity9 number, quantity10 number, quantity11 number, quantity12 number, quantity13 number, quantity14 number, quantity15 number ) tablespace payer1_tbs_p201208_1_48 nocompress pctfree &1;

select table_name, pct_free, compression from user_tables where table_name = 'MIGTEST_UNCOMPRESSED';

insert into migtest_uncompressed
select rownum, rownum*2, rownum*3, rownum*5, rownum*5, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 from dual
connect by level <= 100000;

exec
dbms_stats.gather_table_stats(ownname=>'PAYER1',tabname=>'MIGTEST_UNCOMP RESSED',method_opt=>'for all columns size 1');

select table_name, num_rows, blocks, avg_row_len from user_tables where table_name = 'MIGTEST_UNCOMPRESSED';

truncate table chained_rows;

analyze table migtest_uncompressed list chained rows into chained_rows;

select * from chained_rows where table_name = 'MIGTEST_UNCOMPRESSED';

• update 100% of the rows update migtest_uncompressed set quantity1 = 100000, quantity2 = 100000, quantity3 = 100000, quantity4 = 100000, quantity5 = 100000, quantity6 = 100000, quantity7 = 100000, quantity8 = 100000, quantity9 = 100000, quantity10 = 100000, quantity11 = 100000, quantity12 = 100000, quantity13 = 100000, quantity14 = 100000, quantity15 = 100000, amount1 = 100000;

exec
dbms_stats.gather_table_stats(ownname=>'PAYER1',tabname=>'MIGTEST_UNCOMP RESSED',method_opt=>'for all columns size 1');

select table_name, num_rows, blocks, avg_row_len from user_tables where table_name = 'MIGTEST_UNCOMPRESSED';

analyze table migtest_uncompressed list chained rows into chained_rows;

select count(*) from chained_rows where table_name = 'MIGTEST_UNCOMPRESSED'; </pre>This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.

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