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

From: peter <p_msantos_at_yahoo.com>
Date: 2 Dec 2005 09:22:16 -0800
Message-ID: <1133544136.827546.136280@g47g2000cwa.googlegroups.com>

>From my testing it's not the same in 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 Received on Fri Dec 02 2005 - 11:22:16 CST