6 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.

This chapter includes these sections:

See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide for more information about performance tuning when using LOBs in Oracle Database

6.1 Understanding the Performance Profile of Oracle Multimedia Operations

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 tables in this section 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.

The following table shows the profile for loading and retrieving data, which applies to all Oracle Multimedia media types.

Table 6-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

The following table shows the profile for commonly used functions and procedures of the ORD_IMAGE PL/SQL package.

Table 6-2 Performance Profile for ORD_IMAGE PL/SQL Package Functions and Procedures

Package Procedure I/O Pattern (Source) I/O Pattern (Destination) I/O Pattern (Amount) CPU Usage

getProperties( )

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

convert( )

Sequential read

Sequential write

All

High

crop( )

Sequential read

Sequential write

All

High

scale( )

Sequential read

Sequential write

All

High

thumbnail( )

Sequential read

Sequential write

All

High

The following table shows the profile for commonly used methods of the ORDImage type.

Table 6-3 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

The following table shows the profile for commonly used functions and procedures of the ORD_DICOM PL/SQL package.

Table 6-4 Performance Profile for ORD_DICOM PL/SQL Package Functions and Procedures

Package Procedure I/O Pattern (Source) I/O Pattern (Destination) I/O Pattern (Amount) CPU Usage

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

The following table shows the profile for commonly used methods of the ORDDicom type.

Table 6-5 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

The following table shows the profile for commonly used procedures of the ORD_AUDIO, ORD_DOC, and ORD_VIDEO PL/SQL packages.

Table 6-6 Performance Profile for ORD_AUDIO, ORD_DOC, and ORD_VIDEO PL/SQL Package Procedures

Package Procedure I/O Pattern (Source) I/O Pattern (Destination) I/O Pattern (Amount) CPU Usage

getProperties( )

Sequential read

N/A

Media header

Low

The following table shows the profile for commonly used methods of the ORDAudio, ORDDoc, and ORDVideo types.

Table 6-7 Performance Profile For ORDAudio, ORDDoc, 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

6.2 Choosing LOB Storage Parameters for Multimedia LOBs

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:

See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide for detailed information about LOBs

6.2.1 SecureFiles LOBs

Store your media data with SecureFiles LOBs.

Oracle recommends using SecureFiles LOBs for storing media data whenever possible. SecureFiles LOBs are identified by specifying the SQL parameter SECUREFILE.

6.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.

6.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.

These are the possible values for the cache option:

  • For 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.

If your application performs multiple read operations on a media object (for example: invoking the getProperties( ) procedure or the setProperties( ) method and then generating a thumbnail image), enable read caching for the source media LOB.

6.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).

Note:

Oracle Data Guard Redo Apply technology uses logging to populate the standby database. Thus, do not specify NOLOGGING when using this Data Guard technology.

6.2.5 Example of Setting LOB Storage Options

This section describes a simple example that shows how to use the performance profiles of Oracle Multimedia operations 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. Although this example uses the ORDImage object type for storing the images, the concepts apply to images stored directly in BLOB columns.

create table images(id        integer primary key,
                    original  ordsys.ordimage,
                    scale50   ordsys.ordimage,
                    scale25   ordsys.ordimage)
tablespace tbs1
lob(original.source.localdata)store as secureFile (tablespace tbs2)
lob(scale50.source.localdata)store as secureFile (tablespace tbs2)
lob(scale25.source.localdata)store as secureFile (tablespace tbs2);

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.

alter table images modify lob(original.source.localdata) (nocache nologging);

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 images 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.

alter table images modify lob(original.source.localdata) (cache reads);
alter table images modify lob(scale50.source.localdata) (nocache nologging);
alter table images modify lob(scale25.source.localdata) (nocache nologging);

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.

alter table images modify lob(original.source.localdata) (nocache logging);
alter table images modify lob(scale50.source.localdata) (nocache logging);
alter table images modify lob(scale25.source.localdata) (nocache logging);

See Also:

Table 6-1 through Table 6-7 for more information about Oracle Multimedia performance profiles

6.3 Setting Database Initialization Parameters

You can disable logging of LOB data at the column level to reduce the amount of I/O to the redo log. If logging cannot be disabled, additional database tuning may be required. For example, 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.

See Also: