RE: Impact of SQL Dynamic on Library cache

Definitely you need to use bind variables.

-----Original Message-----
From: DEMANCHE Luc (Cetelem) [mailto:luc.demanche_at_cetelem.fr] Sent: Friday, September 08, 2000 11:46 AM To: Multiple recipients of list ORACLE-L Subject: Impact of SQL Dynamic on Library cache

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 Received on Fri Sep 08 2000 - 13:17:22 CDT