PGA Memory Leak And Poor Performance Using PL/SQL JSON Functions (JSON_VALUE, JSON_EXISTS...) (Doc ID 2364455.1)

Last updated on APRIL 01, 2020

Applies to:

Goal

On a 12.2.0.1 database, why is the PL/SQL function (see below) using so much memory and taking much longer than the other 2 methods?

Bulk processing using PL/SQL JSON functions such as JSON_VALUE and JSON_EXISTS results in poor performance and PGA memory limit reached. Doing the same processing using JSON_OBJECT_T getter or SQL version of JSON_VALUE does not seem to have this problem. Issue is easily reproducible with static JSON strings and looping. See 3 examples of parsing and timings in below example.

DECLARE
  l_json_value VARCHAR2(100);
  l_json_string VARCHAR2(100) := '{"foo": "bar"}';
  l_json_object JSON_OBJECT_T := JSON_OBJECT_T(l_json_string);
BEGIN
  FOR i IN 1..100000 LOOP
  --l_json_value := l_json_object.get_String('foo'); -- ~.4 seconds
  --EXECUTE IMMEDIATE 'SELECT JSON_VALUE('''|| l_json_string || ''', ''$.foo'') FROM DUAL' INTO l_json_value; -- ~2.5 seconds (longer from SQL-PL/SQL context switching)
  l_json_value := JSON_VALUE(l_json_string, '$.foo'); -- Failed after ~370 seconds with ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
  END LOOP;
END;
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.