Re: Query Performance issue

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




text/plain attachment: attachment_sqlmonitor.txt