| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Spotting the real cause of a Production slowdown (10g)
Once I've identified potential offenders with the script I posted, I run a second script to examine the SQL (I should have mentioned that):
select t2.sql_text from v$sql t1, v$sqltext t2
where t1.plan_hash_value = &plan_hash_value
and t1.hash_value = t2.hash_value
order by t2.hash_value, t2.piece
/
Rich's suggested addition to the original query makes sense.
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jesse, Rich
Sent: Thursday, April 20, 2006 5:42 PM
To: oracle-l_at_freelists.org
Subject: RE: Spotting the real cause of a Production slowdown (10g)
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.credit-suisse.com/legal/en/disclaimer_email_ib.html
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 21 2006 - 09:09:01 CDT
 |
 |