Re: Re: SQL performance in prod

Lothar,

That’s because the nested loop line is only executed once.

Unfortunately my phone won’t let me copy and paste to an email with a decent font but if you go to
https://docs.oracle.com/database/121/TGSQL/tgsql_interp.htm#TGSQL94854  and scroll down to the execution plan with row source statistics. You can see that the nested loop operation on line 1 is started 1 time even though it has 9165 rows input into it. The e-rows is based on that one execution of the whole set of input.

For a nested nested loop, it’s still consistent. The e-rows will be for one execution of the line, the line might be executed multiple times though because it is in a nested loop itself.

Andy

On Thu, 26 Jul 2018 at 08:42, l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> wrote:

> Andy,
>
> for a nested loop join the estimates in the outer loop are per execution,
> but on the join level (the NL Operation) the estimates reflects the result
> of all executions.
>
> Regards
>
> Lothar
>
> ----Ursprüngliche Nachricht----
> Von : andysayer_at_gmail.com
> Datum : 26/07/2018 - 09:28 (GMT)
> An : gogala.mladen_at_gmail.com
> Cc : oracle-l_at_freelists.org
> Betreff : Re: SQL performance in prod
>
>
>
> Mladen,
>
> “My assumption was that the plans for a single execution are different. I
> maybe wrong.”
>
> I was referring to the execution of a single line in the plan.
> eg for a nested loop: there will be multiple executions of the plan lines
> that hit the joined table (assuming multiple rows in the driving table),
> the e-rows represents the number of rows the CBO expects to find on one
> iteration.
>
> Assuming one statement execution. A-rows will be the total number of rows
> flowing from that row source from the entire execution of the statement. So
> you would have to multiply the E-rows by the number of times the line was
> hit
>
> Hopefully that’s clearer now
>
> Andy
>
>
>

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