We all know that Oracle is powerful when you use HINTS and override tables’ physical options and database parameters. There are many tuning and/or performance related blogs on this subject, but what I will share is how to implement HINTS in ODI.
First create a backup of your Knowledge Module and add 2 options to this Knowledge Module, shown below.
Then it is time to add/modify some steps to this Knowledge Module.
To use HINTS successfully, you need to add a step named Parallel DML whose options would be as;
Transaction# is important because this option will let you open a session and make your DML statements under the same session. We will commit after making modifications.
Now it is time to apply HINTS into SELECT and INSERT statements.
Now it is time to commit the Transaction#.
Limitations;
* HINTS are stored in Knowledge Module Options and can store maximum 250 characters (stored in SNP_TXT table in Work Repository).
* HINTS are typed in Interfaces and to modify a HINT, you need to regenerate scenario of interface (or use versioning), test and deploy it to production environment.
Can you do me a favor and elaborate on the limitations?
Comment by Tom — 12.02.13 @ 16:31 |
For instance, if you are using execution work repository on production, and you have development and test development work repositories and your dba/admin wants you to change/rewrite a HINT, your developer should change the HINT, test again and deploy it into production.
Comment by gurcanorhan — 12.02.13 @ 16:39 |
Do you have a post showing how to implement a Hint in an interface? Thank You
Comment by Tom — 12.02.13 @ 18:34 |
If you are going to use method above, you should follow these steps below to add hints to an interface:
1. Open your interface and navigate to Flow Tab,
2. If you did not change your Knowledge Module to edited one. Change your Knowledge Module.
3. Now you will see two hint options in options list.
4. Write your hints in the option values. Do not forget to start with /*+ and end with */ Because knowledge module will get the aoption value as you write it. It will not add this opening and closing brackets. (actually I believe you can do it in Knowledge Module and you can gain 7 more characters for your actual hint)
Note: Watch out for aliases, you should use same aliases as ODI does. Otherwise hints will not work properly.
Comment by Canburak Tumer — 13.02.13 @ 12:00 |
[...] Instead, we can add KM options to that knowledge module as my mentor have explained in his blog, http://gurcanorhan.wordpress.com/2013/02/05/adding-hints-in-odi/ . [...]
Pingback by Using Variable Based Hints in an ODI Knowledge Module « A. Fuat Sungur's Blog — 16.02.13 @ 01:36 |
[...] in my previous post I have described how to implement HINTS per interface. It is a little bit tricky because HINTS are [...]
Pingback by Adding Variabled HINTS in ODI « Gurcan Orhan's Oracle Data Integrator Blog — 18.02.13 @ 14:25 |