Re: datafile sizing question

[Tim Gorman]

Title: 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:[EMAIL PROTECTED]] 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.