RE: Optimizing Programs for ORACLE Apps (RULE Based)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <tanmoydc@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 10 Jul 2005 12:37:59 -0400
You previously had a plan that worked in 4 hours.
If 4 hours was acceptable, the minimum expense to get back there is to save
that plan
as a stored outline and make sure the environment when "Outbound" is invoked
gets it to
use stored outlines.
The other alternative is to see if some particular index is missing OR if
the relationship
of creation order of some relevant indexes have changed such that the tie
breaker switched between
two indexes. (Did someone recreate indexes without regard to maintaining the
order of creation or add
a new index that the RULE optimizer rates higher? If I recall correctly
alter rebuild preserves the
order of creation with respect to the RULE optimizer, but I'm not sure
without testing on a particular
patch and port.)
Of course if you do not have a way to see what the old plan was then:
1) Your migration protocol is defective.
2) You'll have to do the work of actually tuning a query you can't touch.
There is some possibility that the query has received a "performance" patch,
but that gets
into the realm of applying patches, and it sounds to me like you just want
to get back to
where you were before.
I hope one of these alternatives helps you out.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tanmoy
Sent: Sunday, July 10, 2005 9:24 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Optimizing Programs for ORACLE Apps (RULE Based)
Hi,
We have a client system running on Oracle Apps 10 and the optimizer
mode is RULE based. For the past fewer months one of the interfaces
(Outbound) is running long. It was earlier finished on 4hrs now its
taking 11+ hrs.The row processed are almost same for each run. Quest
Central is showing TOP sql but it is a part of common code which runs
fine on 11i.
OS - Sun Solaris
DB - Oracle 8.1.7
<huge snip>
Any suggestions regarding this or what should be my approach from the
developer point of view will be highly appreciated.
--
Regards,
Tanmoy
-- "Time is the coin of life. Only you can determine how it will be spent."
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
