Forum Stats

  • 3,815,686 Users
  • 2,259,067 Discussions
  • 7,893,206 Comments

Discussions

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

Satish Kandi
Satish Kandi Member Posts: 9,627
edited Feb 10, 2017 6:56AM in SQL & PL/SQL

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

Tagged:

Best Answer

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,801 Red Diamond
    edited Feb 10, 2017 6:43AM Answer ✓

    Yes - a temporary LOB segment is created for that transient CLOB. And the segment will be owned by the owner of the DBMS_LOB.CreateTemporary() call.

    How about creating a wrapper proc for DBMS_LOB.WriteAppend() - use a static 32KB PL/SQL string variable as buffer cache, flushing it using a WriteAppend() call when full. If you have a lot of small strings being written, this should reduce the actual number of WriteAppend() calls at the expense of a 32KB memory chunk from the PGA.

Answers

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited Feb 10, 2017 3:39AM

    According to that bug : The schema that owns the table that holds the LOB column  should be the same as the database account (user) running the PLSQL call.

    >>who owns these in memory LOB object

    It is the schema that owns the table that contains the LOB column.

    It is possible to write an application where the schema that owns the table is different from the database account that makes the PLSQL calls.

    Hemant K Chitale

  • Satish Kandi
    Satish Kandi Member Posts: 9,627
    edited Feb 10, 2017 4:42AM

    Thanks Hemant for the reply.

    However, we are not referring to any Table.Column combination here. Its just a in memory CLOB variable created in the procedure, which is used for further processing - that too, in memory.

    Does the ownership rule still apply in this case?

    Regards,

    Satish

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,801 Red Diamond
    edited Feb 10, 2017 6:43AM Answer ✓

    Yes - a temporary LOB segment is created for that transient CLOB. And the segment will be owned by the owner of the DBMS_LOB.CreateTemporary() call.

    How about creating a wrapper proc for DBMS_LOB.WriteAppend() - use a static 32KB PL/SQL string variable as buffer cache, flushing it using a WriteAppend() call when full. If you have a lot of small strings being written, this should reduce the actual number of WriteAppend() calls at the expense of a 32KB memory chunk from the PGA.

  • Satish Kandi
    Satish Kandi Member Posts: 9,627
    edited Feb 10, 2017 6:56AM

    Thanks Billy for the update. This means we have to first of all upgrade our environment to supported Oracle version and then try to reproduce the problem.

    I will certainly try out the WriteAppend() method.

    Regards,

    Satish

This discussion has been closed.