Re: ** outline change
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: ajoshi97@xxxxxxxxx, riyaj.shamsudeen@xxxxxxxxx
- Date: Wed, 18 Mar 2009 11:37:11 -0700 (PDT)
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 good one bad and then exchange. In my case : I
cannot change query etc and create outline since it is indirect query.
So I need to create outline and then change it. Another problem is my
OEM is not working for which I sent another mail. So any help is
welcome. Thanks
--- On Wed, 3/18/09, Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx> wrote:
From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxx
Subject: Re: ** outline change
To: ajoshi977@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Wednesday, March 18, 2009, 11:07 AM
Hi Joshi
Have you reviewed document 730062.1? That document is the correct way to
manipulate outlines.
--
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 9:12 AM, A Joshi <ajoshi977@xxxxxxxxx> wrote:
Hi,
I want to update a stored outline to change the join method. Is
there a way it can be done from sqlplus instead of from OEM or tool.
Using just unix sqlplus access. I looked at DBMS_OUTLN package but
nothing in it. I do see tables like outln.ol$hints and the outline in
it but I do not want to do a sqlplus update on it without knowing the
full impact. I cannot change the query with hint etc since it is a
indirect query. So I cannot give hint etc and exchange plan. Thanks for
help. In TOAD : it says there is outline management but I cannot find
it. Thanks
Other related posts:
