Oracle8i interMedia Audio, Image, and Video User's Guide and Reference
Release 8.1.5






Prev Next

Tuning Tips for the DBA

This chapter provides tuning tips for the Oracle DBA who wants to achieve more efficient storage of multimedia data in the database when using Oracle8i interMedia. Multimedia data consists of a variety of media types including character text, images, audio clips, video clips, line drawings, and so forth. All these media types are typically stored in LOBs, in either internal LOBs (stored in an internal database tablespace) or in external LOBs in operating system files outside of the database tablespaces, in BFILEs.

Internal LOBs consist of: CLOBs, NCLOBs, and BLOBs and can be up to 4 gigabytes in size. BFILEs can be as large as the operating system will allow up to a maximum of 4 gigabytes.

Oracle8i interMedia manages a variety of LOB types. The following general topics will help you to better manage your interMedia LOB data:

For more information, see information about LOB partitioning, LOB tuning, and LOB buffering in the Oracle8i Application Developer's Guide - Large Objects (LOBs), Oracle Call Interface Programmer's Guide, Oracle8i Concepts, and Oracle8i Tuning manuals.

For information on any restrictions to consider when using LOBs, see Oracle8i Application Developer's Guide - Large Objects (LOBs).

For information on guidelines for using the DIRECTORY feature in Oracle8i to enable a simple, flexible, nonintrusive, yet secure mechanism for the DBA to manage access to large files in the server file system, see Oracle8i Application Developer's Guide - Large Objects (LOBs).

7.1 Improving Multimedia LOB Data INSERT Performance

Issues to Consider in Creating Tables that Will Contain LOBs

The following information provides some strategies to consider when you create tables that will contain LOB columns. These topics are discussed in more detail and with examples in Oracle8i Application Developer's Guide - Large Objects (LOBs). The information that follows is excerpted in whole or in part from Chapter 2 and is briefly presented to give you an overview of the topic. Refer to Oracle8i Application Developer's Guide - Large Objects (LOBs) for the most current information.

Example 7-1 Creating a Separate Tablespace to Store LOB Data

Statement processed.
     LOB (image.source.localData) STORE AS 
            tablespace MONTANA
            STORAGE (
                   INITIAL 100M
                   NEXT 100M
            CHUNK 21K

LOB Buffering

Use LOB buffering, if you need to read or write small pieces of LOB data repeatedly to specific regions of the LOB on the client. Typically, Oracle8i options, Web servers, and other client-based applications may need to buffer the contents of one or more LOBs in the client's address space. Using LOB buffering, you can use up to 512K bytes of buffered access. The advantages of LOB buffering include:

See Oracle8i Application Developer's Guide - Large Objects (LOBs) for further considerations and the use of LOB buffering.


Parallelization is the parallel execution of a single SQL statement using multiple processes. Parallel execution can dramatically improve performance for data-intensive operations associated with decision-support applications or very large database environments such as multimedia databases. Parallel execution is useful for operations accessing large amounts of data and can improve processing for bulk insert, update, and delete operations and processing for objects and data types, such as LOBs. The Oracle database server can use parallel execution for a number of operations, including PL/SQL functions called from SQL, split partition, update, delete,, and many other operations. See the Oracle8i Concepts manual for a complete list of SQL operations that benefit from parallelization. See the Oracle8i SQL Reference manual for examples.

You can use parallelization features for bulk loading of LOB data. See the Oracle8i Tuning manual for more information.

7.2 Other Bulk Loading Methods

There are a number of other bulk loading methods available for loading FILE data into LOBs. These include:

Using the SQL Loader Utility

For Oracle8i, you can use SQL*Loader to bulk load objects, collections, and LOBs. This release of SQL*Loader supports loading of the following two object types:

Supported collection types include:

Supported LOB types include four types of LOBs:

See the Oracle8i Utilities manual for more information on SQL*Loader and Oracle8i Application Developer's Guide - Fundamentals for more information on LOBs.

Using the OCILobLoadFromFile( ) Relational Function

The Oracle Call Interface (OCI) is an application programming interface (API) that allows you to manipulate data and schemas in an Oracle database using a host programming language, such as C.

The OCI relational function, OCILobLoadFromFile( ), loads or copies all or a portion of a FILE into an internal LOB as specified. The data is copied from the source FILE to the destination internal LOB (BLOB/CLOB). No character set conversions are performed when copying the FILE data to a CLOB/NCLOB. Also, when binary data is loaded into a BLOB, no character set conversions are performed. Therefore, the FILE data must already be in the same character set as the LOB in the database. No error checking is performed to verify this.

See Oracle Call Interface Programmer's Guide for more information.

Using the DBMS_LOB.LOADFROMFILE( ) Procedure in the DBMS_LOB Package

The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use the DBMS_LOB package for access and manipulation of specific parts of a LOB, as well as complete LOBs. DBMS_LOB can read as well as modify BLOBs, CLOBs, and NCLOBs, and provides read-only operations for BFILEs. The main bulk of the LOB operations are provided by this package.

The DBMS_LOB.LOADFROMFILE( ) procedure copies all, or a part of, a source-external LOB (BFILE) to a destination-internal LOB.

You can specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source BFILE. The amount and src_offset, because they refer to the BFILE, are in terms of bytes, and the destination offset is either in bytes or characters for BLOBs and CLOBs respectively.

The input BFILE must have been opened prior to using this procedure. No character set conversions are performed implicitly when binary BFILE data is loaded into a CLOB. The BFILE data must already be in the same character set as the CLOB in the database. No error checking is performed to verify this.

See Oracle8i Supplied Packages Reference for more information.

7.3 User Guidelines for Best Performance Practices

The following guidelines can be used to help you achieve the best performance when working with LOBs:

See Oracle8i Application Developer's Guide - Large Objects (LOBs) for more information.

7.4 Improving Multimedia LOB Data Retrieval and Update Performance

Once the LOB data is stored in the database, a modified strategy must be used to improve the performance of retrieving and updating the LOB data compared to the insertion strategy described in Section 7.1. The following guidelines should be considered:

7.5 Setting the Maximum Number of Open BFILEs

A limited number of BFILES can be open simultaneously per session. The default value is 10 files. Set the initialization parameter, SESSION_MAX_OPEN_FILES, to a higher value in the init.ora file if you need to have more BFILEs open simultaneously.

7.6 Using LOBs in Table Partitions

Because you can partition tables containing LOB columns, LOB segments can be spread between several tablespaces to:

LOB data can be partitioned to improve I/O problems to better balance the I/O load across the data files of the tablespace containing the LOB data. You can allocate data storage across devices to further improve performance in a practice known as striping. This permits multiple processes to access different portions of the table concurrently without disk contention.

LOB data can be partitioned to tune database backup and recovery operations to make more efficient use of resources needed to perform these operations. For example, having two or more tablespaces that are partitioned lets you perform partial database backup and recovery operations on specific data files as needed.

Similarly, tablespaces with LOBs can be partitioned for easy maintenance of the LOB data by logically grouping LOB data together into smaller partitions that are grouped by date, by subject, by category, and so forth. This makes it easier to add, merge, split, or delete partitions as needed, based on your application.

See Oracle8i Application Developer's Guide - Large Objects (LOBs) for examples and further discussion of each of these topics. See the Oracle8i SQL Reference manual for examples, specifically the CREATE TABLE statement and the Partitioned Table with LOB Columns example.


Copyright © 1999 Oracle Corporation.

All Rights Reserved.