Tuning Help

From: Kumar Mantripragada <kumar.mantripragada_at_oracle.com>
Date: Wed, 05 Sep 2001 16:48:24 -0400
Message-ID: <3B968F98.A26A29D3@oracle.com>

Hello everyone,

I have a report that runs for 15 minutes and produces 1500 pages of output. The main query is a UNION ALL of three select statements. On reviewing the tkprof output, the total cpu time for non-recursive calls is only 39 secs.

The query uses indexes on all tables mentioned in the select list and joins them in NESTED LOOPS and then sorts the output.

The optimizer is defaulted to RULE and all tables are analyzed as of date. I have used FIRST_ROWS, USE_HASH, CHOOSE, INDEX hints in the select clauses but could not reduce the run time of the report.

Could anyone provide some comments based on the above information. Is it a problem with the sql statement or should i look some place else??

Any help is greatly appreciated.

Thanks,
Kumar

text/x-vcard attachment: Card for Kumar Mantripragada

Received on Wed Sep 05 2001 - 15:48:24 CDT