Re: Increase tablespace, which way is better?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Sun, 20 Jul 2003 14:55:30 +0300
Message-Id: <25968.338590@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_009A_01C34ECE.F67BBDC0
Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi!

In multi-user environments IO times won't probably be much slower with = 1M extensions, because disk reading heads will very likely move away = from original location right after first request to serve other sessions = request. Also, if you think about indexed access to tables, then data = from completely different locations of Oracle datafiles are read anyway.

You just should make sure that your extent sizes match the (multiple of) = size of db_block_size * db_file_multiblock_read_count. That way if you = even got 32 block size * 64 d_f_m_r_c =3D 2MB and autoextend 1M you = won't have any problem, because if extent size is 2MB, 2MB is = autoextended anyway.

I personally prefer to precreate datafiles (usually with uniform sizes) = for highly loaded systems and monitor space in them, for the reason that = otherwise lot's of extra IO is generated when extending 100M for = example. Also, the session extending segment, has to wait until the = extension is complete... This might cause locking explosions etc.. in = high-concurrency systems etc.. Instead, I monitor the free space inside = tablespaces and add datafiles during low activity periods.

But for small databases in sense of disk usage and load, I usually use = autoextend and monitor free space on disk just to avoid extra = maintenance.

Tanel.

Original Message -----=20 From: Jos=20 To: Multiple recipients of list ORACLE-L=20 Sent: Saturday, July 19, 2003 5:49 PM Subject: Re: Increase tablespace, which way is better?

Please correct me if I am wrong. With option 1, the datafile will = dynamically extend, 1M at a time and there is no guarantee the datafile = is contiguous on disk. That will increase your I/O time when Oracle need = to find data all over the disk. And also with autoextend, you may run = out of space on that disk at any time (ie someone else created a big = datafile on that disk), with option 2 at least you know you have 100M = available for that datafile on the disk. Jos

  "Liu, Jack" <jliu_at_atla.com> wrote:
    Hi,=20
    I want to increase tablespace, just want to know which way is = better:

ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/oradata/orcl/users02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 100M;

2.alter tablespace system add datafile = '/u01/oradata/orcl/users02.dbf' size 100m;

Thanks,

Jack=20

-------------------------------------------------------------------------=

Yahoo! Mobile

Check & compose your email via SMS on your Telstra or Vodafone = mobile.
------=_NextPart_000_009A_01C34ECE.F67BBDC0
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=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 6.00.2800.1141" name=3DGENERATOR></HEAD>
<BODY bgColor=3D#e0e0e0>
<DIV><FONT face=3DArial size=3D2>Hi!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>In multi-user environments IO times =

won't probably=20
be much slower with 1M extensions, because disk reading heads will very = likely=20
move away from original location right after first request to serve = other=20
sessions request. Also, if you think about indexed access to tables, = then data=20
from completely different locations of Oracle datafiles are read=20 anyway.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>You just should make sure that your = extent sizes=20
match the (multiple of) size of db_block_size * = db_file_multiblock_read_count.=20
That way if you even got 32 block size * 64 d_f_m_r_c =3D 2MB and = autoextend 1M=20
you won't have any problem, because if extent size is 2MB, 2MB is = autoextended=20
anyway.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I personally prefer to precreate = datafiles (usually=20
with uniform sizes) for highly loaded systems and monitor space in them, = for the=20
reason that otherwise lot's of extra IO is generated when extending 100M = for=20
example. Also, the session extending segment, has to wait until the = extension is=20
complete... This might cause locking explosions etc.. in = high-concurrency=20
systems etc.. Instead, I monitor the free space inside tablespaces and = add=20
datafiles during low activity periods.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>But for small databases in sense = of disk=20
usage and load, I usually use autoextend and monitor free space on = disk=20
just to avoid extra maintenance.</FONT></DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Tanel.</FONT></DIV>
<BLOCKQUOTE=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=3Djos1992831_at_yahoo.com.au =
href=3D"mailto:jos1992831_at_yahoo.com.au">Jos</A>=20   </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> Saturday, July 19, 2003 = 5:49=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Re: Increase = tablespace, which=20
  way is better?</DIV>
  <DIV><BR></DIV>
  <DIV>Please correct me if I am wrong. With option 1, the datafile will =

  dynamically extend, 1M at a time and there is no guarantee the = datafile is=20
  contiguous on disk. That will increase your I/O time when Oracle need = to find=20
  data all over the disk. And also with autoextend, you may run out of = space on=20
  that disk at any time (ie someone else created a big datafile on that = disk),=20
  with option 2 at least you know you have 100M available for that = datafile on=20
  the disk.</DIV>
  <DIV>Jos<BR><BR><B><I>"Liu, Jack" <jliu_at_atla.com></I></B> = wrote:</DIV>
  <BLOCKQUOTE=20
  style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px = solid; WIDTH: 100%">

<DIV><FONT face=3D"Times New Roman" color=3D#0000ff>Hi, <BR>I want = to increase=20

    tablespace, just want to know which way is better:<BR>1. ALTER = TABLESPACE=20
    SYSTEM<BR>ADD DATAFILE '/u01/oradata/orcl/users02.dbf'<BR>SIZE=20     1M<BR>AUTOEXTEND ON<BR>NEXT 1M<BR>MAXSIZE 100M;</FONT></DIV>     <DIV> </DIV>
    <DIV><FONT face=3D"Times New Roman" color=3D#0000ff>2.alter = tablespace system=20

add datafile '/u01/oradata/orcl/users02.dbf' size 100m;</FONT></DIV> <DIV> </DIV>
<DIV><FONT face=3D"Times New Roman" = color=3D#0000ff>Thanks,</FONT></DIV>

<DIV> </DIV>
<DIV><FONT face=3D"Times New Roman"><FONT color=3D#0000ff>Jack<SPAN=20 class=3D380473019-18072003> </SPAN></FONT></FONT></DIV> <DIV><FONT face=3D"Times New Roman"><FONT color=3D#0000ff><SPAN=20 =
class=3D380473019-18072003></SPAN></FONT></FONT> </DIV></BLOCKQUOTE>=

  <P><BR>
  <HR SIZE=3D1>
  <A=20

=
href=3D"http://au.rd.yahoo.com/mail/tagline/?http://au.mobile.yahoo.com/s= ms/mail/index.html"=20
target=3D_blank><B>Yahoo! Mobile</B></A><BR>- Check & compose your = Received on Sun Jul 20 2003 - 06:55:30 CDT