Tom,

I recently was reading a BUG report 895995
In the report it was recommended, that Whatever highest value is 
reached (pool heap session (MTS)), set the LARGE_POOL_SIZE to 3x's that size

On my system
SQL>select sum(value) from v$sesstat wher staistic# = 15 -- "session uga memory"
29524368 = ~29 M
SQL>select * from v$sgastat;
....
large pool session heap 26382240 = ~ 25M

Setting the large pool to 3 x 25 = 75 M. , is this a good guide line or that recommendation
is specific to that environment . Why sum(session uga memory) show higher value
than "large pool session heap"

Thanks

 

Sir,

I am calculating total memory used by all logged in user..
I have 1GB ram..

I'm doing this by,

select decode(NAME,'session pga memory','session pga memory','f') ,sum(VALUE) from
    v$session se, v$sesstat ss, v$statname sn
    WHERE ss.sid=se.sid
        AND sn.statistic# = ss.statistic#
   group by decode(NAME,'session pga memory','session pga memory','f')
SQL> /

DECODE(NAME,'SESSI SUM(VALUE)
------------------ ----------
f                  1312030183
session pga memory   41989456

So,is this correct ??

Thanks.
atul
 

Hi Tom,

I would like to know if the result of the above query to calculate average value per process in bytes?

Thanks,

Dear Tom:

Suppose there are 50 sessions in the Oracle 9i (9201). Now how can we calculate the total RAM which is bieng used by the single Oracle 9i instance, which contains the 50 sessions. Please explain.

Thanks

add up v$sgastat and the pga values from v$sesstat:

ops$tkyte@ORA10G> l
  1  select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4* and lower(a.name) like '%' || lower('&S')||'%'
ops$tkyte@ORA10G> /
old   4: and lower(a.name) like '%' || lower('&S')||'%'
new   4: and lower(a.name) like '%' || lower('session pga memory')||'%'
 
NAME                                VALUE
------------------------------ ----------
session pga memory                1022540


 

Dear Tom,

    Would like to check with you on the following calculation.

why the total shared_pool in init.ora is different from total sum(bytes) from v$sesstat where pool = 'shared pool'

    show parameter shared
    shared_pool_size                     string  500000000  
<>
  1  select sum(bytes) from v$sgastat
  2* where pool = 'shared pool'
SQL> /
SUM(BYTES)
----------
 525391776      

and Q2. why the total sum(bytes) from v$sesstat <> shared_pool_size - shared pool free memory.

     Thanks in advance.

Rgds
SHGoh 

Hi Tom,

If I run the following query :

SELECT name,value/1024/1024
FROM v$session se, v$sesstat ss, v$statname sn
WHERE ss.sid=se.sid
AND sn.statistic# = ss.statistic#
AND sn.name = 'session pga memory'
and se.sid=&vsid;

Do you know which unix command on Aix will show me the amount of memory assign to a specific spid (where the spid is associated with the vsid given as an input parameter in the query )?
I need to demostrate that the amount of memory that the query is showing for a particular session id match the amount of memory that unix will show for that same session.

Thanks for any help you can give on this matter.

Thanks for answering me Tom.

You said "...I don't know if aix can or cannot report this level of detail and even if it can, it'll never be exactly the same -- we are simply reporting the malloc()'s we've done)"

So does this mean that an oracle process will actually use more memory than the amount that is shown in v$sesstat for the event 'session pga memory'?, If it is so, how can I do in order to calculate the exact amount of memory than an oracle process will be using at any moment?

Thanks for your help on this.