Update statement Causes 100% CPU usage in 11.2 database (Doc ID 1153823.1)

Last updated on JULY 22, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

PeopleSoft Expenses is causing 100% CPU usage on database server whenever the following update statement is executed:

UPDATE oracle.ps_ex_sheet_line
SET    monetary_amount   = 11 ,
       txn_amount        = 11 ,
       ee_dplcat_comment = ' ',
       duplicates_exist  = 'N'
WHERE  sheet_id          = '0000014585'
AND    line_nbr          = 1;


The execution plan shows usage of the following index:
CREATE INDEX ORACLE.PSAEX_SHEET_LINE
ON ORACLE.PS_EX_SHEET_LINE
(
"TRANS_DT" DESC,   ---------<<<<<<----------
SHEET_ID ,
LINE_NBR ,
EXPENSE_TYPE ,
TXN_AMOUNT ,
TXN_CURRENCY_CD
)
LOGGING TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE
(
INITIAL 5M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT
)
NOPARALLEL;


This problem is not restricted to UPDATEs. This can happen with any SQL statement whose execution plan references an index created with the DESC clause.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms