How to optimize usage of pipelined, weakly typed ref cursor

Question

I am having some trouble with a procedure; when run for “big” sets (800+ parents, 1300+ children), it is very slow (30 - 60 secs).

Basic idea is to fetch all parent records (and their respective children) fitting a certain search criteria, along with 3 additional pieces of information that will have to be computed.

My approach to the problem was

1. to create a custom record type with additional fields for the computed values.
2. A reference to this record type can then be passed around to each function, controlled by a main processing function.
3. As a value is computed for each parent record, tack it onto the record.

Each procedure GET_PARENT_RECORDS and GET_CHILD_RECORDS are called once per search, and each computing functions are run N times (where N is the number of parent and/or child records).

---

Question 1: Is this the right approach? (weakly typed cursors, pipelined functions) If not, then how should I have approached the problem, assuming I can have a re-do?

Question 2: Barring a complete rewrite, is there anything obvious that can be improved in the code provided?

Question 3: Or could something else be wrong, as I notice that the same slow query returned in 20 secs when I've run the procedures a few times?

---

Package definition

create or replace
PACKAGE THIS_PKG AS

  Type parentCursor IS REF CURSOR;
  Type childCursor IS REF CURSOR;

  Type ParentRecordType IS RECORD (
    other_columns,
    Extra_column_A,
    Extra_column_B, 
    Extra_column_C,
    Row_num);    

  --associative array
  TYPE ParentArray IS TABLE OF ParentRecordType;

  FUNCTION processParents(
      p IN THIS_PKG. parentCursor
  )  RETURN ParentArray
  PIPELINED
  ;

  FUNCTION countSomething(some params…)
      RETURN INT;

  FUNCTION checkCondX (SomeParent IN ParentRecordType) 
      RETURN VARCHAR2;

  FUNCTION checkCondY (SomeParent IN ParentRecordType)
      RETURN VARCHAR2;

  PROCEDURE GET_PARENT_RECORDS( other_parameters, Parents OUT THIS_PKG.parentCursor);

  PROCEDURE GET_CHILD_RECORDS( other_parameters, Children OUT THIS_PKG.childCursor);

END THIS_PKG;

Package Body

-- omitted

FUNCTION processParents(
      p IN THIS_PKG.parentCursor
  )  RETURN ParentArray
  PIPELINED
  IS
      out_rec  ParentArray;
      someParent   ParentRecordType;
  BEGIN
    LOOP
        FETCH p BULK COLLECT INTO out_rec LIMIT 100;

        FOR i IN 1 .. out_rec.COUNT
        LOOP
        out_rec(i).extra_column_A := countSomething (out_rec(i).field1, out_rec(i).field2);
        out_rec(i).extra_column_B := checkCondX(out_rec(i));
        out_rec(i).extra_column_C := checkCondY(out_rec(i));
        pipe row(out_rec(i));
        END LOOP;

        EXIT WHEN p%NOTFOUND;
    END LOOP;
    RETURN;
  END processParents;

PROCEDURE GET_PARENT_RECORDS(
      some_columns,
      Parents OUT THIS_PKG. parentCursor) IS
  BEGIN   
      OPEN Parents FOR
      SELECT *
      FROM TABLE(processParents (CURSOR(
        SELECT *
        FROM (
              --some select statement with quite a few where clause 
          --to simulate dynamic search (from pre-canned search options)
       )
     ))) abc
      WHERE abc.extra_column_C like '%xyz%' --(xyz is a user given value)
      ;
END GET_PARENT_RECORDS;

---

Update Did some exploring yesterday and came across the Quest Batch SQL Optimizer (from Toad). I plugged in the package and here's what I got.

Batch Optimizer results

Complex query

Problematic query

Answer 1

What is happening in the row processing section? A lot of time may be spent in these countSomething, checkCondX/Y functions. Are they also making SQL calls? I'd check the performance of the table function without the additional predicates first. It may be better to simply create a query which does this all in SQL rather than functions - if you can do this it will be very much quicker than calling out to a function for every row.

    out_rec(i).extra_column_A := countSomething (out_rec(i).field1, out_rec(i).field2);
    out_rec(i).extra_column_B := checkCondX(out_rec(i));
    out_rec(i).extra_column_C := checkCondY(out_rec(i));

Also the explain plan you provided is interesting as the optimiser believes there is only 1 row being returned from all tables (cardinality 1). If this isn't the case then the query plan will not be optimal. It may be necessary to gather statistics, use dynamic sampling or cardinality hints on the table function.

Finally, look into DBMS_SQLTUNE.REPORT_SQL_MONITOR which provides a detailed report on your sql. Unless the query is dynamically identified as needing monitoring your need to add the /*+ MONITOR */ hint. This provides more details like number of rows returned, execution count and other interesting tidbits not available in an explain plan.

SELECT /*+ MONITOR */
FROM slow_query;

-- then run sqltune to get a report
SELECT *
FROM TABLE(DBMS_SQLTUNE.REPORT_SQL_MONITOR()); 

Answer 2

Quest Batch SQL Optimizer (from Toad) or any other tool will be unable to help you taking into consideration that they don't understand what you do inside the functions. The problem is in "FETCH p BULK COLLECT INTO out_rec LIMIT 100;". The quality of the query that is passed into p actually defines the final execution plan and run times. Pipelining is not the reason for slowness. When you run your procedure several times, Oracle uses cached data. My best advice is: use Java instead of PL/SQL for this particular purpose, it will be more simpler for understanding.