Avoiding
misuse of the SYSTEM tablespace that could lead to poor database performance
Author: Alex Caminals Introduction
In
any Oracle database, the SYSTEM tablespace contains all the internal objects used
by the database server. These objects are tables, views, packages, procedures,
functions, triggers, sequences, etc. Whenever a SQL statement is sent to the database,
many operations such as checking permissions on the tables and views during the
parse of the SQL statement require access to the information stored in the SYSTEM
tablespace. Allowing users to create their own tables in this tablespace is a
bad practice as that will affect the overall database performance.
When
a user is created in an Oracle database, an optional clause to specify the default
tablespace is available. Of course, a user can be granted permissions to create
tables and indexes to other tablespaces, but there is always a default tablespace
for each user. If it is not specified in the CREATE USER statement, the default
tablespace for that user will be set to the SYSTEM tablespace. So when a new table
or index is created by that user and no tablespace is specified in the storage
clause of the CREATE TABLE or CREATE INDEX statement, it will be created in the
SYSTEM tablespace.
Having
user objects created in the SYSTEM tablespace neither produces an error nor restricts
its functionality. However it has performance implications. As outlined above,
the SYSTEM tablespace stores all the Oracle catalog information and it is therefore
accessed in almost every query. In a multi-user environment that means a lot of
disk accesses where the tablespace datafiles are stored. So, if additionally user
data is stored in the SYSTEM tablespace, an overhead to the already high workload
of the disks containing its datafiles will be added and as a consequence the overall
database performance will be impacted.
Recommendation
The recommendation
is therefore to always have a default tablespace for each user. It can be specified
in the CREATE USER statement when a user is created and in the ALTER USER statement
if the user already exists. By following this best practice for every user, this
source of poor database performance can be avoided.
Go
to top
|
