Monitoring resource limits and utilization history is crucial for maintaining an Oracle database. By analyzing current usage and historical data, you can identify trends, optimize resources, and avoid performance bottlenecks. This guide walks you through commands to check session and process limits as well as their historical utilization.<\/p>\n\n\n\n
Use the following command to understand the columns and their significance:<\/p>\n\n\n\n
SQL>DESC V$RESOURCE_LIMIT\n\nName Type\n--------------------- --------------\nRESOURCE_NAME VARCHAR2(30)\nCURRENT_UTILIZATION NUMBER\nMAX_UTILIZATION NUMBER\nINITIAL_ALLOCATION VARCHAR2(40)\nLIMIT_VALUE VARCHAR2(40)<\/code><\/pre>\n\n\n\nColumn<\/strong><\/th>Description<\/strong><\/th><\/tr><\/thead>RESOURCE_NAME<\/strong><\/td>Name of the resource (e.g., sessions, processes).<\/td><\/tr> CURRENT_UTILIZATION<\/strong><\/td>Current usage of the resource.<\/td><\/tr> MAX_UTILIZATION<\/strong><\/td>Maximum usage since the last database startup.<\/td><\/tr> INITIAL_ALLOCATION<\/strong><\/td>Initial allocated value from the initialization parameter file.<\/td><\/tr> LIMIT_VALUE<\/strong><\/td>Defined limit for the resource (can be UNLIMITED for infinite allocation).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nCheck Current Session and Process Limits<\/h2>\n\n\n\nSET LINESIZE 150\nSET PAGESIZE 50\nCOL resource_name FOR A30 \nCOL current_utilization FOR 9999\nCOL max_utilization FOR 9999\nCOL limit_value FOR 9999\n\nSELECT * \nFROM v$resource_limit;<\/code><\/pre>\n\n\n\nOutput<\/strong>:<\/p>\n\n\n\nRESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE CON_ID\n------------------------------ ------------------- --------------- -------------------- ---------------------------------------- ----------\nprocesses 76 95 640 640 0\nsessions 88 124 984 984 0\nenqueue_locks 25 41 11660 11660 0\nenqueue_resources 17 61 4548 UNLIMITED 0\nges_procs 0 0 0 0 0\nges_ress 0 0 0 UNLIMITED 0\nges_locks 0 0 0 UNLIMITED 0\nges_cache_ress 0 0 0 UNLIMITED 0\nges_reg_msgs 0 0 0 UNLIMITED 0\nges_big_msgs 0 0 0 UNLIMITED 0\nges_rsv_msgs 0 0 0 0 0\ngcs_resources 0 0 0 UNLIMITED 0\ngcs_shadows 0 0 0 UNLIMITED 0\nsmartio_overhead_memory 0 ##### 0 UNLIMITED 0\nsmartio_buffer_memory 0 0 0 UNLIMITED 0\nsmartio_metadata_memory 0 0 0 UNLIMITED 0\nsmartio_sessions 0 1 0 UNLIMITED 0\ndml_locks 0 37 4328 UNLIMITED 0\ntemporary_table_locks 0 5 UNLIMITED UNLIMITED 0\ntransactions 0 5 1082 UNLIMITED 0\nbranches 0 0 1082 UNLIMITED 0\ncmtcallbk 0 3 1082 UNLIMITED 0\nmax_rollback_segments 11 11 1082 65535 0\nsort_segment_locks 2 5 UNLIMITED UNLIMITED 0\nk2q_locks 0 0 1968 UNLIMITED 0\nmax_shared_servers 1 2 UNLIMITED UNLIMITED 0\nparallel_max_servers 16 16 160 32767 0\n\n27 rows selected.<\/code><\/pre>\n\n\n\nYou can also check for specific resource like sessions, processes etc. like below. <\/p>\n\n\n\n
SET LINESIZE 150\nSET PAGESIZE 50\nCOL resource_name FOR A20\nCOL current_utilization FOR 9999\nCOL max_utilization FOR 9999\nCOL limit_value FOR 9999\n\nSELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE \nFROM v$resource_limit \nWHERE resource_name = 'sessions';<\/code><\/pre>\n\n\n\nOutput:<\/strong><\/p>\n\n\n\nRESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE\n-------------------- ------------------- --------------- ----------------------------------------\nprocesses 76 95 640\nsessions 88 124 984<\/code><\/pre>\n\n\n\nCheck Historical Resource Limits<\/h2>\n\n\n\n
Use this query to view historical data of session and process limits:<\/p>\n\n\n\n
SET LINESIZE 150\nSET PAGESIZE 50\nCOL resource_name FOR A20\nCOL current_utilization FOR 9999\nCOL max_utilization FOR 9999\nCOL limit_value FOR 9999\nCOL initial_allocation FOR A20\n\nSELECT * \nFROM DBA_HIST_RESOURCE_LIMIT \nWHERE resource_name IN ('sessions', 'processes');<\/code><\/pre>\n\n\n\nOutput:<\/strong> <\/p>\n\n\n\nRESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE SNAP_ID START_INTERVAL_TIME END_INTERVAL_TIME\n-------------- ------------------- ---------------- ------------- ------- ----------------------- ----------------------\nsessions 100 150 300 1 2025-01-01 00:00:00 2025-01-01 01:00:00\nprocesses 50 80 100 1 2025-01-01 00:00:00 2025-01-01 01:00:00<\/code><\/pre>\n\n\n\nRetrieve Historical Resource Utilization<\/h2>\n\n\n\n
To check utilization history with a timestamp, use the query below:<\/p>\n\n\n\n
Sessions:<\/h3>\n\n\n\nSET LINESIZE 150\nSET PAGESIZE 50\nCOL snapshottime FOR A20\nCOL resource_name FOR A20\nCOL current_utilization FOR 9999\nCOL max_utilization FOR 9999\nCOL limit_value FOR 9999\n\nSELECT s.SNAP_ID, TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,\n resource_name, current_utilization, max_utilization, limit_value \nFROM DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b\nWHERE s.snap_id = b.snap_id \n AND resource_name IN ('sessions') \nORDER BY s.END_INTERVAL_TIME DESC;<\/code><\/pre>\n\n\n\nThis query retrieves historical utilization data for sessions with a timestamp, helping to understand resource usage patterns over time.<\/p>\n\n\n\n
Output:<\/strong><\/p>\n\n\n\nSNAP_ID Snapshottime RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE\n-------- ------------------- -------------- ------------------- ---------------- -------------\n1 01-JAN 12:00:00 sessions 100 150 300<\/code><\/pre>\n\n\n\nProcesses:<\/h2>\n\n\n\nSET LINESIZE 150\nSET PAGESIZE 50\nCOL snapshottime FOR A20\nCOL resource_name FOR A20\nCOL current_utilization FOR 9999\nCOL max_utilization FOR 9999\nCOL limit_value FOR 9999\n\nSELECT s.SNAP_ID, TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,\n resource_name, current_utilization, max_utilization, limit_value \nFROM DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b\nWHERE s.snap_id = b.snap_id \n AND resource_name IN ('processes') \nORDER BY s.END_INTERVAL_TIME DESC;<\/code><\/pre>\n\n\n\nThis query retrieves historical utilization data for processes, displaying the resource usage at various time intervals.<\/p>\n\n\n\n
Output:<\/strong><\/p>\n\n\n\nSNAP_ID Snapshottime RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE\n-------- ------------------- -------------- ------------------- ---------------- -------------\n1 01-JAN 12:00:00 processes 50 80 100<\/code><\/pre>\n\n\n\nFind Timing of Peak Session Connections<\/h2>\n\n\n\n
To identify the exact time of peak session usage, adjust the CURRENT_UTILIZATION<\/code> threshold in the query:<\/p>\n\n\n\nSET LINESIZE 150\nSET PAGESIZE 50\nCOL snapshottime FOR A20\nCOL resource_name FOR A20\nCOL current_utilization FOR 9999\nCOL max_utilization FOR 9999\nCOL limit_value FOR 9999\n\nSELECT s.SNAP_ID, TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,\n resource_name, current_utilization, max_utilization, limit_value \nFROM DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b\nWHERE s.snap_id = b.snap_id \n AND resource_name IN ('sessions') \n AND current_utilization > 600 \nORDER BY s.END_INTERVAL_TIME DESC;<\/code><\/pre>\n\n\n\nThis query identifies the specific times when session usage exceeded a defined threshold, helping to pinpoint peak times for database connections.<\/p>\n\n\n\n
Output:<\/strong><\/p>\n\n\n\nSNAP_ID Snapshottime RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE\n-------- ------------------- -------------- ------------------- ---------------- -------------\n2 01-JAN 12:30:00 sessions 650 700 800<\/code><\/pre>\n\n\n\nConclusion<\/h2>\n\n\n\n
By using these commands, you can efficiently monitor both the current and historical resource usage of sessions and processes in Oracle. Understanding these trends helps in better capacity planning and ensuring the database operates optimally.<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction Monitoring resource limits and utilization history is crucial for maintaining an Oracle database. By analyzing current usage and historical data, you can identify trends, optimize resources, and avoid performance bottlenecks. This guide walks you through commands to check session and process limits as well as their historical utilization. Describe the V$RESOURCE_LIMIT View Use the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-2455","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2455","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=2455"}],"version-history":[{"count":4,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2455\/revisions"}],"predecessor-version":[{"id":3249,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/2455\/revisions\/3249"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=2455"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=2455"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=2455"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}