Oracle10g New Feature -- 13. Automatic Shared Memory Management

Automatic Shared Memory Management 是個不錯的功能。

Oracle9i以前修改記憶體引數，都需要重新啟動instance，9i時允許再線修改db_cache_size,shared_pool,log_buffer等引數，10g裡能自動再線調整data buffer, shared pool, java pool 和large pool。不知道下一個版本里還有什麼更進一步的改進？ 總之Oracle記憶體管理是越來越方便了

[@more@]

  Automatic Shared Memory Management

Automatic Shared Memory Management helps you to allocate memory where it’s needed most automatically. Users need not allocate memory between buffer cache/shared pool/large pool/java pool manually when required by applications.

1.     set automatic shared memory management but keep the the shared_pool/java_pool/large_pool as it was

If these automatically tuned memory pools(buffer cache/shared pool/large pool/java pool) are kept as they were, then their values are used as minimum levels by Automatic Shared Memory Management

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

SUM(VALUE)/1024/1024

--------------------

                 164

SQL> alter system set sga_target=200M scope=both;

System altered.

 

Show parameters:

__shared_pool_size                   big integer 80M

shared_pool_size                     big integer 80M

__large_pool_size                    big integer 8M

large_pool_size                      big integer 8M

__java_pool_size                     big integer 40M

java_pool_size                       big integer 40M

__db_cache_size                      big integer 68M

db_cache_size                        big integer 60M

SQL> select component,current_size/1024/1024 curr_size,min_size/1024/1024 min_size,max_size/1024/1024 max_size  from v$sga_dynamic_components;

COMPONENT                                                         CURR_SIZE   MIN_SIZE   MAX_SIZE

---------------------------------------------------------------- ---------- ---------- ----------

shared pool                                                              80         80          0

large pool                                                                8          8          0

java pool                                                                40         40          0

streams pool                                                              0          0          0

DEFAULT buffer cache                                                     68         60          0

KEEP buffer cache                                                         0          0          0

RECYCLE buffer cache                                                      0          0          0

DEFAULT 2K buffer cache                                                   0          0          0

DEFAULT 4K buffer cache                                                   0          0          0

DEFAULT 8K buffer cache                                                   0          0          0

DEFAULT 16K buffer cache                                                  0          0          0

DEFAULT 32K buffer cache                                                  0          0          0

OSM Buffer Cache                                                          0          0          0

13 rows selected.

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE

------------

          68

 

init.ora:

orcl.__db_cache_size=71303168 (68M)

orcl.__java_pool_size=41943040 (40M)

orcl.__large_pool_size=8388608 (8M)

orcl.__shared_pool_size=83886080 (80M)

*.db_cache_size=62914560 (60M)

*.java_pool_size=41943040

*.large_pool_size=8388608

*.shared_pool_size=83886080

2.     set automatic shared memory management and set shared_pool/java_pool/large_pool to 0

If these automatically tuned memory pools(buffer cache/shared pool/large pool/java pool) are set to zero, actually there are still minimun values(usually much lower than previous setting) used by Automatic Shared Memory Management

 

SQL> alter system set shared_pool_size=0;

System altered.

SQL> alter system set large_pool_size=0;

System altered.

SQL> alter system set java_pool_size=0;

System altered.

SQL> alter system set db_cache_size=0;

alter system set db_cache_size=0

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero

SQL> alter system set db_cache_size=8k;

System altered.

 

Show Parameters:

__shared_pool_size                   big integer 80M

shared_pool_size                     big integer 8M

__large_pool_size                    big integer 8M

large_pool_size                      big integer 4M

__java_pool_size                     big integer 48M

java_pool_size                       big integer 4M

__db_cache_size                      big integer 60M

db_cache_size                        big integer 4M

SQL> select component,current_size/1024/1024 curr_size,min_size/1024/1024 min_size,max_size/1024/1024 max_size  from v$s

ga_dynamic_components;

COMPONENT                                                         CURR_SIZE   MIN_SIZE   MAX_SIZE

---------------------------------------------------------------- ---------- ---------- ----------

shared pool                                                              80         80          0

large pool                                                                8          8          0

java pool                                                                40         40          0

streams pool                                                              0          0          0

DEFAULT buffer cache                                                     68         60          0

KEEP buffer cache                                                         0          0          0

RECYCLE buffer cache                                                      0          0          0

DEFAULT 2K buffer cache                                                   0          0          0

DEFAULT 4K buffer cache                                                   0          0          0

DEFAULT 8K buffer cache                                                   0          0          0

DEFAULT 16K buffer cache                                                  0          0          0

DEFAULT 32K buffer cache                                                  0          0          0

OSM Buffer Cache                                                          0          0          0

Init.ora:

orcl.__db_cache_size=71303168 (68M)

orcl.__java_pool_size=41943040 (40M)

orcl.__large_pool_size=8388608 (8M)

orcl.__shared_pool_size=83886080 (80M)

*.db_cache_size=4194304 (4M)

*.java_pool_size=4194304

*.large_pool_size=4194304

*.shared_pool_size=8388608 (8M)

 

3.     After turning off the automatic shared memory management

After tuning off the automatic shared memory management, these pools( buffer cache/shared pool/large pool/java pool) will get back to the settings before turning on.

SQL> alter system set sga_target=0 scope=both;

System altered.

 

Show Parameters:

__db_cache_size                      big integer 60M

db_cache_size                        big integer 60M

__shared_pool_size                   big integer 80M

shared_pool_size                     big integer 80M

__large_pool_size                    big integer 8M

large_pool_size                      big integer 8M

__java_pool_size                     big integer 48M

java_pool_size                       big integer 48M

 

SQL> select component,current_size/1024/1024 curr_size,min_size/1024/1024 min_size,max_size/1024/1024 max_size  from v$sga_dynamic_components;

COMPONENT                                                         CURR_SIZE   MIN_SIZE   MAX_SIZE

---------------------------------------------------------------- ---------- ---------- ----------

shared pool                                                              80         80          0

large pool                                                                8          8          0

java pool                                                                48         48          0

streams pool                                                              0          0          0

DEFAULT buffer cache                                                     60         60          0

KEEP buffer cache                                                         0          0          0

RECYCLE buffer cache                                                      0          0          0

DEFAULT 2K buffer cache                                                   0          0          0

DEFAULT 4K buffer cache                                                   0          0          0

DEFAULT 8K buffer cache                                                   0          0          0

DEFAULT 16K buffer cache                                                  0          0          0

DEFAULT 32K buffer cache                                                  0          0          0

OSM Buffer Cache                                                          0          0          0

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE

------------

          60

SQL> select pool,sum(bytes)/1024/1024 from v$sgastat group by pool;

POOL         SUM(BYTES)/1024/1024

------------ --------------------

java pool                      48

large pool                      8

shared pool                    80

 

init.ora:

orcl.__db_cache_size=62914560 (60M)

orcl.__java_pool_size=50331648 (48M)

orcl.__large_pool_size=8388608 (8M)

orcl.__shared_pool_size=83886080 (80M)

*.db_cache_size=62914560

*.java_pool_size=50331648

*.large_pool_size=8388608

*.shared_pool_size=83886080

4.     Which Pools are Not Affected?

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

·         Log buffer

·         Other buffer caches, such as KEEP, RECYCLE, and other block sizes

·         Streams pool

·         Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.