Adding HINTS in ODI

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

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.

About these ads

6 thoughts on “Adding HINTS in ODI

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

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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