How to Check Resource Limits and Utilization History in Oracle
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 following command to understand the columns and their significance:
SQL>DESC V$RESOURCE_LIMIT
Name Type
--------------------- --------------
RESOURCE_NAME VARCHAR2(30)
CURRENT_UTILIZATION NUMBER
MAX_UTILIZATION NUMBER
INITIAL_ALLOCATION VARCHAR2(40)
LIMIT_VALUE VARCHAR2(40)| Column | Description |
|---|---|
| RESOURCE_NAME | Name of the resource (e.g., sessions, processes). |
| CURRENT_UTILIZATION | Current usage of the resource. |
| MAX_UTILIZATION | Maximum usage since the last database startup. |
| INITIAL_ALLOCATION | Initial allocated value from the initialization parameter file. |
| LIMIT_VALUE | Defined limit for the resource (can be UNLIMITED for infinite allocation). |
Check Current Session and Process Limits
SET LINESIZE 150
SET PAGESIZE 50
COL resource_name FOR A30
COL current_utilization FOR 9999
COL max_utilization FOR 9999
COL limit_value FOR 9999
SELECT *
FROM v$resource_limit;Output:
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE CON_ID
------------------------------ ------------------- --------------- -------------------- ---------------------------------------- ----------
processes 76 95 640 640 0
sessions 88 124 984 984 0
enqueue_locks 25 41 11660 11660 0
enqueue_resources 17 61 4548 UNLIMITED 0
ges_procs 0 0 0 0 0
ges_ress 0 0 0 UNLIMITED 0
ges_locks 0 0 0 UNLIMITED 0
ges_cache_ress 0 0 0 UNLIMITED 0
ges_reg_msgs 0 0 0 UNLIMITED 0
ges_big_msgs 0 0 0 UNLIMITED 0
ges_rsv_msgs 0 0 0 0 0
gcs_resources 0 0 0 UNLIMITED 0
gcs_shadows 0 0 0 UNLIMITED 0
smartio_overhead_memory 0 ##### 0 UNLIMITED 0
smartio_buffer_memory 0 0 0 UNLIMITED 0
smartio_metadata_memory 0 0 0 UNLIMITED 0
smartio_sessions 0 1 0 UNLIMITED 0
dml_locks 0 37 4328 UNLIMITED 0
temporary_table_locks 0 5 UNLIMITED UNLIMITED 0
transactions 0 5 1082 UNLIMITED 0
branches 0 0 1082 UNLIMITED 0
cmtcallbk 0 3 1082 UNLIMITED 0
max_rollback_segments 11 11 1082 65535 0
sort_segment_locks 2 5 UNLIMITED UNLIMITED 0
k2q_locks 0 0 1968 UNLIMITED 0
max_shared_servers 1 2 UNLIMITED UNLIMITED 0
parallel_max_servers 16 16 160 32767 0
27 rows selected.You can also check for specific resource like sessions, processes etc. like below.
SET LINESIZE 150
SET PAGESIZE 50
COL resource_name FOR A20
COL current_utilization FOR 9999
COL max_utilization FOR 9999
COL limit_value FOR 9999
SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM v$resource_limit
WHERE resource_name = 'sessions';Output:
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------------- ------------------- --------------- ----------------------------------------
processes 76 95 640
sessions 88 124 984Check Historical Resource Limits
Use this query to view historical data of session and process limits:
SET LINESIZE 150
SET PAGESIZE 50
COL resource_name FOR A20
COL current_utilization FOR 9999
COL max_utilization FOR 9999
COL limit_value FOR 9999
COL initial_allocation FOR A20
SELECT *
FROM DBA_HIST_RESOURCE_LIMIT
WHERE resource_name IN ('sessions', 'processes');Output:
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE SNAP_ID START_INTERVAL_TIME END_INTERVAL_TIME
-------------- ------------------- ---------------- ------------- ------- ----------------------- ----------------------
sessions 100 150 300 1 2025-01-01 00:00:00 2025-01-01 01:00:00
processes 50 80 100 1 2025-01-01 00:00:00 2025-01-01 01:00:00Retrieve Historical Resource Utilization
To check utilization history with a timestamp, use the query below:
Sessions:
SET LINESIZE 150
SET PAGESIZE 50
COL snapshottime FOR A20
COL resource_name FOR A20
COL current_utilization FOR 9999
COL max_utilization FOR 9999
COL limit_value FOR 9999
SELECT s.SNAP_ID, TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,
resource_name, current_utilization, max_utilization, limit_value
FROM DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
WHERE s.snap_id = b.snap_id
AND resource_name IN ('sessions')
ORDER BY s.END_INTERVAL_TIME DESC;This query retrieves historical utilization data for sessions with a timestamp, helping to understand resource usage patterns over time.
Output:
SNAP_ID Snapshottime RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------- ------------------- -------------- ------------------- ---------------- -------------
1 01-JAN 12:00:00 sessions 100 150 300Processes:
SET LINESIZE 150
SET PAGESIZE 50
COL snapshottime FOR A20
COL resource_name FOR A20
COL current_utilization FOR 9999
COL max_utilization FOR 9999
COL limit_value FOR 9999
SELECT s.SNAP_ID, TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,
resource_name, current_utilization, max_utilization, limit_value
FROM DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
WHERE s.snap_id = b.snap_id
AND resource_name IN ('processes')
ORDER BY s.END_INTERVAL_TIME DESC;This query retrieves historical utilization data for processes, displaying the resource usage at various time intervals.
Output:
SNAP_ID Snapshottime RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------- ------------------- -------------- ------------------- ---------------- -------------
1 01-JAN 12:00:00 processes 50 80 100Find Timing of Peak Session Connections
To identify the exact time of peak session usage, adjust the CURRENT_UTILIZATION threshold in the query:
SET LINESIZE 150
SET PAGESIZE 50
COL snapshottime FOR A20
COL resource_name FOR A20
COL current_utilization FOR 9999
COL max_utilization FOR 9999
COL limit_value FOR 9999
SELECT s.SNAP_ID, TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,
resource_name, current_utilization, max_utilization, limit_value
FROM DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
WHERE s.snap_id = b.snap_id
AND resource_name IN ('sessions')
AND current_utilization > 600
ORDER BY s.END_INTERVAL_TIME DESC;This query identifies the specific times when session usage exceeded a defined threshold, helping to pinpoint peak times for database connections.
Output:
SNAP_ID Snapshottime RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------- ------------------- -------------- ------------------- ---------------- -------------
2 01-JAN 12:30:00 sessions 650 700 800Conclusion
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.
