CACHE_LOBS vs. NOCACHE_LOBS

[peter_strauss]

Hello

I have a test program that creates a lot of NOCACHE_LOBS (as seen in V$TEMPORARY_LOBS).

I was expecting CACHE_LOBS. How can I check the storage parameters for the CLOB involved? Are they simply the same as the table in which the CLOB field was contained?

To put the question in another way, I know that the cache option can be set with something like the following:

create table goods_tab

(item_id number primary key,

name_tx varchar2(256),

remarks_cl CLOB DEFAULT empty_clob(),

LOB(remarks_cl) store as remarks_seg(

tablespace USERS

enable storage in row

chunk 8192

cache)

AFTER this table has been created, how is possible to find out the value of the cache option for remarks_cl?

Kind regards,

Peter

Edited by: Peter Strauss on Aug 24, 2009 12:19 PM

[stefan_koehler]

Hello Peter,

here we go with your example

shell> sqlplus / as sysdba
SQL> SELECT COLUMN_NAME, SEGMENT_NAME, CACHE FROM ALL_LOBS WHERE TABLE_NAME = 'GOODS_TAB';

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1092.htm#i1581211

But this just works for permanent LOBs .. for temporary LOBs you need to know the option that was used in the PL/SQL procedure.

Regards

Stefan

[peter_strauss]

Hello Stefan,

Thank you. That told me that the cache option for the lob field was NO; however we are dealing with temporary lobs.

There is probably something in the java coding we are dealing with that is causing the objects to be cached, and a problem in the coding that is causing the problem described at http://sql-plsql-de.blogspot.com/2008/03/damit-es-keine-memory-leaks-gibt.html?lang=en

Kind regards,

Peter

[lbreddemann]

Hello Peter,

sorry but somehow I don't get your problem here.

What I get is:

- you wrote JAVA code to access Oracle and work with temporary LOBs in this code.

- you expect these LOBs to be created as CACHED temp. LOBs but they aren't (?)

- you hit somekind of memory leak issue for which the solution is already described in the link you provided (as well as it is in the documentation...).

So what exactly is the problem right now?

And just out of curiosity: what does your program do with those temporary lobs?

regards,

Lars

[peter_strauss]

Hi Lars,

>>- you wrote JAVA code to access Oracle and work with temporary LOBs in this code.

The coding is SAP standard (using 700 SP10/SP11). We suspect a problem in AuditLogManager.java

>>- you expect these LOBs to be created as CACHED temp. LOBs but they aren't (?)

Lobs seem to be created when accessing table XI_AF_MSG_AUDIT. This table has one CLOB column, TEXT_KEY, for which caching is disabled. So far we have managed to recreate the problem in test programs by using very large data sets, and in these tests NOCACHE_LOBS are created. In production we see CACHE_LOBS, and they are created with very small data sets. I guess this has something to do with the way AuditLogManager.java is coded (my java coding skills are poor, so I'm not sure what).

>>- you hit somekind of memory leak issue for which the solution is already described in the link you provided (as well as it is in the documentation...).

I suspect that the memory leak occurs because there is no explicit freeTemporary used in AuditLogManager.java. This is exactly as explained in the blog entry I posted a link to earlier.

>>So what exactly is the problem right now?

Our exact problem is:

• CACHE_LOBS are continually created as described above.

• Because the CACHE_LOBS are not released by AuditLogManager.java they continue to grow until the session becomes idle for 300 seconds (runCleanupThread). This will trigger ConnectionSetCleaner, which does use freeTemporary. The problem is that by this time the number of CACHE_LOBS has increased to upwards of 200,000. The server node hangs until all CACHE_LOBS are free, which can take several hours.

>>And just out of curiosity: what does your program do with those temporary lobs?

If you like I can post the content of AuditLogCleaner.java here, or email it to you.

Peter

[Former Member]

Did you think of doing an SQL trace? For a j2ee you can do it in your browser using the the url: http://<server>:500<instnr>/OpenSQLMonitors/index.html

Might give you more insight on what is going on.

Regards, Michael