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.

Hi Alan,

Thanks for the reply first and foremost, your explanation of of the latch buffer chains was very helpful.

To answer your questions:

1. What version of Oracle?
   Here’s our v$version:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE    11.2.0.4.0    Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

2. Can you please provide a dump of your Oracle initialization parameters?
   I’ve attached our initialization parameters
3. How much memory is installed on the server?
   80GB available on the server, currently is using 52.8GB (middle of working day)
4. Is this the only database on the server or are there multiple instances?
   Single instance

Based on the results from the SQLSTATS query the top query is the “Problem” statement that our 3rd party identified for us previously.

BUFFER_GETS: 4157385699

SELECT NVL(SUM(NVL(WOC.AMOUNT,0)), 0) TOTAL_AMOUNT FROM WORK_ORDER_CODING_UIV 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 PRL.STAT_GRP = PPG.STAT_GRP AND WOC.WORK_ORDER_COST_TYPE_DB = :B3 AND PRL.ROWTYPE = 'PurchaseReqLineNopart' AND PPG.C_PUR_GRP_CATEGORY = :B2 AND (WOC.WO_NO = :B1 OR WOC.WO_NO IN (SELECT WC.CONNECTED_WO_NO FROM WORK_ORDER_CONNECTION WC START WITH WO_NO = :B1 CONNECT BY PRIOR WC.CONNECTED_WO_NO = WO_NO ))

I’ve previously identified this query as part of an IFS mod we have had for a number of years now. Our 3rd party gave us the attached execution plan.

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.

Hi Alan, 

Once again thanks for your time.

I’ve added a file of an extract of all indexes and all index columns I hope you will find this helpful. WORK_ORDER_CODING_UIV does contain a bit of site level user security within the view which I can understand why it would have an impact on the performance, sadly its being called within IFS’s logic so I can’t edit this myself, but I will raise this point with IFS. 

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

Thanks Alan I really appreciate your time on this, I’ll look to adding the indexes but I’ll have to liaise with IFS on changing the cursor in the package,

I’ll let you know the results from both and if we continue to be impacted by the contention.

Thanks again,

Tom