Much has been written on designing good application (which uses bind variables) so as to have better performance and avoid shared pool memory issues. Tom Kyte explains this on one of his post consequences of not using bind variables
If you do not use bind variables and you flood the server with
hundreds/thousands of unique queries you will
-run dog slow
-consume a
ton of RAM (and maybe run out)
-not scale beyond a handful of users, if
thatamong other really bad side effects.
This is very true and have observed it myself many times. But today we will see one more kind of bad application design which I came across working on a ORA – 4031 issue.
Environment was already using CURSOR_SHARING=SIMILAR (application developers were not ready to change code, so it was Kind of quick fix) to force bind variables but we were still observing ORA – 4031 errors. Query on V$SGASTAT gave us following results
http://askoracledba.wordpress.com/2008/04/27/application-design-and-ora-4031/sgastat/
Out of 4 gb allocated to shared pool, around 2.4 G is being used by sql area and library cache indicating that sql are consuming lot of memory in shared pool.
Whenever I observe high memory usage for Sqlarea, I run following sql statements.
1) select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss where sa.address=ss.KGLHDPAR and sa.version_count > 50 order by sa.version_count ;
- This one is to find if child cursors are being generated.
2) Use script from Asktom website to find if application is using Bind variables. You can find ,more information by clicking here
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
/
On executing first statement , there were no rows returned. But on executing second query , got following results
SELECT NVL(SUM(FEE),:"SYS_B_@") FROM (SELECT NVL(SUM(FREECALLFEE)+SUM(SALARY)+ SUM(SALARY@)+SUM(FREEINFOFEE),:"SYS_B_@") FEE FROM SCOTT.A_BL_C@_@_F_@ WHERE MSISDN=:MSISDN UNION ALL SELECT NVL(SUM(COMM),:"SYS_B_@") FEE FROM SCOTT.T_BL_SM_F_@ WHERE MSISDN=:MSISDN ) 150 SELECT NVL(SUM(FEE),:"SYS_B_@") FROM (SELECT NVL(SUM(FREECALLFEE)+ SUM(SALARY)+SUM(SALARY@)+SUM(FREEINFOFEE),:"SYS_B_@") FEE FROM SCOTT.A_BL_C@_@_G_@ WHERE MSISDN=:MSISDN UNION ALL SELECT NVL(SUM(COMM),:"SYS_B_@") FEE FROM SCOTT.T_BL_SM_G_@ WHERE MSISDN=:MSISDN ) 150 SELECT NVL(SUM(FEE),:"SYS_B_@") FROM (SELECT NVL(SUM(FREECALLFEE)+SUM(SALARY)+ SUM(SALARY@)+SUM(FREEINFOFEE),:"SYS_B_@") FEE FROM SCOTT.A_BL_C@_@_H_@ WHERE MSISDN=:MSISDN UNION ALL SELECT NVL(SUM(COMM),:"SYS_B_@") FEE FROM SCOTT.T_BL_SM_H_@ WHERE MSISDN=:MSISDN ) 150 TRUNCATE TABLE SCOTT.A_BL_C@_@_L_@ 310 TRUNCATE TABLE SCOTT.A_BL_C@_@_M_@ 310 TRUNCATE TABLE SCOTT.A_BL_C@_@_P_@ 310
Ideally this should not have returned any query as we are forcing bind variables.
But looking closely we find that there around 620tables with name of format SCOTT.A_BL_C@_@_M_@ and SCOTT.A_BL_C@_@_P_@(basically @ will be actually a Numeric or a literal) which is cause for so many versions for sql. Application is running set of 20 queries on these tables , so in total there are 20 X620 queries being run , leading to high usage of shared pool memory.
Apart from that we see that all these tables are being truncated .This actually invalidates the cursors and causes a hard parse along with causing shared pool fragmentation
Based on our findings, we gave following recommendation which could be performed to alleviate (if not resolve) the issue.
- Have the shared pool set to 4 Gb itself
- Turn these 620tables into two paritioned tables. This will avoid multiple sql’s in shared pool. But this can have impact on the time taken for query to run and had to be tested
- Instead of truncate tables, use delete operation so that we do not invalidate the cursor. But again catch here is that we will have more redo generation and if data being loaded uses append operation, size for these tables will grow.
As you see this is basically Application design issue and made a application which is not scalable and also leading to downtime.
One Comment for “Application Design and ORA – 4031”