Home » SQL & PL/SQL » SQL & PL/SQL » optimizer_feature_enable Hint (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
optimizer_feature_enable Hint [message #675807] Mon, 22 April 2019 01:12 Go to next message
saipradyumn
Messages: 396
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 Go to previous messageGo to next message
John Watson
Messages: 7933
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 #675812 is a reply to message #675809] Mon, 22 April 2019 01:59 Go to previous messageGo to next message
saipradyumn
Messages: 396
Registered: October 2011
Location: Hyderabad
Senior Member



Hi

That hint is there from the long back.Now we are in 11.2.0.4.0 Release.But As per the query still we are referring to the old version '11.1.0.7'.
Recently we didn't perform any version up gradation.

Apart from this Hint, remaining stuff seems to be normal business logic.

That's why suspecting the problem with HINT only

Thanks
SaiPradyumn
Re: optimizer_feature_enable Hint [message #675813 is a reply to message #675812] Mon, 22 April 2019 02:07 Go to previous messageGo to next message
John Watson
Messages: 7933
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 #675815 is a reply to message #675813] Mon, 22 April 2019 03:01 Go to previous messageGo to next message
saipradyumn
Messages: 396
Registered: October 2011
Location: Hyderabad
Senior Member
Hi John Watson,

I have gone through the HINT & ORA-04031 ,Below one is basic information which I got


1) The optimizer_features_enable parameter shifts the behavior of the optimizer backwards to the specified release. The optimizer_features_enable is very useful when an execution plan changes after the application of a patch or the installation of a new release.

2) ORA-04031: unable to allocate nn bytes of shared memory

Cause: More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".



Second solution is completely DBA activity which I am discussing with DBA Team to do the such kind of enhancements IN UAT first

Simultaneously working on tuning part of the query. As we didn't went for DB up gradation & Db is having new version and through the query
still we are referring the features of OLD version.That's why suspected the problem with HINT.


More over this problem is there only in production.As UAT and PROD DB configurations are different, We can't expect the same issue in UAT.
I can't ask DBA to go with same configuration in UAT also(just like Prod).

Finally I am doing the analysis what ever I can do.


Please let me know if I am wrong still.

Thanks
SaiPradyumn
Re: optimizer_feature_enable Hint [message #675816 is a reply to message #675815] Mon, 22 April 2019 03:07 Go to previous messageGo to next message
John Watson
Messages: 7933
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 Go to previous message
Bill B
Messages: 1868
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?
Previous Topic: update with subqueries possibilty
Next Topic: pl/sql procedure compile error PLS-00103
Goto Forum:
  


Current Time: Mon Jun 17 14:26:37 CDT 2019