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
…
