Re: Query Performance issue

Attached is the one i had captured for the completed one, but it's in text only.

I will try to capture the html version next time, Out of curiosity, does html/flash give some additional information to perform analysis which the text one won't provide?

On Thu, Dec 24, 2020 at 12:09 AM Laurentiu Oprea < laurentiu.oprea06_at_gmail.com> wrote:

> 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




text/plain attachment: complteted_sql_monitor.txt