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.

RE: Performance Problem.

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Wed, 27 Sep 2000 07:15:16 -0400
Message-Id: <10632.117971@fatcity.com>


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).
Received on Wed Sep 27 2000 - 06:15:16 CDT

Original text of this message

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