Message-ID: <F001.00518FC0.20021212182628@fatcity.com>
Date: Thu, 12 Dec 2002 18:26:28 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Deshpande, Kirti" <kirti.deshpande@verizon.com>
Subject: RE: sql with bind variable in stored procedure doesnt use
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

It appears to be version dependent... 

Here is what I did in 9i R2 (and in 9i R1): 

SQL> create table emp (num number,sal number);
Table created.
SQL> insert into emp values(1,1);
1 row created. 
SQL> create or replace procedure t as
  2  total number:=0;
  3  cursor c1 is
  4  select sal from emp;
  5  begin
  6  for i in c1 loop
  7  total:=total+i.sal;
  8  dbms_output.put_line('total salary is $'||total);
  9  end loop;
 10  end;
 11  /
Procedure created.
SQL> set serveroutput on
SQL> @f2  
SQL> set echo on
SQL> exec outln_pkg.drop_by_cat('DEFAULT');
PL/SQL procedure successfully completed.
SQL> create outline on
  2  select sal from emp;
Outline created.
SQL> select used from dba_outlines;
USED
---------
UNUSED
SQL> alter session set QUERY_REWRITE_ENABLED=true;
Session altered.
SQL> alter session set STAR_TRANSFORMATION_ENABLED=true;
Session altered.
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> exec t;
total salary is $1
PL/SQL procedure successfully completed.
SQL> select used from dba_outlines;
USED
---------
USED
SQL> exec outln_pkg.drop_by_cat('DEFAULT');
PL/SQL procedure successfully completed.
SQL> select used from dba_outlines;
no rows selected
SQL> create outline on
  2  select sal from emp;
Outline created.
SQL> select sal from emp;
       SAL
----------
         1
SQL> select used from dba_outlines;
USED
---------
USED
SQL> 

================
But in 8.1.7.4:
================
SQL> @f2
SQL> set echo on
SQL> exec outln_pkg.drop_by_cat('DEFAULT');
PL/SQL procedure successfully completed.
SQL> create outline on
  2  select sal from emp;
Outline created.
SQL> select used from dba_outlines;
USED
---------
UNUSED
SQL> alter session set QUERY_REWRITE_ENABLED=true;
Session altered.
SQL> alter session set STAR_TRANSFORMATION_ENABLED=true;
Session altered.
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> exec t;
total salary is $1
PL/SQL procedure successfully completed.
SQL> select used from dba_outlines;
USED
---------
UNUSED
SQL> exec outln_pkg.drop_by_cat('DEFAULT');
PL/SQL procedure successfully completed.
SQL> select used from dba_outlines;
no rows selected
SQL> create outline on
  2  select sal from emp;
Outline created.
SQL> select sal from emp;
       SAL
----------
         1
SQL> select used from dba_outlines;
USED
---------
USED
SQL> 


- Kirti 

-----Original Message-----
Sent: Thursday, December 12, 2002 2:14 PM
To: Multiple recipients of list ORACLE-L
stored


Shaleen,
 
I have not been able to dig deeper (yet) into this. However the following
_may_ be why this is not working: I believe that the stored outline is
matched with an about-to-be-executed SQL using the Hash value of the SQL.
The hash value is computed by some mumbo-jumbo based on the actual SQL
string - thus even a single extra space in the to-be-executed SQL will not
pick up the previously stored Outline since the hash value will be
different. Also, the hash value may depend on the shared_pool_size and
shared_pool_reserved_sizes and any changes will require Outline to be
generated again... (Gurus may want to add to this)
 
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

So WHO is the Reason for the Season?! Write me for details!

** The opinions and statements above are entirely my own and not those of my
employer or clients **


-----Original Message-----
Sent: Wednesday, December 11, 2002 9:29 PM
To: Multiple recipients of list ORACLE-L
outlines


I tried this even without bind variable and could not make it work from a
stored procedure. ANy help over here will be very appreciated
Folllowing is the testcase.
 
Thanks
Shaleen
 
create table emp (num number,sal number);
insert into emp values(1,1);
 
create or replace procedure t as
total number:=0;
cursor c1 is select sal from emp;
begin
for i in c1 loop
total:=total+i.sal;
dbms_output.put_line('total salary is $'||total);
end loop;
end;
/
 
exec outln_pkg.drop_by_cat('DEFAULT');
create outline on select sal from emp;
select used from dba_outlines;
alter session set QUERY_REWRITE_ENABLED=true;
alter session set STAR_TRANSFORMATION_ENABLED=true;
alter session set use_stored_outlines=true;
exec t;
select used from dba_outlines;
select sal from emp;
select used from dba_outlines;


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande@verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).