RE: Oracle 10g and DBD::Oracle

This does not look the same. Where are the bind variables?

-----Original Message-----
From: Peter Santos [mailto:psantos@cheetahmail.com]=20
Sent: Friday, December 09, 2005 7:32 AM
To: Reidy, Ron
Cc: dbi-users@perl.org
Subject: 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
===========3D
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=20
>
>
>Dear users,
>I'm hoping to get some insight into why oracle creates 2 cursors for=20
>the same sql query when I execute a SELECT statement via my small=20
>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=20
>variables,
>
>it generates a 2nd cursor for the same query.
>The problem here is that during the prepare, oracle identifies my 2=20
>bind
>
>parameters as 2000 character bytes max.
>When I actually execute the query with the binds, it calculates the=20
>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=3D0.
>
>I can turn off bind peeking with the parameter=20
>"_optim_peek_user_binds", but then instead of just having a cursor=20
>mismatch on user_bind_peeking I have a cursor mismatch on=20
>"bind_mismatch".
>
>Does anyone know how to get around this? This to me is a waste of=20
>shared pool resources..and we have lots of this happening in our=20
>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 *
>===========3D
>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=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=20
>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=20
>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=20
>contains information which may be confidential or privileged. The=20
>information is intended to be for the use of the individual or entity=20
>named above. If you are not the intended recipient, please be aware=20
>that any disclosure, copying, distribution or use of the contents of=20
>this information is prohibited. Please notify the sender of the=20
>delivery error by replying to this message, or notify us by telephone=20
>(877-633-2436, ext. 0), and then delete it from your system.
>
>
>
> =20
>

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

--------------010508030903030501090107
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

The bind variable is x.dt which will contain 3 different values for
every iteration of the loop.
I'll be more explicit..

Here is what I have in the database:

--select to_char(date_left,'YYYYMMDD')dt from test_t1;
20051129
20051130
20051128

Now for the sql script...

-<<--START-OF-SCRIPT-->>-

var p1 varchar2(100);
var p2 varchar2(100);
exec :p1:='20051128'; :p2:='20051128';

SELECT /*BIND-TEST */ column1,column2,date_left
FROM TEST_T1
WHERE DATE_LEFT BETWEEN TO_DATE(:p1, 'YYYYMMDD') AND TO_DATE(:p2 || '
235959', 'YYYYMMDD HH24MISS')
/
exec :p1:='20051129'; :p2:='20051129';
SELECT /*BIND-TEST */ column1,column2,date_left
FROM TEST_T1
WHERE DATE_LEFT BETWEEN TO_DATE(:p1, 'YYYYMMDD') AND TO_DATE(:p2 || '
235959', 'YYYYMMDD HH24MISS')
/
exec :p1:='20051130'; :p2:='20051130';
SELECT /*BIND-TEST */ column1,column2,date_left
FROM TEST_T1
WHERE DATE_LEFT BETWEEN TO_DATE(:p1, 'YYYYMMDD') AND TO_DATE(:p2 || '
235959', 'YYYYMMDD HH24MISS')
/
--<<-END-OF-SCRIPT-->>-

So before each SELECT I set the bind variables to a new value. This
gives me 1 hard parse and 2 soft parses
and ONLY 1 cursor in the database (V$SQL).

HTH

--peter







Reidy, Ron wrote:

>This does not look the same. Where are the bind variables?
>
>-----Original Message-----
>From: Peter Santos [mailto:psantos@cheetahmail.com]
>Sent: Friday, December 09, 2005 7:32 AM
>To: Reidy, Ron
>Cc: dbi-users@perl.org
>Subject: 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.
>>
>>
>>
>>
>>
>>
>>
>
>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.
>
>
>
>
>

--------------010508030903030501090107--