RE: SQL performacne issue due to bind mismatch
Date: Tue, 21 Feb 2012 05:10:43 +0000
Message-ID: <BAY158-W52BFB1E371802EA8DA7E6DB6670_at_phx.gbl>
Oops forgot to mention the version information , Running on Oracle 11.1.0.7 on Sun Soalris 5.10
From: xrajx_at_hotmail.com
To: oracle-l_at_freelists.org
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-lReceived on Mon Feb 20 2012 - 23:10:43 CST