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

У меня проблемы с процедурой; при беге для "больших" сетов (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). Я подключил пакет, и вот что я получил.

Результаты Оптимизатора партииРезультаты Оптимизатора партии

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

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

Источник

2 ответа

Что происходит в секции обработки строк? Много времени может быть потрачено на эти функции countSomething, checkCondX/Y. Они также делают вызовы SQL? Сначала я проверю производительность табличной функции без дополнительных предикатов. Может быть, лучше просто создать запрос, который делает все это в SQL, а не в функциях - если вы можете сделать это, это будет намного быстрее, чем вызов функции для каждой строки.

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

Кроме того, план объяснения, который вы предоставили, интересен, так как оптимизатор полагает, что из всех таблиц возвращается только 1 строка (количество элементов 1). Если это не так, то план запроса не будет оптимальным. Может потребоваться собрать статистику, использовать динамические выборки или советы по количеству элементов в табличной функции.

Наконец, загляните в DBMS_SQLTUNE.REPORT_SQL_MONITOR, который предоставляет подробный отчет о вашем sql. Если запрос не определен динамически как требующий мониторинга, вам нужно добавить подсказку /*+ MONITOR */. Это предоставляет более подробную информацию, такую ​​как количество возвращаемых строк, количество выполненных операций и другие интересные лакомые кусочки, недоступные в плане объяснения.

SELECT /*+ MONITOR */
FROM slow_query;

-- then run sqltune to get a report
SELECT *
FROM TABLE(DBMS_SQLTUNE.REPORT_SQL_MONITOR());
Источник

Quest Batch SQL Optimizer (от Toad) или любой другой инструмент не сможет помочь вам, учитывая, что они не понимают, что вы делаете внутри функций. Проблема в "FETCH p BULK COLLECT INTO out_rec LIMIT 100;". Качество запроса, который передается в p, фактически определяет окончательный план выполнения и время выполнения. Трубопровод не является причиной медлительности. Когда вы запускаете процедуру несколько раз, Oracle использует кэшированные данные. Мой лучший совет: используйте Java вместо PL/SQL для этой конкретной цели, это будет проще для понимания.

Источник
Другие вопросы по тегам