ASH reporting on SQL spending most of time on CPU
From: Goti <aryan.goti_at_gmail.com>
Date: Wed, 25 Aug 2021 15:28:25 +0530
Message-ID: <CAOzfMuqN7SzKnKgv7VRLfGL3OwGPxqVcfTHoTc2ksWjRFHiE2A_at_mail.gmail.com>
Hi All,
.3%
Date: Wed, 25 Aug 2021 15:28:25 +0530
Message-ID: <CAOzfMuqN7SzKnKgv7VRLfGL3OwGPxqVcfTHoTc2ksWjRFHiE2A_at_mail.gmail.com>
Hi All,
We have a SQL performance issue and as per ASH the SQL is spending most of its time on CPU. Not sure which operation is taking more time. Can someone guide me how to troubleshoot what is causing the SQL to spend time on the CPU?
SQL_ID EVENT SESSION MODULE
PCT
------------------ ------------------------------------ -------
------------------------------------ -------
fyfnh1pnxjg2d ON CPU PL/SQL
Developer 91.9% <<<<<<<<<<<<
OBJ OTYPE OID ONAME
SQL_ID CLASS PCT
--------------------------- --------------------------- ----------
------------------------------------ ------------------ ------------------
----------
XIE1CUST_TERR INDEX PARTITION 106842
XIE1CUST_TERR fyfnh1pnxjg2d data block
0%
XFK1PARTITION_TERR_POSTAL INDEX PARTITION 107430
XFK1PARTITION_TERR_POSTAL fyfnh1pnxjg2d data block
.2%
XFK1PARTITION_TERR_POSTAL INDEX PARTITION 107435
XFK1PARTITION_TERR_POSTAL fyfnh1pnxjg2d
95.5%
XIE1CUST_TERR INDEX PARTITION 106843
XIE1CUST_TERR fyfnh1pnxjg2d data block
1.6%
XIE1CUST_TERR INDEX PARTITION 106841
XIE1CUST_TERR fyfnh1pnxjg2d data block
.1%
XIE4CUST_TERR INDEX PARTITION 106911
XIE4CUST_TERR fyfnh1pnxjg2d free list
0%
fyfnh1pnxjg2d 0%
XIE1CUST_TERR INDEX PARTITION 106839
XIE1CUST_TERR fyfnh1pnxjg2d data block
2.3%
XIE1CUST_TERR INDEX PARTITION 106838
XIE1CUST_TERR fyfnh1pnxjg2d data block
.3%
Execution Plan Details:
https://gist.githubusercontent.com/aryangoti/7f0bc85cbe6df372e488deecbfdf30ef/raw/40598552b0a5c47c6857c86b06eabbca2d5daf8a/gistfile1.txt
Thanks,
Goti
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 25 2021 - 11:58:25 CEST