Bind variables vs Literals


Taking just a few minutes to look at the output from a CPU monitoring tool like 'sar', 'Task Manager' and the parsing statistics will convince you of the worth of bind variables. But once you accept the evidence of the enormous benefits of bind variables (as we all will do), what can we do besides trawling through v$sqlarea statement-by-statement looking for literals?

The following process may assist (you can see Tom Kyte's post here)

The logic is quite simple - look for anything in quotes (since these will be "implicit" if bind variables are in use), and any plain numerics as indicators of literals. Then by populating a column with the "de-literalised" version of the SQL, we can assess which statements should be tackled first.


create table t1 as select * from v$sqlarea;
alter table t1 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 t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100    -- or any other limit you would like to choose (the lower the better)
 order by 2
/