Yesterday I was working on issue where our Monitoring system reported increased number of database connections. We were already aware of this issue and application team was asked to look into it.

On checking GV$RESOURCE_LIMIT, we found that it had touched the limit value on Instance 1 (since startup).

 SELECT INST_ID "ID",RESOURCE_NAME "RESOURCE",CURRENT_UTILIZATION
CURRENT",MAX_UTILIZATION "MAX",INITIAL_ALLOCATION "INITIAL",LIMIT_VALUE FROM
V$RESOURCE_LIMIT WHERE RESOURCE_NAME='sessions';

ID RESOURCE CURRENT MAX  INITIAL LIMIT_VALUE
--  -------   --------- ----- ----     -----------
1   sessions     232       335   335        335
2   sessions     245       314   335        335

This was quite surprising as we did not get any Critical tickets. (I think this is because polling setting for tool for this parameter was set to 10 minutes).

I checked the alert log for any ORA-18 errors on Instance 1, but could not find any error. I had faced this situation sometime back (with different client) when user complained about getting ORA-18 error but during that time too, there were no entries in Alert log. It could be that ORA-18 errors are not recorded (unlike ORA -20, number of processes exceeding errors).

Since it was not recorded in Alert log (nor any trace file), I turned to AWR to help me out.In this case view WRH$_RESOURCE_LIMIT was the one which we needed to look as it stores statistics captured using AWR snapshots.

SQL> col BEGIN_INTERVAL_TIME for a28
SQL> col END_INTERVAL_TIME for a28
SQL>col RESOURCE_NAME for a10
SELECT A.SNAP_ID,A.INSTANCE_NUMBER "ID",B.BEGIN_INTERVAL_TIME,B.END_INTERVAL_TIME,A.RESOURCE_NAME,
CURRENT_UTILIZATION "CURRENT",MAX_UTILIZATION "MAX"
FROM WRH$_RESOURCE_LIMIT A, WRM$_SNAPSHOT B
WHERE A.RESOURCE_NAME LIKE '%session%'
AND A.SNAP_ID=B.SNAP_ID
AND A.INSTANCE_NUMBER= B.INSTANCE_NUMBER
AND ((A.INSTANCE_NUMBER=1
AND B.BEGIN_INTERVAL_TIME > (SELECT STARTUP_TIME FROM GV$INSTANCE WHERE INSTANCE_NUMBER=1)) OR
(A.INSTANCE_NUMBER=2 AND B.BEGIN_INTERVAL_TIME >
(SELECT STARTUP_TIME FROM GV$INSTANCE WHERE INSTANCE_NUMBER=2)))
order by A.INSTANCE_NUMBER,A.SNAP_ID;

SNAP ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME          RESOURCE CUR   MAX
---- ---------- ---------------    ------------------------  --------- ---  ---

936  1 28-JUN-08 05.00.04.133 AM  28-JUN-08 06.00.14.342 AM  sessions  212  297
937  1 28-JUN-08 06.00.14.342 AM  28-JUN-08 07.00.54.865 AM  sessions  255  323
938  1 28-JUN-08 07.00.54.865 AM  28-JUN-08 08.00.18.080 AM  sessions  227  323
939  1 28-JUN-08 08.00.18.080 AM  28-JUN-08 09.00.29.881 AM  sessions  101  335

936  2 28-JUN-08 05.00.03.797 AM  28-JUN-08 06.00.14.117 AM  sessions  233  292
937  2 28-JUN-08 06.00.14.117 AM  28-JUN-08 07.00.54.521 AM  sessions  247  310
938  2 28-JUN-08 07.00.54.521 AM  28-JUN-08 08.00.17.725 AM  sessions  253  314
939  2 28-JUN-08 08.00.17.725 AM  28-JUN-08 09.00.29.648 AM  sessions  101  314

We cannot get the exact time, but we can see that number of Database sessions reached 335 sessions on Instance 1 between 28-JUN-08 08.00.18.080 AM AND 28-JUN-08 09.00.29.881

Similarly for Instance 2, number of Database sessions reached to value of 314 between 28-JUN-08 07.00.54.521 AM AND 28-JUN-08 08.00.17.725 AM

This information was sufficient at this time to ask Application support team to check from their end. Though it would have been really nice to find the exact time. Also finding which users were having most sessions would have also helped to point the module.