Re: 9i - ASSM

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 6 Mar 2004 08:08:36 -0000
Message-ID: <036e01c40352$39ad5940$7002a8c0@Primary>

One example of a bug (now fixed) is on
my website under the URL

http://www.jlcomp.demon.co.uk/bustbits.html

It was a test designed to highlight the problems of single-row updates on bitmapped indexed columns (now improved in 10g), but caught a bug in ASSM relating to excessive space allocation.

Another, already mentioned I think, relates to bitmaps not being fixed on rollback; As a simple test try this (ASSM tablespace, 8K block):

create table t1 (v1 varchar2(100))
pctfree 90 pctused 10;

    insert into t1
    select rpad('x',100)
    from all_objects
    where rownum <= 100
;

analyze table t1 compute statistics; select blocks from user_tables where table_name = 'T1';

will probably be about 16, may vary according to your process id

    truncate table t1;
    insert into t1
    select rpad('x',100)
    from all_objects
    where rownum <= 100
;

rollback;

    insert into t1
    select rpad('x',100)
    from all_objects
    where rownum <= 100
;

analyze table t1 compute statistics; select blocks from user_tables where table_name = 'T1';

will probably be about 32, may vary according to your process id
for a DMT, both results will be about 14 on an 8K block size.

The index problem relates to clustering factor. ASSM works by "randomly" distributing
row inserts across a small number of blocks in a table. The clustering_factor of an index is used to obtain a measure of how "non-random" the row distribution is. Spot the conflict of interest.

If you have important requirements that follow the PATTERN of the following:

create index (order_date, sequence_number) on ...

select * from .. where order date = {const}

Then data that arrived in an FMT (freelist managed tablespace) would give the index a perfect clustering factor (matches blocks in table), data arriving in a PMT (page-table managed tablespace) would give the index a disastrous clustering factor.

I have a demonstration case - not complex, but tedious to set up because of the required concurrency - that shows an efficient indexed access path an FMT, turning into a pointless and expensive tablescan on PMT because of this.

(Note - the same problem arises with multiple freelists. But ASSM (PMT) has an effect on every single table in the tablespace; whereas you choose very carefully which tables you want have with multiple freelists, and work around the side effects).

I don't have bug numbers. I stopped reporting things like this some time ago. It was too much like hard work persuading the support staff that there was a problem. (As soon as you say "I can work around it", it's an uphill problem getting it on to the people who understand the issue).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Koivu, Lisa" <Lisa.Koivu_at_Cendant-TRG.com> To: <oracle-l_at_freelists.org> Sent: Friday, March 05, 2004 3:37 PM Subject: RE: 9i - ASSM

Jonathan, can you expand on the bugs you reference and how the CBO is fooled? We have the same debate going on here.

If you have bug numbers and a nice example of the CBO choking, that would be very helpful.

Thank you
Lisa Koivu
Orlando, FL, USA

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Mar 06 2004 - 02:05:15 CST