Document Tree
Document Properties
Kbid
3U0598
Last Modified
23-Aug-2023
Added to KB
05-Sep-2022
Public Access
Everyone
Status
Online
Doc Type
Support Articles
Product
ICM 7.10
Home ⋮ Knowledge Base
Support Article - Investigate ICM Database Query Performance Issues
Share this document

Table of Contents


Introduction

During ICM runtime, ICM triggers 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 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 determining long or slow running queries and their bind variables.

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.

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 pay attention to the same request ID to combine and understand these two log information. They belong together.

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

 
 
If you know the bind variables from a suspect long running query, a database administrator create/analyze an execution plan from it 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.
Home
Knowledge Base
Product Releases
Log on to continue
This Knowledge Base document is reserved for registered customers.
Log on with your Intershop Entra ID to continue.
Write an email to supportadmin@intershop.de if you experience login issues,
or if you want to register as customer.