Re: Query Performance issue

 Regarding the root cause, the team is asking how come the same query with the same path and without any UNDO read running suddenly longer for each and every execution. And as we suspect its volume in some/all base tables increased slightly causing the hash table now to spill to temp, but we don't have the evidence to prove that. And I do see the stats collection was also not getting logged in WRI$_OPTSTAT_TAB_HISTORY, so not able to know how much they have been increased by. And even dba_hist_seg_stats also contains all zero for these objects.

So in this scenario i just go back to the dbms_xplan.display_awr and saw the plan and the estimations done by the optimizer VS the current one which i ran manually by setting "set autotrace traceonly explain" and notice the difference as below, is that accurate information to confirm that organic growth in volume of rows in the base table is the only cause of this higher temp spill and thus slow execution time. or do we have any other way we can verify this? Actually that AWR plan in dba_hist_sqlplan shows a timestamp as sep 2019, so I'm a bit confused.

• Result by manually setting "set autotrace traceonly explain"***************

Execution Plan

      | 43497   (1)| 00:13:47 |


|*  1 |  VIEW                          | VW_FOJ_0        |  1548K|   881M|


      | 43497   (1)| 00:13:47 |


|*  2 |   HASH JOIN FULL OUTER         |                 |  1548K|   698M|


  141M| 43497   (1)| 00:13:47 |


|   3 |    TABLE ACCESS STORAGE FULL   | BOS             |  1152K|   128M|


      |  3274   (1)| 00:01:03 |


|   4 |    VIEW                        |                 |  1548K|   525M|


      | 16677   (1)| 00:05:17 |


|   5 |     VIEW                       | VW_FOJ_1        |  1548K|   525M|


      | 16677   (1)| 00:05:17 |


|*  6 |      HASH JOIN FULL OUTER      |                 |  1548K|   361M|


  132M| 16677   (1)| 00:05:17 |


|   7 |       TABLE ACCESS STORAGE FULL| CS              |   948K|   121M|


      |  2683   (1)| 00:00:51 |


|   8 |       TABLE ACCESS STORAGE FULL| COX             |  1548K|   163M|


      |  3213   (1)| 00:01:02 |
----------------------------------------------------------------------------------------------------------


***************** Result from dbms_xplan.display_awr********************

Plan hash value: 2475526405

----------------------------------------------------------------------------------------------------------


| Id  | Operation                      | Name            | Rows  | Bytes


|TempSpc| Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT               |                 |       |       |


      | 38942 (100)|          |


|   1 |  VIEW                          | VW_FOJ_0        |  1384K|   787M|


      | 38942   (1)| 00:12:20 |


|   2 |   HASH JOIN FULL OUTER         |                 |  1384K|   621M|


  117M| 38942   (1)| 00:12:20 |


|   3 |    TABLE ACCESS STORAGE FULL   | BOS             |   972K|   106M|


      |  2813   (1)| 00:00:54 |


|   4 |    VIEW                        |                 |  1384K|   469M|


      | 15392   (1)| 00:04:53 |


|   5 |     VIEW                       | VW_FOJ_1        |  1384K|   469M|


      | 15392   (1)| 00:04:53 |


|   6 |      HASH JOIN FULL OUTER      |                 |  1384K|   327M|


  124M| 15392   (1)| 00:04:53 |


|   7 |       TABLE ACCESS STORAGE FULL| CS              |   889K|   114M|


      |  2568   (1)| 00:00:49 |


|   8 |       TABLE ACCESS STORAGE FULL| COX             |  1384K|   149M|


      |  2868   (1)| 00:00:55 |
----------------------------------------------------------------------------------------------------------

On Thu, Dec 24, 2020 at 3:55 PM Lok P <loknath.73_at_gmail.com> wrote:

> Attached is the sql monitor with setting these session level parameters. I
> killed it after 15minutes.
>
> Yes, i see the estimation is accurate in all the steps except the final
> one i.e. plan_line_id-1 i.e. VIEW, do you mean to say by some way we can
> rewrite the query(may be using UNION etc) without hampering the result
> set/functionality Or say there is other transformation/path possible which
> optimizer is not doing here , to come up with a better possible path for
> the same query?
> Is it possible, to hint it someway(using cardinality hint) to let the
> optimizer know there are actually going to be ~2rows in the final result
> set, and then see what path the optimizer is taking?
>
>
>
>
> On Thu, Dec 24, 2020 at 3:14 PM Laurentiu Oprea <
> laurentiu.oprea06_at_gmail.com> wrote:
>
>> From my perspective there are multiple  suspicious things
>>
>> First is the estimated number of returned rows. The optimizer is very
>> wrong on this. Based on the fact that your query returns 1 or 2 rows, I
>> think that OR conditions should be transformed in concatenation and
>> predicates pushed at table level as filters . It doesn't make sense to scan
>> everything and filter at the end .
>>
>>  Out of curiosity can you run a serial execution with these at session
>> level:
>>  alter session set "_serial_direct_read"=ALWAYS ;
>>  alter session set "cell_offload_processing"=TRUE;
>>  alter session set "_kcfis_cell_passthru_enabled"=FALSE;
>>  alter session set "_kcfis_storageidx_disabled"=FALSE;
>>  alter session set "cell_offload_plan_display"=ALWAYS';
>>
>> I`ll not set parallel based on the fact that you mentioned this query
>> might be executed by 50 concurrent sessions , you might kill your box.
>> (of course I`m miles away from experience perspective compared with
>> people who answered you,  I might be very wrong... so threat everything I
>> say with care :) )
>>
>> În joi, 24 dec. 2020 la 09:17, Lok P <loknath.73_at_gmail.com> a scris:
>>
>>> Thank You Jonathan.
>>>
>>> I see the projected columns in the inline view are around ~40 to 50 but
>>> are all referred to in the outer query so they are needed.
>>>
>>> As we see the comparison of parallel path vs original query sql monitor(
>>> as attached) for the same set of binds,  I do see the execution finished in
>>> <~15 seconds incase of parallel(4) VS ~15minutes for the original query.
>>> The parallel path does take the HASH HASH distribution for the JOINS, and
>>> the memory has been increased from ~1-2MB to ~9MB for the HASH join
>>> operations, but i am seeing the temp spill has been on the higher side in
>>> case of the parallel execution path i.e. ~578MB VS ~560MB in original
>>> query. I was expecting it to be lesser in case of parallel execution. So is
>>> the execution finished fast , just because the more number of threads
>>> helped in reading the HASH table data from disk faster? And there is no
>>> benefit with respect to overall temp spill, or the sql monitor is really
>>> hiding something/logging something wrong in case of parallel execution sql
>>> monitor report?
>>>
>>> As it rightly happened , it was running in a few seconds in the past but
>>> started running longer suddenly. And i suspect this might be because of
>>> some slight organic increase in data volume in the base tables caused to
>>> break that max session level memory limit oracle allocated to hold the HASH
>>> table and thus spilled to temp causing this multifold run duration, so we
>>> were kind of running on edge since a few days may be.
>>>
>>> So Is it correct to think that if the data volume will increase in the
>>> base table further a bit(due to normal organic growth) , the parallel
>>> path(/*+parallel(4)*/) is also going to slow down after some days? What
>>> should be the long term approach to cater this query if we want it to
>>> finish in the same time around <15-20 seconds duration?
>>>
>>>
>>> On Thu, Dec 24, 2020 at 1:41 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>> wrote:
>>>
>>>>
>>>> Forcing it parallel might be sufficient - I haven't checked to see how
>>>> well a Full outer join would behave in parallel, I imagine it would to a
>>>> hash/hash distribution, which would tend to be even shares with the effect
>>>> of a much larger total memory allocation for the same amount of data.
>>>>
>>>> Another thing to check is that you don't specify any columns in the
>>>> inline view that aren't needed - the optimizer might be unable to project
>>>> them out until it's carried them through the join.
>>>>
>>>> The drawback to usnig parallelism as a workaround is that you might
>>>> starve other processes of CPU, or get unstable response time if too many
>>>> queries try to go parallel at the same time.
>>>>
>>>> If you're trying to get parallelism through an SQL profile/baseline or
>>>> SQL Patch you might have to use the /*+ shared() */ hint rather than the
>>>> /*+ parallel() */ hint.
>>>>
>>>>
>>>>
>>>>
>>>> Regards
>>>> Jonathan Lewis
>>>>
>>>>
>>>> On Wed, 23 Dec 2020 at 19:53, Lok P <loknath.73_at_gmail.com> wrote:
>>>>
>>>>> When you said "* but it's possible that the only option is to ensure
>>>>> that you can get enough memory to avoid the spill to disc*. ", are
>>>>> you pointing towards setting manual work area size and hash_area=2GB in
>>>>> session level? or some other way?
>>>>>
>>>>> I was thinking if driving the query in parallel -4 is a sensible way.
>>>>> What's your thought on this?
>>>>>
>>>>> On Thu, Dec 24, 2020 at 1:03 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>>
>>>>>> Almost certainly correct.
>>>>>> A (native) full outer join can't use in index "into" a table because
>>>>>> the full outer join needs to be an outer join in both directions.
>>>>>>
>>>>>> You could try disabling the full outer join - as a test you could
>>>>>> 'alter session set "_optimizer_native_full_outer_join" = off' to see if
>>>>>> that makes enough of a difference to performance; but it's possible that
>>>>>> the only option is to ensure that you can get enough memory to avoid the
>>>>>> spill to disc.  (In your version of Oracle/Exadata a hash join spill to
>>>>>> disc can be a huge fraction of the total run-time because the I/O can't
>>>>>> "cheat".)
>>>>>>
>>>>>> Regards
>>>>>> Jonathan Lewis
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Wed, 23 Dec 2020 at 18:22, Lok P <loknath.73_at_gmail.com> wrote:
>>>>>>
>>>>>>>  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?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>

--
http://www.freelists.org/webpage/oracle-l