Oracle Scratchpad

March 11, 2022

Session count

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 12:38 pm GMT Mar 11,2022

This note is an extended answer to a question the Kyle Hailey raised on the oracle-l list server a couple of days ago. Rather than repeat the question immediately, I’ll start with a little scripts (to be run by a suitably privileged account – which eventually will have to be SYS) and then telll you the question.

rem
rem     Script:         xksuse.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2022
rem     Purpose:        
rem
rem     Last tested 
rem             21.3.0.0
rem             19.11.0.0
rem
rem     Notes:
rem     Has to be run by sys in the cdb$root
rem
rem     See also:
rem     https://www.freelists.org/post/oracle-l/sysmetric-Session-Count-Count-from-vsession
rem

select value from v$sysmetric where metric_name = 'Session Count';

select current_utilization from v$resource_limit where resource_name = 'sessions';

select count(*) from v$session;

Here’s a set of results taken from a test instance of 19.11.0.0

     VALUE
----------
	78

CURRENT_UTILIZATION
-------------------
		 78

  COUNT(*)
----------
	54

The question on oracle-l was: “why doesn’t v$sysmetric agree with the count of v$session?”

This raises an internesting question about licencing. I don’t really pay much attention to licensing since I don’t run a production Oracle database any more, but I have a vague memory that for some versions there were things like “named user” licenses and “concurrent sessions” licenses – and there are a couple of parameters you can set to avoid the risk of breaching the license agreement:

Parameter                       Description   
-------------------------------------------------------------------------------------------------
license_max_sessions            maximum number of non-system user sessions allowed
license_max_users               maximum number of named users that can be created in the database

Do these license agreements still exist? Has anyone signed up for them?

I haven’t set either of these parameters in my database so they’ve defaulted to show zero, but by running up several extra sessions and checking the v$sysmetic entry “Session Limit %” I could infer that the default value for license_max_sessions was 1,000 (for my instance on my machine, possibly affected by other parameter settings), and that the limit percentage was based on the v$sysmetric value not a count from v$session. (And if it had been based on the v$session count that would have been almost as bad since the description of the parameter is “non-system user sessions” – and of the 54 sessions reported above only 2 of them were of type “USER”.)

The Geek Bit

Before Kyle mentioned the license issue I was only interested in finding out why there was a difference in the counts. The starting point for this was to look at the x$ structure underlying v$session – and the critical one is x$ksuse although for some time the view includes a join to two other x$ structures: x$kslwt (v$session_wait) and x$ksled (v$event_name).

If you want to see the definition of v$session (or, rather, gv$session) you will run into a problem if you query v$fixed_view_definition because the definition of v$session is longer than the varchar2(4000) limit.

A workaround to this problem is to extract the full definition from the oracle executable which, for Unix systems you can do with a command like:

strings -a oracle | grep -inT "x\$ksuse" | fold >temp.txt

You’ll have to search the (fairly small) text file to find the right definition, of course as there are several dynamic performance views that include x$ksuse or x$ things with names that start the same way. When you”ve identified the correct definition you’ll note that there are two predicates applied to the structure to generate v$session (and these have not changed since at least 8.1.7.4 – which is the oldest listing of the views I have stored):

where
        bitand(s.ksspaflg,1)!=0 
and     bitand(s.ksuseflg,1)!=0

Based on these predicates I wrote a simple script to report sessions (x$ entries) that matched each of the predicates separately:

select 'U' flag, count(*) from x$ksuse where bitand(ksuseflg,1)!=0
union all
select 'S' flag, count(*) from x$ksuse where bitand(ksspaflg,1)!=0
/

F   COUNT(*)
- ----------
U         61
S         78

The 78 S types matches (maybe coincidentally) the v$resource_limit and v$sysmetric figures, but there’s an interesting excess built into the U types when compared to the count of v$session. So the next step is to drill into the detail a bit.

To drill down I wrote a query to list the program column (ksusepnm) with a count of how many rows there were for that program name in the union all. For many program names we could expect to see the answer 2, one row of type U and one of type S, but there are likely to be many end user sessions showing showing some format of connection mechanism such as ((TNS V1-V3). Here’s the script with a predicate showing only the “interesting” rows (i.e. the ones where the result is not 2):

set null n/a

column ksusepnm format a40

select ksusepnm, count(*) from (
select 'U' flag, ksusepnm from x$ksuse where bitand(ksuseflg,1)!=0
union all
select 'S' flag, ksusepnm from x$ksuse where bitand(ksspaflg,1)!=0
)
group by ksusepnm
having count(*) != 2
order by count(*)
/

KSUSEPNM                                   COUNT(*)
---------------------------------------- ----------
oracle@linux183.localdomain (P001)                1
oracle@linux183.localdomain (P002)                1
oracle@linux183.localdomain (J00B)                1
oracle@linux183.localdomain (P000)                1
oracle@linux183.localdomain (MZ00)                1
oracle@linux183.localdomain (MZ01)                1
oracle@linux183.localdomain (P003)                1
oracle@linux183.localdomain (SCMN)                4
sqlplus@linux183.localdomain (TNS V1-V3)          4
n/a                                              24

10 rows selected.

We can ignore the sqlplus rows, they correspond to the two sessions (reported twice each) that are visible in v$session. Similarly we can ignore the SCMN rows, of which there are two in my v$session at present, but we have 24 rows with no program, and 7 rows that appear only in one of the two classes.

If we take the 7 rows where the count is only 1 we note that they are all named for “slave” processes: Jnnn are job queue slaves, Pnnn are parallel execution slaves, and MZnn are manageability monitor (MMON) slaves which, the operating system tells me, don’t currently exist.

The interesting thing is the set of 24 rows that have no associated program; and to look at those I’m just going list the whole dataset rather than counting it and (since the volume is small in my case) examine the results by eye.

select 'U' flag, ksusepnm from x$ksuse where bitand(ksuseflg,1)!=0
union all
select 'S' flag, ksusepnm from x$ksuse where bitand(ksspaflg,1)!=0
order by 1,2
/

I won’t reproduce the results but the key detail was that all the rows where the program name was null were from the S class (and that’s not too surprising, really).

The next (and final, so far) check was to add a few columns to the simple query and change the sort order to see if anything stood out:

break on flag 
compute count of ksspatyp on flag

select 
        'U' flag, indx, ksuseser, ksspatyp, ksusepnm, bitand(ksspaflg,1) spaflg, bitand(ksuseflg,1) useflag, con_id, ksusepro 
from    x$ksuse 
where   bitand(ksuseflg,1)!=0
union all
select 
        'S' flag, indx, ksuseser, ksspatyp, ksusepnm, bitand(ksspaflg,1) spaflg, bitand(ksuseflg,1) useflag, con_id, ksusepro 
from    x$ksuse 
where   bitand(ksspaflg,1)!=0
order by 
        flag, ksusepro, ksusepnm
/

The break/compute commands allow me to check that the totals for the S class and U class still matched with the original 78 and 61 – dynamic structures can change very quickly, and some of the oddities were about background processes. The interesting rows in the U class were as follows:

F       INDX   KSUSESER   KSSPATYP KSUSEPNM                                     SPAFLG    USEFLAG     CON_ID KSUSEPRO
- ---------- ---------- ---------- ---------------------------------------- ---------- ---------- ---------- ----------------
U        533          0          0 oracle@linux183.localdomain (J00B)                0          1          1 00
         546          0          0 oracle@linux183.localdomain (MZ00)                0          1          0 00
         539          0          0 oracle@linux183.localdomain (MZ01)                0          1          0 00
         537          0          0 oracle@linux183.localdomain (P000)                0          1          0 00
         525          0          0 oracle@linux183.localdomain (P001)                0          1          0 00
         544          0          0 oracle@linux183.localdomain (P002)                0          1          0 00
          49          0          0 oracle@linux183.localdomain (P003)                0          1          0 00

Basically this looks like a set of processes which used to be “user” processes, which have terminated and shutdown, but their entries in x$ksuse have not been completely “cleaned” out. The numbers are nice because we have 61 rows in this class, 7 rows show “no serial#, no process” and v$session is reporting 54 sessions which is the result of 61 minus 7.

So what about the S class rows. I’ve cunningly sorted by ksusepro which, if you hadn’t guessed, is the paddr for the process supporting the session. So do we have any cases where multiple sessions seem to be supported by a single process – and this is where I viewed the output and noticed I could show the interesting detail with a simple call to grep plus a little cosmetic editing:

grep -nT -B+1 "                                " xksuse.lst >temp.txt

                 INDX   KSUSESER   KSSPATYP KSUSEPNM                                     SPAFLG    USEFLAG     CON_ID KSUSEPRO
                 ----   --------   -------- -------------------------------------------- ------    -------     ------ ----------------
 270    -           3      62786          1 oracle@linux183.localdomain (GEN0)                1          1          0 000000008E567480
 271    :          26       4581          1                                                   1          0          0 000000008E567480
 274    -         504       2420          1 oracle@linux183.localdomain (GEN1)                1          1          0 000000008E56B4D0
 275    :         519      53839          1                                                   1          0          0 000000008E56B4D0
 278    -           6      44715          1 oracle@linux183.localdomain (OFSD)                1          1          0 000000008E56F520
 279    :          27      60151          1                                                   1          0          0 000000008E56F520
 281    -           4      65197          1 oracle@linux183.localdomain (DBRM)                1          1          0 000000008E572000
 282    :          25      41180          1                                                   1          0          0 000000008E572000
 294    -          13      25844          1 oracle@linux183.localdomain (W000)                1          1          0 000000008E582140
 295    :          14      42325          1                                                   1          0          0 000000008E582140
 296    -         513      33860          1 oracle@linux183.localdomain (LREG)                1          1          0 000000008E5836B0
 297    :         520      56123          1                                                   1          0          0 000000008E5836B0
 298    -          28       5509          1 oracle@linux183.localdomain (W005)                1          1          0 000000008E584C20
 299    :          29      49967          1                                                   1          0          0 000000008E584C20
 301    -         515      19909          1 oracle@linux183.localdomain (MMON)                1          1          0 000000008E588C70
 302    :         516       3981          1                                                   1          0          0 000000008E588C70
 303    -          17       5100          1 oracle@linux183.localdomain (MMNL)                1          1          0 000000008E58A1E0
 304    :          32      46728          1                                                   1          0          0 000000008E58A1E0
 306    -          37      30525          1 oracle@linux183.localdomain (M000)                1          1          0 000000008E58F7A0
 307    :          31      18290          1                                                   1          0          0 000000008E58F7A0
 308    -         523       2817          1 oracle@linux183.localdomain (M002)                1          1          0 000000008E590D10
 309    :         524      58038          1                                                   1          0          0 000000008E590D10
 310    -          19      30164          1 oracle@linux183.localdomain (M001)                1          1          0 000000008E592280
 311    :          35      25427          1                                                   1          0          0 000000008E592280
 315    -         518      20043          1 oracle@linux183.localdomain (AQPC)                1          1          0 000000008E598DB0
 316    :         530      24619          1                                                   1          0          0 000000008E598DB0
 317    -          15      27947          1 oracle@linux183.localdomain (M004)                1          1          0 000000008E59A320
 318    :          45      37304          1                                                   1          0          0 000000008E59A320
 319    -         521      58123          1 oracle@linux183.localdomain (CJQ0)                1          1          0 000000008E59E370
 320    :         522      32179          1                                                   1          0          0 000000008E59E370
 321    -          23      17837          1 oracle@linux183.localdomain (W003)                1          1          0 000000008E59F8E0
 322    :          30      24152          1                                                   1          0          0 000000008E59F8E0
 326    -         529      54638          1 oracle@linux183.localdomain (M003)                1          1          0 000000008E5A0E50
 327    :         526       9248          1                                                   1          0          0 000000008E5A0E50
 328    -          16      23626          1 oracle@linux183.localdomain (W001)                1          1          0 000000008E5A23C0
 329    :          20      52434          1                                                   1          0          0 000000008E5A23C0
 332    -         531      31198          1 oracle@linux183.localdomain (QM02)                1          1          0 000000008E5A6410
 333    :         532      53916          1                                                   1          0          0 000000008E5A6410
 334    -          38      28119          1 oracle@linux183.localdomain (W006)                1          1          0 000000008E5A7980
 335    :          39      55963          1                                                   1          0          0 000000008E5A7980
 336    -          42      46149          1 oracle@linux183.localdomain (W007)                1          1          0 000000008E5AA460
 337    :          43      45287          1                                                   1          0          0 000000008E5AA460
 341    -          47      36878          1 oracle@linux183.localdomain (W002)                1          1          0 000000008E5BD080
 342    :          48      26536          1                                                   1          0          0 000000008E5BD080
 343    -         538      50713          1 oracle@linux183.localdomain (Q002)                1          1          0 000000008E5BE5F0
 344    :         517      40400          1                                                   1          0          0 000000008E5BE5F0
 345    -          34      27163          1 oracle@linux183.localdomain (W004)                1          1          0 000000008E5C5120
 346    :          44      19000          1                                                   1          0          0 000000008E5C5120

Every single one of the S class with a blank program name is “sharing” a process with one of the background processes. In many of the pairs the row with a named program is a slave process (Mnnn, Wnnn etc.) but that’s far from a uniform pattern. I could imagine an argument that says Oracle wants to “pre-allocate” a spare session linked to an existing process in some special cases so that a program can switch to the alternate session (memory structures) if the first session gets corrupted in some way – but I can’t really see that as relevant to any slave processes, while I can think of a couple more programs where this strategy would be more important.

Adding a little weight to the “shadow” idea (or maybe just highlighting a bug) it’s interesting to note that a number of the pairs show consecutive values for the indx (SID) – as if Oracle has created two sessions at the same time.

All I can say at the moment, though, is that we have a consistent pattern for all the excess “invisible” sessions, and the strange entries make up the difference between the v$session count and the v$sysmetric report.

Summing up:

v$session shows a count of 54 but digging into x$ksuse for “user sessions” I can see a further 7 sessions; but the pattern for these sessions is simply “memory structure doesn’t need to be cleaned out after process terminated” so they have nothing to do with any counting anomaly.

v$resource_limit and v$sysmetric report a value that is larger than the v$session count by 24; and we can find 24 rows in x$ksuse which (a) “mirror” 24 background sessions and (b) are linked to processes (v$process.addr) but don’t have a program name (v$session.program) and aren’t flagged as user processes. So it looks as if we can (partially) explain why the two views are over-reporting.

There are some indications that if you are licensing by sessions (if that’s still possible) then the number used for checking the limit is not consistent with the description of the relevant parameter. A large number of background sessions is included in the v$sysmetric figure that seems to be the reference record.

Footnote:

I have a little oddity to pursue in 21.3.0.0, where my v$sysmetric.value was one less than the v$resource_limit current_utilization. The same “shadow” process strategy was visible though, and v$session count plus the “shadow” count agreed with v$resource_limit.

1 Comment »

  1. […] Session Count issue (Mar 2022): v$session, v$resource_limit and v$sysmetric don’t agree – digging in to x$ […]

    Pingback by Infrastructure Catalogue | Oracle Scratchpad — March 11, 2022 @ 12:43 pm GMT Mar 11,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: