Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Опять тормоза с pipelined  [new]
Landgraf
Member

Откуда:
Сообщений: 278
Приветствую всех!

Товарищи, здесь уже обсуждалась подобная тема, но так она и не была решена до конца. Собственно у меня очень похожая ситуация. Есть запрос, который достаточно шустро работает, и как обычный SQL и как часть анонимного блока в PL/SQL(курсор). Сделал pipelined функцию, поместил туда свой запрос. И начинается здесь какая-то котовасия! То выборка из функции работает идеально быстро, то висит по часу. Причем, сегодня ввел параметры, обработка прошла за секунды. На следующий день пришел, запустил отбор с теми-же параметрами и выборка работает долго (30 мин и больше). Причем возвращаемое количество записей ничтожно мало от 1 до N. Но, как правило N, не превышает 50 записей.
Запрос идет к таблице Orders, там есть индекс основанный на функции (UPPER(SENDERSURNAME)). Если не использовать этот индекс, то запрос долго работает. Может быть такое, что при каждом обращении к pipelined функции заново формируется план для запроса? И в какой то момент индекс подхватывается, а в какой то нет.

Планы запросов прилагаю ниже:
Сама функция
CREATE OR REPLACE Function V_FindPeople (dDateFrom in date, dDateTo in date, Familiya in varchar2, Imya in varchar2, Otchestvo in varchar2)
       Return DopTypePack.FindPeopleCollect PIPELINED
IS
  Rec DopTypePack.FindPeople_Record;
  IncorrectPeriod Exception; 
Begin
  if dDateFrom > dDateTo then RAISE IncorrectPeriod; end if;
  
  if Trunc(MONTHS_BETWEEN(dDateTo,dDateFrom))>120 then RAISE IncorrectPeriod; end if; 

  for c_People in (
                        select
                        ORD.Label Номер,
                        ORD.Amount Сумма,
                        getcurrencyname(ORD.Currency) Валюта,
                        ORD.Paydate Дата,
                        trim(upper(ORD.Sendersurname))||' '||trim(upper(ORD.Senderfirstname))||NVL2(trim(ORD.Sendersecondname),' '||trim(upper(ORD.Sendersecondname)),null) Отправитель,
                        trim(upper(ORD.Receiversurname))||' '||trim(upper(ORD.Receiverfirstname))||NVL2(trim(ORD.Receiversecondname),' '||trim(upper(ORD.Receiversecondname)),null) Получатель


                          from ORDERS DT
                          where ORD.Entityclass=1734
                          and ORD.Entitystate in (2071,1736,460738)
                          and ORD.Istestdoc=0
                          and ORD.Paydate >= dDateFrom
                          and ORD.Paydate <= dDateTo
                          and (
                          (upper(ORD.Sendersurname) = upper(Familiya) and upper(trim(ORD.Senderfirstname))||' '||upper(trim(ORD.Sendersecondname)) =  upper(Imya||' '||Otchestvo))
                          or
                          (upper(ORD.Receiversurname) = upper(Familiya) and  upper(trim(ORD.Receiverfirstname))||' '||upper(trim(ORD.Receiversecondname)) =  upper(Imya||' '||Otchestvo)))
                          order by Дата
                          ) loop

        Rec := c_People;
        PIPE ROW(Rec);
  End Loop;
  RETURN;

EXCEPTION
         When IncorrectPeriod then null;  
  
End V_FindPeople;


Версия СУБД и план выполнения запроса SQL:
SQL> select * from  v$version;

BANNER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
----------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                    
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi                                                                                                                                                                                                                                                                                                                                                                                                                                                    
PL/SQL Release 10.2.0.3.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
CORE	10.2.0.3.0	Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
TNS for Solaris: Version 10.2.0.3.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
NLSRTL Version 10.2.0.3.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

SQL> set autot trace
SQL>                   select
  2                          ORD.Label Номер,
  3                          ORD.Amount Сумма,
  4                          getcurrencyname(ORD.Currency) Валюта,
  5                          ORD.Paydate Дата,
  6                          trim(upper(ORD.Sendersurname))||' '||trim(upper(ORD.Senderfirstname))||NVL2(trim(ORD.Sendersecondname),' '||trim(upper(ORD.Sendersecondname)),null) Отправитель,
  7                          trim(upper(ORD.Receiversurname))||' '||trim(upper(ORD.Receiverfirstname))||NVL2(trim(ORD.Receiversecondname),' '||trim(upper(ORD.Receiversecondname)),null) Получатель
  8                           from ORDERS DT
  9                            where ORD.Entityclass=1734
 10                            and ORD.Entitystate in (2071,1736,460738)
 11                            and ORD.Istestdoc=0
 12                            and ORD.Paydate >=  '01.01.04'
 13                            and ORD.Paydate <= '01.04.13'
 14                            and (
 15                            (upper(ORD.Sendersurname) = upper('Петров') and upper(trim(ORD.Senderfirstname))||' '||upper(trim(ORD.Sendersecondname)) =  upper('Иван'||' '||'Иванович'))
 16                            or
 17                            (upper(ORD.Receiversurname) = upper('Петров') and  upper(trim(ORD.Receiverfirstname))||' '||upper(trim(ORD.Receiversecondname)) =  upper('Иван'||' '||'Иванович')))
 18                            order by Дата;

32 строк выбрано.


План выполнения
----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                          
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=454 Card=95 Bytes=81                                                                                                                                                                                                                                                                                                                                                                                                                                              
          70)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
   1    0   SORT (ORDER BY) (Cost=454 Card=95 Bytes=8170)                                                                                                                                                                                                                                                                                                                                                                                                                                                           
   2    1     FILTER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS' (TABLE)                                                                                                                                                                                                                                                                                                                                                                                                                                              
           (Cost=453 Card=95 Bytes=8170)                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
   4    3         BITMAP CONVERSION (TO ROWIDS)                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
   5    4           BITMAP OR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
   6    5             BITMAP CONVERSION (FROM ROWIDS)                                                                                                                                                                                                                                                                                                                                                                                                                                                               
   7    6               SORT (ORDER BY)                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
   8    7                 INDEX (RANGE SCAN) OF 'IX_ORDERS_RCVSUR                                                                                                                                                                                                                                                                                                                                                                                                                                              
          NAME' (INDEX) (Cost=12 Card=13135656)                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
   9    5             BITMAP CONVERSION (FROM ROWIDS)                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  10    9               SORT (ORDER BY)                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
  11   10                 INDEX (RANGE SCAN) OF 'IX_ORDERS_SNDSUR                                                                                                                                                                                                                                                                                                                                                                                                                                              
          NAME' (INDEX) (Cost=11 Card=13135656)                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    




Статистика
----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          0  recursive calls                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          0  db block gets                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          0  consistent gets                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          0  physical reads                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
          0  redo size                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
          0  bytes sent via SQL*Net to client                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
          0  bytes received via SQL*Net from client                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
          0  SQL*Net roundtrips to/from client                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
          0  sorts (memory)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
          0  sorts (disk)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
         32  rows processed                                         


Трасировка запроса из pipelined функции:
SQL> select * from TABLE(v_findpeople('01.01.04','01.04.13', 'Петров', 'Иван','Иванович'));

32 строк выбрано.


План выполнения
----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                          
   0      SELECT STATEMENT Optimizer=CHOOSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'V_FINDPEOPLE' (PRO                                                                                                                                                                                                                                                                                                                                                                                                                                              
          CEDURE)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    




Статистика
----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          0  recursive calls                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          0  db block gets                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          0  consistent gets                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          0  physical reads                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
          0  redo size                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
          0  bytes sent via SQL*Net to client                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
          0  bytes received via SQL*Net from client                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
          0  SQL*Net roundtrips to/from client                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
          0  sorts (memory)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
          0  sorts (disk)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
         32  rows processed            


Товарищи у кого есть какие соображения по этому поводу?
12 апр 13, 16:18    [14174057]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Landgraf
Member

Откуда:
Сообщений: 278
Написал еще одну функцию, в которой внутри используются bind-переменные. Если сравнивать с точки зрения оптимизации и производительности, на сколько она лучше/хуже моей первой функции?

CREATE OR REPLACE Function V_FindPeople2 (dDateFrom in date, dDateTo in date, Familiya in varchar2, Imya in varchar2, Otchestvo in varchar2)
       Return DopTypePack.FindPeopleCollect PIPELINED
IS
  Rec DopTypePack.FindPeople_Record;
  IncorrectPeriod Exception;
  c_Zapros sys_refcursor;
Begin
  if dDateFrom > dDateTo then RAISE IncorrectPeriod; end if;

  if Trunc(MONTHS_BETWEEN(dDateTo,dDateFrom))>120 then RAISE IncorrectPeriod; end if; --- Если период больше 10 лет, то не ищем

  Open c_Zapros for (
                        'select
                        ORD.Label Номер,
                        ORD.Amount Сумма,
                        getcurrencyname(ORD.Currency) Валюта,
                        ORD.Paydate Дата,
                        trim(upper(ORD.Sendersurname))||'' ''||trim(upper(ORD.Senderfirstname))||NVL2(trim(ORD.Sendersecondname),'' ''||trim(upper(ORD.Sendersecondname)),null) Отправитель,
                        trim(upper(ORD.Receiversurname))||'' ''||trim(upper(ORD.Receiverfirstname))||NVL2(trim(ORD.Receiversecondname),'' ''||trim(upper(ORD.Receiversecondname)),null) Получатель


                          from ORDERS ORD
                          where ORD.Entityclass=1734
                          and ORD.Entitystate in (2071,1736,460738)
                          and ORD.Istestdoc=0
                          and ORD.Paydate >= :dDateFrom
                          and ORD.Paydate <= :dDateTo
                          and (
                          (upper(ORD.Sendersurname) = upper(:Familiya) and upper(trim(ORD.Senderfirstname))||'' ''||upper(trim(ORD.Sendersecondname)) =  upper(:Imya||'' ''||:Otchestvo))
                          or
                          (upper(ORD.Receiversurname) = upper(:Familiya) and  upper(trim(ORD.Receiverfirstname))||'' ''||upper(trim(ORD.Receiversecondname)) =  upper(:Imya||'' ''||:Otchestvo)))
                          order by Дата') using dDateFrom,dDateTo,Familiya,Imya,Otchestvo,Familiya,Imya,Otchestvo;

  Loop
        
        FETCH c_Zapros into Rec;
        Exit when c_Zapros%notfound;
        PIPE ROW(Rec);
       
  End Loop;
  Close c_Zapros;
  RETURN;

EXCEPTION
         When IncorrectPeriod then null;

End V_FindPeople2;
12 апр 13, 18:33    [14174819]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Landgraf
Написал еще одну функцию, в которой внутри используются bind-переменные. Если сравнивать с точки зрения оптимизации и производительности, на сколько она лучше/хуже моей первой функции?
стало хуже, потому что добавилась ненужная динамика. Поверьте, Вы ничего не добавили относительно использования bind-переменных. Они и так использовались.

Возможно есть индексы ещё? По дате, например?

Если у Вас есть повторяемость тормозов, то снимите трассировку с ожиданиями и смотрите, что подвисает...
15 апр 13, 10:41    [14180161]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
Landgraf,

Как-то несколько странно выглядят нулевые статистики, покажите статистику из v$sesstat.
15 апр 13, 10:53    [14180228]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Landgraf
Member

Откуда:
Сообщений: 278
JaRo
Landgraf
Написал еще одну функцию, в которой внутри используются bind-переменные. Если сравнивать с точки зрения оптимизации и производительности, на сколько она лучше/хуже моей первой функции?
стало хуже, потому что добавилась ненужная динамика. Поверьте, Вы ничего не добавили относительно использования bind-переменных. Они и так использовались.

Возможно есть индексы ещё? По дате, например?

Если у Вас есть повторяемость тормозов, то снимите трассировку с ожиданиями и смотрите, что подвисает...


Да, есть индекс по полю Paydate. Четкая закономерность проявления тормозов вообще не наблюдается. Вчера полдня гонял запрос с выборкой, передавая различные параметры, работало все быстро! Как сегодня будет, не знаю. Приеду начну тестировать. Думаю трассмровка запроса здесь конечно необходима. Другое дело можно и целый день с трассировкой просидеть, а он собака работать будет быстро :-)

По поводу использования bind переменных: получается это прерагатива внешних систем(оболочек) либо внутреннего ораклого кода, при условии, что там используется курсор или динамический sql, применяющий конкатенацию параметра для предиката. Так можно сказать?
15 апр 13, 11:33    [14180458]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Landgraf
Member

Откуда:
Сообщений: 278
ten
Landgraf,

Как-то несколько странно выглядят нулевые статистики, покажите статистику из v$sesstat.


Сейчас нет базы под рукой, как доеду до работы, покажу.
15 апр 13, 11:36    [14180478]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Иван Помидоров
Member

Откуда: альянс
Сообщений: 1108
Landgraf
ten
Landgraf,

Как-то несколько странно выглядят нулевые статистики, покажите статистику из v$sesstat.


Сейчас нет базы под рукой, как доеду до работы, покажу.


Расстрелять за неявные преобразования. К написанию кода не допускать.
15 апр 13, 11:47    [14180549]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Landgraf
Да, есть индекс по полю Paydate.
Возможно Вы запускали с такими параметрами по этим датам, когда Оракл выбрал этот индекс как наиболее подходящий, далее именно этот план был зафиксирован и использовался в cитуациях, где он уже перестал быть селективным.

Landgraf
По поводу использования bind переменных: получается это прерагатива внешних систем(оболочек) либо внутреннего ораклого кода, при условии, что там используется курсор или динамический sql, применяющий конкатенацию параметра для предиката. Так можно сказать?
Вот честно, не понимаю, что именно Вы хотели сказать :) Что касается PLSQL - Вы просто посмотрите, как будет выглядеть запрос в трассе - думаю, вопросы отпадут.
15 апр 13, 11:56    [14180599]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Landgraf
Member

Откуда:
Сообщений: 278
Иван Помидоров
Расстрелять за неявные преобразования. К написанию кода не допускать.


Аргументируйте свой ответ.
15 апр 13, 14:18    [14181763]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Иван Помидоров
Member

Откуда: альянс
Сообщений: 1108
Landgraf
Иван Помидоров
Расстрелять за неявные преобразования. К написанию кода не допускать.


Аргументируйте свой ответ.



CREATE OR REPLACE Function V_FindPeople (dDateFrom in date, dDateTo in date, Familiya in varchar2, Imya in varchar2, Otchestvo in varchar2)

+

select * from TABLE(v_findpeople('01.01.04','01.04.13', 'Петров', 'Иван','Иванович'));
15 апр 13, 14:28    [14181828]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Skadovskiy
Member

Откуда: Екатеринбург
Сообщений: 16
Тынц
15 апр 13, 14:29    [14181834]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Landgraf
Member

Откуда:
Сообщений: 278
Иван Помидоров
Landgraf
пропущено...


Аргументируйте свой ответ.



CREATE OR REPLACE Function V_FindPeople (dDateFrom in date, dDateTo in date, Familiya in varchar2, Imya in varchar2, Otchestvo in varchar2)

+

select * from TABLE(v_findpeople('01.01.04','01.04.13', 'Петров', 'Иван','Иванович'));


А как надо? Я не так давно занимаюсь написания кода под Oracle, поэтому к конструктивной критике отношусь положительно. Покажите, как правильно.
15 апр 13, 14:49    [14181968]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
Skadovskiy,
Это не то, у ТС проблема не в соединении с функцией, а внутри самой функции.
15 апр 13, 14:51    [14181993]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
гыгыгы
Guest
Landgraf,
8лет это действительно не так давно ))
тынц
15 апр 13, 15:03    [14182083]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
d234
Guest
Landgraf
...


Когда вы избавляетесь от bind variables, подставляя статические значения, получаете совсем другой запрос, смысла их сравнивать нет, используйте те же bind variables.
А вообще, я бы поковырял SQL Tuning Sets и посмотрел-посравнивал какие там планы генерируются и статистика
15 апр 13, 15:04    [14182087]     Ответить | Цитировать Сообщить модератору
 Re: Опять тормоза с pipelined  [new]
Иван Помидоров
Member

Откуда: альянс
Сообщений: 1108
Landgraf
Иван Помидоров
пропущено...



CREATE OR REPLACE Function V_FindPeople (dDateFrom in date, dDateTo in date, Familiya in varchar2, Imya in varchar2, Otchestvo in varchar2)

+

select * from TABLE(v_findpeople('01.01.04','01.04.13', 'Петров', 'Иван','Иванович'));


А как надо? Я не так давно занимаюсь написания кода под Oracle, поэтому к конструктивной критике отношусь положительно. Покажите, как правильно.


То, что написано Вас есть мина, которая взорвется в самый неподходящий момент - в запросе к функции Вы написали строки, а сама функция ждет дату. Строка в дату преобразуется по определенным правилам (если, конечно, Вы не напишете to_date('Ваша строка с датой','формат даты') ), которые включают в себя настройки на клиенте (nls). Т.е. у Вас может сработать, у кого-то еще сработает, а у кого-то нет. Веселей всего, когда у Вас сработает, у тестеров сработает, а потом попадет к клиенту...
15 апр 13, 15:04    [14182090]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить