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

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 18 Jan 2001 13:20:53 -0800
Message-Id: <10745.127080@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.

--Boundary_(ID_x5d7oVUX8Rv3ImQuGH11eA)

Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7BIT

But, in dropping the definitions stated below one is not dropping objects, but rows in various data dictionary objects. Why would this lead to fragmentation at the tablespace level? Also your statement suggests that the blocks in these tables which are no longer in use cannot be reused. If you mean by another data dictionary object, then I have to agree; however, surely, they are available to be used by the same data dictionary object.

I don't know if the source code for a package must be stored contiguously. It wouldn't surprise me at all if it did. Since it is more common to add code to a package than subtract it, the contiguous area needed to store the code would be larger as well. If one cannot be found then the table which stores the code has to grow. This can lead to an oversized "source" table, which leads to an oversized systems tablespace, but I fail to see how it fragments the system tablespace.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu <mailto:ian_at_slac.stanford.edu>

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

From: Alex Hillman [mailto:alex_hillman_at_physia.com] Sent: Thursday, January 18, 2001 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: A 50 MB System Tablespace !? Ruminations on System Tablespac

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

--Boundary_(ID_x5d7oVUX8Rv3ImQuGH11eA)

Content-type: text/html; charset=iso-8859-1 Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: A 50 MB System Tablespace !? Ruminations on System Tablespace Fr</TITLE>

<META content="MSHTML 5.00.2919.6307" name=GENERATOR></HEAD>

<BODY>
<DIV>But,  in dropping the definitions stated below one is not dropping objects, but rows in various data dictionary objects.   Why would this lead to fragmentation at the tablespace level?  Also your statement suggests that the blocks in these tables  which are no longer in use</DIV> <DIV>cannot be reused.  If you mean by another data dictionary object,   then I have to agree; however, surely,  they are available to be used by the same data dictionary object.</DIV>
<DIV> </DIV> <DIV>I don't know if the source code for a package must be stored  contiguously.  It wouldn't surprise me at all if it did.  Since it is more common to add code to a package than subtract it, the contiguous area needed to store the code would be larger as well.   If one cannot be found then the table which stores the code has to grow.  This can lead to an oversized "source" table, which leads to an </DIV> <DIV>oversized systems tablespace, but I fail to see how it fragments the system tablespace.</DIV> <DIV> </DIV>
<DIV>Ian MacGregor</DIV>
<DIV>Stanford Linear Accelerator Center</DIV> <DIV><A href="mailto:ian_at_slac.stanford.edu">ian_at_slac.stanford.edu</A></DIV> <BLOCKQUOTE style="MARGIN-RIGHT: 0px">
 <DIV align=left class=OutlookMessageHeader dir=ltr>-----Original Message----- From: Alex Hillman [mailto:alex_hillman_at_physia.com] Sent: Thursday, January 18, 2001 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: A 50 MB System Tablespace !? Ruminations on System Tablespac </DIV>
 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. [<A
 href="mailto:ian_at_SLAC.Stanford.EDU">mailto:ian_at_SLAC.Stanford.EDU</A>] 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';

  <P><FONT size=2>BYTES/1024/1024</FONT> <BR><FONT size=2>---------------</FONT> 
  <BR><FONT size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 478.96094</FONT> </P>
  <P><FONT size=2>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) <FONT

  size=2>------------------</FONT> <BR><FONT

size=2>              1429 
OBJECT_INFO is a table I use to track object growth.  It is appended to via dbms_space nightly.

  <P><FONT size=2>SQL&gt; SELECT 1429 * 8 /1024 FROM DUAL;</FONT> </P>
  <P><FONT size=2>1429*8/1024</FONT> <BR><FONT size=2>-----------</FONT> 
  <BR><FONT size=2>&nbsp; 11.164063</FONT> </P>
  <P><FONT size=2>I use an 8k block size.&nbsp;&nbsp; So there are about&nbsp;

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

  </FONT><BR><FONT size=2>&nbsp; 2&nbsp; DBA_SEGBY PCT_INCREASE</FONT> <BR><FONT 
  size=2>&nbsp; 4&nbsp; /MENTS WHERE TABLESPACE_NAME = 'SYSTEM'</FONT> <BR><FONT 
  size=2>&nbsp; 3&nbsp; GROUP </FONT></P>

PCT_INCREASE COUNT(PCT_INCREASE) <FONT

  size=2>------------ -------------------</FONT> <BR><FONT

size=2>           0                  31           50                 384

  <P><FONT size=2>AND the maximum number of extents&nbsp; differs as well</FONT> 
  </P>
  <P><FONT size=2>MAX_EXTENTS COUNT(MAX_EXTENTS)</FONT> <BR><FONT 
  size=2>----------- ------------------</FONT> <BR><FONT

size=2>          0                 

  1</FONT> <BR><FONT size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  99&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
  1</FONT> <BR><FONT size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

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

  size=2>------------------------------ ------------------ ---------

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

  size=2>----------------------------------------------------------------------------------------------</FONT>

-- Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com"
target=_blank>http://www.orafaq.com</A> -- 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 <FONT

  size=2>--------------------------------------------------------------------</FONT>

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 Received on Thu Jan 18 2001 - 15:20:53 CST