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;
----- Original Message -----
To: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
Sent: Wednesday, December 11, 2002 2:08 PM
outlines
All,
I am unable to make stored outlines work for sql statements with bind
variable in stored procedures. Please see following sample code and tell me
what am I doing wrong.
Thanks in Advance
create table emp (num number,sal number);
insert into emp values(1,1);
create or replace procedure t(num_i number) as
total number;
cursor c1(p1 number) is select sal from emp where num=p1;
begin
for i in c1(num_i) loop
total:=total+i.sal;
dbms_output.put_line('total salary is $'||total);
end loop;
end;
/
alter session set sql_trace=true;
exec t(1);
alter session set sql_trace=false;
create outline on select sal from emp where num=:b1;
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(2);
select used from dba_outlines;
variable b1 number;
exec :b1:=1;
select sal from emp where num=:b1;
select used from dba_outlines;
Thanks
Shaleen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
