USING Auotomatic Shared Memory Management

USING Auotomatic Shared Memory Management

今天是2013-08-05，前段时间学习了amm，今天开始学习一下ASMM 。
asmm启动非常简单就是设置，sga_target参数即可，只要设置了asmm，那么sga中的如buffer cache、large pool ，java pool、streams pool 、shared pool，都会根据work进行动态调整，但是像log buffer，db keep 和db recycle buffer以及非标准块，都不能进行自动调整大小了，另外在sag中存在一种叫做granules的单元。
eg：
SQL> select name,bytes/1024/1024||'M' M from v$sgainfo;

NAME                             M
-------------------------------- -----------------------------------------
Fixed SGA Size                   2.1118316650390625M
Redo Buffers                     3.875M
Buffer Cache Size                124M
Shared Pool Size                 148M
Large Pool Size                  4M
Java Pool Size                   4M
Streams Pool Size                4M
Shared IO Pool Size              0M
Granule Size                     4M
Maximum SGA Size                 445.98828125M
Startup overhead in Shared Pool  64M

NAME                             M
-------------------------------- -----------------------------------------
Free SGA Memory Available        156M

12 rows selected.

SQL>
可以看出所有pool都是granule的整数倍，如果当设置一个参数不是granule的整数倍的时候，取相邻的一个值。例如，我设置shared pool为150M，那么实际为152M。（除了redo buffer之外）
另外asmm有一个sga_max_size，该值和amm的memory_max_target参数的性质类似。
注意：如果你不指定一个sga_max_size值，在数据库初始化的时候会选择一个所有components的sum值，或是一个默认值进行启动实例，演示如下：
首先我确定一下sga_target大小，然后关闭amm。
SQL> SELECT (SELECT SUM(VALUE)/1024/1024 FROM V$SGA)-(SELECT CURRENT_SIZE/1024/1024 FROM V$SGA_DYNAMIC_FREE_MEMORY) AS SGA_TARGET FROM DUAL;

SGA_TARGET
----------
289.988281

SQL>
SQL> SHOW PARAMETER MEMORY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 448M
memory_target                        big integer 448M
shared_memory_address                integer     0
SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
SQL> SELECT * FROM V$PGA_TARGET_ADVICE;

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED  ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
               20447232              .125 ON        477455360     229211            29921280                            94        6
               40894464               .25 ON        477455360     229211            29921280                            94        6
               81788928                .5 ON        477455360     215694                   0                           100        0
              122683392               .75 ON        477455360     215694                   0                           100        0
              163577856                 1 ON        477455360     215694                   0                           100        0
              196292608               1.2 ON        477455360     215694                   0                           100        0
              229008384               1.4 ON        477455360     215694                   0                           100        0
              261724160               1.6 ON        477455360     215694                   0                           100        0
              294439936               1.8 ON        477455360     215694                   0                           100        0
              327155712                 2 ON        477455360     215694                   0                           100        0
              490733568                 3 ON        477455360     215694                   0                           100        0

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED  ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
              654311424                 4 ON        477455360     215694                   0                           100        0
              981467136                 6 ON        477455360     215694                   0                           100        0
             1308622848                 8 ON        477455360     215694                   0                           100        0

14 rows selected.

SQL> SELECT 81788928/1024/1024 m FROM DUAL;

         M
----------
        78

SQL>
SQL>
SQL> alter system reset memory_max_target scope=spfile;

System altered.

SQL> alter system reset memory_target scope=spfile;

System altered.

SQL> alter system set sga_target=310M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=78M scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area  405020672 bytes
Fixed Size                  2213816 bytes
Variable Size             251660360 bytes
Database Buffers          146800640 bytes
Redo Buffers                4345856 bytes
Database mounted.
Database opened.
SQL>

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 388M
sga_target                           big integer 312M
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter pga_aggregate

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 78M
SQL>

好，开始做实验。首先，我不指定sga_max_size参数。
eg：
SQL> alter system reset sga_max_size;

System altered.

SQL>
If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization
SQL> alter system reset sga_max_size;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  325685248 bytes
Fixed Size                  2213056 bytes
Variable Size             167775040 bytes
Database Buffers          150994944 bytes
Redo Buffers                4702208 bytes
Database mounted.
Database opened.
SQL>
SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
325685248

SQL> 
If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE and chooses a correct value for this parameter.

Note:

The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.

一）启动asmm
启动asmm需要设置sga_target参数，然后statistics_leve 为typical 或是all，另外如下参数需要设置为0。还需要设置memory_target和memory_max_target参数。
large_pool_size,
java_pool_size
shared_pool_size
streams_pool_size
db_cache_size
然后手工设置如下需要参数
log_buffer
db_keep_cache_size
db_recycle_cache_size
db_nk_cache_size
二）监控asmm
我们可以使用v$sga_target_advice视图进行监视。
SQL> select * from v$sga_target_advice;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       156              .5           40              1.0256               10995
       234             .75           39                   1                9770
       312               1           39                   1                9770
       390            1.25           39                   1                9770
       468             1.5           39                   1                9770
       546            1.75           39                   1                9770
       624               2           39                   1                9770

7 rows selected.

SQL>
可以看到oracle给予的建议值。另外该视图在awr中对应如下：
我们先抓取awr
eg：
[oracle@oracle-one ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 6 00:09:51 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @/opt/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
2735005832 RHYS                1 RHYS

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 2735005832        1 RHYS         RHYS         oracle-one

Using 2735005832 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
RHYS         RHYS                 9 06 Aug 2013 00:07      1
                                 10 06 Aug 2013 00:08      1
                                 11 06 Aug 2013 00:10      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 9
Begin Snapshot Id specified: 9

Enter value for end_snap: 11

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_9_11.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_9_11.html
然后我们看一下这个地方：

就是v$sga_target_advice这个视图了。
另外在awr中的这些都是基于视图的。只不过看起来方便多了吧了。

 

擦。弄到现在觉得没啥意思。

###########################################↖(^ω^)↗…………………………&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&