Re: SQL Performance

Thank you, Marthin. The issue is resolved.

Best Regards,
AMIT On Mon, Mar 18, 2024 at 4:22 PM Martin Berger <martin.a.berger_at_gmail.com> wrote:

> Hi Amit,
>
> More important than the developer is the documentation. Do you have a
> description what the View should return?
>
> To give you an example, I created an EMP and DEPT table - similar to the
> SCOTT schema.
> Just for EMP I used this definition:
> DEPTNO NUMBER(2) --CONSTRAINT FK_DEPTNO REFERENCES DEPT
> => there is NO FK constraint!
>
> I have 3 queries with the same result but different plans:
>
> with *ROWNUM=1*
>
> select e.ename -- BX7
> , (select d.dname
> from dept d
> where d.DEPTNO = e.deptno
> and *rownum = 1* ) as dname
> , (select d.loc
> from dept d
> where d.DEPTNO = e.deptno
> and *rownum = 1* ) as loc
> from EMP e
> where e.sal=3000;
>
> Plan hash value: 2353315787
>
>
> ----------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost (%CPU)| Time |
>
> ----------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | | *9
> *(100)| |
> |* 1 | COUNT STOPKEY | | | |
> | |
> |* 2 | TABLE ACCESS STORAGE FULL FIRST ROWS| DEPT | 1 | 13 |
> 3 (0)| 00:00:01 |
> |* 3 | COUNT STOPKEY | | | |
> | |
> |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| DEPT | 1 | 11 |
> 3 (0)| 00:00:01 |
> |* 5 | TABLE ACCESS STORAGE FULL | EMP | 2 | 26 |
> 3 (0)| 00:00:01 |
>
> ----------------------------------------------------------------------------------------------
>
> with *ANYVALUE*
>
> select e.ename -- BX8
> , (select *any_value*(d.dname)
> from dept d
> where d.DEPTNO = e.deptno
> ) dname
> , (select *any_value*(d.loc)
> from dept d
> where d.DEPTNO = e.deptno
> ) as loc
> from EMP e
> where e.sal=3000;
>
> Plan hash value: 2085794782
>
>
> ------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
>
> ------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | | *11
> *(100)| |
> |* 1 | HASH JOIN OUTER | | 2 | 112 | 11
> (19)| 00:00:01 |
> |* 2 | HASH JOIN OUTER | | 2 | 70 | 7
> (15)| 00:00:01 |
> |* 3 | TABLE ACCESS STORAGE FULL | EMP | 2 | 26 | 3
> (0)| 00:00:01 |
> | 4 | VIEW | VW_SSQ_2 | 4 | 88 | 4
> (25)| 00:00:01 |
> | 5 | HASH GROUP BY | | 4 | 52 | 4
> (25)| 00:00:01 |
> | 6 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 52 | 3
> (0)| 00:00:01 |
> | 7 | VIEW | VW_SSQ_1 | 4 | 84 | 4
> (25)| 00:00:01 |
> | 8 | HASH GROUP BY | | 4 | 44 | 4
> (25)| 00:00:01 |
> | 9 | TABLE ACCESS STORAGE FULL | DEPT | 4 | 44 | 3
> (0)| 00:00:01 |
>
> ------------------------------------------------------------------------------------------
>
> with an *OUTER JOIN* (Oracle style)
>
> select e.ename, d.dname, d.loc -- BX9
> from emp e
> , dept d
> where e.deptno = d.deptno *(+)*
> and e.sal=3000;
>
> Plan hash value: 3387915970
>
>
> -----------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
>
> -----------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | | *6*
> (100)| |
> |* 1 | HASH JOIN *OUTER *| | 2 | 66 | 6
> (0)| 00:00:01 |
> |* 2 | TABLE ACCESS STORAGE FULL| EMP | 2 | 26 | 3 (0)|
> 00:00:01 |
> | 3 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 80 | 3 (0)|
> 00:00:01 |
>
> -----------------------------------------------------------------------------------
>
> The rewrite of your query is similar.
>
> best regards,
> Martin
>
>
> Am Mo., 18. März 2024 um 14:32 Uhr schrieb Amit Saroha <
> eramitsaroha_at_gmail.com>:
>
>> Hi Martin,
>> Thank you for your feedback.
>> I have included the updated monitoring report. The developer who built
>> the view script is no longer with us, making it difficult to determine why
>> there were so many ROWNUM conditions. However, your argument is accurate,
>> and I will investigate further. I'm confused by the suggestion to delete
>> ROWNUM and replace it with outer join. Could you provide an example or
>> clarify further on this re-write?
>>
>> Best Regards,
>> AMIT
>>
>>
>> On Sun, Mar 17, 2024 at 5:04 AM Martin Berger <martin.a.berger_at_gmail.com>
>> wrote:
>>
>>> Hi Amit,
>>>
>>> As Laurentiu asked already: please be more specific and generous with
>>> your information. If the Plan Hash Value is the same, this is an important
>>> detail. Otherwise, the new SQL real time monitor gives others the chance to
>>> help you.
>>>
>>> Can you please share some thoughts and concepts the author
>>> of XXOF_312_ASN_CF_LINES_V had in mind?
>>> There are subselects for vendor_number, vendor_site_code and ebs_po_num.
>>> They contain a filter ROWNUM = 1 but does *not *have an ORDER BY -
>>> this looks similar to the idea of using a DISTINCT - and raises all the
>>> related questions.
>>> All these subselects share access on the tables po_headers_all poh and xxpo01t_asn_inbound
>>> asn_l. As PO_HEADERS_ALL seems to be critical, please translate your
>>> query into an outer join with those 2 tables. So they are only visited once
>>> and not 3 times - all subselects can then use their attributes for their
>>> joins.
>>>
>>> Does this help in your effort?
>>>
>>> Martin
>>>
>>>
>>> Am Fr., 15. März 2024 um 21:07 Uhr schrieb Amit Saroha <
>>> eramitsaroha_at_gmail.com>:
>>>
>>>> I have created the index but no improvement in run time.
>>>>
>>>> Please let me know if I can try anything else?
>>>>
>>>> Best Regards,
>>>> AMIT
>>>>
>>>>
>>>> On Tue, Mar 12, 2024 at 12:20 PM Amit Saroha <eramitsaroha_at_gmail.com>
>>>> wrote:
>>>>
>>>>> Thank you for your feedback, I will create the index and test. Could
>>>>> you also advise what do you mean by re-write?
>>>>>
>>>>> Best Regards,
>>>>> AMIT
>>>>>
>>>>>
>>>>> On Tue, Mar 12, 2024 at 11:13 AM Lothar Flatz <l.flatz_at_bluewin.ch>
>>>>> wrote:
>>>>>
>>>>>> It should be possible to rewrite the statement avoiding that column
>>>>>> level selects.
>>>>>> Am 12.03.2024 um 16:09 schrieb Lothar Flatz:
>>>>>>
>>>>>> Have to correct myself, line 7 is executed 23 time, thus estimate
>>>>>> looks ok.
>>>>>>
>>>>>> Am 12.03.2024 um 16:03 schrieb Lothar Flatz:
>>>>>>
>>>>>> Hi Amit,
>>>>>>
>>>>>> it is both time the access on table PO_HEADERS_ALL where time is
>>>>>> spend.
>>>>>> That is in Line 7 where the selection criteria is "POH"."ATTRIBUTE1"=:B1
>>>>>> AND "POH"."ORG_ID"=:B2. As an easiest option an Index on (ORG_ID, ATTRIBUTE1)
>>>>>> might be useful.
>>>>>> We see that the estimates are somewhat off, as estimated was 1 row,
>>>>>> whereas 23 rows were retrieved.
>>>>>> An improvement of statistics might be advisable.
>>>>>> On line 28, the same table, but this time selection criteria are ("POH"."ATTRIBUTE1"="ASN_L"."DOCUMENT_NUM"
>>>>>> AND "POH"."ORG_ID"="ASN_L"."ORG_ID" AND "POH"."TYPE_LOOKUP_CODE"='STANDARD'
>>>>>> AND NVL("POH"."CLOSED_CODE",'OPEN')='OPEN' AND
>>>>>> NVL("POH"."CANCEL_FLAG",'N')='N').
>>>>>>
>>>>>> As the same columns as above are included and the columns proved to
>>>>>> be selective in line 7 probably the same index would help.
>>>>>> This , if possible create an index on PO_HEADERS_ALL (ORG_ID, ATTRIBUTE1).
>>>>>>
>>>>>>
>>>>>> There might be some testing necessary of just one of the two columns
>>>>>> is enough etc.
>>>>>> However as a first attempt that might be a good startpoint.
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> Lothar
>>>>>>
>>>>>> Am 12.03.2024 um 15:38 schrieb Amit Saroha:
>>>>>>
>>>>>> Hi All,
>>>>>> I have the enclosed query running rather slowly, and I'd like to
>>>>>> enhance it such that it finishes in a few seconds. The query is a SELECT
>>>>>> statement on a view and I enclosed the view text.
>>>>>> Please review the accompanying query and monitoring report and
>>>>>> provide input for improvements.
>>>>>> I appreciate your aid and support in advance.
>>>>>>
>>>>>> Best Regards,
>>>>>> AMIT
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>
>>>

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