re: Oracle 10g and DBD::Oracle
am 08.12.2005 17:00:19 von psantos--------------040405000901020701020403
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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|perl@[hostname]
3546516858|2jwh16z9q73bu| 2| 1|1
|000004012CECAAF0|perl@[hostname]
** 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
--------------040405000901020701020403--