Andrew Fraser DBA

Cache LOBs for Better Performance

LOBs generally(*) perform better if cached – that is, stored in the database buffer cache.

However, that is not switched on by default.

To change a lob to be cached:

alter table mytable modify lob (mycolumn) (cache) ;

To set it back to being uncached:

alter table mytable modify lob (mycolumn) (nocache) ;

And to see what the current setting is:

select cache from dba_lobs where table_name = 'MYTABLE' ;

* The exception to this, when caching LOBs can go bad, is where there is so much LOB data, being accessed so often, that the rest of the database data is pushed out of the buffer cache. That is unusual in my experience, but as always, testing is worthwhile. Increasing the SGA/buffer cache to accommodate cached LOBs might turn out to be beneficial at the same time.

A 2004 Oracle White Paper, LOB Performance Guidelines (pdf), says:

Recommendation
Enable caching except for cases where caching LOBs would severely impact performance for other online users, by forcing these users to perform disk reads rather than cache hits.