Re: Query Performance issue

From: Lok P <loknath.73_at_gmail.com>
Date: Wed, 23 Dec 2020 23:51:54 +0530
Message-ID: <CAKna9VYaKKZmep-58thjr6TOzypqv6tM0ZxMP4T-kTcqkoS2BA_at_mail.gmail.com>



 Table COX has PK on column (EID,BID,OID,ETYP) and table CS has index on column (EID) and table BOS has index on (BID, OID). But none of the index is getting used in the query path, is it because the FULL OUTER JOIN cant use index+ nested loop path here. Is this understanding correct?

On Wed, Dec 23, 2020 at 11:36 PM Lok P <loknath.73_at_gmail.com> wrote:

> Forgot to attach the sql monitor which is better formatted.
>
> On Wed, Dec 23, 2020 at 11:31 PM Lok P <loknath.73_at_gmail.com> wrote:
>
>> Hello ,
>>
>> This database is on version 11.2.0.4 of Oracle Exadata. We have below
>> query which was running in few seconds , but its now running for
>> >15minutes. No change in execution path happened, but after looking into
>> details, we saw its the increase in tempspace spill during HASH JOIN on
>> plan_line_id - 6 and plan_line_id- 2 causing the query to run longer these
>> days. During the slow execution most of the time its spending at line no-6
>> followed by line no- 2 in the execution path with wait event "Direct path
>> read temp". So wanted to understand , what are the possible way to make
>> this query run faster?
>>
>> Users are expecting the query to finish in same time ,within around <30
>> seconds. But it seems that one of these three table might have the volume
>> increased slightly more these days so the hash table spilling to temp
>> multiplying overall execution time for the query.
>>
>> I see , if i am setting the work area_size_policy to MANUAL and
>> hash_area_size to 2GB, we can get those execution happened fully in memory
>> and the query is finishing in ~>15 seconds, but that would be a code change
>> and also considering this query sometimes executing concurrently from many
>> sessions(~50+ executions) , so that seems risky as that may eat-up
>> significant memory.
>>
>> I doubt that the tempspace consumption may be because of the number of
>> columns we are exposing out from the table in the query, but i see all of
>> those are getting fetched and there are no unnecessary column in the
>> projection list. Again i also was trying to see if i can use some indexed
>> path + nested loop, but perhaps because of full outer join index hint is
>> not working too.
>>
>> Is there any other option we can opt to fix this issue in short term
>> using some hints(a better path may be) and then may opt for some code
>> change for long term fix?
>>
>> *Below is the query and also the sql monitor for same, also attached the
>> sql monitor as the body was not appearing properly **formatted** here:-*
>>
>>
>> SELECT ....~58 columns projected...
>>
>> FROM (SELECT ....~60 columns projected
>>
>> FROM "USER1"."BOS" "A2"
>>
>> FULL OUTER JOIN
>>
>> (SELECT ...~41 columns projected from A4 and A5
>>
>> FROM "USER1"."CS" "A4"
>>
>> FULL OUTER JOIN "USER1"."COX" "A5"
>>
>> ON "A5"."EID" = "A4"."EID") "A3"
>>
>> ON "A2"."BI" = "A3"."BID1"
>>
>> AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1"
>>
>> WHERE "A1"."BI0" = :b1
>>
>> OR "A1"."COl1" = :b2
>>
>> AND "A1"."I_DT" IS NULL
>>
>> AND ( "A1"."BI0" IS NOT NULL
>>
>> AND "A1"."CT1" = 'XXX'
>>
>> OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
>>
>>
>>
>> Global Information
>>
>> ------------------------------
>>
>> Status : EXECUTING
>>
>> Instance ID : 1
>>
>> SQL Execution ID : 16777403
>>
>> Execution Started : 12/22/2020 10:08:16
>>
>> First Refresh Time : 12/22/2020 10:08:20
>>
>> Last Refresh Time : 12/22/2020 10:26:15
>>
>> Duration : 1080s
>>
>>
>>
>> Global Stats
>>
>>
>> ==================================================================================
>>
>> | Elapsed | Cpu | IO | Buffer | Read | Read | Write | Write | Cell |
>>
>> | Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
>> Offload |
>>
>>
>> ==================================================================================
>>
>> | 1124 | 120 | 1004 | 81196 | 808K | 92GB | 7100 | 832MB | -2.04% |
>>
>>
>> ==================================================================================
>>
>>
>> SQL Plan Monitoring Details (Plan Hash Value=2475526405)
>>
>>
>> =======================================================================================================================================================================================================================
>>
>> | Id | Operation | Name | Rows | Cost | Time |
>> Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp |
>> Activity | Activity Detail | Progress |
>>
>> | | | | (Estim) | | Active(s) | Active
>> | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (#
>> samples) | |
>>
>>
>> =======================================================================================================================================================================================================================
>>
>> | 0 | SELECT STATEMENT | | | | | | 1
>> | | | | | | | | | | |
>>
>> | -> 1 | VIEW | VW_FOJ_0 | 2M | 42883 | 1072 | +8
>> | 1 | 0 | | | | | | | | | |
>>
>> | -> 2 | HASH JOIN FULL OUTER | | 2M | 42883 | 1079
>> | +2 | 1 | 390K | 147K | 17GB | 2979 | 349MB | 1M | 559M | 17.55 |
>> Cpu (7) | 8% |
>>
>> | | | | | | | | | | | | | | | | |
>> direct path read temp (180) | |
>>
>> | | | | | | | | | | | | | | | | |
>> direct path write temp (2) | |
>>
>> | 3 | TABLE ACCESS STORAGE FULL | BOS | 1M | 3044 | 1
>> | +4 | 1 | 1M
>> | | | | | | | | | |
>>
>> | 4 | VIEW | | 2M | 16655 | 888 | +8 | 1
>> | 2M | | | | | | | | | |
>>
>> | 5 | VIEW | VW_FOJ_1 | 2M | 16655 | 888 | +8
>> | 1 | 2M | | | | | | | | | |
>>
>> | 6 | HASH JOIN FULL OUTER | | 2M | 16655 | 892
>> | +4 | 1 | 2M | 661K | 76GB | 2583 | 303MB | | | 82.45 | Cpu
>> (56) | |
>>
>> | | | | | | | | | | | | | | | | |
>> direct path read temp (829) | |
>>
>> | | | | | | | | | | | | | | | | |
>> direct path write temp (3) | |
>>
>> | 7 | TABLE ACCESS STORAGE FULL | CS | 944K | 2683 | 3
>> | +4 | 1 | 948K
>> | | | | | | | | | |
>>
>> | 8 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 | 3
>> | +6 | 1 | 2M
>> | | | | | | | | | |
>>
>>
>> =======================================================================================================================================================================================================================
>>
>>
>>
>> 1 - filter(("A3"."BID1"=:B1 OR ("A2"."BI"=:B2 AND "A2"."I_DT" IS NULL
>>
>> AND (("A3"."BID1" IS NOT NULL AND "A3"."CT3"='XXX') OR
>> ("A3"."BID1" IS
>>
>> NULL AND "A3"."CT3"='YYY')))))
>>
>> 2 - access("A2"."BI"="A3"."BID1" AND
>>
>> "A2"."OID"=TO_NUMBER("A3"."OID2"))
>>
>> 6 - access("A5"."EID"="A4"."EID")
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 23 2020 - 19:21:54 CET

Original text of this message