Home » RDBMS Server » Performance Tuning » Query Performance issue (Oracle, 10.2.0.1, Solaris)
Query Performance issue [message #422538] Thu, 17 September 2009 05:53 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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: Mon Dec 05 02:54:55 CST 2016

Total time taken to generate the page: 0.11785 seconds