Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query

This post is just a reflection, basically based on my last post about Adaptive Query Optimization/SQL Plan Directives. Several times we find some situations that can be solved by setting a parameter (likely a “_”) and we just proceed with an “alter system” disregarding about all the impact of this.

This is specially important when talking about “optimizer” parameters even for bugs causing ORA-600’s or performance issues… Changing those parameters in system will affect not only the SQL with the error but all SQLs in database, which may cause a really bad effect in some cases.

Ok Matheus, but what can I do?
Let’s to the obvious and generally applied methods:

1. Change parameter in whole database:
As I just said, this is the most common action taken by DBAs, but with high risks for other SQLs and Database as a system. Should be avoided unless this is generalized problem affecting several SQLs which cannot be mapped.

2. Using a logon trigger for application schema:
Ok, it’s better, but still is affecting all the application… You can do better than this… 🙂

3. Changing SQL to avoid bug:
Yes, Oracle don’t use to say that, but understanding the issue by MOS or even an SR, can you change the query to avoid the bug? Of so, DO IT!

4. Use Optimzer Hint for the specific SQL:
Oh that’s the point I was trying to say… If you can change the SQL but can’t avoid to hit the bug, you can use a hint OPT_PARAM to change the parameter ONLY for this SQL. Perfect!
Of course you should be aware on the impacts to setting hint all time in database, but once you are hitting a bug for only one specific query and you have access to code, this is the best option.

And as you can imagine, Oracle don’t use to recommend that… 🙂

Oh, and how to use it? Like this:

/*+  OPT_PARAM('&parameter' '&value') */

Examples:

/*+  OPT_PARAM('optimizer_mode','first_rows_10') */
/*+  OPT_PARAM('_push_join_predicate' 'FALSE') */

5. Use Optimzer Hint for the specific SQL without changing the code

Oh, this is a PLUS!

Lets review:
– You are hitting a bug, a performance issue or something with an SQL that requires you to change a parameter
– This is only for one (or a few) specific SQLs
– You cannot change the SQL to avoid the bug
– BUT, You also cannot change the code to add the Hint…

There is a bug tip. You can use an SQL Profiles to accomplish that!
You know, in the end of the day, SQL Profiles are basically hints that are recommended to your SQL persisted in Database.
And the procedure dbms_sqltune.import_sql_profile allows you to create a SQL Profile using the hint you desire.

At this point, I’d recommend you to check this post by Kerry Osborne that have a pretty nice script doing everything for you.
Of course, pay attention to licensing side. To use this you must have Oracle Tuning Pack which requires Diagnostics Pack. So, be aware.

And now, pay attention in next time you are changing the bahavior of your ENTIRE database because one query.

Be safe, and see you next week!