Application Design and ORA – 4031

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
that

among 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.

Tags: databaseora-4031oracle