How To Pass Init.ora Parameters Through Oracle HINTS

Few days after patching and Oracle database to 11.2.0.4, I get an email from a user reporting ORA-00600 errors when selecting from a few views. I can reproduce the error, which looks something like this:

08:10:36 HRPRD (HRAPP) >select * from HR.PROJECT_EQUIP_V ;
select * from HR.PROJECT_EQUIP_V
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], []

I quickly research Oracle Support for any possible solutions/bugs, and find a few articles which report this problem as a bug.

The solution to it is to set the parameter “_push_join_predicate” to FALSE. For those of you who are curious or have encountered a similar problem, see Doc ID 13501787.8

The error can be replicated in the test environment as well, and I quickly implemented the solution, just to confirm that the parameter change, fixes my problem. Indeed, the ORA-00600 doesn’t occur anymore.

It is so tempting to just go ahead and alter the environment and fix the problem.

Instead, I start asking question, similar to the following ones:

Q: What is the impact of setting this parameter?
A: Huge impact, it can affect lots of select statements that push join predicates. This change can result in lots of execution plan changes.

Q: What is the consequence of this change?
A: Performance could be degraded, as a result of execution plan changes.

Q: How can I implement this change with minimal impact?
A: To have minimal impact, I need to implement the recommendation in a way that only the views are affected. So, how do I do that?

Today’s post is going to show you how I approached this problem, and what you can do, if you encounter similar challenges.

What are the possible solutions to implement the recommended parameter change?

1. Modify the spfile for the database.

   alter system set "_push_join_predicate" = FALSE scope=both sid='*';

This is would be the obvious solution. But let me tell you something. Are you aware of how many SQL statements will change their execution plan?

Most likely not.

Do you have the time to do a full performance testing on the database, to capture what is being affected by the parameter change?

Most likely not.

After some thinking, it turns out, this is not the best solution after-all.

2. Create a trigger that will alter the user’s session that is running the query against the view.

When the user connects, the trigger would do something similar to “alter session set “_push_join_predicate” = FALSE; “. This approach may or may not work. The risk of doing this, is that all the sessions for this specific user are affected. Beside the risk, now we are introducing a new object, a trigger, which needs to be tested as well. This is turning into something more complicated than it needs to be.

This is not the best solution either.

3. Modify the views that get the ORA-00600 error, and somehow pass the parameter to the view. Is this even possible?

YES, it is!

Thanks to HINTS. Before I reveal the solution, let me WARN you about hints.

Unless you know what you are doing DO NOT USE HINTS.

Jonathan Lewis has a great post about hints: Rules For Hinting, and he is also warning you not to use hints

Going back to the solution, I altered the view’s DDL, and in the select statement of the view I added the following hint:

/*+  OPT_PARAM('_push_join_predicate' 'FALSE') */

What is this hint telling the optimizer?

The hint says, when you run the select statement behind the view, know that the _push_join_predicate parameter’s value is FALSE.

Problem solved! It is that simple.

I have recreated the view, with the hint, and the ORA-00600 disappeared.

create or replace view...
as
 SELECT /*+  OPT_PARAM('_push_join_predicate' 'FALSE') */
column1, column2 ....

You can pass this way, any dynamic parameter to the optimizer. I believe this is a great case of using HINTS, especially when you want only a specific statement to be affected.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana