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

Ok, the short answer is that there should be a condition applied in step 26 on Terr_Postal which is estimated as too selective. IT is possible that this is the source of your trouble. Find out why it is wrong and correct the stats if possible. Sorry that I am a bit short, but I am under some time pressure.

Regards

Lothar

Am 25.08.2021 um 13:12 schrieb Goti:
> 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
> <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 <mailto: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
> <mailto: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
> <https://gist.githubusercontent.com/aryangoti/7f0bc85cbe6df372e488deecbfdf30ef/raw/40598552b0a5c47c6857c86b06eabbca2d5daf8a/gistfile1.txt>
>
> Thanks,
> Goti
>

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