Re: datafile sizing question

----- Original Message -----

From: VIVEK_SHARMA

To: ORACLE-L@fatcity.com ; Tim@SageLogix.com

Sent: Monday, September 30, 2002 9:05 AM

Subject: RE: datafile sizing question

If Next Extent Sizing is NON-Uniform for an LMT , will the Larger Number of Extents cause Fragmentation & Performance Degradation ?

If so What Number of Extents may be Considered as a Candidate for DE-Fragmentation ?

 

NOTE - We have been Manually Specifying the Size of the NEXT_EXTENT of Objects in LMTs by Converting ALLOCATION_TYPE (sys.dba_tablespaces) from "SYSTEM" to "USER"  for respective Tablespaces to Check Growth to Larger Numbers of Extents

 

100-200 Extents we Consider as a Candidate for DE-Fragmentation using exp/imp OR ALTER TABLE/INDEX ... MOVE with Bigger Extent Sizes

 

Oracle 8.1.7

-----Original Message-----
From: Tim Gorman [mailto:Tim@SageLogix.com]
Sent: Monday, September 30, 2002 7:48 PM
To: Multiple recipients of list ORACLE-L
Subject: 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 -----

From: Jamadagni, Rajendra

To: Multiple recipients of list ORACLE-L

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).