LOB Segment Analysis (dlob.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes LOB (Large Object) segments in the database, displaying storage characteristics, performance settings, and associated indexes. It helps DBAs understand LOB storage configuration, identify performance tuning opportunities, and manage space allocation for CLOB, BLOB, and NCLOB columns.

Script

--  OWNER                                                 NOT NULL VARCHAR2(30)
 -- TABLE_NAME                                            NOT NULL VARCHAR2(30)
 -- COLUMN_NAME                                                    VARCHAR2(4000)
 -- SEGMENT_NAME                                          NOT NULL VARCHAR2(30)
 -- INDEX_NAME                                            NOT NULL VARCHAR2(30)
 -- CHUNK                                                          NUMBER
 -- PCTVERSION                                            NOT NULL NUMBER
 -- CACHE                                                          VARCHAR2(10)
 -- LOGGING                                                        VARCHAR2(3)
 -- IN_ROW                                                         VARCHAR2(3)
select
 OWNER,
 TABLE_NAME,
 COLUMN_NAME,
 SEGMENT_NAME,
 INDEX_NAME,
 CHUNK,
 PCTVERSION,
 CACHE,
 LOGGING,
 IN_ROW
from dba_lobs
where owner like nvl(upper('&owner'),'%')
and table_name like nvl(upper('&table'),'%')
and segment_name like nvl(upper('&lob_segment'),'%')
order by 1,2,3
/

Usage

-- Run the script in SQL*Plus or SQLcl
@dlob.sql

-- When prompted, enter parameters:
-- owner: Schema owner (% for all)
-- table: Table name pattern (% for all)
-- lob_segment: LOB segment name pattern (% for all)

-- Examples:
Enter value for owner: HR
Enter value for table: %
Enter value for lob_segment: %

-- Find specific LOB segments
Enter value for owner: APP
Enter value for table: DOCUMENTS
Enter value for lob_segment: SYS_LOB%

Parameters

• &owner - Schema owner (supports wildcards)
• &table - Table name pattern
• &lob_segment - LOB segment name pattern

Required Privileges

• SELECT on DBA_LOBS
• Typically requires DBA role or SELECT ANY DICTIONARY

Sample Output

OWNER  TABLE_NAME    COLUMN_NAME    SEGMENT_NAME           INDEX_NAME             CHUNK PCTVERSION CACHE      LOG IN_ROW
------ ------------- -------------- ---------------------- ---------------------- ----- ---------- ---------- --- ------
HR     EMPLOYEES     RESUME         SYS_LOB0000074213C00   SYS_IL0000074213C00    8192         10 NO         YES YES
HR     DOCUMENTS     PDF_CONTENT    SYS_LOB0000074234C00   SYS_IL0000074234C00   16384         20 YES        NO  NO
HR     DOCUMENTS     XML_DATA       SYS_LOB0000074234C00   SYS_IL0000074234C00   16384         20 YES        NO  YES
APP    ATTACHMENTS   FILE_DATA      LOB_ATTACHMENTS_FILE   IDX_ATTACHMENTS_FILE  32768         10 NO         YES NO
APP    IMAGES        IMAGE_BLOB     SYS_LOB0000074456C00   SYS_IL0000074456C00   32768          5 YES        NO  NO

Key Output Columns

• OWNER - Schema owning the table
• TABLE_NAME - Table containing the LOB column
• COLUMN_NAME - Name of the LOB column
• SEGMENT_NAME - LOB segment name (system or user-defined)
• INDEX_NAME - Associated LOB index name
• CHUNK - Chunk size in bytes (unit of LOB manipulation)
• PCTVERSION - Percentage of LOB storage for versioning
• CACHE - Whether LOB is cached in buffer cache
• LOGGING - Whether changes are logged
• IN_ROW - Whether small LOBs stored in-row

Understanding LOB Storage

Storage Parameters

• CHUNK - Basic unit of LOB access (multiple of block size)
• PCTVERSION - Space reserved for read consistency
• IN_ROW - LOBs < 4000 bytes can be stored inline

Performance Settings

• CACHE - Improves read performance for frequently accessed LOBs
• LOGGING - Affects recovery and performance
• NOCACHE NOLOGGING - Fastest for bulk loads

Common Use Cases

Find Large LOB Segments

SELECT l.owner, l.table_name, l.column_name,
       s.bytes/1024/1024 size_mb
FROM dba_lobs l, dba_segments s
WHERE l.segment_name = s.segment_name
AND l.owner = s.owner
AND s.bytes > 100*1024*1024  -- Over 100MB
ORDER BY s.bytes DESC;

Identify Non-Cached LOBs

SELECT owner, table_name, column_name, segment_name
FROM dba_lobs
WHERE cache = 'NO'
AND owner NOT IN ('SYS','SYSTEM')
ORDER BY owner, table_name;

Find LOBs with High Version Retention

SELECT owner, table_name, column_name, pctversion
FROM dba_lobs
WHERE pctversion > 20
ORDER BY pctversion DESC;

Performance Tuning

Optimize Chunk Size

-- Check average LOB size
SELECT AVG(DBMS_LOB.GETLENGTH(column_name))/1024 avg_size_kb,
       MAX(DBMS_LOB.GETLENGTH(column_name))/1024 max_size_kb
FROM owner.table_name;

-- Chunk size should be close to average LOB size

Enable Caching for Small LOBs

-- Modify LOB to enable caching
ALTER TABLE owner.table_name
MODIFY LOB (column_name) (CACHE);

Reduce Version Retention

-- Lower PCTVERSION for less read consistency needs
ALTER TABLE owner.table_name
MODIFY LOB (column_name) (PCTVERSION 5);

Space Management

LOB Segment Sizes

SELECT l.owner, l.table_name, l.column_name,
       s.segment_name, s.bytes/1024/1024 size_mb,
       s.extents, s.blocks
FROM dba_lobs l, dba_segments s
WHERE l.segment_name = s.segment_name
AND l.owner = s.owner
AND l.owner = '&owner'
ORDER BY s.bytes DESC;

Shrink LOB Segments

-- Enable row movement first
ALTER TABLE owner.table_name ENABLE ROW MOVEMENT;

-- Shrink LOB segment
ALTER TABLE owner.table_name
MODIFY LOB (column_name) (SHRINK SPACE);

Best Practices

Storage Configuration

1. Set appropriate CHUNK size based on average LOB size
2. Use CACHE for frequently accessed small LOBs
3. Consider SECUREFILE LOBs for better performance
4. Use compression for large text/XML LOBs

Performance Optimization

• Enable caching for LOBs < 32KB accessed frequently
• Use NOCACHE NOLOGGING for bulk loads
• Set PCTVERSION based on read consistency needs
• Consider partitioning tables with large LOBs

Migration to SecureFile LOBs

Check BasicFile LOBs

-- Find BasicFile LOBs (candidates for migration)
SELECT owner, table_name, column_name, securefile
FROM dba_lobs
WHERE securefile = 'NO'
AND owner NOT IN ('SYS','SYSTEM');

Benefits of SecureFile

• Compression and deduplication
• Encryption support
• Better space management
• Improved performance

Related Scripts

• Table Storage Analysis (dtable.md) - Table storage details
• Large Table Statistics (dstatbig.md) - Statistics for large objects
• Database Objects (dobject.md) - Object overview

Advanced Analysis

LOB Access Patterns

-- Monitor LOB read/write activity
SELECT segment_name,
       physical_reads, physical_writes,
       physical_reads_direct, physical_writes_direct
FROM v$segment_statistics
WHERE segment_name IN (
    SELECT segment_name FROM dba_lobs
    WHERE owner = '&owner'
)
AND statistic_name IN ('physical reads','physical writes');

LOB Fragmentation

-- Check for fragmented LOB segments
SELECT l.owner, l.table_name, l.column_name,
       s.extents, s.bytes/s.blocks/1024 avg_block_fill_kb
FROM dba_lobs l, dba_segments s
WHERE l.segment_name = s.segment_name
AND l.owner = s.owner
AND s.extents > 100
ORDER BY s.extents DESC;