Re: Query Performance issue

From: Lok P <loknath.73_at_gmail.com>
Date: Thu, 24 Dec 2020 00:48:09 +0530
Message-ID: <CAKna9VYD=Je6w16w31rdeaEL3RF+0wKdYNGFhRpMoUjCOOSYCQ_at_mail.gmail.com>



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
Received on Wed Dec 23 2020 - 20:18:09 CET

Original text of this message