SQL statements using literals

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
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
 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;

 FORCE_MATCHING_SIGNATURE   COUNT(*)
------------------------- ----------
      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;

SQL_TEXT
----------------------------------------------------------------
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

11 comments

mvelikikh says:

May 30, 2016 at 11:10 am

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.

Reply
connormcdonald says:

June 1, 2016 at 1:31 pm

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 🙂

Reply
Ricardo Pereira says:

April 12, 2017 at 11:14 pm

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
;

Reply
1. connormcdonald says:
  
  April 13, 2017 at 7:58 am
  
  Definitely. And given that these SQL’s are typically single-use, they’d be quickly aged out.
  
  Reply
Matthias Rogel says:

August 20, 2018 at 1:30 pm

Hi Connor,
Just upgraded my daily check-routines for finding sqls don’t using bind-literals.
thanks for tuning !
Matthias

Reply
Matthias Rogel says:

August 20, 2018 at 1:31 pm

bind-variables

Reply
1. Arvind says:
  
  January 11, 2020 at 3:27 am
  
  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
  /
  
  Reply
Pingback: Developer Live wrap up – Learning is not a spectator sport
Alan Mckeown says:

August 28, 2020 at 7:33 pm

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’?

Reply
1. Connor McDonald says:
  
  August 28, 2020 at 8:07 pm
  
  In all my demos it was the default (cursor_sharing = exact). The faster demo were just showing what happens when you write “good” code, ie, binding in the application
  
  Reply
  1. Alan Mckeown says:
    
    August 28, 2020 at 9:05 pm
    
    Very many thanks.
    
    Reply