Restructuring a query causing 'latch: cache buffer chains' waits
Hi,
We have this query that is causing 'latch: cache buffer chains' waits. This is causing a very huge performance degradation on our production server since the query is scheduled to run every hour. Rescheduling the queries are not an option. I understand that there are several ways to address hot blocks but we want to try first restructuring (if possible) the query. Please see below:
INSERT INTO IM_RECEIPT_ITEM_POSTING_STAGE (SEQ_NO, RECEIPT_ID, ITEM_ID, QTY_POSTED, CREATE_DATE) SELECT SEQ_NO, RECEIPT_ID, ITEM_ID, QTY_POSTED, :1 FROM IM_RECEIPT_ITEM_POSTING WHERE QTY_POSTED