Re: ASSM bug: slow INSERT after uncomitted DELETE

"Cristian Cudizio" <cristian.cudizio_at_yahoo.it> wrote in message news:c8ba2866-cda9-45c8-bca8-9b646f450c31_at_q14g2000vbi.googlegroups.com... On 7 Ago, 12:35, ca111026 <ca111..._at_gmail.com> wrote:
> In my previous post I mentioned ASSM bug (present in 10.2.0.4) that
> makes single-row INSERT extremely slow when table contains large
> uncomitted DELETE. Here is the test case:
>
> Tests were run on AIX 5.3 (64-bit) and Red Hat Linux (32-bit),
> Oracle 10.2.0.4
>
> 1. Create tablespace, it uses default 8K block size
>
> create tablespace assm
> extent management local uniform size 1m
> segment space management auto
> datafile
> '/abc/db01/oracle/ABC1P/oradata/assm_01.dbf' size 1000m;
>
> 2. Create table
>
> create table test_assm
> (
> n1 number,
> v1 varchar2(50),
> v2 varchar2(50),
> v3 varchar2(50),
> v4 varchar2(50),
> v5 varchar2(50),
> v6 varchar2(50),
> v7 varchar2(50),
> v8 varchar2(50),
> v9 varchar2(50),
> v10 varchar2(50)
> )
> tablespace assm;
>
> 3. Populate table with 1,000,000 rows, COMMIT at the end
>
> begin
> for i in 1..1000000 loop
> insert into test_assm values
> (i,
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567',
> '123456789*123456789*123456789*123456789*1234567');
>
> end loop;
> end;
> /
>
> COMMIT;
>
> 4. Insert additional 1000 rows into the table using ***SINGLE_ROW***
> inserts.
> I used following script to generate INSERT statements
>
> select
> 'insert into test_assm values(' || n1 ||
> ',''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'',' ||
>
> '''123456789*123456789*123456789*123456789*1234567'');'
> from
> test_assm
> where
> rownum < 1001;
>
> It took 1 second to insert 1000 rows through single-row inserts.
>
> 5. Delete all rows from the table, don't commit
>
> 6. Re-execute script that inserts 1000 rows from a different session.
> Runtime > 20 min.
>
> There were no indexes on the table.
>
> Insert into table containing uncomitted DELETE should not be
> significantly slower
> than insert into table without DELETE.
>
> When test was repeated using table in non-ASSM tablespace
> (still locally managed) there were no performance degradation
> when table contained uncomitted DELETE.

I've made this test.
Really each insert on the second session is very slow, i've traced the session and Oracle scans all (it seems) table blocks, with "db file sequential read"s
 while in the first session doesn't.
also after commit on first session insert on secondo session is fast. I'm just curious to know why oracle make those *consistent* gets on a insert.

Regards,
 Cristian

The problem is when the bitmap blocks are updated to show free space. It's a difficult one to handle. In this case, Oracle has updated the bitmap blocks as the 100,000 rows were deleted, so all the bitmaps show that the target blocks may have free space.

The second session comes in and reads the first hinted bitmap block, which points it to a data block, which turns out to have an uncommitted delete and doesn't have enough free space.

So the session works through every single bitmap block in turn, and each bitmap block points to 64 or 128 table blocks which all "might" have space but none of them do because the delete hasn't committed. Ultimately you examine every single block in the table before you get past all the blocks affected by the delete.

In an earlier version of a similar bug, Oracle DIDN'T mark the bitmap blocks on the delete, and then failed to clean up, so ended up with lots of free space in tables that couldn't be reached because the bitmaps said the blocks were full.

Look on this as a demonstration that ASSM is supposed to help with OLTP activities only - and DSS/DW bulk activity will cause problems.

(The trouble is, highly concurrency OLTP activity can still run into problems
with ASSM).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html