8 Oracle Multimedia Tuning Tips for DBAs

This chapter provides information and advice for Oracle DBAs who want to achieve more efficient storage and management of multimedia data in the database when using Oracle Multimedia.

The goals of your Oracle Multimedia application determine the resource requirements and how to allocate those resources. Because application development and design decisions have the greatest effect on performance, standard tuning methods must be applied to the system planning, design, and development phases of the project to achieve optimal results for your Oracle Multimedia application in a production environment.

Multimedia data consists of a variety of media types including images, audio clips, video clips, line drawings, and so on. All these media types are typically stored in LOBs. LOBs can be either internal BLOBs (stored in an internal database tablespace) or BFILEs (external LOBs in operating system files outside of the database tablespaces). This chapter discusses the management of audio, image, and video data stored in BLOBs only.

Multimedia data, and the operations that can be performed on that data, differs significantly from traditional types of data commonly stored in relational databases. A basic understanding of the performance profile of Oracle Multimedia operations can help you make better decisions when tuning your database for media performance.

The following tables summarize the general performance profiles for a set of commonly performed operations. There are two primary components to each profile. The I/O pattern is a general characterization of the primary type of I/O access and of how much of the media data the operation reads or writes. Because some operations involve two media objects, the I/O pattern is described for both the source and destination media objects. The second component is a general characterization of the level of CPU usage for the operation.

Note:

The information in these tables describes general characterizations and I/O patterns, thus CPU usage may vary considerably for some media formats.

Table 8-1 shows the profile for loading and retrieving data, which applies to all Oracle Multimedia media types.

Table 8-1 Performance Profile For All Multimedia Types

Operation

I/O Pattern (Source)

I/O Pattern (Destination)

I/O Pattern (Amount)

CPU Usage

Load new media data into a database

N/A

Sequential write

All

Low

Retrieve media from a database

Sequential read

N/A

All

Low

Table 8-2 shows the profile for commonly used methods of the ORDImage type.

Table 8-2 Performance Profile For ORDImage Methods

Object Method

I/O Pattern (Source)

I/O Pattern (Destination)

I/O Pattern (Amount)

CPU Usage

setProperties( )

Sequential read

N/A

Media header

Low to medium

getMetadata( )

Sequential read

N/A

Media header

Low to medium

putMetadata( )

Sequential read

Sequential write

All

Low to medium

process( )

Sequential read

Sequential write

All

High

processCopy( )

Sequential read

Sequential write

All

High

Table 8-3 shows the profile for commonly used methods of the ORDDicom type.

Table 8-3 Performance Profile For ORDDicom Methods

Object Method

I/O Pattern (Source)

I/O Pattern (Destination)

I/O Pattern (Amount)

CPU Usage

setProperties( )

Sequential read

N/A

Media header

Low to medium

extractMetadata( )

Sequential read

N/A

Media header

Low to medium

writeMetadata( )

Sequential read

Sequential write

All

Low to medium

makeAnonymous( )

Sequential read

Sequential write

All

Low to medium

process( )

Sequential read

Sequential write

All

High

processCopy( )

Sequential read

Sequential write

All

High

Table 8-4 shows the profile for commonly used methods of the ORDAudio and ORDVideo types.

Table 8-4 Performance Profile For ORDAudio and ORDVideo Methods

Object Method

I/O Pattern (Source)

I/O Pattern (Destination)

I/O Pattern (Amount)

CPU Usage

setProperties( )

Sequential read

N/A

Media header

Low

getProperties( )

Sequential read

N/A

Media header

Low

8.2 Choosing LOB Storage Parameters for Oracle Multimedia Objects

The choices you make for specifying LOB storage attributes during table creation can significantly affect the performance of media load, retrieval, and processing operations. This section describes the most important options to consider and shows how the performance profile of Oracle Multimedia operations can affect the choice of LOB storage parameters.

The following subsections describe the LOB storage parameters and include examples of how to use them:

8.2.1 SecureFile LOBs and BasicFile LOBs

SecureFile LOBs (SecureFiles) were introduced in Oracle Database 11g Release 1 (11.1) to supplement the original BasicFile LOBs implementation that is identified by the SQL parameter BASICFILE. The performance of SecureFile LOBs is significantly better than that of BasicFile LOBs, especially for large media data. Oracle recommends using SecureFile LOBs for storing media data whenever possible. SecureFile LOBs are identified by specifying the SQL parameter SECUREFILE.

8.2.2 TABLESPACE

You can achieve the best performance for LOBs by specifying storage for LOBs in a different tablespace than the one used for the table that contains the LOB. If many different LOBs are to be accessed frequently, you can also specify a separate tablespace for each LOB column or attribute to reduce device contention.

8.2.3 CACHE, NOCACHE, and CACHE READS

The cache option is a part of the STORE AS clause, and determines whether LOB pages are stored in the buffer cache.

When the option has the value CACHE, Oracle places LOB pages in the buffer cache where they can be shared among multiple users. Over time and if the LOB pages are no longer accessed, the pages are eventually removed from the buffer cache.

For the value NOCACHE, LOB pages are not placed in the buffer cache.

For the value CACHE READS, LOB pages are placed in the cache for read operations only.

8.2.4 LOGGING and NOLOGGING

The logging option is a part of the STORE AS clause and determines if REDO data is logged when a LOB is updated. If the [NO]LOGGING clause is omitted, neither NOLOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.

There is another alternative depending on how the cache option is specified.

If CACHE is specified and [NO]LOGGING is omitted, LOGGING is automatically implemented (because you cannot have CACHE NOLOGGING).

If CACHE is not specified and [NO]LOGGING is omitted, the [NO]LOGGING value is obtained from the tablespace in which the LOB segment resides.

Specify NOLOGGING only when you do not care about media recovery. However, if the disk, tape, or storage media fails, you will not be able to recover your changes from the log because those changes were not logged.

NOLOGGING can be useful for bulk loading of media data. For instance, when loading data into the LOB, if you do not care about the redo operation and you can start the load over if it fails, set the LOB data segment storage characteristics to NOCACHE NOLOGGING. This option provides good performance for the initial loading of data.

After you finish loading data, if necessary, you can use the ALTER TABLE statement to modify the LOB storage characteristics for the LOB data segment for normal LOB operations (for example: to CACHE or NOCACHE LOGGING).

8.2.5 CHUNK

The CHUNK option applies only to BasicFile LOBs. It is part of the STORE AS clause, and indicates the size of the minimum unit of storage for the LOB data. CHUNK must be an integer multiple of the block size, and it must have a maximum value of 32K bytes.

Accessing LOBs in bigger chunks is more efficient. For the most efficient storage of media objects, which are almost always much larger than 32K, choose the maximum value of 32K.

8.2.6 Example of Setting LOB Storage Options

This section describes a simple example that shows how to use the performance profiles of Oracle Multimedia operations (see Table 8-1 through Table 8-4) to guide your usage of LOB storage options.

In this example, Company X wants to build an archive for digital images. The archive stores a full resolution copy of the original image, and two Web-ready, JPEG format versions of the original at reduced scales, one at 50% of the original size and another at 25% of the original size. The database team plans to use the SQL*Loader utility to bulk load all the initial images. Then, they can use a PL/SQL program to initialize the image data. Initialization consists of setting the properties for the original image and generating the scaled images. After initialization, the table is prepared for the primary application, which retrieves images for Web-based users.

The following example shows a table definition for storing the images. The table stores the binary image data using SecureFiles in tablespace tbs2. All the other table data is stored in tablespace tbs1.

After the table is created, the image data can be loaded. Loading image data generates a sequential write pattern to the LOB. Because no applications are reading the data during the load operation, caching it is not required. You can also improve load performance by disabling logging for the column that is loaded. The following command dynamically alters the table to prepare the original image column LOB for loading.

After loading, the next step is to set the image properties for the original column and generate the scaled versions to be stored in the scale50 and scale25 columns. In this step, the source image are fully read twice to generate the scaled versions. The scaled images that are generated are written but not read. The following command dynamically alters the table to enable read caching for the source image, and disables caching and logging for the destination images.

After running the program to set the properties of the original image and generate the scaled versions, the LOB storage attributes can be optimized for the main application that retrieves images for users to view in a Web browser. Because the archive contains millions of images, users are not expected to view the same image simultaneously. Thus, there is little benefit to caching the image data. The following command reenables logging for the LOBs and disables caching.

8.3 Setting Database Initialization Parameters

Section 8.2 points out that you can disable logging of LOB data at the column level to reduce the amount of I/O to the redo log. However, if logging cannot be disabled, additional database tuning may be required. Specifically, you may have to increase the size of the redo log buffer to prevent load processes from waiting.

The initialization parameter LOG_BUFFER specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.

If the LGWR process writes the redo log data to the redo log buffer faster than it can write the data to disk, the buffer becomes full and user sessions are forced to wait until space is available. The wait event "log buffer space" indicates the number of times a session had to wait for space in the redo log buffer. You can monitor this event in the V$SYSTEM_EVENT dynamic view to learn how many times a session had to wait for log buffer space. If sessions are forced to wait often for log buffer space, consider increasing the value of the LOG_BUFFER initialization parameter.