 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|
|||
 |
|||
|
|
|||
 |
|||
|
|||
 |
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.
|
 |
email BC:
Copyright © 1996 - 2010 by Burleson Enterprises, Inc. All
rights reserved. Oracle® is the registered trademark of Oracle Corporation.
|
|