Performance Impact in Filtering - Due to Query Splitting
Last updated on FEBRUARY 22, 2017
Applies to:Oracle Commerce Platform - Version 11.0 and later
Information in this document applies to any platform.
On : 11.0 version, Crashes, Hangs, Performance, Data Corruption
Customer was experiencing a performance impact in their filtering on targeters due to a functionality in ATG called "Query Splitting".
Query Splitting is a functionality in ATG that creates separate queries from larger single queries, and then combines the results as needed on the separate queries. It does this on queries that are too complicated for ATG to dynamically produce as one query and could potentially return bad results. This can however, cause querying to take much longer depending on how the query was split. There really isn't a definitive way to avoid query splitting altogether without negatively affecting the areas of the product that use this functionality heavily and with benefit.
Note (Finding Query Splitting Instances):
They key is noticing in the logging when you see log debugging with "eq2" or "eq3" and so on (Examples Below) , this is indicating the query was split. This doesn't necessarily mean that it wasn't efficient, but if you are encountering performance issues, reviewing this sql debugging may give you some insight into what is occurring and how you can improve the filtering to speed up the queries that are generated (Explained below how to improve filtering)
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms