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

From: peter <p_msantos_at_yahoo.com>
Date: 1 Dec 2005 12:11:43 -0800
Message-ID: <1133467903.157694.314450@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 Received on Thu Dec 01 2005 - 14:11:43 CST