Speed of R04701

rheggs

Member
Hi List

First time posting, please treat me gently. :)

We are running R04701 (AP to GL by Original Document) nightly for integrity checking. The report now takes several hours to run, and The Powers That Be are asking if anything can be done to make the run shorter.

Does anyone have any experience of tuning the database (Oracle 8.1.6) to speed things up, or any other suggestions? I'm quite comfortable with Oracle, but I've only had 2 months of exposure to OneWorld so far, and I'm still feeling a bit shell shocked!

Some figures that may or may not be useful...
F0911 contains about 820,000 rows
F0101 contains about 50,000 rows
F0411 contains 83,000 rows

Richard



OneWorld Xe SP15.1
Enterprise: Oracle 8.1.6, AIX 4.3.3
Webserver: (HTML) SP 15.1_0012, AIX 4.3.3
 
Hi Richard and Welcome!

Good question. Our A/P volume is low here so I have no specific application tuning suggestions for you. Here's some server-side general tips for you though.

1. in the JDE.INI file (oneworld/b7333/ini/) find the [LOCK MANAGER]section and make sure that the RequestedService parameter is set to NONE, per oti-99-0024. This has the effect of speeding up transaction processing for some UBEs (this should only be changed on the server's JDE.INI file).

2. If you're an Oracle guru you already recognize the need to periodically rebuild the indices. We do it every 3 to 6 months.

3. Another oracle/oneworld tip - JDE has had a problem when copying tables of putting the indices in the data tablespace. Check your schemas to see if the indices need to be moved to the appropriate index tablespace.

4. Use Cost-based Optimization (unless you have a LOT of time to analyze and setup Rules Based) and update your database statistics on a regular (weekly) basis.

Good Luck,


Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials
 
Hi Larry,

Many thanks for your tips. All taken on board!

Richard



OneWorld Xe SP15.1
Enterprise: Oracle 8.1.6, AIX 4.3.3
Webserver: (HTML) SP 15.1_0012, AIX 4.3.3
 
We run this job weekly, but it only takes 2 hours and we have a much larger
F0911 and F0411, than you do, 11,000,000 and 1,000,000 respectively. Have
you optimised your databases? We run a weekly optimisation script to refresh
the Oracle statistics, this is essential with a growing database, to ensure
good performance.

Do you have so many problems that you need to run this report daily, we run
the R047001A daily, this highlights problems, which we use the R04701 to
help identify.



OW733.3 Xe SP 14.2
Enterprise Server - Intel NT + Oracle 8.0.6
Client - Citrix TSE + 4 NT PC's for development
 
Hi Richard,
it is correct create statistics on your Oracle table. You have to study the database fragmentation. If your table have a lot of extents you've to reorganize them.
It's a good thing activate debug when you submit this report first time to determine if you need build other index on table.

Hi Gigi

CNC Senior
OneWorld Xe, B7332, B7331, B733
Oracle 8.1.6, 8.1.7
SQLServer 7
Webserver
 
Back
Top