Re: Tuning Help

"Kumar Mantripragada" <kumar.mantripragada_at_oracle.com> wrote in message news:3B968F98.A26A29D3_at_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
>
>
>

39 secs for 1500 pages output doesn't look much to me. You just didn't provide any sensible detail to come up with sensible advice, as

- platform
- version
- why is the optimizer defaulted to rule
- what is the affected sql-statement
- which are the tables and indexes involved
- and so and so on

Theoretically it can be a problem with the sql statement, the buffer cache, the sort area size, your temporary segments, to mention only a few options.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Wed Sep 05 2001 - 16:13:27 CDT