Re: Performance Problem.

Dear Ian

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??

Sounds Good

GOahead

Rao

--

On Tue, 26 Sep 2000 16:20:55  
 Biddell, Ian wrote:


>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).

>



Get your FREE Email and Voicemail at Lycos Communications at