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
