Consultor Eletrônico

Hints on how to improve application performance with Oracle


If users are seeing significant degradation in performance
when adding Oracle to their environment/application, here
are a few hints that may help track down the cause(s).

1) Evaluate the use of indices.

Look at how Oracle has chosen to execute specific queries.
Find the 4GL and see if you can better formulate the 4GL.
Some hints to use and statements to watch out for are:

USE-INDEX
BEGINS
MATCHES
WHERE x = y and y = "string" or z begins "a" ....etc.
[A complex predicate list may become overly
and unnecessarily complicated causing ORACLE
t to take a wrong query path.]
FIND FIRST
FIND LAST
RECID(x) [Not as fast as you'd anticipated? Check to
see if the same result can be achieved without
going to the database.]


2) Narrow down queries that are taking way too long and
investigate using Stored Procedures. (See V7 manuals
for the syntax.)

3) Use triggers to do data validation on the application
side, thereby limiting the necessity to go to the
database for yet another query.

4) Are other miscellaneous Oracle products running?
Statistics on? DB Links not needed?

If so, disable them.

5) For some suspiciously slow programs or set of programs,
run and watch locking behavior in promon. Is the number
of locks held significantly higher than running against
a PROGRESS db? Are any being held for a long period
of time? What you are investigating is the answer to
the question "Has my transaction scope changed?", since
commit points are a bit different.


Progress Software Technical Support Note # 13925