Release 9.0.1
Part Number A88786-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle interMedia User's Guide and Reference Release 9.0.1 Part Number A88786-01 |
|
This chapter provides tuning tips for the Oracle DBA who wants to achieve more efficient storage and management of multimedia data in the database when using Oracle interMedia.
The goals of your interMedia application determine the resource needs and how those resources should be allocated. 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 interMedia application in a production environment.
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 BFILEs (external LOBs in operating system files outside of the database tablespaces). This chapter discusses only the management of audio, image, and video data.
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.
Oracle interMedia manages a variety of LOB types. The following general topics will help you to better manage your interMedia LOB data:
For more information about LOB partitioning, LOB tuning, and LOB buffering, see Oracle9i Application Developer's Guide - Large Objects (LOBs), Oracle Call Interface Programmer's Guide, Oracle9i Database Concepts, and Oracle9i Database Performance Guide and Reference.
For information on restrictions to consider when using LOBs, see Oracle9i Application Developer's Guide - Large Objects (LOBs).
For guidelines on using the DIRECTORY feature in Oracle9i, see Oracle9i Application Developer's Guide - Large Objects (LOBs). This feature enables a simple, flexible, nonintrusive, and secure mechanism for the DBA to manage access to large files in the server file system.
The information that follows is an excerpt from Oracle9i Database Performance Guide and Reference and Oracle9i Database Reference, and is presented as an overview of the topic. Refer to Oracle9i Database Performance Guide and Reference and Oracle9i Database Reference for more information.
Database tuning of the Oracle instance consists of tuning the system global area (SGA). The SGA is used to store data in memory for fast access. The SGA consumes a portion of your system's physical memory. The SGA must be sufficiently large to keep your data in memory but neither too small nor so large that performance begins to degrade. Degrading performance occurs when the operating system begins to page unused information to disk to make room for new information needed in memory, or begins to temporarily swap active processes to disk so other processes needing memory can use it. Excessive paging and swapping can bring a system to a standstill. The goal in sizing the SGA is to size it for the data that must be kept in main memory to keep performance optimal. With this in mind, you must size the SGA required for your interMedia application. This may mean increasing the physical memory of your system and monitoring your operating system behavior to ensure paging and swapping remains minimal.
The size of the SGA is determined by the values of the following database initialization parameters: DB_BLOCK_SIZE, DB_CACHE_SIZE, SHARED_POOL_SIZE, and LOG_BUFFER.
Beginning with Oracle9i, the SGA infrastructure is dynamic. This means that the following primary parameters used to size the SGA can be changed while the instance is running:
The LOG_BUFFER parameter is used when buffering redo entries to a redo log. It is a static parameter and represents a very small portion of the SGA and can be changed only by stopping and restarting the database to read the changed value for this parameter from the initialization parameter file (init.ora).
Note that even though you cannot change the MAX_SGA_SIZE parameter value dynamically, you do have the option of changing any of its three dependent primary parameters (DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE) to make memory tuning adjustments on the fly. To help you specify an optimal cache value, you can use the dynamic DB_CACHE_ADVICE parameter with statistics gathering enabled to predict behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view. Use the ALTER SYSTEM...SET clause... statement to enable this parameter. See Oracle9i Database Performance Guide and Reference for more information about using this parameter.
Beginning with Oracle9i, there is a concept of creating tablespaces with multiple block sizes and specifying cache sizes corresponding with each block size. The SYSTEM tablespace uses a standard block size and additional tablespaces can use up to five non-standard block sizes.
The standard block size is specified by the DB_BLOCK_SIZE parameter. Its cache size is specified by the DB_CACHE_SIZE parameter. Non-standard block sizes are specified by the BLOCKSIZE clause of the CREATE TABLESPACE statement. The cache size for each corresponding non-standard block size is specified using the notation: DB_nK_CACHE_SIZE parameter, where the value n is 2, 4, 8, 16, or 32 K bytes.
The standard block size, known as the default block size, is usually set to the same size in bytes as the operating system block size, or a multiple of this size. The DB_CACHE_SIZE parameter, known as the DEFAULT cache size, specifies the size of the cache of standard block size (default is 48M bytes). The system tablespace uses the standard block size and the DEFAULT cache size.
Either the standard block size or any of the non-standard block sizes and their associated cache sizes can be used for any of your other tablespaces. If you intend to use multiple block sizes in your database storage design, you must specify at least the DB_CACHE_SIZE and one DB_nK_CACHE_SIZE parameter value. You must specify all sub-caches for all the other non-standard block sizes that you intend to use. This block size/cache sizing scheme lets you use up to five different non-standard block sizes for your tablespaces and lets you specify respective cache sizes for each corresponding block size. For example, you can size your system tablespace to the normal 2K or 4K bytes standard block size with a default DB_CACHE_SIZE of 48M bytes or whatever size you want to specify. Then you can use the remaining non-standard block sizes of 2K or 4K, 8K, 16K, or the maximum 32K bytes for storing your interMedia LOB data in appropriate block-sized tablespaces and respective caches to achieve optimal LOB storage and retrieval performance.
Because the DB_BLOCK_SIZE parameter value can be changed only by re-creating the database, the value for this parameter must be chosen carefully and remain unchanged for the life of the database. See the next section "DB_BLOCK_SIZE" for more information about this parameter.
The following sections describe these and some related initialization parameters and their importance to interMedia performance.
The DB_BLOCK_SIZE parameter is the size in bytes of Oracle database blocks (2048-32768). 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; this value should be a multiple of the operating system's block size within the maximum (port-specific) limit to avoid unnecessary I/O operations. This parameter value is set for each Oracle database from the DB_BLOCK_SIZE parameter value in the initialization parameter file when you create the database. This value cannot be changed unless you create the database again.
The size of a database block determines how many rows of data Oracle can store in a single database page. The size of an average row is one piece of data that a DBA can use to determine the correct database block size. interMedia objects with instantiated LOB locators range in size from 175 bytes for ORDImage to 260 bytes for ORDAudio and ORDVideo. This figure does not include the size of the media data. (The difference in row sizes between instantiated image and audio and video data is that audio and video data contain a Comments attribute that is about 85 bytes in size to hold the LOB locator.)
If LOB data is less than 4000 bytes, then it can be stored in line or on the same database page as the rest of the row data. LOB data can be stored in line only when the block size is large enough to accommodate it.
LOB data that is stored out of line, on database pages that are separate from the row data, is accessed (read and written) by Oracle in CHUNK size pieces where CHUNK is specified in the LOB storage clause (see Section 11.2 for more information about the CHUNK option). CHUNK must be an integer multiple of DB_BLOCK_SIZE and defaults to DB_BLOCK_SIZE if not specified. Generally, it is more efficient for Oracle to access LOB data in large chunks, up to 32 KB. However, when LOB data is updated, it may be versioned (for read consistency) and logged both to the rollback segments and the redo log in CHUNK size pieces. If updates to LOB data are frequent then it may be more efficient space wise to manipulate smaller chunks of LOB data, especially when the granularity of the update is much less than 32 KB.
The preceding discussion is meant to highlight the differences between the initialization parameter DB_BLOCK_SIZE and the LOB storage parameter CHUNK. Each parameter controls different aspects of the database design, and though related, they should not be automatically equated.
Allocating memory to database structures and proper sizing of these structures can greatly improve database performance when working with LOB data. See Oracle9i Database Performance Guide and Reference for a comprehensive, in-depth presentation of this subject, including understanding memory allocation issues as well as detecting and solving memory allocation problems. The following sections describe a few of the important initialization parameters specifically useful for optimizing LOB performance relative to tuning memory allocation.
The DB_CACHE_SIZE parameter specifies the size of the DEFAULT buffer pool for buffers in bytes. This value is the database buffer value that is displayed when you issue a SQL SHOW SGA statement. Because you cannot change the value of the DB_BLOCK_SIZE parameter without re-creating the database, change the value of the DB_CACHE_SIZE parameter to control the size of the database buffer cache using the ALTER SYSTEM...SET clause... statement. The DB_CACHE_SIZE parameter is dynamic.
To greatly reduce I/O operations while reading and processing LOB data, tune the database instance by partitioning your buffer cache into multiple buffer pools for the tables containing the LOB columns.
By default, all tables are assigned to the DEFAULT pool. Tune this main cache buffer using the DB_CACHE_SIZE initialization parameter and assign the appropriate tables to the keep pool using the DB_KEEP_CACHE_SIZE initialization parameter and to the recycle pool using the DB_RECYCLE_CACHE_SIZE initialization parameter.
The keep pool contains buffers that always stay in memory and is intended for frequently accessed tables that contain important data. The recycle pool contains buffers that can always be recycled and is intended for infrequently accessed tables that contain much less important data. The size of the main buffer cache (DEFAULT) is calculated from the value specified for the DB_CACHE_SIZE parameter minus the values specified for the DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters. Tables are assigned to respective buffer pools (KEEP, RECYCLE, DEFAULT) using the STORAGE (buffer_pool) clause of the CREATE or ALTER TABLE statement. Determine what tables you want allocated to which of these memory buffers and the ideal size of each buffer when you implement your memory allocation design. These parameter values can be changed only in the initialization parameter file and take effect only after stopping and restarting the database.
When working with very large images, set the DB_CACHE_SIZE parameter to a large number for your Oracle instance. For example, to cache a 40MB image, set this parameter to a value of 48MB. Some general guidelines to consider when working with LOB data are:
See Oracle9i Database Performance Guide and Reference for more information about tuning multiple buffer pools.
The SHARED_POOL_SIZE parameter specifies the size in bytes of the shared pool that contains the library cache of shared SQL requests, shared cursors, stored procedures, the dictionary cache, and control structures, Parallel Execution message buffers, and other cache structures specific to a particular instance configuration. This parameter value is dynamic. This parameter represents most of the variable size value that displays when you issue a SQL SHOW SGA statement. Specifying a large value improves performance in multi-user systems. A large value for example, accommodates the loading and execution of interMedia PL/SQL scripts and stored procedures; otherwise, execution plans are more likely to be swapped out. A large value can also accommodate many clients connecting to the server with each client connection using some shared pool space. However, when the shared pool is full, the server is unable to accept additional client connections.
The SHARED_POOL_RESERVED_SIZE parameter specifies the shared pool space that is reserved for large contiguous requests for shared pool memory. This static parameter should be set high enough to avoid performance degradation in the shared pool from situations where pool fragmentation forces Oracle to search for free chunks of unused pool to satisfy the current request.
Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool.
The default value is 5% of the shared pool size, while the maximum value is 50% of the shared pool size. For interMedia applications, a value at or close to the maximum can provide performance benefits.
The LOG_BUFFER parameter specifies the amount of memory, in bytes, used for buffering redo entries to the redo log file. Redo entries are written to the on disk log file when a transaction commits or when the LOG_BUFFER is full and space must be made available for new redo entries. Large values for LOG_BUFFER can reduce the number of redo log file I/O operations by allowing more data to be flushed per write. Large values can also eliminate the waits that occur when redo entries are flushed to make space in the log buffer pool. interMedia applications that have buffering enabled for the LOB data can generate large amounts of redo data when media is inserted or updated. These applications would benefit from a larger LOG_BUFFER size. This is a static parameter.
The following information provides some strategies to consider when you create tables with interMedia column objects containing BLOBs. You can explicitly indicate the tablespace and storage characteristics for each BLOB. These topics are discussed in more detail and with examples in Oracle9i Application Developer's Guide - Large Objects (LOBs). The information that follows is excerpted from Chapter 2 and is briefly presented to give you an overview of the topic. Refer to Oracle9i Application Developer's Guide - Large Objects (LOBs) for more information.
An interMedia column object containing 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 or ORDxxx.import method.
You may want to set the BLOB value to NULL upon inserting the row whenever you do not have the BLOB data at the time of the INSERT operation. In this case, you can issue a SELECT statement at some later time to obtain a count of the number of rows in which the value of the BLOB is NULL, and determine how many rows must be populated with BLOB data for that particular column object.
However, the drawback to this approach is that you must then issue a SQL UPDATE statement to reset the NULL BLOB column to EMPTY_BLOB( ). The point is that you cannot call the OCI or the PL/SQL DBMS_LOB functions on a BLOB that is NULL. These functions work only with a locator, and if the BLOB column is NULL, there is no locator in the row.
If you do not want to set an interMedia column object containing a BLOB to NULL, another option is to set the BLOB value to EMPTY by using the EMPTY_BLOB( ) function in the INSERT statement. Even better, set the BLOB value to EMPTY by using the EMPTY_BLOB( ) function in the INSERT statement, and use the RETURNING clause (thereby eliminating a round-trip that is necessary for the subsequent SELECT statement). Then, immediately call OCI, the import method, or the PL/SQL DBMS_LOB functions to fill the LOB with data. See Oracle9i Application Developer's Guide - Large Objects (LOBs) for an example.
When you create tables and define interMedia column objects containing BLOBs, you can explicitly indicate the tablespace and storage characteristics for each BLOB. The following guidelines can help you fine-tune BLOB storage.
The best performance for interMedia column objects containing BLOBs can often be achieved by specifying storage for BLOBs in a tablespace that is different from the one used for the table that contains the interMedia object with a BLOB. See the ENABLE | DISABLE STORAGE IN ROW clause near the end of this section for further considerations on storing BLOB data inline or out of line. If many different LOBs are to be accessed frequently, it may also be useful to specify a separate tablespace for each BLOB 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 Oracle9i SQL Reference manual for examples, specifically the CREATE TABLE statement and the LOB column example. See Example 11-1.
Example 11-1 assumes that you have already issued a CONNECT statement as a suitably privileged user. This example creates a separate tablespace, called MONTANA, that is used to store the interMedia column object containing BLOB 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 24KB.
SVRMGR> CREATE TABLESPACE MONTANA DATAFILE 'montana.tbs' SIZE 400M; Statement processed. SVRMGR> CREATE TABLE images (imageID INTEGER ,image ORDSYS.ORDImage) LOB (image.source.localData) STORE AS ( TABLESPACE MONTANA STORAGE ( INITIAL 100M NEXT 100M ) CHUNK 24K NOCACHE NOLOGGING );
The LOB index is an internal structure that is strongly associated with the LOB storage.
|
Note: The LOB_index_clause in the CREATE TABLE statement is deprecated beginning with release 8.1.5. Oracle generates an index for each LOB column and beginning with release 8.1.5, LOB indexes are system named and system managed. For information on how Oracle manages LOB indexes in tables migrated from earlier versions, see Oracle9i Database Migration. |
When an interMedia column object containing a BLOB is modified, a new version of the BLOB page is made in order to support consistent reading of prior versions of the BLOB 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 versions 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). Allow for a percentage of LOB storage space to be used as old versions of LOB pages so users can get consistent read results 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 reading of LOBs, it is useful to keep more old versions of LOB pages around. LOB storage may increase if you increase the PCTVERSION value 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 versions 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 interMedia column objects containing BLOBs if the same BLOB data is to be accessed frequently. The CACHE option puts the data into the database buffer and makes it accessible for subsequent read operations. If you specify CACHE, then LOGGING is used; you cannot have CACHE and NOLOGGING.
Use the NOCACHE option (the default) if BLOB data is to be read only once or infrequently, or if you have too much BLOB data to cache, or if you are reading lots of images but none more frequently than others.
See Example 11-1.
An example of when NOLOGGING is useful is with bulk loading or inserting of data. See Example 11-1. For instance, when loading data into the interMedia column objects containing BLOBs, if you do not care about redo logging and can just start the load over if it fails, set the BLOB data segment storage characteristics to NOCACHE NOLOGGING. This setting gives good performance for the initial loading of data. Once you have successfully completed loading the data, you can use the ALTER TABLE statement to modify the BLOB storage characteristics for the BLOB data segment to the desired storage characteristics for normal BLOB operations, such as CACHE or NOCACHE LOGGING.
Set the CHUNK option to the number of blocks of interMedia column objects containing BLOB data that are to be accessed at one time. That is, the number of blocks that are to be read or written using the object.readFromSource or object.writeToSource interMedia audio and video object methods or call, OCILobRead( ), OCILobWrite( ), DBMS_LOB.READ( ), or DBMS_LOB.WRITE( ) during one access of the BLOB value. Note that the default value for the CHUNK option is 1 Oracle block and does not vary across systems. If only 1 block of BLOB 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 CHUNK option to the next largest integer multiple of database block size that is slightly larger than the audio, image, or video data size being inserted. Specifying a slightly larger CHUNK option allows for some variation in the actual sizes of the multimedia data and ensures that the benefit is realized. For large-sized media data, a general rule is to set the CHUNK option as large as possible. The maximum is 32K in Oracle9i. For example, if the database block size is 2K or 4K or 8K and the image data is mostly 21K in size, set the CHUNK option to 24K. See Example 11-1.
If you explicitly specify the storage characteristics for the interMedia column object containing a BLOB, make sure that the INITIAL and NEXT parameters for the BLOB 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 at least 8K, preferably higher (for example, at least 16K).
For LOB storage, Oracle automatically builds and maintains a LOB index that allows quick access to any chunk and thus any portion of a LOB. The LOB index gets the same storage extent parameter values as its LOBs. Consequently, to optimize LOB storage space, you should calculate the size of your LOB index size as well as the total storage space needed to store the media data including its overhead.
Assume that N files comprising of M total bytes of media data are to be stored and that the value C represents the size of the LOB chunk storage parameter. To calculate the total number of bytes Y needed to store the media data:
Y = M + (N*C)
The expression (N*C) accounts for the worst case in which the last chunk of each LOB contains a single byte. Therefore, an extra chunk is allowed for each file that is stored. On average, the last chunk will be half full.
To calculate the total number of bytes X to store the LOB index:
X = CEIL(M/C) * 32
The value 32 indicates that the LOB index requires roughly 32 bytes for each chunk that is stored.
The total storage space needed for the media data plus its LOB index is then X + Y.
The following two examples describe these calculations in detail.
Example 1: Assume you have 500 video clips comprising a total size of 250MB with an average size is 512K bytes. Assume a LOB chunk size of 32768 bytes. The total space needed for the media data is 250MB + (5000*32768) or 266MB. The overhead is 16MB or about 6.5% storage overhead. The total space needed to store the LOB index is CEIL(250MB/32768) * 32 or 244KB. The total space needed to store the media data plus its LOB index is then about 266.6MB.
SQL> SELECT 250000000+(500*32768)+CEIL(250000000/32768)*32 FROM dual; 250000000+(500*32768)+CEIL(250000000/32768)*32 ---------------------------------------------- 266628160
The following table definition could be used to store this amount of data:
CREATE TABLE video_items ( video_id NUMBER, video_clip ORDSYS.ORDVideo ) -- storage parameters for table in general TABLESPACE video1 STORAGE (INITIAL 1M NEXT 10M) -- special storage parameters for the video content LOB(video_clip.source.localdata) STORE AS (TABLESPACE video2 STORAGE (INITIAL 260K NEXT 270M) DISABLE STORAGE IN ROW NOCACHE NOLOGGING CHUNK 32768);
Example 2: Assume you have 5000 images comprising a total size of 274MB with an average size of 56K bytes. Because the average size of the images are smaller than the video clips in the preceding example, it is more space efficient to choose a smaller chunk size, for example 8192 bytes to store the data in the LOB. The total space needed for the media data is 274MB + (5000*8192) or 314MB. The overhead is about 40MB or about 15% storage overhead. The total space needed to store the LOB index is CEIL(274MB/8192) * 32 or 1.05MB. The total space needed to store the media data plus its LOB index is then about 316MB.
SQL> SELECT 274000000+(5000*8192)+CEIL(274000000/8192)*32 FROM dual; 274000000+(5000*8192)+CEIL(274000000/8192)*32 --------------------------------------------- 316030336
The following table definition could be used to store this amount of data:
CREATE TABLE image_items ( image_id NUMBER, image ORDSYS.ORDImage ) -- storage parameters for table in general TABLESPACE image1 STORAGE (INITIAL 1M NEXT 10M) -- special storage parameters for the image content LOB(image.source.localdata) STORE AS (TABLESPACE image2 STORAGE (INITIAL 1200K NEXT 320M) DISABLE STORAGE IN ROW NOCACHE NOLOGGING CHUNK 8192);
When working with very large BLOBs on the order of 1 gigabyte in size, choose a proportionately large INITIAL and NEXT extent parameter size, for example an INITIAL value slightly larger than your calculated LOB index size and a NEXT value of 100 megabytes, to reduce the frequency of extent creation, or commit the transaction more often to reuse the space in the rollback segment; otherwise, if the number of extents is large, the rollback segment can become saturated.
Set the PCTINCREASE parameter value to 0 to make the growth of new extent sizes more manageable. When working with very large BLOBs and the BLOB is being filled up piece by piece in a tablespace, numerous new extents are created in the process. If the extent sizes keep increasing by the default value of 50 percent each time one is created, extents will become unmanageably big and eventually will waste space in the tablespace.
Set the MAXEXTENTS parameter value to suit the projected size of the BLOB or set it to UNLIMITED for safety. That is, when MAXEXTENTS is set to UNLIMITED, extents will be allocated automatically as needed and this minimizes fragmentation.
You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether the interMedia column objects containing a BLOB 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 or 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 bytes, the LOB value is automatically moved out of the row.
This suggests the following guideline: If the interMedia column object containing a BLOB is small (that is, less than 4000 bytes), then storing the BLOB data out of line will decrease performance. However, storing the BLOB 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 doing many UPDATE or SELECT statements to columns other than the interMedia column objects containing BLOBs. If you do not expect the BLOB data to be less than 4000 bytes, that is, if all BLOBs are big, then the default is the best choice because:
The following physical attribute is important for optimum storage of BLOB data in the data block and consequently achieving optimum retrieval performance.
The PCTFREE parameter specifies the percentage of space in each data block of the table or partition reserved for future updates to each row of the table. Setting this parameter to an appropriate value is useful for efficient inline storage of multimedia data. The default value is 10%.
Set this parameter to a high enough value to avoid row chaining or row migration. Because the INSERT statement for BLOBs requires an EMPTY_BLOB column object initialization followed by an UPDATE statement to load the BLOB data into the data block, you must set the PCTFREE parameter value to a proper value especially if the BLOB data will be stored inline. For example, row chaining can result after a row INSERT operation when insufficient space is reserved in the existing data block to store the entire row, including the inline BLOB data in the subsequent UPDATE operation. As a result, the row would be broken into multiple pieces and each piece stored in a separate data block. Consequently, more I/O operations would be needed to retrieve the entire row, including the BLOB data, resulting in poorer performance. Row migration can also result if there is insufficient space in the data block to store the entire row during the initial INSERT operation, and thus the row is stored in another data block.
To make best use of the PCTFREE parameter, determine the average size of the BLOB data being stored inline in each row, and then determine the entire row size, including the inline BLOB data. Set the PCTFREE parameter value to allow for sufficient free space to store an entire row of data in the data block. For example, if you have a large number of thumbnail images that are about 3K bytes in size, and each row is about 3.8K bytes in size, and the database block size is 8K, set the value of PCTFREE to a value that ensures that two complete rows can be stored in each data block in the initial INSERT operation. This approach initially uses 1.6K bytes of space (0.8K bytes/row *2 rows) leaving 6.4K bytes of free space. Because two rows initially use 20% of the data block and 95% after an UPDATE operation and adding a third row would initially use 30% of the data block causing a chain to occur when the third row is updated, set the PCTRFEE parameter value to 75. This setting permits a maximum of two rows to be stored per data block and leaves sufficient space to update each row with its 3K image thumbnail leaving about 0.4K bytes free space minus overhead per data block.
Temporary LOBs created when you have set the table LOB CACHE parameter to TRUE move through the buffer cache; otherwise, they are read directly from and written to disk if the CACHE parameter is set to FALSE.
Use durations for automatic cleanup to save time and effort. Let the database end a duration and free all temporary LOBs associated with a duration because this is more efficient than freeing each one explicitly.
Temporary LOBs create deep copies of themselves on assignments; that is, a new copy of the temporary LOB is created. Use the OCILobLocatorAssign( ) call to assign the source locator to the destination locator when assigning one LOB locator to another. If the source locator refers to a temporary LOB, specify the equals sign (=) in the assignment to ensure that the two LOB locator pointers refer to the same LOB locator; otherwise, the source temporary LOB is deep-copied and a destination locator is created to refer to the new deep copy of the temporary LOB.
You may also want to consider using pass-by reference semantics in PL/SQL or declare pointers to locators, because a pointer assignment does not cause a deep copy. Instead, it causes the pointer to point to the same thing. See the PL/SQL User's Guide and Reference, Oracle9i Database Performance Guide and Reference, and Oracle Call Interface Programmer's Guide for more information.
Because you can partition tables containing interMedia column objects that have BLOBs, BLOB segments can be spread between several tablespaces to:
interMedia column objects containing BLOB data can be partitioned to improve I/O problems and to better balance the I/O load across the data files of the tablespace containing the BLOB 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.
interMedia column objects containing BLOB data can be partitioned to tune database backup and recovery operations to make more efficient use of resources. For example, having two or more tablespaces that are partitioned lets you perform partial database backup and recovery operations on specific data files.
Similarly, tablespaces with interMedia column objects containing BLOBs can be partitioned for easy maintenance of the BLOB data. This is done by logically grouping BLOB 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 Oracle9i Application Developer's Guide - Large Objects (LOBs) for examples and further discussion of each of these topics. See the Oracle9i SQL Reference manual for examples, specifically the CREATE TABLE statement and the Partitioned Table with LOB Columns example.
Use LOB buffering if you need to repeatedly read or write small pieces of interMedia column objects containing BLOB data to specific regions of the BLOB on the client. Typically, for releases of Oracle8i or higher, options, Web servers, and other applications may need to buffer the contents of one or more LOBs in the client address space. Using LOB buffering, you can use up to 512K bytes of buffered access. The advantages of LOB buffering include:
See Oracle9i Application Developer's Guide - Large Objects (LOBs) for further considerations and the use of LOB buffering.
There are a number of bulk loading methods available for loading FILE data into interMedia objects containing BLOBs. These include:
Example 11-2 shows the contents of the load1.bat file, which invokes SQL*Plus and runs the t1.sql procedure (Example 11-3). The db_block_size for this schema is 8K bytes.
sqlplus scott/tiger@intertcp @t1
Example 11-3 shows the contents of the t1.sql file. This procedure:
spool t1.log set echo on connect internal/internal create tablespace Image_h default storage (initial 30m next 400m pctincrease 0)
datafile 'h:\IMPB\Image_h.DBF'
size 2501M reuse; create tablespace Image_i default storage (initial 30m next 400m pctincrease 0)
datafile 'i:\IMPB\Image_i.DBF'
size 2501M reuse; connect scott/tiger drop table image_items; create table image_items( image_id number,-- constraint pl_rm primary key, image_title varchar2(128), image_artist varchar2(128), image_publisher varchar2(128), image_description varchar2(1000), image_price number(6,2), image_file_path varchar2(128), image_thumb_path varchar2(128), image_thumb ordsys.ordimage, image_clip ordsys.ordimage ) -- -- physical properties of table -- -- physical attributes clause pctfree 35 storage (initial 30M next 400M pctincrease 0) -- LOB storage clause (applies to LOB column) LOB (image_clip.source.localdata) store as (disable storage in row nocache nologging chunk 32768) -- -- table properties (applies to whole table) -- Partition by range (image_id) ( Partition Part1 values less than (110001) Tablespace image_h, Partition Part2 values less than (maxvalue) Tablespace image_i ); connect scott/tiger; create or replace procedure load_image ( image_id number, image_title varchar2, image_artist varchar2, image_publisher varchar2, image_description varchar2, image_price number, image_file_path varchar2, image_thumb_path varchar2, thumb_dir varchar2, content_dir varchar2, file_name1 varchar2, file_name2 varchar2) as ctx raw(4000) := NULL; obj1 ORDSYS.ORDIMAGE; obj2 ORDSYS.ORDIMAGE; nxtseq rowid; Begin Insert into image_items( image_id, image_title, image_artist, image_publisher, image_description, image_price, image_file_path, image_thumb_path , image_thumb, image_clip) values ( image_id, image_title, image_artist, image_publisher, image_description, image_price, image_file_path, image_thumb_path , ORDSYS.ORDIMAGE.init('FILE',upper(thumb_dir),file_name1), ORDSYS.ORDIMAGE.init('FILE',upper(content_dir),file_name2)) returning rowid into nxtseq; -- load up the thumbnail image select t.image_thumb, t.image_clip into obj1, obj2 from image_items t where t.rowid = nxtseq for update; obj1.import(ctx); -- import sets properties obj2.import(ctx); Update image_items I set I.image_thumb = obj1, I.image_clip = obj2 where i.rowid = nxtseq; Commit; End; / spool off set echo off
Example 11-4 shows the contents of the load1.sql file. The image load directories are created and specified for each tablespace and user scott is granted read privilege on each load directory. The stored procedure named load_image is then executed, which loads values for each column row. By partitioning the data into different tablespaces, each partition can be loaded in a parallel data load operation.
connect internal/internal drop directory IMAGE_H; drop directory IMAGE_I; create directory IMAGE_H as 'h:\image_files'; create directory IMAGE_I as 'i:\image_files'; grant read on directory IMAGE_H to scott; grant read on directory IMAGE_I to scott; EXEC Load_image(100001,'T_100001',1916,'Publisher','Visit our WEB page' ,8.71,'image_I\T_100001.jpg','image_I\T_100001_thumb1.jpg','image_I','image_ I','T_100001_thumb1.jpg','T_100001.jpg'); EXEC Load_image(100002,'T_100002',2050,'Publisher','Visit our WEB page' ,9.61,'image_I\T_100002.jpg','image_I\T_100002_thumb10.jpg','image_I','image_ I','T_100002_thumb10.jpg','T_100002.jpg'); exit
SQL*Loader provides two methods for loading data:
A conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL commands are generated, passed to Oracle, and executed. Oracle looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically. Use conventional path load if you encounter certain restrictions on direct path loads.
A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed. In addition, if asynchronous I/O operations is available on your host platform, multiple buffers are used for the formatted data blocks to further increase load performance.
See Oracle9i Database Utilities for a complete list of restrictions for using either the conventional path load or direct path load method for loading data using SQL*Loader. See Oracle9i Application Developer's Guide - Fundamentals for more information on LOBs.
Example 11-5 shows the use of the control file to load one ORDVideo object per file into a table named JUKE that has three columns, with the last one being a column object. Each LOB file is the source of a single LOB and follows the column object name with the LOBFILE data type specifications. Two LOB files are loaded in this example.
LOAD DATA INFILE * INTO TABLE JUKE REPLACE FIELDS TERMINATED BY ',' ( id integer external, file_name char(1000), mediacontent column object ( source column object ( 1) localData_fname FILLER CHAR(128),
2) localData LOBFILE (mediacontent.source.localData_fname) terminated by EOF
) ) ) BEGINDATA 1,slynne,slynne.rm 2,Commodores,Commodores - Brick House.rm
Notes:
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 interMedia column object containing a specified BLOB. The data is copied from the source file to the destination interMedia column objects containing a BLOB. When binary data is loaded into an interMedia column object containing a BLOB, no character set conversions are performed. Therefore, the file data must already be in the same character set as the BLOB 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 an interMedia column object containing a BLOB, as well as complete BLOBs. DBMS_LOB can read as well as modify BLOBs, CLOBs, and NCLOBs, and provides read-only operations for BFILEs. The majority of the LOB operations are provided by this package.
The DBMS_LOB.LOADFROMFILE( ) procedure copies all, or part of, a source-external LOB (BFILE) to a destination internal LOB.
You can specify the offsets for both the source LOB (BFILE) and destination interMedia column object containing the BLOB 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 Oracle9i Supplied PL/SQL Packages Reference for more information.
From the Java client, you can use the Java loadDataFromByteArray( ), loadDataFromFile( ), or loadDataFromInputStream( ) methods of interMedia Java Classes to load media data from a given file into a server-side media object designated by the corresponding media locator parameters. You must specify the name of the file from which to load the data and the method returns true if loading is successful, false otherwise. See Oracle interMedia Java Classes User's Guide and Reference for more information.
You can use the Oracle interMedia Clipboard (Release 2) to:
See Oracle interMedia Clipboard (Release2) Installation and Configuration Guide for more information. See Section 1.13.5 for information on how obtain this software and documentation.
You can use the Oracle interMedia Annotator utility to upload media data and an associated annotation into an Oracle8i or higher database where Oracle interMedia is installed. Annotator does this using an Oracle PL/SQL upload template, which contains both PL/SQL calls and Annotator-specific keywords.
See Oracle interMedia Annotator User's Guide for more information.
Example 11-6 shows the contents of the readvideo1.sql file. This procedure reads data from an ORDVideo object with the video stored in a BLOB in the database using the readFromSource method in a PL/SQL script until no more data is found. The procedure then returns a NO_DATA_FOUND exception when the read operation is complete and displays an "End of data" message.
create or replace procedure readVideo1(i integer) as obj ORDSYS.ORDVideo; buffer RAW (32767); numbytes BINARY_INTEGER := 32767; startpos integer := 1; read_cnt integer := 1; ctx RAW(4000) := NULL; BEGIN Select mediacontent into obj from juke where id = 100001; LOOP obj.readFromSource(ctx,startpos,numbytes,buffer); startpos := startpos + numBytes; read_cnt := read_cnt + 1; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data '); DBMS_OUTPUT.PUT_LINE('doing read '|| read_cnt); DBMS_OUTPUT.PUT_LINE('start position :'|| startpos); END; / show errors
The benchmark environment for the hardware and software for the interMedia BLOB read tests that were performed are described in this section.
The server side consisted of a quad 200MHz Pentium Pro processor with 3GB of memory. The I/O disk subsystem consisted of a raid 0 stripe set supported by four Adaptec controllers. The system was running MS Windows NT V4.0 Service Pack 3. The OCI experiments were conducted in a client/server environment where the client was also a quad 200MHz Pentium Pro processor linked to the server using a 100Mbits Ethernet connection.
The database was partitioned by range using a range ID such that each client reader or loader process used a dedicated database partition. Tests were conducted with a database block size set to 8K and 16K, a LOB chunk size set to 32K, and a read size (1 round-trip) set to 32K for the interMedia import( ) method in PL/SQL tests, and a LOB buffer size set to 32K to 64K for the OCI tests.
BLOB I/O tests were conducted in an MS Windows NT environment running Oracle interMedia. BLOB read tests were conducted with the interMedia readFromSource( ) method in a PL/SQL script to read BLOBs from the database, as well as making OCI calls without callbacks to perform BLOB read operations from C++. Parallel processes were submitted on the client system to read BLOBs residing on the server side making use of the 100 megabit network bandwidth. Database connections ranged from 6 to 16 for the BLOB read tests.
A benchmark was performed to measure the performance of an Oracle-based system in a setting modeling a real-life audio server application. The Oracle server serves multiple requests by clients to a set of CDs. CDs are stored in Oracle8i or higher using the Oracle interMedia. The CD access pattern is modeled by an exponential distribution to simulate that some CDs are more popular than others. A client has a tolerance on the response time of a request. Each request asks for a particular amount of audio data. The throughput of the server, defined by the amount of audio data provided per unit time, is measured, subjected to the following constraints:
Throughput levels as high as 29 MB/second using a large cache of 1.7GB, a LOB chunk size set to 32K, and with OCI using buffered read operations to read BLOBs locally on the backend, memory-rich server. Using a less memory-rich server system with a 320MB cache buffer size, throughput decreased by one third to a low of 20MB/second level.
The performance-limiting factor was the 100 megabit bandwidth, which became saturated in the client/server tests. All tests with OCI had caching turned on. Using the interMedia readFromSource( ) method in a PL/SQL procedure, and with no cache set, the throughput was limited to 18MB/second. The limiting factor for performance for reading BLOB data was the I/O subsystem in the absence of caching.
The following guidelines can be used to help you achieve the best performance when working with interMedia objects:
See Oracle9i 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 11.3. The following guidelines should be considered:
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|