IFS Oracle latch contention (Apps 8)

What a great question. To help provide advice I need to know more about your Oracle instance. These are pretty basic questions, but will give me an idea of how resources are assigned to the database.

1. What version of Oracle?
2. Can you please provide a dump of your Oracle initialization parameters?
3. How much memory is installed on the server?
4. Is this the only database on the server or are there multiple instances?

"latch: cache buffers chains" contention is typically encountered because SQL statements read more buffers than they need to, and multiple sessions are waiting to read the same block.

If you have high contention, you need to look at the statements that perform the most buffer gets and then look at their access paths to determine whether these are performing as efficiently as you would like. Hopefully we can identify queries that are not utilizing an index, create the index, and thereby decrease the number of blocks that need to be read. We can’t change the query in IFS, but we can add indexes if they are missing.

Lets see what SQL statements are using the most BUFFER_GETS

select buffer_gets, sql_fulltext
from V$SQLSTATS
order by buffer_gets desc;

Based on the SQL queries we can identify the code that uses the most buffer gets and see if it can be improved.

Thank you for the update. 4 Billion buffer gets (4,157,385,699) is a high number and the time spent on the query is high at about 1.5 minutes. Can you please provide the definitions for all indexes on the following objects. The explain plan you provided shows that some full scans are being performed.

• WORK_ORDER_CODING_UIV
• PURCHASE_REQ_LINE_TAB
• PURCHASE_PART_GROUP_TAB
• WORK_ORDER_CONNECTION

Are you able to use base tables in place of WORK_ORDER_CODING_UIV or WORK_ORDER_CONNECTION? These VIEWs might be adding overhead for the columns you are referencing.

You are missing indexes on some objects and the select statement could use improvement. Ideas,

1. Select only the unique records from the last inner select statement.

OLD

SELECT WC.CONNECTED_WO_NO
FROM WORK_ORDER_CONNECTION WC
START WITH WO_NO = :B1
CONNECT BY PRIOR WC.CONNECTED_WO_NO = WO_NO

NEW

​SELECT UNIQUE WC.CONNECTED_WO_NO
FROM WORK_ORDER_CONNECTION WC
START WITH WO_NO = :B1
CONNECT BY PRIOR WC.CONNECTED_WO_NO = WO_NO

1.  Add a non-unique index on the PURCHASE_PART_GROUP_TAB table for the column C_PUR_GRP_CATEGORY.
2. Add a non-unique index on the PURCHASE_REQ_LINE_TAB table for the column ROWTYPE.
3. Change the selection of WORK_ORDER_CODING_UIV to WORK_ORDER_CODING_TAB and the where statement ““WOC.WORK_ORDER_COST_TYPE_DB = :B3”” to “WOC.COST_TYPE = :B3”. This will drive down the cost and number of blocks required by the query.
4. Add a non-unique index on the table WORK_ORDER_CODING_TAB for the following columns; REQUISITION_NO, REQUISITION_LINE_NO, REQUISITION_RELEASE_NO, COST_TYPE, WO_NO

Lets see what these improvements bring. The reworked statement might look like,

SELECT NVL (SUM (NVL (WOC.AMOUNT, 0)), 0)     TOTAL_AMOUNT
  FROM WORK_ORDER_CODING_TAB    WOC,
       PURCHASE_REQ_LINE_TAB    PRL,
       PURCHASE_PART_GROUP_TAB  PPG
 WHERE     WOC.REQUISITION_NO = PRL.REQUISITION_NO
       AND WOC.REQUISITION_LINE_NO = PRL.LINE_NO
       AND WOC.REQUISITION_RELEASE_NO = PRL.RELEASE_NO
       AND WOC.COST_TYPE = :B3
       AND (WOC.WO_NO = :B1
            OR WOC.WO_NO IN (    SELECT UNIQUE WC.CONNECTED_WO_NO
                                   FROM WORK_ORDER_CONNECTION_TAB WC
                             START WITH WO_NO = :B1
                             CONNECT BY PRIOR WC.CONNECTED_WO_NO = WO_NO))
                                    AND PRL.STAT_GRP = PPG.STAT_GRP
       AND PRL.ROWTYPE = 'PurchaseReqLineNopart'
       AND PPG.C_PUR_GRP_CATEGORY = :B2