Order Management & Fulfillment

Avoid using complex database functions in SQL queries, instead use binding variables

This is part 4 of our blog series aimed at providing tips on how to optimize performance of your IBM Sterling OMS. In each blog, we address common issues that affect performance, best practices for addressing them, and real-world performance issues customers have faced and how they were resolved.

Here, we’re going to discuss how to avoid using complex database functions in SQL queries that increase high wait times, by using binding variables instead.

Problem Statement

Database monitoring report has listed a library cache lock wait event for an update query.

Details

As part of order fulfilment flow, when a consignment of shipments is dispatched from the warehouse, the order status update data sent to IBM Sterling OMS to create/confirm shipment information is processed by an integration server (CSM Dispatch). Post business changes made to receive input data at the shipment level, instead of at the shipment line level, lead to library cache lock wait events being reported.

• Observed a high response time for the Integration server. 
• Observed a high wait time for an update shipment line query on table YFS_SHIPMENT_LINE.
• Elapsed time for the query varied from 136 ms to 800 ms. 
• The usage of SYS_EXTRACT_UTC(TIMESTAMP '2020-01-22 07:32:53.000+00:00’) in the update query resulted in a high number of hard parses of the query when each of the timestamp values being passed differsconsidering they are new literals.
• As the hard parses increase, so do the number of processes waiting to acquire lock on library cache thereby increasing contention and memory consumption.
• On review of code, it was also observed that the prepared SQL statements were being executed inside FOR loop for each shipment line in the code, adding to more executions. 
• As a result, the number of executions and the execution time of the query increased resulting in the high wait time affecting the processing time of the integration server.

Fix Provided

• Fix done by using binding variables in the update query instead of SQL functions.
• Further code optimizations done to place the prepared SQL statement outside the FOR loop.
• Remove explicit commits for update in the code as IBM Sterling OMS takes care of commit.

Performance Improvement

Using binding variables instead of SQL functions along with the other optimizations described above provided the following benefits:

1. No library cache lock wait events for the update shipment line query were reported and the elapsed time was reduced from 162 ms to 0.4 ms, as shown in the table below.
   
   
   
   
2. High reduction in Hard Parses in the database from 146821 to 3.
   
   
3. Significant improvement in the average response times for the integration server, down from 860ms to 136ms, as shown in the graph below.