Oracle PL/SQL and VARCHAR2 Memory Allocation

The biggest challenge of explaining efficient ways of using the VARCHAR2 data type is the fact that its table storage implementation is often confused with its PL/SQL implementation. VARCHAR2 is named for the VARiable length of CHARacters, which implies dynamic allocation of storage. This mechanism is usually efficient from the perspective of persistent tables because it saves a significant amount of space. But from a memory allocation perspective, it is a mixed blessing because reallocation of memory comes with a price.

For years, Oracle did something unexpected: In some cases, the PL/SQL engine decided to allocate the maximum length of memory to the variable, regardless of the amount of data being stored.

In Oracle Database 10g, the mechanism is simple. Up to and including VARCHAR2(1999), the memory is fully allocated, while above this length, it becomes dynamic. There is even explicit mention of this in the documentation: http://tinyurl.com/Varchar2Limit10g. However, from Oracle Database 11g onward, the documentation does not mention this topic, so some testing was required. To find out the precise details, a special measuring mechanism is needed (thanks to Tim Hall for this concept):

The procedure P_TESTVARCHAR2 generates an anonymous PL/SQL block that recursively calls the procedure P_DRILLDOWN 1000 times. This means that inside of the procedure P_DRILLDOWN, a local variable V_TX is initialized each time. The length of this variable is defined by the input parameter P_TESTVARCHAR2, but the length of data to be stored is 1 (single letter A). To measure the memory allocation, the anonymous block captures PGA statistics from V$MYSTAT. The following is the result of a test run in Oracle Database 11g R2:

Obviously, the same implementation difference detected in Oracle Database 10g exists in higher versions as well, in both Oracle Database 11g and 12c. However, the threshold has been moved from 1999 to 4000 characters. If you are using a lot of VARCHAR2 variables that are being initialized at the same time, the impact on overall PGA usage can be significant. In the previous test, for 1000 variables, if you change VARCHAR2(4000) to VARCHAR2(4001), the savings will be twentyfold.

From a practical standpoint, the authors’ suggestions can be summarized into the following set of rules:

•        Declaring all of your string variables VARCHAR2(4000) is a bad idea.
•        If you know (more or less) how much text you need to manipulate, you should declare the variable using that expected length plus a safety margin (if needed).
•        If you don’t have a good idea of the necessary text length and the variable in question will be used only in the context of PL/SQL, the easiest option is to declare it as VARCHAR2(32767). Don’t be afraid of the big number! This way, you can store as much information as you like without worrying about either memory or length restrictions.
•        If you cannot correctly guesstimate the text length (at least in the range of plus/minus a few hundred), but it has to be used in the context of SQL, the solution is a bit more complicated. Since SQL does not support VARCHAR2 above 4000 (unless you explicitly enable it in Oracle Database 12c), having variables that can potentially hold longer text may cause problems, although you can get better length precision by using the TABLE.COLUMN%TYPE-declaration mechanism.