Skip to Main Content
  • Questions

  • LOB Chunk size equal to the frequent update size

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: October 12, 2015 - 10:00 pm UTC

Last updated: October 14, 2015 - 12:40 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi,

I came across this document that provides recommendations for the Chuck size of a Basicfile. Here is the link:

http://www.oracle.com/technetwork/articles/lob-performance-guidelines-128437.pdf

It states the following:

"Recommendation:

For LOBs that are less than 32K, a chunk size that is 60% (or more) of the LOB size is a good starting point. For LOBs larger than 32K, choose a chunk size equal to the frequent update size. "

Will you please tell me the query to determine what that update size is?

Also, let's say that the frequent update size turns out to be 1 MB. If I change the chunk size from 32k to 1MB, where would I see the most performance gains? would it be in the inserts, deletes, updates, selects?

Thanks,

John



and Connor said...

The chunk size is capped anyway at far less than 1meg, eg

SQL> create table T1 (
  2  col1 int,
  3  col2 clob
  4  )
  5  lob ( col2 ) store as ( chunk 32k) ;

Table created.

SQL> create table T2 (
  2  col1 int,
  3  col2 clob
  4  )
  5  lob ( col2 ) store as ( chunk 40k) ;
lob ( col2 ) store as ( chunk 40k)
                              *
ERROR at line 5:
ORA-22851: invalid CHUNK LOB storage option value


Chunksize determines the unit of IO for the LOB. If you have large LOB's (ie, much greater than max chunksize) and when you update them, you rewrite the entire LOB, then you'd keep the chunksize large.

But you might have large lobs (eg XML) and only modify tiny portions of them. In that case, you might choose a chunksize smaller to reflect the size of the update.

But the size will be defined by your application code.

Hope this helps.

Rating

  (1 rating)

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

Comments

John Cantu, October 13, 2015 - 10:47 pm UTC

Hi Connor. Thanks for your response. It was very useful.

I do have a few follow-up questions regarding updating a Basicfile.

So in order to find what how much data is being updated, I have to speak with the application developers? That is too bad since I don't have access to the application developers because the application is a commercial software. I was hoping that I would be able to query that from within the database.

In our case, the LOB is storing XML data and it should only modify a tiny amount of LOB data, does Oracle provide different "update LOB" commands? I want to make know if it is possible for a developer to set it up to update all of the LOB with every tiny change or if Oracle has protections in it to perform the smart thing which is to only modify the tiny amount that changed in the LOB.

Here is an example of the types of LOBs were are dealing with:
Create table "trxt"
(ID varchar2(200), lobt blob
primary key(ID)


LOB (lobt) STORE AS BASICFILE (


TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION


NOCACHE LOGGING


STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645


PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1


BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

If I see this type of SQL being executed on the database for the LOB column, does it mean that they are reloading the LOB every time instead of just updating the changed pieces of the LOB?

"update table_name set LOB_COLUMN='xxxxxxxxxxxxxx' where ...."

If that is the case, can you point me to the Oracle documentation that I can pass to the vendor so that they can use efficient LOB update commands? I looked at Oracle's SecureFiles and Large Objects Developer's Guide, but it wasn't clear to me what package a developer uses to perform updates of only what has changed.



Connor McDonald
October 14, 2015 - 12:40 am UTC

"update table_name set LOB_COLUMN='xxxxxxxxxxxxxx' where ...."

Yes, thats basically a 'replace' operation.

Tell the vendor to check out DBMS_LOB if they use PLSQL for reading/writing portions of a LOB, and similar facilities exist in the other interfaces (eg, ODP.Net has an OracleCLob class, etc)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here