Oracle Dynamic SQL Procedure with Aggregate Function takes long time to call via ORDS, but runs quickly in SQL Developer

Question

I have been working to improve my procedures, so I started changing them from using || string concatenation to use bind variables. When I added the bind variables to the following procedure, I found that I needed to add an aggregate function to the where clause in order to search where a number is in a comma separated list of numbers.

After adding the aggregate function string_to_table_num and string_to_table_varchar2 and updating other parts of the procedure to use bind variables, I tried making a request to my ORDS endpoint. The request took over 10 minutes to complete. I assume this is due to a combination of the aggregate functions and APEX because the original query runs in less than 500ms. I have tried several different functions in order to improve the speed, but I have not had any luck.

I will also take suggestions on how to improve the overall query if you have them. I am relatively new to Oracle.

ORDS Endpoint

BEGIN
  get_categoryprods2(:commasepproductids, :commasepproductskus, :pcategoryid, :sortby);
END;

Parameters

commasepproductids IN STRING from URI
commasepproductskus IN STRING from URI
pcategoryid IN STRING from URI
sortby IN STRING from URI
ptopcount in INT

Original Query

create or replace PROCEDURE get_categoryprods (
    commasepproductids     IN   VARCHAR2,
    commasepproductskus     in  varchar2,
    pcategoryid   IN   NUMBER,
    sortby        IN   VARCHAR2
) AS

    l_cursor               SYS_REFCURSOR;
    v_stmt_str             STRING(5000);
    s_counter              NUMBER;
    v_productid            productnew.productid%TYPE;
    v_manufacturerid       productmanufacturer.manufacturerid%TYPE;
    v_sename               productnew.sename%TYPE;
    v_name                 productnew.name%TYPE;
    v_summary              varchar(3999);
    v_freeground           productnew.freeground%TYPE;
    v_quantitydiscountid   productnew.quantitydiscountid%TYPE;
    v_sku                  productnew.sku%TYPE;
    v_price                productnew.price%TYPE;
    v_msrp                 productnew.msrp%TYPE;
    v_cost                 productnew.cost%TYPE;
    v_saleprice            productnew.saleprice%TYPE;
    v_weight               productnew.weight%TYPE;
    v_percase              productnew.percase%TYPE;
    v_relatedproducts      varchar(3999);
    v_hidepriceuntilcart   productnew.hidepriceuntilcart%TYPE;
    v_discontinued         productnew.discontinued%TYPE;
    v_discounttype         productnew.discounttype%TYPE;
    v_unitofmeasure        productnew.unitofmeasure%TYPE;
    v_replacement          productnew.replacement%TYPE;
    v_displayorder         productcategory.displayorder%TYPE;
    v_friendlyurl          urlmapper.friendlyurl%TYPE;
    v_sortby               varchar2(300);
    
BEGIN

    IF sortby IS NULL OR sortby = 'null' OR sortby = '' THEN
        v_sortby := '"p".Discontinued, "pc".DisplayOrder ';
    ELSIF sortby = 'PriceAscending' THEN
        v_sortby := '"p".discontinued, "p".price ';
    ELSIF sortby = 'PriceDescending' THEN
        v_sortby := '"p".discontinued, "p".price DESC ';
    ELSIF sortby = 'Name' THEN
        v_sortby := '"p".discontinued, "p".name ';
    ELSE
        v_sortby := '"p".discontinued, "pc".displayorder ';
    END IF;
        v_stmt_str := 'SELECT
                            "p".productid,
                            "pm".manufacturerid,
                            "p".sename,
                            "p".name,
                            to_char(substr("p".summary, 0, 3999)),
                            TO_NUMBER("p".freeground),
                            "p".quantitydiscountid,
                            "p".sku,
                            "p".price,
                            "p".msrp,
                            "p".cost,
                            "p".saleprice,
                            "p".weight,
                            "p".percase,
                            to_char(substr("p".relatedproducts, 0, 3999)),
                            "p".hidepriceuntilcart,
                            "p".discontinued,
                            "p".discounttype,
                            "p".unitofmeasure,
                            "p".replacement,
                            "pc".displayorder,
                            "um".friendlyurl
                        FROM
                            productnew            "p"
                            LEFT OUTER JOIN productcategory       "pc" ON "p".productid = "pc".productid
                            LEFT OUTER JOIN productmanufacturer   "pm" ON "p".productid = "pm".productid
                            LEFT OUTER JOIN urlmapper             "um" ON "p".productid = "um".productid
                        WHERE
                            ("pc".categoryid = ' || pcategoryid || ')
                        AND "p".productid in ('||commasepproductids||')
                                --and ("p".productid in ('||commasepproductids||') -- removed because I broke the actual original proc
                        AND ( "p".deleted = 0 )
                            AND "p".published = 1 ORDER BY '||v_sortby;
  
    s_counter := 0;
    apex_json.open_array;
    OPEN l_cursor FOR v_stmt_str;                   

        FETCH l_cursor into
            v_productid,
            v_manufacturerid,
            v_sename,
            v_name,
            v_summary,
            v_freeground,
            v_quantitydiscountid,
            v_sku,
            v_price,
            v_msrp,
            v_cost,
            v_saleprice,
            v_weight,
            v_percase,
            v_relatedproducts,
            v_hidepriceuntilcart,
            v_discontinued,
            v_discounttype,
            v_unitofmeasure,
            v_replacement,
            v_displayorder,
            v_friendlyurl;
      
            loop
             EXIT WHEN l_cursor%notfound;
                s_counter := 1;
                apex_json.open_object;
                apex_json.write('ProductID', v_productid);
                apex_json.write('ManufacturerID', v_manufacturerid);
                apex_json.write('SEName', v_sename);
                apex_json.write('Name', v_name);
                apex_json.write('Summary', v_summary);
                apex_json.write('FreeGround', v_freeground);
                apex_json.write('QuantityDiscountID', v_quantitydiscountid);
                apex_json.write('SKU', v_sku);
                apex_json.write('Price', v_price);
                apex_json.write('MSRP', v_msrp);
                apex_json.write('Cost', v_cost);
                apex_json.write('SalePrice', v_saleprice);
                apex_json.write('Weight', v_weight);
                apex_json.write('PerCase', v_percase);
                apex_json.write('RelatedProducts', v_relatedproducts);
                apex_json.write('HidePriceUntilCart', v_hidepriceuntilcart);
                apex_json.write('Discontinued', v_discontinued);
                apex_json.write('QuantityDiscountType', v_discounttype);
                apex_json.write('UnitOfMeasure', v_unitofmeasure);
                apex_json.write('Replacement', v_replacement);
                apex_json.write('DisplayOrder', v_displayorder);
                apex_json.write('FriendlyUrl', v_friendlyurl);
                apex_json.close_object;
        end loop;

    IF s_counter = 0 THEN
        apex_json.open_object;
        apex_json.write('ProductID', 0);
        apex_json.write('SEName', 'NOT FOUND');
        apex_json.write('Name', 'NOT FOUND');
        apex_json.write('Summary', 'NOT FOUND');
        apex_json.write('FreeGround', 'NOT FOUND');
        apex_json.write('QuantityDiscountID', 0);
        apex_json.write('SKU', 'NOT FOUND');
        apex_json.write('Price', 0);
        apex_json.write('MSRP', 0);
        apex_json.write('Cost', 0);
        apex_json.write('SalePrice', 0);
        apex_json.write('Weight', 0);
        apex_json.write('PerCase', 'NOT FOUND');
        apex_json.write('RelatedProducts', 'NOT FOUND');
        apex_json.write('HidePriceUntilCart', 'NOT FOUND');
        apex_json.write('Discontinued', 'NOT FOUND');
        apex_json.write('QuantityDiscountType', 'NOT FOUND');
        apex_json.write('UnitOfMeasure', 'NOT FOUND');
        apex_json.write('Replacement', 'NOT FOUND');
        apex_json.write('FriendlyUrl', 'NOT FOUND');
        apex_json.close_object;
    END IF;

    apex_json.close_all;
END get_categoryprods;

**New Query 1 **

create or replace PROCEDURE GET_CATEGORYPRODS2 
(
  COMMASEPPRODUCTIDS IN VARCHAR2 
, COMMASEPPRODUCTSKUS IN VARCHAR2 
, PCATEGORYID IN NUMBER 
, SORTBY IN VARCHAR2 
, PTOPCOUNT IN VARCHAR2 
) AS

    l_cursor               SYS_REFCURSOR;
    v_stmt_str             STRING(5000);
    s_counter              NUMBER;
    v_productid            productnew.productid%TYPE;
    v_manufacturerid       productmanufacturer.manufacturerid%TYPE;
    v_sename               productnew.sename%TYPE;
    v_name                 productnew.name%TYPE;
    v_summary              varchar(3999);
    v_freeground           productnew.freeground%TYPE;
    v_quantitydiscountid   productnew.quantitydiscountid%TYPE;
    v_sku                  productnew.sku%TYPE;
    v_price                productnew.price%TYPE;
    v_msrp                 productnew.msrp%TYPE;
    v_cost                 productnew.cost%TYPE;
    v_saleprice            productnew.saleprice%TYPE;
    v_weight               productnew.weight%TYPE;
    v_percase              productnew.percase%TYPE;
    v_relatedproducts      varchar(3999);
    v_hidepriceuntilcart   productnew.hidepriceuntilcart%TYPE;
    v_discontinued         productnew.discontinued%TYPE;
    v_discounttype         productnew.discounttype%TYPE;
    v_unitofmeasure        productnew.unitofmeasure%TYPE;
    v_replacement          productnew.replacement%TYPE;
    v_displayorder         productcategory.displayorder%TYPE;
    v_friendlyurl          urlmapper.friendlyurl%TYPE;
    v_sortby               varchar2(300);
    type t_categoryprods is table of categoryprod_typ;
    l_catprodrow            categoryprod_typ;
    l_catprods             t_categoryprods;
    
BEGIN
    IF sortby IS NULL OR sortby = 'null' OR sortby = '' THEN
        v_sortby := '"p".Discontinued, "pc".DisplayOrder ';
    ELSIF sortby = 'PriceAscending' THEN
        v_sortby := '"p".discontinued, "p".price ';
    ELSIF sortby = 'PriceDescending' THEN
        v_sortby := '"p".discontinued, "p".price DESC ';
    ELSIF sortby = 'Name' THEN
        v_sortby := '"p".discontinued, "p".name ';
    ELSE
        v_sortby := '"p".discontinued, "pc".displayorder ';
    END IF;
        v_stmt_str := 'SELECT
"p".productid,
"pm".manufacturerid,
"p".sename,
"p".name,
to_char(substr("p".summary, 0, 3999)),
to_number("p".freeground),
"p".quantitydiscountid,
"p".sku,
"p".price,
"p".msrp,
"p".cost,
"p".saleprice,
"p".weight,
"p".percase,
to_char(substr("p".relatedproducts, 0, 3999)),
"p".hidepriceuntilcart,
"p".discontinued,
"p".discounttype,
"p".unitofmeasure,
"p".replacement,
"pc".displayorder,
"um".friendlyurl
FROM
productnew "p"
LEFT OUTER JOIN productcategory "pc" ON "p".productid = "pc".productid
LEFT OUTER JOIN productmanufacturer "pm" ON "p".productid = "pm"
.productid
LEFT OUTER JOIN urlmapper "um" ON "p".productid = "um".productid
WHERE
"pc".categoryid = :pcategoryid
AND ( ( "p".productid IN (SELECT * FROM TABLE (string_to_table_num(:commasepproductids))) OR :commasepproductids IS NULL)
AND (lower("p".sku) IN (SELECT * FROM TABLE(string_to_table_varchar2(:commasepproductskus))) OR :commasepproductskus IS NULL ) )
AND  "p".deleted = 0 
AND "p".published = 1
ORDER BY :sortby';
     s_counter := 0;
    apex_json.open_array;
    OPEN l_cursor FOR v_stmt_str USING pcategoryid, commasepproductids, commasepproductids, commasepproductskus, commasepproductskus, v_sortby;
    
        FETCH l_cursor into
            v_productid,
            v_manufacturerid,
            v_sename,
            v_name,
            v_summary,
            v_freeground,
            v_quantitydiscountid,
            v_sku,
            v_price,
            v_msrp,
            v_cost,
            v_saleprice,
            v_weight,
            v_percase,
            v_relatedproducts,
            v_hidepriceuntilcart,
            v_discontinued,
            v_discounttype,
            v_unitofmeasure,
            v_replacement,
            v_displayorder,
            v_friendlyurl;
        loop
             EXIT WHEN l_cursor%notfound;
                s_counter := 1;
                apex_json.open_object;
                apex_json.write('ProductID', v_productid);
                apex_json.write('ManufacturerID', v_manufacturerid);
                apex_json.write('SEName', v_sename);
                apex_json.write('Name', v_name);
                apex_json.write('Summary', v_summary);
                apex_json.write('FreeGround', v_freeground);
                apex_json.write('QuantityDiscountID', v_quantitydiscountid);
                apex_json.write('SKU', v_sku);
                apex_json.write('Price', v_price);
                apex_json.write('MSRP', v_msrp);
                apex_json.write('Cost', v_cost);
                apex_json.write('SalePrice', v_saleprice);
                apex_json.write('Weight', v_weight);
                apex_json.write('PerCase', v_percase);
                apex_json.write('RelatedProducts', v_relatedproducts);
                apex_json.write('HidePriceUntilCart', v_hidepriceuntilcart);
                apex_json.write('Discontinued', v_discontinued);
                apex_json.write('QuantityDiscountType', v_discounttype);
                apex_json.write('UnitOfMeasure', v_unitofmeasure);
                apex_json.write('Replacement', v_replacement);
                apex_json.write('DisplayOrder', v_displayorder);
                apex_json.write('FriendlyUrl', v_friendlyurl);
                apex_json.close_object;
        end loop;
        
        IF s_counter = 0 THEN
        apex_json.open_object;
        apex_json.write('ProductID', 0);
        apex_json.write('SEName', 'NOT FOUND');
        apex_json.write('Name', 'NOT FOUND');
        apex_json.write('Summary', 'NOT FOUND');
        apex_json.write('FreeGround', 'NOT FOUND');
        apex_json.write('QuantityDiscountID', 0);
        apex_json.write('SKU', 'NOT FOUND');
        apex_json.write('Price', 0);
        apex_json.write('MSRP', 0);
        apex_json.write('Cost', 0);
        apex_json.write('SalePrice', 0);
        apex_json.write('Weight', 0);
        apex_json.write('PerCase', 'NOT FOUND');
        apex_json.write('RelatedProducts', 'NOT FOUND');
        apex_json.write('HidePriceUntilCart', 'NOT FOUND');
        apex_json.write('Discontinued', 'NOT FOUND');
        apex_json.write('QuantityDiscountType', 'NOT FOUND');
        apex_json.write('UnitOfMeasure', 'NOT FOUND');
        apex_json.write('Replacement', 'NOT FOUND');
        apex_json.write('FriendlyUrl', 'NOT FOUND');
        apex_json.close_object;
    END IF;

    apex_json.close_all;
END GET_CATEGORYPRODS2;

string_to_table_num

create or replace FUNCTION string_to_table_num (
    p VARCHAR2
)
   RETURN tab_number
   PIPELINED IS
BEGIN
   FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
                FROM (SELECT p || ',' str FROM dual)
              CONNECT BY level <= length(str) 
                                  - length(replace(str, ',', ''))) LOOP
      PIPE ROW(lower(cc.res));
   END LOOP;
    
END;

string_to_table_varchar

create or replace FUNCTION string_to_table_varchar2(p VARCHAR2)
   RETURN tab_varchar2
   PIPELINED IS
BEGIN
   FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
                FROM (SELECT p || ',' str FROM dual)
              CONNECT BY level <= length(str) 
                                  - length(replace(str, ',', ''))) LOOP
      PIPE ROW(lower(cc.res));
   END LOOP;
END;

Then I tried replacing the string_to_table functions with the following functions

f_convert2

create or replace FUNCTION f_convert2(p_list IN STRING)
RETURN tab_number
    PIPELINED
   AS
      l_string       LONG := p_list || ',';
      l_comma_index  PLS_INTEGER;
      l_index        PLS_INTEGER := 1;
    BEGIN
      LOOP
       l_comma_index := INSTR(l_string, ',', l_index);
       EXIT WHEN l_comma_index = 0;
       PIPE ROW ( SUBSTR(l_string, l_index, l_comma_index - l_index) );
       l_index := l_comma_index + 1;
     END LOOP;
     RETURN ;
   END f_convert2;

f_convert

create or replace FUNCTION f_convert(p_list IN STRING)
RETURN tab_varchar2
    PIPELINED
   AS
      l_string       LONG := p_list || ',';
      l_comma_index  PLS_INTEGER;
      l_index        PLS_INTEGER := 1;
    BEGIN
      LOOP
       l_comma_index := INSTR(l_string, ',', l_index);
       EXIT WHEN l_comma_index = 0;
       PIPE ROW ( SUBSTR(l_string, l_index, l_comma_index - l_index) );
       l_index := l_comma_index + 1;
     END LOOP;
     RETURN;
   END f_convert;

If I call the procedure from Postman or my Node app, it either times out or gives Please check the SQL statement is correctly formed and executes without error. SQL Error Code: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT. If I replace the apex_json.write with dbms_output.write_line and run it through SQL developer, it runs fine and very quickly.

Answer 1

Consider using a DYNAMIC_SAMPLING hint whenever using a table function. Oracle has no idea how many rows are returned by the function STRING_TO_TABLE_NUM, and will likely use a guess of 8168 rows. The dynamic sampling hint instructs Oracle to spend a little extra parsing time counting the rows, which will hopefully be much less than the time saved by a better execution plan.

For example, using this type and function:

create or replace type tab_number as table of number;

create or replace FUNCTION string_to_table_num (
    p VARCHAR2
)
   RETURN tab_number
   PIPELINED IS
BEGIN
   FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
                FROM (SELECT p || ',' str FROM dual)
              CONNECT BY level <= length(str) 
                                  - length(replace(str, ',', ''))) LOOP
      PIPE ROW(lower(cc.res));
   END LOOP;
    
END;
/

The simple example below shows Oracle wildly overestimating the number of rows as 8168 instead of the actual value of 9:

explain plan for select * from table(string_to_table_num('1,2,3,4,5,6,7,8,9'));
select * from table(dbms_xplan.display);

Plan hash value: 127161297
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STRING_TO_TABLE_NUM |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

When we add the dynamic sampling hint, Oracle get the cardinality perfect:

explain plan for select /*+ dynamic_sampling(2) */ * from table(string_to_table_num('1,2,3,4,5,6,7,8,9'));
select * from table(dbms_xplan.display);

Plan hash value: 127161297
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |     9 |    18 |    11   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STRING_TO_TABLE_NUM |     9 |    18 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

This solution doesn't directly address the question of why does the SQL perform differently in different contexts. But if you can fix two huge cardinality issues the other issues may not matter anymore.