Re: Query runtime is slow in view

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Wed, 3 Feb 2021 09:21:45 +0100
Message-ID: <c97ec78d-c793-113b-a1c2-a629b5e301ba_at_bluewin.ch>

Hi Laurentiu,

there is likely no need for that.
As the activity column in the sql Monitor suggests, time is almost exclusively spent in this column value subquery:

(

     SELECT poh.segment1
     FROM po_headers_all       poh
        , xxpo01t_asn_inbound  asn_l
     WHERE 1 = 1
           AND ROWNUM                              = 1
           AND poh.attribute1 (+)                  = asn_l.document_num
           AND poh.org_id                          = asn_l.org_id
           AND asn_l.header_seq_id                 = asn_lines.header_seq_id
           AND asn_l.line_seq_id                   = asn_lines.line_seq_id
           AND asn_l.file_id                       = asn_lines.file_id
           AND poh.type_lookup_code                = 'STANDARD'
           AND nvl(poh.cancel_flag, 'N')           = 'N'
           AND nvl(poh.closed_code, 'OPEN')        = 'OPEN'

)
The subquery is executed once for each retrieved row. Thus, it was executed 50 times in sql developer (unless you scroll)  and 2255 times in sql*plus that might well explain the time difference. It takes 265 seconds to retrieve 2255 rows, which means 50 rows will take about 6 seconds.

There is some general observation of mine that in many cases it is better to make a query run efficiently than to find out why it runs different in different environments.
I many cases you are hunting ghosts, like in this one. Although I might still be wrong, not having all the facts.

Besides, rethinking the index I believe that it should contain at least the  closed_code in his calculated form: nvl(closed_code, 'OPEN') .

regards

Lothar

Am 03.02.2021 um 07:16 schrieb Laurentiu Oprea:
> Hello Amit,
>
> To make sure you receive better advice in the execution with sql
> developer where you mention is running fast include /*+ monitor */
> hint and attach that sql monitor as well.
>
> Thanks.
>
> În mie., 3 feb. 2021 la 00:21, Amit Saroha <eramitsaroha_at_gmail.com
> <mailto:eramitsaroha_at_gmail.com>> a scris:
>
> Thank you for the clarification and as suggested I have enclosed
> the SQL monitoring report here. Kindly review if you have some
> time to help me.
>
>
> Best Regards,
> Amit
>
>
> On Mon, Feb 1, 2021 at 1:30 PM Lothar Flatz <l.flatz_at_bluewin.ch
> <mailto:l.flatz_at_bluewin.ch>> wrote:
>
> Hi,
>
> first of all Sql Devloper will only retrieve the first 50 (by
> default)
> rows. Timing in SQL Developer can not be trusted if the result
> is > 50 rows.
>  From your description I do not know if whatever you query in SQL
> Developer is really the equivalent of selecting from the
> (tuned) view.
> There is a good chance that you are dealing with two diffent
> queries.
> The best way simulating a not yet created view is a subquery
> in the from
> clause. Even than it is not sure that we really get the same
> execution
> plan when we query the stored view.
> We would need run time statistics to determine what is going on.
> Working without exact measurement is unscientific.
>
> Regards
>
> Lothar
>
>
>
>
>
> Am 01.02.2021 um 18:14 schrieb Amit Saroha:
> > Good Morning, Everyone,
> >
> > I am in need of your expert suggestions for a weird
> situation and any
> > pointers are appreciated.
> >
> > The situation is - There’s a view containing a UNION query,
> which I
> > tuned and results are coming in less than a second when
> running in SQL
> > developer. But, when I replaced the existing view query with
> the tuned
> > query and select it from the view then it started taking
> more than 2
> > minutes.
> >
> > I have never seen such a situation before when the query is
> running
> > within a second but when used inside view starts taking the
> time.
> >
> >
> > Best Regards,
> > Amit
>
> --
> http://www.freelists.org/webpage/oracle-l
> <http://www.freelists.org/webpage/oracle-l>
>
>

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

Received on Wed Feb 03 2021 - 09:21:45 CET