Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: datafile sizing question

RE: datafile sizing question

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Mon, 30 Sep 2002 08:08:38 -0800
Message-ID: <F001.004DC7EA.20020930080838@fatcity.com> 





Content-Type: multipart/alternative;


 boundary="----_=_NextPart_001_01C26892.CD921686"
------_=_NextPart_001_01C26892.CD921686


Content-Type: text/plain;


 charset="iso-8859-1"


Content-Transfer-Encoding: quoted-printable



If Next Extent Sizing is NON-Uniform for an LMT , will the Larger Number =
of Extents cause Fragmentation & Performance Degradation ?=20
If so What Number of Extents may be Considered as a Candidate for =
DE-Fragmentation ?


=20



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=20


=20



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


=20



Oracle 8.1.7



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


Sent: Monday, September 30, 2002 7:48 PM
To: Multiple recipients of list ORACLE-L




Do your own testing.  Don't rely on papers.  Prove it yourself.  It's =
easy.


=20



There are two types of "performance" implied in this discussion about =
extent allocation and deallocation:




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.


=20



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


=20



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


=20



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.


=20



Try it out!





Rachel,=20



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=20


Raj=20

______________________________________________________=20
Rajendra Jamadagni              MIS, ESPN Inc.=20


Rajendra dot Jamadagni at ESPN dot com=20
Any opinion expressed here is personal and doesn't reflect that of ESPN =
Inc.=20


QOTD: Any clod can have facts, but having an opinion is an art!=20




-----Original Message-----=20


Sent: Monday, September 30, 2002 7:03 AM=20
To: Multiple recipients of list ORACLE-L=20




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.




------_=_NextPart_001_01C26892.CD921686


Content-Type: text/html;


 charset="iso-8859-1"


Content-Transfer-Encoding: quoted-printable


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =


charset=3Diso-8859-1">


<TITLE>RE: datafile sizing question</TITLE>


<META content=3D"MSHTML 5.50.4807.2300" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20


class=3D556051115-30092002>If Next Extent Sizing is NON-Uniform for an =
LMT , will=20


the Larger Number of Extents cause Fragmentation &amp; Performance =
Degradation ?=20


</SPAN></FONT></DIV>


<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20
class=3D556051115-30092002>If so What Number of Extents may be =
Considered as a=20


Candidate for DE-Fragmentation ?</SPAN></FONT></DIV>
<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20
class=3D556051115-30092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20
class=3D556051115-30092002>NOTE - We have been Manually =
Specifying&nbsp;the Size=20


of the NEXT_EXTENT of Objects in LMTs by Converting ALLOCATION_TYPE=20
(sys.dba_tablespaces) from "SYSTEM" to "USER"&nbsp; for respective =
Tablespaces=20


to Check Growth </SPAN></FONT><FONT size=3D2><FONT face=3D"Courier =
New"><FONT=20


color=3D#ff0000><SPAN class=3D556051115-30092002>to Larger Numbers =
</SPAN><SPAN=20


class=3D556051115-30092002>of Extents </SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20
class=3D556051115-30092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20
class=3D556051115-30092002>100-200 Extents we Consider as a Candidate =
for=20


DE-Fragmentation using exp/imp OR ALTER TABLE/INDEX ... MOVE =
</SPAN></FONT><FONT=20


face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN =
class=3D556051115-30092002>with=20


Bigger Extent Sizes </SPAN></FONT></DIV>
<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20
class=3D556051115-30092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Courier New" color=3D#ff0000 size=3D2><SPAN=20
class=3D556051115-30092002>Oracle 8.1.7</SPAN></FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
  <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT =
face=3DTahoma=20


  size=3D2>-----Original Message-----<BR><B>From:</B> Tim Gorman=20
  [mailto:Tim_at_SageLogix.com]<BR><B>Sent:</B> Monday, September 30, 2002 =
7:48=20


  PM<BR><B>To:</B> Multiple recipients of list =
ORACLE-L<BR><B>Subject:</B> Re:=20


  datafile sizing question<BR><BR></FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>Do your own testing.&nbsp; Don't rely =
on=20


  papers.&nbsp; Prove it yourself.&nbsp; It's easy.</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>There are two types of "performance" =
implied in=20


  this discussion about extent allocation and deallocation:</FONT></DIV>
  <UL>


    <LI><FONT face=3DArial size=3D2>performance of SQL statements like =
SELECT,=20



    INSERT, UPDATE, DELETE (i.e. DML)</FONT>=20
    <LI><FONT face=3DArial size=3D2>performance of statements like =
CREATE, ALTER,=20



    DROP, and TRUNCATE (i.e. DDL)</FONT></LI></UL>
  <DIV><FONT face=3DArial size=3D2>There is no reason to suggest that =
the=20


  performance of DML might be affected by the number of extents, whether =
1=20


  extent or 500,000 extents.&nbsp; Think about it.&nbsp; Random, =
single-block=20


  reads (i.e. indexed scans) are completely unaffected by Oracle extent=20
  <EM>size</EM> and <EM>number</EM>;&nbsp; they are <EM>block-level=20
  </EM>accesses, after all.&nbsp; They care nothing about the concept of =



  <EM>extent.&nbsp; S</EM>equential, multi-block reads (i.e. full table =
scans,=20


  fast full index scans) can only be affected if the extent =
<EM>size</EM> is=20


  extremely small but is completely unaffected by the <EM>number </EM>of =



  extents.&nbsp;&nbsp;Extremely small extents can obviously affect a =
multi-block=20


  read if they&nbsp;consistently limit the&nbsp;number of blocks that =
can be=20

  read.</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>Since testing&nbsp;this requires some =


non-trivial=20


  resources (i.e. <EM>test data</EM> and <EM>disk space</EM>)&nbsp;to =
prove,=20


  I'll leave the proving to those who have both (in addition=20
  to&nbsp;<EM>time</EM>).</FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>This leaves DDL, which is mercifully =
easy to test=20


  on any environment using locally-managed tablespaces.&nbsp; Do *not* =
do this=20


  type of testing in dictionary-managed tablespaces, as there is no =
point.&nbsp;=20


  LMTs were created to alleviate the problems you'd be experiencing with =



  DMTs...</FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>Try </FONT><FONT face=3DArial =
size=3D2>an exercise=20


  like the following in SQL*Plus:</FONT></DIV>
  <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
    <DIV><FONT face=3DArial size=3D2>set timing on</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>create table bumpf (xxx number) =
tablespace=20


    &lt;LMT-tsname&gt;;</FONT></DIV>


    <DIV><FONT face=3DArial size=3D2>begin</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; for i in =
1..&lt;COUNTER&gt;=20


    loop</FONT></DIV>


    <DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp; execute=20


    immediate 'alter table bumpf allocate extent';</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; end =
loop;</FONT></DIV>

    <DIV><FONT face=3DArial size=3D2>end loop;</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>/</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>drop table =


bumpf;</FONT></DIV></BLOCKQUOTE>


  <DIV dir=3Dltr><FONT face=3DArial size=3D2>Re-run the test for =
different values of=20


  &lt;COUNTER&gt;, all the way up to values like 250,000 or 500,000, if =
you=20


  like.&nbsp; The timings for CREATE TABLE should be consistent, of =
course, as=20


  it is the exact same command each time.&nbsp; The time spent in the =
PL/SQL=20



  loop should be roughly linear with the value of &lt;COUNTER&gt;, the =
point=20


  being that each ALLOCATE EXTENT takes roughly the same amount of =
time.&nbsp;=20


  You might observe an "elbow" in the plotted curve of timings at some =
point=20


  which Rachel suggested at 4000 but I think will vary depending on your =



  environment.&nbsp; On my laptop, I've seen the curve stay linear up =
into the=20


  100,000s.&nbsp; The time spent in DROP&nbsp;may not vary a great =
deal;&nbsp;=20


  it should be roughly linear with the value of COUNTER but I find that =
it is=20


  much better than linear, which leads me to believe that some parts of =
a=20


  DROP/TRUNCATE operation are asynchronous.</FONT></DIV>

  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>Try it out!</FONT></DIV>
  <BLOCKQUOTE dir=3Dltr=20
  style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =


BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">


    <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
    <DIV=20


    style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20


    <A title=3DRajendra.Jamadagni_at_espn.com=20
    href=3D"mailto:Rajendra.Jamadagni_at_espn.com">Jamadagni, Rajendra</A> =
</DIV>


    <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3DORACLE-L_at_fatcity.com=20


    href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list =
ORACLE-L</A>=20


    </DIV>


    <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Monday, September 30, =
2002 6:33=20


    AM</DIV>


    <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> RE: datafile sizing=20
    question</DIV>


    <DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial=20
size=3D2></FONT><BR></DIV>


    <P><FONT size=3D2>Rachel,</FONT> </P>
    <P><FONT size=3D2>Are there any studies or papers that test and =
explain this=20


    new magic 4000 extents number? My manager is excited about LMT, but =
no so=20


    excited about number of extents. So, if there is a good paper, I can =
make=20


    him feel happy about this ...</FONT></P>
    <P><FONT size=3D2>Thanks in advance</FONT> <BR><FONT =
size=3D2>Raj</FONT>=20


    <BR><FONT=20


    =

size=3D2>______________________________________________________</FONT>=20




    <BR><FONT size=3D2>Rajendra Jamadagni&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MIS, ESPN Inc.</FONT> =
<BR><FONT=20


    size=3D2>Rajendra dot Jamadagni at ESPN dot com</FONT> <BR><FONT =
size=3D2>Any=20


    opinion expressed here is personal and doesn't reflect that of ESPN =
Inc.=20


    </FONT><BR><FONT size=3D2>QOTD: Any clod can have facts, but having =
an opinion=20


    is an art!</FONT> </P><BR>


    <P><FONT size=3D2>-----Original Message-----</FONT> <BR><FONT =
size=3D2>From:=20


    Rachel Carmichael [<A=20


    =


href=3D"mailto:wisernet100_at_yahoo.com">mailto:wisernet100_at_yahoo.com</A>]</=
FONT>=20



    <BR><FONT size=3D2>Sent: Monday, September 30, 2002 7:03 AM</FONT> =
<BR><FONT=20


    size=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT=20
    size=3D2>Subject: RE: datafile sizing question</FONT> </P><BR>
    <P><FONT size=3D2>with evenly sized extents, there is no such thing =
as=20


    fragmentation anymore and Oracle can deal with objects with numbers =
of=20


    extents up to about 4000 before it starts to slow down a=20
  bit.</FONT></P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>



------_=_NextPart_001_01C26892.CD921686--



------=_NextPartTM-000-f5d1e002-7a7f-4f8a-90ce-50acb44ed359--


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infosys.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 - 11:08:38 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US