Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
ozzmosis
Member

Откуда:
Сообщений: 281
Всем доброго здоровья.

Почитываю тут: "Expert PL/SQL Practices for oracle Developers and DBAs" (пдфник валяется во многих местах). Читаю главу "Bulk SQL Operations" (автор - Connor McDonnald), в ней - пример, показывающий, как плохо фетчить из большой таблицы в коллекцию без указания кляузы LIMIT (сильно растёт pga).
DDL:
-- см раздел "Monitoring Bulk Collect Overheads"
drop table hardware;
create table hardware (aisle number, item number, descr varchar2(50 char));

insert --+append
into hardware
select 
        trunc(rownum/10000)+1 aisle,
        rownum item,
        'description #'||rownum
from
(select 1 from dual connect by level<= 50),
(select 1 from dual connect by level<= 1000),
(select 1 from dual connect by level<= 1000);
commit;
Test:
declare
        type t_row is table of hardware.descr%type;
        v_rows t_row;
        v_pga number;
        type t_fetchsize is table of pls_integer;
        v_fetch_sizes t_fetchsize := t_fetchsize(50, 100, 500, 1000, 2000, 5000, 10000, 20000, 50000, 100000, 200000, 500000, 1000000, 5000000, 10000000, 20000000, 50000000);
        rc sys_refcursor;
        function get_pga return number is
                v_pga number;
        begin
                select m.value into v_pga
                from v$mystat m, v$statname s 
                where m.statistic# = s.statistic# and s.name='session pga memory';
                return v_pga;
        end;
begin
        dbms_output.put_line(to_char(current_timestamp,'dd.mm.yy hh24:mi:ss')||' init pga: '||get_pga());
        
        for i in 1..v_fetch_sizes.count loop
                open rc for select descr from hardware;
                loop
                        fetch rc bulk collect into v_rows limit v_fetch_sizes(i);
                        exit when rc%notfound;
                end loop;
                close rc;
                dbms_output.put_line( to_char(current_timestamp,'dd.mm.yy hh24:mi:ss')||' fetch limit: '||v_fetch_sizes(i)||', pga: '||get_pga());
        end loop;
end;
/

Получаю на своём серваке:
06.01.13 22:08:54 init pga: 7413872
06.01.13 22:09:11 fetch limit: 50, pga: 8200304
06.01.13 22:09:25 fetch limit: 100, pga: 7282800
06.01.13 22:09:35 fetch limit: 500, pga: 7282800
06.01.13 22:09:46 fetch limit: 1000, pga: 7282800
06.01.13 22:09:56 fetch limit: 2000, pga: 7348336
06.01.13 22:10:06 fetch limit: 5000, pga: 7544944
06.01.13 22:10:16 fetch limit: 10000, pga: 8003696
06.01.13 22:10:26 fetch limit: 20000, pga: 8790128
06.01.13 22:10:36 fetch limit: 50000, pga: 11280496
06.01.13 22:10:46 fetch limit: 100000, pga: 15474800
06.01.13 22:10:56 fetch limit: 200000, pga: 23732336
06.01.13 22:11:06 fetch limit: 500000, pga: 48636016
06.01.13 22:11:17 fetch limit: 1000000, pga: 90120304
06.01.13 22:11:27 fetch limit: 5000000, pga: 421798000
06.01.13 22:11:38 fetch limit: 10000000, pga: 836509808
06.01.13 22:11:50 fetch limit: 20000000, pga: 1665802352
06.01.13 22:12:04 fetch limit: 50000000, pga: 4153811056

Процедура PL/SQL успешно завершена.

Затрач.время: 00:03:10.46
Объясните, плз, почему время выполнения каждого варианта практически не меняется и составляет около 14 сек ?
ЗЫ.
select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
7 янв 13, 01:15    [13730392]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
Bogdanov Andrey
Member

Откуда: Да уже и сам не знаю...
Сообщений: 2203
ozzmosis
Объясните, плз, почему время выполнения каждого варианта практически не меняется и составляет около 14 сек ?
Ну вобщем-то не с чего ему особенно меняться. В каждом случае надо все записи из таблицы "отфетчить". А временные расходы на выделение pga относительно невелики (кстати, в конце время начало расти - может быть там уже своп пошел?).
7 янв 13, 11:08    [13730972]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ozzmosis,

С sql*plus'e попробуйте обычный селект с разными arraysize
7 янв 13, 13:55    [13731322]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
ozzmosis
Member

Откуда:
Сообщений: 281
xtender
С sql*plus'e попробуйте обычный селект с разными arraysize
попробовал выборку первого миллиона строк из этой таблицы. Вижу разницу только для изменения значений arraysize в пределах от 100 до 1000.
Дальше всё одинаково, примерно 5 сек.
+
arraysize ==> 50 100 200 500 1 000 1 500 2 000 2 500 3 000 3 500 4 000 4 500 5 000
consistent gets 24 455 14 505 9 530 6 545 5 549 5 213 5 052 4 952 4 884 4 839 4 803 4 775 4 753
physical reads 4 780 4 780 4 780 4 780 4 780 4 780 4 780 4 780 4 780 4 780 4 780 4 780 4 780
bytes sent via SQL*Net to client 34 629 085 33 749 085 33 309 085 33 045 085 32 957 085 32 927 781 32 913 085 32 904 285 32 898 477 32 894 253 32 891 085 32 888 709 32 886 685
bytes received via SQL*Net from client 220 349 110 349 55 349 22 349 11 349 7 686 5 849 4 749 4 023 3 495 3 099 2 802 2 549
SQL*Net roundtrips to/from client 20 001 10 001 5 001 2 001 1 001 668 501 401 335 287 251 224 201
time 00:00:25.28 00:00:16.43 00:00:09.96 00:00:09.1500:00:06.8600:00:05.12 00:00:05.2100:00:05.6800:00:05.12 00:00:05.71 00:00:04.8200:00:04.4800:00:04.48
Из статистики видно, что сильнее всего уменьшаются показатели "bytes received via SQL*Net from client" и "SQL*Net roundtrips to/from client".
Почему тогда эффект от этого нулевой ?

Впрочем, причём тут SQL*Net ? Когда я в коллекцию гружу записи, то на клиента ничего ведь не передаётся...
7 янв 13, 16:36    [13731951]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ozzmosis
Из статистики видно, что сильнее всего уменьшаются показатели "bytes received via SQL*Net from client" и "SQL*Net roundtrips to/from client".
а consistent gets не видно? :)


ozzmosis
Впрочем, причём тут SQL*Net ? Когда я в коллекцию гружу записи, то на клиента ничего ведь не передаётся...
PL/SQL движок - тоже клиент, хоть и не sql*net
8 янв 13, 02:33    [13734963]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ozzmosis,

вообще, в принципе, при оценке времени надо учитывать на что конкретно и сколько его уходит, а не просто сравнивать, тогда все проще будет понять. SQL*plus я советовал просто, чтобы разница стала более заметной...
8 янв 13, 02:38    [13734975]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
ozzmosis
Member

Откуда:
Сообщений: 281
xtender
при оценке времени надо учитывать на что конкретно и сколько его уходит, а не просто сравнивать
Хорошо, вот я запускаю несколько раз следующий код (делая переконнект перед каждым запуском):
variable n number;
exec :n := 10; -- затем другие значения: 50, 500, 1000 и т.д.
alter session set sql_trace=true;
declare
  type t_row is table of hardware.descr%type;
  v_rows t_row;
  rc sys_refcursor;
begin
  open rc for select descr from hardware;
  loop
    fetch rc bulk collect into v_rows limit :n;
    exit when rc%notfound;
  end loop;
  close rc;
end;
/
alter session set sql_trace=false;

После чего иду в tkprof и вытряхиваю оттуда статистику для select descr from hardware.
Вот что в итоге получилось:
+
	fetch limit 10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5000001 32.69 32.04 254382 5223909 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5000003 32.69 32.04 254382 5223909 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=5223909 pr=254382 pw=0 time=28804230 us cost=37214 size=1034560215 card=38317045)

fetch limit 50
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1000001 16.19 16.31 254382 1248295 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1000003 16.19 16.31 254382 1248295 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=1248295 pr=254382 pw=0 time=10152359 us cost=37214 size=1034560215 card=38317045)

fetch limit 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 500001 14.13 14.22 254382 751342 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 500003 14.13 14.22 254382 751342 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=751342 pr=254382 pw=0 time=10393125 us cost=37214 size=1034560215 card=38317045)

fetch limit 500
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 100001 12.74 12.72 254382 353783 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100003 12.74 12.72 254382 353783 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=353783 pr=254382 pw=0 time=6266427 us cost=37214 size=1034560215 card=38317045)

fetch limit 1000
SELECT DESCR FROM HARDWARE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 50001 13.45 15.44 254382 304087 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 50003 13.45 15.44 254382 304087 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=304087 pr=254382 pw=0 time=7034368 us cost=37214 size=1034560215 card=38317045)

fetch limit 10'000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5001 12.51 12.59 254382 259357 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5003 12.51 12.59 254382 259357 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=259357 pr=254382 pw=0 time=5448599 us cost=37214 size=1034560215 card=38317045)

fetch limit 100'000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 501 12.67 12.74 254382 254885 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 503 12.67 12.74 254382 254885 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=254885 pr=254382 pw=0 time=5345402 us cost=37214 size=1034560215 card=38317045)

fetch limit 1'000'000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 51 13.58 13.65 254382 254439 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 53 13.58 13.65 254382 254439 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=254439 pr=254382 pw=0 time=5563991 us cost=37214 size=1034560215 card=38317045)

fetch limit 10'000'000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6 13.84 13.87 254382 254394 0 50000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 13.84 13.87 254382 254394 0 50000000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1964 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
50000000 50000000 50000000 TABLE ACCESS FULL HARDWARE (cr=254394 pr=254382 pw=0 time=5151383 us cost=37214 size=1034560215 card=38317045)

Я не вижу в этой статистике объяснения тому, почему при увеличении limit в 10 раз (начиная с порога ~1000) общее время всех фетчей перестаёт уменьшаться.
Чем еще можно посмотреть разблюдовку временнЫх затрат ?
8 янв 13, 13:15    [13735621]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
ozzmosis
Я не вижу в этой статистике объяснения тому, почему при увеличении limit в 10 раз (начиная с порога ~1000) общее время всех фетчей перестаёт уменьшаться.
Чем еще можно посмотреть разблюдовку временнЫх затрат ?
У тебя 197 строк на блок. pin не сохраняется между fetch, поэтому при небольших limit один и тот же буфер читается несколько раз (при limit 10 каждый буфер читается по 20 раз, при limit 500 лишь некоторые буферы читаются по два раза и т.д.). Количество согласованных чтений - в колонке query. Чем больше чтений - тем больше CPU, тем больше тратится времени. У тебя еще, судя по всему, данные лежат в кэше операционной системы, поэтому картина несколько искаженная и физические чтения очень быстрые.
8 янв 13, 14:31    [13735906]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
A.
Guest
Если всё еще не понятно зачем использовать limit, то рекомендую автору увеличить таблицу с 2Гб до 50Гб и повторить эксперимент.
8 янв 13, 15:05    [13736134]     Ответить | Цитировать Сообщить модератору
 Re: fetch ... bulk collect into ... limit <N>: должен ли <N> влиять на суммарное время фетчей?  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
ozzmosis,

Помимо того, что сказал wurdu(о чем я намекал с consistent gets), влияет еще и количество переключение контекстов(в случае с pl/sql блоком), что в случае с sql*plus превращается в sqlnet round trips.
В целом разница становится просто менее заметной в общем времени

Ps. проще смотреть статистики ожиданий было
8 янв 13, 16:07    [13736437]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить