Thanks Laurentiu for responding. Please find the below link which contains
the sql monitor report. This was taken 5 minutes after it was started.The
SQL ran for more than 5 hours and hence we had to cancel that. I don't see
SQL in RTSM history too. I am not sure how far this will be useful.
https://gist.githubusercontent.com/aryangoti/eae0776d88d46361c2c60d0d6b083680/raw/ec1ac6a6359ca3f99c6f7216850dfa09103b6d78/gistfile1.txt
Thanks,
Goti
On Wed, Aug 25, 2021 at 4:07 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
Hello,
Can you attach a sql monitor report?
În mie., 25 aug. 2021 la 12:58, Goti <aryan.goti@xxxxxxxxx> a scris:
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
