Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance Problem - select from dual

RE: Performance Problem - select from dual

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Wed, 27 Sep 2000 09:49:14 -0400
Message-Id: <10632.117992@fatcity.com>


If you to the function in PL/SQL, you don't have to make a call to the SQL engine -- the whole operation stays in the PL/SQL engine.

-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu_at_qode.com] Sent: Wednesday, September 27, 2000 10:11 AM To: Multiple recipients of list ORACLE-L 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 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
<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
<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 
Received on Wed Sep 27 2000 - 08:49:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US