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.
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>