8 Tuning Tips for the DBA

8.1 Setting Database Initialization Parameters

The information that follows is an excerpt from Oracle Database Performance Tuning Guide and Oracle Database Reference, and is presented as an overview of the topic. Refer to Oracle Database Performance Tuning Guide and Oracle 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 remain 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:

• Buffer cache (DB_CACHE_SIZE) -- the size in bytes of the cache of standard blocks

• Shared pool (SHARED _POOL_SIZE) -- the size in bytes of the area devoted to shared SQL and PL/SQL statements

• Large pool (LARGE_POOL_SIZE) (default is 0 bytes) -- the size in bytes of the large pool used in shared server systems for session memory, parallel execution for message buffers, and by backup and restore processes for disk I/O buffers

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 Oracle Database Performance Tuning Guide 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 KB.

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 48 megabytes (MB)). 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 8 KB standard block size with a default DB_CACHE_SIZE of 48 MB or whatever size you want to specify. Then you can use any of the block sizes of 2 KB, 4 KB, 8 KB, 16 KB, or the maximum 32 KB 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 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.

DB_BLOCK_SIZE

The DB_BLOCK_SIZE parameter is the size in bytes of 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 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 ORDDoc, 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 inline or on the same database page as the rest of the row data. LOB data can be stored inline 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 8.2.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.

Tuning Memory Allocation

Allocating memory to database structures and proper sizing of these structures can greatly improve database performance when working with LOB data. See Oracle Database Performance Tuning Guide 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.

DB_CACHE_SIZE

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.

BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE - Tuning Multiple Buffer Pools Using the Standard Block Size

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 40 MB image, set this parameter to a value of 48 MB. Some general guidelines to consider when working with LOB data are:

• You should have enough buffers to hold the object, regardless of table LOB logging and cache settings. See Section 8.2 for more information.

• When using log files you should make the log files larger, otherwise, more time is spent waiting for log switches. See Section 8.2 for more information.

• If the same BLOB is to be accessed frequently, set the table LOB CACHE parameter to TRUE. See Section 8.2 for more information.

• Use a large page size (DB_BLOCK_SIZE) if the database is going to contain primarily large objects.

See Oracle Database Performance Tuning Guide for more information about tuning multiple buffer pools.

SHARED_POOL_SIZE

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 is displayed when you issue a SQL SHOW SGA statement. Specifying a large value improves performance in multiuser 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 database is unable to accept additional client connections.

SHARED_POOL_RESERVED_SIZE

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.

LOG_BUFFER

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 for each write operation. 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.

8.2 Issues to Consider in Creating Tables with Column Objects Containing BLOBs

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 Oracle Database Application Developer's Guide - Large Objects. The information that follows is excerpted from Chapter 2 and is briefly presented to give you an overview of the topic. Refer to Oracle Database Application Developer's Guide - Large Objects for more information.

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
            );

SQL> SELECT 250000000+(500*32768)+CEIL(250000000/32768)*32 FROM dual;

250000000+(500*32768)+CEIL(250000000/32768)*32
----------------------------------------------
                                     266628160

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);

SQL> SELECT 274000000+(5000*8192)+CEIL(274000000/8192)*32 FROM dual;

274000000+(5000*8192)+CEIL(274000000/8192)*32
---------------------------------------------
                                    316030336

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);

8.3 Improving Multimedia Data INSERT Performance in Objects Containing LOBs

There are a number of bulk loading methods available for loading FILE data into interMedia objects containing BLOBs. These include:

• interMedia import( ) method in a PL/SQL stored procedure

• SQL*Loader (conventional path load and direct path load)

• OCILobLoadFromFile( ) relational function

• DBMS_LOB.LOADFROMFILE( ) procedure in the DBMS_LOB package

• DBMS_LOB.LOADBLOBFROMFILE( ) procedure in the DBMS_LOB package

• Java loadDataFromFile( ) or loadDataFromInputStream( ) methods of Oracle interMedia Java Classes to load media data from a client file

Using interMedia import( ) Method in a PL/SQL Stored Procedure

Example 8-2 shows the contents of the load1.bat file, which invokes SQL*Plus and runs the t1.sql procedure (Example 8-3). The db_block_size for this schema is 8 kilobytes.

sqlplus scott/tiger@intertcp @t1

• Creates two tablespaces.

• Creates the image_items table and defines the physical properties of the table, specifically the physical attributes and LOB storage attributes.

• Partitions the table storage into each tablespace by range using the image_id value.

• Creates the load_image stored procedure that:

  • Declares a variable nxtseq defined as the ROWID data type.

  • Inserts a row into the image_items table and uses the INSERT RETURNING ROWID statement to return the ROWID value for fastest access to the row for loading the image BLOB data into the object columns of each row using the import( ) method.

  • Sets the image attribute properties automatically (by means of the import operation) for each loaded image (note that thumbnail images are stored inline, and regular images are stored out of line).

  • Commits the update operation.

spool t1.log
set echo on
connect sys/change_on_install as sysdba

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

connect sys/change_on_install as sysdba
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

Using SQL*Loader

SQL*Loader provides two methods for loading data:

• Conventional Path Load

  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 statements are generated, passed to Oracle, and executed. Oracle looks for partially filled blocks and attempts to fill them on each insert operation. Although appropriate during normal use, this can slow bulk loads dramatically. Use conventional path load if you encounter certain restrictions on direct path loads.

• Direct Path Load

  A direct path load eliminates much of the 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 the asynchronous I/O operations feature is available on your host platform, multiple buffers are used for the formatted data blocks to further increase load performance.

See Oracle 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 Oracle Database Application Developer's Guide - Fundamentals for more information about LOBs.

Using SQL*Loader to Load Multimedia Data into Oracle Database Using interMedia Column Objects

Example 8-5 shows the use of the control file to load one ORDVideo object for each 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

1. The FILLER field is mapped to the 128-byte long data field which is read using the SQL*Loader CHAR data type.

2. SQL*Loader gets the LOB file name from the localData_fname FILLER field. It then loads the data from the LOB file (using the BLOB data type) from its beginning to the EOF character, whichever is reached first. Note that if no existing LOB file is specified, the localData field is initialized to empty.

Using the OCILobLoadFromFile( ) Relational Function

Oracle Call Interface (OCI) is an application programming interface (API) that enables you to manipulate data and schemas in a 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.

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 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 Oracle Database PL/SQL Packages and Types Reference for more information.

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

The DBMS_LOB.LOADBLOBFROMFILE( ) procedure loads a persistent or temporary BLOB instance with data from a BFILE. This procedure achieves the same result as using DBMS_LOB.LOADFROMFILE, but returns the new offset in bytes in the destination BLOB right after the end of the write operation, which is also where the next write operation should begin and the offset in bytes in the source BFILE right after the end of the read operation, which is also where the next read operation should begin.

To use this procedure, you can specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source BFILE. The value you pass for the amount parameter to the DBMS_LOB.LOADBLOBFROMFILE function must be either an amount less than or equal to the actual size (in bytes) of the BFILE you are loading or the maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE. Passing this latter value causes the function to load the entire BFILE, which is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.

To use this procedure, the target BLOB instance and the source BFILE must both exist and the BFILE must be opened and later closed after calling this procedure.

See Oracle Database PL/SQL Packages and Types Reference for more information.

Using Java loadDataFrom...( ) Methods to Load Media Data from a Client File

From the Java client, you can use the Java loadDataFromByteArray( ), loadDataFromFile( ), or loadDataFromInputStream( ) methods of Oracle 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 API Reference for more information.

8.4 Transferring Multimedia Data Using Oracle Data Pump

Oracle Data Pump ("Data Pump") enables very high-speed movement of data and metadata from one database to another using the Data Pump Export and Data Pump Import utilities. Data Pump enables you to specify whether or not a job should move a subset of the data and metadata. This is done using data filters and metadata filters, which are implemented through Export and Import parameters using the Metadata API and the Data Pump API. The Metadata API uses the procedures provided in the DBMS_METADATA PL/SQL package and the Data Pump API uses the procedures provided in the DBMS_DATAPUMP PL/SQL package. See Oracle Database Concepts, Oracle Database Utilities, and Oracle Database PL/SQL Packages and Types Reference for more information.

8.5 Reading Data from an ORDVideo Object Using the readFromSource( ) Method in a PL/SQL Script

Example 8-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

8.6 Getting the Best Performance Results

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

• Because interMedia objects are big, attain the best performance by reading and writing large CHUNKS of an interMedia object value at a time. This helps in several respects:

  • If you are accessing the interMedia object from the client side and the client is on a different node than the server, large read/write operations reduce network overhead.

  • If you are using the NOCACHE option, each small read/write operation incurs an I/O impact. Reading and writing large quantities of data reduces the I/O impact.

  • Writing to the interMedia object creates a new version of the interMedia object CHUNK. Therefore, writing small amounts at a time will incur the cost of a new version for each small write operation. If logging is on, the CHUNK is also stored in the redo log.

• If you need to read or write small pieces of interMedia object data on the client, use LOB buffering (see OCILobEnableBuffering( ), OCILobDisableBuffering( ), OCILobFlushBuffer( ), OCILobWrite( ), OCILobRead( ) in Oracle Call Interface Programmer's Guide for more information.). Turn on LOB buffering before reading or writing small pieces of interMedia object data. For more information about LOB buffering, its advantages, guidelines for use, and usage, see Oracle Database Application Developer's Guide - Large Objects.

• Use interMedia methods (readFromSource( ) and writeToSource( )) for audio and video data or OCILobWrite( ) and OCILobRead( ) with a callback for image data so media data is streamed to and from the BLOB. Ensure that the length of the entire write operation is set in the numBytes parameter using interMedia methods or in the amount parameter using OCI calls on input. Whenever possible, read and write in multiples of the LOB CHUNK size.

• Use a checkout/checkin model for LOBs. LOBs are optimized for the following:

  • Updating interMedia object data: SQL UPDATE operations, which replaces the entire BLOB value.

  • Copying the entire LOB data to the client, modifying the LOB data on the client side, and copying the entire LOB data back to the database. This can be done using OCILobRead( ) and OCILobWrite( ) with streaming.

• Commit changes frequently.

• Follow temporary LOB performance guidelines.

  See Oracle Database Application Developer's Guide - Large Objects for information and guidelines about using temporary LOBs.

• Use interMedia column objects containing BLOBs in table partitions.

  See the information about LOBs in partitioned tables in Oracle Database Application Developer's Guide - Large Objects and see Oracle Database SQL Reference for examples, specifically the CREATE TABLE statement and the Partitioned Table with LOB Columns example.

See Oracle Database Application Developer's Guide - Large Objects for more information.

8.7 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 8.3. The following guidelines should be considered:

• Use the CACHE option on LOBs if the same LOB data is to be accessed frequently by other users.

• Increase the number of buffers if you are going to use the CACHE option.

• Have enough buffers to hold the object. Using a small number of buffers for large objects is not good. Set the DB_CACHE_SIZE parameter to a value that you know will hold the object.

• Ensure that your redo log files are much larger than they usually are; otherwise, you may be waiting for log switches, especially if you are making many updates to your LOB data.

• Ensure that you use a larger page size (DB_BLOCK_SIZE), especially if the majority of the data in the database is LOB data.