Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Michael.

Asked: April 15, 2014 - 7:10 am UTC

Last updated: April 18, 2014 - 9:13 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Tom,

When quering the v$temporary_lobs view we can see a column named "abstract_lobs":

desc v$temporary_lobs

Name          Null Typ    
------------- ---- ------ 
SID                NUMBER 
CACHE_LOBS         NUMBER 
NOCACHE_LOBS       NUMBER 
ABSTRACT_LOBS      NUMBER

While the meaning of all the other columns is quite clear, i found no hint in the documentation (not in the view description nor in the SecureFiles and Large Objects Developer's Guide) what an "abstract lob" is.

But, by accident i found out how to "create" one:

set serveroutput on
declare
  l_my_clob clob;
  --
  procedure print_abstract_lobs as
    l_abstract_lobs number;
  begin
    select abstract_lobs into l_abstract_lobs
    from   v$temporary_lobs
    where sid = sys_context('userenv', 'sid');
  dbms_output.put_line('Number of abstract lobs: ' || l_abstract_lobs);
  end;
begin
  print_abstract_lobs;
  select xmlserialize(content xmltype('<test/>') as clob)
  into   l_my_clob
  from   dual;
  print_abstract_lobs;
end;
/


The output:

Number of abstract lobs: 0
Number of abstract lobs: 1


Do you have an idea?

and Tom said...

They are special case of a temporary lob, one created by us implicitly.

if you were to use the XMLTYPE and call "getClobVal()" you'd see it again:


ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
       429          0            0             0

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x clob
ops$tkyte%ORA11GR2> begin
  2  select xmltype( '<hello>World</hello>' ).getClobval()
  3    into :x
  4    from dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
       429          0            0             1




so, it is pretty much a large object created by us as a side effect of some abstract object (object type) creating them.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Are abstract lobs cached?

Michael, April 18, 2014 - 1:20 pm UTC

OK, so they are some special temporary lobs created implicitly. Now, regarding the performance when accessing them often for example with dbms_lob.read - are those abstract lobs cached or not?
Tom Kyte
April 18, 2014 - 9:13 pm UTC

they will be cache - did a small test accessing xmltype(...).getclobval 10's of thousands of times - no write IO at all, no reads from temp.

Thanks!

Michael, April 25, 2014 - 7:42 am UTC

Thanks for your answer (and test).
And sorry for bothering you with something that i could have (quite easliy) found out myself...

When do abstract lobs go away?

Kim Berg Hansen, March 31, 2023 - 8:19 am UTC

I'm currently investigating a 19.17 Standard Edition where memory usage keeps growing. I suspect temporary lobs that aren't freed properly.

PL/SQL web application using htp.p with ORDS as server. The PL/SQL extensively uses lobs, almost all the time with cache=true and default session duration (which I think they should use call duration instead.)

But I notice in v$temporary_lobs that these ORDS sessions typically have slowly increasing ABSTRACT_LOBS counts - up to maybe 120.

The code often creates a temp lob, then assigns to it a from an xmltype. I suspected this might make two lobs (one of them the abstract) and this might cause only one to be cleaned up.

I tried reproducing it:

set serveroutput on size unlimited;

declare
   procedure lobout (t varchar2 default 'none') is
      cl pls_integer;
      al pls_integer;
   begin
      select cache_lobs, abstract_lobs into cl, al from v$temporary_lobs where sid = sys_context('USERENV','SID');
      dbms_output.put_line(t||' : '||cl||' - '||al);
   end;
   procedure do (outc out clob) is
      c2 clob;
   begin
      lobout('in do');
      dbms_lob.createtemporary(c2,true);
      lobout('in do');
      select xmlserialize(content xmltype('<a>b2</a>') as clob) into c2 from dual;
      lobout('in do');
      outc := c2;
      lobout('in do');
   end;
begin
   lobout;
   declare
      c clob;
   begin
      lobout('outer');
      dbms_lob.createtemporary(c,true);
      lobout('outer');
      select xmltype('<a>b</a>').getclobval() into c from dual;
      lobout('outer');
      dbms_output.put_line(c);
      lobout('outer');
      do(c);
      lobout('outer');
   end;
   lobout;
end;
/


I get this output (before running, my session had 15 cached lobs, 0 abstract lobs):

none : 15 - 0
outer : 15 - 0
outer : 16 - 0
outer : 15 - 1
<a>b</a>
outer : 15 - 1
in do : 15 - 1
in do : 16 - 1
in do : 15 - 2
in do : 15 - 2
outer : 15 - 1
none : 15 - 1


I had expected the last line to show "15 - 0", so I thought I'd caught the culprit, but after finishing the block, my session still has 15 cached lobs and 0 abstract lobs, so the abstract lob is cleaned after all.

So when are abstract lobs cleaned out? In what circumstances can they keep hanging around, so a pooled ORDS web session slowly keeps growing number of abstract lobs?

Thanks for any ideas that I can try investigating. (For case specific help I'll go to support.)

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here