Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is this a RAC database? If you add DBID and INSTANCE_NUMBER to the GROUP BY, are you still getting duplicate rows? –  Justin Cave Jul 5 '12 at 5:26
    
@JustinCave: This is not an RAC database (I have updated the question accordingly). –  René Nyffenegger Jul 5 '12 at 6:27
add comment

1 Answer

up vote 4 down vote accepted

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;
share|improve this answer
    
Thank you very much! –  René Nyffenegger Jul 5 '12 at 8:26
    
You're welcome. Thanks to you for your website - It's helped me many a time ;) –  FreshPhilOfSO Jul 5 '12 at 8:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.