When is a row added to DBA_HIST_SQLSTAT?

Question

I don't understand the criteria that entails an additional record in the dba_hist_sqlstat view. I have expected there to be at most one entry per sql_id and snap_id, that is, the following statement to return no record:

select 
  count(*),
  snap_id,
  sql_id
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id
having
  count(*) > 1
order by
  count(*) desc;

Yet, it returns many records, indicating that it is not possible, but the normality, that the same sql statement is captured multiple times per snap_id.

Additionaly, with more than one record per sql and snap period, how do I interpret the delta columns within this view? The documentation reads:

The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

This doesn't really make sense to me.

Update as per Justin's comment: this is not in a RAC environment: so even if I group by snap_id, sql_id, dbid, instance_number, the query returns multiple records per snap_id and sql_id.

Answer 1

Oracle collects SQL statistics for each SQL execution plan/sql_id (plan_hash_value), rather than just for a specific sql_id.

To verify that this is indeed the case, this query should return 0 rows:

select 
  count(*),
  snap_id,
  sql_id,
  plan_hash_value
from
  dba_hist_sqlstat
group by
  snap_id,
  sql_id,
  plan_hash_value
having
  count(*) > 1
order by
  count(*) desc;