| 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
Good catch. The Jan70 constant date should be placed in a Package header as
a constant to avoid those 17 million executions of the expensive
char-to-date conversion.
Kevin
-----Original Message-----
From: Oweson Flynn [mailto:Oweson.Flynn_at_liberty.co.za]
Sent: Thursday, September 28, 2000 4:41 AM
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 - 06:29:42 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
 |
 |