Re: PeopleSoft query

  Replace all your subqueries by a single one. Use an analytical function such as rank() (although row_number() could do) to identify all the "highest RCD for highest REV_NUM for highest VER_NUM" kind of conditions.
I believe the last subquery should be made uncorrelated, make it a GROUP BY query, move it up into the FROM clause and join on it.

Basically, the fewer times each table appears in your query the better ...

HTH S Faroult

 Arao wrote:
> We have this Peoplesoft query that now runs for about 18hours. This is
> a payroll batch run and you can see from the SQL that is has 8
> subqueries.
> Last year we were able to trace (10046 and used OraSrp for the
> profile) the whole batch process, and found out that this query is the
> one consuming half of the payroll runtime.
> You can see below that it is mostly on CPU which could be attributed
> by lots of LIOs on the lines 18,19,22 (and other lines), plus the SORT
> AGGREGATE and HASH JOIN operations.
> Well this profile was from last year, and now the query has become slower.
>
> I'm not aware of the PeopleSoft tables and how to approach the tuning
> for this SQL, so I just explained to the developers/DBAs the areas
> where this SQL goes wrong.
>

--
http://www.freelists.org/webpage/oracle-l