Hi Team,
I am interested to know if it is possible to find amount of SGA/PGA usage in terms of user sessions or sql's (sql_id's) ?
My ideas is to plan any expansions based on current state from above model, e.g. If I have 200 sessions generated from 20 App servers at peak, If we are adding another 20 app servers , I know that they will generate ~400 session. So If 200 sessions are consuming 100 GB SGA/20 GB PGA, then I can derive it for ~400 sessions.
Tried different methods to for PGA but SGA seems to be somewhat troublesome, any thoughts or ideas ?
I do have access to Performance/diagnostics ADDM views.
e.g.
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
Thanks,
Jayanath
For SGA I'd be inclined to look at the advisor views, eg V$SGA_TARGET_ADVICE
If the advice within them is that even a small increase will reap large benefits from either library cache or db cache , then this is an indication that they might be close to "capacity" given the current workload. That would suggest you'd need a generous increase as workload goes up.
However, if they suggest minimal benefit as you go larger, then this would suggest an increase in workload does not necessarily require an equivalent jump in size.
For example, on my (barely used) laptop
SQL> select * from V$SGA_TARGET_ADVICE;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR
---------- --------------- ------------ -------------------
2048 .5 217086 12.7271
2560 .625 40886 2.397
3072 .75 18937 1.1102
3584 .875 17437 1.0223
4096 1 17057 1
4608 1.125 16873 .9892
5120 1.25 16603 .9734
5632 1.375 16503 .9675
6144 1.5 16441 .9639
6656 1.625 16412 .9622
7168 1.75 16376 .9601
7680 1.875 16370 .9597
8192 2 16370 .9597
I'm basically being told there is little or benefit to bumping up allocations.
Also, you can use your own business knowledge. For example
- 20 more app servers accessing the *same* code and the *same* data ? Probably not much SGA boost needed
- 20 more app servers accessing the *new* code and the *same* data ? Probably need a boost to SGA to help shared pool
- 20 more app servers accessing the *new* code and *new* data ? Probably need a bigger boost to SGA to help shared pool *and* buffer cache