Re: Tuning Help

Before anyone can comment on it, you need to post the explain plan along with the actual query.
FIRST_ROWS hint will automatically use the CBO. Though I suppose in your case
you are more interested in the ALL_ROWS hint. Other hints you mentioned will use RBO (hence won't use table statistics). Try enabling the CBO explicitly by issuing ALTER SESSION SET optimizer_goal=choose;

In tuning you should be more concerned with the physical I/O than with cpu time (i.e. reducing physical i/o).
Considering the amount of data your report is returning, maybe the sort operation needs
to be tuned by the DBA. I would assume that disk sorts are occuring. You can check this
by querying V$sort_usage.

Anurag

"Kumar Mantripragada" <kumar.mantripragada_at_oracle.com> wrote in message news:3B968F98.A26A29D3_at_oracle.com...
> Hello everyone,
>
> 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??
Received on Wed Sep 05 2001 - 16:30:22 CDT