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

Откуда:
Сообщений: 56
Имеем:
sif v_selectiondata.coreferencetype = cnst_req then
      v_main_sql := '
                      select r.corequest,
                             r.cobillgroup,
                             nvl(k.timezonevalue,0),
                             substr(
                              decode(c.legalentity,
                                     1,
                                     bg.phone,
                                     (
                                      select bga.smsnum
                                        from billgroup_info_addl bga
                                       where bga.cobillgroup = bg.cobillgroup
                                     )
                                    ),
                                    1,
                                    12
                              ) as ctn
                        from billgroup4sms_send_tmp_test btg,
                             request r,
                             client c,
                             billgroup bg,
                             katimezone k,
                             departments d
                      where bg.cobillgroup = btg.cobillgroup
                        and bg.coclient = c.coclient
                        and r.coclient = c.coclient
                        and r.cobillgroup = bg.cobillgroup
                        and btg.id_dep = d.id_dep
                        and k.cotimezone = d.cotimezone
                    ';
      if v_filter_data.req_type.count() <> 0 then
        v_main_sql := v_main_sql || ' and r.corequesttype in (:reqtype) ';
      end if;
      v_cur_id := dbms_sql.open_cursor();
      dbms_sql.parse(c             => v_cur_id,
                     statement     => v_main_sql,
                     language_flag => dbms_sql.native);
    
      if v_filter_data.req_type.count() <> 0 then
        dbms_sql.bind_array(v_cur_id, 'reqtype', v_filter_data.req_type);
      end if;
      dbms_output.put_line(v_main_sql);
      v_rc      := dbms_sql.execute(v_cur_id);
      v_ref_cur := dbms_sql.to_refcursor(v_cur_id);
      insert into sms_send_test
        select *
          from table(SMS_DELIVERY_PKG.make_sms_by_req(v_ref_cur,
                               
          
                                 a_coselection,
                                                      a_userid));

Коллекция v_filter_data.req_type содержит 1500 тысячи различных значений.
Функция SMS_DELIVERY_PKG.make_sms_by_req содержит код не представляющий интереса.
Проблема в том, что после выполнения таблица sms_send_test - пуста.
Если при этом выполнить исходный запрос, подставив вместо reqtype значения из коллекции, то все работает.
Снял трассу, и увидел, что запрос выполняется только для 3 значений коллекций, для которых нет записей.
SQL Statements
SQL Hash Value: 4068245201     SQL Id: fyzzb6rt7szqj     uid: 37    depth: 1,2    optimizer mode: ALL_ROWS
Statement Text
                      select r.corequest,
                             r.cobillgroup,
                             nvl(k.timezonevalue,0),
                             substr(
                              decode(c.legalentity,
                                     1,
                                     bg.phone,
                                     (
                                      select bga.smsnum
                                        from billgroup_info_addl bga
                                       where bga.cobillgroup = bg.cobillgroup
                                     )
                                    ),
                                    1,
                                    12
                              ) as ctn
                        from billgroup4sms_send_tmp_test btg,
                             request r,
                             client c,
                             billgroup bg,
                             katimezone k,
                             departments d
                      where bg.cobillgroup = btg.cobillgroup
                        and bg.coclient = c.coclient
                        and r.coclient = c.coclient
                        and r.cobillgroup = bg.cobillgroup
                        and btg.id_dep = d.id_dep
                        and k.cotimezone = d.cotimezone
                     and r.corequesttype in (:reqtype) and r.created >=
                       :reqcreate_start and r.created <= :reqcreate_to and
                       r.status in (:reqstatus)

Statement Cumulative Statistics
Call	Cache
Misses	Count	 - Seconds -	Physical
Reads	 - Logical Reads -	Rows
CPU	Elapsed	Consistent	Current
Parse	 1	 1	 0.0050s	 0.0170s	 0	 0	 0	 0
Exec	 1	 1	 0.1390s	 0.1585s	 0	 156	 0	 0
Fetch	  	 1	 0.0010s	 0.0003s	 0	 3	 0	 0
Total	 2	 3	 0.1450s	 0.1759s	 0	 159	 0	 0
Per Fch	 2.0	 3.0	 0.1450s	 0.1759s	 0.0	 159.0	 0.0	 0.0
Per Row	 2.0	 3.0	 0.1450s	 0.1759s	 0.0	 159.0	 0.0	 0.0
Statement Self Statistics
Call	Cache
Misses	Count	 - Seconds -	Physical
Reads	 - Logical Reads -	Rows
CPU	Elapsed	Consistent	Current
Parse	 1	 1	 0.0040s	 0.0080s	 0	 0	 0	 0
Exec	 1	 1	 0.0510s	 0.0710s	 0	 2	 0	 0
Fetch	  	 1	 0.0010s	 0.0003s	 0	 3	 0	 0
Total	 2	 3	 0.0560s	 0.0794s	 0	 5	 0	 0
Per Fch	 2.0	 3.0	 0.0560s	 0.0794s	 0.0	 5.0	 0.0	 0.0
Per Row	 2.0	 3.0	 0.0560s	 0.0794s	 0.0	 5.0	 0.0	 0.0
Statement Flat Profile
Event Name	 % Time	 Seconds	 Calls	 - Time per Call -
Avg	 Min	 Max
EXEC calls [CPU]	 91.1%	 0.0510s	 1	 0.0510s	 0.0510s	 0.0510s
PARSE calls [CPU]	 7.1%	 0.0040s	 1	 0.0040s	 0.0040s	 0.0040s
FETCH calls [CPU]	 1.8%	 0.0010s	 1	 0.0010s	 0.0010s	 0.0010s
Total	100.0%	0.0560s	 

Statement Plan
met 1 time
Rows	Row Source Operation	Object Id
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0	TABLE ACCESS BY INDEX ROWID BILLGROUP_INFO_ADDL (cr=0 pr=0 pw=0 time=0.0000s cost=4 size=8 card=1)
  INDEX RANGE SCAN I_BG_I_ADDL (cr=0 pr=0 pw=0 time=0.0000s cost=3 size=0 card=1)
FILTER  (cr=3 pr=0 pw=0 time=0.0004s)
  NESTED LOOPS  (cr=3 pr=0 pw=0 time=0.0004s cost=1,139 size=98 card=1)
    NESTED LOOPS  (cr=3 pr=0 pw=0 time=0.0004s cost=1,137 size=89 card=1)
      NESTED LOOPS  (cr=3 pr=0 pw=0 time=0.0004s cost=1,136 size=83 card=1)
        HASH JOIN  (cr=3 pr=0 pw=0 time=0.0004s cost=1,135 size=77 card=1)
          NESTED LOOPS  (cr=3 pr=0 pw=0 time=0.0002s)
            NESTED LOOPS  (cr=3 pr=0 pw=0 time=0.0002s cost=1,095 size=867 card=17)
              TABLE ACCESS BY INDEX ROWID REQUEST (cr=3 pr=0 pw=0 time=0.0002s cost=1,061 size=578 card=17)
                BITMAP CONVERSION TO ROWIDS (cr=3 pr=0 pw=0 time=0.0001s)
                  BITMAP AND  (cr=3 pr=0 pw=0 time=0.0001s)
                    BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=0.0001s)
                      INDEX RANGE SCAN REQUEST_COREQUESTTYPE (cr=3 pr=0 pw=0 time=0.0001s cost=47 size=0 card=19,816)
                    BITMAP CONVERSION FROM ROWIDS (cr=0 pr=0 pw=0 time=0.0000s)
                      SORT ORDER BY (cr=0 pr=0 pw=0 time=0.0000s)
                        INDEX RANGE SCAN REQUEST_CREATED (cr=0 pr=0 pw=0 time=0.0000s cost=447 size=0 card=19,816)
              INDEX UNIQUE SCAN PK_BILLGROUP (cr=0 pr=0 pw=0 time=0.0000s cost=1 size=0 card=1)
            TABLE ACCESS BY INDEX ROWID BILLGROUP (cr=0 pr=0 pw=0 time=0.0000s cost=2 size=17 card=1)
          TABLE ACCESS FULL BILLGROUP4SMS_SEND_TMP_TEST (cr=0 pr=0 pw=0 time=0.0000s cost=40 size=2,447,614 card=94,139)
        TABLE ACCESS BY INDEX ROWID DEPARTMENTS (cr=0 pr=0 pw=0 time=0.0000s cost=1 size=6 card=1)
          INDEX UNIQUE SCAN PK_DEPARTMENTS (cr=0 pr=0 pw=0 time=0.0000s cost=0 size=0 card=1)
      TABLE ACCESS BY INDEX ROWID KATIMEZONE (cr=0 pr=0 pw=0 time=0.0000s cost=1 size=6 card=1)
        INDEX UNIQUE SCAN KATIMEZONE_PK (cr=0 pr=0 pw=0 time=0.0000s cost=0 size=0 card=1)
    INDEX RANGE SCAN CLIENT_F3 (cr=0 pr=0 pw=0 time=0.0000s cost=2 size=9 card=1)	170407
427925
 
 
 
 
 
 
 
50195
 
 
 
127452
 
 
84760
47687
47686
427592
48653
48654
49281
49282
84741

Statement Binds Statistics
total binds sets: 3    unique binds sets: 3
Sample Bind Set
met 1 time; elapsed time spent: 0.0710s 
Name	Data Type	Value
reqtype	NUMBER	19888
reqcreate_start	DATE	"1/1/2013 0:0:0"
reqcreate_to	DATE	"1/1/2500 23:59:59"
reqstatus	NUMBER	40

Шо за фигня?
21 янв 14, 18:57    [15448767]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Fangrem
..
Функция SMS_DELIVERY_PKG.make_sms_by_req содержит код не представляющий интереса.
..

как это, как это?! очень даже представляет интерес.
она (хотя бы) конвейерная?
21 янв 14, 19:28    [15448908]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
-2-
Member

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

bind_array используется для bulk dml. Твой запрос выполнен один раз для одного bulk-значения. Используй bind_variable
21 янв 14, 19:38    [15448952]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
-2-
Member

Откуда:
Сообщений: 15330
И кстати, оператор IN к коллекциям неприменим.
21 янв 14, 19:40    [15448957]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
orawish
Fangrem
..
Функция SMS_DELIVERY_PKG.make_sms_by_req содержит код не представляющий интереса.
..

как это, как это?! очень даже представляет интерес.
она (хотя бы) конвейерная?

Конвеерная, конвеерная. Не имеет значения, потому что даже более простой код работает не так как я предполагаю:
declare
  v_sql      varchar2(4000);
  v          number;
  v_coclient number_t;
  cur        sys_refcursor;
  v_rc       number;
  v_cocl     dbms_sql.Number_Table;
  v_c        number;
begin
  execute immediate 'alter session set events ''10046 trace name context forever, level 4''';
  v_sql := 'select coclient 
            from client
            where coclient in :coclient';
  v     := dbms_sql.open_cursor();
  dbms_sql.parse(c             => v,
                 statement     => v_sql,
                 language_flag => dbms_sql.native);
  v_cocl(1) := 1;
  v_cocl(2) := 2;
  v_cocl(3) := 3;
  v_cocl(4) := 4;
  v_cocl(5) := 5;

  dbms_sql.bind_variable(v, 'coclient', value => v_cocl);
  v_rc := dbms_sql.execute(v);
  cur  := dbms_sql.to_refcursor(cursor_number => v);
  fetch cur bulk collect
    into v_coclient;
  dbms_output.put_line(v_coclient.count());
end;


в результате получается 0. Хотя запрос
select *
  from client 
where coclient in (1,2,3,4,5)

возвращает аж 2 строки.
При этом в трассе я вижу вот такую картину:
BINDS #47049016313592:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0081 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aca73f2a0d0  bln=22  avl=02  flg=05
  value=1
BINDS #47049016313592:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0081 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aca73f2a0d0  bln=22  avl=02  flg=05
  value=2
BINDS #47049016313592:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0081 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aca73f2a0d0  bln=22  avl=02  flg=05
  value=3
BINDS #47049016313592:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0081 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aca73f2a0d0  bln=22  avl=02  flg=05
  value=4
BINDS #47049016313592:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0081 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2aca73f2a0d0  bln=22  avl=02  flg=05
  value=5


То есть все-таки вся коллекция была обработана. Но где значения?!
21 янв 14, 20:16    [15449129]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
В коде ошибка, конечно же там не
dbms_sql.bind_variable(v, 'coclient', value => v_cocl);
а
dbms_sql.bind_array(v, 'coclient', value => v_cocl);
21 янв 14, 20:18    [15449134]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
-2-
Fangrem,

bind_array используется для bulk dml. Твой запрос выполнен один раз для одного bulk-значения. Используй bind_variable

не очень понимаю как?
Проходится по коллекции и делать bind_variable для каждого его элемента?
21 янв 14, 20:20    [15449143]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
Fangrem
не очень понимаю как?
Проходится по коллекции и делать bind_variable для каждого его элемента?


А зачем начинать с DBMS_SQL и затем превращать в ref cursor? Опиши тип v_filter_data.req_type.

SY.
21 янв 14, 21:21    [15449310]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
Понимаю что я чего-то не понимаю.
declare
  v_sql      varchar2(4000);
  v          number;
  --v_coclient number_t;
  v_coclient dbms_sql.Number_Table;
  cur        sys_refcursor;
  v_rc       number;
  v_cocl     dbms_sql.Number_Table;
  v_c        number;
begin
 -- execute immediate 'alter session set events ''10046 trace name context forever, level 4''';
  v_sql := 'select coclient 
            from client
            where coclient in :coclient';
  v     := dbms_sql.open_cursor();
  dbms_sql.parse(c             => v,
                 statement     => v_sql,
                 language_flag => dbms_sql.native);
  v_cocl(1) := 1;
  v_cocl(2) := 2;
  v_cocl(3) := 3;
  v_cocl(4) := 4;
  v_cocl(5) := 5;

  dbms_sql.bind_array(v, 'coclient',v_cocl);
  dbms_sql.define_column(c => v,position => 1,column =>  v_c);
  v_rc := dbms_sql.execute(v);
  while dbms_sql.fetch_rows(v) <> 0 loop
    dbms_sql.column_value(c => v,position => 1,value => v_c);
    dbms_output.put_line(v_c);  
  end loop;
end;


Не работает. То есть в результате ничего нет. Надо видимо перечитывать про DBMS_SQL более внимательно. Хотя я не помню там подобных ограничений, но есть фраза про то что этот пакет полностью соответствует логике и стандартам OCI. А я про него, честно говоря, читал через строку. Видимо тут где-то собака и порылась.
Черт, придется видимо переписывать код.
21 янв 14, 21:29    [15449329]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

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

Смысл в том, что сначала динамически формируется список сущностей по которым надо вести SMS рассылку пользователям. Я прибегнул к пакету DBMS_SQL чтобы не тянуть лишних предикатов в запросы. Нативный динамический SQL не очень подходит так как заранее не известно сколько будет входных параметров. После того как список сущностей определен, я формирую SMS. Сделал при помощи табличной функции потому что одним запросом не так просто реализовать. А еще проблема в том что мне предстояло вызывать функцию заказчика, заранее неизвестную для выполнения макроподстановок в тексте сообщения, поэтому внутри функции динамический вызов заранее неизвестной функции, название которой берется из настройки системы. Поэтому я и преобразую к ref_cursor.

Видимо придется все переделать. Хотел как лучше, но знаний видать не хватило. Придется делать втупую. Вычислять все одним запросом, а для вызова функции заказчика вешать триггер. Прощай 3-4 млн смс за 1 минуту =(.
21 янв 14, 21:36    [15449339]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
Fangrem
Я прибегнул к пакету DBMS_SQL чтобы не тянуть лишних предикатов в запросы. Нативный динамический SQL не очень подходит так как заранее не известно сколько будет входных параметров.


Еще раз: Приведи определение типа v_filter_data.req_type.

SY.
21 янв 14, 23:58    [15449797]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
-2-
Member

Откуда:
Сообщений: 15330
Fangrem
Я прибегнул к пакету DBMS_SQL чтобы не тянуть лишних предикатов в запросы.
скорее всего, овчинка выделки не стоит.
Fangrem
Нативный динамический SQL не очень подходит так как заранее не известно сколько будет входных параметров.
это элементарно решается
Fangrem
но знаний видать не хватило.
просто не надо фантазировать на счет bind_array и in с коллекциями
22 янв 14, 00:08    [15449831]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
SY
Fangrem
Я прибегнул к пакету DBMS_SQL чтобы не тянуть лишних предикатов в запросы. Нативный динамический SQL не очень подходит так как заранее не известно сколько будет входных параметров.


Еще раз: Приведи определение типа v_filter_data.req_type.

SY.


SY
Fangrem
Я прибегнул к пакету DBMS_SQL чтобы не тянуть лишних предикатов в запросы. Нативный динамический SQL не очень подходит так как заранее не известно сколько будет входных параметров.


Еще раз: Приведи определение типа v_filter_data.req_type.

SY.


 type t_filter_data is record(
    departments      dbms_sql.Number_Table,
    legalentity      number,
    clientstatus     number,
    kaoffset         dbms_sql.Number_Table,
    paysystem        number,
    realbalance_from number,
    realbalance_to   number,
    biltype          dbms_sql.Number_Table,
    repperiod        number,
    billstatus       dbms_sql.Number_Table,
    havebillparcell  number,
    zero_bills       number,
    req_type         dbms_sql.Number_Table,
    reqcreate_start  date,
    reqcreate_to     date,
    reqstatus        dbms_sql.Number_Table,
    reqcreate_user   dbms_sql.Number_Table
    
    );

.....
v_filter_data   t_filter_data;
22 янв 14, 09:59    [15450544]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
-2-
Fangrem
Я прибегнул к пакету DBMS_SQL чтобы не тянуть лишних предикатов в запросы.
скорее всего, овчинка выделки не стоит.
Fangrem
Нативный динамический SQL не очень подходит так как заранее не известно сколько будет входных параметров.
это элементарно решается
Fangrem
но знаний видать не хватило.
просто не надо фантазировать на счет bind_array и in с коллекциями

Ну я в итоге пока в доке не нашел описания особенностей работы bind_array.
Насчет того чтобы попробовать втащить все предикаты, можно попробовать. Но мой опыт подсказывает что это сильно уронить производительность запроса. Но возможно в данной конкретной ситуации все будет ок. Просто я не очень люблю запросы вида:
....
where (coclient =:a_coclient or a_coclient is null)
  and (corequesttype in (select * from table (v_filter_data.req_type)) or v_filter_data.req_type is null)
22 янв 14, 10:13    [15450615]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
Разобрался.

procedure bind_array(c in integer, name in varchar2, ...
-- n_tab, c_tab, d_tab, bl_tab, cl_tab, bf_tab
-- For array execute operations, where the user wishes to execute
-- the SQL statement multiple times without returning control to
-- the caller, a list of values can be bound to this variable. This
-- functionality is like the array execute feature of OCI, where a
-- list of values in a PLSQL index table can be inserted into a SQL
-- table with a single (parameterized) call to execute.


То есть запрос выполняется N раз, но результат возвращается только от последнего. То есть по сути bind_array будет работать только с DML.
Странно, что довольно важный вопрос, так плохо освещен в доке.
Придется переделывать логику, или втаскивать в табличную функцию еще одну переменную. Но тут уже наверняка проще будет сделать при помощи статики и втащить все предикаты.
22 янв 14, 11:23    [15451063]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Fangrem
Разобрался.

procedure bind_array(c in integer, name in varchar2, ...
-- n_tab, c_tab, d_tab, bl_tab, cl_tab, bf_tab
-- For array execute operations, where the user wishes to execute
-- the SQL statement multiple times without returning control to
-- the caller, a list of values can be bound to this variable. This
-- functionality is like the array execute feature of OCI, where a
-- list of values in a PLSQL index table can be inserted into a SQL
-- table with a single (parameterized) call to execute.


То есть запрос выполняется N раз, но результат возвращается только от последнего. То есть по сути bind_array будет работать только с DML.
Странно, что довольно важный вопрос, так плохо освещен в доке.
Придется переделывать логику, или втаскивать в табличную функцию еще одну переменную. Но тут уже наверняка проще будет сделать при помощи статики и втащить все предикаты.

заместо чем дурью маяться, открыли бы, что ли, для себя gtt
22 янв 14, 12:31    [15451612]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
Fangrem
Member

Откуда:
Сообщений: 56
И в чем цимус использования gtt в данной ситуации?
23 янв 14, 20:12    [15459311]     Ответить | Цитировать Сообщить модератору
 Re: To_refcursor и bind_array.  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Fangrem
И в чем цимус использования gtt в данной ситуации?

(даже если оставить в стороне перформанс) - в вашем примере совсем нет оснований для использования динамического эскуэля
23 янв 14, 20:26    [15459366]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить