 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|
|||
 |
|||
|
|
|||
 |
|||
|
|||
 |
Donald K. Burleson Oracle Tips |
Find high Oracle RAM memory high usage
You can use the memhog.sql script to find the sessions that use the most memory in a database:
See Code depot for complete script
select sid,username,
round(total_user_mem/1024,2) mem_used_in_kb,
round(100 * total_user_mem/total_mem,2) mem_percent
from
(select . . .
from sys.v$statname c,
sys.v$sesstat a,
sys.v$session b,
sys.v$bgprocess p
where . . .
group by b.sid, nvl(b.username,p.name)),
(select sum(value) total_mem
from sys.v$statname c,
sys.v$sesstat a
where . . .
order by 3 desc;
Figure 5.10 – Sample output showing the top memory users in a database
Another metric shown in the memsnap.sql script is the parse to execute ratio. It shows the percentage of SQL executed that did not incur a hard parse. Seeing low values might indicate that users are executing SQL with many hard-coded literals instead of bind variables within the application. High values (90% +) generally indicate Oracle is saving precious CPU resources by avoiding heavy parse tasks.
While the above figures help you get a handle on session memory usage within Oracle’s program global areas (PGA) and user global areas (UGA), another area you will want to check into is sort activity.
The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.
It's only $19.95 and you can order it and get instant access to the Oracle scripts here:
http://www.rampant-books.com/book_2003_1_perf.htm
|
 |
email BC:
Copyright © 1996 - 2010 by Burleson Enterprises, Inc. All
rights reserved. Oracle® is the registered trademark of Oracle Corporation.
|
|