Skip to content

Troubleshooting production problems with oracle LOB – complete reference for new and advanced oracle DBA’s




whether you are a new or advanced DBA , below metalink document contains the various issues that a DBA an encounter when the database is managing LOB’s and how to handle different kinds of issues.

Subject: Troubleshooting Guide (TSG) – Large Objects (LOBs)
  Doc ID: 846562.1 Type: TROUBLESHOOTING
  Modified Date: 16-SEP-2009 Status: REVIEWED

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
     ISSUE CLARIFICATION (IC)
     DATA COLLECTION (DC)
     ISSUE VERIFICATION (IV)
     RESEARCH (R)
     CAUSE DETERMINATION (CD)
     CAUSE JUSTIFICATION (CJ)
     POTENTIAL SOLUTIONS (PS)
     POTENTIAL SOLUTION JUSTIFICATION (PSJ)
     QUESTION (Q)
     ANSWER (A)
  References


Applies to:

Oracle Server – Enterprise Edition – Version: 8.0.3.0 to 11.1.0.7
Information in this document applies to any platform.

Purpose

This is the troubleshooting guide for handling Large Objects (LOBs) within Oracle Support and is part of the Enhanced Problem Analysis (EPA) project.

The purpose of this document is to instruct engineers and customers how to diagnose LOB related issues / questions

This is not a comprehensive list of problems and solutions … but a ‘road map’ for problem / solution question / answer resolution

This document will use the Oracle Diagnostic Methodology (ODM) approach and provide some detailed suggestions for the usage of each section of the process.

CUSTOMERS: Please note … this document contains references to some notes that are not available on Metalink

    This document is published to provide insight into the structured approach by which Oracle Support addresses issues and questions.

Last Review Date

June 26, 2009

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

ISSUE CLARIFICATION (IC)

This is the problem description in your own words based on the information provided by in the initial statement in the Service Request (SR). 
It is also the initial assessment of the business impact to the customer.

It is important that you restate the issue in your own words as it is possible that your perception / understanding of the issue is not the real issue or is not
the same as that of the person who wrote the initial statement in the SR.

The IC should be short and precise and should match the SR abstract

This section should also set the severity of the issue

This section can include information from a DATA COLLECTED (DC) section (see below) … and is helpful that it include such information as

*  Does the issue with the LOB occur every time the operation is attempted?

*  Does the operation on the LOB occur with every lob in the database .. or just  certain ones

*  If the LOB is moved to/from an Automatic Segment Space Managed (ASSM) tablespace … does the issue still occur?

In other words .. under what conditions does the issue occur? … when does it not occur?


EXAMPLES
(of the top 4 most common LOB problems)

Temporary LOBS not being released / reused

Using Oracle 10.2.0.4 customer finds that after COMMIT temporary lobs are not being released for reuse

As a result

   ORA-1691: unable to extend lob segment SCOTT.MYLOB by 1024 in tablespace USERS

is occuring and is stopping production operations
LOB performance

After upgrading from 9.2.0.8 to 10.2.0.4 customer reports that inserts into BLOBS are slow

Prior to the upgrade inserts or updates of 400mb of JPG files took 3 minutes

After the upgrade the same size of inserts takes 1 hour to insert

The LOB segment exists in a automatic segment space management (ASSM) tablespace

The slowdown is preventing batch processes on Testing / Development instance from being able to complete overnight
LOB segment growth / shrink issues

LOB Segment (CLOB) will not shrink even though it is in an automatic segment space managed (ASSM) tablespace and row movement is enabled

LOB tablespace on production system running out of space
ORA-1555s involving LOBS

ORA-01555: snapshot too old: rollback segment number with name "" too small

   occurs while selecting from a table containing a LOB column

This prevents a crutial management report from being able to be run

DATA COLLECTION (DC)

Data collection for LOB issues should include any and all information that show that the issue is occurring

DC can include .. but is not limited to .. queries from dictionary tables … OEM screenshots … trace files … alert logs … your own testing

This section is not needed for Question / Answer service requests

EXAMPLES (of the top 4 most common problems)

Temporary LOBS not being released / reused

The following query will show that temporary LOB segments are not being released and which user is holding onto the temporary LOB

SELECT USERNAME, TABLESPACE
FROM V$TEMPSEG_USAGE
WHERE CONTENTS = ‘TEMPORARY’ AND SEGTYPE LIKE ‘LOB_%’;

NOTE: This is a normal condition (please proceed with review of other sections)

USERNAME  TABLESPACE
—————–  ——————-
tc                      TEMP

LOB performance

1) Gather and upload an AWR report for the period of time that the LOB operations are running slow

     Note 748642.1 What is AWR( Automatic workload repository ) and How to generate the AWR report?

SQL ordered by Elapsed Time
* % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed Time (s)    CPU Time (s)    Executions    Elap perExec (s)   % Total DB Time   SQL Id      SQL Module    SQL Text
2,458                         1,612                   3,778              0.65                         26.89                       bb1g705   awrsud            INSERT INTO My_App_table….

SQL ordered by CPU Time
CPU Time (s)    Elapsed Time (s)    Executions    CPU per Exec (s)    %Total DB Time    SQL Id        SQL Module     SQL Text
1,612                   2,458                         3,778              0.43                           26.89                        bk14425    mwrsud            INSERT INTO My_App_table…
 …
2) Take a Screenshot of the ASH report

    Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Part Number B14211-03

          5 Automatic Performance Statistics

               5.3.6 Generating Active Session History Reports

LOB segment growth / shrink issues

Information needs to be gathered over time in relation to the amount of actual data inserted into the lob

This usually requires a testcase … to isolate the table / lob segment

Generally select from DBA_LOBS as well as DBA_SEGMENTS is needed to show the unexpected growth

< No easy example for this case >

ORA-1555s involving LOBS

The key information to gather for this case is the exact error message displayed on the client screen and the alert log

CLIENT EXAMPLES
 

     ORA-01555: snapshot too old: rollback segment number with name "" too small

     ORA-01555: snapshot too old: rollback segment number # with name "???" too small

ALERT LOG

     ORA-01555 caused by SQL statement below (Query Duration=9999 sec, SCN: 0x0000.008a7c2d):   

ISSUE VERIFICATION (IV)

Information from the DATA COLLECTED (DC) <above> should be referenced as proof of the statement(s) in the ISSUE VERIFICATION (IC) section

EXAMPLE:

Issue verified by DATA COLLECTION detailed on <date time>

NOTE : As as part of the IV methodology step the relevant error codes should be entered into the Error/Bug section of the SR.

RESEARCH (R)

Research should be based on the information gathered in the ISSUE CLARIFICATION (IC), DATA COLLECTED (DC) or QUESTION (Q) section

This section should include KEYWORDS used so that the customer and other engineers can see how you are researching

When searching for documents about LOBS … is always better to search LOB rather than CLOB … or BLOB … as these limit your results

It is also helpful to use quotes ("") when searching for small phrases like "LOB SEGMENT"

Resources for research

    WEBIV (Internal) / Metalink (External)
    The Oracle Documentation for the version exhibiting the behavior (External)
    Previous Service Requests (SR) (Internal)
    Internal developers mailing lists (Internal)
    External search engines (Google / MSN / Yahoo) (External)
    Your own testing of the condition is research … but should be included as a DATA COLLECTED (D) section
    

EXAMPLES

Temporary LOBS not being released / reused

KEYWORDS: TEMPORARY LOBS

Bug 2784791
Status: 32,Not a Bug. To Filer
Abstract: TEMPORARY LOBS NOT FREED AUTOMATICALLY AFTER PL/SQL BLOCK EXECUTION

     ** BUG superceded by <Bug 5723140> **

Note 386341.1 How to determine the actual size of the LOB segments

Note 750209.1 Temporary LOB space not released after commit ora-1652 being hit

Note 5723140.8  Bug 5723140 – Temp LOB space not released after commit

Description

While using temporary LOBs the temp segment used for this purpose remains active until the session exits. When there is a request to cleanup those temp lobs we do free space
from temp tablespace and the same session can reuse it, but the temp segment is not released which means it is not available for use to other sessions. The actual cleanup
happens only on session exit. This is intentional.

This fix introduces a new event 60025 such that when there are no active temp lobs in the session (ie: both cache temp lob and no-cache temp lobs used are zero) then the temp
segment itself will also be freed releasing the space for other sessions to use.

Note that this change is disabled by default.

This issue is fixed in

     * 10.2.0.4 (Server Patch Set)
     * 11.1.0.6 (Base Release)

Bug 5723140
Fixed In Ver:  10.2.0.4  11.1.0.6
Abstract: TEMP SPACE NOT RELEASED AFTER COMMIT ON TABLE WITH XMLTYPE AND XSD

Details:
While using temporary LOBs the temp segment used for this purpose remains active until the session exits. When there is a request to cleanup those temp lobs we do free space
from temp tablespace and the same session can reuse it, but the temp segment is not released which means it is not available for use to other sessions.

The actual cleanup happens only on session exit. This is intentional.

This fix introduces a new event 60025 such that when there are no active temp lobs in the session (ie: both cache temp lob and no-cache temp lobs used are zero) then the temp
segment itself will also be freed releasing the space for other sessions to use.

Note that this change is disabled by default.

KEYWORDS: 60025 LOB

Note 802897.1  How to Release the Temp LOB Space and Avoid Hitting ORA-1652

     alter session set events ‘60025 trace name context forever’;
LOB performance

KEYWORDS: LOB HWM Filter on list:

Note 837883.1 LOB HWM CONTENTION : Using the AWR Reports To Confirm and Problem; Confirm and Verify the Fix

Bug 6376915 – ENQ: HW – CONTENTION WITH LOB SEGMENTS

Bug 5396550 INFINITE HW ENQUEUE LOCKING DUE TO A LOB SEGMENT MANAGEMENT
LOB segment growth / shrink issues

KEYWORDS: LOB GROW  Filter on list: LOB

Note 401881.1  Clob In Tablespace With Segment Space Management Auto Does Not Reuse Space
  …

KEYWORDS: SHRINK LOB Filter on list: SHRINK

Bug 5565887  SHRINK SPACE IS REQUIRED TWICE FOR RELEASING SPACE.
Bug 4867081  SHRINK SPACE HAS TO BE EXECUTED TWICE FOR LOB COLUMNS (INTERNAL)

Note 820043.1 Ext/Mod Why is no space released after an ALTER TABLE … SHRINK?

       CASE STUDY #3 – Demonstrate a shrink of a LOB segment
   …
ORA-1555s involving LOBS

KEYWORDS: ORA-1555 LOB Filter on list: 1555

Note 467872.1 ORA-1555 – YET STILL ANOTHER APPROACH (INTERNAL)

What to do if an ORA-1555 is occurring with a LOB segment
———————————————————————————————

Trouble Converting to/from LOBS

KEYWORDS: CONVERT LOB Filter on list: LOB

Note 282464.1 How to convert LONG to a CLOB
Note 251417.1 To convert LONG column to CLOB using package DBMS_REDEFINITION
Note 235142.1  How to Convert the Data from BLOB to CLOB using PL/SQL
Note 107570.1  How to Convert from VARCHAR2 to CLOB using PL/SQL
Note 1071540.6 Converting a Long datatype to Clob

KEYWORDS: CONVERT BLOB RAW Filter list on: BLOB

Note 203882.1 How To Convert A BLOB Into A Long Raw
How do you move a lob from one tablespace to another?

KEYWORDS: MOVE LOB Filter on list: MOVE

Note 310002.1  How to Move a Subpartition that contains a Type Object with LOBs
Note 130814.1  How to move LOB Data to Another Tablespace
Note 761388.1  How To Move Or Rebuild A Lob Partition
<453186.1>  How to move LOB Data to Another Tablespace when the Table also contains a LONG column

CAUSE DETERMINATION (CD)

If a bug was identified in the RESEARCH (R) section and justification can be given then document it as a possible cause.

If a document was identified that explains the behavior as a setup / configuration problem, wrong usage of a feature, expected behavior etc. then document this in this section

EXAMPLES

Temporary LOBS not being released / reused

Expected Behavior

Prior to 10.2.0.4 Temporary LOBs are not released for reuse until the session holding them disconnects
LOB performance

Customer is encountering Bug 6376915 – ENQ: HW – CONTENTION WITH LOB SEGMENTS
LOB segment growth / shrink issues

Customer is encountering

Bug 5565887 SHRINK SPACE IS REQUIRED TWICE FOR RELEASING SPACE.
Bug 4867081 SHRINK SPACE HAS TO BE EXECUTED TWICE FOR LOB COLUMNS (INTERNAL)
ORA-1555s involving LOBS

Expected Behavior

Insufficient UNDO / Consistent Read settings for the LOB segment

Note 467872.1 ORA-1555 – YET STILL ANOTHER APPROACH (INTERNAL)

If PCTVERSION is used
————————————

This method of LOB undo management is akin to use of rollback segments and as such … there is little control

The only parameter we have control over with this method is PCTVERSION itself … so …

If RETENTION is used
———————————

If RETENTION is used the same rules apply for the LOB segments as do the UNDO segments

1) QUERY DURATION > UNDO_RETENTION

There are no guarantees that read consistency can be maintained after the old lob segment for the committed row has expired
(exceeded UNDO_RETENTION)

2) QUERY DURATION <= UNDO_RETENTION

This case is most often due to the LOB segments tablespace becoming full sometime during the time when the query was running

How do we tell if the LOB segment tablespace has become full during the query?

Unfortunately there is no easy way to do this … as V$UNDOSTAT does not contain info about the LOB segments
There may be a way to use Enterprise Manager and examine the high watermark (HWM) of the tablespace in question

There is an enhancement request for this info to be included somewhere

Bug 3200789 Abstract: VISIBILITY OF LOB SEGMENT USAGE FOR UNDO
Important: If at all possible, document whether the problem is critical (ie. whether it can cause instance crashes, whether it is corruptive, etc.)
 or non-critical (eg. error only happens during shutdown, when an idle session is disconnected, etc.). Be very careful with this – and only make
 a statement if you are confident about it.

CAUSE JUSTIFICATION (CJ)

If a bug was identified as the CAUSE DETERMININATION (CD) then the a bug, use the supporting evidence needs to be cited in this section

   Such evidence would be in the form of proof that the issue fits the "rediscovery information" (if present) or the conditions of the bug

If a document was found that explains the condition … cite the justification from the reference …

    Include document ID’s …

              for notes … the note number …
              for Online documentation … give the full title of the book (includng serial #) the chapter … and sub chapter
              for internal references … give the URL (if possible) and if not already in the RESEARCH section .. supply the justification text

An additional part of this section is update to the BUG / NOTE section of the Service Request


EXAMPLES

Temporary LOBS not being released / reused

Bug 5723140
Fixed In Ver: 10.2.0.4 11.1.0.6
Abstract: TEMP SPACE NOT RELEASED AFTER COMMIT ON TABLE WITH XMLTYPE AND XSD

Details:
While using temporary LOBs the temp segment used for this purpose remains active until the session exits. When there is a request to cleanup those temp lobs we do free space
from temp tablespace and the same session can reuse it, but the temp segment is not released which means it is not available for use to other sessions.

The actual cleanup happens only on session exit. This is intentional.

This fix introduces a new event 60025 such that when there are no active temp lobs in the session (ie: both cache temp lob and no-cache temp lobs used are zero) then the temp
segment itself will also be freed releasing the space for other sessions to use.

Note that this change is disabled by default.
LOB performance

Customer is

* using ASSM and is doing high Update / Inserts using LOBS
* encountering enq-HWM Contention during high load periods

as such this matches the condition described in

Note 837883.1 LOB HWM CONTENTION : Using the AWR Reports To Confirm and Problem; Confirm and Verify the Fix

A truss of the ‘hanging’ process did not show a loop in ktsplbfmb so Bug 5396550 can be eliminated
LOB segment growth / shrink issues

Per

Bug 5565887 SHRINK SPACE IS REQUIRED TWICE FOR RELEASING SPACE.
Bug 4867081 SHRINK SPACE HAS TO BE EXECUTED TWICE FOR LOB COLUMNS (INTERNAL)

Discovered that if a second shrink is done .. the lob segment will shrink
ORA-1555s involving LOBS

LOB segment was set with PCTVERSION of 0 … so no consistent read space was provided … thus the ORA-1555 is expected

POTENTIAL SOLUTIONS (PS)

When the root cause has been identified, list the possible options, eg.

  • workarounds (events, parameters, change to SQL statements, etc.)
  • upgrade (bug fixed in 11.1 etc.)
  • patchsets (bug fixed in 10.2.0.4, 11.1.0.7, etc.)
  • one-off patches (for non-current versions only list this option if a patch is already available or customer has an approved exception for patches for their version)


EXAMPLES

Temporary LOBS not being released / reused

1) Install / Upgrade to 10.2.0.4

2) Implement event 60025

       alter session set events ‘60025 trace name context forever’;
LOB performance

1) Download and apply the patch for Bug 6376915 on 10.2.0.3 : the fix is already included in 11.1.0.7 and 10.2.0.4

2) Set EVENT 44951 up to 1024 (which changes Chunks allocated from 1 on ASSM to 1024)

NOTE: The fix for this problem introduces a *mandatory event* which must be set in order for the ASSM architectural change leading to more efficient allocation of LOB chunks during high load and transaction contention when Extent allocation is needed. The event is *not* set even after you apply the patch.

ALTER SYSTEM SET EVENT=’44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024′ scope=spfile;

– or set directly in the pfile/init.ora –

EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024"

3) Shutdown and restart the database
LOB segment growth / shrink issues

1) Until the fix for the bug can be applied … issue a 2nd ALTER TABLE … SHRINK command

2) When the patch for Bug 5565887 becomes available apply the patch / patcheset
ORA-1555s involving LOBS

Note 467872.1 ORA-1555 – YET STILL ANOTHER APPROACH (INTERNAL)

SOLUTION:

Increase PCTVERSION …

ALTER TABLE **table name** MODIFY LOB (**lob column name**) (PCTVERSION 100)

if the parameter is at 100% then the only solution will be to move to use of RETENTION

ALTER TABLE **table name** MODIFY LOB (**lob column name**) (RETENTION)

POTENTIAL SOLUTION JUSTIFICATION (PSJ)

Explain each of the potential solutions identified – for workarounds, document the possible impact etc.

Temporary LOBS not being released / reused

An upgrade / patchset application to 10.2.0.4 (or higher) will allow event 60025 to be used

Use of event 60025 will free temporary lobs when there are no active temp lobs in the session (ie: both cache temp lob and no-cache temp lobs used are zero) thus allowing for other sessions to use the space.
LOB performance

Solution taken from Note 837883.1 LOB HWM CONTENTION : Using the AWR Reports To Confirm and Problem; Confirm and Verify the Fix

1) Application of the patch enables the event to be set

2) Setting the event … changes the number of chunks that can be accessed in a single request from a single session

      Setting this event .. should have no negative effects on systems encountering this condition

3) Restarting the database is required in order for the event to take effect
LOB segment growth / shrink issues

Bug 5565887 does not currently have a fix … until it does the workaround is to issue a second ALTER TABLE SHRINK
ORA-1555s involving LOBS

An Increase PCTVERSION … or move to RETENTION will provide the needed Consistent Read (CR) copies of the LOB

Please note … this will cause higher consumption of space in the UNDO tablespace … as space will be needed for the consistent read (previous) copies of the LOB

QUESTION (Q)

State the question in clear precise language using accepted Oracle terminology

If the initial statement in the Service Request (SR) is not clear contact the filer and determine a clear question

The Abstract of the SR should match (or a shortened version thereof) this section …

EXAMPLES:

Converting to/from LOBS

How do you convert from LONG or LONG RAW to CLOB or BLOB and then reverse the process?
Trouble moving lobs

How do you move a lob from one tablespace to another?

RESEARCH (R)

See RESEARCH (R)

ANSWER (A)

When possible provide the full answer in this section.

This is especially important if the answer to the question is being documented for the first time as creation of new note will be required (where appropriate)

Converting to/from LOBS

There are several methods that can be used

The following notes discuss several of these methods

Note 282464.1 How to convert LONG to a CLOB
Note 251417.1 To convert LONG column to CLOB using package DBMS_REDEFINITION
Note 235142.1 How to Convert the Data from BLOB to CLOB using PL/SQL
Note 107570.1 How to Convert from VARCHAR2 to CLOB using PL/SQL
Note 1071540.6 Converting a Long datatype to Clob
Trouble moving lobs

There are several methods that can be used

The following note lists only one method

Note 130814.1 How to move LOB Data to Another Tablespace

Other methods that may be considered

     * EXPORT / Drop the table / IMPORT into the new tablespace
     * Use DBMS_REDEFINITION to do an online rebuild / recreate of the table / lob into a new tablespace

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.