Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PL/SQL CLOB variable: Which user owns the LOB?

Satish KandiFeb 10 2017 — edited Feb 10 2017

Hello,

Our environment: 64 bit Oracle 11.2.0.3.0 Standard edition - June 2013 bundle, on Microsoft Windows 2008 R2 server.

Needless to mention, we have to upgrade

While debugging a performance issue which involves a procedure working around DBMS_LOB package, we found that the APPEND method takes a lot of time.

Sample piece of code:

>>

newCLOB CLOB := EMPTY_CLOB();

oldCLOB CLOB := EMPTY_CLOB();

BEGIN

...................

IF OperationFlag = 'I' THEN

DBMS_LOB.APPEND(newCLOB, '<some text>');

END IF;

IF OperationFlag = 'I' THEN

DBMS_LOB.APPEND(newCLOB, '<some other text>');

END IF;

.............

>>

The DBMS_LOB.APPEND takes anywhere between 500ms to 3 seconds, at random.

Search on Oracle support site indicated a bug in our version (Bug 12834930 : DBMS_LOB.APPEND CONSUMES HIGH CPU USAGE WHEN USER IS NOT THE LOB OWNER). As per support site, a patch including fix for this bug is provided for every other operating system than ours.

Before, we jump to conclusion that our environment is affected by this bug, we would like to know the answer to the question - who owns these in memory LOB object because based on our understanding, since the procedure is owned by the application schema user, the in memory LOB is also owned by the user.

If this is the case, are we looking at another performance bug in this release?

Thanks,

Satish

This post has been answered by Billy Verreynne on Feb 10 2017
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 10 2017
Added on Feb 10 2017
4 comments
173 views