SQL performacne issue due to bind mismatch
- From: rajesh koppada <xrajx@xxxxxxxxxxx>
- To: Posting <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Feb 2012 05:00:53 +0000
Hello
We are having performance issues in a SIBIEL vendor application.
The end user complains that some SIBIEL screens/query¡¯s runs fast some times
and slow at times. after further
investigation Oracle is generating multiple child cursors for these sql's, the
Plan hash value and predicate section is same for the both child cursors
including the no of rows return by cursors.
The reason we are getting 2 cursors are due to bind mismatch, I pulled the bind
variables for both child cursors from v$sql_bind_capture , the bind values for
the both cursors are exactly matching , however the application is declaring
different length of bind variables on each execution for the same query
resulting in multiple child cursors . When we approached vendor they suggested
a minor version upgrade and business is not ready to undergo this upgrade due
to some constraints.
The first 2 times oracle is doing hard parsing for both child cursors and
response time is 2 minutes per execution and 99%
percent of time is spent on CPU for parsing the sql (25+ tables join), Starting
from 3rd execution the product is declaring the same length binds hence Oracle
is reusing child cursor 1(Already parsed) with an a execution time of less than
30 seconds.
As these queries are aging out from cache from time to time the end user is
complaining that query runs some times faster and some time slower as they have
to execute the query 3 times to get faster response.
what are my options to resolve this from database side
The first option would be tune those queiries so that it runs faster for first
time ,As this is a SIBIEL vendor product all the queries are generated
dynamically by the product based on the options chosen on the screen by end
user and we are not sure how many sibiel screens we are seeing this issue.
Any suggestions??
Thanks
Raj
--
//www.freelists.org/webpage/oracle-l
Other related posts:
