Troubleshooting
Problem
When prepared queries are executed in an Oracle database, and one of the primary limiting fields is a Date/Time, JDBC sends this as a Timestamp (time including milliseconds) when the field in the database is a Date (Date / Time to nearest second), Oracle optimizer often chooses to do a full table scan even if an index is present.
Symptom
High buffer gets and/or disk reads for a query, since oracle is performing a FTS (full table scan).
Cause
For a prepared query, SSFS code populates using a Timestamp field, which sets the data type in the JDBC client side as having time accurate to the millisecond or more (even though the code only goes out to the nearest second.) When the Oracle database sees this, it says that the passed value is more accurate than the value stored in the database data and indexes as Date (date and time to the nearest second). This can cause either 1) a less-efficient execution plan using an internal function to convert the database Date to a TimeStamp, or 2) decide that it is too inefficient to use an index, and chooses to perform a FTS.
Environment
Oracle 11x or greater; SSFS 8.5 or greater
Diagnosing The Problem
A typical example would be the following:
SELECT /*YANTRA*/ YFS_STATISTICS_DETAIL.* FROM YFS_STATISTICS_DETAIL YFS_STATISTICS_DETAIL WHERE MODIFYTS < :1
Even if there is an index on (MODIFYTS), and a small selection set (relative to table size) is predicted, Oracle will choose an inefficient FTS for access. Reviewing AWRs and explain plans will show the inefficient access.
Note: Not every occurrence of a Timestamp field in a query necessarily means that it has to be this Timestamp problem. Regular analysis to review indexes present, index statistics, data distributions/histograms, etc. need to be check and eliminated first before proceeding with this resolution.
Resolving The Problem
Two components: 1) Let SSFS know about the field to be handled as a Timestamp, and 2) use dbverify or generated script to modify the field in the database from Date to Timestamp
The application has been enhanced to support the TIMESTAMP datatype for Oracle database, so that the index on the column is used. For altering the datatype of a column from Date to Timestamp, a new
ConsiderOracleDateTimeAsTimeStamp XML attribute has been exposed. The user must set the value of this attribute to true to change the datatype from Date to Timestamp. The user must extend the column definition in their entity extension XML to set this attribute.
For example, if the column MODIFYTS in the YFS_STATISTICS_DETAIL table needs to be changed from Date to Timestamp for Oracle database, add the following entry in the extension XML:
<Entity TableName="YFS_STATISTICS_DETAIL">
<Attributes>
<Attribute ColumnName="MODIFYTS" ConsiderOracleDateTimeAsTimeStamp="true"/>
</Attributes>
</Entity>
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21678721