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