My Oracle Support Banner

Delivered SQL Highlighted For SQL Tuning For Performance Improvement In Oracle AWR/ADDM Reports (Doc ID 2768815.1)

Last updated on APRIL 16, 2021

Applies to:

PeopleSoft Enterprise CS Student Records - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

Performance slowness during enrollment (add/drop), looking for recommendations of SQL tuning based on ADDM and AWR reports.

Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
261.7 3,904 0.07 1.0 .0 .0 8ntrggjgabzwa
Module: PSRUNRMT@sisapp14 (TNS V1-V3)
UPDATE PS_CLASS_TBL SET LOCATION=LOCATION WHERE STRM=:1 AND CLASS_NBR=:2

Please check what this SQL was doing and why it was executed so many times, and suggest ways for tuning and performance improvements.

ADDM report
Recommendation 3: SQL Tuning
Estimated benefit is .19 active sessions, 2.52% of total activity.
Action
Investigate the UPDATE statement with SQL_ID "8ntrggjgabzwa" for
possible performance improvements. You can supplement the information
given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 8ntrggjgabzwa.
UPDATE PS_CLASS_TBL SET LOCATION=LOCATION WHERE STRM=:1 AND CLASS_NBR=:2

AWR report
Finding 6: Row Lock Waits
Impact is .25 active sessions, 3.54% of total activity.
SQL statements were found waiting for row lock waits.
Recommendation 1: Application Analysis
Estimated benefit is .19 active sessions, 2.66% of total activity.
Action
Significant row contention was detected in the TABLE
"SYSADM.PS_CLASS_TBL" with object ID 283262. Trace the cause of row
contention in the application logic using the given blocked SQL.
Related Object
Database object with ID 283262.
Rationale
The SQL statement with SQL_ID "8ntrggjgabzwa" was blocked on row locks.
Related Object
SQL statement with SQL_ID 8ntrggjgabzwa.
UPDATE PS_CLASS_TBL SET LOCATION=LOCATION WHERE STRM=:1 AND CLASS_NBR=:2

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.