When I use DBD::Oracle 1.12 with DBI 1.30 (I also saw this on
1.06/1.15) I see two parse_calls for every execute on all selects.
Update/insert/delete just have 1 parse/execute. I have a simple test
script.
#!/usr/local/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect("dbi:Oracle:db", "user/pass");
$dbh->trace(8);
my $sth = $dbh->prepare("SELECT 1 FROM dual");
$sth->execute();
SQL> SELECT parse_calls, executions FROM v$sqlarea WHERE sql_text = 'SELECT 1 FROM
dual';
PARSE_CALLS EXECUTIONS
----------- ----------
16 8
Each time I run the script, the parse goes up by two and the
executions by one. If I put a sleep before the execute, I can see that
prepare is doing one parse, and execute is doing both a parse and an
execute. I also tried just doing a selectall_arrayref and it also has
two parses. Any ideas why the extra parse?
Setup:
perl 5.6.1
Oracle client 8.1.7.0.0 on Linux
Oracle server 8.1.7.3.0 on Sparc Solaris
Here is the trace
DBI::db=HASH(0x817c820) trace level set to 8 in DBI 1.30-nothread
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x82044ac)~0x817c820 'SELECT 1 FROM
dual')
dbih_setup_handle(DBI::st=HASH(0x8175cc0)=>DBI::st=HASH(0x8175d08),
DBD::Oracle::st, 8175ccc, Null!)
dbih_make_com(DBI::db=HASH(0x817c820), DBD::Oracle::st, 208) thr#(nil)
dbih_setup_attrib(DBI::st=HASH(0x8175d08), Err, DBI::db=HASH(0x817c820))
SCALAR(0x817c67c) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8175d08), State, DBI::db=HASH(0x817c820))
SCALAR(0x81a7e14) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8175d08), Errstr, DBI::db=HASH(0x817c820))
SCALAR(0x817c664) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8175d08), Debug, DBI::db=HASH(0x817c820)) 8
(already defined)
dbih_setup_attrib(DBI::st=HASH(0x8175d08), FetchHashKeyName,
DBI::db=HASH(0x817c820)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x8175d08), HandleError, DBI::db=HASH(0x817c820))
undef (not defined)
OCIHandleAlloc(0x820f120,0x8236770,OCI_HTYPE_STMT,0,(nil))=SUCCESS
OCIStmtPrepare(0x8235030,0x821e2dc,'SELECT 1 FROM dual',18,1,0)=SUCCESS
OCIAttrGet(0x8235030,OCI_HTYPE_STMT,0x8236774,(nil),24,0x821e2dc)=SUCCESS
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
OCIStmtExecute(0x821e0b4,0x8235030,0x821e2dc,0,0,(nil),(nil),16)=SUCCESS
OCIAttrGet(0x8235030,OCI_HTYPE_STMT,0xbffff14c,(nil),18,0x821e2dc)=SUCCESS
OCIParamGet(0x8235030,4,0x821e2dc,0x8236650,1)=SUCCESS
OCIAttrGet(0x8234778,OCI_DTYPE_PARAM,0x8236666,(nil),2,0x821e2dc)=SUCCESS
OCIAttrGet(0x8234778,OCI_DTYPE_PARAM,0x8236664,(nil),1,0x821e2dc)=SUCCESS
OCIAttrGet(0x8234778,OCI_DTYPE_PARAM,0x8236668,(nil),5,0x821e2dc)=SUCCESS
OCIAttrGet(0x8234778,OCI_DTYPE_PARAM,0x823666a,(nil),6,0x821e2dc)=SUCCESS
OCIAttrGet(0x8234778,OCI_DTYPE_PARAM,0x823666b,(nil),7,0x821e2dc)=SUCCESS
OCIAttrGet(0x8234778,OCI_DTYPE_PARAM,0x8236678,0xbffff148,4,0x821e2dc)=SUCCESS
fbh 1: '1' NULLable, otype 2-> 5, dbsize 2/134, p0.s0
OCIAttrSet(0x8235030,OCI_HTYPE_STMT,0xbffff144,4,11,0x821e2dc)=SUCCESS
OCIDefineByPos(0x8235030,0x8236654,0x821e2dc,1,0x82367c8,134,5,0x8236e48,0x8236858,0x8236868,0)=SUCCESS
dbd_describe'd 1 columns (row bytes: 2 max, 4 est avg, cache: 858)
<- prepare= DBI::st=HASH(0x8175cc0) at ./oracle_parse.pl line 8
-> execute for DBD::Oracle::st (DBI::st=HASH(0x8175cc0)~0x8175d08)
dbd_st_execute SELECT (out0, lob0)...
OCIStmtExecute(0x821e0b4,0x8235030,0x821e2dc,0,0,(nil),(nil),0)=SUCCESS
OCIAttrGet(0x8235030,OCI_HTYPE_STMT,0xbffff3d6,(nil),10,0x821e2dc)=SUCCESS
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at ./oracle_parse.pl line 9
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x8175d08)~INNER)
OCIStmtFetch(0x8235030,0x821e2dc,0,2,0)=SUCCESS
OCIHandleFree(0x8235030,OCI_HTYPE_STMT)=SUCCESS
<- DESTROY= undef
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x817c820)~INNER)
OCISessionEnd(0x821e0b4,0x821e2dc,0x8235494,0)=SUCCESS
OCIServerDetach(0x821e120,0x821e2dc,0)=SUCCESS
OCIHandleFree(0x8235494,OCI_HTYPE_SESSION)=SUCCESS
OCIHandleFree(0x821e120,OCI_HTYPE_SERVER)=SUCCESS
OCIHandleFree(0x821e0b4,OCI_HTYPE_SVCCTX)=SUCCESS
OCIHandleFree(0x821e2dc,OCI_HTYPE_ERROR)=SUCCESS
<- DESTROY= undef
