RE: datafile sizing question
Do your own testing. Don't rely on
papers. Prove it yourself. It's easy.
There are two types of "performance" implied in
this discussion about extent allocation and deallocation:
- performance of SQL statements like SELECT, INSERT,
UPDATE, DELETE (i.e. DML)
- performance of statements like CREATE, ALTER,
DROP, and TRUNCATE (i.e. DDL)
There is no reason to suggest that the performance
of DML might be affected by the number of extents, whether 1 extent or 500,000
extents. Think about it. Random, single-block reads (i.e. indexed
scans) are completely unaffected by Oracle extent size and
number; they are block-level accesses, after all.
They care nothing about the concept of extent. Sequential,
multi-block reads (i.e. full table scans, fast full index scans) can only be
affected if the extent size is extremely small but is completely
unaffected by the number of extents. Extremely small extents
can obviously affect a multi-block read if they consistently limit
the number of blocks that can be read.
Since testing this requires some non-trivial
resources (i.e. test data and disk space) to prove, I'll
leave the proving to those who have both (in addition
to time).
This leaves DDL, which is mercifully easy to test
on any environment using locally-managed tablespaces. Do *not* do this
type of testing in dictionary-managed tablespaces, as there is no point.
LMTs were created to alleviate the problems you'd be experiencing with
DMTs...
Try an exercise like
the following in SQL*Plus:
set timing on
create table bumpf (xxx number) tablespace
<LMT-tsname>;
begin
for i in 1..<COUNTER>
loop
execute
immediate 'alter table bumpf allocate extent';
end loop;
end loop;
/
drop table bumpf;
Re-run the test for different values of
<COUNTER>, all the way up to values like 250,000 or 500,000, if you
like. The timings for CREATE TABLE should be consistent, of course, as it
is the exact same command each time. The time spent in the PL/SQL loop
should be roughly linear with the value of <COUNTER>, the point being that
each ALLOCATE EXTENT takes roughly the same amount of time. You might
observe an "elbow" in the plotted curve of timings at some point which Rachel
suggested at 4000 but I think will vary depending on your environment. On
my laptop, I've seen the curve stay linear up into the 100,000s. The time
spent in DROP may not vary a great deal; it should be roughly linear
with the value of COUNTER but I find that it is much better than linear, which
leads me to believe that some parts of a DROP/TRUNCATE operation are
asynchronous.
Try it out!
----- Original Message -----
Sent: Monday, September 30, 2002 6:33
AM
Subject: RE: datafile sizing
question
Rachel,
Are there any studies or papers that test and explain this new
magic 4000 extents number? My manager is excited about LMT, but no so excited
about number of extents. So, if there is a good paper, I can make him feel
happy about this ...
Thanks in advance
Raj
______________________________________________________
Rajendra Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any
opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion
is an art!
-----Original Message-----
From:
Rachel Carmichael [mailto:wisernet100@yahoo.com]
Sent: Monday, September 30, 2002 7:03 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: datafile sizing question
with evenly sized extents, there is no such thing as
fragmentation anymore and Oracle can deal with objects with numbers of extents
up to about 4000 before it starts to slow down a
bit.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: Tim_at_SageLogix.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Sep 30 2002 - 09:18:23 CDT