optimizer_feature_enable Hint [message #675807] |
Mon, 22 April 2019 01:12 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
One of my query is following hint to execute:
/*+ OPTIMIZER_FEATURES_ENABLE('11.1.0.7') */
When we executed this query on production, we got the following exception in the exception from alert log
ORA-04031 Out of Memory in database
/qfxora/optware/oracle/diag/rdbms/pqfxcep/database_sid/trace/database_sid_j010_14604.trc:
ORA-04031: unable to allocate 760 bytes of shared memory ("shared pool","unknown object","KKSSP^446","kglss")
Not only this HINT, query contains complex business logic.But I am suspecting that because of the HINT ,
its leading to that Out of memory exception.
Could any body help to understand the functionality of that HINT.
Thanks
SaiPradyumn
|
|
|
Re: optimizer_feature_enable Hint [message #675809 is a reply to message #675807] |
Mon, 22 April 2019 01:15 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:But I am suspecting that because of the HINT ,
its leading to that Out of memory exception. Why do you think the hint is the problem? What happens if you remove it? Why did you put it there? Did you not read up on it first?
|
|
|
|
Re: optimizer_feature_enable Hint [message #675813 is a reply to message #675812] |
Mon, 22 April 2019 02:07 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
So you have no idea what the hint does or why it is there. You appear to be applying the principle that if A=B and C=B then A=C. In this case the logic is hint="not understood", ora-4031="no understood, therefore hint=ora-4031.
You really need to develop some problem solving skills. I've already given you some suggestions: read up on what the hint does, and perform an experiment. It would help to read up on ora-4031 too.
|
|
|
|
Re: optimizer_feature_enable Hint [message #675816 is a reply to message #675815] |
Mon, 22 April 2019 03:07 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:Please let me know if I am wrong still. For heaven's sake, man, DO THE TEST. What happens if you run the query without the hint? You have no idea why the hint was put in there, so you should get rid of it anyway. Typically hints are put in to fix a problem in one release, perhaps when you upgraded to 11.2.0.2, and they cause problems in a later release, such as 11.2.0.4.
|
|
|
Re: optimizer_feature_enable Hint [message #675828 is a reply to message #675816] |
Mon, 22 April 2019 10:24 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The optimizer is getting better in every release. Take out the hint and see how it runs. You are informing the optimizer engine to throw away all the improvements in the engine that were installed between version 11.1.0.7 and 11.2.0.4.0
Why are your crippling your query?
|
|
|