Problem solve Get help with specific problems with your technologies, process and projects.

Pinpoint bind variables

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.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close