Gurcan Orhan's Oracle Data Integrator Blog


Adding HINTS in ODI

Filed under: Oracle Data Integrator — gurcanorhan @ 15:55
Tags: , , ,

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.

Adding HINT #1 - Set KM Options

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;

Adding HINT #2 - Parallel DML

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.

Adding HINT #3 - Usage in KM Step

Now it is time to commit the Transaction#.

Adding HINT #4 - Commit


* 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.

About these ads


  1. Can you do me a favor and elaborate on the limitations?

    Comment by Tom — 12.02.13 @ 16:31 | Reply

    • 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 | Reply

  2. Do you have a post showing how to implement a Hint in an interface? Thank You

    Comment by Tom — 12.02.13 @ 18:34 | Reply

    • 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 | Reply

  3. [...] Instead, we can add KM options to that knowledge module as my mentor have explained in his blog, . [...]

    Pingback by Using Variable Based Hints in an ODI Knowledge Module « A. Fuat Sungur's Blog — 16.02.13 @ 01:36 | Reply

  4. [...] 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 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at


Get every new post delivered to your Inbox.

Join 483 other followers

%d bloggers like this: