Re: ASH reporting on SQL spending most of time on CPU
Date: Wed, 25 Aug 2021 19:24:46 +0100
Message-ID: <CACj1VR692+Kv53L-cO+uL6V=TU8AWYqj6COibJr+X5cNj=YhSQ_at_mail.gmail.com>
Hi Goti,
What query did you use to return the results you shared in the first email?
The reason I ask is that it looks like you are returning the current_obj#
as the object but this is not valid when the event is null. You should be
joining to the v$sql_plan row and using that. It's easy to go down the
wrong rabbit hole based on misleading information.
The join conditions between customer and market_segment_mapping are quite
interesting:
INNER JOIN terr.market_segment_mapping MSM
The plan says you're joining to market_segment_mapping in the NOT IN subquery
using an index on MARKET_SEGMENT_ID: this, on average, finds 60 rows per
loop which escalates to 4 million rowids in the 5 minute sample. There is
an extra join condition that is repeated in all of the OR branches -
C.customer_group
In the main subquery, you're spending a lot of CPU time doing the hash join
for this condition too. You can see that it is using COMPANY_ID and COUNTRY to
create the hash buckets, the CUSTOMER_GROUP condition is evaluated as a
filter rather than an access, meaning you are comparing more rows than
necessary to each other and probably using more memory to do it. Here, all
you need to do is factorize out the condition. If that is not enough then
you may want to put effort into expanding out the other conditions you are
OR'ing between.
There is a worse row index used on line 46 of the plan though, here each
row driving this loop causes 8 index range scans on average (due to the
number of partitions it needs to read from) and then the filter leads to
about 1000 rows per partition - in the 5 minute sample you obtain 447
million rowids that all need to be accessed in TERR_POSTAL. Eventually this
gets filtered down to 60898 rows (the number of rows from the driving row
source). Judging by the table name, the postal_code join filter probably
provides extremely good selectivity, so an index on that will probably give
you better performance. If you need to include additional columns in the
index to provide this selectivity (maybe terr_id and country?) then do so.
Also consider whether it's worth making it a global index - if postal_code is
supposed to be unique in terr_postal then it would be sensible to make it a
global unique index.
tl;dr
Hope this helps,
On Wed, 25 Aug 2021 at 14:39, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
> Ok, the short answer is that there should be a condition applied in step
ON ( ( C.customer_group = MSM.customer_group
AND C.industry_code = MSM.industry_code
AND C.industry_code_5 = MSM.industry_code_5 )
OR ( C.customer_group = MSM.customer_group
AND C.industry_code = MSM.industry_code
AND MSM.industry_code_5 IS NULL )
OR ( C.customer_group = MSM.customer_group
AND MSM.industry_code IS NULL
AND MSM.industry_code_5 IS NULL ) )
AND MSM.eff_date <= :B1
AND MSM.end_date > :B1
(taken from the NOT IN subquery)
= MSM.customer_group, If you had an index on market_segment_mapping which
covered both of these conditions you should find this effort is reduced
quite a lot (you might need to factorize out this condition for the
optimizer).
Andrew
> 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
>
> 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
>>> 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 - 20:24:46 CEST