Frequently executed sql

We have several sql statements that are virtually locked into the SGA of our database due to the frequency which they are executed. They range from once a centi-second to 3 to 4 times a centi-second. From looking at snap-shots of our database, they appear to be coming from oexplorer, jdequeues and jdenet_k type of connections.
I'm trying to figure out if this is a bug in the reporting of the number of times they are being executed or if they actually are being executed that often and if so, then how and why. This happens on our production and our test systems. The test system should be almost dormant as compared to our production system, but the issue still shows up.

Anyone else notice this behavior on their system?

It is likely that the JDE processes "poll" the database often, even when idle. What version of JDE are you running ? The configuration would also impact how much database impact you'd see.

OW Xe Update 8 sp23 r1


if this is indeed a polling issue, then what determines what sql statement is used and how often?

If F986110 is one of the tables involved then the batch queue polling is happening too frequently -or- you've run into the mysterious "F986110 full table scans bug".

To correct the polling too frequently issue - see Posts #111017 and #56600.

To correct the issue of Oracle doing full table scans on every Poll instead of using the indices make sure that you regularly compute stats on the F986110 table.

You should also periodically purge the F986110 table using R9861101.

Regards,

very good information, thank you. I was unaware of the polling that is setup. What determines the sql that is used for this polling? I've noticed similar activities with a few other sql statements. The statements in question aren't necessarily strenuous to execute one time but when you start executing them 5 to 10 times every centi-second it can cause some overhead. They also get locked into the library cache as a side effect ( which isn't necessarily a bad thing either ).

Here are a few of the statements that I have seen exhibit similar behaviors taken from a one hour time period. The numbers before each statement are

# executions, rows, rows/exe, cpu/exe, elapsed time/exe, hash id

574,218 0 0.0 0.00 0.00 18pjk2d4u1ap8
Module: runbatch_ow@japp1 (TNS V1-V3)
SELECT * FROM PRODDTA.F49090 WHERE ( CZMMCU = :KEY1 AND CZWDCT = :KEY2 AND C
ZWDCK = :KEY3 AND CZCTRY = :KEY4 AND CZYR = :KEY5 AND CZMT = :KEY6 )


42,331 407 0.0 0.00 0.00 arzau9n48581y
Module: jdequeue@jent1 (TNS V1-V3)
SELECT * FROM SVM7333.F986110 WHERE ( JCJOBQUE = :KEY1 AND JCJOBSTS = :KEY2
AND JCEXEHOST = :KEY3 AND JCFUNO = :KEY4 AND JCPRTQ = :KEY5 ) ORDER BY JCJOBPTY
ASC,JCACTDATE ASC,JCACTTIME ASC,JCJOBTYPE ASC

39,912 39,436 1.0 0.00 0.00 g4pfx2dzf81xp
Module: oexplore.exe
SELECT * FROM PRODDTA.F4801 WHERE ( WADOCO = :KEY1 )

The F49090 was the first one that stood out to me because of the higher # of executions with 0 rows returned.