HOW TO

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

Last Published: April 25, 2020

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.

Article ID: 000010291

Software:
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options