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: A 50 MB System Tablespace !? Ruminations on System Tablespac

RE: A 50 MB System Tablespace !? Ruminations on System Tablespac

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Thu, 18 Jan 2001 14:43:47 -0500
Message-Id: <10745.127061@fatcity.com>


This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C08186.F92ADD60
Content-Type: text/plain;

        charset="iso-8859-1"

View definitions, stored procedures, packages, etc. are stored in the system tablespace so when one create them a lot it will require a lot of space and if they are dropped this space is not freed.

Alex Hillman

-----Original Message-----
From: MacGregor, Ian A. [mailto:ian_at_SLAC.Stanford.EDU] Sent: Thursday, January 18, 2001 1:01 PM To: Multiple recipients of list ORACLE-L Subject: A 50 MB System Tablespace !? Ruminations on System Tablespace Fr

Who, these days can get along with a 50 MB system tablespace? I just moved a database from 7.3.4 to 8.1.6 via export/import. I precreated all the tablespaces allocating 1000 MB for
system. If I look at dbms_free_space for the system tablespace :

SQL> select bytes/1024/1024 from dba_free_space where tablespace_name =
'SYSTEM';
BYTES/1024/1024


      478.96094

I didn't order by block_id because I cannot fathom how the system database gets fragmented. Fragmentation comes from the dropping of database objects. One never drops anything from the system tablespace; unless, it was something which should not have been there in the first place. The idea of uniform extents is that when an object is dropped, or moved to another tablespace, the holes can be filled by the recreated object or a new one. This plugging cannot be done with objects of different extent sizes which are not multiples of each other. Eventually a hole will be too small to be used and "permanent" fragmentation results. But again this shouldn't happen in the system tablespace because objects are not dropped.

The other part of the system tablespace problem is that objects and extent sizes become too
large due to the 50% pctincrease value.

SQL> SELECT SUM(UNUSED_BLOCKS) FROM OBJECT_INFO   2 WHERE TABLESPACE_NAME = 'SYSTEM' AND RUN# = 138   3 /

SUM(UNUSED_BLOCKS)


              1429

OBJECT_INFO is a table I use to track object growth. It is appended to via dbms_space nightly.

SQL> SELECT 1429 * 8 /1024 FROM DUAL; 1429*8/1024



  11.164063

I use an 8k block size. So there are about 11 MB in unused blocks in the tablespace.
Looks like the tablespace could be crammed into a little over 500 MB. This is not a huge database. Total size of all objects in the database is slightly less that 27 GB.

Note in 8i not all objects in the system tablespace use a 50% pctincrease.

SQL> SELECT PCT_INCREASE, COUNT(PCT_INCREASE) FROM   2 DBA_SEGBY PCT_INCREASE
  4 /MENTS WHERE TABLESPACE_NAME = 'SYSTEM'   3 GROUP PCT_INCREASE COUNT(PCT_INCREASE)

------------ -------------------
           0                  31
          50                 384


AND the maximum number of extents differs as well

MAX_EXTENTS COUNT(MAX_EXTENTS)

----------- ------------------
          0                  1
         99                  1
        249                  1
        505                379
       4096                  4
  2.147E+09                 29

Wow an object which can have no more than 0 extents. That's the type sort of efficiency I like.

SQL> select segment_name, segment_type, blocks, extents   2 from dba_segments where max_extents = 0;

SEGMENT_NAME                   SEGMENT_TYPE          BLOCKS   EXTENTS
------------------------------ ------------------ --------- ---------
1.173                          CACHE                      1         1


I guess max_extents doesn't mean much here. I didn't even know there was a cache segment until
a few weeks ago. It has to do with marking the start of the non-bootstrap data dictionary elements.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

------_=_NextPart_001_01C08186.F92ADD60
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: A 50 MB  System Tablespace !? Ruminations on System =
Tablespace Fr</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>View definitions, stored procedures, packages, etc. =
are stored in the system tablespace so when one create them a lot it =
will require a lot of space and if they are dropped this space is not =
freed.</FONT></P>

<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: MacGregor, Ian A. [<A =
HREF=3D"mailto:ian_at_SLAC.Stanford.EDU">mailto:ian_at_SLAC.Stanford.EDU</A>]<=
/FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, January 18, 2001 1:01 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: A 50 MB System Tablespace !? Ruminations on =
System Tablespace</FONT>
<BR><FONT SIZE=3D2>Fr</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Who, these days can get along with a 50 MB system =
tablespace?&nbsp; I just moved a database from 7.3.4 to 8.1.6 via =
export/import.&nbsp; I precreated all the tablespaces allocating 1000 =
MB for</FONT></P>

<P><FONT SIZE=3D2>system.&nbsp; If I look at dbms_free_space for the =
system tablespace&nbsp; :</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; select bytes/1024/1024 from dba_free_space =
where tablespace_name =3D 'SYSTEM';</FONT>
</P>

<P><FONT SIZE=3D2>BYTES/1024/1024</FONT>
<BR><FONT SIZE=3D2>---------------</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 478.96094</FONT>
</P>

<P><FONT SIZE=3D2>I didn't order by block_id because I cannot fathom =
how the system database gets fragmented.&nbsp; Fragmentation&nbsp; =
comes from the dropping of database objects.&nbsp; One never drops =
anything from the system tablespace; unless, it was something which =
should not have been there in the first place.&nbsp; The idea of =
uniform extents is that when an object is dropped, or moved to another =
tablespace, the holes&nbsp; can be filled by the recreated object or a =
new one.&nbsp; This plugging cannot be done with objects of different =
extent sizes which are not multiples of each other.&nbsp; Eventually a =
hole will be too small to be used and &quot;permanent&quot; =
fragmentation results.</FONT></P>

<P><FONT SIZE=3D2>But again this shouldn't happen in the system =
tablespace because objects are not dropped.</FONT>
</P>

<P><FONT SIZE=3D2>The other part of the&nbsp; system tablespace problem =
is that objects and extent sizes become too</FONT>
<BR><FONT SIZE=3D2>large due to the 50% pctincrease value.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; SELECT SUM(UNUSED_BLOCKS) FROM =
OBJECT_INFO</FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; WHERE TABLESPACE_NAME =3D 'SYSTEM' =
AND RUN# =3D 138</FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; /</FONT>
</P>

<P><FONT SIZE=3D2>SUM(UNUSED_BLOCKS)</FONT>
<BR><FONT SIZE=3D2>------------------</FONT>
<BR><FONT =
SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp; 1429</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>OBJECT_INFO is a table I use to track object =
growth.&nbsp; It is appended to via dbms_space nightly.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; SELECT 1429 * 8 /1024 FROM DUAL;</FONT>
</P>

<P><FONT SIZE=3D2>1429*8/1024</FONT>
<BR><FONT SIZE=3D2>-----------</FONT>
<BR><FONT SIZE=3D2>&nbsp; 11.164063</FONT>
</P>

<P><FONT SIZE=3D2>I use an 8k block size.&nbsp;&nbsp; So there are =
about&nbsp; 11 MB in unused blocks in the tablespace. </FONT>
<BR><FONT SIZE=3D2>Looks like the tablespace could be crammed into a =
little over 500 MB.&nbsp; This is not a huge database.&nbsp; Total size =
of all objects in the database is slightly less that 27 GB.&nbsp; =
</FONT></P>

<P><FONT SIZE=3D2>Note in 8i not all objects in the system&nbsp; =
tablespace use a 50% pctincrease.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; SELECT PCT_INCREASE, COUNT(PCT_INCREASE) FROM =
</FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; DBA_SEGBY PCT_INCREASE</FONT>
<BR><FONT SIZE=3D2>&nbsp; 4&nbsp; /MENTS WHERE TABLESPACE_NAME =3D =

'SYSTEM'</FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; GROUP </FONT> </P> <P><FONT SIZE=3D2>PCT_INCREASE COUNT(PCT_INCREASE)</FONT> <BR><FONT SIZE=3D2>------------ -------------------</FONT> <BR><FONT = SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp= ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31</FONT> <BR><FONT = SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp; 384</FONT> </P> <BR> <P><FONT SIZE=3D2>AND the maximum number of extents&nbsp; differs as = well</FONT> </P> <P><FONT SIZE=3D2>MAX_EXTENTS COUNT(MAX_EXTENTS)</FONT> <BR><FONT SIZE=3D2>----------- ------------------</FONT> <BR><FONT = SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp= ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 99&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 249&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 505&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp; 379</FONT> <BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 4096&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n= bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4</FONT> <BR><FONT SIZE=3D2>&nbsp; = 2.147E+09&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29</FONT> </P> <P><FONT SIZE=3D2>Wow&nbsp; an object&nbsp; which can have no more than = 0 extents.&nbsp; That's the type sort of&nbsp; efficiency I = like.</FONT> </P> <P><FONT SIZE=3D2>SQL&gt; select segment_name, segment_type, blocks, = extents </FONT> <BR><FONT SIZE=3D2>&nbsp; 2&nbsp; from dba_segments where max_extents = =3D 0;</FONT> </P> <P><FONT = SIZE=3D2>SEGMENT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb= sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = SEGMENT_TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = BLOCKS&nbsp;&nbsp; EXTENTS</FONT> <BR><FONT SIZE=3D2>------------------------------ ------------------ = --------- ---------</FONT> <BR><FONT = SIZE=3D2>1.173&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p;&nbsp;&nbsp;&nbsp; = CACHE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&= nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1</FONT> </P> <BR> <P><FONT SIZE=3D2>I guess max_extents doesn't mean much here.&nbsp; I = didn't even know there was a cache segment until</FONT> <BR><FONT SIZE=3D2>a few weeks ago.&nbsp; It has to do with marking the = start of the non-bootstrap data dictionary elements.</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2>Ian MacGregor</FONT> <BR><FONT SIZE=3D2>Stanford Linear Accelerator Center</FONT> <BR><FONT SIZE=3D2>ian_at_slac.stanford.edu</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -------------------------------</FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: MacGregor, Ian A.</FONT> <BR><FONT SIZE=3D2>&nbsp; INET: ian_at_SLAC.Stanford.EDU</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) = 538-5051&nbsp; FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
Received on Thu Jan 18 2001 - 13:43:47 CST

Original text of this message

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