ETL9.2:Entire System Unresponsive / SQL Pegged With Temp Table Delete Statements (Doc ID 2234688.1)

Last updated on FEBRUARY 22, 2017

Applies to:

PeopleSoft Enterprise HCM Time and Labor - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Other

ACTUAL BEHAVIOR
---------------
Entire system unresponsive / SQL pegged with Temp Table delete statements

Our Production environment has been unusable this morning and continues to be so. We are seeing all the Application Queues stuck on ICPANEL and the database is being bombarded with the following SQL command:

DELETE FROM PS_TL_VALID_TAO7 WHERE PROCESS_INSTANCE = 2582

The "PROCESS_INSTANCE" number never changes, it's always 2582 and these commands have been running since 7:30am this morning (2.5 hours). SQL is pegged at 99% CPU and the environment is unusable.

In an attempt, we forced shutdown the Application Stack on all the nodes, Waited for the DB to clear up, and then restarted the Application Stacks and the commands started to flood back into SQL.

EXPECTED BEHAVIOR
-----------------------
The above delete statement should not cause db to not respond

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Users are approving time and submitting time
2. On database side the above delete statement is issued to many times causing db performance
BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot perform their normal day to day activities as it is causing extreme performance bottleneck

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