Home » RDBMS Server » Performance Tuning » OPT_PARAM - WORK_AREA_POLICY (11.2.0.4)
OPT_PARAM - WORK_AREA_POLICY [message #644342] Tue, 03 November 2015 09:46 Go to next message
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 #644345 is a reply to message #644342] Tue, 03 November 2015 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What make you think you can use these hints and parameters in a query?

Re: OPT_PARAM - WORK_AREA_POLICY [message #645266 is a reply to message #644345] Tue, 01 December 2015 21:57 Go to previous message
Kevin Meade
Messages: 2102
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
Previous Topic: Testing SPM
Next Topic: Please help for tuning .sql query
Goto Forum:
  


Current Time: Wed Jan 17 14:59:23 CST 2018

Total time taken to generate the page: 0.01192 seconds