A couple of days ago, I’ve been called to solve integration interface issue between SAP and ReIM (ReIM is application within the Oracle Retail Suite).
The main challenge when solving such issue is to find out where you should start with your analysis and to distinguish between what is the cause of the issue and what is the consequence.
To be able to find out, you really need to know much not only about Oracle Db (from dev/dba/performance tuning perspective along with internal Db stuff), but the architecture of the system along with how integration is working (Oracle Service Bus in this case).
Problem can be best described by showing you the picture of how it looks like.
The picture points to the following SQL as the guilty one, as consumes of all enq – TX wait event.
But that SQL is only the consequence, not the real cause.
First attempt is to gather stats and rebuild indexes. But that doesn’t help this time (it would be to easy and I wouldn’t describe it here).
After a while, I’ve found the line in the code PL/SQL package that executes the DML with enq – TX wait.
... l_im_doc_head_row IM_DOC_HEAD%ROWTYPE; UPDATE IM_DOC_HEAD SET ROW = l_im_doc_head_row where doc_id = l_im_doc_head_row.doc_id; ...
What is the problem with that code?
You should better ask why developer has written such a code.
There could be two reasons:
– he/she was not sure which columns from the integration point is changed between two systems (SAP & Oracle Retail in this case)
– he/she found the code like this as convenient way/shortcut to code the statement like this, where only couple of columns need to be updated:
Parsed call, as a result of the UPDATE … SET ROW = l_im_doc_head_row statement is here:
UPDATE im_doc_head SET "DOC_ID" =:b1, "TYPE" =:b2, "STATUS" =:b3, "ORDER_NO" =:b4, "LOCATION" =:b5, "LOC_TYPE" =:b6, "TOTAL_DISCOUNT" =:b7, "GROUP_ID" =:b8, "PARENT_ID" =:b9, "DOC_DATE" =:b10, "CREATE_DATE" =:b11, "CREATE_ID" =:b12, "VENDOR_TYPE" =:b13, "VENDOR" =:b14, "EXT_DOC_ID" =:b15, "EDI_UPLOAD_IND" =:b16, "EDI_DOWNLOAD_IND" =:b17, "TERMS" =:b18, "TERMS_DSCNT_PCT" =:b19, "DUE_DATE" =:b20, "PAYMENT_METHOD" =:b21, "MATCH_ID" =:b22, "MATCH_DATE" =:b23, "APPROVAL_ID" =:b24, "APPROVAL_DATE" =:b25, "PRE_PAID_IND" =:b26, "PRE_PAID_ID" =:b27, "POST_DATE" =:b28, "CURRENCY_CODE" =:b29, "EXCHANGE_RATE" =:b30, "TOTAL_COST" =:b31, "TOTAL_QTY" =:b32, "MANUALLY_PAID_IND" =:b33, "CUSTOM_DOC_REF_1" =:b34, "CUSTOM_DOC_REF_2" =:b35, "CUSTOM_DOC_REF_3" =:b36, "CUSTOM_DOC_REF_4" =:b37, "LAST_UPDATE_ID" =:b38, "LAST_DATETIME" =:b39, "FREIGHT_TYPE" =:b40, "REF_DOC" =:b41, "REF_AUTH_NO" =:b42, "COST_PRE_MATCH" =:b43, "DETAIL_MATCHED" =:b44, "BEST_TERMS" =:b45, "BEST_TERMS_SOURCE" =:b46, "BEST_TERMS_DATE" =:b47, "BEST_TERMS_DATE_SOURCE" =:b48, "VARIANCE_WITHIN_TOLERANCE" =:b49, "RESOLUTION_ADJUSTED_TOTAL_COST" =:b50, "RESOLUTION_ADJUSTED_TOTAL_QTY" =:b51, "CONSIGNMENT_IND" =:b52, "DEAL_ID" =:b53, "RTV_IND" =:b54, "DISCOUNT_DATE" =:b55, "DEAL_TYPE" =:b56, "HOLD_STATUS" =:b57, "TOTAL_COST_INC_TAX" =:b58, "TAX_DISC_CREATE_DATE" =:b59, "DSD_IND" =:b60, "ERS_IND" =:b61, "SUPPLIER_SITE_ID" =:b62, "CONSOLIDATION_START_DATE" =:b63, "CONSOLIDATION_END_DATE" =:b64, "MANUALLY_CREATED_IND" =:b65, "INTERNAL_DOCUMENT" =:b66 WHERE doc_id =:b1;
To translate in plain English, this means either developer has lack of business knowledge to solve the issue, or he/she was lazy to code an update statement that includes a couple of columns.
After analyzing the IM_DOC_HEAD table, I’ve found many chained rows as shown on the following picture.
To solve the chained rows issue, It’s enough just to execute ALTER TABLE MOVE.
Unfortunately, this is not the only issue.
Second point is to rewrite the problematic DML like this, where only necessary columns will be updated:
UPDATE IM_DOC_HEAD SET TERMS = l_im_doc_head_row.TERMS, DOC_DATE = l_im_doc_head_row.DOC_DATE, DUE_DATE = l_im_doc_head_row.DUE_DATE, ORDER_NO = l_im_doc_head_row.ORDER_NO, TYPE = l_im_doc_head_row.TYPE, CONSOLIDATION_START_DATE = l_im_doc_head_row.CONSOLIDATION_START_DATE, CONSOLIDATION_END_DATE = l_im_doc_head_row.CONSOLIDATION_END_DATE, CREATE_ID = l_im_doc_head_row.CREATE_ID, VENDOR = l_im_doc_head_row.VENDOR, EXT_DOC_ID = l_im_doc_head_row.EXT_DOC_ID, CREATE_DATE = l_im_doc_head_row.CREATE_DATE, PRE_PAID_IND = l_im_doc_head_row.PRE_PAID_IND, LOCATION = l_im_doc_head_row.LOCATION, LOC_TYPE = l_im_doc_head_row.LOC_TYPE, STATUS = l_im_doc_head_row.STATUS, RTV_IND = l_im_doc_head_row.RTV_IND, CURRENCY_CODE = l_im_doc_head_row.CURRENCY_CODE, EXCHANGE_RATE = l_im_doc_head_row.EXCHANGE_RATE, TOTAL_COST = l_im_doc_head_row.TOTAL_COST, CUSTOM_DOC_REF_1 = l_im_doc_head_row.CUSTOM_DOC_REF_1, CUSTOM_DOC_REF_2 = l_im_doc_head_row.CUSTOM_DOC_REF_2, CUSTOM_DOC_REF_3 = l_im_doc_head_row.CUSTOM_DOC_REF_3, CUSTOM_DOC_REF_4 = l_im_doc_head_row.CUSTOM_DOC_REF_4, LAST_UPDATE_ID = l_im_doc_head_row.LAST_UPDATE_ID, LAST_DATETIME = l_im_doc_head_row.LAST_DATETIME, HOLD_STATUS = l_im_doc_head_row.HOLD_STATUS, TOTAL_COST_INC_TAX = l_im_doc_head_row.TOTAL_COST_INC_TAX, RESOLUTION_ADJUSTED_TOTAL_COST = l_im_doc_head_row.RESOLUTION_ADJUSTED_TOTAL_COST, RESOLUTION_ADJUSTED_TOTAL_QTY = l_im_doc_head_row.RESOLUTION_ADJUSTED_TOTAL_QTY WHERE DOC_ID = l_im_doc_head_row.DOC_ID;
There are two more issues in this case, but that will be covered in some other blog.
The main point is to change as little data as you can, if you want the system to scale and run fast.