SESSIONS Derived Value Formula – Doc Bug Explains

8022012

February 8, 2012

Default parameter values are great, as long as it is possible to predict the default values, and the defaults are appropriate for the environment in which the defaults are present. It is sometimes a challenge to remember all of the rules, and exceptions to those rules, that determine the defaults. I had a vague recollection of how the value of the SESSIONS parameter value is derived from the value of the PROCESSES parameter value. An Oracle Database 11.2.0.2 question recently arrived in my email box from an ERP mailing list. The original poster (OP) showed the following output in the email:

At first glance, I thought that the OP made a mistake with the manual specification of the SESSIONS parameter value when taking into consideration the current value of the PROCESSES parameter value.

A Google search found a couple of helpful discussion threads that seemed to provide a formula that showed how the default value for the SESSIONS parameter is derived. I decided to also perform a Google search of the Oracle documentation (trying to avoid circular references). The same formula found in the helpful discussion threads is also included in the documentation, so that lends a bit of credibility to the information. Let’s take a look at the Oracle Database 11.2 documentation for the SESSIONS parameter:

Default Value: Derived: (1.1 * PROCESSES) + 5

“… You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.”

A second page from the documentation library included additional information about the PROCESSES parameter:

“Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead…”

If you plan on running 50 user processes, a good estimate would be to set the PROCESSES initialization parameter to 70.”

So, let’s take another look at the PROCESSES value specified by the OP, keeping in mind the information from the documentation. The OP has a database instance with a PROCESSES value of 400, and with the insight provided by the first documentation quote, that would suggest that the SESSIONS parameter would have been autoset to a value of (1.1 * 400) + 5 = 445. That formula result coupled with the first paragraph of the second quote might lead someone to believe that the OP manually set the SESSIONS value to 624 – there could be a good reason for doing so. The second paragraph in the second quote from the documentation suggests that OP’s PROCESSES value would allow about (400 – (70 – 50)) = 380 user processes (each session will require 1 dedicated process, so that value would permit about 380 user sessions).

In an earlier blog article we saw that the auto-tuned value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter is dependent, in part, on the value of the SESSIONS parameter and the size of the SGA. In another blog article we found that the maximum number of blocks read by serial direct path read is the greatest power of 2 that is equal to or less than the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter. Various other items are also affected by the SESSIONS parameter value, so setting the value to too high of a value has few rewards.

So, did the OP make a mistake when setting the SESSIONS parameter value? I took a look at a test 11.2.0.2 database instance where I had set the PROCESSES parameter to a value of 150 and left the SESSIONS parameter unset. The SESSIONS parameter value in the 11.2.0.2 database instance showed a value of 248 when I opened the database and executed:

SHOW PARAMETER SESSIONS

Something a bit stange is happening. Oracle Database 10.2 seemed to respect the 11.2 documentation’s formula for the auto-calculation of the SESSIONS parameter, but Oracle Database 11.2.0.2 seems to use a different formula. It was late at night when I posted my reply, so I guessed that the formula was about (1.52 * PROCESSES) + 20 (it was too late at night for thinking in terms of basic algebra, so I just picked a set of numbers that worked with my test PROCESSES and SESSIONS parameter values).

Since responding to the email, I took a sampling of a couple of 11.2.0.2 database instances, and summarized those findings below, including the actual derived SESSIONS value and the calculated value using the formula from the documentation:

PROCESSES

SESSIONS(Actual Derived)

SESSIONS(Documentation Derived)

330

520

368

150

248

170

400

624

445

60

112

71

80

144

93

The actual numbers don’t quite match what the documentation suggests.

—

Time for a little basic algebra. The formula from the documentation could be represented as:

(x * PROCESSES) + y = SESSIONS

If the structure of the above formula is still correct, just with unknown values for x and y, we will need at least two sets of equations to determine the value of the x and y variables. To find the x and y variable values, we will use the known PROCESSES and actual derived SESSIONS values from first two rows of the above table:

(x * 330) + y = 520
(x * 150) + y = 248

Solving for y in the first equation:

y = 520 - (x * 330)
y = 520 - 330x

Substituting the above value of y into the second equation to solve for x:

8 responses

I considered submitting a comment in the documentation, but I have not done so. I am curious to know:
* Does the new formula apply to 11.2.0.2 on all operating system platforms?
* Does the new formula apply to 11.2.0.1 and 11.2.0.3, and what about 11.1.0.6 and 11.1.0.7?
* 11.2.0.1 was released September 1, 2009 – has anyone else reported the apparent error in the documentation?

I can confirm for 11.2.0.3. I changed processes to 300 and sessions were automatically set to 472 after bouncing the instance. If I get around to testing against 11.1.0.6 I’ll bump those results here too.
Thanks for the excellent post.

And I have confirmed that the behaviour of 11.1.0.6 is different again. Setting processes to 300 and bouncing the instance yielded sessions of 335 and transactions of 368. This conforms to the published Oracle documentation formulas (sessions=processes * 1.1 + 5 and transactions=sessions * 1.1). so it would appear that the formulas changed in 11.2 and that the documentation was never updated.

On Solaris x86_64, Oracle SE 11.2.0.2, with processes=500, sessions was 792. So that works out to processes * 1.5 + 42.
On another Solaris x86_64, Oracle SE 11.2.0.2.8, with processes=2000, sessions was 3024. So that works out to processes * 1.5 + 24.

This morning I tried digging through My Oracle Support (MOS) to see if there had been any discussion regarding the derived value of SESSIONS (I would like to mention that the search functionality is very limited in MOS – many unrelated articles are returned). This time I managed to find a bug report titled “Bug 9357816 : [DOCBUG] DEFAULT VALUE OF SESSIONS IS CHANGED ABOVE 11.2” that was last updated late in February 2010. A user reported several pairs of PROCESSES and derived SESSIONS pairs, and in each case the derived value of SESSIONS could be determined based on the following formula:

While the documentation is closer to being correct now, the new formula in the documentation does not agree with the formula that anyone commenting on this blog article has provided. Of the 5 sample PROCESSES and SESSIONS pairs found in the table of this article, the documentation’s new formula only works for 1 of the PROCESSES and SESSIONS pairs.

I located a couple of 10+ year old articles in MOS that suggested that enabling auditing may cause the formula for deriving the SESSIONS parameter value, but I do not know if that has any impact.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: