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

Display Oracle SGA

After you have a comfort level for the memory behavior on the database server, you will need to turn your attention to Oracle.  The first step is to find the size of the current SGA that controls the database.  To get such information, you can use the sgasize.sqlscript.  Note that this script can be used on all Oracle versions.  However, some of the columns may be NULL or zero because certain memory regions are not available in all versions.

See Code depot for complete script

select
       db_size_in_mb - db_caches db_buffers_in_mb,
       db_caches db_caches_mb,
       fixed_size_in_mb,
       lb_size_in_mb,
       sp_size_in_mb,
       lp_size_in_mb,
       jp_size_in_mb
from
(select
       round (max(a.bytes) / 1024 / 1024, 2)  db_size_in_mb
. . .
  from
       sys.v$sgastat a
 where
       sys.v$sgastat b
 where
        b.name = 'fixed_sga'),
. . .
from
        sys.v$parameter d
 where
        d.name = 'shared_pool_size'),
(select
        round (sum (e.value) / 1024 / 1024, 2) lp_size_in_mb
  from
        sys.v$parameter e
 where
        e.name = 'large_pool_size' ),
 . . .
(select
        round (sum (f.value) / 1024 / 1024, 2) jp_size_in_mb
  from
        sys.v$parameter f
 where
        f.name = 'java_pool_size ');

 

This script delivers more detailed information than the standard show sgacommand in the server manager or SQL*Plus because it breaks down the standard buffer cache, showing the total amount of memory given to the special 9i and above data caches and displaying information for the large and java pools. 

Exactly what each of these areas is and how Oracle uses them is the topic of the next section. 


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.

 

 

 

 
 
 

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.