Re: SQL Performance

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Mon, 18 Mar 2024 21:22:12 +0100
Message-ID: <CALH8A91DOjLXaSNL_iL-Tweg=2oOtHq9EXrfa+KN8FRC0fzhig_at_mail.gmail.com>

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

| 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

| 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

| 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

Received on Mon Mar 18 2024 - 21:22:12 CET

This message: [ Message body ]
Next message: Krishnaprasad Yadav: "Re: High CPU utilization"
Previous message: Tanel Poder: "Re: High CPU utilization"
Maybe in reply to: Amit Saroha: "SQL Performance"
Next in thread: Mladen Gogala: "Re: concurrent statistics gathering"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message