Troubleshooting
Problem
A performance problem may occur in the drill down operation on column analysis results of an Oracle data source even though the column has an index.
Symptom
The drill down operation appears to take more time than expected.
Cause
For drill-down operations against an Oracle source table, InfoSphere Information Analyzer executes a SQL query that uses the RTRIM function.
In order to handle a column that has '\0' padded values, there is a need to add RTRIM to the SQL query.
The usage of RTRIM causes a performance problem, as the Oracle optimizer does not use the index when RTRIM is present in the SQL query.
If the column on which the drill down operation is being performed does not have an index, then there would only be a small impact from having RTRIM in the SQL query.
Environment
InfoSphere Information Analyzer 8.7 Rollup Patch 3
Diagnosing The Problem
If after installing InfoSphere Information Analyzer 8.7 Rollup Patch 3 (or later release), there is an unexplained delay in getting the results from drill down of a large Oracle source table, check the RTRIM setting of DBMS-Specific options in the global level Analysis settings.
Resolving The Problem
In InfoSphere Information Analyzer 8.7 Rollup Patch 3, a check box to select RTRIM for Oracle data sources has been added in DBMS specific options of Global Analysis Settings. By default, the check box will be checked.
The check box allows the user to contol whether RTRIM is included in the drill down query.
Since RTRIM was added to the SQL query to trim padded '\0' characters in the Oracle column, removing RTRIM could cause drill down operation failure in cases where the user has '\0' padded values in the data. In this case, the user should have the check box checked whild doing drill down of such columns.
Hence, when the user unchecks the check box, the user will receive a message in a dialog box, about the reason for using RTRIM.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21609978