We discovered that some tables, which get some new records by several OWB Mappings every night, are filled extremely sparse. In most blocks we find only one record even if the average record size is some 60 bytes and the blocksize is 8k. Only in a few blocks there are up to 18 records and every night nearly the same - but not exactly the same - mappings write their data in one block together, while all other mappings use a separate block. When we move all records to a temporary table, truncate the original table and move all records back, every block is filled with 110 to 120 records but in the next night the new records are sparse again.
I moved the problematical tables from an ASSM managed tablespace to a manually managed tablespace and increased the number of freelists to 2 but this made no difference in the behavior. Is there any explanation for this conduct of the database? Is there a known bug in 10.2.0.3 (it’s not up to date, I know) which leads to this result?
I do not have a 10.2.0.3 environment at hand but I remember there was a bug in Oracle 11gR1 where an insert statement (with values clause and an append hint specified) use to write to a new block even when free space was available in existing blocks to fit the row.
There are no references as yet (or may be my search is not exhaustive enough) if this bug existed in lower versions as well.
You could do a test yourself to know this.
As the problem still exited after changing to database version 126.96.36.199 I opened a new thread for this topic: Empty blocks are not used for inserts
It seems that the problem occured, because OWB uses the APPEND hint by default for mappings, which is inadequate when only single rows are written to a log table.