Oracle has stated in numerous publications that bind variables are a major reason of performance issues on commerical sites, resulting in non-scable applications and slow response times. This tip allows allows you to pinpoint whether you are using bind variables or not, identifies which queries are not using bind variables and highlights the worst offenders.
Not using bind variables in an OLTP setting can be a disaster -- it can be a major performance issue and leave you with a non-scalable application. Not only will it result in a lot of hard parsing (which results in slower, more resource-hungry queries) but also can lead to a lot of latching and, indirectly, more physical reads. You would prefer to see close to zero hard parsing in your statspack. You can also run the below code to check whether duplicate statements are being held in the view $SQLAREA; i.e., not using bind variables. Note: I have set to extract queries that appear more than 75 times -- this can be moved up or down depending on your system. I have come across variations of this script in various publications (e.g., "Expert One-On-One" by Thomas Kyte) but I've modied it to suit my application needs. This script has been tested on Oracle 8.1.6 and 8.1.7.
create table bind_table as select sql_text from v$sqlarea; alter table bind_table add sql_text_wo_constants varchar2(1000); create or replace function remove_constants( p_query in varchar2 ) return varchar2 as l_query long; l_char varchar2(1); l_in_quotes boolean default FALSE; begin for i in 1 .. length( p_query ) loop l_char := substr(p_query,i,1); if ( l_char = '''' and l_in_quotes ) then l_in_quotes := FALSE; elsif ( l_char = '''' and NOT l_in_quotes ) then l_in_quotes := TRUE; l_query := l_query || '''#'; end if; if ( NOT l_in_quotes ) then l_query := l_query || l_char; end if; end loop; l_query := translate( l_query, '0123456789', '@@@@@@@@@@' ); for i in 0 .. 8 loop l_query := replace( l_query, lpad('@',10-i,'@'), '@' ); l_query := replace( l_query, lpad(' ',10-i,' '), ' ' ); end loop; return upper(l_query); end; / update bind_table set sql_text_wo_constants = remove_constants(sql_text); select sql_text_wo_constants, count(*) from bind_table group by sql_text_wo_constants having count(*) > 75 order by 2 /
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.