SQL statements using literals

Posted by

16 years ago, someone “Ask-ed Tom” how to find those SQL statements that were not using bind variables.   You can see the question here (because we don’t delete stuff ever Smile) but I’ll paraphrase the answer below:

Tom took the following approach

  • take a copy of SQL statements in the library cache
  • create a routine that would hunt for constants in the SQL text (that is, numbers and anything within quotes) and replace them with place holders
  • then count the resultant SQL’s for duplicates

create table t1 as select sql_text 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
    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;
    for i in 1 .. length( p_query )
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
            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);
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
 order by 2

It’s a utility I used many times over the years.  But times change and it’s always good to reflect on the tools and techniques you use, and whether anything has come along in later versions to assist you.  Consider the second bullet point above:

create a routine that would hunt for constants and replace them with place holders

That sounds a lot like the process that must be followed when “cursor_sharing” is enabled (and set to “force”), the only difference being the place holders would be come bind variables.  With that in mind, even if we are not using cursor_sharing, then the information that got added to the library cache when cursor_sharing was introduced all those years ago can now be used to assist us.

In V$SQLSTATS, there is a column FORCE_MATCHING_SIGNATURE which is a “signature” (or hash value) representing a SQL statement that has been converted to allow for cursor_sharing = force.  So that becomes the perfect grouping mechanism to identify repeated statements using literals.  For example:

SQL> select force_matching_signature, count(*)
  2  from  v$sqlstats
  3  where force_matching_signature > 0
  4  group by force_matching_signature
  5  having count(*) > 10
  6  order by 2 desc;

------------------------- ----------
      7756258419218828704         73
     15993438058742417605         16
     15893216616221909352         15
     14052954841849993177         12
     10493170372233636846         11

5 rows selected.

SQL> select sql_text from v$sqlstats
  2  where FORCE_MATCHING_SIGNATURE = 7756258419218828704;

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 148525
 and bitand(FLAGS, 128)=0

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8448 
and bitand(FLAGS, 128)=0

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 6309 
and bitand(FLAGS, 128)=0

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 148524
 and bitand(FLAGS, 128)=0

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8442 
and bitand(FLAGS, 128)=0

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8594 
and bitand(FLAGS, 128)=0

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 148693
 and bitand(FLAGS, 128)=0

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 8602 
and bitand(FLAGS, 128)=0


Identification of literal SQL has become trivial. Fixing it ? Well…that’s another story Smile


  1. Hello Connor,

    The force_matching_signature is not suitable for PL/SQL anonymous blocks because they all have force_matching_signature=0. The Tom approach is.
    For instance, I used to work with a database where KGLH0 heap grew constantly leading to ORA-4031.
    The reason was a huge number of anonymous blocks like ‘begin :var := expression;end;’, such as:
    ‘begin :1:=100/500;end;’, ‘begin :1:=1002/2003;end;’ and so on. It was a silly developer realization of a “flexible” application logic. We rewrote it to 10-30 distinct anonymous blocks using bind variables.

    It is slightly offtopic but just for information: the unshared SQL against SYS_FBA_TRACKEDTABLES is due to bug https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=15931756.8 which is still not resolved in the latest DBBP (Bundle Patch).

    Yours faithfully,
    Mikhail Velikikh.

  2. Hi Mikhail,

    Thanks for stopping by with the additional info. People using literals in (multiple) PLSQL blocks sounds counter-intuitive to the whole idea of PLSQL, but then again, we had a question on AskTom just a few days ago from someone was passing PL/SQL block text as parameters into a PL/SQL procedure, which was then used to dynamically build another PL/SQL block, which in itself had various “execute immediate” calls in to produce yet a 3rd “layer” of PL/SQL block to be dynamically run…..

    Sometimes … you just wonder 🙂

  3. Hi, what about trying to find the SQL statements that already left memory? I mean, what if we want to look to a time period (sample_time) say last 24h, and retrieve the statements that have been using literals?

    I have came across this sql but not sure if it can be totably reliable:

    select a.force_matching_signature, count(distinct a.sql_id) from WRH$_ACTIVE_SESSION_HISTORY a, WRH$_SQLTEXT B where (a.SNAP_ID between 1 and 20)
    and a.SQL_ID is not null and a.sql_id=b.sql_id
    and a.force_matching_signature > 0
    group by a.force_matching_signature
    having count(distinct a.sql_id) > 1
    order by 2 desc

    1. The other way of looking at is which database module is causing hard parses that will answer question of what can be done.
      select force_matching_signature, count(distinct sql_id) , MODULE , ACTION
      from dba_hist_sqlstat
      where force_matching_signature > 0
      group by force_matching_signature , MODULE , ACTION
      having count(distinct sql_id) > 1
      order by 2 desc

  4. hi,
    just looking at this
    less code, better performance from developer live.
    and its a fascinating topic. I assume that your cursor_sharing is set to exact for the demos or are you issuing a session level to set it to ‘force’?

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.