I think the results might be misleading. Add these columns:
MIN(sql_text), MAX(sql_text)
to the query. I have some SQLs that are vastly different, but produce
the exact same plan under 9.2.0.5.0 with stats and indexed column
histograms collected (i.e. more than just a plan difference).
Thoughts?
Rich
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Baumgartel, Paul
Sent: Thursday, April 20, 2006 8:49 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Spotting the real cause of a Production slowdown
(10g)
And to help find those same-but-for-literal-values SQLs (which
typically have the same optimizer plan), you can use this query, which I
have found very useful:
select plan_hash_value, count(*) from v$sql
where plan_hash_value > 0
group by plan_hash_value having count(*) > 4 --or whatever
number you like
order by count(*);
Paul Baumgartel