Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Abd.

Asked: June 19, 2024 - 9:25 am UTC

Last updated: June 25, 2024 - 6:29 am UTC

Version: 19.18

Viewed 1000+ times

You Asked

Hi,

In order to improve reading performance of a LOB column in my table, I enable CACHE option for it.

But when I check in the buffer cache using gv$bh (I did some SELECT queries on it so it can be cached), I cannot find any entries for my cached LOB.

SELECT *
  FROM gv$bh
 WHERE lobid =
          (SELECT object_id
             FROM dba_lobs lob, dba_objects obj
            WHERE     lob.table_name = 'MY_TABLE'    -- it contains only one lob column, so the returned result is unique
                  AND lob.SEGMENT_NAME = obj.object_name
                  AND lob.owner = obj.owner);



Is this the way to go? or I m missing something?

Thanks.

and Connor said...

Here's a simple test you can try on your instance

SQL> create table t (pk int, c clob) lob ( c ) store as ( disable storage in row );

Table created.

SQL> insert into t
  2  select rownum, rpad(rownum,6000)
  3  from dual
  4  connect by level <= 1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select *
  2  from user_lobs
  3  where table_name = 'T'
  4  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : C
SEGMENT_NAME                  : SYS_LOB0000196870C00002$$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000196870C00002$$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : NO
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : NO
DEDUPLICATION                 : NO
IN_ROW                        : NO
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :
VALUE_BASED                   : NO
MAX_INLINE                    : 0

PL/SQL procedure successfully completed.

SQL> select object_name, data_object_id
  2  from user_objects
  3  where object_name in ('T','SYS_LOB0000196870C00002$$','SYS_IL0000196870C00002$$');

OBJECT_NAME                              DATA_OBJECT_ID
---------------------------------------- --------------
SYS_IL0000196870C00002$$                         196872
SYS_LOB0000196870C00002$$                        196871
T                                                196870

SQL> select objd, count(*)
  2  from v$bh
  3  where objd in (
  4  196872
  5  ,196871
  6  ,196870
  7  )
  8  group by objd;

      OBJD   COUNT(*)
---------- ----------
    196871        108
    196872          4
    196870         16

SQL> declare
  2    v varchar2(8000);
  3  begin
  4  for x in 1 .. 10
  5  loop
  6  for i in ( select * from t ) loop
  7    v := substr(i.c,2000);
  8  end loop;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2    v varchar2(8000);
  3  begin
  4  for x in 1 .. 10
  5  loop
  6  for i in ( select * from t ) loop
  7    v := substr(i.c,2000);
  8  end loop;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select objd, count(*)
  2  from v$bh
  3  where objd in (
  4  196872
  5  ,196871
  6  ,196870
  7  )
  8  group by objd;

      OBJD   COUNT(*)
---------- ----------
    196871        109
    196872          4
    196870         31

sql> alter table t  modify lob (c)(cache);

Table altered.

SQL> declare
  2    v varchar2(8000);
  3  begin
  4  for x in 1 .. 10
  5  loop
  6  for i in ( select * from t ) loop
  7    v := substr(i.c,2000);
  8  end loop;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select objd, count(*)
  2  from v$bh
  3  where objd in (
  4  196872
  5  ,196871
  6  ,196870
  7  )
  8  group by objd;

      OBJD   COUNT(*)
---------- ----------
    196871       2110
    196872          5
    196870         31

SQL>
SQL>


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

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here