Re: Query Performance issue

Do you have the Sql Monitor where execution is completed? (maybe in html/flash format)

În mie., 23 dec. 2020 la 20:23, Lok P <loknath.73_at_gmail.com> a scris:

> 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