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.
|