Bindless Statements or How to Pinpoint the Reason of ORA-04031
SQL statements not using bind variables are just messing up your library cache and easily can cause ORA-04031 (unable to allocate %s bytes of shared memory) for other users. All attempts to increase shared pool or issue 'alter system flush shared_pool' do not eliminate the nature of the problem and can help only for a short while.
To bust the problem with ORA-04031 it is necessary to find the actual reason. Very often it happens to be SQL statements that do not use bind variables.
The "Note:146599.1" (Diagnosing and Resolving Error ORA-04031) gives an example of SQL query which is able to show some parts of those SQL statements:
SELECT substr(sql_text,1,40) "SQL", count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,40) HAVING count(*) > 80 ORDER BY 2;
But we do not consider this query as an appropriate solution, and that is why:
After tracing the query and parsing trace file with OraSRP, you can see the following picture:
Response Time Components Summary
|Response Time Component||Duration||# Calls||- Duration per Call -|
|direct path write||6.15s||41.1%||963||0.006386s||0.00s||0.17s|
|direct path read||4.15s||27.7%||1,007||0.004121s||0.00s||0.32s|
|SQL*Net message from client||0.02s||0.1%||4||0.005000s||0.00s||0.01s|
|SQL*Net message to client||0.00s||0.0%||4||0.000000s||0.00s||0.00s|
Look carefully at 'direct path read/write' waits. Two thirds of our time we are just sorting. Why ? Because v$sqlarea is built up on x$kglcursor with grouping by several columns and that gives us these 'direct path read/write' waits.
Anyway, the best that query can show is only some shreds of real statements.
Another way (which you can try in real life) was proposed by Tom Kyte back in 2000. Answering question about "Library cache latch contention", Tom proposed the following PL/SQL code which detects statements with no bind variables:
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 /
Quite rough solution, but anyway, it can be accepted as a working one. It's funny to observe how this algorithm is cutting out numeric and text constants. But it is a bit pity to see the same 'v$sqlarea' being used in here, impacting performance of Oracle Server. However, if we are limited to use only SQL and PL/SQL it is probably the best way.
At this point there could come up an idea to not restrict ourselves with SQL. And finally, we created simple script bindless.py which gives almost the same as Tom Kyte's does, but puts less impact on Oracle Server and is reasonable faster.
In fact, this script could be written in Perl and even in ksh probably, but we have chosen Python, because it is just more fun for us.
Here what is happening in the script:
- using sqlplus, run query 'select sql_text from v$sql'
- put contents of spool file into 'hash'; it will remove duplicates coming from v$sql (v$sqlarea is doing 'group by' for the same reason)
- with 'regular expressions' a bit more accurately and sure faster that in Tom's case, do replace numeric and text constants with symbols '@' and '#' accordingly.
- build up a new 'hash' where 'key' is a "post-replace" sql statement and the 'value' is number of occurencies.
- sort new 'hash' by 'value' and print out SQL statements which occurs in library more than hundred times (or whatever number we specify at the script startup)
- All set! You can start to pull out owners of "bad" sql statements and encourage them to use bind variables in their SQL.