Re: Performance Problem - select from dual

From: Oweson Flynn <Oweson.Flynn_at_liberty.co.za>
Date: Thu, 28 Sep 2000 09:36:21 +0200
Message-Id: <10633.118119@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0251_01C0292F.8FE49D60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

RE: Performance Problem - select from dualJust a thought that occurred to m=
e while I was perusing this thread - please enlighten me if my line of thou=
ght is incorrect. (I am in the mood to use 'high-faluting' grammar this mor=

ning!)

Would the  to_date('1970-01-01.09:30:00',   'YYYY-MM-DD.HH24:MI:SS') functi=
on be evaluated for every row in the 17 million (i.e. - 17 million times)? =
If so, it would help to have it 'pre-executed', and just add the (tstamp/86=

400000000) to it, and then do the to_char bit?

for example

DECLARE
Jan70 DATE :=3D TO_DATE( '1970-01-01.09:30:00', 'YYYY-MM-DD.HH24:MI:SS= ' );
BEGIN
RETURN( TO_CHAR( Jan70 + ( TStamp / 86400000000 ), 'dd-mm-yyyy' ));=20 END; =20

The way I understood the parse/execute procedure works in Oracle the PL/SQL=
 engine, the 'combined' statement/function would only be parsed once (as th=
e TStamp is a bind variable'), but the TO_DATE function inside the Return s=
tatement would be executed the 17 million times, after the TStamp is added =
to the 'constant' Jan70 date value.  If it is in a function/procedure which=
 is called the 17 million times, would the conversion/declaration be done 1=
7 million times, or would it be part of the initial parsing?  If so, maybe =

defining a 'global variable' in a package could be a way round that.

Any comments / corrections/ elucidations?

Regards
Oweson Flynn

The Flynn Consultancy
Tel: 082-600-7-006
Fax: (011) 782-9313
EMail: oef_at_icon.co.za

Original Message -----=20 From: Koivu, Lisa=20 To: Multiple recipients of list ORACLE-L=20 Sent: Wednesday, September 27, 2000 4:10 PM Subject: RE: Performance Problem - select from dual

This is so interesting. I have read before that SELECT ... FROM DUAL is = expensive in a procedure, and have seen it in traces. Can anyone explain w= hy? =20

  Thanks=20
  Lisa=20
  Ft. Lauderdale, FL, USA=20

-----Original Message-----=20
From: Toepke, Kevin M [mailto:ktoepke_at_cms.cendant.com]=20 Sent: Wednesday, September 27, 2000 8:25 AM=20 To: Multiple recipients of list ORACLE-L=20 Subject: RE: Performance Problem.=20

The SELECT from DUAL is a problem. It is not needed. Recode as follows.=

          BEGIN=20
                  RETURN(to_char(to_date('1970-01-01.09:30:00',=20
                                     'YYYY-MM-DD.HH24:MI:SS') +=20
                             (tstamp/86400000000),'dd-mm-yyyy'));=20
          END;=20

  If you have the space, create an index on the 17Mill row table that conta= ins=20
  all of the columns you need. Make sure the columns in the index are in th= e=20
  same order as the columns in ORDER BY clause -- reduce I/O plus no sortin= g=20
  required.=20

Kevin=20

>=20
>=20

  > Hi All,=20

>=20

  > Our customer has a report running under NT/Oracle 7.3.4 that=20
  > runs for a very=20
  > long time, nearly 16 hours. After investigating the program I=20
  > discovered two=20
  > things:=20
  > 1. A call to a procedure was taking nearly half the cpu, but=20
  > all it is, is a=20
  > select from dual to convert a timestamp column, that is passed to the=
 
  > procedure, to a date.=20
  > eg select tstamp_to_date(colname), ..... from table where ....=20
  > The function is:=20
  > BEGIN=20
  >   select to_char(to_date('1970-01-01.09:30:00',=20
  > 'YYYY-MM-DD.HH24:MI:SS') +=20
  > (tstamp/86400000000),'dd-mm-yyyy')=20
  >   into ts_date from dual;=20
  >   return ts_date;=20
  > END;=20

>=20

  > Would there be a great improvement if we pinned the function=20
  > into memory, or=20
  > should we just replace it all together which I believe is possible.=20

>=20

  > 2. The huge cursor, on a table of 17mill rows, that drives=20
  > the report is=20
  > using 7.5Gb of temporary tablespace because of the hash joins=20
  > of full table=20
  > scans & order by.=20

>=20

  > Would it be better to scan the large table and select the=20
  > records required=20
  > (maybe 1.5mill) into another table that is Primary keyed on=20
  > the required=20
  > Order and then report from that table so eliminating the=20
  > order by and the=20
  > need for the temporary tablespace??=20

>=20
>=20

  > Any help would be greatly appreciated.=20

>=20

  > Thanks=20
  > Ian=20
  > --=20
  > Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
  > --=20
  > Author: Biddell, Ian=20
  >   INET: Ian.Biddell_at_compaq.com=20

>=20

> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051=20 > San Diego, California -- Public Internet access / Mailing Lists=

  > --------------------------------------------------------------------=20
  > To REMOVE yourself from this mailing list, send an E-Mail message=20
  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20
  > the message BODY, include a line containing: UNSUB ORACLE-L=20
  > (or the name of mailing list you want to be removed from).  You may=20

  > also send the HELP command for other information (like subscribing).=20
>=20

  --=20
  Please see the official ORACLE-L FAQ: http://www.orafaq.com=20   --=20
  Author: Toepke, Kevin M=20
    INET: ktoepke_at_cms.cendant.com=20

  Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051=20
  San Diego, California        -- Public Internet access / Mailing Lists=20
  --------------------------------------------------------------------=20

To REMOVE yourself from this mailing list, send an E-Mail message=20 to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20 the message BODY, include a line containing: UNSUB ORACLE-L=20 (or the name of mailing list you want to be removed from). You may=20 also send the HELP command for other information (like subscribing).=20

This message may contain information which is confidential and subject to l=
egal privilege. If you are not the intended recipient, you may not peruse, =
use, disseminate, distribute or copy this message. If you have received thi=
s message in error, please notify the sender immediately by email, facsimil=

e or telephone and return and/or destroy the original message.

------=_NextPart_000_0251_01C0292F.8FE49D60 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>RE: Performance Problem - select from dual</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 size=3D2>Just a thought that occurred to me while I was perusing=

this=20
thread - please enlighten me if my line of thought is incorrect. (I am in t= he=20
mood to use 'high-faluting' grammar this morning!)</DIV>

<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>Would the&nbsp;=20
<STRONG>to_date('1970-01-01.09:30:00',&nbsp;</STRONG><FONT size=3D2><STRONG=

> =20
'YYYY-MM-DD.HH24:MI:SS') function be evaluated for every row in th= e 17=20
million (i.e. - 17 million times)? If so, it would help to have it=20 'pre-executed', and just add the (tstamp/86400000000) to i= t,=20
and then do the to_char bit?</DIV>

<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>for example</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>DECLARE</FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; Jan70 DATE&nbsp; :=3D TO_DATE( =20

'1970-01-01.09:30:00', 'YYYY-MM-DD.HH24:MI:SS' );</DIV> <DIV>BEGIN</DIV>
<DIV>   RETURN( TO_CHAR( Jan70 +</F= ONT><FONT=20
size=3D2> ( TStamp / 86400000000 ), 'dd-mm-yyyy' ));  <FONT= s
ize=3D2>END;  </DIV> <DIV> </DIV>
<DIV>The way I understood the parse/execute procedure works = in=20
Oracle the PL/SQL engine, the 'combined' statement/function would only be p= arsed=20
once (as the TStamp is a bind variable'), but the TO_DATE function inside t= he=20
Return statement would be executed the 17 million times, after the TStamp i= s=20
added to the 'constant' Jan70 date value.  If it is in a function/proc= edure=20
which is called the 17 million times, would the conversion/declaration be d= one=20
17 million times, or would it be part of the initial parsing?  If so, = maybe=20
defining a 'global variable' in a package could be a way round=20

that.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Any comments / corrections/ elucidations?</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Regards<BR>Oweson=20
Flynn<BR>------------------------------------------------------------------=

The=20
Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: <A= h
ref=3D"mailto:oef_at_icon.co.za">oef_at_icon.co.za</A></DIV> <BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0p= x; 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">Fro=

m:</B>=20
  <A href=3D"mailto:lkoivu_at_qode.com" title=3Dlkoivu_at_qode.com>Koivu, Lisa</A=
> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A href=3D"mailto:ORACLE-L_at_fat=

city.com"=20
 title=3DORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> </D= IV>
 <DIV style=3D"FONT: 10pt arial">Sent: Wednesday, September 27, 200= 0 4:10=20
 PM</DIV>
 <DIV style=3D"FONT: 10pt arial">Subject: RE: Performance Problem -= select=20
 from dual</DIV>
 <DIV> </DIV>
 This is so interesting.  I have read before that S= ELECT=20
 ... FROM DUAL is expensive in a procedure, and have seen it in traces.&nb= sp;=20
 Can anyone explain why?  Thanks Lisa <FONT=

size=3D2>Ft. Lauderdale, FL, USA -----Original Message----- Fr= om:=20
 Toepke, Kevin M [<A=20
 href=3D"mailto:ktoepke_at_cms.cendant.com">mailto:ktoepke_at_cms.cendant.com</A= >]=20
 Sent: Wednesday, September 27, 2000 8:25 AM <BR=

><FONT=20
  size=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT=20
  size=3D2>Subject: RE: Performance Problem.</FONT> </P><BR>

The SELECT from DUAL is a problem. It is not needed. Re= code as=20
follows.         <FONT=20 size=3D2>BEGIN        =20         <FONT=20 size=3D2>RETURN(to_char(to_date('1970-01-01.09:30:00', <FONT=

size=3D2>          &nbs= p;            &= nbsp;          =20 'YYYY-MM-DD.HH24:MI:SS') + <FONT=20 size=3D2>          &nbs= p;            &= nbsp;  =20
 (tstamp/86400000000),'dd-mm-yyyy'));=20         END;= 
 If you have the space, create an index on the 17Mill ro= w table=20
 that contains all of the columns you need. Make= sure=20
 the columns in the index are in the same order = as the=20
 columns in ORDER BY clause -- reduce I/O plus no sorting <FONT=

  size=3D2>required.</FONT> </P>
  <P><FONT size=3D2>Kevin</FONT> </P>
  <P><FONT size=3D2>&gt; </FONT><BR><FONT size=3D2>&gt; </FONT><BR><FONT si=

ze=3D2>>=20
 Hi All, > > Our= customer=20
 has a report running under NT/Oracle 7.3.4 that >=20
 runs for a very > long time, nearly 16 hours= . After=20
 investigating the program I > discovered two= =20
 > things: > 1. A call = to a=20
 procedure was taking nearly half the cpu, but &= gt; all=20
 it is, is a > select from dual to convert a=

timestamp column, that is passed to the >=20 procedure, to a date. > eg select=20 tstamp_to_date(colname), ..... from table where .... <FONT=20 size=3D2>> The function is: > BEGIN</FONT= >=20
 >   select=20 to_char(to_date('1970-01-01.09:30:00', >=20 'YYYY-MM-DD.HH24:MI:SS') + >=20 (tstamp/86400000000),'dd-mm-yyyy') > &n= bsp;=20
 into ts_date from dual; >   return=

ts_date; > END; &g= t;=20
 > Would there be a great improvement if we p= inned=20
 the function > into memory, or <F= ONT=20
 size=3D2>> should we just replace it all together which I believe is=

possible. > > 2= . The=20
huge cursor, on a table of 17mill rows, that drives <FONT=20 size=3D2>> the report is > using 7.5Gb of=

temporary tablespace because of the hash joins = > of=20
 full table > scans & order by. <B= R><FONT=20
 size=3D2>> > Would it be better to scan t= he large=20
 table and select the > records required</FON= T>=20
 > (maybe 1.5mill) into another table that is Primar= y keyed=20
 on > the required = >=20
 Order and then report from that table so eliminating the <FONT=

size=3D2>> order by and the > need for th= e=20
 temporary tablespace?? > <FONT=20 size=3D2>> > Any help would be greatly=20 appreciated. > &gt= ;=20
 Thanks > Ian > = --=20
 > Please see the official ORACLE-L FAQ: <A=

href=3D"http://www.orafaq.com" target=3D_blank>http://www.orafaq.com</A><= /FONT>=20
 > -- > Author: Biddell= ,=20
 Ian >   INET:=20 Ian.Biddell_at_compaq.com > <FONT=20 size=3D2>> Fat City Network Services    -- (858)=20 538-5051  FAX: (858) 538-5051 > San Die= go,=20
 California        -- Public Internet a= ccess=20
 / Mailing Lists >=20

  --------------------------------------------------------------------</FON=

T>=20
 > To REMOVE yourself from this mailing list, send a= n=20
 E-Mail message > to: ListGuru_at_fatcity.com (n= ote=20
 EXACT spelling of 'ListGuru') and in > the m= essage=20
 BODY, include a line containing: UNSUB ORACLE-L >=20
 (or the name of mailing list you want to be removed from).  You=20 may > also send the HELP command for other=

information (like subscribing). > <BR= ><FONT=20
 size=3D2>-- Please see the official ORACLE-L FA= Q: <A=20
 href=3D"http://www.orafaq.com" target=3D_blank>http://www.orafaq.com</A><= /FONT>=20
 -- Author: Toepke, Kevin M</= FONT>=20
   INET: ktoepke_at_cms.cendant.com Fat City Network Services    -- (858)=20 538-5051  FAX: (858) 538-5051 San Diego,=

California        -- Public Internet a= ccess=20
/ Mailing Lists <FONT=20

  size=3D2>----------------------------------------------------------------=

----=20
 To REMOVE yourself from this mailing list, send an E-M= ail=20
 message to: ListGuru_at_fatcity.com (note EXACT sp= elling=20
 of 'ListGuru') and in the message BODY, include= a line=20
 containing: UNSUB ORACLE-L (or the name of mail= ing=20
 list you want to be removed from).  You may also=20
 send the HELP command for other information (like subscribing).=20 </BLOCKQUOTE><CODE>

*******************************************************************<BR>

This message may contain information which is confidential and subject to l= egal privilege. If you are not the intended recipient, you may not peruse, = Received on Thu Sep 28 2000 - 02:36:21 CDT