Home » RDBMS Server » Server Administration » V$SGA and ASMM (merged) (oracle10g)
V$SGA and ASMM (merged) [message #448116] Fri, 19 March 2010 09:59 Go to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
I am using oracle10g. Here is my DB version.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


Here is my sga size..

SQL> select * from v$sga
  2  ;

NAME                      VALUE
-------------------- ----------
Fixed Size              2090112
Variable Size        3556772736
Database Buffers     6912212992
Redo Buffers           14684160

SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
10485760000

SQL> 


SGA = Fixed size + Variable size + DB buffers + Redo buffers

Here is my sga_target value.

SQL> select value from v$parameter
  2  where name='sga_target';

VALUE
-----------------------------------------
8388608000


My assumption is, sga_target value should be same as sum(value) in v$sga. But is not same. Any clarification is helpful.

Re: v$sga is not same as sga_target value [message #448117 is a reply to message #448116] Fri, 19 March 2010 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
when all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams193.htm#I1010256
Re: v$sga is not same as sga_target value [message #448142 is a reply to message #448117] Fri, 19 March 2010 14:34 Go to previous messageGo to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
Thanks. It helps.
sga is not changing after ASMM is enabled [message #448143 is a reply to message #448116] Fri, 19 March 2010 14:42 Go to previous messageGo to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
I am using oracle10g...

The database is not enabled for ASMM.

SQL> show sga

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes

SQL> select value from v$parameter
  2  where name='sga_target';

VALUE
------------------------------
0


Now i changed this mode to ASMM.

SQL> alter system set sga_target=300M
  2  scope=both;

System altered.

SQL> show sga

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes


But still SGA shows 600M.

Now i changed the initial values...

SQL> alter system set shared_pool_size = 0 scope=memory;

System altered.

SQL> alter system set large_pool_size  = 0 scope=memory;

System altered.

SQL> alter system set java_pool_size   = 0 scope=memory;

System altered.

SQL> alter system set db_cache_size    = 0 scope=memory;

System altered.

SQL> show sga

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes
SQL>



Still it is showing 600M. When would it change the memory size?

The statistics_level is TYPICAL. Any clarification is helpful.


Re: sga is not changing after ASMM is enabled [message #448150 is a reply to message #448143] Fri, 19 March 2010 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
V$SGA_RESIZE_OPS

Regards
Michel
Re: sga is not changing after ASMM is enabled [message #449153 is a reply to message #448150] Sat, 27 March 2010 08:45 Go to previous messageGo to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
Michel, thanks for the input and appreicate your help here.

Still my question is, why show sga is showing the same old values.
does it take some time to change the dynamic values?

Thanks
Re: sga is not changing after ASMM is enabled [message #449169 is a reply to message #449153] Sat, 27 March 2010 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you query the view I mentioned? What have you?

Regards
Michel
Re: sga is not changing after ASMM is enabled [message #449205 is a reply to message #449169] Sat, 27 March 2010 18:09 Go to previous messageGo to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
V$SGA_RESIZE_OPS gives the correct info.
Re: sga is not changing after ASMM is enabled [message #449214 is a reply to message #449205] Sun, 28 March 2010 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have your answer?
Post the content of the view.

Regards
Michel
Re: sga is not changing after ASMM is enabled [message #449252 is a reply to message #449214] Sun, 28 March 2010 22:27 Go to previous message
shrinika
Messages: 252
Registered: April 2008
Senior Member
I set the sga_target=1GB.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show sga

Total System Global Area 1048576000 bytes
Fixed Size                  1252976 bytes
Variable Size             570425744 bytes
Database Buffers          469762048 bytes
Redo Buffers                7135232 bytes
SQL>


Here is the view content. But sorry... i could not align
well if i display all the fields in the view..

SQL> select component,oper_type,initial_size,target_size,final_size from v$sga_resize_ops;

COMPONENT                      OPER_TYPE     INITIAL_SIZE TARGET_SIZE FINAL_SIZE
------------------------------ ------------- ------------ ----------- ----------
shared pool                    STATIC                   0   541065216  541065216
large pool                     STATIC                   0     4194304    4194304
DEFAULT buffer cache           INITIALIZING     473956352   473956352  473956352
java pool                      STATIC                   0    20971520   20971520
streams pool                   STATIC                   0           0          0
DEFAULT buffer cache           STATIC                   0   473956352  473956352
KEEP buffer cache              STATIC                   0           0          0
RECYCLE buffer cache           STATIC                   0           0          0
DEFAULT 2K buffer cache        STATIC                   0           0          0
DEFAULT 4K buffer cache        STATIC                   0           0          0
DEFAULT 8K buffer cache        STATIC                   0           0          0
DEFAULT 16K buffer cache       STATIC                   0           0          0
DEFAULT 32K buffer cache       STATIC                   0           0          0
ASM Buffer Cache               STATIC                   0           0          0
DEFAULT buffer cache           SHRINK           473956352   469762048  469762048
shared pool                    GROW             541065216   545259520  545259520

16 rows selected.

SQL> 


Previous Topic: Database Configuration Assistant doesn't work Oracle 8.0.5
Next Topic: How does SGA and PGA split memory when we use MEMORY_TARGET
Goto Forum:
  


Current Time: Thu Sep 29 22:46:19 CDT 2016

Total time taken to generate the page: 0.05387 seconds