Strent request for more than 32K error 893 - Oracle dataserv
INTRODUCTION:
=============
This Technical Support Knowledgebase entry describes how to correct
an 893 error when using the PROGRESS Oracle Dataserver version 8.0A
and above.
WHY YOU NEED TO KNOW THIS:
===========================
You need to know this so that you can correctly modify your queries
to eliminate the error. The dataserver may be attempting to allocate
too much memory when performing multiple table joins that is too wide
to be done by the server in its present form. In version 8.0A and
above the Oracle Dataserver allows the Oracle RDBMS to to perform
joins BY DEFAULT to improve performance.
PROCEDURAL APPROACH:
====================
Things to try:
1. specify query-tuning (no-lookahead)
2. specify a field list to reduce the width of the rows.
3. specify query-tuning (no-join-by-sqldb)
(difficulty: 1(easy), 2(hard), 3(easy)
performance: 1(medium), 2(best), 3(worst)).
Option #1 is reccommended.
For the join to be done on the server, the size of a row in the join
(the sum of the max. size for each column in all tables) must be less
than 16k (16k since we need space for TWO rows, so we can tell when
the outer table's row changes).
You can get the Dataserver to return the maximun row size for a table
checking the dataserv.lg file after you connect -Dsrv qt_debug,1023:
1. do a find first on each table in the join
2. disconnect (to write cursor statistics to the dataserv.lg file
3. reconnect (again with -Dsrv qt_debug,1023)
Add the max. row size for all tables in a join.
Do step 1-3 for the "for each" that fails with error 893.
OR execute the following query for each table in the join and again
checking the dataserv.lg file for the maximun row size for a table:
for each <table> where yes or no query-tuning (lookahead debug
extended):
end.
Do this for all tables in involved in your join (individually),
and then add the max. row size together for all tables in a join. This
number should be > 16k if you are getting error 893.
The calculation on size of each field is based on how much space
the Dataserver need for the "maximun" size row as retrieved from
Oracle. Basically it is:
15 bytes for each date in 8.0A.
7 bytes for each date in 8.0B and above.
n+1 bytes for number, char or varchar2.
(Where n is the maximun size for the column in Oracle).
15 for a ROWID (if we're using Oracle's native rowid),
11 if using unique integer (or progress_recid).
For LONG or LONG RAW, we use 256 bytes (and will refetch
into another, larger area if its longer).
If the join is being performed by ORACLE, we need to allocate
sufficient space for TWO result rows. If it's not a join, space
for ONE row is sufficient.
ONLINE PROCEDURES OR UTILITIES:
===============================
None
REFERENCES TO WRITTEN DOCUMENTATION:
====================================
Dataserver for Oracle Guide.
Progress Software Technical Support Note # 16297