It appears that you are seeing the parse done by DBD::Oracle
to describe the table.
Look at the DBI docs for ora_check_sql. Turn it off and
the extra parse should go away.
Jared
On Thu, 2005-12-08 at 11:00 -0500, Peter Santos wrote:
> Dear users,
> I'm hoping to get some insight into why oracle creates 2 cursors for
> the same sql query when I execute a SELECT statement via my small little
> perl script.
> Here is what is happening .. My query uses 2 bind
> variables and when it is prepared oracle generates 1 cursor for my
> query.
>
> When I execute that prepared statement with actual input bind variables,
> it generates a 2nd cursor for the same query.
> The problem here is that during the prepare, oracle identifies my 2 bind
> parameters as 2000 character bytes max.
> When I actually execute the query with the binds, it calculates the bind
> parameters as 32
> characters. So because of bind peeking it thinks it's 2 different
> queries and generates 2 cursors...
> where the first cursor has executions=0.
>
> I can turn off bind peeking with the parameter
> "_optim_peek_user_binds", but then instead of just having a cursor
> mismatch on user_bind_peeking I have a cursor mismatch on "bind_mismatch".
>
> Does anyone know how to get around this? This to me is a waste of
> shared pool resources..and we have lots of this happening in our
> database. I've asked oracle for help, but I'm getting nowhere...
>
> Here is the complete test case.
> My env: Solaris 8 - Oracle 10.1.0.4 DBI (version 1.48) and DBD::Oracle
> (version 1.6)
>
> **
>
> *TEST TABLE *
> ===========
> CREATE TABLE TEST_T1 (column1 varchar2(10),
> column2 varchar2(10),
> date_left date);
>
> insert into TEST_1 values('Fname','Lname',to_date('20051130
> 153015','YYYYMMDD HH24MISS');
> insert into TEST_1 values('Fname','Lname',to_date('20051130
> 203015','YYYYMMDD HH24MISS');
>
> *PERL PROGRAM *
> ==============
> #!/bin/perl
> use DBI;
> my $dbh = DBI->connect( 'dbi:Oracle:;[mydb]', '[username]', '[password]',)
> || die "Database connection not made: $DBI::errstr";
>
> my $date1 = "20051130";
> my $date2 = "20051130";
>
> my $sql = qq{SELECT column1,column2\nfrom TEST_T1\n};
> $sql .= qq{WHERE\ndate_left between to_date( :p1 , 'YYYYMMDD') and };
> $sql .= qq{to_date( :p2 || ' 235959', 'YYYYMMDD HH24MISS')};
>
> *# prepare select query *
> my $sth = $dbh->prepare($sql); *# generates cursor1*
>
> $sth->bind_param(":p1",$date1);
> $sth->bind_param(":p2",$date2);
> $sth->execute(); *# generates cursor2*
>
> print "COLUMN1\tCOLUMN2\n";
> print "=======\t=======\n";
> $sth->bind_columns( \$column1, \$column2);
> while( $sth->fetch() ) {
> print "$column1\t$column2\n";
>
> }
>
> $dbh->disconnect;
> exit;
>
> *Here is what's in the Oracle data dictionary*
>
> HASH_VALUE|SQL_ID | ROWSP| EXECS| CHILD#|CHILD_ADDRESS |MODULE
> ----------|-------------|------|------|--------|----------------|-----------------
> 3546516858|2jwh16z9q73bu| 0| 0|0
> |0000040144BD2140|[EMAIL PROTECTED]
> 3546516858|2jwh16z9q73bu| 2| 1|1
> |000004012CECAAF0|[EMAIL PROTECTED]
>
> ** So, 1 query and 2 representations of that same query in the database.
> The execute
> did not shared the cursor prepared by the prepare() call.
>
> SQL > select * from v$sql_shared_cursor where sql_id='2jwh16z9q73bu';
>
> CHILD_ADDRESS
> |CHILD#|U|S|O|O|S|L|S|E|B|P|I|S|T|A|B|D|L|T|R|I|I|R|L|I|O|S|M|*U*|T|
> ----------------|------|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|
> 0000040144BD2140|0
> |N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|
> 000004012CECAAF0|1
> |N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|N|*Y*|N|
>
> SQL>select * from v$sql_bind_metadata where address in
> ('0000040144BD2140','000004012CECAAF0');
>
> *** This shows us that cursor1 had different bind variable metadata than
> cursor2
> which explains why they weren't shared.
>
> ADDRESS | POSITION| DATATYPE|MAX_LENGTH| ARRAY_LEN|BIND_NAME
> ----------------|----------|----------|----------|----------|---------
> 0000040144BD2140| 1| 1| 2000| 0|P1
> 0000040144BD2140| 2| 1| 2000| 0|P2
> 000004012CECAAF0| 1| 1| 32| 0|P1
> 000004012CECAAF0| 2| 1| 32| 0|P2
>
>
