From time to time I see people on the public Oracle forums asking whether they should adjust one or other of the resource-related parameters – and it’s often the hidden or derived parameters that get targetted for this type of request. For example I came across a request fairly recently that said:
I’ve got a problem with the CF enqueue, I see lots of time spent on waits for this enqueue, should I increase the parameter _enqueue_locks?
Quick tip: if you have to ask a question like this the answer is almost certainly “no”. On the other hand if you can present a rational argument why an observation might point you to a parameter and an explanation of why you think the change might help there’s a simple check that you could do (for some parameters) before you ask the question. There’s a dynamic performance view that lists the utilitisation of a number of the special “resource” parameters and lets you see very easily whether you’re reaching the limit – it’s the view named in the title of this piece: v$resource_limit.
Here’s a simple script to report the contents of the view. It’s a script that has to be run by SYS (unless you care to grant suitable privileges to a non-SYS user), and in a container database it has to be executed in CDB$ROOT.
rem
rem Script: resource_limit.sql
rem Author: Jonathan Lewis
rem Dated: Feb 2007
rem
set linesize 180
set pagesize 60
set trimspool on
column resource_name format a32
column max_utilization format 999,999
column current_utilization format 999,999
column initial_allocation format a18
column limit_value format a11
spool resource_limit.lst
select
resource_name,
max_utilization,
current_utilization,
lpad(initial_allocation,18) initial_allocation,
lpad(limit_value,11) limit_value
from
v$resource_limit
;
spool off
The following is a sample output from a small 19.3 instances shortly after startup. In this case you can see that the RAC (ges) resources all show zero utilisation, and most of the others are fairly low.
RESOURCE_NAME MAX_UTILIZATION CURRENT_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------------------- --------------- ------------------- ------------------ -----------
processes 92 85 360 360
sessions 109 83 564 564
enqueue_locks 94 39 6644 6644
enqueue_resources 68 31 2700 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
gcs_resources 0 0 0 UNLIMITED
gcs_shadows 0 0 0 UNLIMITED
smartio_overhead_memory 171,032 0 0 UNLIMITED
smartio_buffer_memory 0 0 0 UNLIMITED
smartio_metadata_memory 0 0 0 UNLIMITED
smartio_sessions 1 0 0 UNLIMITED
dml_locks 29 0 2480 UNLIMITED
temporary_table_locks 5 0 UNLIMITED UNLIMITED
transactions 6 0 620 UNLIMITED
branches 0 0 620 UNLIMITED
cmtcallbk 3 0 620 UNLIMITED
max_rollback_segments 22 22 620 65535
sort_segment_locks 9 3 UNLIMITED UNLIMITED
k2q_locks 0 0 1128 UNLIMITED
max_shared_servers 2 1 UNLIMITED UNLIMITED
parallel_max_servers 26 26 32 32767
I’ve listed the columns in an order that doesn’t match the view definition because the thing we’re likely to be interested in is the maximum utilisation we’ve seen since instance startup – so that’s the one I’ve reported first. After that I’ve reported the current utilisation, and then the initial allocation (the value that, in many cases, is set by one of the startup parameters), followed by the limiting value for the resource.
In some cases the initial allocation is the same as the limiting value – processes and sessions (mapping to v$process/x$ksupr and v$session/x$ksuse) are good examples of this – these are fixed arrays defined when the instance starts – but in some cases the initial allocation is only a “reasonable” starting guess which allows Oracle to extend on demand, often through segmented arrays of 16 entries at a time, and in some cases there is no final limit to what resources you’re allowed (until the instance crashes with an ORA-04030 error, of course).
So, for example, to answer the question posed at the start of this note – should you increase the hidden parameter _enqueue_locks ? Certainly not for this instance because we can see the initial allocation is 6,644 and we’ve only reached a maximum of 94 enqueue locks allocated simultaneously.
AWR / Statspack
As with most of the dynamic performance views, there’s a summary report of v$resource_limit in the AWR (or Statspack) reports. The type of output you get is as follows;
Resource Limit Stats DB/Inst: OR19/or19 Snap: 4576
-> Only rows with Current or Maximum Utilization > 80% of Limit are shown
-> For "UNLIMITED" resources, rows whose Current or Maximum Utilization
exceeds 2*Initial Allocation are shown
-> Ordered by Resource Name
Current Maximum Initial
Resource Name Utilization Utilization Allocation Limit
------------------------------ ------------ ------------ ---------- ----------
smartio_overhead_memory 0 171,032 0 UNLIMITED
smartio_sessions 0 1 0 UNLIMITED
------------------------------------------------------
As you can see the output is strictly limited by an 80% “stress” condition – but there’s more data available if you query the dba_hist_resource_limit view (or wrh$_resource_limit table, or Statspack equivalents) directly. For example:
select
resource_name,
max_utilization,
current_utilization,
lpad(initial_allocation,18) initial_allocation,
lpad(limit_value,11) limit_value
from
wrh$_resource_limit
where
snap_id = 4849
and instance_number = 1
and dbid = 3091945231
and max_utilization != 0
;
RESOURCE_NAME MAX_UTILIZATION CURRENT_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------------------- --------------- ------------------- ------------------ -----------
cmtcallbk 2 0 620 UNLIMITED
dml_locks 28 19 2480 UNLIMITED
enqueue_locks 68 41 6644 6644
enqueue_resources 62 55 2700 UNLIMITED
max_rollback_segments 22 22 620 65535
parallel_max_servers 12 8 32 32767
processes 99 69 360 360
sessions 123 87 564 564
transactions 4 3 620 UNLIMITED
I’ve limited the history query to rows with non-zero utilization, which is why it’s showing so few rows, but It’s an interesting oddity that the dba_hist_resource_limit view (and the underlying table) does actually hold fewer statistics than the initial dynamic performance view. In a 12.1.0.2 (RAC) instance I found that 3 of the dynamic statistics had not been captured in the history, in 19.3 this had gone up to 5 missing statistics, namely:
Not captured in 12.1.0.2 (RAC) history
max_shared_servers
sort_segment_locks
temporary_table_locks
Further items not captured in 19.3.0.0
ges_procs
ges_rsv_msgs
It’s possible, of course, that the two extra stats from 19.3 would be captured in a RAC system.
Finally you might want to run a query through history if you needed to see if there was a pattern to some unexpected change in resource utilisation, so a query running through time might be useful, e.g.:
select *
from (
select
ss.end_interval_time,
res.resource_name, res.max_utilization, res.current_utilization
from
dba_hist_resource_limit res,
dba_hist_snapshot ss
where
ss.end_interval_time between to_date('18-Mar-2021 10:00','dd-mon-yyyy hh24:mi')
and to_date('18-Mar-2021 22:30','dd-mon-yyyy hh24:mi')
and res.snap_id = ss.snap_id
and res.resource_name in ('sessions','processes','transactions')
) piv
pivot (
avg(max_utilization) as max,
avg(current_utilization) as cur
for resource_name in (
'sessions' as sess,
'processes' as proc,
'transactions' as trns
)
)
order by
end_interval_time
/
END_INTERVAL_TIME SESS_MAX SESS_CUR PROC_MAX PROC_CUR TRNS_MAX TRNS_CUR
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
18-MAR-21 10.00.46.698 123 84 99 66 4 3
18-MAR-21 11.00.59.610 123 84 99 67 4 2
18-MAR-21 12.00.13.015 123 82 99 66 4 2
18-MAR-21 13.00.27.443 123 82 99 66 4 1
18-MAR-21 14.00.40.316 123 84 99 66 4 2
18-MAR-21 15.00.51.705 123 80 99 64 4 1
18-MAR-21 16.00.57.293 123 84 99 66 4 1
18-MAR-21 17.00.03.197 123 80 99 65 4 2
18-MAR-21 18.00.09.448 123 81 99 65 4 2
18-MAR-21 19.00.16.419 123 82 99 66 4 2
18-MAR-21 20.00.22.669 123 81 99 65 4 2
18-MAR-21 21.00.31.215 123 83 99 66 4 1
18-MAR-21 22.00.43.615 123 86 99 68 4 2
In my particular case there’s absolutely nothing interesting to see, but the sort of thing you might spot is a steady growth in the maximum number of sessions over a couple of hours one day, then a subsequent repeated decrease and increase (to that max) in the current number of sessions from then on. It’s always a little difficult when you have a statistic that is “maximum since startup” so you would have to be a little careful in interpreting the results of a query like this.
Comments and related questions are welcome.