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 ) 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
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).
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 🙂
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
Definitely. And given that these SQL’s are typically single-use, they’d be quickly aged out.
Just upgraded my daily check-routines for finding sqls don’t using bind-literals.
thanks for tuning !
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
where force_matching_signature > 0
group by force_matching_signature , MODULE , ACTION
having count(distinct sql_id) > 1
order by 2 desc
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’?
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
Very many thanks.