| V$SGA and ASMM (merged) [message #448116] |
Fri, 19 March 2010 09:59  |
shrinika
Messages: 306
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.
|
|
|
|
|
|
|
|
| sga is not changing after ASMM is enabled [message #448143 is a reply to message #448116] |
Fri, 19 March 2010 14:42  |
shrinika
Messages: 306
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 #449252 is a reply to message #449214] |
Sun, 28 March 2010 22:27 |
shrinika
Messages: 306
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
