Re: Query Performance issue

In below BIO- comes from - A5 i.e table COX,  COL1 comes from A2 i.e table BOS
 and CT1 comes from A5 i.e table COX
 and I_DT comes from A2 i.e table BOS.

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 Thu, Dec 24, 2020 at 1:04 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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