Home » RDBMS Server » Performance Tuning » Query Performance issue (Oracle,, Solaris)
Query Performance issue [message #422538] Thu, 17 September 2009 05:53 Go to next message
Messages: 1
Registered: September 2009
Junior Member


I am running with Oracle 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.

Re: Query Performance issue [message #422575 is a reply to message #422538] Thu, 17 September 2009 08:25 Go to previous messageGo to next message
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 Go to previous message
Messages: 3727
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
Previous Topic: update statement issue
Next Topic: alternate for Table Partitioning
Goto Forum:

Current Time: Fri Aug 18 20:50:41 CDT 2017

Total time taken to generate the page: 0.19863 seconds