Hi there,
V$SQLSTATSis a beautiful view for information about sql statements. you might say v$sql has that information too which is correct but V$SQLSTATSis much more faster than v$sql and keeps data about sql statements for a longer time than v$sql.
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS.html
However, the
V$SQLSTATS
view differs fromV$SQL
andV$SQLAREA
in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool).
so it is a very useful view to check. V$SQLSTATS contains one row for one sql statements. that will summarize some information about sql statements because that sql may have different execution plans but V$SQLSTATS will sum up them. if you want to get data for same sql with different execution plans then you must check V$SQLSTATS_PLAN_HASH view which is very similar to V$SQLSTATS. only difference is V$SQLSTATS_PLAN_HASH has more than one row for each sql_id with different execution plan.
Please mind that V$SQLSTATS also has PLAN_HASH_VALUE column to identify execution plan but this column stores only the first cursor’s execution plan not the others.
So when you check the columns for V$SQLSTATS_PLAN_HASH view at the documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS_PLAN_HASH.html it says “same with V$SQL_STATS” but that is not entirely correct (and also not wrong). When you compare the columns (db version 19.6) you will see that those columns are not listed in V$SQLSTATS_PLAN_HASH
DIRECT_READS
DELTA_DIRECT_READS
CON_DBID
OBSOLETE_COUNT
AVOIDED_EXECUTIONS
DELTA_AVOIDED_EXECUTIONS
so my initial thought was that should be a documentation bug but then I checked underlying X$ tables; X$KKSSQLSTAT and X$KKSSQLSTAT_PLAN_HASH (easy to understand which one is which). Those X$ tables has the exact same columns (including the columns above) so documentation is partially correct. These X$ tables (virtual tables, views, memory arrays whatever you say) literally contains same data but definition of V$SQLSTATS_PLAN_HASH view has missing columns. I assume these columns will be added on further releases. Both of the views are great to explore the sql statements.
wish you health days.
All lives matter.