Performance Problem.

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. Received on Tue Sep 26 2000 - 18:18:56 CDT