| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance Problem - select from dual
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_01C02884.CCE8DE02
Content-Type: text/plain;
charset="iso-8859-1"
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 why?
Thanks
Lisa
Ft. Lauderdale, FL, USA
-----Original Message-----
From: Toepke, Kevin M [mailto:ktoepke_at_cms.cendant.com]
Sent: Wednesday, September 27, 2000 8:25 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Performance Problem.
The SELECT from DUAL is a problem. It is not needed. Recode as follows.
BEGIN
RETURN(to_char(to_date('1970-01-01.09:30:00',
'YYYY-MM-DD.HH24:MI:SS') +
(tstamp/86400000000),'dd-mm-yyyy'));
END;
If you have the space, create an index on the 17Mill row table that contains all of the columns you need. Make sure the columns in the index are in the same order as the columns in ORDER BY clause -- reduce I/O plus no sorting required.
Kevin
>
>
> Hi All,
>
> Our customer has a report running under NT/Oracle 7.3.4 that
> runs for a very
> long time, nearly 16 hours. After investigating the program I
> discovered two
> things:
> 1. A call to a procedure was taking nearly half the cpu, but
> all it is, is a
> select from dual to convert a timestamp column, that is passed to the
> procedure, to a date.
> eg select tstamp_to_date(colname), ..... from table where ....
> The function is:
> BEGIN
> select to_char(to_date('1970-01-01.09:30:00',
> 'YYYY-MM-DD.HH24:MI:SS') +
> (tstamp/86400000000),'dd-mm-yyyy')
> into ts_date from dual;
> return ts_date;
> END;
>
> Would there be a great improvement if we pinned the function
> into memory, or
> should we just replace it all together which I believe is possible.
>
> 2. The huge cursor, on a table of 17mill rows, that drives
> the report is
> using 7.5Gb of temporary tablespace because of the hash joins
> of full table
> scans & order by.
>
> Would it be better to scan the large table and select the
> records required
> (maybe 1.5mill) into another table that is Primary keyed on
> the required
> Order and then report from that table so eliminating the
> order by and the
> need for the temporary tablespace??
>
>
> Any help would be greatly appreciated.
>
> Thanks
> Ian
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Biddell, Ian
> INET: Ian.Biddell_at_compaq.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: ktoepke_at_cms.cendant.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C02884.CCE8DE02 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: Performance Problem - select from dual</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>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 why? </FONT></P> <P><FONT SIZE=3D2>Thanks</FONT> <BR><FONT SIZE=3D2>Lisa</FONT> <BR><FONT SIZE=3D2>Ft. Lauderdale, FL, USA</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Toepke, Kevin M [<A = HREF=3D"mailto:ktoepke_at_cms.cendant.com">mailto:ktoepke_at_cms.cendant.com</= A>]</FONT> <BR><FONT SIZE=3D2>Sent: Wednesday, September 27, 2000 8:25 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: RE: Performance Problem.</FONT> </P> <BR> <P><FONT SIZE=3D2>The SELECT from DUAL is a problem. It is not needed. = Recode as follows. </FONT> <BR> <FONT = SIZE=3D2>BEGIN</FONT> <BR> = <FONT = SIZE=3D2>RETURN(to_char(to_date('1970-01-01.09:30:00', </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; = 'YYYY-MM-DD.HH24:MI:SS') +</FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; (tstamp/86400000000),'dd-mm-yyyy'));</FONT> <BR> <FONT = SIZE=3D2>END;</FONT> </P> <P><FONT SIZE=3D2>If you have the space, create an index on the 17Mill = row table that contains</FONT> <BR><FONT SIZE=3D2>all of the columns you need. Make sure the columns = in the index are in the</FONT> <BR><FONT SIZE=3D2>same order as the columns in ORDER BY clause -- = reduce I/O plus no sorting</FONT> <BR><FONT SIZE=3D2>required.</FONT> </P> <P><FONT SIZE=3D2>Kevin</FONT> </P> <P><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Hi All,</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Our customer has a report running under = NT/Oracle 7.3.4 that </FONT> <BR><FONT SIZE=3D2>> runs for a very</FONT> <BR><FONT SIZE=3D2>> long time, nearly 16 hours. After investigating = the program I </FONT> <BR><FONT SIZE=3D2>> discovered two</FONT> <BR><FONT SIZE=3D2>> things:</FONT> <BR><FONT SIZE=3D2>> 1. A call to a procedure was taking nearly half = the cpu, but </FONT> <BR><FONT SIZE=3D2>> all it is, is a</FONT> <BR><FONT SIZE=3D2>> select from dual to convert a timestamp column, = that is passed to the</FONT> <BR><FONT SIZE=3D2>> procedure, to a date. </FONT> <BR><FONT SIZE=3D2>> eg select tstamp_to_date(colname), ..... from = table where ....</FONT> <BR><FONT SIZE=3D2>> The function is:</FONT> <BR><FONT SIZE=3D2>> BEGIN</FONT> <BR><FONT SIZE=3D2>> select = to_char(to_date('1970-01-01.09:30:00', </FONT> <BR><FONT SIZE=3D2>> 'YYYY-MM-DD.HH24:MI:SS') +</FONT> <BR><FONT SIZE=3D2>> (tstamp/86400000000),'dd-mm-yyyy') </FONT> <BR><FONT SIZE=3D2>> into ts_date from dual;</FONT> <BR><FONT SIZE=3D2>> return ts_date;</FONT> <BR><FONT SIZE=3D2>> END;</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Would there be a great improvement if we pinned = the function </FONT> <BR><FONT SIZE=3D2>> into memory, or</FONT> <BR><FONT SIZE=3D2>> should we just replace it all together which I = believe is possible. </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> 2. The huge cursor, on a table of 17mill rows, = that drives </FONT> <BR><FONT SIZE=3D2>> the report is</FONT> <BR><FONT SIZE=3D2>> using 7.5Gb of temporary tablespace because of = the hash joins </FONT> <BR><FONT SIZE=3D2>> of full table</FONT> <BR><FONT SIZE=3D2>> scans & order by.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Would it be better to scan the large table and = select the </FONT> <BR><FONT SIZE=3D2>> records required</FONT> <BR><FONT SIZE=3D2>> (maybe 1.5mill) into another table that is = Primary keyed on </FONT> <BR><FONT SIZE=3D2>> the required</FONT> <BR><FONT SIZE=3D2>> Order and then report from that table so = eliminating the </FONT> <BR><FONT SIZE=3D2>> order by and the</FONT> <BR><FONT SIZE=3D2>> need for the temporary tablespace??</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Any help would be greatly appreciated.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Thanks</FONT> <BR><FONT SIZE=3D2>> Ian</FONT> <BR><FONT SIZE=3D2>> -- </FONT> <BR><FONT SIZE=3D2>> Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>> -- </FONT> <BR><FONT SIZE=3D2>> Author: Biddell, Ian</FONT> <BR><FONT SIZE=3D2>> INET: Ian.Biddell_at_compaq.com</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Fat City Network Services -- = (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>> San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT SIZE=3D2>> = --------------------------------------------------------------------</FO= NT> <BR><FONT SIZE=3D2>> To REMOVE yourself from this mailing list, send = an E-Mail message</FONT> <BR><FONT SIZE=3D2>> to: ListGuru_at_fatcity.com (note EXACT spelling = of 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>> the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>> (or the name of mailing list you want to be = removed from). You may</FONT> <BR><FONT SIZE=3D2>> also send the HELP command for other = information (like subscribing).</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Toepke, Kevin M</FONT> <BR><FONT SIZE=3D2> INET: ktoepke_at_cms.cendant.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =Received on Wed Sep 27 2000 - 08:14:00 CDT
 |
 |