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