My Oracle Support Banner

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:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


On a 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.

  l_json_value VARCHAR2(100);
  l_json_string VARCHAR2(100) := '{"foo": "bar"}';
  l_json_object JSON_OBJECT_T := JSON_OBJECT_T(l_json_string);
  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


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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