| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
 |
 |