Release 8.1.5
A67299-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i interMedia Audio, Image, and Video User's Guide and Reference Release 8.1.5 A67299-01 |
|
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).
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.
A LOB value set to NULL has no locator. By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, you get back a locator, which you can use to fill the LOB with data using the OCI or DBMS_LOB routines.
You may want to set the internal LOB value to NULL upon inserting the row in cases where you do not have the LOB data at the time of the INSERT operation or if you want to issue a SELECT statement at some later time, or for both of these reasons.
However, the drawback to this approach is that you must then issue a SQL UPDATE statement to reset the NULL LOB column -- to EMPTY_BLOB( ) or EMPTY_CLOB( ) or to a known value for internal LOBs, or to a file name for external LOBs. The point is that you cannot call the OCI or the PL/SQL DBMS_LOB functions on a LOB that is NULL. These functions work only with a locator, and if the LOB column is NULL, there is no locator in the row.
If you do not want to set an internal LOB column to NULL, another option is for you to set the LOB value to EMPTY by using the function EMPTY_BLOB ( ) or EMPTY_CLOB( ) in the INSERT statement.
Even better, use the RETURNING clause (thereby eliminating a round trip that is necessary for the subsequent SELECT statement). Then, immediately call OCI or the PL/SQL DBMS_LOB functions to fill the LOB with data. See Oracle8i Application Developer's Guide - Large Objects (LOBs) for an example.
The best performance for LOBs can often be achieved by specifying storage for LOBs in a tablespace that is different from the one used for the table that contains the LOB column. If many different LOBs are to be accessed frequently, it may also be useful to specify a separate tablespace for each LOB column or attribute in order to reduce device contention. Preallocate the tablespace to the required allocation size to avoid allocation when inserting BLOB data. See the Oracle8i SQL Reference manual for examples, specifically the CREATE TABLE statement and the LOB Column example. See Example 7-1.
The LOB index is an internal structure that is strongly associated with the LOB storage. This implies that a user may not delete the LOB index and rebuild it. Note that the LOB index cannot be altered. The system determines which tablespace to use for the LOB data and LOB index depending on the user specification in the LOB storage clause:
If in creating tables in Oracle release 8.1, you specify a tablespace for the LOB index for a nonpartitioned table, your specification of the tablespace will be ignored and the LOB index and the LOB data will use the same tablespace. Partitioned LOBs do not include the LOB index syntax.
Specifying a separate tablespace for the LOB storage segments will decrease the contention on the tablespace of the table.
When a LOB is modified, a new version of the LOB page is made in order to support a consistent read of prior versions of the LOB value.
PCTVERSION is the percent of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle tries to reclaim the old versions and reuses them. In other words, PCTVERSION is the percentage of used LOB data blocks that is available for versioning of old LOB data.
One way of approximating PCTVERSION is to set PCTVERSION = (% of LOBs updated at any given point in time) times (% of each LOB updated whenever a LOB is updated) times (% of LOBs being read at any given point in time). Basically, the idea is to allow for a percentage of LOB storage space to be used as old versions of LOB pages so readers are able to get consistent reads of data that has been updated.
Setting PCTVERSION to twice the default allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of LOBs, it is useful to keep more old versions of LOB pages around. Of course, LOB storage may increase because Oracle will not be reusing free pages aggressively.
The more infrequent and smaller the LOB updates are, the less space that needs to be reserved for old copies of LOB data. If existing LOBs are known to be read-only, you could safely set PCTVERSION to 0% because there would never be any pages needed for old versions of data.
Use the CACHE option on LOBs if the same LOB data is to be accessed frequently. Use the NOCACHE option (the default) if LOB data is to be read only once, or infrequently.
Use the NOCACHE option when reading and writing large quantities of LOB data to reduce the I/O operations.
See Example 7-1.
An example of when NOLOGGING is useful is with bulk loads or inserts of data. See Example 7-1. For instance, when loading data into the LOB, if you do not care about redo logging and can just start the load over if it fails, set the LOB's data segment storage characteristics to NOCACHE NOLOGGING. This setting gives good performance for the initial load of data. Once you have completed loading the data, you can use the ALTER TABLE statement to modify the LOB storage characteristics for the LOB data segment to the desired characteristics for normal LOB operations. For example, change the LOB's data storage characteristics to CACHE or NOCACHE LOGGING.
Additional guidelines include: use the NOLOGGING option when writing large amounts of data to the LOB so that when the new version of the LOB data is created, it is not written in the redo log. This improves performance because much less redo log is generated; however, the LOB data must be backed up following a no-logging INSERT operation to ensure the data can be restored.
Set the CHUNK option to the number of blocks of LOB data that are to be accessed at one time, for example, the number of blocks that are to be read or written using the call, OCILobRead( ), OCILobWrite( ), DBMS_LOB.READ( ), or DBMS_LOB.WRITE( ) during one access of the LOB value. Note that the default value for the CHUNK option is one
Oracle block and does not vary across systems. For example, if only 1 block of LOB data is accessed at a time, set the CHUNK option to the size of 1 block. For example, if the database block size is 2K, then set the CHUNK option to 2K.
Set the CHUNKSIZE option slightly larger than the audio, image, or video data size being inserted. Specifying a slightly larger CHUNKSIZE allows for some variation in the actual sizes of the multimedia data and ensures that the benefit is realized. See Example 7-1.
If you explicitly specify the storage characteristics for the LOB, make sure that the INITIAL and NEXT parameters for the LOB data segment storage are set to a size that is larger than the CHUNK size. For example, if the database block size is 2K and you specify a CHUNK value of 8K, make sure that the INITIAL and NEXT parameters are bigger than 8K and preferably considerably bigger (for example, at least 16K). Also, the INITIAL and NEXT parameters should normally be set to the same value.
Oracle manages the storage space in the data files of a database in units called data blocks. The data block is the smallest unit of I/O operation used by a database. You set the data block size for each Oracle database when you create the database. The data block size should be a multiple of the operating system's block size within the maximum (port-specific) limit to avoid unnecessary I/O operations.
For reading and writing LOB data, set the DATABLOCKSIZE parameter to the size of the LOB data you are going to bulk load or to some fractional equivalent. For example, if you are attempting to optimize the LOB storage of 20KB images, set the DATABLOCKSIZE parameter to 10KB or 20KB to move more LOB data in each I/O operation.
You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether the LOB should be stored inline (that is, in the row) or out of line. You may not alter this specification once you have made it: if you ENABLE STORAGE IN ROW, you cannot alter it to DISABLE STORAGE IN ROW and the reverse. The default is ENABLE STORAGE IN ROW.
The maximum amount of LOB data that will be stored in the row is the maximum VARCHAR size (4000). Note that this includes the control information as well as the LOB value. If the user indicates that the LOB should be stored in the row, once the LOB value and control information are larger than 4000, the LOB value is automatically moved out of the row.
This suggests the following guideline: If the LOB is small (that is, less than 4000 bytes), then storing the LOB data out of line will decrease performance. However, storing the LOB in the row increases the size of the row. This has a detrimental impact on performance if you are doing a lot of base table processing, such as full table scans, multiple row accesses (range scans), or many UPDATE or SELECT statements to columns other than the LOB columns. If you do not expect the LOB data to be less than 4000 bytes, that is, if all LOBs are big, then the default is the best choice because:
(a) The LOB data is automatically moved out of line once it gets bigger than 4000 bytes.
(b) Performance is slightly better because you can still store some control information in the row even after you move the LOB data out of the row.
Example 7-1 assumes that you have already issued a CONNECT statement as some suitably privileged user. This example creates a separate tablespace called MONTANA that is used to store the LOB data for the image column. Ideally, this tablespace would be located on its own high-speed storage device to reduce contention. Other image attributes and the imageID column are stored in the default tablespace. The initial allocation allows 100MB of storage space. The images to be inserted are about 20KB in size. To improve insert performance, NOCACHE and NOLOGGING options are specified along with a CHUNK size of 21KB.
SVRMGR> CREATE TABLESPACE MONTANA DATAFILE 'montana.tbs' SIZE 400M; Statement processed. SVRMGR> CREATE TABLE images (image ORDSYS.ORDImage, imageID INTEGER) LOB (image.source.localData) STORE AS ( tablespace MONTANA STORAGE ( INITIAL 100M NEXT 100M ) CHUNK 21K NOCACHE NOLOGGING );
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, insert...select, 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.
There are a number of other bulk loading methods available for loading FILE data into LOBs. These include:
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.
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.
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.
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.
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:
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.
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.
