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@xxxxxxxxxxx>
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..
