Message-Id: <10613.116497@fatcity.com> From: "K Gopalakrishnan" Date: Fri, 8 Sep 2000 15:58:11 +0100 Subject: Re: Impact of SQL Dynamic on Library cache This is a multi-part message in MIME format. ------=_NextPart_000_0072_01C019AD.96C08590 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Impact of SQL Dynamic on Library cacheWhich version of Oracle? If 8.1.6 = use CURSOR_SHARING .. K Gopalakrishnan Bangalore, INDIA ----- Original Message -----=20 From: DEMANCHE Luc (Cetelem)=20 To: 'oracledba@quickdoc.co.uk' ; 'oracle-l@fatcity.com'=20 Sent: Friday, September 08, 2000 10:50 AM Subject: Impact of SQL Dynamic on Library cache Hi all,=20 We have a stored procedure who executes a least 250 000 to 500 000 SQL = Dynamic statement like this :=20 begin=20 bla bla ...=20 ...=20 matrice :=3D ' from t_finclvf';=20 selection :=3D 'select sum(fcp_mtdistot),sum(fcp_nbdistot),' || = 'sum(fcp_nbfoulee),sum(fcp_mtfoulee)';=20 qd_soc :=3D ' and fcp_socfin=3D' || code_societe || ' and fcp_vdrfin=3D' || code_vendeur;=20 qd_prod :=3D ' and fcp_codif_natudos=3D''R''' || ' and fcp_codif_marketi !=3D 9';=20 qd_codif :=3D '';=20 qd_tps :=3D ' where ' || distinction || '_aamm>=3D' || date1 || ' and ' || distinction || '_aamm<=3D' || date2; ...=20 ...=20 dbms_sql.parse ( curseur , selection || matrice || qd_tps || = qd_codif || qd_prod || qd_soc , dbms_sql.native ); =20 bla bla ...=20 end;=20 The problem is the library cache ratio drop at 0.01%. What can we do = ? Rewrite for use bind variables ? =20 We are on Oracle 734 and we go on 816 in 2 weeks.=20 shared_pool_size =3D 100m=20 TIA=20 Luc Demanche=20 Cetelem=20 Tel.: 01.46.39.14.49=20 luc.demanche@cetelem.fr=20 ------=_NextPart_000_0072_01C019AD.96C08590 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Impact of SQL Dynamic on Library cache
Which version of Oracle? If 8.1.6 use=20 CURSOR_SHARING ..
 
K Gopalakrishnan
Bangalore, INDIA
----- Original Message -----
From:=20 DEMANCHE Luc (Cetelem)
To: 'oracledba@quickdoc.co.uk' ; 'oracle-l@fatcity.com'
Sent: Friday, September 08, = 2000 10:50=20 AM
Subject: Impact of SQL Dynamic = on Library=20 cache

Hi all,

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

begin
    bla bla ...
    ...
  matrice     = :=3D=20 ' from = t_finclvf';

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

  = qd_soc     =20 :=3D ' and=20 fcp_socfin=3D' ||=20 code_societe=20 = ||
           &= nbsp;    =20 ' and = fcp_vdrfin=3D' || code_vendeur; =

  = qd_prod    =20 :=3D ' and=20 fcp_codif_natudos=3D''R'''=20 = ||
           &= nbsp;    =20 ' and = fcp_codif_marketi !=3D=20 9'; =

  qd_codif = :=3D=20 '';

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

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

   bla bla ...

end;

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

TIA



Luc Demanche =
Cetelem
Tel.:=20 01.46.39.14.49
luc.demanche@cetelem.fr =