Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
Возвращаю процедурой курсор, по непонятной причине зависаю при попытке открыть содержимое курсора.
Запуск самого запроса из процедуры работает молниеносно; если выключить любой из двух запросов, сцепляемых через union all, процедура так же отрабатывает быстро.

Структура процедуры:
procedure p_proc(p_param varchar2,
                outcur  out sys_refcursor) is
begin
  open outcur for
    select *
      from (select * 
              from first_query
             union all
            select * 
              from second_query)
        -- predicate block
     where 1=1;
end;
23 ноя 21, 11:57    [22399582]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8544
disgust,

Приведите реальный текст вашего запроса.
"Дьявол скрывается в деталях".
23 ноя 21, 12:30    [22399599]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3279
disgust,

юнион точно с all?

......
stax
23 ноя 21, 12:35    [22399608]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
Вызов процедуры с одним из двух подзапросов curr day / prev days работает отлично. Выполнение запроса целиком с подстановкой параметров также не выявляет никаких проблем, план запроса прекрасный.


Полный код процедуры:
procedure p_getUserOnlineSales(p_emp_id varchar2,
                               p_office_id     varchar2,
                               p_date_from   date default trunc(sysdate),
                               p_date_to     date default sysdate + 1,
                               p_price_from  number default null,
                               p_price_to    number default null,
                               p_item_group  typ_item_groups default cast(null as
                                                                          typ_item_groups),
                               p_receipt_num varchar2 default null,
                                   
                               p_page_num  number default 0,
                               p_page_size number default 10,
                               outcur      out sys_refcursor) is
  item_group_cnt number;
  v_op_id number := f_getOPIdByCode(p_office_id);
  v_emp_id number := f_getEmpIdByCodeTimesheet(p_emp_id);
begin
  -- check filters empty
  item_group_cnt := p_item_group.count(); 
  --
  open outcur for
    select *
      from (select -- visible cols
                   si.item_full_name
                 , si.final_price
                 , si.full_price
                 , si.receipt_num
                 , si.receipt_date
                 , si.vendor_code
                 , case when det.br_summary is null and mr.motiv_rate_value is not null then mr.motiv_rate_value
                        when det.br_summary is not null then det.br_summary
                    end personal_bonus_amount
                 , case when det.br_summary is null and mr.motiv_rate_value is not null then 1
                        when det.br_summary is not null then det.cross_sale_kt
                    end personal_bonus_koeff
                 -- service cols
                 , case when det.br_summary is null and mr.motiv_rate_value is not null then 'approximate'
                        when det.br_summary is not null then 'definite'
                    end personal_bonus_type
                 , coalesce(det.sale_stream, mr.sale_stream, 'Не определено') item_group_name
                 , si.operation_type
                 , si.src
                 -- pagination
                 , row_number() over (order by si.receipt_date desc) rn 
              from (-- curr day
                    select b.cost final_price
                         , case when b.discount = 0 then null else b.price
                            end full_price
                         , b.doc_number receipt_num
                         , b.receipt_date receipt_date
                         , i.item_code vendor_code
                         , i.full_name item_full_name
                         , b.subsite code_op
                         , b.operator_id
                         , to_char(b.businessday, 'yyyymm') sale_period
                         , b.oper_type operation_type
                         , 'bill' src
                      from scheme.bills b
                      join scheme.items i on i.item_code = b.item
                     where b.businessday = trunc(p_date_to)
                       and b.subsite = p_office_id
                       and b.operator_id = p_emp_id
                    union all
                    -- prev days
                    select l.txn_amount final_price
                         , case when l.disc = 0 then null else l.price
                             end full_price
                         , t.receipt_num receipt_num
                         , t.ts receipt_date
                         , i.item_code vendor_code
                         , i.full_name item_full_name
                         , s.office_code code_op
                         , e.emp_code operator_id
                         , to_char(l.dt,'yyyymm') sale_period
                         , l.txn_type operation_type
                         , 'txn' src
                      from scheme.txn t
                      join scheme.txn_lines l on t.rtl_txn_id = l.rtl_txn_id
                      join scheme.items i on l.item_id = i.item_id
                      join scheme.offices s on t.subsite_id = s.subsite_id
                      join scheme.employees e on t.employee_id = e.employee_id
                     where t.ts between trunc(p_date_from) and trunc(p_date_to)
                       and t.subsite_id = v_op_id
                       and t.employee_id = v_emp_id
                 ) si
             /* fact */
             left join scheme.sales_details det on si.sale_period = det.period
                                               and si.code_op = det.op_code
                                               and ltrim(si.operator_id,'0') = ltrim(det.tab_num,'0')
                                               and si.receipt_num = det.rcpt_num
                                               and si.vendor_code = det.item_article
             /* prognosis */
             left join scheme.rates mr on si.sale_period = mr.motiv_rate_period
                                      and si.code_op = mr.code_op
                                      and si.vendor_code = mr.code_1c
            where 1 = 1
              and si.final_price between nvl(p_price_from, si.final_price) and nvl(p_price_to, si.final_price)
              /* if no filters */
              and (item_group_cnt = 0 or coalesce(det.sale_stream, mr.sale_stream, 'Не определено') in (select * from table(p_item_group)))
              and si.receipt_num = nvl(p_receipt_num, si.receipt_num)                                        
           )
     where rn between p_page_num * p_page_size + 1 and (p_page_num + 1) * p_page_size;
end; 


Сообщение было отредактировано: 23 ноя 21, 12:46
23 ноя 21, 12:45    [22399616]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
Методика решения подобных проблем одна: смотрите планы исполнения (dbms_xplan.display_cursor, sql_id найдете в v$session), анализируете.
Если есть tuning pack, то можно воспользоваться SQL Monitor-ом.
На основании раскопанного много думаете и корректируете.
23 ноя 21, 12:47    [22399618]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
disgust

     where rn between p_page_num * p_page_size + 1 and (p_page_num + 1) * p_page_size;

ууу....
23 ноя 21, 12:48    [22399620]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
andrey_anonymous,

Вообще не уууу, работает без проблем с производительностью.
23 ноя 21, 12:50    [22399622]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
disgust
работает без проблем с производительностью.

Очевидно, мне рановато давать советы такому мастеру оптимизации, как Вы.
23 ноя 21, 12:54    [22399626]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
andrey_anonymous,

Советы давать не рано, но существенного влияния на производительность паждинатор не оказывает.
Фетч курсора виснет вне зависимости от накладывания оконной функции, при этом тесты показывают, что к проблемам приводит юнион, о чём и был задан вопрос.
23 ноя 21, 13:00    [22399635]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3279
disgust,

как вариант
по receipt_date есть индекс, c union all он "не используется"

.....
stax
23 ноя 21, 13:20    [22399645]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
disgust
существенного влияния на производительность паждинатор не оказывает.

Я же говорю - Ваше кунг-фу круче моего.
23 ноя 21, 13:21    [22399646]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
Stax
как вариант
по receipt_date есть индекс, c union all он "не используется"

Ставлю на сортировку результата объединенного union all множества для нужд "паджинатора".
Но он "влияния не оказывает", так что ТС может продолжать изыскания...
23 ноя 21, 13:23    [22399648]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
andrey_anonymous

Я же говорю - Ваше кунг-фу круче моего.


Да нет, конечно. offset мне в оракл ещё не подвезли, паджинатор через row_number() не идеален, но справляется.
Но можете и дальше обижаться.

За совет про dbms_xplan.display_cursor спасибо, пока нет возможности воспользоваться, грантов не хватает.
23 ноя 21, 13:26    [22399651]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
andrey_anonymous,

отключённая паджинация никак не повлияла, об этом я уже писал. и всё равно непонятно, почему вне процедуры запрос не тормозит.
23 ноя 21, 13:28    [22399655]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19924
disgust
andrey_anonymous,
отключённая паджинация

1. Убирать надо оконную функцию.
2. Методику разборок я уже привел - dbms_xplan.display_cursor в помощь, остальное - от лукавого.
23 ноя 21, 13:30    [22399657]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3279
disgust

и всё равно непонятно, почему вне процедуры запрос не тормозит.


1) возможно разные параметры оптимизатора, напр first/all rows

2) возможно не все сроки фетчите

3) select count(поля) from ( ... тож за разное время выполняется в процедуре и вне?

4) бинд в процедуре и константы во вне (плюсе)

5) еще что-то

зы
просить ДБА чтоб снял трассу для обоих случаев

.....
stax
23 ноя 21, 13:44    [22399666]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8544
andrey_anonymous
Stax
как вариант
по receipt_date есть индекс, c union all он "не используется"

Ставлю на сортировку результата объединенного union all множества для нужд "паджинатора".
Но он "влияния не оказывает", так что ТС может продолжать изыскания...

+1
23 ноя 21, 15:04    [22399706]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8544
disgust
andrey_anonymous,

отключённая паджинация никак не повлияла...

"Не верю!" (с) Склихасовский

Сколько всего строк выбирает этот запрос без "паджинации"?
23 ноя 21, 15:07    [22399708]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
SQL*Plus


Сколько всего строк выбирает этот запрос без "паджинации"?


68, выборка небольшая.
23 ноя 21, 15:26    [22399725]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18618
Дык может и проверить планы с "паджинацией" и без?
23 ноя 21, 15:51    [22399743]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
case solved, достучался до админов, получил план оптимизатора при выполнении запроса в процедуре.
оптимизатор каким-то мистическим образом решил начать сканить scheme.sales_details по другому индексу, вылечилось хинтом.

всем спасибо за участие, советы и саркастичные комментарии.

Сообщение было отредактировано: 24 ноя 21, 13:08
24 ноя 21, 13:08    [22400155]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
booby
Member

Откуда:
Сообщений: 2671
disgust
case solved, достучался до админов, получил план оптимизатора при выполнении запроса в процедуре.
оптимизатор каким-то мистическим образом решил начать сканить scheme.sales_details по другому индексу, вылечилось хинтом.

всем спасибо за участие, советы и саркастичные комментарии.


такой эффект обычно получается на маленьких таблицах при изобилии индексов, подходящих под условие запроса.
Надо либо порядка на четыре - пять увеличить объем данных в таблице scheme.sales_details, либо дропнуть бессмысленные индексы, начиная с самого бессмысленного, или, как-то заменить на осмысленные.
Тогда и без хинтов, скорее всего, все придет в норму.

Когда таблица маленькая, и размеры ключа в индексах отличаются, оптимизатор имеет обыкновение считать,
что для такой таблицы, из всех бессмысленных, но подходящих под условия запроса индексов,
надо просто взять тот, у которого самый короткий ключ. Отсюда возникают хинты.
Кстати, на индексы хинты, исторически, достаточно хрупкие.

Оптимизатор следует им, до тех пор, пока, в процессе своей деятельности, не посчитает их полнейшей дурью.
Правда, обычно, он может хотя бы косвенно объяснить, почему он так подумал.
Например, потому что решил, что раз объем данных возрос, то пора переходить от nested loop join к hash join.

В таких обстоятельствах, с любовью указанный вами индекс, оптимизатором может быть признан за чистый мусор.
Тогда вам придется ему объяснять:
"Дорогой, тебе хеш джойн нельзя, колокольчик в нос вставь и пользуйся нестед лупом с помощью указанного мной индекса".

Сообщение было отредактировано: 24 ноя 21, 14:22
24 ноя 21, 14:21    [22400199]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
disgust
Member

Откуда:
Сообщений: 16
booby,

Понятно, что жёстко прибивать гвоздями индекс - не самый хороший вариант.
Сейчас таблица ~24кк строк, прирастает на 3-4кк в месяц.

Вероятно, стоит поубивать все те индексы, которые явно не берутся оптимизатором и попробовать выключить хинт.
24 ноя 21, 14:42    [22400212]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
booby
Member

Откуда:
Сообщений: 2671
disgust,

вы постарайтесь поострожнее обходиться с такими приговорами.

При вынесении приговора будет хорошо, если вы точно понимаете за что приговаривается,
и должна ли быть приговорённому замена, или и так сойдет.

Если не знаете, зачем он создавался, и не можете самостоятельно определить правдоподобную версию,
лучше ничего не делать, или, хотя бы спросить у кого-то - "о чём ты думал?".

Убить и посмотреть, кто заплачет - может быть вариантом, но, предпочтительно, не в боевом окружении,
где может прилететь ответка за опрометчивые атакующие действия.
24 ноя 21, 15:45    [22400244]     Ответить | Цитировать Сообщить модератору
 Re: Падает производительность при union all в курсоре  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2872
booby

...
Кстати, на индексы хинты, исторически, достаточно хрупкие.
Оптимизатор следует им, до тех пор, пока, в процессе своей деятельности, не посчитает их полнейшей дурью.
...

Оптимизатор не посчитает их "полнейшей дурью" и будет следовать им всегда, если может их применить.
И откажется только в случае их полной невозможности, например, когда сам же перепишет запрос при преобразованиях в такой вид, где захинтованный индекс в принципе неприменим или другие хинты/настройки оптимизатора помешают.
24 ноя 21, 16:50    [22400292]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить