In Oracle, each operation with a LOB chunk requires physical I/O. As a result, you may end up with a high number of I/O-related wait events in the system. That is why proper management of I/O-related parameters is extremely important. The slightest mistake could have significant repercussions across the whole system.
In this article, we will consider managing small data volumes; in part 2, we will consider buffer cache alternatives. In the final article in this series, we will consider logging modes.
Managing Small Data Volumes
It is reasonable to ask the following question: Why place data in the special storage structure if you only have a small amount of data in some rows? To handle such cases, Oracle allows you to store data in the row (instead of outside of the row) if you have less than 3964 bytes (Oracle Database 11g) or 3968 bytes (Oracle Database 12c). This causes all of the small pieces of data to be processed as if they were regular VARCHAR2 columns. When their size exceeds this limit, the data will be moved to LOB storage:
This example shows that a single extra byte in the string immediately caused Oracle to initiate the LOB storage mechanisms. You might consider disabling this feature by setting the DISABLE STORAGE IN ROW clause if your data length is fluctuating around 4KB and you do not want to constantly move it back and forth. But in almost all cases, using ENABLE STORAGE IN ROW is the best option, especially because data “in row” uses regular table storage mechanisms and requires no multiple segments or special I/O.
NOTE
In version 12c, Oracle introduced an extension of VARCHAR2 in SQL from 4KB to 32KB. Internally, it is nothing more than a CLOB restricted to a single chunk with enabled storage in row. This means that up to 3968 bytes of data in the column will behave like VARCHAR2. Above that, it will behave like a small CLOB.
In the next article, we will consider buffer cache alternatives.
Leave a Reply