RE: Oracle 10g and DBD::Oracle

Peter,

Can you repeat this same issue in SQL*Plus?

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Peter Santos [mailto:psantos@cheetahmail.com]=20
Sent: Thursday, December 08, 2005 9:00 AM
To: dbi-users@perl.org
Subject: re: Oracle 10g and DBD::Oracle=20


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=20
perl script. =20
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=20
queries and generates 2 cursors...
where the first cursor has executions=3D0.

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=20
(version 1.6)

**

*TEST TABLE *
===========3D
CREATE TABLE TEST_T1 (column1 varchar2(10),
column2 varchar2(10),
date_left date);

insert into TEST_1 values('Fname','Lname',to_date('20051130=20
153015','YYYYMMDD HH24MISS');
insert into TEST_1 values('Fname','Lname',to_date('20051130=20
203015','YYYYMMDD HH24MISS');

*PERL PROGRAM *
==============
#!/bin/perl
use DBI;
my $dbh =3D DBI->connect( 'dbi:Oracle:;[mydb]', '[username]',
'[password]',)
|| die "Database connection not made: $DBI::errstr";

my $date1 =3D "20051130";
my $date2 =3D "20051130";

my $sql =3D qq{SELECT column1,column2\nfrom TEST_T1\n};
$sql .=3D qq{WHERE\ndate_left between to_date( :p1 , 'YYYYMMDD') =
and
};
$sql .=3D qq{to_date( :p2 || ' 235959', 'YYYYMMDD HH24MISS')};

*# prepare select query *
my $sth =3D $dbh->prepare($sql); *# generates cursor1*

$sth->bind_param(":p1",$date1);
$sth->bind_param(":p2",$date2);
$sth->execute(); *# generates cursor2*

print "COLUMN1\tCOLUMN2\n";
print "=======3D\t=======3D\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 =20
|0000040144BD2140|perl@[hostname]
3546516858|2jwh16z9q73bu| 2| 1|1 =20
|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=3D'2jwh16z9q73bu';

CHILD_ADDRESS =20
|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 =20
|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 =20
|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



This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: Oracle 10g and DBD::Oracle

No this issue is not repeatable in SqlPlus.

Here is the test case for sqlplus.

First I updated the table to include 3 records with distinct date_left
columns. Just to make it easier to test.

DATE_LEFT
===========
11/28/05 12:13
11/29/05 15:30
11/30/05 19:22

Then I setup my test script which I will show you shortly, but I also
executed the following query to give me the total parse and hard parses
before and after my query block.

QUERY1:
select decode(statistic#,299,'Total parse count (299)',
300,'Hard parse count (300)',statistic#)statistic#,
value from v$mystat where statistic# in (299,300)

So QUERY1 was executed before and after my little script...

Here is the actual sql block.

DECLARE
col1 varchar2(10);
col2 varchar2(10);
BEGIN
for x in (select to_char(date_left,'YYYYMMDD')dt from PETER_T1)
LOOP
SELECT /*SQL-TEST4 */ column1,column2 INTO col1,col2
FROM PETER_T1
WHERE DATE_LEFT BETWEEN TO_DATE(x.dt, 'YYYYMMDD') AND
TO_DATE(x.dt || ' 235959', 'YYYYMMDD HH24MISS');
END LOOP;
END;
/
The above SQL Block creates only 1 cursor not 2. I also confirmed by
running QUERY1 before and after my block that the first time I have a
hard parse, but subsequent times, it's a soft parse.
The parsing behavior is the same with perl ... meaning the 2nd time the
query is executed it's a soft parse, but there is that 1 extra cursor
from the prepare() in perl, but not via sqlplus.

--peter



Reidy, Ron wrote:

>Peter,
>
>Can you repeat this same issue in SQL*Plus?
>
>--
>Ron Reidy
>Lead DBA
>Array BioPharma, Inc.
>
>-----Original Message-----
>From: Peter Santos [mailto:psantos@cheetahmail.com]
>Sent: Thursday, December 08, 2005 9:00 AM
>To: dbi-users@perl.org
>Subject: re: Oracle 10g and DBD::Oracle
>
>
>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
>
>
>
>This electronic message transmission is a PRIVATE communication which contains
>information which may be confidential or privileged. The information is intended
>to be for the use of the individual or entity named above. If you are not the
>intended recipient, please be aware that any disclosure, copying, distribution
>or use of the contents of this information is prohibited. Please notify the
>sender of the delivery error by replying to this message, or notify us by
>telephone (877-633-2436, ext. 0), and then delete it from your system.
>
>
>
>
>