Hi! I have some PL/SQL code running in a XE (v18) database but I have to move it to standard edition since I am running up against the 12GB limit. So I created a VM in the Oracle Public Cloud running standard edition v19. I then imported my data and code. I find though that the code runs about 3 times slower in the new database and I narrowed the problem down to calling json_value. The new database runs on a bigger, faster server and appears to run faster in all other aspects. For example, I did the below quick test and it runs in 26 seconds in the new database and 82 in the old, so almost 3 times faster in the new, the very opposite of what I see with the code calling json_value.
The json documents are stored in clobs and I query them once into a clob variable and then repeatedly call jason_value to extract the data I need.
Appreciate any tips on how to tune this!
Test I did to just check overall performance between the 2 databases (count from user_tab_columns is the same in both)
set serveroutput on
declare
lv_line_cnt number := 0;
lv_cnt number;
begin
dbms_output.put_line('starting... time is: '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
loop
lv_line_cnt := lv_line_cnt + 1;
select count(*)
into lv_cnt
from user_tab_columns;
exit when lv_line_cnt = 1000;
end loop;
dbms_output.put_line('end! time is: '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
end;