Re: 9i - ASSM
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 6 Mar 2004 08:08:36 -0000
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@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
