Impact of SQL Dynamic on Library cache

From: DEMANCHE Luc (Cetelem) <luc.demanche_at_cetelem.fr>
Date: Fri, 8 Sep 2000 11:50:13 +0200
Message-Id: <10613.116496@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0197A.317C765E
Content-Type: text/plain;

charset="iso-8859-1"

Hi all,

We have a stored procedure who executes a least 250 000 to 500 000 SQL Dynamic statement like this :

begin

    bla bla ...
    ...
  matrice := ' from t_finclvf';

selection := 'select sum(fcp_mtdistot),sum(fcp_nbdistot),' || 'sum(fcp_nbfoulee),sum(fcp_mtfoulee)';

  qd_soc      := ' and fcp_socfin=' || code_societe ||
                 ' and fcp_vdrfin=' || code_vendeur; 

  qd_prod     := ' and fcp_codif_natudos=''R''' ||
                 ' and fcp_codif_marketi != 9';

qd_codif := '';

qd_tps := ' where ' || distinction || '_aamm>=' || date1 ||

' and ' || distinction || '_aamm<=' || date2;

...
...

dbms_sql.parse ( curseur , selection || matrice || qd_tps || qd_codif || qd_prod || qd_soc , dbms_sql.native );

bla bla ...

end;

The problem is the library cache ratio drop at 0.01%. What can we do ? Rewrite for use bind variables ?
We are on Oracle 734 and we go on 816 in 2 weeks. shared_pool_size = 100m

TIA Luc Demanche
Cetelem
Tel.: 01.46.39.14.49
luc.demanche_at_cetelem.fr

------_=_NextPart_001_01C0197A.317C765E
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>

<TITLE>Impact of SQL Dynamic on Library cache</TITLE>
</HEAD>
<BODY>

Hi all,

We have a stored procedure who = executes a least 250 000 to 500 000 SQL Dynamic statement like this = :

<P><FONT SIZE=3D2 FACE=3D"Arial">begin</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; bla bla ...</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; ...</FONT>
<BR><FONT COLOR=3D"#000000" SIZE=3D1 FACE=3D"Courier New">&nbsp; =

matrice     :=3D ' from t_finclvf';

= selection   :=3D 'select =
sum(fcp_mtdistot),sum(fcp_nbdistot),' || 'sum(fcp_nbfoulee),sum(fcp_mtfoulee)'; 
   = qd_soc      :=3D ' and = fcp_socfin=3D' || code_societe || 
            =      ' and fcp_vdrfin=3D' || code_vendeur; 
   = qd_prod     :=3D ' and =
fcp_codif_natudos=3D''R''' || 
            =      ' and fcp_codif_marketi !=3D 9';

= qd_codif :=3D '';

qd_tps = :=3D ' = where ' || = distinction || '_aamm>=3D' || date1 || 
           </FONT= > ' and = ' || = distinction || '_aamm<=3D' || date2;

<P><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp; ...</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp; ...</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;</FONT> <FONT =

COLOR=3D"#000000" SIZE=3D1 FACE=3D"Courier New">dbms_sql.parse ( = curseur , selection || matrice || qd_tps || qd_codif || qd_prod || = qd_soc , dbms_sql.native );

bla bla ...

end;

The problem is the library cache ratio = drop at 0.01%.  What can we do ?  Rewrite for use bind = variables ?  
 We are on Oracle 734 and we go on 816 = in 2 weeks.
 shared_pool_size =3D 100m

<P><FONT SIZE=3D2 FACE=3D"Arial">TIA</FONT>
</P>
<BR>
<BR>

<P><B><I><FONT FACE=3D"Arial">Luc Demanche</FONT></I></B><I></I>

Cetelem Tel.: 01.46.39.14.49 Received on Fri Sep 08 2000 - 04:50:13 CDT