DBD::Oracle insert speed

--000000000000b1b9fd05b880ca69
Content-Type: text/plain; charset="UTF-8"

Hi,
I have already asked this question at perlmonks.org but I did not get
proper answer.

Module DBD::Oracle <http://search.cpan.org/perldoc?DBD%3A%3AOracle> has
serious performance degradation at inserting rows. Except execute_array (or
bind_param_array), all other insert solutions are pretty slow (eg. bind by
name). DBD::Oracle use its own wrapper (dbimp.c, oci8.c) and seem to be
implemented in an ineffective way. Comparing to otlv4 (
http://otl.sourceforge.net/otl3_intro.htm) which is a wrapper over Oracle's
OCI interface written in C++ the basic
http://otl.sourceforge.net/otl3_ex10.htm is much (~10 times) faster than
DBD::Oracle row based insertion. DBD::Oracle implements DATA_AT_EXEC mode
with OCIBindDynamic for row based insertion (not array) as can be seen
below and this block is repeated for each value.

....
dbd_bind_ph(1): bind :id <== '1774684594' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph() (1): rebinding :id as '17746...' (not-utf8, ftype 1
(VARCHAR), csid 0, csform 0(0), inout 0)
dbd_rebind_ph_char() (1): bind :id <== '17746...' (size 10/12/0, ptype
3(VARCHAR), otype 1 )
Changing maxlen to 12
dbd_rebind_ph_char() (2): bind :id <== '17746...' (size 10/12, otype
1(VARCHAR), indp 0, at_exec 1)
      bind :id as ftype 1 (VARCHAR)
    OCIBindByName(163ad08,1675e58,1614d20,":id",placeh_len=3,value_p=1675a00,value_sz=12,dty=1,indp=1675e78,alenp=0,rcodep=1675e70,maxarr_len=0,curelep=0
(*=0),mode=DATA_AT_EXEC,2)=SUCCESS
    OCIBindDynamic(163a970,1614d20,1675e20,7fdf910dacc0,1675e20,7fdf910daf30)=SUCCESS
    OCIAttrGet(163a970,OCI_HTYPE_BIND,1675e3c,0,31,1614d20)=SUCCESS
dbd_rebind_ph(): bind :id <== '17746...' (in, not-utf8, csid
873->0->873, ftype 1 (VARCHAR), csform 0(0)->0(0), maxlen 12,
maxdata_size 0)
    OCIAttrSet(163a970,OCI_HTYPE_BIND, 7ffda0f7f960,0,Attr=31,1614d20)=SUCCESS
....
[download] <https://perlmonks.org/?displaytype=displaycode;abspart=1;part=1;node_id=11126161>

Is this design intentional? OCI documentation said that OCIBindDynamic can
be useful at working with big data items
https://web.stanford.edu/dept/itss/docs/oracle/10gR2/appdev.102/b14250/oci05bnd.htm#i427753.
C++ code using otlv4 can process blob/clob data and it does not use
OCIBindDynamic at all. So, why DBD::Oracle implemented this way?

Thanks in advance

--000000000000b1b9fd05b880ca69
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi, <br></div><div>I have already asked this question=
 at <a href=3D"http://perlmonks.org" target=3D"_blank">perlmonks.org</a> bu=
t I did not get proper answer.<br></div><p>
Module <a href=3D"http://search.cpan.org/perldoc?DBD%3A%3AOracle" target=3D=
"_blank">DBD::Oracle</a> has serious performance degradation at inserting r=
ows. Except <tt>execute_array</tt> (or <tt>bind_param_array</tt>), all othe=
r insert solutions are pretty slow (eg. bind by name). DBD::Oracle use its =
own wrapper (dbimp.c, oci8.c) and seem to be implemented in an ineffective =
way. Comparing to otlv4 (<a href=3D"http://otl.sourceforge.net/otl3_intro.h=
tm" target=3D"_blank">http://otl.sourceforge.net/otl3_intro.htm</a>) which =
is a wrapper over Oracle&#39;s OCI interface written in C++ the basic <a hr=
ef=3D"http://otl.sourceforge.net/otl3_ex10.htm" target=3D"_blank">http://ot=
l.sourceforge.net/otl3_ex10.htm</a> is much (~10 times) faster than DBD::Or=
acle row based insertion. DBD::Oracle implements <tt>DATA_AT_EXEC</tt> mode=
 with <tt>OCIBindDynamic</tt> for row based insertion (not array) as can be=
 seen below and this block is repeated for each value.

</p><pre><div><tt><font size=3D"-1">...
dbd_bind_ph(1): bind :id &lt;=3D=3D &#39;1774684594&#39; (type 0 (DEFAULT (=
varchar)))
dbd_rebind_ph() (1): rebinding :id as &#39;17746...&#39; (not-utf8, ftype 1=
 (VARCHAR), csid 0, csform 0(0), inout 0)
dbd_rebind_ph_char() (1): bind :id &lt;=3D=3D &#39;17746...&#39; (size 10/1=
2/0, ptype 3(VARCHAR), otype 1 )
Changing maxlen to 12
dbd_rebind_ph_char() (2): bind :id &lt;=3D=3D &#39;17746...&#39; (size 10/1=
2, otype 1(VARCHAR), indp 0, at_exec 1)
      bind :id as ftype 1 (VARCHAR)
    OCIBindByName(163ad08,1675e58,1614d20,&quot;:id&quot;,placeh_len=3D3,va=
lue_p=3D1675a00,value_sz=3D12,dty=3D1,indp=3D1675e78,alenp=3D0,rcodep=3D167=
5e70,maxarr_len=3D0,curelep=3D0 (*=3D0),mode=3DDATA_AT_EXEC,2)=3DSUCCESS
    OCIBindDynamic(163a970,1614d20,1675e20,7fdf910dacc0,1675e20,7fdf910daf3=
0)=3DSUCCESS
    OCIAttrGet(163a970,OCI_HTYPE_BIND,1675e3c,0,31,1614d20)=3DSUCCESS
dbd_rebind_ph(): bind :id &lt;=3D=3D &#39;17746...&#39; (in, not-utf8, csid=
 873-&gt;0-&gt;873, ftype 1 (VARCHAR), csform 0(0)-&gt;0(0), maxlen 12, max=
data_size 0)
    OCIAttrSet(163a970,OCI_HTYPE_BIND, 7ffda0f7f960,0,Attr=3D31,1614d20)=3D=
SUCCESS
....
</font></tt></div><font size=3D"-1"><div><a href=3D"https://perlmonks.org/?=
displaytype=3Ddisplaycode;abspart=3D1;part=3D1;node_id=3D11126161" target=
=3D"_blank">[download]</a></div></font></pre>

Is this design intentional? OCI documentation said that <tt>OCIBindDynamic<=
/tt> can be useful at working with big data items <a href=3D"https://web.st=
anford.edu/dept/itss/docs/oracle/10gR2/appdev.102/b14250/oci05bnd.htm#i4277=
53" target=3D"_blank">https://web.stanford.edu/dept/itss/docs/oracle/10gR2/=
appdev.102/b14250/oci05bnd.htm#i427753</a>. C++ code using otlv4 can proces=
s blob/clob data and it does not use <tt>OCIBindDynamic</tt> at all. So, wh=
y DBD::Oracle implemented this way?
<p>
Thanks in advance
</p></div>

--000000000000b1b9fd05b880ca69--
0
hauptadler
1/10/2021 12:22:48 AM
perl.dbi.users 11100 articles. 1 followers. Follow

1 Replies
9 Views

Similar Articles

[PageSpeed] 47

--------------EC940BF5256E465B53D73711
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit


On 10/1/21 11:22 am, Peter Meszaros wrote:
> Hi,
> I have already asked this question at perlmonks.org 
> <https://urldefense.com/v3/__http://perlmonks.org__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpZEzbH1Ow$> but I 
> did not get proper answer.
>
> Module DBD::Oracle 
> <https://urldefense.com/v3/__http://search.cpan.org/perldoc?DBD*3A*3AOracle__;JSU!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpZHuCdBzA$> 
> has serious performance degradation at inserting rows. Except execute_array (or bind_param_array), all other insert solutions are pretty slow (eg. 
> bind by name). DBD::Oracle use its own wrapper (dbimp.c, oci8.c) and seem to be implemented in an ineffective way. Comparing to otlv4 
> (http://otl.sourceforge.net/otl3_intro.htm 
> <https://urldefense.com/v3/__http://otl.sourceforge.net/otl3_intro.htm__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpYvQ6Ozwg$>) 
> which is a wrapper over Oracle's OCI interface written in C++ the basic http://otl.sourceforge.net/otl3_ex10.htm 
> <https://urldefense.com/v3/__http://otl.sourceforge.net/otl3_ex10.htm__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpaGeVTYiQ$> 
> is much (~10 times) faster than DBD::Oracle row based insertion. DBD::Oracle implements DATA_AT_EXEC mode with OCIBindDynamic for row based 
> insertion (not array) as can be seen below and this block is repeated for each value.
>
> ... dbd_bind_ph(1): bind :id <== '1774684594' (type 0 (DEFAULT (varchar))) dbd_rebind_ph() (1): rebinding :id as '17746...' (not-utf8, ftype 1 
> (VARCHAR), csid 0, csform 0(0), inout 0) dbd_rebind_ph_char() (1): bind :id <== '17746...' (size 10/12/0, ptype 3(VARCHAR), otype 1 ) Changing 
> maxlen to 12 dbd_rebind_ph_char() (2): bind :id <== '17746...' (size 10/12, otype 1(VARCHAR), indp 0, at_exec 1) bind :id as ftype 1 (VARCHAR) 
> OCIBindByName(163ad08,1675e58,1614d20,":id",placeh_len=3,value_p=1675a00,value_sz=12,dty=1,indp=1675e78,alenp=0,rcodep=1675e70,maxarr_len=0,curelep=0 
> (*=0),mode=DATA_AT_EXEC,2)=SUCCESS OCIBindDynamic(163a970,1614d20,1675e20,7fdf910dacc0,1675e20,7fdf910daf30)=SUCCESS 
> OCIAttrGet(163a970,OCI_HTYPE_BIND,1675e3c,0,31,1614d20)=SUCCESS dbd_rebind_ph(): bind :id <== '17746...' (in, not-utf8, csid 873->0->873, ftype 1 
> (VARCHAR), csform 0(0)->0(0), maxlen 12, maxdata_size 0) OCIAttrSet(163a970,OCI_HTYPE_BIND, 7ffda0f7f960,0,Attr=31,1614d20)=SUCCESS ...
> [download] 
> <https://urldefense.com/v3/__https://perlmonks.org/?displaytype=displaycode;abspart=1;part=1;node_id=11126161__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpbjTJWXvg$>
> Is this design intentional? OCI documentation said that OCIBindDynamic can be useful at working with big data items 
> https://web.stanford.edu/dept/itss/docs/oracle/10gR2/appdev.102/b14250/oci05bnd.htm#i427753 
> <https://urldefense.com/v3/__https://web.stanford.edu/dept/itss/docs/oracle/10gR2/appdev.102/b14250/oci05bnd.htm*i427753__;Iw!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpYj5lOcvQ$>. 
> C++ code using otlv4 can process blob/clob data and it does not use OCIBindDynamic at all. So, why DBD::Oracle implemented this way?
>
> Thanks in advance
>
In all language drivers for Oracle DB, the "Array DML" interface is significantly faster compared with a loop that executes individual statements.  
Reducing network round-trips is a big win. See 
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/binding-and-defining-in-oci.html#GUID-442E47AD-83A8-4959-9A73-DBFB7D3482A6

-- 
https://twitter.com/ghrd


--------------EC940BF5256E465B53D73711
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 10/1/21 11:22 am, Peter Meszaros
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CABMJSUOiWqtsW04UBtK=uOQoYOcvL127K2st8SYYVMcPLTnQOg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div>Hi, <br>
        </div>
        <div>I have already asked this question at <a
href="https://urldefense.com/v3/__http://perlmonks.org__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpZEzbH1Ow$"
            target="_blank" moz-do-not-send="true">perlmonks.org</a> but
          I did not get proper answer.<br>
        </div>
        <p>
          Module <a
href="https://urldefense.com/v3/__http://search.cpan.org/perldoc?DBD*3A*3AOracle__;JSU!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpZHuCdBzA$"
            target="_blank" moz-do-not-send="true">DBD::Oracle</a> has
          serious performance degradation at inserting rows. Except <tt>execute_array</tt>
          (or <tt>bind_param_array</tt>), all other insert solutions
          are pretty slow (eg. bind by name). DBD::Oracle use its own
          wrapper (dbimp.c, oci8.c) and seem to be implemented in an
          ineffective way. Comparing to otlv4 (<a
href="https://urldefense.com/v3/__http://otl.sourceforge.net/otl3_intro.htm__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpYvQ6Ozwg$"
            target="_blank" moz-do-not-send="true">http://otl.sourceforge.net/otl3_intro.htm</a>)
          which is a wrapper over Oracle's OCI interface written in C++
          the basic <a
href="https://urldefense.com/v3/__http://otl.sourceforge.net/otl3_ex10.htm__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpaGeVTYiQ$"
            target="_blank" moz-do-not-send="true">http://otl.sourceforge.net/otl3_ex10.htm</a>
          is much (~10 times) faster than DBD::Oracle row based
          insertion. DBD::Oracle implements <tt>DATA_AT_EXEC</tt> mode
          with <tt>OCIBindDynamic</tt> for row based insertion (not
          array) as can be seen below and this block is repeated for
          each value.
        </p>
        <pre><div><tt><font size="-1">...
dbd_bind_ph(1): bind :id &lt;== '1774684594' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph() (1): rebinding :id as '17746...' (not-utf8, ftype 1 (VARCHAR), csid 0, csform 0(0), inout 0)
dbd_rebind_ph_char() (1): bind :id &lt;== '17746...' (size 10/12/0, ptype 3(VARCHAR), otype 1 )
Changing maxlen to 12
dbd_rebind_ph_char() (2): bind :id &lt;== '17746...' (size 10/12, otype 1(VARCHAR), indp 0, at_exec 1)
      bind :id as ftype 1 (VARCHAR)
    OCIBindByName(163ad08,1675e58,1614d20,":id",placeh_len=3,value_p=1675a00,value_sz=12,dty=1,indp=1675e78,alenp=0,rcodep=1675e70,maxarr_len=0,curelep=0 (*=0),mode=DATA_AT_EXEC,2)=SUCCESS
    OCIBindDynamic(163a970,1614d20,1675e20,7fdf910dacc0,1675e20,7fdf910daf30)=SUCCESS
    OCIAttrGet(163a970,OCI_HTYPE_BIND,1675e3c,0,31,1614d20)=SUCCESS
dbd_rebind_ph(): bind :id &lt;== '17746...' (in, not-utf8, csid 873-&gt;0-&gt;873, ftype 1 (VARCHAR), csform 0(0)-&gt;0(0), maxlen 12, maxdata_size 0)
    OCIAttrSet(163a970,OCI_HTYPE_BIND, 7ffda0f7f960,0,Attr=31,1614d20)=SUCCESS
....
</font></tt></div><font size="-1"><div><a href="https://urldefense.com/v3/__https://perlmonks.org/?displaytype=displaycode;abspart=1;part=1;node_id=11126161__;!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpbjTJWXvg$" target="_blank" moz-do-not-send="true">[download]</a></div></font></pre>
        Is this design intentional? OCI documentation said that <tt>OCIBindDynamic</tt>
        can be useful at working with big data items <a
href="https://urldefense.com/v3/__https://web.stanford.edu/dept/itss/docs/oracle/10gR2/appdev.102/b14250/oci05bnd.htm*i427753__;Iw!!GqivPVa7Brio!OybtaVZUVoKl3qAbiNkFHQxZALNrn7ocQA5ft_OycHRoAtyyjCPfcy33Ob-PjpYj5lOcvQ$"
          target="_blank" moz-do-not-send="true">https://web.stanford.edu/dept/itss/docs/oracle/10gR2/appdev.102/b14250/oci05bnd.htm#i427753</a>.
        C++ code using otlv4 can process blob/clob data and it does not
        use <tt>OCIBindDynamic</tt> at all. So, why DBD::Oracle
        implemented this way?
        <p>
          Thanks in advance
        </p>
      </div>
    </blockquote>
    In all language drivers for Oracle DB, the "Array DML" interface is
    significantly faster compared with a loop that executes individual
    statements.  Reducing network round-trips is a big win. See
<a class="moz-txt-link-freetext" href="https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/binding-and-defining-in-oci.html#GUID-442E47AD-83A8-4959-9A73-DBFB7D3482A6">https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/binding-and-defining-in-oci.html#GUID-442E47AD-83A8-4959-9A73-DBFB7D3482A6</a><br>
    <pre class="moz-signature" cols="150">-- 
<a class="moz-txt-link-freetext" href="https://twitter.com/ghrd">https://twitter.com/ghrd</a>
</pre>
  </body>
</html>

--------------EC940BF5256E465B53D73711--
0
christopher
1/11/2021 12:19:03 AM
Reply: