This is version 11.2.0.4 of Oracle. And we have multiple databases on this host and we suddenly saw the database is not responding and the host memory usage went up to ~100% as we saw from OEM. And then while digging into the issue, we saw one of the database started executing some queries from program "JDBC Thin client" and were suffering from concurrency waits "Library cache: mutex X". The queries looks like below. We endup killing the processes in that database to mitigate the issue.
We were trying to see the number of executions but i am not seeing these queries logged in DBA_HISt_SQLSTAT somehow, and considering we are holding just ~8days worth of AWR data, not seeing these queries in those last ~8days in DBA_HIST_ACTIVE_SESS_HISTORY too.
So wondering , why any of the java application can generate such queries as its looks to be querying some internal SYS views and even it does what wrong with these which consumed all the memory which is around ~500GB+ impacting other databases to freeze. And i see in dba_hist_pgastat the "total PGA allocated" does reach out to ~500GB+ at specific time not sure how as because i see the max (pga_allocated) in dba_hist_active_sess_history during that point, it was showing ~3GB .
SELECT obj#, type#, ctime, mtime, stime, status,dataobj#, flags,oid$,spare1,spare2
FROM obj$
WHERE owner# = :1
AND name = :2
AND namespace = :3
AND (remoteowner = :4 OR remoteowner IS NULL AND :4 IS NULL)
AND (linkname = :5 OR linkname IS NULL AND :5 IS NULL)
AND (subname = :6 OR subname IS NULL AND :6 IS NULL)