Strange SQL submitted by JDE

[MattNorman]

hi,

I am hoping someone has some idea about some intermittent SQL we are seeing being submitted.

The SQL takes the form:

Select * from <table>
where (1=:1)

we have seen examples where the table is the F0006, F0901, F0004, F0010, F04257W, F0005 - as well as various custom tables.

We see SQL of this type being submitted by PSFT_USER and JDBC_THIN_CLIENT - so we know it is web client based.

Some of these tables are very large, and this SQL causes the application to hang whilst processing - because it causes full table scans to be performed.

(we capture the SQL using:

select *
from
DBA_HIST_ACTIVE_SESS_HISTORY ash,
V$SQLTEXT_WITH_NEWLINES swn
where
ash.sql_id = swn.sql_id
and sql_text like '% WHERE (1 = :1 )%'
order by sample_time
;

)


We cannot replicate at will however - it does not appear to be caused by a web restart, a cache clearance etc?

We know that this SQL cannot be created using table i/o - since fields are named... We know that when it does happen, and we see it, repeating with the same application, does not cause it... (and there is no slow behaviour...)

Has anyone else seen SQL of this format being submitted? Any ideas what causes it...


Thanks in advance


Matthew

JDE 8.10
8.97.1.2
Solaris
Oracle 10.2.0.4 (64 bit)

 

[craig_welton]

The web server runs those queries when it first accesses a table in an app. I believe it's a mechanism for getting the table's metadata which is cached for later references. At an iSeries client, this was causing massive delays and a system setting needed a tweak to get around it. Perhaps there is a similar setting for Oracle.

Craig