Consultor Eletrônico

SUMMARY:

This Solution describes some tips on how to tune the ORACLE DataServer in the most efficient way.

EXPLANATION:

When tuning performance, there is a tendency to concentrate on whether DataServer generates index hints. However, index hints might help or hurt performance; sometimes the cost based optimizer will perform better.

The following rules can help in tuning ORACLE DataServer application performance:

1) Run the application with the logging turned on.

2) Find the SQL statements that use the most time; both the ones that
take a long time for each execution and the ones that are executed
many times. Use -Dsrv qt_debug,performance option and examine the dataserv.lg file. Use tools such as grep on UNIX to help in locating these statements.

3) Spend time on these high value targets. Don't spend time optimizing code that is never or rarely executed.

4) If the statement is executed many times, but each execution is
fairly quick, look for an alternative 4GL construct. For example:

assign num = 0.
do i = 1 to 1000000:
if can-find(customer where cust-num = i)
num = num + 1.
end.

This will cause a select count(*) from customer where cust-num = :1 to be executed 1000000 times. Each execution will be fairly quick, but they will add up.

assign num = 0.
for each customer where cust-num >= 1 and cust-num <= 1000000:
num = num + 1.
end.

In this case, the performance may be better, particularly when there are few customers in the range. Adding a field list will help, especially if the record is large.

An even better approach would be to use SQL select pass through:

select count(*) from customer where cust-num between 1 and 1000000

This select statement will be passed through to ORACLE. All ORACLE has to do then is an index range scan on the cust-num index. It does not need to read the actual row if it doesn't need to send anything other than the answer.

5) Compare the number of rows reported by either the dataserv.lg and/or ORACLE's tool, with the number that shows up for the client. Check if the WHERE clause is using some Progress-specific functions. For example:

- replacing a lookup (x, "1,3,5")<> 0 with (x = "1" or
x = "3" or x = "5").

When you use Progress specific functions, the function needs to be resolved by the client process. This might cause rows that do not satisfy the criteria to be sent to the client.

6) Avoid unnecessary work. If you don't need a particular column,
you should not fetch it. If you can limit the set of columns to just those in the index ORACLE will use, ORACLE does not need to read the row. If the order of the result set doesn't matter, don't force a particular ordering.

7) Not all table scans are bad. Sometimes a table scan is faster than
using an index. This is the case when a large enough percentage of the rows are to be returned, columns not in the index are requested, or the table is fairly small.

8) Find statements are not good for performance when running against the ORACLE DataServer, due to the work the DataServer needs to do to emulate the FIND behavior. Use find statements only if performance is not a concern.

9) Avoid lock upgrades (share-lock to exclusive lock). Since ORACLE
doesn't have a real share lock, Progress has to fetch the same row twice.

a. Check if the application is thrashing its cursors, and if the same SQL is being compiled many times. Use the dataserv.lg file to verify it.

Each SQL statement has a sqlcrc value. This value is used to determine if a SQL statement is the same, so it can be reused. If the SQL is compiled (oparse call), used (oexec/oexfet calls) and then the cursor is released to the pool (omru, cursor no longer needed to maintain context), then the cursor might be used for a different SQL statement before the DataServer needs the identical SQL again. This is when your application is thrashing its cursors. Either increase -c and the open_cursor parameter in init.ora (but not so much that you run into recursive SQL errors) or reduce the number of cursors that must be held to maintain context.

Avoid find statements, once you do a find on a particular index, the DataServer needs to hold on to the cursor since there is no way to predict if there will be a FIND NEXT on the same index. If a program does a FIND FIRST, the DataServer also has to keep the cursor opened since there might be a FIND NEXT (on the same index) afterwards. The same is also true for FIND LAST -- the DataServer keeps the cursor in case there is a FIND PREV.

Check if queries are being closed at the appropriate point. If you use stored procedures, make sure you close them. They will occupy a cursor until closed. There is a limit of 50 cursors that can be used for stored procedures and functions.

-- EXAMPLE OF ANALYSYS

cut -c32- dataserv.lg|grep sqlcrc|sort|tee 1.tmp|wc;uniq <1.tmp|wc

This extracts and counts all the oparse <xxx> sqlcrc lines. It
then counts how many unique sqlcrc values there are. Comparing these
two numbers can give you an idea if the application is thrashing its
cursors. You still need to look at the context (are there omru
calls or do you really need the same SQL in n different cursors to
maintain context?) uniq -c will even tell you how many times the
same SQL was compiled.

b. Use field lists. This helps in several ways. It works well with
array fetches, since narrower result set rows mean more of them can
fit in the same size buffer. This also helps with Progress
networking because multiple rows are sent in a single message. The
client holds this message as a cache and does not need to ask the
server for the next row.


cut -c9- dataserv.lg|grep ofen|sort|uniq -c

This will give a rough idea (this does not take into account the
same cursor being reused for different SQL*) what cursors are
fetching large numbers of rows. These are the ones that might
benefit most from field lists and/or query-tuning (cache <larger
number>).

c. More indexes do not always help. Indexes hurt inserts and updates of the columns in the index.

d. Don't declare an index as unique unless you need it to enforce a
constraint:

Consider a table with columns a, b, and c, along with a
progress_recid columns and the following indexes:

Progress ORACLE

a unique a unique
a, b unique a, b unique
a, b, c unique a, b, c unique

Now, if only the first index is declared unique, what you will
have in ORACLE is:

a unique
a, b, progress_recid
a, b, c, progress_recid

If you then issue a query that uses either the second or third
index but does not select any columns other than what is in the
index, ORACLE does not need to read the row. The queries that
the DataServer issues typically include the progress_recid
column in the select list.

e. Measure the performance. Then measure it again, without any
changes. Check if the numbers are close to each other. If not, you
might need work on the environment until you are able to produce
consistent results.

f. If the performance problem is that the application is too fast,
insert pause statements in the appropriate places.


References to Written Documentation:

Progress DataServer for ORACLE Guide