| 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_01C0293A.4065F6C0
Content-Type: text/plain;
charset="iso-8859-1"
HI Oweson,
I'm not sure about your suggestion yet, I will have to give it a test, but I did change the function from the "select on Dual" to just a RETURN as previously advised on the list. This changed the elapsed seconds on the call to the function from 409 to 70, running in a development database which is about 250 times smaller than the production one. There was no time spent in the parsing in my trace file, all the time was on the execute. So that helps me on the function problem, now I just have to get rid of the need for a 7.5Gb temporary tablespace :-/
Thanks again
Ian
-----Original Message-----
From: Oweson Flynn [mailto:Oweson.Flynn_at_liberty.co.za]
Sent: Thursday, 28 September 2000 6:11 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Performance Problem - select from dual
Just a thought that occurred to me while I was perusing this thread - please enlighten me if my line of thought is incorrect. (I am in the mood to use 'high-faluting' grammar this morning!)
Would the to_date('1970-01-01.09:30:00', 'YYYY-MM-DD.HH24:MI:SS') function 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/86400000000) to it, and then do the to_char bit?
for example
DECLARE
Jan70 DATE := TO_DATE( '1970-01-01.09:30:00', 'YYYY-MM-DD.HH24:MI:SS' );
BEGIN
RETURN( TO_CHAR( Jan70 + ( TStamp / 86400000000 ), 'dd-mm-yyyy' ));
END;
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 the
TStamp is a bind variable'), but the TO_DATE function inside the Return
statement 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 17
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
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
<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.comReceived on Thu Sep 28 2000 - 05:52:53 CDT
<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). ******************************************************************* This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. ******************************************************************* ------_=_NextPart_001_01C0293A.4065F6C0 Content-Type: text/html; charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: Performance Problem - select from dual</TITLE>
<META content="MSHTML 5.00.3103.1000" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=460264510-28092000>HI
Oweson,</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=460264510-28092000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=460264510-28092000>I'm
not sure about your suggestion yet, I will have to give it a test, but I did change the function from the "select on Dual" to just a RETURN as previously advised on the list. This changed the elapsed seconds on the call to the function from 409 to 70, running in a development database which is about 250 times smaller than the production one. There was no time spent in the parsing in my trace file, all the time was on the execute.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=460264510-28092000>So
that helps me on the function problem, now I just have to get rid of the need for a 7.5Gb temporary tablespace :-/</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=460264510-28092000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=460264510-28092000>Thanks
again</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=460264510-28092000>Ian</SPAN></FONT></DIV>
<BLOCKQUOTE>
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Oweson Flynn [mailto:Oweson.Flynn_at_liberty.co.za]<BR><B>Sent:</B> Thursday, 28 September 2000 6:11 PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> Re: Performance Problem - select from dual<BR><BR></DIV></FONT> <DIV><FONT size=2>Just a thought that occurred to me while I was perusing this thread - please enlighten me if my line of thought is incorrect. (I am in the mood to use 'high-faluting' grammar this morning!)</FONT></DIV> <DIV> </DIV> <DIV><FONT size=2>Would the <STRONG>to_date('1970-01-01.09:30:00', </STRONG><FONT size=2><STRONG> 'YYYY-MM-DD.HH24:MI:SS') </STRONG>function 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 <STRONG>(tstamp/86400000000)</STRONG> to it, and then do the to_char bit?</FONT></FONT></DIV> <DIV> </DIV> <DIV><FONT size=2>for example</FONT></DIV> <DIV> </DIV> <DIV><FONT size=2>DECLARE</FONT></DIV> <DIV><FONT size=2> Jan70 DATE := TO_DATE( '1970-01-01.09:30:00', 'YYYY-MM-DD.HH24:MI:SS' );</FONT></DIV> <DIV><FONT size=2>BEGIN</FONT></DIV> <DIV><FONT size=2> <FONT size=2>RETURN( TO_CHAR( Jan70 +</FONT><FONT size=2> ( TStamp / 86400000000 ), 'dd-mm-yyyy' ));</FONT> <BR><FONTS ize="2">END;</FONT> </FONT></DIV> <DIV> </DIV> <DIV><FONT size=2>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 the TStamp is a bind variable'), but the TO_DATE function inside the Return statement 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 17 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.</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>Any comments / corrections/ elucidations?</FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT size=2>Regards<BR>Oweson Flynn<BR>------------------------------------------------------------------<BR>The Flynn Consultancy<BR>Tel: 082-600-7-006<BR>Fax: (011) 782-9313<BR>EMail: <AH ref="mailto:oef_at_icon.co.za">oef_at_icon.co.za</A></FONT></DIV> <BLOCKQUOTE style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV> <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> <A href="mailto:lkoivu_at_qode.com" title=lkoivu_at_qode.com>Koivu, Lisa</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A href="mailto:ORACLE-L_at_fatcity.com" title=ORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> </DIV> <DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, September 27, 2000 4:10 PM</DIV> <DIV style="FONT: 10pt arial"><B>Subject:</B> RE: Performance Problem - select from dual</DIV> <DIV><BR></DIV> <P><FONT size=2>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=2>Thanks</FONT> <BR><FONT size=2>Lisa</FONT> <BR><FONT size=2>Ft. Lauderdale, FL, USA</FONT> </P> <P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: Toepke, Kevin M [<A href="mailto:ktoepke_at_cms.cendant.com">mailto:ktoepke_at_cms.cendant.com</A>]</FONT> <BR><FONT size=2>Sent: Wednesday, September 27, 2000 8:25 AM</FONT> <BR><FONT size=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT size=2>Subject: RE: Performance Problem.</FONT> </P><BR> <P><FONT size=2>The SELECT from DUAL is a problem. It is not needed. Recode as follows. </FONT><BR> <FONT size=2>BEGIN</FONT> <BR> <FONT size=2>RETURN(to_char(to_date('1970-01-01.09:30:00', </FONT><BR><FONT size=2> 'YYYY-MM-DD.HH24:MI:SS') +</FONT> <BR><FONT size=2> (tstamp/86400000000),'dd-mm-yyyy'));</FONT> <BR> <FONT size=2>END;</FONT> </P> <P><FONT size=2>If you have the space, create an index on the 17Mill row table that contains</FONT> <BR><FONT size=2>all of the columns you need. Make sure the columns in the index are in the</FONT> <BR><FONT size=2>same order as the columns in ORDER BY clause -- reduce I/O plus no sorting</FONT> <BR><FONT size=2>required.</FONT> </P> <P><FONT size=2>Kevin</FONT> </P> <P><FONT size=2>> </FONT><BR><FONT size=2>> </FONT><BR><FONT size=2>> Hi All,</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>> Our customer has a report running under NT/Oracle 7.3.4 that </FONT><BR><FONT size=2>> runs for a very</FONT> <BR><FONT size=2>> long time, nearly 16 hours. After investigating the program I </FONT><BR><FONT size=2>> discovered two</FONT> <BR><FONT size=2>> things:</FONT> <BR><FONT size=2>> 1. A call to a procedure was taking nearly half the cpu, but </FONT><BR><FONT size=2>> all it is, is a</FONT> <BR><FONT size=2>> select from dual to convert a timestamp column, that is passed to the</FONT> <BR><FONT size=2>> procedure, to a date. </FONT><BR><FONT size=2>> eg select tstamp_to_date(colname), ..... from table where ....</FONT> <BR><FONT size=2>> The function is:</FONT> <BR><FONT size=2>> BEGIN</FONT> <BR><FONT size=2>> select to_char(to_date('1970-01-01.09:30:00', </FONT><BR><FONT size=2>> 'YYYY-MM-DD.HH24:MI:SS') +</FONT> <BR><FONT size=2>> (tstamp/86400000000),'dd-mm-yyyy') </FONT><BR><FONT size=2>> into ts_date from dual;</FONT> <BR><FONT size=2>> return ts_date;</FONT> <BR><FONT size=2>> END;</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>> Would there be a great improvement if we pinned the function </FONT><BR><FONT size=2>> into memory, or</FONT> <BR><FONT size=2>> should we just replace it all together which I believe is possible. </FONT><BR><FONT size=2>> </FONT><BR><FONT size=2>> 2. The huge cursor, on a table of 17mill rows, that drives </FONT><BR><FONT size=2>> the report is</FONT> <BR><FONT size=2>> using 7.5Gb of temporary tablespace because of the hash joins </FONT><BR><FONT size=2>> of full table</FONT> <BR><FONT size=2>> scans & order by.</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>> Would it be better to scan the large table and select the </FONT><BR><FONT size=2>> records required</FONT> <BR><FONT size=2>> (maybe 1.5mill) into another table that is Primary keyed on </FONT><BR><FONT size=2>> the required</FONT> <BR><FONT size=2>> Order and then report from that table so eliminating the </FONT><BR><FONT size=2>> order by and the</FONT> <BR><FONT size=2>> need for the temporary tablespace??</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>> </FONT><BR><FONT size=2>> Any help would be greatly appreciated.</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>> Thanks</FONT> <BR><FONT size=2>> Ian</FONT> <BR><FONT size=2>> -- </FONT><BR><FONT size=2>> Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com</A></FONT> <BR><FONT size=2>> -- </FONT><BR><FONT size=2>> Author: Biddell, Ian</FONT> <BR><FONT size=2>> INET: Ian.Biddell_at_compaq.com</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT size=2>> San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT size=2>> --------------------------------------------------------------------</FONT> <BR><FONT size=2>> To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT size=2>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT size=2>> the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT size=2>> (or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT size=2>> also send the HELP command for other information (like subscribing).</FONT> <BR><FONT size=2>> </FONT><BR><FONT size=2>-- </FONT><BR><FONT size=2>Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com</A></FONT> <BR><FONT size=2>-- </FONT><BR><FONT size=2>Author: Toepke, Kevin M</FONT> <BR><FONT size=2> INET: ktoepke_at_cms.cendant.com</FONT> </P> <P><FONT size=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT size=2>San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT size=2>--------------------------------------------------------------------</FONT> <BR><FONT size=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT size=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT size=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT size=2>(or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT size=2>also send the HELP command for other information (like subscribing).</FONT> </P></BLOCKQUOTE><CODE><FONT size=3><BR><BR>*******************************************************************<BR><BR>This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this
 |
 |