Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBD2N4p25873 for ; Thu, 12 Dec 2002 20:23:04 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBD2N3325866 for ; Thu, 12 Dec 2002 20:23:03 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id PAA62807; Thu, 12 Dec 2002 15:05:45 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00518EDB; Thu, 12 Dec 2002 14:28:42 -0800 Message-ID: Date: Thu, 12 Dec 2002 14:28:42 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Shaleen Garg" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Shaleen Garg" Subject: Re: sql with bind variable in stored procedure doesnt use stored Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit John, Thanks for the input. As far as I can see that sql's are exactly same in my example. Morover none of the initialization parameters have been changed. Need input from all Gurus Thanks Shaleen ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" Sent: Thursday, December 12, 2002 12:14 PM > 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: orACLE-L@fatcity.com > 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: john.kanagaraj@hds.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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaleen Garg INET: shgarg_orafaq@hotmail.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).