Creating Tablespaces to Maximize Performance for Oracle Database

Tablespaces to Maximize Performance for Oracle

Problem

You realize that tablespaces are the logical containers for Oracle database objects such as tables and indexes. Furthermore, you’re aware that if you don’t specify storage attributes when creating objects, then the tables and indexes automatically inherit the storage characteristics of the tablespaces (that the tables and indexes are created within). Therefore you want to create tablespaces in a manner that maximizes table performance and maintainability.

Solution

We recommend that you create your tablespaces with the locally managed and automatic segment space management features (ASSM) enabled. This is the default behavior starting with Oracle Database 12c:

create tablespace tools
       datafile '/u01/dbfile/O12C/tools01.dbf' size 100m;

 You can verify that the tablespace was created locally managed and is using ASSM via this query:

select tablespace_name, extent_management, segment_space_management
from dba_tablespaces
where tablespace_name='TOOLS';


Here is some sample output:

TABLESPACE_NAME  EXTENT_MANAGEMENT       SEGMENT_SPACE_MANAGEMENT
---------------- ----------------------- -------------------------
TOOLS            LOCAL                   AUTO

How It Works

 

To be clear, this recipe discusses two separate desirable tablespace features:

  • Locally managed tablespaces
  • Automatic Segment Space Management (ASSM)

Starting with Oracle Database 12c, all tablespaces are created as locally managed. In prior versions of Oracle you had the choice of either locally managed or dictionary managed. Going forward you should always use locally managed tablespaces.

The tablespace segment space management feature can be set to either AUTO (the default) or MANUAL. Oracle strongly recommends that you use AUTO (referred to as ASSM). This allows Oracle to automatically manage many physical space characteristics that the DBA had to previously manually adjust. In most scenarios, an ASSM managed tablespace will process transactions more efficiently than a MANUAL segment space management enabled tablespace. There are a few corner cases where this may not be true. We recommend that you use ASSM unless you have a proven test case where MANUAL is better.

 Note  You cannot create the SYSTEM tablespace with the ASSM feature. Also, the ASSM feature is valid only for permanent, locally managed tablespaces.

When creating a tablespace, if you don’t specify a uniform extent size, then Oracle will automatically allocate extents is sizes of 64 KB, 1 MB, 8 MB, and 64 MB. Use the auto-allocation behavior if the objects in the tablespace typically are of varying size. You can explicitly tell Oracle to automatically determine the extent size via the EXTENT MANAGEMENT LOCAL AUTOALLOCATE clause.

You can choose to have the extent size be consistently the same for every extent within the tablespace via the UNIFORM SIZE clause. This example uses a uniform extent size of 128k:

create tablespace tools
  datafile '/u01/dbfile/O12C/tools01.dbf' size 100m
  extent management local uniform size 128k;

 If you have a good reason to set the extent size to a uniform size, then by all means do that. However, if you don’t have justification, take the default of AUTOALLOCATE.

You can also specify that a datafile automatically grow when it becomes full. This is set through the AUTOEXTEND ON clause. If you use this feature, we recommend that you set an overall maximum size for the datafile. This will prevent runaway or erroneous SQL from accidentally consuming all available disk space (think about what could happen with a cloud service that automatically adds disk space as required for a database). Here’s an example:

create tablespace tools
  datafile '/u01/dbfile/O12C/tools01.dbf' size 100m
  autoextend on maxsize 10G;

Вас заинтересует / Intresting for you:

Oracle Database and Instance d...
Oracle Database and Instance d... 752 views Masha Thu, 21 Jun 2018, 18:23:39
How to connect to Oracle Datab...
How to connect to Oracle Datab... 403 views Андрей Волков Sat, 29 Feb 2020, 10:19:58
Starting the Oracle Database 1...
Starting the Oracle Database 1... 299 views Андрей Волков Sat, 29 Feb 2020, 10:19:42
Stopping the Oracle Database 1...
Stopping the Oracle Database 1... 300 views Андрей Волков Sat, 29 Feb 2020, 10:19:28