Re: (?) bind variables not helping at all..

In article <5fff533a.0208261300.61bfbc76_at_posting.google.com>, dmclaude_at_yahoo.com says...
>
>im just learning oracle.. (newbie pretty much).. and i was told about
>the great and awesome wonders of bind variables.. then how come im
>getting this result (from trace file)
>
>----------------->
>
>SELECT ACNO,CHKNO,IPPRECDATE,CUSTID,ASOFDATE,PAIDDATE,AMT,HASDECISION,
> ADDITIONALINFO,CPCS,BELOWTHRESH,DECISIONTN
>FROM
> PNI WHERE HASDECISION LIKE :b1 AND ACNO LIKE :b2 AND CHKNO LIKE :b3
> AND IPPRECDATE BETWEEN :b4 AND :b5 ORDER BY IPPRECDATE,ACNO,CHKNO,
> HASDEC
>
>
>call count cpu elapsed disk query current rows
>------- ------ ----- -------- ------- ------- -------- ----------
>Parse 4 0.00 0.01 0 0 0 0
>Execute 4 0.00 0.00 0 0 0 0
>Fetch 486 0.46 0.51 117 463 18 4835
>------- ------ ----- -------- ------- ------- -------- ----------
>total 494 0.46 0.52 117 463 18 4835
>
>
>Misses in library cache during parse: 0
>Optimizer goal: CHOOSE
>Parsing user id: 84 (recursive depth: 1)
>
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 87 SORT ORDER BY
> 87 TABLE ACCESS FULL PNI
>---------------->
>
>
>am i doing something drastically wrong? i mean i have been using bind
>variables.. but im still getting a parse/execute ratio of 1 (which i
>am told, is pretty dam' horrendous)..
>
>
>the actual stored procedure follows..
>
>
>help me out somebody...
>-tone
>
>
>-------------store procedure --------------------->
>
>CREATE OR REPLACE PACKAGE BODY testSearch AS
>
>procedure Search ( p_in_acno IN NUMBER,
> p_in_chkno IN NUMBER,
> p_in_fromdate IN DATE,
> p_in_todate IN DATE,
> p_in_hasdec IN VARCHAR,
> p_out_returncode OUT NUMBER,
> p_out_returncursor OUT RETURNCURSOR ) AS
>
>v_hasdec varchar2(1);
>v_acno varchar(13);
>v_chkno varchar(10);
>v_startdate date;
>v_enddate date;
>
>
>
> BEGIN
>
> if (p_in_hasdec IS NOT NULL) then
> v_hasdec := p_in_hasdec;
> else
> v_hasdec := '%';
> end if;
>
>
> if (p_in_acno IS NOT NULL) then
> v_acno := p_in_acno;
> else
> v_acno := '%';
> end if;
>
>
> if (p_in_chkno IS NOT NULL) then
> v_chkno := p_in_chkno;
> else
> v_chkno := '%';
> end if;
>
>
> if (p_in_fromdate IS NOT NULL) then
> v_startdate := p_in_fromdate;
> else
> v_startdate := TO_DATE('January 15, 2000', 'Month dd, YYYY');
> end if;
>
>
> if (p_in_todate IS NOT NULL) then
> v_enddate := p_in_todate;
> else
> v_enddate := SYSDATE+1;
> end if;
>
>
> p_out_returncode := 0;
>
> open p_out_returncursor for SELECT
> acno ,
> chkno ,
> ipprecdate ,
> custid ,
> asofdate ,
> paiddate ,
> amt ,
> hasdec ,
> additionalinfo ,
> cpcs ,
> belowthresh ,
> decisiontn
> FROM test
> WHERE
> hasdec like v_hasdec
> AND acno like v_acno
> AND chkno like v_chkno
> AND ipprecdate between
> TRUNC(v_startdate)
> and
> TRUNC(v_enddate)
> order by ipprecdate, acno, chkno, hasdec;
>
>
> exception
> WHEN OTHERS THEN p_out_returncode := 8;
>
> END Search;
>
>END testSearch;
>/
>-----------------end stored proc------------->

ref cursors are *always* parsed.

they have to be, they cannot be cached like other static sql in a stored procedure.

The reason: testSearch.search could be called 5 times before the first row is fetched from the first returned ref cursor. We have no idea how many ref cursor instances are needed -- hence, we cannot "cache them". We don't control them -- the client does.

So, everything looks OK here (except your exception handling -- return codes? really? just use exceptions. The caller will get this funky number 8 and have NO CLUE what the real error is!)

And as long as you are going down the ref cursor path, might as well make it dynamic and build the query -- it'll be more performant in your case (instead of searching on '%' when a parameter isn't passed in) See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279

for an example

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp