We have an ongoing issue after an upgrade to 11.2 from 10.2 where the same application used to run flawless; now some
queries take an exceptionally long time to complete while the CPU on the server spinning at 100% for several minutes, an example
on one of those query is:
KEY_ARTI.CADESART AS MVDESART,
KEY_ARTI.CADESSUP AS MVDESSUP,
EVA_COMP.MVDESSUP AS MVDESSU1,
EVA_ARTI.CPROWORD AS ROWDISP,
EVA_ARTI.MVCODART AS CODART,
GIOPLART_ICOL ART_ICOL INNER JOIN GIOPLEVA_COMP EVA_COMP ON EVA_COMP.MVCODART=ART_ICOL.ARCODART
INNER JOIN GIOPLKEY_ARTI KEY_ARTI ON EVA_COMP.MVCODICE=KEY_ARTI.CACODICE
INNER JOIN GIOPLEVA_ARTI EVA_ARTI ON EVA_ARTI.MVSERIAL=EVA_COMP.MVSERIAL
LEFT OUTER JOIN GIOPLVOCIIVA VOCIIVA ON VOCIIVA.IVCODIVA=ART_ICOL.ARCODIVA
WHERE (EVA_COMP.MVSERIAL = '0000023301'
AND EVA_COMP.MVTIPREC IN ('1', '2'))
ORDER BY 1,2,3
Note that the biggest table in this example has 3k rows while GIOPLVOCIIVA has 4/5 rows so I don't expect a combinatorial explosion due to the outer join and the
size of the computation should be well beyond the capabilities of our (last gen - 1) Xeon CPU.
Moreover the issue manifest itself when the database is accessed by the instant_client ODBC, in that case we have the CPU _on the server_ spinning and the
query above takes 5-6 minutes to complete, the same query issued on the same client machine by JDBC is instantaneous; the execution is slow even from sqlplus on the client
but not from sqlplus on the server. Moreover the execution plans in both case are completely different, it's like if for the ODBC access we're bypassing all the indexes.
Oracle in installed on a Centos 7 distribution, I've checked the kernel parameters like shmmax and they are fine but I don't see the problem in our specific setup because
while the oracle process is spinning the rest of the system is almost idle, if the problem is caused by a kernel / filesystem issues I'd rather expect some kernel thread to be busy as well.
The client uses instant_client 18.104.22.168.0 32bit installed on Windows 10 Pro creators update at 64bit.