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.
