| Query Performance issue [message #422538] |
Thu, 17 September 2009 05:53  |
bhas_m
Messages: 1
Registered: September 2009
|
Junior Member |
|
|
Hi,
I am running with Oracle 10.2.0.1 on Solaris platform. Once in a while overall DB performance becomes bad and based on the AWR report, below query was consuming more than 80% of the DB time.
select a1.bbid from fc__KeyTable_1098 u1, fc__KeyTable_1040 d1, f
fc__KeyTable_1016 a1 where a1.bbid = u1.bbid and d1.bbid = u1.bbi
d and u1.keyValue = :1 and d1.keyValue = :2 and a1.keyValue = (se
lect /*+ INDEX(u1) USE_NL(d1) USE_NL(a1) */ max(a1.keyValue) from
fc__KeyTable_1098 u1, fc__KeyTable_1040 d1, fc__KeyTable_1016 a1
where a1.bbid = u1.bbid and d1.bbid = u1.bbid and u1.keyValue =
:3 and d1.keyValue = :4)
I generated the awr sql report for the period I has this issue and also for the period before the this performance issue. As per these reports (attached with this thread), execution plan of the same query is changed.
Please suggest how can I get rid of this issue.
Regards
Bhaskar
|
|
|
|
| Re: Query Performance issue [message #422575 is a reply to message #422538] |
Thu, 17 September 2009 08:25  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) What changed between the query working, and the query running slowly?
2) Are those fc__keytable_... tables pary of your application?
|
|
|
|
| Re: Query Performance issue [message #422611 is a reply to message #422575] |
Thu, 17 September 2009 19:41 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You are using bind variables. When you first run the SQL, it will peek at the bind variable values and optimise the SQL based on those values. Every subsequent run uses the same plan as the first run.
If you don't run the SQL for a while, it gets aged out of the SGA. The next time it is run it peeks AGAIN at the bind variables. If by chance these are "unusual" values, Oracle may choose an "unusual" plan. Unfortunately you are then STUCK with this unusual plan until it is aged out of the SGA again.
An INSTANT fix is to flush the shared pool. This will negatively impact the rest of the database, but it will temporarily fix your problem (until the next time).
A more permanent fix is to use Hints, Outlines / Plan Stability, or update your code to specifically handle special cases.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
