Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to check Total memory used by Oracle and which user are using more memo

608897Jul 30 2009 — edited Jul 30 2009
Hi,

I am using oracle version 10.2.0.4 on Windows 2003 server SP2.

1. How can i check the "Total memory" used by Oracle

2. Which users are using more CPU resources.

Thanks and Regards
Amit
This post has been answered by Chandan Tanwani on Jul 30 2009
Jump to Answer

Comments

Chandan Tanwani
Answer
Hi,

Oracle EM is the best to identify the usage of CPU and memory used by each session.

Moreover use following query to calculate the memory used by the each session.

SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+);


Regards,
Chandan
Marked as Answer by 608897 · Sep 27 2020
1 person found this helpful
Pierre Forstmann
Memory which is allocated by Oracle instance is SGA and PGA.

To get SGA size, you can SQL*Plus statement SHOW SGA or SQL statement: 
select sum(value) from v$sga;
To get PGA size, you can use: 
select name, value from v$pgastat where name like 'total PGA a%';
Edited by: P. Forstmann on Jul 30, 2009 2:18 PM
3 people found this helpful
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 27 2009
Added on Jul 30 2009
#general-database-discussions
2 comments
142,250 views