Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
PL/SQL CLOB variable: Which user owns the LOB?
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
Best 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
-
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
-
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
-
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.
-
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