{"id":3166,"date":"2020-01-04T15:36:34","date_gmt":"2020-01-04T15:36:34","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=3166"},"modified":"2022-02-17T15:27:57","modified_gmt":"2022-02-17T15:27:57","slug":"writing-blob-to-file-plsql-java","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/","title":{"rendered":"Writing a BLOB to a File – Comparison Between PL\/SQL and Java"},"content":{"rendered":"

I asked the question on Oracle-L<\/a> 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<\/a>. Interestingly, I was recently troubleshooting a 3rd party application that has been hitting java.lang.OutOfMemoryError<\/i> 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<\/a>. This prompted me to evaluate the alternative approach based on the built-in PL\/SQL package UTL_FILE<\/i><\/a>. 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.<\/p>\n

I was surprised to find out that the internal functioning of UTL_FILE<\/i><\/a> depends on the file system type. First, I tested on \/tmp<\/i>.<\/p>\n

\/tmp<\/i><\/h1>\n

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

JVM:<\/p>\n

libc.so.1`__write<\/span>+0xa\na.out`sjonfile_write+0x42\na.out`jonfos_write_bytes+0x18d\na.out`jonfos9_write_bytes+0x21\na.out`joevm_invoke_sysnative+0x20f\n...\n1165<\/code><\/pre>\n
PL\/SQL:<\/p>\n
libc.so.1`__write<\/span>+0xa\nlibc.so.1`_write_file+0x1a\nlibc.so.1`_xflsbuf+0x83\nlibc.so.1`_fwrite_unlocked+0x2e8\nlibc.so.1`fwrite<\/span>+0x59\na.out`slfiwrb+0x31\na.out`lfibwrb+0x38\na.out`lfiwr+0x98\na.out`pifipr+0x567\na.out`spefcmpa+0x423\na.out`spefmccallstd+0x10c\na.out`peftrusted+0x88\na.out`psdexsp+0x134\noracle`rpiswu2+0x22e\na.out`kxe_push_env_internal_pp_+0x171\na.out`kkx_push_env_for_ICD_for_new_session+0x71\na.out`psdextp+0x1a3\na.out`pefccal+0x39a\na.out`pefcal+0x100\na.out`pevm_FCAL+0xe4\n1589<\/code><\/pre>\n
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<\/span><\/i><\/a>. 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<\/i><\/a>. Consequently, knowing that the third argument to __write<\/i><\/a> is the number of bytes to be written, we can count the number of calls per write size as follows:<\/p>\n
pid$target:libc:__write:entry\n{\n  @[arg2]=count();\n}<\/code><\/pre>\n
Unexpectedly, the PL\/SQL execution gave rise to a larger number of smaller, i.e. 4k<\/span>, writes, even though I specified 32k buffer size.<\/p>\n
PL\/SQL:<\/p>\n
bytes          #calls\n---------------------\n4095            18870\n4096           132090<\/span><\/code><\/pre>\n
JVM:<\/p>\n
bytes           #calls\n----------------------\n32767            18870<\/code><\/pre>\n
Simply put, unlike JVM, PL\/SQL ignored the specified 32k buffer size and reduced the write size to 4k<\/span>, which was the reason for the observed performance degradation.<\/p>\n
ZFS<\/h1>\n
After moving the destination directory to ZFS, the performance of PL\/SQL became comparable to JVM. Unsurprisingly, PL\/SQL performed 32k writes:<\/p>\n
bytes           #calls\n----------------------\n32767            18870<\/code><\/pre>\n
Note that the top of the stack looks different when writing from PL\/SQL to ZFS (compared to \/tmp<\/i>):<\/p>\n
libc.so.1`__write+0xa\nlibc.so.1`_write_file+0x1a\nlibc.so.1`_xflsbuf+0x83\nlibc.so.1`_fflush_u+0x32\nlibc.so.1`fflush<\/span>+0x50\na.out`slfifl+0x27\na.out`lfiflu+0x94\na.out`pififl+0x1fe\na.out`pifipr+0x653\na.out`spefcmpa+0x423\na.out`spefmccallstd+0x10c\na.out`peftrusted+0x88\na.out`psdexsp+0x134\noracle`rpiswu2+0x22e\na.out`kxe_push_env_internal_pp_+0x171\na.out`kkx_push_env_for_ICD_for_new_session+0x71\na.out`psdextp+0x1a3\na.out`pefccal+0x39a\na.out`pefcal+0x100\na.out`pevm_FCAL+0xe4\n1070<\/code><\/pre>\n
In particular, the difference is in the caller. When the output file was written to ZFS, __write<\/i><\/a> was called by the function fflush<\/span><\/i><\/a>, which flushes a fully buffered stream to a file [1]<\/a>. Obviously, the buffer size was took over from the user PL\/SQL code.<\/p>\n
In contrast, when writing to \/tmp<\/i> from UTL_FILE<\/i><\/a>, __write<\/i><\/a> was called by fwrite<\/i><\/a> with the 4k I\/O size, regardless of what the user specified. fwrite<\/i><\/a> is the OS function that handles unformatted stream output, often used for handling binary data.<\/p>\n
The decision whether to do a fully buffered write (fflush<\/i><\/a>) or a direct stream write (fwrite<\/i><\/a>) is made within the Oracle C function pifipr<\/i> and, obviously, depends on the file system type. By the way, pifipr<\/i> underpinns UTL_FILE.PUT_RAW_I<\/i> [2]<\/a>.<\/p>\n
In JVM, the function sjonfile_write<\/i> calls __write<\/i><\/a> directly with the buffer size specified in the user PL\/SQL code. sjonfile_write<\/i> seems to be the Oracle C function that supports the Java FileOutputStream<\/i> object.<\/p>\n
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]<\/a>.<\/p>\n
In contrast, the IO done within UTL_FILE<\/i><\/a> is instrumented with the wait event utl_file I\/O<\/i><\/a><\/p>\n
Summary<\/h1>\n
In summary, the specified UTL_FILE<\/i><\/a> 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.<\/p>\n
Appendix<\/h1>\n
The appendix contains the test code.<\/p>\n
Create the test table:<\/p>\n
create table t_lob (b blob) tablespace users ;<\/code><\/pre>\n
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<\/a>.<\/p>\n
Create Tim Hall’s Java (ExportBlob<\/i><\/a>) and PL\/SQL (blob_to_file<\/i><\/a>) procedures for writing a BLOB to a file.<\/p>\n
Finally, the code snippets for calling the functions:<\/p>\n
--PL\/SQL\nDECLARE\n  l_blob  BLOB;\n  l_start timestamp ;\n  l_end timestamp ;\nBEGIN\n  -- Get LOB locator\n  SELECT b\n    INTO   l_blob\n    FROM   t_lob\n  ;\n  select systimestamp into l_start from dual ;\n  blob_to_file(p_blob     => l_blob,\n               p_dir      => 'BLOB_DIR',\n               p_filename => 'apex_5.0.tar');\n  select systimestamp into l_end from dual ;\n  dbms_output.put_line(to_char( l_end - l_start ));\nEND;\n\/\n\n--Java\nDECLARE\n  l_blob  BLOB;\n  l_start timestamp ;\n  l_end timestamp ;\nBEGIN\n  -- Get LOB locator\n  SELECT b\n    INTO   l_blob\n    FROM   t_lob\n  ;\n  select systimestamp into l_start from dual ;\n  ExportBlob ('\/tmp\/apex_5.0.tar',l_blob);\n  select systimestamp into l_end from dual ;\n  dbms_output.put_line(to_char( l_end - l_start ));\nEND;\n\/<\/code><\/pre>\n
References<\/h1>\n
[1]<\/a> W. Richard Stevens, Advanced Programing in the Unix Environment, 25th Printing<\/i> February, 2002
\n[2]<\/a> Frits Hoogland, Oracle C functions annotations<\/i>
\n[3]<\/a> Jonathan Lewis, Wait for Java<\/i> December 18, 2019<\/p>\n","protected":false},"excerpt":{"rendered":"
Undocumented UTL_FILE internals – possible performance degradation with BLOBs Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[31,5,52],"tags":[],"class_list":["post-3166","post","type-post","status-publish","format-standard","hentry","category-java","category-oracle","category-pl-sql"],"yoast_head":"\nWriting a BLOB to a File - Comparison Between PL\/SQL and Java - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Undocumented internals - possible performance degradation with BLOBs\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Writing a BLOB to a File - Comparison Between PL\/SQL and Java - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Undocumented internals - possible performance degradation with BLOBs\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-04T15:36:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-17T15:27:57+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Writing a BLOB to a File – Comparison Between PL\\\/SQL and Java\",\"datePublished\":\"2020-01-04T15:36:34+00:00\",\"dateModified\":\"2022-02-17T15:27:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/\"},\"wordCount\":740,\"commentCount\":0,\"articleSection\":[\"Java\",\"Oracle\",\"PL\\\/SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/\",\"name\":\"Writing a BLOB to a File - Comparison Between PL\\\/SQL and Java - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-01-04T15:36:34+00:00\",\"dateModified\":\"2022-02-17T15:27:57+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Undocumented internals - possible performance degradation with BLOBs\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/writing-blob-to-file-plsql-java\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Writing a BLOB to a File – Comparison Between PL\\\/SQL and Java\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Writing a BLOB to a File - Comparison Between PL\/SQL and Java - All-round Database Topics","description":"Undocumented internals - possible performance degradation with BLOBs","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/","og_locale":"en_US","og_type":"article","og_title":"Writing a BLOB to a File - Comparison Between PL\/SQL and Java - All-round Database Topics","og_description":"Undocumented internals - possible performance degradation with BLOBs","og_url":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/","og_site_name":"All-round Database Topics","article_published_time":"2020-01-04T15:36:34+00:00","article_modified_time":"2022-02-17T15:27:57+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Writing a BLOB to a File – Comparison Between PL\/SQL and Java","datePublished":"2020-01-04T15:36:34+00:00","dateModified":"2022-02-17T15:27:57+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/"},"wordCount":740,"commentCount":0,"articleSection":["Java","Oracle","PL\/SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/","url":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/","name":"Writing a BLOB to a File - Comparison Between PL\/SQL and Java - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2020-01-04T15:36:34+00:00","dateModified":"2022-02-17T15:27:57+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Undocumented internals - possible performance degradation with BLOBs","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/writing-blob-to-file-plsql-java\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Writing a BLOB to a File – Comparison Between PL\/SQL and Java"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3166","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=3166"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3166\/revisions"}],"predecessor-version":[{"id":3195,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3166\/revisions\/3195"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=3166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=3166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=3166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}