Re: Increase tablespace, which way is better?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Sun, 20 Jul 2003 20:36:27 +0300
Message-Id: <25968.338601@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0086_01C34EFE.98458B20
Content-Type: text/plain;

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

Hi!

Also, there is (or was?) a tradeoff between size and number of = datafiles.

Before 8i, if I recall correctly, all datafile headers had to be = physically visited (header SCN changes were written) before a checkpoint = could finish. If you got thousands of files, this adds significant = overhead. Starting from 8i you actually have to do same thing, but with = incremenal checkpointing, the datafile headers are updated in groups, = thus distributing the jump in IO load over longer period of time.=20

In 6.0, I believe there was no checkpoint process and logwriter was the = one responsible updating all file headers. When log buffer was full, all = transactions were halt. Others can comment it, because I haven't worked = with O 6.

Another thing with datafile sizes was recovery in case of corrupt block = - it's faster to restore and recover a 1GB file than a 10GB file, but = with 9i RMAN block level recovery this problem is relieved as well.

And last, a lot of people have had problems with file sizes over 2GB, = even in last few years. Some filesystems simply didn't support files = over 2GB, others could corrupt the whole file when extended over 2GB = (8.1.5 on linux for example). That's why sizes like 1G or 2040 MB were = used a lot (by me;)

Tanel.

"Liu, Jack" <jliu_at_atla.com> wrote:

      Hi,=20
      I want to increase tablespace, just want to know which way is =
better:
      1. 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_0086_01C34EFE.98458B20
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>Also, there is (or was?) a tradeoff =

between size=20
and number of datafiles.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Before 8i, if I recall correctly, = all datafile=20
headers had to be physically visited (header SCN changes were = written)=20
before a checkpoint could finish. If you got thousands of files, this = adds=20
significant overhead. Starting from 8i you actually have to do same = thing, but=20
with incremenal checkpointing, the datafile headers are updated in = groups, thus=20
distributing the jump in IO load over longer period of time. =

</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>In 6.0, I believe there was no =

checkpoint process=20
and logwriter was the one responsible updating all file headers. When = log buffer=20
was full, all transactions were halt. Others can comment it, because I = haven't=20
worked with O 6.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Another thing with datafile sizes was = recovery in=20
case of corrupt block - it's faster to restore and recover a 1GB file = than a=20
10GB file, but with 9i RMAN block level recovery this problem is = relieved as=20

well.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>And last, a lot of people have had =

problems with=20
file sizes over 2GB, even in last few years. Some filesystems simply = didn't=20
support files over 2GB, others could corrupt the whole file when = extended over=20
2GB (8.1.5 on linux for example). That's why sizes like 1G or 2040 MB = were used=20
a lot (by me;)</FONT></DIV>

<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Tanel.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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"><BR><B><I>"Liu, Jack"=20 <jliu_at_atla.com></I></B> wrote:</DIV> <BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

<BLOCKQUOTE=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff = 2px solid; WIDTH: 100%">

      <META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
      <STYLE></STYLE>

      <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>&nbsp;</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>&nbsp;</DIV>
      <DIV><FONT face=3D"Times New Roman" =
color=3D#0000ff>Thanks,</FONT></DIV>
      <DIV>&nbsp;</DIV>
      <DIV><FONT face=3D"Times New Roman"><FONT =
color=3D#0000ff>Jack<SPAN=20
      class=3D380473019-18072003>&nbsp;</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 = Received on Sun Jul 20 2003 - 12:36:27 CDT