Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: 
set linesize 190
set pages 200
select * from gv$resource_limit;

    INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- --------------- ------------
         1 processes                                       44             300        300             300
         1 sessions                                        52             215        512             512
         1 enqueue_locks                                   30             295       6480            6480
         1 enqueue_resources                               24             165       2472       UNLIMITED
         1 ges_procs                                        0               0          0               0
         1 ges_ress                                         0               0          0       UNLIMITED
         1 ges_locks                                        0               0          0       UNLIMITED
         1 ges_cache_ress                                   0               0          0       UNLIMITED
         1 ges_reg_msgs                                     0               0          0       UNLIMITED
         1 ges_big_msgs                                     0               0          0       UNLIMITED
         1 ges_rsv_msgs                                     0               0          0               0
         1 gcs_resources                                    0               0  UNLIMITED       UNLIMITED
         1 gcs_shadows                                      0               0  UNLIMITED       UNLIMITED
         1 smartio_overhead_memory                          0           71704          0       UNLIMITED
         1 smartio_buffer_memory                            0               0          0       UNLIMITED
         1 smartio_metadata_memory                          0               0          0       UNLIMITED
         1 smartio_sessions                                 0               1          0       UNLIMITED
         1 dml_locks                                        1              29       2252       UNLIMITED
         1 temporary_table_locks                            0              28  UNLIMITED       UNLIMITED
         1 transactions                                     1               7        563       UNLIMITED
         1 branches                                         0               0        563       UNLIMITED
         1 cmtcallbk                                        0               3        563       UNLIMITED
         1 max_rollback_segments                           11              11        563           65535
         1 sort_segment_locks                               1              12  UNLIMITED       UNLIMITED
         1 k2q_locks                                        0               0       1024       UNLIMITED
         1 max_shared_servers                               1               1  UNLIMITED       UNLIMITED
         1 parallel_max_servers                             0               0        270            3600
This info is, as all v$ views, from instance startup to current time. So, in this example we can say that max processes limit was reached, and just now there are only 44 processes running. v$resource_limit is a very useful view for day-to-day work because you can glance in a moment if there is or were some process reaching his limit

If you check sessions, processes or another parameter and they are nearly or reaching their limits you can resize them by following this article: change parameters in Oracle
Please note that if you set processes value, oracle will automatically set sessions value to (1.1 * PROCESSES) + 5 for oracle 10g and (1.5 * PROCESSES) + 22 in oracle 11g and above...(for previous versions you should set both parameter accordingly)

And finally, you can obtain detailed and historical info with Oracle Historical Session Information

Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more