Re: Reports Tuning issues

kpram27 wrote:

> There are some reports which run at varying times : 2.92 min (best) and
> 19.58 min(worst) on a single day.
> This variance suggsts that there is a problem with the Server
> parameters.

Why do you say that? There are numerous factors that can influence the performance, besides server parameters.

A simple change in criteria, e.g. date range covering 2 days to a date range covering a month, can cause a _huge_ difference in performance.

PQ. E.g. PQ max is set to 20 processes. The report uses PQ and is very fast. However, when there are not enough PQ slaves available, the report run in serial and is dog slow.

Etc. Etc.

I would not mess around with tuning any parameters without first knowing what the underlaying performance issue is. You may well tune a parameter, and worsen the situation. Or cause knock on effects in other processes.

The first rule in performance tuning is to identify the problem causing the performance problem in the first place. No guesses. :-)

I suggest that you closely monitor the report that causes this problem. Put it through an explain plan. Do it again when the report is being run and it is very slow. Maybe there is a change in the execution plan.

When it does run slow, monitor the V$ tables for session waits, session stats and session events. Find out what waits events are the problem. Relate that to the processing plan.

You can not tune performance by listening how the engine runs and guessing what the problem is. You need to pop the hood and taking a look under it.

--
Billy