sql with bind variable in stored procedure doesnt use stored 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)
astotal number;cursor c1(p1 number) is select sal from emp where
num=p1;beginfor i in c1(num_i)
looptotal:=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
Re: sql with bind variable in stored procedure doesnt use stored 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 astotal
number:=0;cursor c1 is select sal from emp;beginfor i in c1
looptotal:=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 -
From:
orafaq
To: [EMAIL PROTECTED]
Sent: Wednesday, December 11, 2002 2:08
PM
Subject: sql with bind variable in stored
procedure doesnt use stored 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) astotal number;cursor c1(p1 number) is
select sal from emp where num=p1;beginfor i in c1(num_i)
looptotal:=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
create_stored_outlines does not work -urgent
Hi,
We are having a
full tablescan issue with query used by DBMS_AQ.deque procedure and as per
John's recommendation I am playing with outlines to fix this issue. Since this
is an internal query I do not have the exact text of the query . So I was trying
to create the outline and when I execute following piece code no outlines are
created. Can you help in this?alter system set
create_stored_outlines=true;declaretyp number(10);msglen
number(10);msg
varchar2(4000);beginrdmtcpuser.gentcp.Dequeue('KEWILL_SHIP0_PR_OUT',1,typ,msglen,msg
);end;/alter system set create_stored_outlines=false;This should
logically create outlines for all the sqls executed by
dequeue.
I turn the
trace on this session which shows me the sqls being executed but no outline is
created. Although I do have create outline permissions and if I execute sql
statements then outlines are created for those. Only for procedure outline is
not created.
ThanksShaleen
Re: Increase size of data files and rollback segments
A cruel joke Jeremy! BTW may be you can help me out here. I am not receiving back any of the messages I send to the the list and I do not know if list is receiving my messages either. Do you know how to solve this problem? Thanks in advance Shaleen Garg - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 09, 2002 3:38 AM On Mon, 9 Dec 2002, Nguyen, David M wrote: How do I increase size of oracle data files and rollback segments and Can I do it when database is online? Unfortunately these sizes are fixed, and based on your level of license with Oracle Corp. If you need to increase the size of your datafiles or rollback segments, you must contact your Oracle sales representative and request additional power units. If you find that you run in a dynamic enough environment, you may wish to upgrade to a more sophisticated database system such as MS SQL Server or Filemaker Pro, both of which allow dynamic resizing. :-) -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
Re: limiting temp space per user
The way we do it is that create two temporary tablespaces. Use one for the production applicationa and other for adhoc users so that adhoc users do not mess up the production. -Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 09, 2002 8:04 AM SQL alter user scott quota 10M on temp; User altered. HTH, Krishna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 09, 2002 06:24 AM All Is it possible to limit the temp space used per user - either by using profiles or some other method? I tried using profiles but it would not allow me to set a limit on space usage. TIA Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Krishna Rao Kakatur 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
AQ query from dequeuw procedure not using index -URGENT
All, We are using a third party application (Retek Warehouse management) which uses AQ as communication mechanism between processes. The problem we are facing is that the AQ query behind Dequeue is doing a full tablescan on queue table which is causing about 5Billion logical reads/day and is bringing the DB to its knees. Following is the query which I gleaned from tracing the process select /*+ FIRST_ROWS */ qt.rowid, qt.msgid, qt.corrid, qt.priority, qt.delay, qt.expiration, qt.retry_count, qt.exception_qschema, qt.exception_queue, qt.chain_no, qt.local_order_no, qt.enq_time, qt.time_manager_info, qt.state, qt.enq_tid, qt.step_no from PAR3214.GENERICTCP qt where q_name = :1 and state = :2 order by q_name, state, enq_time, step_no, chain_no, local_order_no for update skip locked The procedure call is following DBMS_AQ.DEQUEUE( 'par3214.' || qname, queueopts, msgprops, msg_obj, msgid ); Since this is an internal query, I can not change the HINT ( Itested that removing the hint drops logical reads from 2400 to 3). Any ideas? Thanks Shaleen
Re: AQ query from dequeuw procedure not using index -URGENT
Title: RE: AQ query from dequeuw procedure not using index -URGENT This database is running RULE so analyzing wont help. Outlines is a good idea. Any known issues with outlines in 8173? Thanks Shaleen - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, December 09, 2002 12:39 PM Subject: RE: AQ query from dequeuw procedure not using index -URGENT Outlines is the way to go as John suggested ... also ensure that all tables related to the queues are analyzed ... regularly. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: John Kanagaraj [mailto:[EMAIL PROTECTED]] Sent: Monday, December 09, 2002 2:49 PM To: Multiple recipients of list ORACLE-L Subject: RE: AQ query from dequeuw procedure not using index -URGENT Shaleen, Have you considered using Outlines? John Kanagaraj
Re: AQ query from dequeuw procedure not using index -URGENT
It's oracle AQ internal query so cant change it. I have already tried it without the hint and it does use the index but again can't change the query. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 09, 2002 12:49 PM Shaleen, The use of FIRST_ROWS hint with an order by clause is ridiculous. The hint is ignored. Raj John Kanagaraj john.kanagaraTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Sent by: Subject: RE: AQ query from dequeuw procedure not using index -URGENT [EMAIL PROTECTED] om December 09, 2002 02:49 PM Please respond to ORACLE-L Shaleen, Have you considered using Outlines? 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: Monday, December 09, 2002 10:04 AM To: Multiple recipients of list ORACLE-L All, We are using a third party application (Retek Warehouse management) which uses AQ as communication mechanism between processes. The problem we are facing is that the AQ query behind Dequeue is doing a full tablescan on queue table which is causing about 5Billion logical reads/day and is bringing the DB to its knees. Following is the query which I gleaned from tracing the process select /*+ FIRST_ROWS */ qt.rowid, qt.msgid, qt.corrid, qt.priority, qt.delay, qt.expiration, qt.retry_count, qt.exception_qschema, qt.exception_queue, qt.chain_no, qt.local_order_no, qt.enq_time, qt.time_manager_info, qt.state, qt.enq_tid, qt.step_no from PAR3214.GENERICTCP qt where q_name = :1 and state = :2 order by q_name, state, enq_time, step_no, chain_no, local_order_no for update skip locked The procedure call is following DBMS_AQ.DEQUEUE( 'par3214.' || qname, queueopts, msgprops, msg_obj, msgid ); Since this is an internal query, I can not change the HINT ( Itested that removing the hint drops logical reads from 2400 to 3). Any ideas? Thanks Shaleen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
Re: LGWR using lots of CPU time, low CPU usage
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
Re: OT- mailx option
-r option of mailx provides you a way to specify reply to address. Thanks Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 28, 2002 6:18 AM --=_MAILER_ATTACH_BOUNDARY1_2002112841948211540383426 Content-Type: text/plain; charset=us-ascii Hi Gurus, This is slightly off the subject. Here I am trying to automate one report and send it to group of DBAs via mail I am using mailx. I am not able to find out the option of setting sender's name and reply-to address. I searched the man pages but couldn't find it. Currently I am doing this. $ report.sh | mailx -s Daily Report address1 address2 My requirement is that the person will be receive this mail should see my name in the 'from' column and if the person reply to the mail, reply should come to my personal address. Can sombody help. ~Dilip Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_2002112841948211540383426 Content-Type: text/html; charset=us-ascii PHi Gurus,/P PThis is slightly off the subject./P PHere I am trying to automate one report and send it to group of DBAs via mail /P PI am using mailx. I am not able to find out the option of setting sender's name and reply-to address. I searched the man pages but couldn't find it. Currently I am doing this./P P$ report.sh | mailx -s Daily Report address1 address2/P PMy requirement is that the person will be receive this mail should see my name in the 'from' column and if the person reply to the mail, reply should come to my personal address./P PCan sombody help./P P~Dilip/P Pnbsp;/P Pnbsp;/P Pnbsp;/P hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com/font/a/bbrBuy Music, Video, CD-ROM, Audio-Books and Music Accessories from A href=http://www.planetm.co.in;http://www.planetm.co.in/A --=_MAILER_ATTACH_BOUNDARY1_2002112841948211540383426-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dilip7772002 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
Re: instance shutdown problem ? (please help)
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). _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arup Nanda 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: john 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
Re: instance shutdown problem ? (please help)
') 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). _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arup Nanda 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: john 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
Re: Problem doing RMAN backup of Clone?
Use following command to generate new dbid. dbms_backup_restore.zeroDbid(0) Read Note:164870.1 on metalink for complete procedure. HTH -Shaleen - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 25, 2002 7:05 AM Unfortunately, this problem is with the DBID, not anything in the controlfile. Oracle 9.2 comes with a utility called 'nid' in $ORACLE_HOME/bin/, which can change the DBID of a database. AFAIK, there is no other way to change the DBID, and you just have to use another catalog. I think nid may work on lower version databases, but I am not sure. Check out the docs. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Mon, 25 Nov 2002, Koivu, Lisa wrote: I can't answer the question about rman... but if you recreate the controlfile you will have a new incarnation number of the database. Have you tried that? -Original Message- From: Doug C [SMTP: ] Someone has just told me you can't do an RMAN backup of a clone because it has the same database id as the original. Is this true or not? If so, how to get around it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orafaq 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).
