Ive run the report against our clients database (9.0.1.1), and it is returning with the following output.

Number of maximum concurrent users on this database is 45Recommended SHARED_POOL_SIZE for initial tuning is between 82.5 MBytes and 110.3 MBytes.Presently SHARED_POOL_SIZE is 48.0 MBytes.

How does the product determine this limit of 45 users?

The following parameters Ive set mean the limit should be higher.;
LICENSE_MAX_SESSIONS is set to 0
We ARE using MTS.
We have 150 PROCESSES and 170 SESSIONS

How does the shared pool size affect the max number of concurrent users?
Our cient is concerned as they are expecting more than 45 concurrent users in the near future, and so I would like to understand how this is calculated, so I know what to increase to allow more user onto the system

Any help would REALLY be appreciated!

Cheers

John

DB Tuning analyzes Oracle performance collected statistics where it determines maximum concurrent users number, which is not a maximum "setting" or "allowed number" (such as LICENSE_MAX_SESSIONS), but indeed a real maximum number of concurrent sessions that connect to your database. This number could be lower that the number you expect because of the session caching in MTS and Oracle. You can use the DB Monitor Expert to monitor database sessions if you want to get the full picture (see Monitor/Connections menu).

Now, every session requires certain amount of memory allocated in the Shared Pool. DB Tuning does some math to determine the appropriate pool size. If the pool size is insufficient to fit al sessions, Oracle must perform some memory sharing and swapping that can potentially cause severe performance hit.

On one hand more memory given to the database buffers (to shared pool and other buffers) leads to better performance, on other hand more memory given to the database leaves less memory to the operation system and other applications and running on the same box.

: Ive run the report against our clients database (9.0.1.1), and it is
: returning with the following output.

: Number of maximum concurrent users on this database is 45Recommended
: SHARED_POOL_SIZE for initial tuning is between 82.5 MBytes and 110.3
: MBytes.Presently SHARED_POOL_SIZE is 48.0 MBytes.

: How does the product determine this limit of 45 users?

: The following parameters Ive set mean the limit should be higher.;
: LICENSE_MAX_SESSIONS is set to 0
: We ARE using MTS.
: We have 150 PROCESSES and 170 SESSIONS

: How does the shared pool size affect the max number of concurrent users?
: Our cient is concerned as they are expecting more than 45 concurrent users in
: the near future, and so I would like to understand how this is calculated,
: so I know what to increase to allow more user onto the system

: Any help would REALLY be appreciated!

: Cheers

: John

Thanks for the swift response.

How can you tell out how much memory each session will get allocated in the Shared pool. If I am to size it for more users (say 100), I would really like to know which parameters I need to look at?

I dont what any OS paging going on, thats for sure !

Cheers,

J.

: DB Tuning analyzes Oracle performance collected statistics where it
: determines maximum concurrent users number, which is not a maximum
: "setting" or "allowed number" (such as
: LICENSE_MAX_SESSIONS), but indeed a real maximum number of concurrent
: sessions that connect to your database. This number could be lower that
: the number you expect because of the session caching in MTS and Oracle.
: You can use the DB Monitor Expert to monitor database sessions if you want
: to get the full picture (see Monitor/Connections menu).

: Now, every session requires certain amount of memory allocated in the Shared
: Pool. DB Tuning does some math to determine the appropriate pool size. If
: the pool size is insufficient to fit al sessions, Oracle must perform some
: memory sharing and swapping that can potentially cause severe performance
: hit.

: On one hand more memory given to the database buffers (to shared pool and
: other buffers) leads to better performance, on other hand more memory
: given to the database leaves less memory to the operation system and other
: applications and running on the same box.

Calculating the requirement for session memory is easy. The statistic 'session uga memory' shows the amount of session memory currently in use and statistic 'session uga memory max' shows the maximum amount of space which has been used, a kind of high water mark if you like. There is an entry in V$SESSTAT for every connected process. For a pessimistic estimate, use the following query to calculate the total amount of session memory in use. Run this at a time when you know concurrent use is at or near a peak.

select count(*) session_count,

substr(b.name,1,30) name,

avg(a.value) average_size
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic# in (15,16)
group by b.name;

SESSION_COUNT NAME AVERAGE_SIZE
------------- ------------------------------ ------------

535 session uga memory 386017

535 session uga memory max 645146

A good initial estimate might be somewhere between the average amount of session memory used 'session uga memory' and the average maximum amount used 'session uga memory max'. The amount of UGA memory required by different Oracle versions will vary widely for the same application.

In addition to this, there is an overhead of 250 bytes per open cursor per session. This will vary for each session, so get a feel for the average by executing this query:

select count(*) session_count,

avg(count(*)) avg_open_cursors,

max(count(*)) max_open_cursors
from V$open_cursor a
group by a.sid;

SESSION_COUNT AVG_OPEN_CURSORS MAX_OPEN_CURSORS
------------- ---------------- ----------------

527 72 249

Again, something between the average and the maximum should provide a good starting point. Finally, add a further 10% to account for any short-term growth. From our example system then, a good initial value for large_pool_size would be:

large_pool_size = UGA + Cursor overhead + 10%

Hopefully, this answers your question.

: Thanks for the swift response.

: How can you tell out how much memory each session will get allocated in the
: Shared pool. If I am to size it for more users (say 100), I would really
: like to know which parameters I need to look at?

: I dont what any OS paging going on, thats for sure !

: Cheers,

: J.