Is my database poorly constructed?

From: Steve <stevenmgarcia_at_hotmail.com>
Date: 26 Mar 2002 16:12:30 -0800
Message-ID: <4c049a8c.0203261612.1811bda8@posting.google.com>

I just ran a diagnostic tool on my database that I made from scratch
(using some scripts, not the DBA Configuration Assistant Tool). Then
I ran a diagnostic report I found off the web with the following results.

I'm not sure how to interpret some of these findings in this report. First, part of the report (if anyone wants to see the code for how this was generated, let me know. It's a monster SQL script).

............................Start Report.............................

Checking free space...

First summarize space available...

                                                Total Total Space
TABLESPACE_NAME                No of Files      Space (Megabytes)
------------------------------ ----------- ---------- -----------
RBS                                      1   15728640          15
SYSTEM                                   1   62914560          60
TEMP                                     1   20971520          20
TOOLS                                    1    1048576           1
USERS                                    1     524288          .5

Next summarize free space...

                         Free Free Space    Largest Largest Extent
TABLESPACE_NAME         Space     (Megs)     Extent    (Megabytes)
------------------ ---------- ---------- ---------- --------------
RBS                    966656    .921875     966656        .921875
SYSTEM               15454208 14.7382813   15454208     14.7382813
TEMP                 19730432 18.8164063   19730432     18.8164063
TOOLS                  534528 .509765625     534528     .509765625
USERS                  522240 .498046875     522240     .498046875

Space Report - Free Extents by Tablespace

TBspace MegsUsed MaxNextExtent FREE_EXTENTS UsableSpc_M FREESPACE_M
--------------- ---------- ------------- ------------ -----------

RBS 14.0761719 1048576 0 0 .92
SYSTEM 45.2578125 204800 75 14.65 14.74
TEMP 1.18164063 10240 1926 18.81 18.82
TOOLS .48828125 10240 52 .51 .51

Checking for tables with too many extents...

                     Object                        Number        Size
Owner  Tablespace    Type       Object Name    of Extents     (bytes)
------ ------------- ---------- -------------- ---------- -----------
SYS    TEMP          TEMPORARY  5.2                   121     1239040
SYS    SYSTEM        TABLE      SOURCE$                90    18237440
SYS    SYSTEM        INDEX      I_SOURCE1              44     8816640
SYS    SYSTEM        TABLE      IDL_UB1$               27     2672640
SYS    SYSTEM                   IDL_UB2$               25     2467840
SYS    SYSTEM                   VIEW$                  13     1239040
SYS    SYSTEM                   ARGUMENT$              11     1034240
SYS    SYSTEM        CLUSTER    C_OBJ#                 10     1976320
SYS    SYSTEM        INDEX      I_ARGUMENT1             9      829440
SYS    SYSTEM                   I_COL1                  8      747520
SYS    SYSTEM        ROLLBACK   SYSTEM                  8      409600

Checking Rollback Segments ...

Object Name Tablespace NEXT_EXTENT MIN_EXTENTS MaxEx PCT_INC STATUS
------------- ------------ ----------- ----------- ------- -------

SYSTEM SYSTEM 51200 2 121 0 ONLINE
RB1 RBS 1048576 2 121 0 ONLINE
RB2 RBS 1048576 2 121 0 ONLINE
RB3 RBS 1048576 2 121 0 ONLINE Checking on space usage and activity for each rollback Segment...

NAME ALLOC_MEGS Exts OPTIMAL_MEGS MAX_SIZE_MEGS --------------- ---------- ----- ------------ -------------

SYSTEM          .388671875     8                 .388671875
RB1             6.02734375     6                 6.02734375
RB2             2.00976563     2                 2.00976563
RB3             6.03320313     6                 6.03320313

Checking the Data Dictionary Cache Misses...

Data Dict Gets Data Dict Cache Misses Miss Percentage

-------------- ---------------------- ---------------
        116336                  12564      10.7997524

If the Miss Percentage above is greater than 10-15, you may need to tune your library cache usage by adding additional memory to the data dictionary cache.
Increasing memory is done by increasing the SHARED_POOL_SIZE parameter in the INITsid.ora file.

............................End Report.............................

My Questions...

I explicity dropped my temporary rollback segment that I assigned to my System Tablespace when I was initially creating my database
(it's in the scripts like

alter rollback segment rbs_temp offline; drop rollback segment rbs_temp;

Does the SYSTEM tablespace have some internal rollback segment that can't be removed? (I wouldn't want to remove it if it did!)

2) Is my rollback segment f*#^$ because there are no more free extents available for it?

3) anything else I should be alarmed about?

Thanks, Steve Received on Tue Mar 26 2002 - 18:12:30 CST