Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, vishnudas.

Asked: June 01, 2018 - 12:13 pm UTC

Last updated: February 10, 2020 - 4:16 am UTC

Version: 11.2.0.1.0

Viewed 50K+ times! This question is

You Asked

Hi there,


For the past few days,during the peak hours i am getting the following error in a frequent way

" Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found "

I googled it first,They all says " increase the number of processes"

But i have some serious doubts here,please help me to figure it out..

Following are the details for my database..
SHOW PARAMETER PROCESS;


NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
aq_tm_processes                                    integer     0                                                                                                    
cell_offload_processing                            boolean     TRUE                                                                                                 
db_writer_processes                                integer     1                                                                                                    
gcs_server_processes                               integer     0                                                                                                    
global_txn_processes                               integer     1                                                                                                    
job_queue_processes                                integer     1000                                                                                                 
log_archive_max_processes                          integer     4                                                                                                    
processes                                          integer     1000                                                                                                 


SHOW PARAMETER SESSION;


NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
java_max_sessionspace_size                         integer     0                                                                                                    
java_soft_sessionspace_limit                       integer     0                                                                                                    
license_max_sessions                               integer     0                                                                                                    
license_sessions_warning                           integer     0                                                                                                    
session_cached_cursors                             integer     50                                                                                                   
session_max_open_files                             integer     10                                                                                                   
sessions                                           integer     1528                                                                                                 
shared_server_sessions                             integer                                                                                                          

so my session and process parameter values are 1528 and 1000 respectively 
 
When i am executing this query  i would get the current value for session and process,

SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ( 'sessions', 'processes');


RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------------------ ------------------- --------------- -----------
processes                                      999            1000       1000  
sessions                                      1007            1009       1528


my doubt is

1) what is this "MAX_UTILIZATION " and "LIMIT_VALUE" columns implies??
2) LIMIT_VALUE column for processes is showing 1000 does this mean i can't alter this value to a higher???
3)According to the value of sessions from the output how much should set for process parameter??






and Connor said...

CURRENT_UTILIZATION is what you are using *right now*
MAX_UTILIZATION is the high water mark (ie, what we've seen since the instance was started)
LIMIT_VALUE is what you cannot go higher than.

So in your case, you've hit your limit of 1000 and hence cannot start more sessions.

Two things to do here

1) Increase your processes parameter to workaround the issue

2) Do some analysis as to why you have 1000 processes connected to the database. 1000 *might* be ok (eg, a Forms environment where each user gets their own session), or it might *not* be ok (eg poor design, process leaks etc).

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 06, 2018 - 6:00 am UTC

Thank you so much for your valuable reply.. :) :) :)
Connor McDonald
June 07, 2018 - 1:23 am UTC

glad we could help

Short Follow up

Hekuran Hajdari, February 06, 2020 - 1:11 pm UTC

Thank you for the clarification, as usual this place has the most authentic answers so far.

We needed a clarification on below problem which has to do with processes and inactive processes vs killed.

select total, active, inactive, system, killed
    from
       (select count(*) total from v$session)
     , (select count(*) system from v$session where username is null)
     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)
     , (select count(*) inactive from v$session where status = 'INACTIVE')
     , (select count(*) killed from v$session where status = 'KILLED');


Above statement returns the below results.
TOTAL ACTIVE INACTIVE SYSTEM KILLED
1707  5   219   53   1430



As shown majority of the processes are killed however the MAX_UTILIZATION and the CURRENT_UTILIZATION values are as below.

SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ( 'sessions', 'processes');

processes 1902 2005       5500
sessions 1734 1944       8280



Our DB does not accept more processes when the limit is reached. Our question is how to get rid of the KILLED sessions which are occupying our resources?

Thank you in advance.
Connor McDonald
February 10, 2020 - 4:16 am UTC

Probably this

Bug 16494960 - killed session not being cleaned up (Doc ID 16494960.8)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.