| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: A 50 MB System Tablespace !? Ruminations on System Tablespac
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
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? 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</FONT></P> <P><FONT SIZE=3D2>system. If I look at dbms_free_space for the = system tablespace :</FONT> </P> <P><FONT SIZE=3D2>SQL> 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> 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. 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.</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 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> SELECT SUM(UNUSED_BLOCKS) FROM = OBJECT_INFO</FONT> <BR><FONT SIZE=3D2> 2 WHERE TABLESPACE_NAME =3D 'SYSTEM' = AND RUN# =3D 138</FONT> <BR><FONT SIZE=3D2> 3 /</FONT> </P> <P><FONT SIZE=3D2>SUM(UNUSED_BLOCKS)</FONT> <BR><FONT SIZE=3D2>------------------</FONT> <BR><FONT = SIZE=3D2> &nb= sp; 1429</FONT> </P> <BR> <P><FONT SIZE=3D2>OBJECT_INFO is a table I use to track object = growth. It is appended to via dbms_space nightly.</FONT> </P> <P><FONT SIZE=3D2>SQL> 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> 11.164063</FONT> </P> <P><FONT SIZE=3D2>I use an 8k block size. So there are = about 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. This is not a huge database. Total size = of all objects in the database is slightly less that 27 GB. = </FONT></P> <P><FONT SIZE=3D2>Note in 8i not all objects in the system = tablespace use a 50% pctincrease.</FONT> </P> <P><FONT SIZE=3D2>SQL> SELECT PCT_INCREASE, COUNT(PCT_INCREASE) FROM = </FONT> <BR><FONT SIZE=3D2> 2 DBA_SEGBY PCT_INCREASE</FONT> <BR><FONT SIZE=3D2> 4 /MENTS WHERE TABLESPACE_NAME =3D =Received on Thu Jan 18 2001 - 13:43:47 CST
'SYSTEM'</FONT>
<BR><FONT SIZE=3D2> 3 GROUP </FONT> </P> <P><FONT SIZE=3D2>PCT_INCREASE COUNT(PCT_INCREASE)</FONT> <BR><FONT SIZE=3D2>------------ -------------------</FONT> <BR><FONT = SIZE=3D2> = 0  = ; 31</FONT> <BR><FONT = SIZE=3D2> = 50 &nbs= p; 384</FONT> </P> <BR> <P><FONT SIZE=3D2>AND the maximum number of extents differs as = well</FONT> </P> <P><FONT SIZE=3D2>MAX_EXTENTS COUNT(MAX_EXTENTS)</FONT> <BR><FONT SIZE=3D2>----------- ------------------</FONT> <BR><FONT = SIZE=3D2> = 0  = ; 1</FONT> <BR><FONT SIZE=3D2> = 99 &nbs= p; 1</FONT> <BR><FONT SIZE=3D2> = 249 &nb= sp; 1</FONT> <BR><FONT SIZE=3D2> = 505 &nb= sp; 379</FONT> <BR><FONT SIZE=3D2> = 4096 &n= bsp; 4</FONT> <BR><FONT SIZE=3D2> = 2.147E+09 &nb= sp; 29</FONT> </P> <P><FONT SIZE=3D2>Wow an object which can have no more than = 0 extents. That's the type sort of efficiency I = like.</FONT> </P> <P><FONT SIZE=3D2>SQL> select segment_name, segment_type, blocks, = extents </FONT> <BR><FONT SIZE=3D2> 2 from dba_segments where max_extents = =3D 0;</FONT> </P> <P><FONT = SIZE=3D2>SEGMENT_NAME &nb= sp; = SEGMENT_TYPE = BLOCKS EXTENTS</FONT> <BR><FONT SIZE=3D2>------------------------------ ------------------ = --------- ---------</FONT> <BR><FONT = SIZE=3D2>1.173 &nbs= p; &nbs= p; = CACHE &= nbsp; = 1 1</FONT> </P> <BR> <P><FONT SIZE=3D2>I guess max_extents doesn't mean much here. I = didn't even know there was a cache segment until</FONT> <BR><FONT SIZE=3D2>a few weeks ago. 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> INET: ian_at_SLAC.Stanford.EDU</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- 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>
 |
 |