Performance Issue In SelfService Is Observed When Awr Report Is Run (Doc ID 2718592.1)

Last updated on JUNE 16, 2022

Applies to:

Symptoms

ACTUAL BEHAVIOR
---------------
When running the AWR report, the following sql is reported for the highest cpu time:

SELECT * FROM (SELECT ts.api_name, ts.item_key, tapiv.VARCHAR2_VALUE TRANS_STEP_VALUE, ts.transaction_id, ts.transaction_step_id, ts.creation_date, ts.creator_person_id, txn.API_ADDTNL_INFO CONTACT_OPERATION FROM hr_api_transaction_steps ts, hr_api_transaction_values tapiv, hr_api_transactions txn WHERE ts.item_type = :1 AND ts.update_person_id = :2 AND txn.transaction_id = ts.transaction_id AND txn.status in ('Y', 'YS') AND (upper(ts.api_name) IN ('HR_PROCESS_PERSON_SS.PROCESS_API', 'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API', 'HR_PROCESS_ADDRESS_SS.PROCESS_API', 'HR_PROCESS_CONTACT_SS.PROCESS_API', 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API')) AND tapiv.transaction_step_id (+) = ts.transaction_step_id AND tapiv.name(+) = 'P_PRIMARY_FLAG' AND EXISTS ( SELECT NULL FROM hr_api_transaction_values tsv WHERE tsv.transaction_step_id = ts.transaction_step_id AND tsv.name = 'P_REVIEW_PROC_CALL' AND tsv.varchar2_value IS NOT NULL) AND EXISTS (SELECT NULL FROM wf_item_attribute_values iav2 WHERE iav2.item_type = ts.item_type AND iav2.item_key = ts.item_key AND iav2.name = 'TRAN_SUBMIT' AND iav2.text_value in ('Y', 'S'))) QRSLT ORDER BY transaction_step_id desc

EXPECTED BEHAVIOR
-----------------------
This should not have such high cpu time

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run and review AWR report

Changes

 None

Cause

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