12.2 Tuning and the Logical Layer

---

I like to call anything in an Oracle database above the datafiles the logical layer. This layer is logical in nature because it overlays the underlying physical datafile structure in the operating system file system. The mapping between the physical and logical layers is not necessarily direct or one-to-one. Figure 12.5 shows a quick picture of the overall structure of both physical and logical Oracle database architecture.

Figure 12.5: Oracle Database Physical and Logical Structure

Figure 12.5 shows the various structural layers in an Oracle database.

• Physical Layers.   Datafiles, extents, and blocks are all contained within a datafile.

  • Datafiles.   Datafiles contain data, metadata, rollback, sorting space, clusters, amongst other things.

  • Extent.   An extent is a new chunk added to a datafile when the datafile is increased in size to accommodate new data.

  • Block.   A block is the smallest physical structure in an Oracle datafile.

• Logical Layers.   All logical layers such as tables and indexes are contained within tablespaces. Also all physical layers, the datafiles, are accessible through tablespaces. Thus datafiles, extents and blocks are all accessible and tunable through tablespaces and their contained schema objects.

  • Tablespace.   A tablespace can have many datafiles.

  • Schema.   A schema is the same thing as an owner or username.

  • Segment.   A segment can be a part of a tablespace, a datafile, and a schema. Table, index, rollback, temporary, partitions, and clusters are all different types of segments.

Now let's examine how we can tune tablespaces.

12.2.1 Tablespaces

A tablespace has a referencing map similar to the map on the boot sector of a hard disk drive. When opening a file on a hard disk drive the map is accessed in order to find the address at which the file begins physically on the disk. An Oracle tablespace contains a map in the headers of datafiles describing all extents within underlying datafiles, allowing rapid access to specific addresses in those datafiles. This map of extents can either be stored in database meta-data or in a bitmap attached to the header of a datafile. There are two different general datafile structures which can be created from a tablespace.

• Dictionary-Managed Tablespace.   Extent and block locations are stored in database metadata. Accessing data from a dictionary-managed tablespace is less efficient because database metadata must be searched internally in the Oracle Database with SQL type functionality, in order to find addresses of data in datafiles.

  Note 

   Oracle Database 10 Grid   Dictionary-managed tablespaces will be deprecated in a future version of Oracle Database.

• Locally Managed Tablespace.   Extent and block locations are managed by and stored in a bitmap contained within header portions of datafiles, not elsewhere in SYSTEM tablespace metadata. Accessing the extent map of a locally managed tablespace using the file header contained bitmap is much faster than searching through database metadata to find physical addresses of data within a datafile.

  Note 

   Oracle Database 10 Grid   Locally managed tablespaces with automatically allocated extents are the default.

In the latest version of Oracle Database all tablespaces are created as locally managed tablespaces. Conversion to locally managed tablespaces has been a gradual process in recent versions of Oracle Database. It is now safe to assume that locally managed tablespaces have been industry standard tested and should always be used.

Tip 

All physical and storage parameters are inherited from datafile to tablespace and ultimately to all objects created in a tablespace. Many of these structural parameters can be overridden. Thus a tablespace can override datafile parameters and database objects such as tables and indexes can override tablespace parameters.

Dictionary-Managed Tablespaces

PCTINCREASE should always be explicitly set to zero (0) for any type of tablespace. PCTINCREASE makes each new extent a percentage larger than the previous extent created. The problem with PCTINCREASE is that since each new extent is larger than the previous one any extent which has all of its contents completely deleted will probably never be reused. The result is a lot of empty space in a datafile, thus fragmentation. Fragmentation can be partially resolved by using the COALESCE option of the ALTER TABLESPACE command. Coalescence attempts to take smaller empty extents and merge them into a smaller number of larger extents. On a previous consulting job I coalesced a number of fragmented tablespaces in order to conserve and defragment space. The result was worse performance and larger datafiles. If you have to use dictionary-managed tablespaces never set PCTINCREASE to anything but zero.

The NEXT parameter is used to specify the size of each subsequently created extent. Setting small extent sizes can be used for tablespaces containing only very small tables but it might be better to override this parameter at the table and index object level. I usually set the size of NEXT in the order of megabytes such as 1M, depending on growth rate.

Do not use dictionary-managed tablespaces as they will eventually be deprecated from Oracle Database. Locally managed tablespaces have all the useful and well-performing attributes of dictionary managed tablespaces with parameters most likely to cause problems disallowed or having no effect. Both the PCTINCREASE and NEXT parameters are ignored for locally managed tablespaces.

Note 

 Oracle Database 10 Grid   Dictionary-managed tablespaces will be deprecated in a future version of Oracle Database. Converting a dictionary tablespace to a locally managed tablespace requires use of the DBMS_SPACE_ADMIN package.

Locally Managed Tablespaces

What are the factors that help in tuning locally managed tablespaces? Let's start with a syntax diagram for the CREATE TABLESPACE command. Certain parts are highlighted.

CREATE [ UNDO ] TABLESPACE tablespace       [ DATAFILE 'file' SIZE n[K|M] [ REUSE ]             [ AUTOEXTEND { OFF                   | ON [ NEXT n[K|M]                         [ MAXSIZE { UNLIMITED | n[K|M] }                            ]                   ]             } ]       ]       [ MINIMUM EXTENT n[K|M] ]       [ BLOCKSIZE n[K] ]       [ [NO]LOGGING ]       [ EXTENT MANAGEMENT { DICTIONARY             | LOCAL [ { AUTOALLOCATE | UNIFORM [ SIZE                n[K|M] ] } ]       } ]       [ SEGMENT SPACE MANAGEMENT { AUTO | MANUAL } ]       [             DEFAULT [ [NO]COMPRESS ] STORAGE(                   [ INITIAL n[K|M] ] [ NEXT n[K|M] ]                      [ PCTINCREASE n ]                   [ MINEXTENTS n ] [ MAXEXTENTS { n |                      UNLIMITED } ])       ]; 

Tip 

If a database is created with a locally managed SYSTEM table-space then dictionary-managed tablespaces cannot be created in that database. Additionally the SYSTEM tablespace cannot be changed from local to dictionary-managed.

Now let's go through the highlighted parts of the CREATE TABLESPACE syntax.

Auto Extend

Tablespaces should always be automatically extended with a specified size for the NEXT parameter. Specifying the NEXT parameter assures that datafiles grow with consistent, reusable extent sizes. If extent sizes are too small then a large table could have so many extents to search through that performance will degrade seriously. The NEXT parameter is defaulted to the block size. Do not leave NEXT undeclared and defaulted. Defaulting to the block size is usually too small. Only small static tables and indexes could have sizes for NEXT of below 1M. For some tables well over 1M is prudent.

Setting MAXSIZE UNLIMITED is also sensible because if a maximum datafile size is specified the database will cease to function if datafiles ever reach a maximum size. It is better to extend Oracle tablespace datafiles automatically and monitor disk space usage with scripting in the operating system.

Minimum Extent Sizes

This option specifies that every extent in a datafile is at least the specified size, minimizing defragmentation.

Block Size

Permanent tablespaces can be created with block sizes different to the DB_BLOCK_SIZE database block size parameter. Appropriate DB_nK_CACHE_SIZE parameters must be created in the configuration parameter file to cater for such tablespaces. Obviously a tablespace with a smaller block size is useful to contain smaller tables. A tablespace with a larger block size is good for large tables when reading large amounts of data at once. I have personally never experimented with this aspect of Oracle9i Database in a highly active production environment but would certainly suggest using them for storage of large objects such as LOBs. LOBs contained within tables can be stored into a tablespace separate to the tablespace in which the table resides.

Logging

Switching off logging as the default for a tablespace will increase performance in your database. However, no logging will result in no redo log entries and an unrecoverable database. Do not switch off logging for the sake of performance. The only type of tablespace for which it is sensible to have no logging for is a read-only tablespace. Make a tablespace read-only using the ALTER TABLESPACE command. There is no logging for read-only tablespaces since read-only does not allow DML command changes. Thus there are no redo log entries. Do not use NOLOGGING on read-write table-spaces unless you are prepared to lose your entire database in the event of a disaster. Individual objects in tablespaces can be forced into logging mode when other tables are not if the tablespace is set to NOLOGGING. Once again any type of no logging setting is risky.

Extent Management

The EXTENT MANAGEMENT clause allows specification of a locally or dictionary-managed tablespace. Stick to locally managed tablespaces unless you have a very good reason.

Tip 

When a database SYSTEM tablespace is created as locally managed, all tablespaces in that database must be locally managed.

Once again the most efficient datafile structure for growth is consistent extent sizes. Differing extent sizes in datafiles will result in fragmentation and resulting slow access times due to bouncing around a disk when searching for data. Coalescence is generally useless when trying to reclaim deleted space where extent sizes differ.

Note 

 Oracle Database 10 Grid   In past versions of Oracle automatic extent allocation was not recommended as varying since documentation stated that very small variable extent sizes were created. In Oracle9i Release 2 (9.2) and beyond automatic extent allocation is recommended as its default extent sizes are uniformly set at 1M.

When using manual extent allocation always use the UNIFORM SIZE clause for extent management to ensure consistency of extent sizes across a datafile. The more growth you have the larger the extent size should be. I generally use 1M and larger values for very large databases or databases with high growth rates. Small objects can have smaller extent sizes as long as the database administrator is absolutely sure that large tables or high growth rate tables will never be placed in that tablespace. Database administrators are rarely involved in the development process and thus a database administrator is unlikely to have enough knowledge of applications to make these types of decisions.

Keep extent sizes consistent and never below 1M.

Segment Space Management

A segment space management specification is allowed for locally managed nontemporary tablespaces. Automatic segment space management as specified by the SEGMENT SPACE MANAGEMENT AUTO clause eliminates the need to declare PCTUSED, FREELIST, and FREELIST GROUPS values for objects created in a tablespace. These settings are now bitmap-managed automatically by Oracle Database.

Tip 

Setting values for PCTUSED, FREELIST, and FREELIST_GROUPS in database objects such as tables will be ignored if the containing tablespace is set to automatic segment space management.

In past versions of Oracle Database manual free list management was usually only required for very highly concurrent active OLTP databases or Oracle RAC (Parallel Server) installations. Access to the PCTUSED parameter, on the other hand, helped immensely with tuning for performance in the following respect. The default value for PCTUSED is very low at 40%. Any database with heavy deletion activity could have a lot of wasted space with PCTUSED set to 40%. Deleted rows are still read by full scans and can ultimately result in serious performance problems. One of the biggest problems with manually setting the PCTUSED value for a table was that it was often set based on subjective guesswork due to a lack of understanding. Otherwise it was simply left at the default value. Automatic management of free space resolves these issues.

Tip 

One of the reviewers for this book commented that automatic segment space management realizes up to 35% performance improvement, specifically in Oracle RAC environments.

Note 

 Oracle Database 10 Grid   Locally managed tablespaces with automatic extent management and automatic segment management have been the default since Oracle9i Release 2 (9.2). This indicates that tablespaces should be created as such. In Oracle Database 10g the current accepted practice is to set the SYSTEM, UNDO, and TEMP tablespaces to manual segment space management and all other tablespaces to automated segment space management.

BIGFILE Tablespaces

 Oracle Database 10 Grid   Oracle Database 10g allows division of tablespace types for locally managed tablespaces into two categories: Smallfile tablespaces and Bigfile tablespaces. A Smallfile tablespace is the default.

Tip 

Smallfile or Bigfile can be set as the default for a database as a whole using the CREATE DATABASE or ALTER DATABASE commands.

A Bigfile tablespace contains a single datafile, which can be up to 128 Tb for a block size of 32K. The general trend for database technology at the file structure level is fewer, larger files. Many years ago relational databases had a single datafile for every table or index; some still do. The most advanced database engines are object databases. The most recently developed object databases generally have a single large datafile. The performance benefit of maintaining a single large file is significant. I would suggest that it is likely that Oracle Database will continue on this path of development. In a future version of Oracle Database Bigfile tablespaces may be the default and perhaps eventually the only option, where all physical storage structure within that Bigfile is automatically managed, transparent and inaccessible to database administrators. Splitting files up physically using striping on a RAID array, for instance, is managed at the operating system level and is not a requirement from within the database. Automated Storage Management, introduced in Oracle Database 10g, can implement striping managed from within the Oracle database. This hints further at less emphasis on explicit management of physical storage structures.

Avoiding Datafile Header Contention

Datafile header contention can be avoided when using locally managed tablespaces in busy Oracle installations by creating a large number of small datafiles for each tablespace. Datafile header contention is avoided since extent bitmaps attached to each datafile are not overstressed in highly concurrent environments. This is a new-fangled way of doing things and is not to be confused with manual striping. It is not striping! Locally managed tablespaces have bitmaps in datafile headers in much the same way that dictionary-managed tablespaces use metadata storage to store extent maps. Since bitmaps compress far more than metadata storage, datafile header contention under highly concurrent conditions is more likely.

Now let's take a look at the different types of functions of table-spaces.

Temporary Sort Space

Temporary sort space is catered for in two ways: firstly, sort buffers declared by the SORT_AREA_SIZE parameter; secondly, a temporary sort space tablespace and datafile on disk. It might make sense to size temporary sort space to a multiple of the SORT_AREA_SIZE parameter.

A temporary tablespace is specially structured for sorting and is best implemented as a locally managed tablespace. This is the syntax for creating a temporary tablespace.

CREATE TEMPORARY TABLESPACE tablespace       [ TEMPFILE 'file' [ SIZE n[K|M] ] ]       [ EXTENT MANAGEMENT LOCAL ]       [ UNIFORM [ SIZE n[K|M] ] ];

Below is an example temporary tablespace creation command. This is how I create temporary tablespaces with Oracle9i Database Release 2 (9.2), which may change with a future version of Oracle Database. This is the type of temporary tablespace structure that I trust completely for performance and stability in Oracle9i Database Release 2 (9.2).

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE       '$ORACLE_HOME/oradata/$ORACLE_SID/temp01.dbf' SIZE          100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT          MANAGEMENT LOCAL UNIFORM SIZE 1M;

It is important to allocate the use of a temporary tablespace automatically to a user either using the CREATE DATABASE command or when creating the user with the CREATE USER command. The ALTER USER command can be used later on.

A temporary tablespace is used by an Oracle database for various operations such as index creation and large sorts in SQL code. Temporary sort space is used to shift sorting operations from the sort buffer to disk when a sort operation does not fit into the maximum sort buffer size allocated for each session. Note that every session obtains a buffer area equal to the SORT_AREA_SIZE parameter for sorting purposes. Every session should have access to a temporary tablespace for sorting on disk when the sort buffer is too small for a particular sort operation. Sorting will be covered more later on.

One common mistake with respect to sorting can have severe implications on sorting. If a user does not have a temporary table-space allocated for on-disk sorting overflow from the sort buffer there will be a problem. Prior to Oracle9i Database the CREATE DATABASE command did not allow the setting of a default sorting temporary tablespace for automatic allocation to a newly created user. Quite often database administrators forgot about allocating a temporary tablespace to a user altogether. The result was that the SYSTEM tablespace contained overflow onto disk from the sort buffer.

The SYSTEM tablespace contains the database metadata and is probably the most critical tablespace in an Oracle database with respect to performance. Whenever a database administrator is confronted with a new database, perhaps in a new job, temporary sort space on disk is one of the first things to check.

The following queries check that users are using appropriate default and temporary tablespaces. The TEMP tablespace is my temporary sorting tablespace, the DATA tablespace contains application tables. The SYSTEM tablespace is used for Oracle Database metadata, Oracle applications, and add-on options such as DBSNMP (Oracle agent) and OUTLN (Outlines). The OEM user and OEM tablespace contain an Oracle Enterprise Manager repository (this is best stored in a separate database on a separate machine) and RMAN (Recovery Manager) is stored in the TOOLS tablespace. This structure is shown in the following script output.

Note 

 Oracle Database 10 Grid   The SYSAUX tablespace is created automatically along with the SYSTEM tablespace when a database is created. The SYSAUX tablespace contains options, Oracle tools, repositories, and even some types of metadata previously stored in the SYSTEM tablespace.

COL username FORMAT a20; COL default_tablespace FORMAT a20; COL temporary_tablespace FORMAT a20; SELECT username, default_tablespace, temporary_tablespace       FROM dba_users ORDER BY 1;     USERNAME           DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE -----------------  ------------------  -------------------- ACCOUNTS           DATA                TEMP DBSNMP             SYSTEM              TEMP EMPLOYEES          DATA                TEMP OEM                OEM                 TEMP OUTLN              SYSTEM              TEMP RMAN               TOOLS               TEMP SYS                SYSTEM              TEMP SYSTEM             SYSTEM              TEMP

One other thing that should be checked is that the temporary tablespace TEMP really is a temporary tablespace and not a permanent tablespace. This is a common error as well.

COL tablespace_name FORMAT a20; SELECT tablespace_name, contents FROM dba_tablespaces ORDER    BY 1;     TABLESPACE_NAME             CONTENTS -------------------------  --------- DATA                       PERMANENT DRSYS                      PERMANENT INDX                       PERMANENT OBJECTS                    PERMANENT OEM                        PERMANENT RBS                        PERMANENT SMALLDATA                  PERMANENT SYSTEM                     PERMANENT TEMP                       TEMPORARY TOOLS                      PERMANENT USERS                      PERMANENT

Tablespace Groups

 Oracle Database 10 Grid   On disk, temporary space can be allocated as tablespace groups where a tablespace group can contain multiple locally managed temporary tablespaces. A tablespace group will allow spreading of SQL execution sorting across multiple temporary tablespaces thereby potentially speeding up sorting operations. Processing is distributed. A user can be allocated a tablespace group as a temporary sort space as opposed to just a single temporary tablespace.

Manual Rollback and Automatic Undo

Manual rollback and automatic undo both do the same thing except that automated undo is the more recent and more sophisticated innovation.

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated and replaced by automatic undo (automatic rollback).

Rollback has two functions. The first is allowing the undoing of noncommitted transactions. The second is allowing for a consistent point-in-time snapshot of data. This snapshot capability allows different sessions to find data as it was at a specific time, regardless of any other uncommitted transactions which have already changed the database. Let's examine automatic undo first.

Automated Undo

The automated undo tablespace was introduced in Oracle9i Database. An undo tablespace automates rollback segment management so rollback segments no longer have to be created and tuned. Many database administrators had problems tuning rollback segments. There are a number of undo configuration parameters for controlling automated undo.

• UNDO_MANAGEMENT.   Switches automated undo on and off. Set to MANUAL to use manual rollback segments.

• UNDO_TABLESPACE.   The name of an undo tablespace must be specified.

• UNDO_RETENTION.   Places a time period for how much committed undo data is kept. When using the Database Configuration Assistant default parameters for this value appear to be set to over 10,000 for a data warehouse and under 1,000 for OLTP databases. Documentation on this parameter is vague and it is unclear whether if this parameter is set to too short a time period then uncommitted information is over written.

  Note 

   Oracle Database 10 Grid   An undo tablespace can be created with the tablespace retention clause. The tablespace retention clause can be set in two different ways: RETENTION_GUARANTEE or RETENTION_ NOGUARANTEE. The latter is the default where undo information not yet expired can be consumed by active transactions. Nonexpired undo data could be required for a rollback on a transaction. The RETENTION_GUARANTEE setting guarantees that this will never happen.

Let's look at rollback segments.

Manual Rollback Segments

In previous versions of Oracle Database rollback involved creation and tuning of rollback segments. Rollback segments are placed into a permanent tablespace. Rollback segments are separate physical areas having individual or multiple transactions allocated to them at the same time. In an OLTP system it is best to create a large number of small rollback segments, even up to the maximum of 50 rollback segments. This will help to divide up different transactions into different rollback segments and thus minimize contention between rollback segments. A database with large transactional throughput would be better served with fewer, much larger rollback segments. If a rollback segment expands up to a specified limited number of extents a transaction will fail and rollback. Sometimes in ad hoc SQL code and development environments it is best to limit rollback segment maximum extents in order to avoid runaway transactions. A runaway transaction can completely destroy database performance.

This command creates a rollback segment in a rollback tablespace called RBS.

create public rollback segment rb00 tablespace rbs storage       (initial 16K next 16K optimal 64K minextents 2          maxextents 64);

The OPTIMAL parameter retains space in a rollback segment after completion of a transaction. The setting of OPTIMAL is a debatable issue and depends on database activity and transaction size. When a transaction is committed and rollback is cleared the rollback segment will be cleared physically back to its optimal size; in the case of the rollback segment shown, OPTIMAL is set to 64K, where the initial size is 16K. This will assure that transactions larger than 16K will always have 64K of space available before having to extend and that rollback segments will be of consistent sizes. On the other hand, continual resizing of rollback segments can affect performance. Check for rollback resizing using this query.

SELECT n.name, s.shrinks, s.gets FROM v$rollname n,    v$rollstat s          WHERE n.usn = s.usn AND s.status = 'ONLINE';

So we set rollback sizing parameters according to the size of transactions in the database. For an OLTP database with very small transactions these values could be as small as the values shown of 16K extent size with an optimal value of 64K.

Do not forget to place rollback segments online. The same can be achieved by placing all rollback segments into the ROLLBACK_SEGMENTS configuration parameter, placing all listed rollback segments online at database startup.

alter rollback segment rb00 online;

Note 

 Oracle Database 10 Grid   Manual rollback is deprecated.

The next chapter will delve a little deeper into the logical layers of the Oracle database and examine physical tuning of database objects such as tables and indexes.

---