by Walter Dorninge » Sun, 31 Dec 1899 09:00:00
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
by Sybrand Bakke » Sun, 31 Dec 1899 09:00:00
Hth,
Sybrand Bakker, Oracle DBA
Quote:> 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
by Mark D Powel » Sun, 31 Dec 1899 09:00:00
> Hth,
> Sybrand Bakker, Oracle DBA
> > 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
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.
by ddf_.. » Sun, 31 Dec 1899 09:00:00
> > Hth,
> > Sybrand Bakker, Oracle DBA
> > > 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.
--
David Fitzjarrell
Oracle Certified DBA
Sent via Deja.com http://www.deja.com/
Before you buy.
by Mark D Powel » Sun, 31 Dec 1899 09:00:00
> > > Hth,
> > > Sybrand Bakker, Oracle DBA
> > > > 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
1) 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.
2) You have jobs that require sorts running as sys/internal that
should probably be changed to run as a user
3) 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.
1. Why the optimizer sux..or sux I ?
Help me to believe in the Oracle 8i CBO:
Assume the following table:
table ojs_main(docnum number primary key ,heading char(4));
The "heading" column is also index.
When I do a simple search like:
select docnum from ojs_main where heading='CONS' or heading='PARL'
I get an optimal execution plan by using a range scan on the index
of the "heading" column. When I change the query to
select docnum from ojs_main where heading='CONS' or heading='PARX'
the optimizer suddenly uses a full table scan for the table ojs_main
with much more consistent gets and physical reads. The complete
table and all indexes are full analyzed.
So can someone explain me this behaviour ?
Andreas
2. PICK Visual Basic Programmer Needed- Wayne,NJ
3. Thanks for responses on System Performance - it is update performance
5. How is the performance difference between a nonclustered system and a clustered system
6. MDAC & VFP
7. performance question.... (dev system versus prod system)
9. UNICODE Sux!
10. US-GA-WIRELESS SYSTEMS PERFORMANCE / RF ENGINEER / ORACLE
11. IL-CHICAGO-78545--Post Sales Support-Windows NT-ORACLE-SYSTEM PERFORMANCE CONSULTANTS