R098011 Performance

peterbruce

peterbruce

Legendary Poster
Fellow JDEListers,

I have an inconsistancy with the R098011 - 52 Period Accounting Post that I don't understand.

We have 2 companies (00006 and 00007) that have 52 Period Accounting enabled. Our F0911 has approximately 9 million rows. We have added data selection to only select companies 00006 and 00007. The SQL statement produced (extracted from the jdedebug.log run on a fat client) is:

SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y' AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC

Originally this SQL would not invoke an index and would take a long time to table scan the F0911. We added a one column index on the GLCO which brought the time to run to a more acceptable level - when there were appropriate batches to post. However, when there was no batches for the R098011 to post it takes the same amount of time. Below is are extracts from 2 jdedebug.log's for local executions of the R098011 that had nothing to post.

Aug 30 09:46:42 ** 4036/168 SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y' AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC

First:
Aug 30 09:46:42 ** 4036/168 Entering JDB_StartRetainPrevious
Aug 30 09:46:42 ** 4036/168 Entering JDB_Fetch
Aug 30 09:46:42 ** 4036/168 ORACLE DBFetch: Invoke OCI Fetch fetchNumRows = 81
Aug 30 10:10:52 ** 4036/168 Entering JDB_CloseView (VIEW = V0911M)

Second:
Aug 30 11:47:07 ** 896/3300 SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y'
AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC
Aug 30 11:47:07 ** 896/3300 Entering JDB_StartRetainPrevious
Aug 30 11:47:07 ** 896/3300 Entering JDB_Fetch
Aug 30 11:47:07 ** 896/3300 ORACLE DBFetch: Invoke OCI Fetch fetchNumRows = 81
Aug 30 12:09:48 ** 896/3300 Entering JDB_CloseView (VIEW = V0911M)

Notice the time gaps of 24 minutes and 10 seconds and 22 minutes and 41 seconds that are highlighted. The same SQL, when executed manually (using TOAD), takes only 7 seconds.

Can someone please explain why the R098011 is taking 20 to 30 minutes while the same SQL executed manually takes only 7 seconds
 
Peter,

If it's not some sort of concurency issue with some other job (which is possible), then it's probably an issue with the execution plan - the RDBMS may select different execution plans, even for identical SQL, depending on which tool runs it.

There's also an unavoidable difference between the OW SQL and yours, due to the parameter substitution.

To troubleshoot plan issues, you will need to trace this SQL at the server side to find out which plan it's actually using at run-time...
 
Alex,

Thanks, for your response. I don't think it's a currency issue, but your other comments confirm the type of problem I suspected. But it is a bit out of my expertise and getting into a fairly heavey DBA area. I'll follow up your suggestions with our IT staff.
 
Hi,

Although I do not use Oracle, I have seen the same problem on a different UBE/table with our SQL installation.

From the UBE debug log I saw that the SELECT statement issued by the enterprise server had two types of syntax.

example:

select * FROM PRODDTA.F4111 (NOLOCK) where....
select * FROM PRODDTA.4111 where ...

When NOLOCK was involved, the statement took less than 1 second. whilst the statement without NOLOCK took 8 seconds per query. I am still trying to figure this one out. When I issued the command via Query analyser, without nolock, it took less than 1 second.
 
(NOLOCK) is MS SQL syntax, but yes, in MS SQL the same SQL can be taking different times to run too, because, again, the execution plans are dynamic and differ from execution to execution. Only in SQL this is harder to fix.
 
Here is an update on the response from Horasoft (Oracle/Peoplsoft/JD Edwards). The current Oracle database MTR for Xe is 9i and we have 8.1.7.4 and Oracle say they cannot help us until we upgrade to 9i and retest. I'm certain that the Oracle database MTR for Xe was 8.1.7.4 and that it has been changed. Can anyone confirm that the Oracle database MTR for Xe was 8.1.7.4 at some time, if so, when was it changed?
 
Peter,

MTR's apply to SP's, e.g.: the base XE was supported with 8i, but SP23 is not.

Doesn't make it any different, as far as the tuning is concerned, though. I'm sure it will be the same issue, no matter what version of the DB...
 
Alex

Am I in a Catch-22 situation? We want to explore converting the database from Oracle to SQL. We have to upgrade to SP23 because of the Operating System on the Enterprise server we are going use. I'm hearing the same thing Peter is hearing about 9i being a requirement for SP23. SP23 was delivered with libora81.sl, can't I just change the datasources to point to this file? I'm currently pointing to libora80.sl. It seems crazy to have to upgrade Oracle when our objective is to get rid of it.

Patty
 
Patty,

I believe you should be able to use the libora80.sl and libora81.sl interchangably, but make sure you update the datasource records in both System and Server Maps before updating your JDE.INI to point to the correct library. We've been on 8.9 for over a year and this is no longer a necessary step (the middleware handles this for you beyond 8.0 - and besides, >8i is a requirement due to Unicode).

Back when we were in the database upgrade mode to 9iR2 (still on Xe but wanting to get to the supported DB release prior to the 8.9 upgrade), we had some issues with the Oracle 9i libary driver. Using the libora81.sl I was able to rectify some serious batch processing issues on the UNIX enterprise server (it worked just fine!). Interestingly, I never saw a single issue related to the Client running on Citrix, regardless of which shared library we used for those datasource pointers.
 
Patty,

No worries, you certainly can and it will likely work allright. The only issue would be that Oracle will probably refuse to support any DB-related issues, like that performance issue, this thread started with...
 
Alex,

Thanks for the SP MTR headsup. I had a look at the SP23 documentation we have and none of it mentions the change in the MTR. So I can be aware of this in the future, where is this documented? Or was the MTR changed when SP23 was released?
 
Peter,

I'm not aware of any explicit references.

I go by the fact, that MTR's change as new SP's are released, so at the time of SP13 the MTR said 8i, somewhere around SP20 it changed to 9i (rel. 1) and then later - to 9i (rel. 2).

If you do not record this as it happens, there's no official record to refer to, so you will have to go by your memory...
 
Alex,

Many thanks. It wasn't what I was hoping to read, but it is good to know.
 
Back
Top