Oracle: Compares performance of building JSON with CLOB+VARCHAR2 vs JSON API

json-performance.sql

-- This script measures json construction via direct clob vs PLSQL JSON API.
-- Result: direct CLOB is ca 2x slower than JSON API + final to_clob.
-- PLSQL JSON API: Available since Oracle 12c.
-- It is a DOM API, i.e. one can modify the JSON object tree in memory before serializing it to VARCHAR2 or CLOB.
-- https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/json-types.html#GUID-BDE10AAA-445B-47B5-8A39-D86C8EA99283
declare
  sText       VARCHAR2( 32767 ) := 'this is a text';
  c           CLOB;
  g_start     NUMBER;
  imax        CONSTANT PLS_INTEGER := 2000;
  iAttrCount  CONSTANT PLS_INTEGER := 100;
PROCEDURE JsonClobDirect
IS
BEGIN
  g_start := DBMS_UTILITY.get_time;
  FOR t IN 1..imax LOOP -- repeat for better mesurements
      dbms_lob.createTemporary( c, TRUE );
      dbms_lob.append( c, '{' );
      FOR i IN 1..iAttrCount LOOP
        IF i > 1 THEN
          dbms_lob.append( c, ',' );
        END IF;
        dbms_lob.append( c, '"text'||i||'":"'||sText||'"' );
        dbms_lob.append( c, ',"val'||i||'":'||i );
      END LOOP;
      dbms_lob.append( c, '}' );
      IF t=1 THEN
        dbms_output.put_line('json length: '||dbms_lob.getlength(c));
        --dbms_output.put_line(c);
      END IF;      
      dbms_lob.FreeTemporary( c );
  END LOOP;
  DBMS_OUTPUT.put_line('JSON construction via direct CLOB:  : ' || (DBMS_UTILITY.get_time - g_start) || ' hsecs');
END;  
--
PROCEDURE JsonApi
IS
  o JSON_OBJECT_T;
BEGIN
  g_start := DBMS_UTILITY.get_time;
  FOR t IN 1..imax LOOP -- repeat for better mesurements
      o := new JSON_OBJECT_T;
      FOR i IN 1..iAttrCount LOOP
        o.put('text'||i, sText );
        o.put('val'||i, i );
      END LOOP;
      -- still create a CLOB in the end as we want the same result
      c := o.to_clob;
      IF t=1 THEN
        dbms_output.put_line('json length: '||dbms_lob.getlength(c));
        --dbms_output.put_line(c);
      END IF;      
  END LOOP;
  DBMS_OUTPUT.put_line('JSON construction via JSON API:  : ' || (DBMS_UTILITY.get_time - g_start) || ' hsecs');
END;  
--
begin
  JsonClobDirect;
  JsonApi;
end;