User defined DML statements on Oracle Connector stage sometimes cause SQL to perform slower or in other words it does full table scan though indexed on the table.
For character columns, the Oracle Connector stage sometimes using an incorrect bind charset form.
Oracle Connector might perform full table scan instead of leveraging indexes if following conditions apply:
1) Oracle database is using a multibyte characterset (e.g.AL32UTF8)
2) Connector Import Wizard is used to import table definitions
3) Varchar2 columns in Oracle get imported as NVarchar in DataStage.
4) Indexed Varchars (seen as nvarchr in DataStage) are used as part of where clause.
Full table scans are occurring because the bind that DataStage uses is NChar if DataStage column is NVarchar or Extended Unicoded. NChar binds will not match the Varchar2 type in Oracle, thus the indexes are not utilized.
Local Fix :
Change nvarchar columns definitions in DataStage to varchar(lenght * bytes per character)
e.g. nvarchar(10) with NLS_LANG set to AL32UTF8 (up to 4 bytes per character) would become varchar(40)
Note: There will not be any issue if you use auto-generated statements in Oracle connector stage.