Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.