A Quick Method to Identify Performance Gains y Using Bind Variables

I'm on a quest to locate some performance gains in a clients database quickly. One of the key factors in performance and scalability is to avoid parsing and latching. As we all know BIND variables can help developers build robust scalable applications.

When we tell developers to use BIND variable they invariably say they are, or that the statements that might not have bind variables are not degrading system performance (much) so they are not worth fixing. Remember too, that everytime code is touched in an app it has to be re-tested, and the possibility of side effects or bugs creeping into the system is a real danger.

So, what we really want to do is identify the statement(s) that need to be fixed, not admonish the devleopers to fix them all (good idea, not practical).

So here's a query that can help. Run this periodically, adjusting the length of the substring will affect the results.

Query to check the SGA for duplicate SQL statements as a possible candidate for bind variable use

select count(*), substr(sql_text, 1, 70) from v$sql group by substr(sql_text, 1, 70) order by 1 DESC;

What this does is return duplicate string from the SQL_TEXT in the SGA. The query depends on the fac tthat the BIND variable typically go into the WHERE clause, at the end of the SL statement.

On one production server I ran this on today I found 8000+ copies of essentially the same SQL statement in memory, differing only by the literal values.

Of course, you have to apply a big does of common sense before drawing ocnclusions but this query can give you some clues, and allows your developers to grep for the offending statement, which helps them as well.

Need Help?
Clark Fork Data Systems, Inc. can assist you with any of your Oracle database administration needs.

Call today! 415-652-2015
Email gpmilliken@clarkforkdata.com