0

I understand statspack (from Oracle) can display query plan information with snap level 6. I understand that if there is a variation in plan statistics then the same query plan will have multiple entries (same hash value) for same query plan in the statspack report, for the given time period betwen snapshots.

If there are two query plans which are identical but have differences in where clause, are they considered different in statspack or "normalized" to one plan? If they are normalized, then is every value of plan node (estimated and actual cost) averaged in normalized plan?

Thank you Sameer

user179056
  • 604
  • 19
  • 36

1 Answers1

0

I asked the same question on the Oracle forum and got this answer "Statspack (and AWR) accumulate SQL statistics by sql_id (hash_value) and plan_hash_value. If two child cursors for a query have the same plan_hash_value their results will be aggregated. In fact, if you're using statspack, it used to aggregate ALL the child cursor statistics - even if there were multiple different plans. (I haven't checked that for the latest versions of Oracle, though)"

Ref: https://forums.oracle.com/thread/2551269

regards Sameer

user179056
  • 604
  • 19
  • 36