Re: Memory Upgrade for 10g

Yes, pull the Oracle version# Reference Manual and look up the following views; however you should realize that the application, the user load, and the tuned state of the SQL could be responsible for the performance difference and the difference in OS memory may not be a factor at all. Unless the two machines and databases run the same application under the same user load the comparison is invalid.

v$session
v$sga
v$sgastat
v$parameter

Here is a query to get you started. You want to look to see how big your shared pool is and how much of it your application is using. You will also want to examine your buffer pool. Then you will want to look at the OS memory and how of it is in use.

select
  to_number(p.value) "Total|Pool"
,s.bytes "Free|Bytes"
,round(( s.bytes / p.value ) * 100,1) "Free"
from

   v$parameter p
  ,v$sgastat s

where p.name = 'shared_pool_size'
and   s.name  = 'free memory'
and   s.pool    = 'shared pool'

Here is another query to find the total memory being used by user sessions
 select name, sum(value)
 from v$sesstat st, v$statname sn
 where st.statistic# = sn.statistic#
 and sn.name = 'session pga memory'
 group by sn.name

HTH -- Mark D Powell -- Received on Thu Sep 29 2005 - 08:48:11 CDT