Re: how can I move from sql profiles to stored outlines?
- From: Stefan Koehler <contact@xxxxxxxx>
- To: ilsuonogiallo@xxxxxxxxx
- Date: Mon, 19 Jan 2015 16:39:11 +0100 (CET)
Hi Andrea,
two (main) possibilities, if you just want to put the SQL profile hints into a
stored outline. Step 3 is needed in general.
1) Query Oracle DDIC (for 10g) directly
SELECT attr_val
FROM sys.sqlprof$ p, sys.sqlprof$attr a
WHERE p.sp_name = '<PROFILE_NAME>'
AND p.signature = a.signature
AND p.category = a.category;
2) Put SQL profiles into stating table and query hints
SELECT *
FROM table(SELECT attributes
FROM <STAGING_TABLE_NAME>
WHERE profile_name = '< PROFILE_NAME>');
3) Create private stored outlines (with and without hinted SQL), switch/modify
(table ol$ and ol$hints) and publish them
The whole procedure can be scripted with PL/SQL, if you have to migrate a lot
of them.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
> Andrea Monti <ilsuonogiallo@xxxxxxxxx> hat am 19. Januar 2015 um 13:42
> geschrieben:
>
> Hi Listers,
>
> I am looking for the most convenient way to build stored outlines from sql
> profiles.
> I realize that this is quite a weird question and I do know that sql
> profiles provide far more benefits than stored outlines; however since I will
> have to move from a 10.2.0.4 Enterprise Edition database to a 10.2.0.4
> Standard Edition cluster, I will have to fall back to stored outlines to
> provide plan stability.
> thanks for every help
> Regards
> Andrea
--
//www.freelists.org/webpage/oracle-l
Other related posts:
