Re: Query Performance issue

I was rather thinking if as a short term fix, we should try parallel hint forced through a profile to make it faster. Not sure if that is a good idea?

On Thu, 24 Dec 2020, 12:48 am Lok P, <loknath.73_at_gmail.com> wrote:

> I was trying to force index hint but it was not taking that so I thought
> may be some restrictions wrt full outer join is causing this . Isn't this
> true?
>
> Also even current plan estimation are accurate so shouldn't the optimizer
> by default go for index + nested loop path?
>
> On Thu, 24 Dec 2020, 12:43 am Laurentiu Oprea, <
> laurentiu.oprea06_at_gmail.com> wrote:
>
>> yes an active report gives more info, like how you benefited from a smart
>> scan, you can compute the I/O latency, etc.
>>
>> I wanted to see a completed report because I was curious about the
>> returned number of rows.
>> I see it is 2 rows, It is possible that indexing will help . Try to do
>> test runs and understand what are the columns that have the best
>> selectivity and are doing the filtration here. it might be the case
>> that they are good candidates for an index.
>>
>>
>> În mie., 23 dec. 2020 la 20:52, Lok P <loknath.73_at_gmail.com> a scris:
>>
>>> 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