Re: Impact of SQL Dynamic on Library cache

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Fri, 8 Sep 2000 15:58:11 +0100
Message-Id: <10613.116497@fatcity.com>

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_at_quickdoc.co.uk' ; 'oracle-l_at_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_at_cetelem.fr=20

------=_NextPart_000_0072_01C019AD.96C08590
Content-Type: text/html;

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Impact of SQL Dynamic on Library cache</TITLE>
<META content=3D"text/html; charset=3Diso-8859-1" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Which version of Oracle? If 8.1.6 use=20

CURSOR_SHARING ..</DIV>

<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>K Gopalakrishnan</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Bangalore, INDIA</FONT></DIV>
<BLOCKQUOTE=20

style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20
 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black">From:=20
 <A href=3D"mailto:luc.demanche_at_cetelem.fr"=20 title=3Dluc.demanche_at_cetelem.fr>DEMANCHE Luc (Cetelem)</A> </DIV> <DIV style=3D"FONT: 10pt arial">To: <A=20 href=3D"mailto:'oracledba_at_quickdoc.co.uk'"=20 title=3Doracledba_at_quickdoc.co.uk>'oracledba_at_quickdoc.co.uk'</A> ; <A=20 href=3D"mailto:'oracle-l_at_fatcity.com'"=20 title=3Doracle-l_at_fatcity.com>'oracle-l_at_fatcity.com'</A> </DIV> <DIV style=3D"FONT: 10pt arial">Sent: Friday, September 08, = 2000 10:50=20
 AM</DIV>
 <DIV style=3D"FONT: 10pt arial">Subject: Impact of SQL Dynamic = on Library=20
 cache</DIV>

  <DIV><BR></DIV>
  <P><FONT face=3DArial size=3D2>Hi all,</FONT> </P>
  <P><FONT face=3DArial size=3D2>We have a stored procedure who executes =

a least 250=20
000 to 500 000 SQL Dynamic statement like this : begin <FONT face=3DArial=20

  size=3D2>&nbsp;&nbsp;&nbsp; bla bla ...</FONT> <BR><FONT face=3DArial=20
  size=3D2>&nbsp;&nbsp;&nbsp; ...</FONT> <BR><FONT color=3D#000000=20
  face=3D"Courier New" size=3D1>&nbsp; matrice&nbsp;&nbsp;&nbsp;&nbsp; =

:=3D=20
 ' from = t_finclvf'<FONT=20
 color=3D#000000 face=3D"Courier New" size=3D1>;   = 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'<FONT=20
 color=3D#000000 face=3D"Courier New" size=3D1> || code_vendeur; = <FONT=20
 color=3D#000000 face=3D"Courier New" size=3D1>  = 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 <FONT=20
 color=3D#0000f0 face=3D"Courier New" size=3D1>' where ' || distinction || '_aamm>=3D' || date1=20 =
||            <= /FONT>=20
 ' and = '<FONT=20
 color=3D#000000 face=3D"Courier New" size=3D1> || distinction = || <FONT=20
 color=3D#0000f0 face=3D"Courier New" = size=3D1>'_aamm<=3D' || date2;    ...    ...   =20
 dbms_sql.parse ( = curseur ,=20
 selection || matrice || qd_tps || qd_codif || qd_prod || qd_soc ,=20 dbms_sql.native );

  <P><FONT face=3DArial size=3D2>&nbsp;&nbsp; bla bla ...</FONT> </P>
  <P><FONT face=3DArial size=3D2>end;</FONT> </P>
  <P><FONT face=3DArial size=3D2>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 = <FONT=20
 face=3DArial size=3D2>Cetelem Tel.:=20
 01.46.39.14.49 luc.demanche_at_cetelem.fr = </BLOCKQUOTE></BODY></HTML> Received on Fri Sep 08 2000 - 09:58:11 CDT