ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
25 сообщений из 26, страница 1 из 2
  1  все
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36728928
Lecter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lecter
Участник
Собственно сабж.

Never again...
...
Рейтинг: 0 / 0
07.07.2010, 19:31
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36728943
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Методом научного втыка - провести серию экспериментов и в данном окружении на данном паттерне нагрузки найти оптимальный limit :)
Но лично я еще ни разу не встречал (правда, уже и давно не экспериментировал) среды, где limit >100..200 давал бы ощутимый прирост.
Поэтому дефолтная 100 - вполне адекватна.
...
Рейтинг: 0 / 0
07.07.2010, 19:44
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36728951
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Поскольку я причастен к созданию этой темы , приведу пример кода, иллюстрирующий вопрос автора:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
DECLARE
   TYPE cursor_ntt IS TABLE OF source_table%ROWTYPE;
   nt_src_data cursor_ntt;
   CURSOR my_cursor IS
      SELECT * FROM source_table;
   summa NUMBER :=  0 ;
   -- Какое значение LIMIT будет оптимальным? 
   my_limit NUMBER := ???;  
BEGIN
   OPEN my_cursor;
   LOOP
      FETCH my_cursor BULK COLLECT INTO nt_src_data LIMIT my_limit;

      EXIT WHEN nt_src_data.COUNT =  0 ;

      FOR i IN  1  .. nt_src_data.COUNT LOOP
         -- Some work
         summa := summa +  1 ;
      END LOOP;
   END LOOP;
   CLOSE my_cursor;
   DBMS_OUTPUT.PUT_LINE('summa=' || summa);
END;
...
Рейтинг: 0 / 0
07.07.2010, 19:50
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36728980
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SQL*PlusПоскольку я причастен к созданию этой темы , приведу пример кода, иллюстрирующий вопрос автора:
Десяток экспериментов - и лимит для конкретной среды найден :)
Или просто поставить 100 и не мучаться.
...
Рейтинг: 0 / 0
07.07.2010, 20:08
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36728990
Так красивее
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Так красивее
Гость
SQL*Plusприведу пример кода, иллюстрирующий вопрос автора:
Тогда уж вот так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
DECLARE
   TYPE cursor_ntt IS TABLE OF source_table%ROWTYPE;
   nt_src_data cursor_ntt;
   CURSOR my_cursor IS
      SELECT * FROM source_table;
   summa NUMBER :=  0 ;
   -- Какое значение LIMIT будет оптимальным? 
   my_limit NUMBER :=  1 ;
   my_limit_inc NUMBER :=  1 ;
   timer_beg NUMBER;
   timer_end NUMBER;
BEGIN
   SELECT hsecs INTO timer_beg FROM v$timer;

   FOR n IN  1 .. 3  LOOP
     FOR m IN  1 .. 9  LOOP
       OPEN my_cursor;
       LOOP
          FETCH my_cursor BULK COLLECT INTO nt_src_data LIMIT my_limit;

          EXIT WHEN nt_src_data.COUNT =  0 ;

          FOR i IN  1  .. nt_src_data.COUNT LOOP
             -- Some work
             summa := summa +  1 ;
          END LOOP;
       END LOOP;
       CLOSE my_cursor;

       SELECT hsecs INTO timer_end FROM v$timer;
       DBMS_OUTPUT.PUT_LINE('my_limit=' || my_limit || '   ' || 'timer=' || (timer_end-timer_beg));
       timer_beg := timer_end;
       my_limit := my_limit + my_limit_inc;
     END LOOP;
     my_limit_inc := my_limit_inc *  10 ;
   END LOOP;
END;
...
Рейтинг: 0 / 0
07.07.2010, 20:14
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36728991
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
andrey_anonymousДесяток экспериментов - и лимит для конкретной среды найден :)Я бы даже сказал конкретного запроса в конкретной ситуации. Есть предположение, что для запросов с результатом на темпе или прямого FTS, оптимальное начинается где-то в районе объема мультиблочного чтения деленного на размер строки. Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке.
...
Рейтинг: 0 / 0
07.07.2010, 20:16
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729002
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
Lecter, а по какому показателю вы хотите оптимизировать?
...
Рейтинг: 0 / 0
07.07.2010, 20:24
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729010
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
LecterNever again..."Never Say Never Again" :-)
...
Рейтинг: 0 / 0
07.07.2010, 20:27
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729013
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL*Plus
Участник
-2-Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке.И чем это может быть лучше?
...
Рейтинг: 0 / 0
07.07.2010, 20:28
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729025
wildwind
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wildwind
Участник
-2-[quot andrey_anonymous]Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке. Наоборот хуже, поскольку увеличивают LIO.
...
Рейтинг: 0 / 0
07.07.2010, 20:41
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729040
Фотография suPPLer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
suPPLer
Участник
ТС, а оптимальный по какому критерию: времени выполнения, PGA, ещё чему-нибудь?
...
Рейтинг: 0 / 0
07.07.2010, 20:53
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729071
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
-2-Есть предположение, что для запросов с результатом на темпе или прямого FTS, оптимальное начинается где-то в районе объема мультиблочного чтения деленного на размер строки.
Ну это смотря что и где.
Если код не на сервере (конкретно PL/SQL - формсы те же), то bulk скорее позволяет оптимизировать сетевые передачи. Чем меньше round-trips случится между клиентом и сервером для получения набора данных - тем лучше используется сеть. Проблема всех синхронных протоколов, не только sql*net.
...
Рейтинг: 0 / 0
07.07.2010, 21:20
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729073
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
suPPLerТС, а оптимальный по какому критерию: времени выполнения, PGA, ещё чему-нибудь?
Ну начинааааитца :)
...
Рейтинг: 0 / 0
07.07.2010, 21:20
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729480
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
SQL*Plus-2-Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке.И чем это может быть лучше?Копейкой памяти и процессорного времени на индексную адресацию буфера.
wildwindНаоборот хуже, поскольку увеличивают LIO.А как размер фетча влияет на LIO?
...
Рейтинг: 0 / 0
08.07.2010, 09:14
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729487
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
-2-wildwindНаоборот хуже, поскольку увеличивают LIO.А как размер фетча влияет на LIO?Конечно
...
Рейтинг: 0 / 0
08.07.2010, 09:21
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729859
Lecter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lecter
Участник
suPPLerТС, а оптимальный по какому критерию: времени выполнения, PGA, ещё чему-нибудь?

времени выполнения
...
Рейтинг: 0 / 0
08.07.2010, 11:40
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729894
Lecter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lecter
Участник
Я так понимаю что рекомендаций Оракл по этому поводу не дает?
Типа пробуйте и вам воздастся?
...
Рейтинг: 0 / 0
08.07.2010, 11:49
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36729904
Lecter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lecter
Участник
Вячеслав Любомудров-2-wildwindНаоборот хуже, поскольку увеличивают LIO.А как размер фетча влияет на LIO?Конечно
В мемориз!
...
Рейтинг: 0 / 0
08.07.2010, 11:51
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36730163
Фотография orawish
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orawish
Участник
SQL*Plus-2-Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке.И чем это может быть лучше?
а имхо, тут оно уже - просто пофиг. (шутка )

на самом деле +1, хотя бы потому, что процесс извлечения данных
может быть остановлен, например, прерыванием от клиентского приложения.
т.е. челу надоело ждать черепашку - вжал капу cancel - инициировал соответствующий (oci или т.п.) функционал. Соответственно, точка во времени, где это можно сделать зависит от размера курсорного буфера (ну и + на стороне клиента ещё может быть аналогичная своя буферизация - влияет аналогично, тоже надо учитывать)
...
Рейтинг: 0 / 0
08.07.2010, 12:55
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36730187
Lecter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lecter
Участник
orawish,

Учитывать нужно осторожно. Ведь "обрывание" операции все же ситуация... не ради которой все делалось :)
...
Рейтинг: 0 / 0
08.07.2010, 13:05
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36730214
Фотография orawish
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orawish
Участник
Lecterorawish,

Учитывать нужно осторожно. Ведь "обрывание" операции все же ситуация... не ради которой все делалось :)
ит, типо, депендс. :)
я про случай, если цель - показать пользователю на экраньчике результат его запроса
(мощностью обратно и скоростью прямо пропорционально зависящий от прямоты его же рук :)
...
Рейтинг: 0 / 0
08.07.2010, 13:19
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36730980
mcureenab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mcureenab
Участник
LecterЯ так понимаю что рекомендаций Оракл по этому поводу не дает?
Типа пробуйте и вам воздастся?

Да. Выбор достаточно субъективный и ориентирован на то, чтобы сократить накладные расходы связанные в выволнением fetch, которые не зависят от числа выбираемых строк.

Время выполнения запроса можно описать такой функцией:

t(N) = call + row * N.

Где call - накладные расходы времени на выполнение fetch. row - время выборки одной записи без учёта накладных расходов времени. Когда N′→∞, row=t′/N′. Соответственно при небольших N″ (например 1..10) call=t″ - row*N″, где t′ и t″ время измеренное экспериментально соответственно для больших (N′) и малых (N″) значений N.

В идеальном случае накладных расходов нет и

τ(N) = row * N.

Чтобы оценить оптимальное значение N, построим график функции t(N)/τ(N) по N.

С ростом N линия графика очень быстро стремится к 1.

t(N)/τ(N) = (call + row * N) / row * N = call / (row * N) + 1;

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

Найдём N, при котором расчётная производительность будет мало отличаться (d) от идеальной.

t(N)/τ(N) = 1 + d = call / (row * N) + 1;

N = call / (d * row);

Полагая d = 0.001 можно потестить запрос column_name из all_tab_columns таким скриптом:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
declare
  cursor cr is
    select column_name
    from all_tab_columns;
  d constant number :=  0 . 001 ;
  p1 constant number :=  2 ;
  p2 constant number :=  5000 ;
  N constant  number := p1 * p2;
  t1 number;
  t2 number;
  rw number;
  cl number;
function test(
  parts number
) return number
is
  lim number := N / parts;
  td number; 
  res number :=  0 ;
  type t is table of cr%rowtype index by binary_integer;
  o t;
begin
    open cr;
    for i in  1 ..parts loop
        td := dbms_utility.get_time;
        fetch cr bulk collect into o limit lim;
        td := dbms_utility.get_time - td; 
        res := res + td;
        if o.count < lim then
            dbms_output.put_line('test: not enought rows '||o.count||' @ '||i);
        end if;
    end loop;
    close cr;
    return res;
end test;
begin
    t1 := test(p1);
    t2 := test(p2);
    dbms_output.put_line('t1:'||t1);
    dbms_output.put_line('t2:'||t2);
    rw := t1 / N;
    cl := (t2 - t1) / p2;
    dbms_output.put_line('row:'||rw);
    dbms_output.put_line('call:'||cl);
    dbms_output.put_line('Opt N:'|| cl / (d * rw));
end;

Скрипт следует запускать несколько раз, чтобы значения стабилизировались.

У меня получились такие значения:

t1:283
t2:314
row:.0283
call:.0062
Opt N:219.08127208480565371024734982332155477

На другом сервере, где собственно выборка строк из БД идёт медленно, получил такой результат:

t1:6987
t2:7009
row:,6987
call:,0044
Opt N:6,29740947473880062974094747388006297409
...
Рейтинг: 0 / 0
08.07.2010, 17:21
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #36731356
Фотография Anton Demidov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Anton Demidov
Участник
Спасибо за скрипт.

Небольшие разъяснения для рискнувших использовать его:
Перепишите определение курсора cr под ваш запрос.

Если исполнение выпадает по ошибке деления на ноль, увеличьте p2 constant number := 50000;

У меня на боевой базе оригинальный запрос на all_tab_columns вернул 400, а на большие таблицы - порядка 14000 в зависимости от использования индексов.
...
Рейтинг: 0 / 0
08.07.2010, 20:56
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #39533754
Гдеправда
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гдеправда
Гость
Господа, а кто прокомментирует, d=0.001 что это? Насколько я понял - точность, к которой мы стремимся. Но при уменьшении значения до 0.0001 получаем увеличение лимита в 10 раз.

Где правда?
...
Рейтинг: 0 / 0
10.10.2017, 05:50
| Ответить | Цитировать | Написать  
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
    #39533789
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
ГдеправдаГде правда?В лженауке нет правды.
 
andrey_anonymousПоэтому дефолтная 100 - вполне адекватна.
...
Рейтинг: 0 / 0
10.10.2017, 07:56
| Ответить | Цитировать | Написать  
25 сообщений из 26, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=36729859&tid=1885133]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 182ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 67ms
get tp. blocked users:
 1ms
others: 246ms
total:  543ms
созд. / загр.: 543ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]