Answers embedded.
HJR
--
------------------------------------------
Resources for Oracle : www.hjrdba.com
============================
"Steve" <stevenmgarcia_at_hotmail.com> wrote in message
news:4c049a8c.0203261612.1811bda8_at_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.
[Snip Report]
> My Questions...
>
> 1) 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!)
>
Daniel M. can answer this one for you, if you like! But yes, there is
indeed a system rollback segment created for each and every database you
create (if you want to see what created it, have a look at the first
operational lines of a script called sql.bsq in the ORACLE_HOME/rdbms/admin
directory. Sql.bsq is ivoked automatically by the 'create database'
command). It is impossible to create a database without it (because if it
were, there'd be nothing to handle the initial creation and population of
the data dictionary tables), and it's impossible to remove it or take it
offline after database creation (try it: the system simply won't let you get
away with it).
> 2) Is my rollback segment f*#^$ because there are no more free
> extents available for it?
No, because provided it's not hit its maxextents setting, and provided
there's room enough in its tablespace, it will automatically acquire as many
extra extents as it needs to do its thing. Extent allocation is not a good
thing to happen, though, unless it's in locally managed tablespace where the
performance dip is not significant. So the usual advice is along the lines
of: rollback segment should be as big as your biggest transaction, or the
sum of your largest concurrent transactions, which ever is the bigger.
>
> 3) anything else I should be alarmed about?
>
Well, the report itself mentioned a low data dictionary cache hit ratio, but
I'm not sure if that's significant or not. If you ran this tool just after
database creation, it's not. If you ran it after doing some typical
workload for a reasonable period, then it might be.
I don't know why all your hand-crafted rollback segments have a maxextents
of 121. That's just daft. There's no reason for the max to be set that
low, unless you're an Oracle 7.0 user with a 2K blocksize. I'd up that to
about 500 or so. On the other hand, I'd probably create them in locally
managed tablespace and set the maximum to unlimited.
The formatting of the report lost a bit in translation, so I *think* you've
not set optimal for any of your rollback segs. That's fine (some here might
tell you otherwise), because optimal causes automatic shrinks, and automatic
shrinks at a time and place of Oracle's choosing is not such a good idea.
Otherwise, nothing else too obvious. A database version would have been
nice, either in the report or direct from you.
Regards
HJR
> Thanks, Steve
Received on Tue Mar 26 2002 - 19:33:55 CST
