8 Tuning Tips for DBAs

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 will be accessed frequently, you may also want to 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 will eventually be 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 will perform multiple read operations on a media object (for example: invoking the setProperties( ) method and then generating a thumbnail image), enable read caching for the source media object.

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.

NOLOGGING should be used only when you do not care about media recovery. However, if the disk, tape, or storage media should fail, 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 in size, 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 Tables 8-1 through 8-4 in Section 8.1) to guide your usage of LOB storage options.

In this example, Company X wants to build an archive for digital images. The archive will store 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 will 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 will be 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 will store the binary image data using SecureFiles in tablespace tbs2. All the other table data will be stored in tablespace tbs1.

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, there is no need to cache it. 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 that will be stored in the scale50 and scale25 columns. In this step, the source image will be fully read twice to generate the scaled versions. The scaled images that are generated will be 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 who will view them in a Web browser. Because the archive contains millions of images, it is not expected that users will want to view the same image at the same time. Thus, there is little benefit to caching the image data. The following command re-enables 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);