I asked the question on Oracle-L to learn about the use cases where people prefer database JVM to PL/SQL. One reason that appeared was better performance when writing BLOBs to files. Interestingly, I was recently troubleshooting a 3rd party application that has been hitting java.lang.OutOfMemoryError while writing BLOBs to files. As it turned out, the error was a consequence of an undocumented hard-coded limit in the Oracle database software. This prompted me to evaluate the alternative approach based on the built-in PL/SQL package UTL_FILE. In particular, I measured the time needed to write a large BLOB (ca. 600 MB) to a file from both PL/SQL and the database JVM. The tests were done on a 19.5.0.0.191015 database running on Solaris x64 11.4 and using a 32k buffer.

I was surprised to find out that the internal functioning of UTL_FILE depends on the file system type. First, I tested on /tmp.

/tmp

PL/SQL was slower than JVM by 38% when the output destination was /tmp. Find below the caputured stacks that appeared in most samples:

JVM:

libc.so.1`__write+0xa
a.out`sjonfile_write+0x42
a.out`jonfos_write_bytes+0x18d
a.out`jonfos9_write_bytes+0x21
a.out`joevm_invoke_sysnative+0x20f
...
1165

PL/SQL:

libc.so.1`__write+0xa
libc.so.1`_write_file+0x1a
libc.so.1`_xflsbuf+0x83
libc.so.1`_fwrite_unlocked+0x2e8
libc.so.1`fwrite+0x59
a.out`slfiwrb+0x31
a.out`lfibwrb+0x38
a.out`lfiwr+0x98
a.out`pifipr+0x567
a.out`spefcmpa+0x423
a.out`spefmccallstd+0x10c
a.out`peftrusted+0x88
a.out`psdexsp+0x134
oracle`rpiswu2+0x22e
a.out`kxe_push_env_internal_pp_+0x171
a.out`kkx_push_env_for_ICD_for_new_session+0x71
a.out`psdextp+0x1a3
a.out`pefccal+0x39a
a.out`pefcal+0x100
a.out`pevm_FCAL+0xe4
1589

We can reach two conclusions by looking into the stacks above. One is that in both cases the time was primarily spent in the standard I/O library function __write. The other is that the difference in the number of sampled stacks approximately matches the difference in the execution time, percentagewise. In other words, we can deduce the reason for different performance by profiling the calls to __write. Consequently, knowing that the third argument to __write is the number of bytes to be written, we can count the number of calls per write size as follows:

pid$target:libc:__write:entry
{
  @[arg2]=count();
}

Unexpectedly, the PL/SQL execution gave rise to a larger number of smaller, i.e. 4k, writes, even though I specified 32k buffer size.

PL/SQL:

bytes          #calls
---------------------
4095            18870
4096           132090

JVM:

bytes           #calls
----------------------
32767            18870

Simply put, unlike JVM, PL/SQL ignored the specified 32k buffer size and reduced the write size to 4k, which was the reason for the observed performance degradation.

ZFS

After moving the destination directory to ZFS, the performance of PL/SQL became comparable to JVM. Unsurprisingly, PL/SQL performed 32k writes:

bytes           #calls
----------------------
32767            18870

Note that the top of the stack looks different when writing from PL/SQL to ZFS (compared to /tmp):

libc.so.1`__write+0xa
libc.so.1`_write_file+0x1a
libc.so.1`_xflsbuf+0x83
libc.so.1`_fflush_u+0x32
libc.so.1`fflush+0x50
a.out`slfifl+0x27
a.out`lfiflu+0x94
a.out`pififl+0x1fe
a.out`pifipr+0x653
a.out`spefcmpa+0x423
a.out`spefmccallstd+0x10c
a.out`peftrusted+0x88
a.out`psdexsp+0x134
oracle`rpiswu2+0x22e
a.out`kxe_push_env_internal_pp_+0x171
a.out`kkx_push_env_for_ICD_for_new_session+0x71
a.out`psdextp+0x1a3
a.out`pefccal+0x39a
a.out`pefcal+0x100
a.out`pevm_FCAL+0xe4
1070

In particular, the difference is in the caller. When the output file was written to ZFS, __write was called by the function fflush, which flushes a fully buffered stream to a file [1]. Obviously, the buffer size was took over from the user PL/SQL code.

In contrast, when writing to /tmp from UTL_FILE, __write was called by fwrite with the 4k I/O size, regardless of what the user specified. fwrite is the OS function that handles unformatted stream output, often used for handling binary data.

The decision whether to do a fully buffered write (fflush) or a direct stream write (fwrite) is made within the Oracle C function pifipr and, obviously, depends on the file system type. By the way, pifipr underpinns UTL_FILE.PUT_RAW_I [2].

In JVM, the function sjonfile_write calls __write directly with the buffer size specified in the user PL/SQL code. sjonfile_write seems to be the Oracle C function that supports the Java FileOutputStream object.

In conclusion, the advantage of JVM is that it doesn’t change the user-specified buffer size. On the downside, I/O isn’t instrumented – everything is counted as the CPU time instead [3].

In contrast, the IO done within UTL_FILE is instrumented with the wait event utl_file I/O

Summary

In summary, the specified UTL_FILE buffer size can be changed under the hood, which might decrease the performance. The buffer size depends on the file system, and it isn’t documented. On the upside, the I/O is instrumented in PL/SQL.

Appendix

The appendix contains the test code.

Create the test table:

create table t_lob (b blob) tablespace users ;

Load a BLOB from a file into the table above, see How to Load File Content to a BLOB Field and Unload BLOB Content to a File on the OS.

Create Tim Hall’s Java (ExportBlob) and PL/SQL (blob_to_file) procedures for writing a BLOB to a file.

Finally, the code snippets for calling the functions:

--PL/SQL
DECLARE
  l_blob  BLOB;
  l_start timestamp ;
  l_end timestamp ;
BEGIN
  -- Get LOB locator
  SELECT b
    INTO   l_blob
    FROM   t_lob
  ;
  select systimestamp into l_start from dual ;
  blob_to_file(p_blob     => l_blob,
               p_dir      => 'BLOB_DIR',
               p_filename => 'apex_5.0.tar');
  select systimestamp into l_end from dual ;
  dbms_output.put_line(to_char( l_end - l_start ));
END;
/

--Java
DECLARE
  l_blob  BLOB;
  l_start timestamp ;
  l_end timestamp ;
BEGIN
  -- Get LOB locator
  SELECT b
    INTO   l_blob
    FROM   t_lob
  ;
  select systimestamp into l_start from dual ;
  ExportBlob ('/tmp/apex_5.0.tar',l_blob);
  select systimestamp into l_end from dual ;
  dbms_output.put_line(to_char( l_end - l_start ));
END;
/

References

[1] W. Richard Stevens, Advanced Programing in the Unix Environment, 25th Printing February, 2002
[2] Frits Hoogland, Oracle C functions annotations
[3] Jonathan Lewis, Wait for Java December 18, 2019