Thursday, April 24, 2008

Ora Tip: How much did you assign your memory for oracle sga and pga?

If you want to know how much does your oracle database taking up memory for SGA and PGA just login to your oracle database with the following command on the command prompt

if you can access to the server physically or remotely
sqlplus "/ as sysdba"

otherwise use this command

sqlplus "sys@ora_service as sysdba"

SQL>show SGA
you would see something like the following lines but difference on number.

Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 109055512 bytes
Database Buffers 96468992 bytes
Redo Buffers 2412544 bytes

this is how your system Global Area taking your memory space every time your database is running.

to see what is your SGA size just run the following command

SQL>show parameter pga

and you would get the folloing result

pga_aggregate_target big integer 75M

this is how much your pga consume your memory.