Re: Oracle System Tables - Performance SUX

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 --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

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


Sent via Deja.com http://www.deja.com/
Before you buy.