Re: Rows processed by a sql but not retrieved

I think the aim of the original question is towards the things Oracle doesn't tell us.

Rowsource execution stats, for example, will tell us: "this tablescan returned 5000 rows" but they won't tell us "during this tablescan I examined 10M rows and discarded all but 5000" - we have to infer some of the numbers some other way, and there are some numbers we cannot infer.

There is an enhancement request on OTN (as was) asking for at least 2 numbers on each line of an execution plan - one for the number of rows going into the operation the other for the number coming out - in some cases you could infer the number going in from other information, of course, but having all components reported on the same line would be helpful. At the time I suggested that you could need more than two - on a hash join, for example, you have the number of rows used to probe the hash table (using the access predicate), the number of rows that find no match, the number of rows generated by matching (on the access predicate) and the number of rows left after applying the filter predicate. (The number of rows used to probe is, of course, the number output from the child operation)

Regards
Jonathan Lewis

On Tue, 9 Feb 2021 at 16:23, Jared Still <jkstill_at_gmail.com> wrote:

> Hello Moustafa,
>
> Your question is quite broad in scope, and there are quite a number of
> things that could be considered.
>
> Rather than enumerate all I can think of, I would direct you to v$sqlstats.
>
> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS.html#GUID-495DD17D-6741-433F-871D-C965EB221DA9
>
>
>

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