Как оптимизировать использование конвейерного, слабо типизированного курсора ref

У меня возникли проблемы с процедурой; при запуске для «больших» наборов (800+ родителей, 1300+ детей) он очень медленный (30–60 секунд).

Основная идея состоит в том, чтобы получить все родительские записи (и их соответствующие дочерние элементы), соответствующие определенным критериям поиска, вместе с 3 дополнительными элементами информации, которые необходимо будет вычислить.

Мой подход к проблеме был

1. для создания пользовательского типа записи с дополнительными полями для вычисляемых значений.
2. Затем ссылка на этот тип записи может быть передана каждой функции, управляемой основной функцией обработки.
3. Поскольку значение вычисляется для каждой родительской записи, прикрепите его к записи.

Каждая процедура GET_PARENT_RECORDS и GET_CHILD_RECORDS вызывается один раз за поиск, а каждая вычислительная функция запускается N раз (где N — количество родительских и/или дочерних записей).

Вопрос 1: правильный ли это подход? (слабо типизированные курсоры, конвейерные функции) Если нет, то как мне подойти к проблеме, предполагая, что я могу переделать?

Вопрос 2. За исключением полной перезаписи, есть ли что-то очевидное, что можно улучшить в предоставленном коде?

Вопрос 3. Может быть, что-то еще не так, так как я заметил, что один и тот же медленный запрос возвращается через 20 секунд, когда я запускаю процедуры несколько раз?

Определение пакета

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;

Тело пакета

-- 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;

Обновление Вчера провел некоторые исследования и наткнулся на Quest Batch SQL Optimizer (от Toad). Я подключил пакет и вот что я получил.

Результаты пакетной оптимизации

Сложный запрос

Проблемный запрос