Re: Oracle System Tables - Performance SUX

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/06/30
Message-ID: <8ji98i$86p$1@nnrp1.deja.com>#1/1

In article <8jgdbv$tts$1_at_nnrp1.deja.com>, ddf_dba_at_my-deja.com wrote:
> In article <8jg1qo$k9o$1_at_nnrp1.deja.com>,
> Mark D Powell <markp7832_at_my-deja.com> wrote:
> > In article <962297072.4119.3.pluto.d4ee154e_at_news.demon.nl>,
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> > > There are none.
> > > One common source for performance problems is ANALYZEing the data
> > > dictionary. You shouldn't do that.
> > > The dictionary has been designed using the rule-based optimizer.
Try
> > > deleting the statistics (it won't harm when they are not there) by
issuing
> > > dbms_utility.analyze_schema('SYS','DELETE')
> > >
> > > Hth,
> > >
> > > Sybrand Bakker, Oracle DBA
> > >
> > > "Walter Dorninger" <walter.dorninger_at_aon.at> wrote in message
> > > news:8jfrut$1rn4$1_at_wrath.news.nacamar.de...
> > > > Hi,
> > > >
> > > > When I access the data dictionary (system tabels) of my oracle
database it
> > > > seems to be very (!) slow. Does anyone know how if there are
some
settings
> > > > to increase the performance of the data dictionary access ?
> > > >
> > > > I already placed the system datafile on a separate disk but the
performance
> > > > still sucks. There is also enough memory ... - so I don't have
an
idea.
> > > >
> > > > any suggestions ?
> > > > Thanks
> > > > Walter
> > > >
> > If you are running queries against sys objects either directly or
via
> > selects on dictionary views then Sybrand is correct in that there is
> > nothing you can do.
> >
> > However I would like to suggest that you:
> > 1) Verify that no non-SYS owned objects have been allocated in the
> > system tablespace except maybe the few system owned tables that
exist
> > immediately after an install.
> >
> > 2) If you are using the defalt shared pool size then since
dictionary
> > information is cached you may get some benefit during normal
operations
> > on the sys objects by increasing the shared pool size.
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >

> You might also check the size of the SYSTEM tablespace; a SYSTEM
> tablespace that is too small will experience the performance problems
> you mention. This may be overkill but I usually size the SYSTEM
> tablespace between 50 and 100 Meg.

> --
> David Fitzjarrell
> Oracle Certified DBA

>
The size of the system tablespace file should have no direct effect on performance. All you have done by adding another file is make more space available to be allocated to object extents, and since only the dictionary objects should be in system and a failure of a sys object to extend will result in DDL failure with alert log entries so you should know if that is happening. These facts would indicate that if you see a performance improvement upon adding a file to system that you probably have one of the following problems:

You have users with [the default] system as their temporary tablespace; this should be changed. Only sys should have system as their assigned temporary tablespace.
You have jobs that require sorts running as sys/internal that should probably be changed to run as a user
You have active rollback segments in the system tablespace; these should be moved to their own tablespace -- Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 30 2000 - 00:00:00 CDT