RE: SQL performacne issue due to bind mismatch

Oops forgot to mention the version information , Running on Oracle 11.1.0.7 on 
Sun Soalris 5.10 
 



From: xrajx@xxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: SQL performacne issue due to bind mismatch
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
 
 
                                          

--
http://www.freelists.org/webpage/oracle-l

Other related posts: