Re: PeopleSoft query
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: karlarao@xxxxxxxxx
- Date: Mon, 13 Jul 2009 10:22:21 +0200
Karl,
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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
