English

How To: Improve performance of raster retrieval by altering the LOB segment of a block table's block_data column from NOCACHE to CACHE

Summary

To improve performance and reduce physical I/O operations, it is recommended that the block_data column's LOB segment be set to CACHED.

Instructions provided show how to alter a raster block table's LOB segment from NOCACHE to CACHE. If a raster block table's block_data column is created without specifying the storage options for the LOB segment, Oracle's default setting is NOCACHE, meaning LOB values are not brought into the buffer cache.

Procedure

To change the default NOCACHE setting for LOB segments to CACHE, use the ALTER TABLE command in SQL*Plus.

  1. Identify if the annotation class' table element attribute's LOB segment is set to NOCACHE.
    Code:

    SQL> SELECT table_name, cache
    2 FROM user_lobs
    3 WHERE column_name = 'BLOCK_DATA'
    4 AND cache = 'NO';

    TABLE_NAME CACHE
    ----------------------- -------
    SDE_BLK_20 NO

  2. Alter the table and verify that the LOB segment is now cached.
    Code:

    SQL> ALTER TABLE sde_blk_20
    2 MODIFY LOB (block_data)(CACHE);

    Table altered.

    SQL> SELECT table_name, cache
    2 FROM user_lobs
    3 WHERE column_name = 'BLOCK_DATA'
    4 AND table_name = 'SDE_BLK_20';

    TABLE_NAME CACHE
    ----------------------- -------
    SDE_BLK_20 YES

  3. The above example demonstrates changing the raster blocks table's block_data column's LOB segment from NOCACHE to CACHE.

    For more information, see Oracle's documentation.