Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT / 25 сообщений из 26, страница 1 из 2
  1  все
07.07.2010, 19:31
    #36728928
Lecter
Lecter
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
Собственно сабж.

Never again...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 19:44
    #36728943
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
Методом научного втыка - провести серию экспериментов и в данном окружении на данном паттерне нагрузки найти оптимальный limit :)
Но лично я еще ни разу не встречал (правда, уже и давно не экспериментировал) среды, где limit >100..200 давал бы ощутимый прирост.
Поэтому дефолтная 100 - вполне адекватна.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 19:50
    #36728951
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
Поскольку я причастен к созданию этой темы , приведу пример кода, иллюстрирующий вопрос автора:
Код: 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, 20:08
    #36728980
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
SQL*PlusПоскольку я причастен к созданию этой темы , приведу пример кода, иллюстрирующий вопрос автора:
Десяток экспериментов - и лимит для конкретной среды найден :)
Или просто поставить 100 и не мучаться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 20:14
    #36728990
Так красивее
Так красивее
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
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:16
    #36728991
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
andrey_anonymousДесяток экспериментов - и лимит для конкретной среды найден :)Я бы даже сказал конкретного запроса в конкретной ситуации. Есть предположение, что для запросов с результатом на темпе или прямого FTS, оптимальное начинается где-то в районе объема мультиблочного чтения деленного на размер строки. Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 20:24
    #36729002
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
Lecter, а по какому показателю вы хотите оптимизировать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 20:27
    #36729010
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
LecterNever again..."Never Say Never Again" :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 20:28
    #36729013
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
-2-Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке.И чем это может быть лучше?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 20:41
    #36729025
wildwind
wildwind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
-2-[quot andrey_anonymous]Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке. Наоборот хуже, поскольку увеличивают LIO.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 20:53
    #36729040
suPPLer
suPPLer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
ТС, а оптимальный по какому критерию: времени выполнения, PGA, ещё чему-нибудь?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 21:20
    #36729071
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
-2-Есть предположение, что для запросов с результатом на темпе или прямого FTS, оптимальное начинается где-то в районе объема мультиблочного чтения деленного на размер строки.
Ну это смотря что и где.
Если код не на сервере (конкретно PL/SQL - формсы те же), то bulk скорее позволяет оптимизировать сетевые передачи. Чем меньше round-trips случится между клиентом и сервером для получения набора данных - тем лучше используется сеть. Проблема всех синхронных протоколов, не только sql*net.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2010, 21:20
    #36729073
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
suPPLerТС, а оптимальный по какому критерию: времени выполнения, PGA, ещё чему-нибудь?
Ну начинааааитца :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.07.2010, 09:14
    #36729480
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
SQL*Plus-2-Запросы, которые думают над каждой строкой, - лучше может оказаться фетч по одной строке.И чем это может быть лучше?Копейкой памяти и процессорного времени на индексную адресацию буфера.
wildwindНаоборот хуже, поскольку увеличивают LIO.А как размер фетча влияет на LIO?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.07.2010, 09:21
    #36729487
Вячеслав Любомудров
Вячеслав Любомудров
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
-2-wildwindНаоборот хуже, поскольку увеличивают LIO.А как размер фетча влияет на LIO?Конечно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.07.2010, 11:40
    #36729859
Lecter
Lecter
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
suPPLerТС, а оптимальный по какому критерию: времени выполнения, PGA, ещё чему-нибудь?

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

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

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

Учитывать нужно осторожно. Ведь "обрывание" операции все же ситуация... не ради которой все делалось :)
ит, типо, депендс. :)
я про случай, если цель - показать пользователю на экраньчике результат его запроса
(мощностью обратно и скоростью прямо пропорционально зависящий от прямоты его же рук :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.07.2010, 17:21
    #36730980
mcureenab
mcureenab
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
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, 20:56
    #36731356
Anton Demidov
Anton Demidov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как выбрать оптимальный размер LIMIT при FETCH ... BULK COLLECT
Спасибо за скрипт.

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

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

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

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


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