Building a Database That Maximizes Performance

Building a Database That Maximizes Performance

Enforce that every tablespace ever created in the database must be locally

managed. Locally managed tablespaces deliver better performance than the

deprecated dictionary-managed technology.

Ensure users are automatically assigned a default permanent tablespace. This

guarantees that when users are created they are assigned a default tablespace

other than SYSTEM. You don’t want users ever creating objects in the SYSTEM

tablespace, as this can adversely affect performance and availability.

Ensure users are automatically assigned a default temporary tablespace. This

guarantees that when users are created they are assigned a temporary tablespace

other than SYSTEM. You don’t ever want users using the SYSTEM tablespace for a

temporary sorting space, as this can adversely affect performance and availability.

*******************************************************

When you have the choice, tablespaces should always be created with the following two features

enabled:

• Locally managed

• Automatic segment space management (ASSM)

Here’s an example of creating a tablespace that enables the prior two features:

Locally managed tablespaces are more efficient than dictionary-managed tablespaces. This feature

is enabled via the EXTENT MANAGEMENT LOCAL clause. Furthermore, if you created your database with the

SYSTEM tablespace as locally managed, you will not be permitted to later create a dictionary-managed

tablespace. This is the desired behavior.

*******************************************************

The ASSM feature allows for Oracle to manage many of the storage characteristics that formerly had

to be manually adjusted by the DBA on a table-by-table basis. ASSM is enabled via the SEGMENT SPACE

MANAGEMENT AUTO clause. Using ASSM relieves you of these manual tweaking activities. Furthermore,

some of Oracle’s space management features (such as shrinking a table and SecureFile LOBs) are

allowed only when using ASSM tablespaces. If you want to take advantage of these features, then you

must create your tablespaces using ASSM.

******************************************************

You can choose to have the extent size be consistently the same for every extent within the

tablespace via the UNIFORM SIZE clause. Alternatively you can specify AUTOALLOCATE. This allows Oracle to

allocate extent sizes of 64 KB, 1 MB, 8 MB, and 64 MB. You may prefer the auto-allocation behavior if the

objects in the tablespace typically are of varying size.

*******************************************************

In prior versions of Oracle, DBAs would spend endless hours monitoring and modifying the physical

space management aspects of a table. The combination of locally managed and ASSM render many of

these space settings obsolete. For example, the storage parameters are not valid parameters in locally

managed tablespaces:

• NEXT

• PCTINCREASE

• MINEXTENTS

• MAXEXTENTS

• DEFAULT

The SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage physical space within the

block. When you use this clause, there is no need to specify parameters such as the following:

• PCTUSED

• FREELISTS

• FREELIST GROUPS