Re: 10g Bind peeking causes multiple cursors for same query with perl DBD::Oracle

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Fri, 2 Dec 2005 07:22:06 -0800
Message-ID: <gMydnStf5u-q-w3eRVn-iQ@comcast.com>

"peter" <p_msantos_at_yahoo.com> wrote in message news:1133467903.157694.314450_at_f14g2000cwb.googlegroups.com...
> Dear all,
> 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 are 2000 character bytes. 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 the 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 and DBD::Oracle
>
> TEST TABLE
> ===========
> CREATE TABLE PETER_T1 (column1 varchar2(10),column2
> varchar2(10),date_left date);
>
> insert into PETER_1 values('Fname','Lname',to_date('20051130 153015',
> 'YYYYMMDD HH24MISS');
> insert into PETER_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 /* TESTING */ column1,column2\nfrom PETER_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);
>
> $sth->bind_param(":p1",$date1);
> $sth->bind_param(":p2",$date2);
> $sth->execute();
>
> print "COLUMN1\tCOLUMN2\n";
> print "=======\t=======\n";
> $sth->bind_columns( \$column1, \$column2);
> while( $sth->fetch() ) {
> print "$column1\t$column2\n";
> }
> $dbh->disconnect;
> exit;
>
> So what I've noticed is that in the above statement, the prepare
> creates cursor1, while
> the execute() creates the 2nd cursor.
>
> The query returns only 2 rows, and here is what's in the database.
>
> << V$SQL >>
> HASH_VALUE|SQL_ID | ROWSP| EXECS| CHILD#|CHILD_ADDRESS |MODULE
>
> ----------|-------------|------|------|--------|----------------|---------

---

> 3546516858|2jwh16z9q73bu| 0| 0|
> 0|0000040144BD2140|dbi_bind_select_at_dev11
> 3546516858|2jwh16z9q73bu| 2| 1|
> 1|000004012CECAAF0|dbi_bind_select_at_dev11
>
> 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|N|F|A|I|T| D|L|D|B|P
> ----------------|------|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|
-|-|-|-|-|-|-|-|-|-|-|-|-|-
> 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|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|N|N|N|N|N|N|N|N| N|N
>
>
> SQL>select * from v$sql_bind_metadata where address in
> ('0000040144BD2140','000004012CECAAF0');
>
> 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
>
> The above information tells me that the 2 cursors where not shared
> because of a user_bind_peek mismatch. When I turn off bind peeking it
> then gives me a mismatch on "bind_mismatch".
>
> Does anyone know of a parameter to turn off whatever to not do the
> "bind_mismatch".
> Any ideas would be greatly appreciated....
>
> --peter
>

Can you reproduce with sqlplus? Maybe it is a problem with the way perl makes calls to Oracle.
Jim Received on Fri Dec 02 2005 - 09:22:06 CST