Re: Rows processed by a sql but not retrieved

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

There you can see the buffer_gets and rows_processed, which may give some rough estimate of what you are looking for. (in conjunction with parse_calls and/or executions)

Any direct IO done on the objects will not be reflected in the buffer_gets, I don't know about the rows_processed column.

Some experimentation would be in order for the version of oracle you are working with.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill

On Fri, Feb 5, 2021 at 5:21 PM Moustafa Ahmed <moustafa_dba_at_hotmail.com> wrote:

> Hello listers
>
> Regardless of what oracle calls them in many views, let’s say a sql (no
> exadata) gets more rows than its need (like a filter predicate not an
> access one) which means we will be throwing away unneeded rows..
> Is there a stat or a way to tell how many rows were thrown away as not
> needed?
>
> For instance I saw at a friend shop that when they hit exacta storage cell
> it affects IO and it makes smart scan degrades terribly and it is not clear
> if smart scan are not actually (cell off loading anymore) or just became
> slower with the issue..
>

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