RE: Spotting the real cause of a Production slowdown (10g)

From: Jesse, Rich <Rich.Jesse_at_qg.com>
Date: Thu, 20 Apr 2006 16:42:12 -0500
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE6697546112@QTEX1.qg.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Baumgartel, Paul
	Sent: Thursday, April 20, 2006 8:49 AM
	To: oracle-l_at_freelists.org
	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 

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 20 2006 - 16:42:12 CDT