Re: Oracle System Tables - Performance SUX

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.