Re: ** outline change
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: ajoshi97@xxxxxxxxx
- Date: Wed, 18 Mar 2009 12:54:57 -0500
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<riyaj.shamsudeen@xxxxxxxxx>
>
> 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:
