Creating Oracle 12c Tablespaces to Maximize Performance

Creating Oracle 12c Tablespaces with Max PerformanceProblem

You realize that tablespaces are the logical containers for 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:

How to ensure the Oracle datab...
How to ensure the Oracle datab... 906 views Aaz24 Mon, 26 Nov 2018, 12:20:58
Terminating Oracle Database pr...
Terminating Oracle Database pr... 831 views Antoni Sun, 13 May 2018, 18:57:10
Choosing Oracle Database 12c T...
Choosing Oracle Database 12c T... 640 views Ded Thu, 23 Aug 2018, 13:33:50
Oracle Database and Instance d...
Oracle Database and Instance d... 506 views Masha Thu, 21 Jun 2018, 18:23:39

Comments on Creating Oracle 12c Tablespaces to Maximize Performance

Be the first to comment
Please login to comment