| OPT_PARAM - WORK_AREA_POLICY [message #644342] |
Tue, 03 November 2015 09:46  |
rasikeg@gmail.com
Messages: 4
Registered: November 2006
Location: LK
|
Junior Member |
|
|
Hi ,
I am trying to add the OPT_PARAM hint to change the WORK_AREA_POLICY parameter in a query but it does not seems to be responding.
select /*+ opt_param('workarea_size_policy','manual')
opt_param('sort_area_size',100000000)
opt_param('hash_area_size',100000000)
*/ *
from V1;
V1 is a view. I am trying to remove temp space usage in a hash join, when I set the session using "alter session set workarea_size_policy=MANUAL" and then the sizes it seems to be working. But using the hint .. have not been successful.
Please let me know if there is a way to do it using this hint.
I am trying to see the impact using explain plan and from real response of the query.
Thanks,
Rasike
|
|
|
|
|
|
| Re: OPT_PARAM - WORK_AREA_POLICY [message #645266 is a reply to message #644345] |
Tue, 01 December 2015 21:57  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Officially, depending upon your release of Oracle:
1. this may still be an undocumented feature.
2. where documented it officially only supports a few parameters.
3. unofficially it does actually set many parameters though there is no list of what these are that I have found so you have to guess.
4. it does not support all parameters. Work area related parameters may simply be ones that do not work with it, I don't know that one.
5. it is not something I would encourage in a production situation.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
