Wednesday, August 16, 2017

Find current and Max utilization of processes, sessions,resources..etc

I see that many users are trying to find the current utilization and max utilization of processes, sessions, enqueue_locks, resources…etic.

You will get the resource limit details from v$resoruce_limit

SQL> select resource_name, current_utilization, max_utilization from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
processes                                      223             235
sessions                                       232             250
enqueue_locks                                  231             264
enqueue_resources                               34              72
ges_procs                                        0               0
ges_ress                                         0               0
ges_locks                                        0               0
ges_cache_ress                                   0               0
ges_reg_msgs                                     0               0
ges_big_msgs                                     0               0
ges_rsv_msgs                                     0               0

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
gcs_resources                                    0               0
gcs_shadows                                      0               0
smartio_overhead_memory                          0           71704
smartio_buffer_memory                            0               0
smartio_metadata_memory                          0               0
smartio_sessions                                 0               1
dml_locks                                        0              27
temporary_table_locks                            0               3
transactions                                     0               8
branches                                         0               0
cmtcallbk                                        0               5

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
max_rollback_segments                           11              11
sort_segment_locks                               5              18
k2q_locks                                        0               0
max_shared_servers                               0               0
parallel_max_servers                             0               5