Re: ASH reporting on SQL spending most of time on CPU

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_at_gmail.com> wrote:

> Hello,
>
> Can you attach a sql monitor report?
>
>
> În mie., 25 aug. 2021 la 12:58, Goti <aryan.goti_at_gmail.com> 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
>>
>

--
http://www.freelists.org/webpage/oracle-l