HOW TO

Alter the ST_Geometry points' LOB segment from NOCACHE to CACHE

Last Published: April 25, 2020

Summary

Instructions provided describe how to alter the ST_Geometry points' LOB segment from NOCACHE to CACHE. If a table containing an ST_Geometry attribute 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.

To improve performance it is recommended that the LOB segment that represents the ST_Geometry points' attribute is CACHED, therefore, reducing physical I/O operations.

When creating a feature class with ArcGIS, ensure the ArcSDE dbtune keyword being used correctly sets the CACHE parameter for the LOB segment. The following keyword parameter ST_GEOM_LOB_STORAGE controls the st_geometry LOB attribute storage.

Code:
ST_GEOM_LOB_STORAGE " STORE AS (
# TABLESPACE <tablespace_name>
ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
# INDEX (TABLESPACE <tablespace_name>)
) "


Procedure

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

  1. Identify if the table's ST_Geometry attribute's LOB segment is set to NOCACHE.

    Code:
    SQL> SELECT segment_name, cache
    2 FROM user_lobs
    3 WHERE table_name = 'STREETS' and column_name = 'SHAPE';

    SEGMENT_NAME CACHE
    ------------------------- -------
    SYS_LOB0000346903C00017$$ NO


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

    Code:
    SQL> ALTER TABLE streets
    2 MODIFY LOB (shape.points)(CACHE);

    Table altered.

    SQL> SELECT segment_name, cache
    2 FROM user_lobs
    3 WHERE table_name = 'STREETS' and column_name = 'SHAPE';

    SEGMENT_NAME CACHE
    ------------------------- -------
    SYS_LOB0000346903C00017$$ YES



    The above example demonstrates changing the 'STREETS' table's LOB segment from NOCACHE to CACHE.

    Note:
    See Oracle's documentation for additional information. A link to Oracle's documentation is located in the Related Information section below.

Article ID: 000009511

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

Related Information

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options