New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    701

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages
«  
  »

Troubleshoot ORA-00020: maximum number of processes exceeded – Systematic way

Situation: Application not able to connect and including sysdba sessions. Complete DB hung due to large number of session spike in very less time.

Error:-ORA-00020: maximum number of processes exceeded

Dependent parameters
sessions = 825
processes = 800

Action taken, We have killed some backups jobs and other local sessions and get a slot of sysdba connectivity at last.

Now troubleshooting, why, who are making this happen ORA-00020: maximum number of processes exceeded

Lets have a look at Resource limit to ensure we reached max utilization (800) of resources sessions/processes

col begin_interval_time format a30
select s.begin_interval_time, rl.current_utilization, rl.max_utilization
from DBA_HIST_RESOURCE_LIMIT rl, dba_hist_snapshot s
where resource_name = 'sessions' and rl.instance_number=1 and
s.snap_id = rl.snap_id and
s.instance_number = rl.instance_number
order by s.snap_id;

BEGIN_INTERVAL_TIME CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
22-NOV-12 20.00.26.150 266 422
22-NOV-12 21.00.31.261 255 422
22-NOV-12 22.00.36.407 213 422
22-NOV-12 23.00.43.342 218 422
23-NOV-12 00.00.49.774 248 422
23-NOV-12 01.00.55.275 221 422
23-NOV-12 02.00.02.368 210 422
23-NOV-12 03.00.07.255 250 422
23-NOV-12 04.00.15.639 211 885
23-NOV-12 05.57.30.381 355 885

See above the during 04:00 Am we have hit or spike in the sessions from 211/355 to 885

Lets have a look at process utilization


col begin_interval_time format a30
select s.begin_interval_time, rl.current_utilization, rl.max_utilization
from DBA_HIST_RESOURCE_LIMIT rl, dba_hist_snapshot s
where resource_name = 'processes' and rl.instance_number=1 and
s.snap_id = rl.snap_id and
s.instance_number = rl.instance_number
order by s.snap_id;

22-NOV-12 10.00.57.535 278 384
22-NOV-12 11.00.01.032 282 384
22-NOV-12 12.00.04.620 286 384
22-NOV-12 13.00.07.258 268 384
22-NOV-12 14.00.09.862 276 384
22-NOV-12 15.00.14.034 270 384
22-NOV-12 16.00.16.643 300 384
22-NOV-12 17.00.19.141 237 384
22-NOV-12 18.00.21.584 232 384
22-NOV-12 19.00.23.864 232 384
22-NOV-12 20.00.26.150 254 384
22-NOV-12 21.00.31.261 239 384
22-NOV-12 22.00.36.407 204 384
22-NOV-12 23.00.43.342 205 384
23-NOV-12 00.00.49.774 237 384
23-NOV-12 01.00.55.275 207 384
23-NOV-12 02.00.02.368 203 384
23-NOV-12 03.00.07.255 237 384
23-NOV-12 04.00.15.639 199 800
23-NOV-12 05.57.30.381 335 800

Let check the sysstat has an entries about it, nopes it does not listed the maximum utilization as it missed to collected the stats.

select to_char(begin_interval_time, 'mm-dd-yy hh24:mi'),
sum(value) value
from DBA_HIST_SYSSTAT s, dba_hist_snapshot h
where s.snap_id=h.snap_id
and s.instance_number=h.instance_number
and STAT_NAME like '%logons current%'
and begin_interval_time > sysdate - 30
group by to_char( begin_interval_time,'mm-dd-yy hh24:mi')
order by 1;

TO_CHAR(BEGIN_ VALUE
-------------- ----------
11-15-12 23:00 135
11-16-12 00:00 122
11-16-12 01:00 129
11-16-12 02:00 187
11-16-12 03:00 144
11-16-12 04:00 193
11-16-12 05:00 236
11-16-12 06:00 169
11-16-12 07:00 258
11-16-12 08:00 270
...
...
...
11-23-12 02:00 202
11-23-12 03:00 236
11-23-12 04:00 198
11-23-12 05:57 334
11-23-12 05:57 334175 rows selected.
SQL>

Q1 Logon currents showing 334 only, why it is not showing?

Another area of interest to troubleshoot, dba_hist_sysmetric_summary gives you the hourly metrics of the statistics, we have to metrics that collected in database called Session Limit % and Process Limit % ,

SQL> select begin_time,metric_name,minval,maxval from DBA_HIST_SYSMETRIC_SUMMARY where metric_name='Session Limit %' order by begin_time;

22-NOV-12 Session Limit % 0 42.0338983
22-NOV-12 Session Limit % 0 30.2824859
22-NOV-12 Session Limit % 0 28.9265537
22-NOV-12 Session Limit % 0 25.6497175
23-NOV-12 Session Limit % 0 27.6836158
23-NOV-12 Session Limit % 0 28.8135593
23-NOV-12 Session Limit % 0 27.4576271
23-NOV-12 Session Limit % 0 40.6779661
23-NOV-12 Session Limit % 0 97.0621469 ---> Increase
23-NOV-12 Session Limit % 0 45.4237288
23-NOV-12 Session Limit % 0 39.0960452

SQL> select begin_time,metric_name,minval,maxval from DBA_HIST_SYSMETRIC_SUMMARY where metric_name='Process Limit %' order by begin_time;

22-NOV-12 Process Limit % 0 27.25
23-NOV-12 Process Limit % 0 29.5
23-NOV-12 Process Limit % 0 30.75
23-NOV-12 Process Limit % 0 29.125
23-NOV-12 Process Limit % 0 41.625
23-NOV-12 Process Limit % 0 99.875 --> Increase
23-NOV-12 Process Limit % 0 45.875
23-NOV-12 Process Limit % 0 41.25

You see above there was a sudden jump in the session and process limit % increase from 49% to 99% , so we determined now there are some obvious/dubios connections happening.

Q2, How to find the which users or machine or terminal has most connections during the period?

If you have audit enabled: use aud$ by filtering the statement column with "%logon%';

Use active session history, but the sample of 10 sec to 1 entry in active session history make the sessions count big large, we have to rollup here something,

I still have to write queries for both of above and publish soon.

Update 21-Feb-2013:-
Few more queries, if you have a Grid Control Enabled and targets created, you can use the below queries.

SQL> select COLLECTION_TIMESTAMP,to_number(value) from sysman.mgmt$metric_details where metric_label = 'User-Defined SQL Metrics' and key_value='CONCURRENT_SESSIONS' and COLLECTION_TIMESTAMP between '19/02/2013 17:30:00' and '19/02/2013 18:30:00' and target_name='' and value is not null order by collection_timestamp;

COLLECTION_TIMESTAM TO_NUMBER(VALUE)
------------------- ----------------
19/02/2013 17:32:50 1965
19/02/2013 17:37:50 1917
19/02/2013 17:42:50 1800
19/02/2013 17:47:50 1758
19/02/2013 17:52:50 1719
19/02/2013 17:57:50 1856
19/02/2013 18:02:50 1937
19/02/2013 18:07:50 1714
19/02/2013 18:12:50 2061
19/02/2013 18:17:50 1737
19/02/2013 18:22:50 1518
19/02/2013 18:27:50 1708

12 rows selected.

SQL> select COLLECTION_TIMESTAMP,to_number(value) from sysman.mgmt$metric_details where metric_label = 'User-Defined SQL Metrics' and key_value='CONCURRENT_SESSIONS_ACTIVE' and COLLECTION_TIMESTAMP between '19/02/2013 17:30:00' and '19/02/2013 18:30:00' and target_name='' and value is not null order by collection_timestamp;

COLLECTION_TIMESTAM TO_NUMBER(VALUE)
------------------- ----------------
19/02/2013 17:32:03 24
19/02/2013 17:37:02 113
19/02/2013 17:42:02 31
19/02/2013 17:47:02 26
19/02/2013 17:52:03 39
19/02/2013 17:57:02 179
19/02/2013 18:02:03 443
19/02/2013 18:07:02 202
19/02/2013 18:12:03 524
19/02/2013 18:17:03 325
19/02/2013 18:22:02 29
19/02/2013 18:27:02 31

-Thanks
Geek DBA

November 26th, 2012 | Tags: , | Category: Administration

Comments are closed.