Here’s a nasty little surprise I got last week while investigating an oddity with stats collection. I wanted to create a table in an ASSM tablespace and populate it from two or three separate sessions simultaneously so that I could get some “sparseness” in the data load. So I created a table and ran up 17 concurrent sessions to insert a few rows each. Because I wanted to know where the rows were going I got every session to dump the bitmap space management block at the start of the segment – the results were surprising.
I was using 18.104.22.168, with a locally managed tablespace with 1MB uniform extents, 8KB blocks, and ASSM. Here’s the basic code:
create table t1 ( n1 number, v1 varchar2(100) ) tablespace test_8k_assm ; -- -- 17 sessions then synchronised to do the following -- begin for i in 1..3 loop insert into t1 values(i, rpad('x',100)); commit; end loop; end; / alter system flush buffer_cache; execute dump_seg('t1',1,'table',0)
(The dump_seg() procedure is just a simple bit of code I wrote to dump selected blocks from a named segment.)
The results I saw in the bitmap block were so bizarre that I then ran the following query:
select dbms_rowid.rowid_block_number(rowid) as block_id, count(*) from t1 group by dbms_rowid.rowid_block_number(rowid) order by block_id ;
Remember, I was using 8KB blocks and 1MB uniform extents – which means 128 blocks per extent – and I had started with a completely new, clean, tablespace. So how many blocks do you think I had allocated to the table ? Here are the results of my query:
BLOCK_ID COUNT(*) ---------- ---------- 40 3 41 3 43 3 106 3 110 3 112 3 165 3 173 3 236 3 303 3 433 3 464 3 541 3 555 3 563 3 626 3 1000 3
What you see here is a table which has managed to allocate several extents – despite the fact that the 17 sessions could have inserted their data into 17 separate blocks in the first extent of the table. The table had grown to nine extents – although it hadn’t even put data into all the extents – and according to dbms_stats.gather_table_stats()there were 1,088 blocks in the table !
This looks pretty disastrous for ASSM – but I do have to say that flushing the buffer cache (17 times) could have introduced an extreme pinning problem that resulted in this surprising result. When I removed the flush and dump lines from my test the “realistic” results showed 17 blocks used in the first extent – so don’t panic if you’re using ASSM in a highly concurrent system; but if you have a couple of tables that seem to be much bigger than expected, maybe you now know what to look for.