I 'm following up a DB crash due to ORA-00018 (See ORA-00018.ora-code.com). Seems V$sessions and V$license
only report "user " sessions not "all " sessions that the process/sessions
parameters work off so even though we monitor v$sessions and never see it
above 350 (which is when Windows runs out of memory anyway) we actually bust
500 (process*1.1+5). Metalink suggest monitoring "true " session usage via:
select count(*) from x$ksuse where bitand(ksspaflg,1)!=0;
And sure enough there are times when there are >30% more sessions
"connected " than v$session would have us believe.
Digging further I find a lot of "simple " selects as being run "recursive " as
"sys " via the x$ksuse (and hence bumping up the number of true sessions).
So my question is why?
An example query that I found by mapping sql/hash from x$ksuse to v$sql is:
SELECT job_count FROM ssjr WHERE company_id=64412
Which is run over JDBC thin by our monitoring tool via a user that does not
own the table ssjr (it is owned by another and granted to the tool with a
public synonym). For some reason the monitoring tool has set up its user
with SELECT ANY TABLE priv too.
So (eventually) the question: Why does Oracle have to run this simple query
as recursive? The user/schema columns in x$ksuse are SYS not the monitor
tool user or table owner. I suspect that select any table might be something
to do with it, or maybe the public synonym but could not find anything on
metalink to back this up. I cannot easlily remove select any table priv to
prove this as then the monitoring tool will fail although if this is the
culprit I will have it rebuilt using grants to specific objects (planned
downtime etc etc).
