RE: Optimizing Programs for ORACLE Apps (RULE Based)

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tanmoy Sent: Sunday, July 10, 2005 9:24 AM
To: oracle-l_at_freelists.org
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."
  --
  http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jul 10 2005 - 11:44:59 CDT