Re: ** outline change
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: riyaj.shamsudeen@xxxxxxxxx
- Date: Wed, 18 Mar 2009 15:14:17 -0700 (PDT)
Riyaj,
Thanks for your help. tkprof shows full table scan on the remote database.
So even if the mlog has 10 entries : it is doing full table scan on source
table which is big and fts is taking almost one hour . It does not do a simple
index search which will take 1 minute. I was able to get the big query which
uses current$ and mlog$. It has a bind variable 1 (:1) and I am not able to
create a bind variable with 1
SP2-0553: Illegal variable name "1".
Also it is date and I cannot create variable with date.
SQL> variable b1 date;
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL>
So I put in value sysdate -1 and created outline and then did the exchange per
note 730062.1 but no luck yet. it is still taking long time. I will try with
pl/sql block but it might have different bind variable name. Thanks
--- On Wed, 3/18/09, Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx> wrote:
From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
Subject: Re: ** outline change
To: ajoshi977@xxxxxxxxx
Cc: ajoshi97@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Date: Wednesday, March 18, 2009, 3:10 PM
Hi Joshi
Use hints and then create outlines exactly as you want the plan to be. Then
you will be able to exchange the outlines, as long as, you don't materially
alter the SQL statement.
create outeline <outln> for
select /*+ hints...*/
.. Rest of the query..
If you are making a very minimal change to the plan, then you can directly
update the ol$hints table, but I always prefer to swap outlines by creating
outlines with explicit hints.
But, I am surprised that you are resorting to this method for MV refresh. Is
it possible for you to share tkprof output lines? It is rare that you need to
use outlines for MV refresh, I suppose, needed in some cases.
--
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
On Wed, Mar 18, 2009 at 1:37 PM, A Joshi <ajoshi977@xxxxxxxxx> wrote:
Riyaj,
I am on dev. The sql is related to MV refresh where the source table is on
remote database. The remote database is not accessible to me as dba. only read
only. The refresh is sending sql to remote database. I have created a outline
by running the mv refresh. Now I need to change the outline. How do I do it
from sqlplus since I cannot go to tool outline management. I need to change the
hint and join method using sqlplus. I did a select * on
OL$HINTS and I can see the hint listed and maybe it can be changed.However, I
cannot see the join method listed like hash join etc. Thanks for help.
--- On Wed, 3/18/09, Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx> wrote:
From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
Subject: Re: ** outline change
To: ajoshi97@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Wednesday, March 18, 2009, 1:54 PM
Hi Joshi
You really need to create outlines through the application, if you don't
have access to code. Oracle uses signature which is raw(16) column and seems to
be generated from SQL text. Unless, you have exactly same text, signature
doesn't match. You might want to run this process in dev or test ( with logon
trigger based created_stored_outlines=true or whole system ) and collect
outlines. Then drop all outlines except that specific outline you are tuning.
Once you have that outline collected, then you can get sql_text from
outln.ol$. Tune that SQL and then create outline using 'create outline
<olname> for <sqltext>'. At this point, you have two outlines and you can swap
them using that technique in that ML document. Technically, speaking you can
update ol_name as that is the link between all three tables. Of course, it is
better idea to update hint_count also.
It is easy to copy that outline from dev to prod using exp/imp (in unix,
$, single quote, double quote, ( etc need to be excaped or use parfile for
exp.).
exp userid=\"/ as sysdba\" file=outln_tuned.dmp log=outln_tuned.log tables=\(
outln.ol\$, outln.ol\$nodes, outln.ol\$hints \) query=\" where
ol_name=\'SYS_OUTLINE_070710104645510\'\"
import that in PROD.
imp userid=\"/ as sysdba\" file=outln_tuned.dmp log=outln_tuned.log
fromuser=outln touser=outln ignore=Y
As a side note, sql_text in outln.ol$ is a long column and searching for
a specific sql_Text is not easy one. To search easier use this method: Create a
clob column and search on that.
create table rs.ol$lob (ol_name varchar2(30), sql_text clob)
tablespace users;
insert into rs.ol$lob select ol_name, to_lob(sql_text) from
outln.ol$;
now, you can search on text...select * from rs.ol$lob where
upper(sql_text) like '%T1$';
--
Cheers
Riyaj
On Wed, Mar 18, 2009 at 11:58 AM, A Joshi <ajoshi977@xxxxxxxxx> wrote:
Riyaj,
Yes. I had seen this note. It is applicable for when we have two outlines
one <snip..>
Other related posts:
