Re: Query Performance issue

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Wed, 23 Dec 2020 22:33:58 +0300
Message-ID: <CAOVevU4-5pg0-rBZrdNHYvcXrEvrHbXUOSHkE5WDiVDoRNvuFg_at_mail.gmail.com>



Hi Lok,

You removed columns projections from the query, so I can't understand where those columns come from:

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')



On Wed, Dec 23, 2020 at 10:22 PM Lok P <loknath.73_at_gmail.com> wrote:

> 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")
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 23 2020 - 20:33:58 CET

Original text of this message