Support Article - How to Investigate ICM db Query Performance Issues

Table of Contents


Product Version

7.10

Product To Version

Status

1 Introduction

Along the ICM runtime, ICM does trigger a lot of queries to fulfill tasks. Sometimes queries will be slow and there is a need to determine and optimize these.
In the example below, you can see how we do determine a slow query by using ICM logging framework for upcoming query optimization tasks.
The query optimization is not part of this document. It is only about to determine long or slow running queries and their bind variables.

2 Steps to Follow

  1. Enable the ICM performance sensors inside the SMC

  1. This allows you to track performance query values inside the SMC performance views and related share drive debug log files in TRACE mode:

  2. Enable debug log scope trace for package


As result the related share drive debug log will describe your suspect query with bind variables and runtime details.

3 Examples

In this example ICM executed a query with bind variables to determine results from "GetTargetCustomers".


Log output from the query, which is triggered:

 
[2022-07-22 12:40:16.290 +0200] DEBUG localhost ES1 appserver0 [PrimeTech-Site] [PrimeTechSpecials] com.intershop.beehive.core.internal.query.processor.oracle.OracleSQLQueryProcessor [] [Storefront] [4SXVFFAKrSfOFDLQnxTBpvjKp6fKSBaI_nmTlk9W] [TAJIEGLafo8UsqjA-0-00] "TAJIEGLafo8UsqjA-0-00" ISH-CORE-2602: Executing SQL: /*customer/GetTargetCustomers (Cartridge bc_customer_orm)*/         SELECT distinct c.uuid as UUID,
case when cp.uuid is null then bp.firstname else cp.companyname end
as SORTING,
count(distinct (c.uuid)) over() as rowcount
FROM customer c
LEFT JOIN companyprofile cp ON c.uuid = cp.customerid
LEFT JOIN customerprofileassignment cpa ON c.uuid = cpa.customerid
JOIN basicprofile bp ON cpa.profileid = bp.uuid
LEFT JOIN basiccredentials bc on bp.uuid = bc.basicprofileid
JOIN usergroupuserassignment ug2u ON bp.uuid = ug2u.userid
WHERE
bp.typecode = 3
AND bp.domainid =  ?
AND (
(ug2u.usergroupid =  ? )
)
ORDER BY SORTING
ASC NULLS FIRST
[binds: [XCsKABE2XSYAAAFxyXUYJOVE, CatalogFilter_w4MKAP8gw1EAAAGCazxZQCV.]]
 
 
Query, describing the runtime:

[2022-07-22 12:40:16.304 +0200] TRACE localhost ES1 appserver0 [PrimeTech-Site] [PrimeTechSpecials] com.intershop.beehive.core.internal.performance.RuntimeSensorImpl [performance] [Storefront] [4SXVFFAKrSfOFDLQnxTBpvjKp6fKSBaI_nmTlk9W] [TAJIEGLafo8UsqjA-0-00] "TAJIEGLafo8UsqjA-0-00" .. 13ms total runtime (13ms effective) for SQL /*customer/GetTargetCustomers (Cartridge bc_customer_orm)*/         SELECT distinct c.uuid as UUID,
case when cp.uuid is null then bp.firstname else cp.companyname end
as SORTING,
count(distinct (c.uuid)) over() as rowcount
FROM customer c
LEFT JOIN companyprofile cp ON c.uuid = cp.customerid
LEFT JOIN customerprofileassignment cpa ON c.uuid = cpa.customerid
JOIN basicprofile bp ON cpa.profileid = bp.uuid
LEFT JOIN basiccredentials bc on bp.uuid = bc.basicprofileid
JOIN usergroupuserassignment ug2u ON bp.uuid = ug2u.userid
WHERE
bp.typecode = 3
AND bp.domainid =  ?
AND (
(ug2u.usergroupid =  ? )
)
ORDER BY SORTING
ASC NULLS FIRST 

Note:

Please take care of the same request ID to combine and understand these two log information. They are one team.

At this point, you know the query "GetTargetCustomers" with bind variables XCsKABE2XSYAAAFxyXUYJOVE and CatalogFilter_w4MKAP8gw1EAAAGCazxZQCV does consume 13ms total runtime.

 
 
If you know the bind variables from a suspect long running query, a DBA can create/analyze an execution plan from to optimize the query (possibly add an index or rework query).
In case of query rework, it is very important that the records still return the expected results.

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Tickets