Personal Library

Support Article - How to Investigate ICM db Query Performance Issues

https://support.intershop.com/kb/3U0598

Introduction
Steps to Follow
Examples

Product Version	7.10
Product To Version
Status	New Labels

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.

Steps to Follow

Enable the ICM performance sensors inside the SMC

This allows you to track performance query values inside the SMC performance views and related share drive debug log files in TRACE mode:
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 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.

Table of Contents

Introduction

Steps to Follow

Examples