V$SqlStats vs V$SqlStats_Plan_Hash, different columns?

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 from V$SQL and V$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.