Oracle的AMM和ASMM以及相關引數探究

PiscesCanon發表於2017-05-26
OracleASM

實驗出發點

1. 資料庫剛啟動時候的sga資訊(show sga可檢視)或者通過show parameter sga/pga/db_cache_size/memory_target等等和記憶體有關的引數，並不能真正反映相對應大小。
   引數存在的意義是什麼，要怎麼檢視資料庫啟動之後sga,pga等真正的大小。
2.如何啟用AMM和ASMM
3.存在靜態引數檔案中類似proc.__db_cache_size、proc.__pga_aggregate_target、*.__sga_target、proc.__shared_pool_size有什麼意義

環境說明：
1.作業系統版本。
  1. [oracle@oracle ~]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle ~]$ lsb_release -a
  4. LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID:    RedHatEnterpriseServer
  6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:    6.5
  8. Codename:    Santiago
2.資料庫版本。
  1. SYS@proc> select * from v$version where rownum=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

實驗過程：
一、實驗準備
1.通過靜態引數檔案將memory_max_target、memory_target、sga_max_size、sga_target、pga_aggregate_target、db_cache_size引數設定為0。
  1. [oracle@oracle dbs]$ cat initproc.ora
  2. *._in_memory_undo=TRUE
  3. *.audit_file_dest='/u01/app/oracle/admin/proc/adump'
  4. *.audit_trail='db'
  5. *.compatible='11.2.0.4.0'
  6. *.control_files='/u01/app/oracle/oradata/proc/control01.ctl','/u01/app/oracle/oradata/proc/control02.ctl'
  7. *.db_block_size=8192
  8. *.db_domain=''
  9. *.db_file_multiblock_read_count=128
  10. *.db_name='proc'
  11. *.processes=150
  12. *.remote_login_passwordfile='EXCLUSIVE'
  13. *.undo_tablespace='UNDOTBS1'
  14. memory_target=0
  15. memory_max_target=0
  16. sga_target=0
  17. sga_max_size=0
  18. pga_aggregate_target=0
  19. db_cache_size=0
2.使用新的引數檔案設定啟動資料庫。
  1. SYS@proc> shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SYS@proc> create spfile from pfile;

  7. File created.

  9. SYS@proc> startup;
  10. ORACLE instance started.

  12. Total System Global Area  175403008 bytes
    Fixed Size                  2251416 bytes
    Variable Size             163579240 bytes
    Database Buffers            4194304 bytes
    Redo Buffers                5378048 bytes
    Database mounted.
    Database opened.
3.使用show parameter檢視指定引數的狀態數值。
  1. SYS@proc> show parameter memory%target

  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. memory_max_target                    big integer 12M
  6. memory_target                        big integer 0
  7. SYS@proc> show parameter sga

  9. NAME                                 TYPE        VALUE
  10. ------------------------------------ ----------- ------------------------------
  11. ...省略部分內容...
  12. sga_max_size                         big integer 168M
  13. sga_target                           big integer 0
  14. SYS@proc> show parameter pga

  16. NAME                                 TYPE        VALUE
  17. ------------------------------------ ----------- ------------------------------
  18. pga_aggregate_target                 big integer 0
  19. SYS@proc> show parameter db_cache_size
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_cache_size                        big integer 4M
說明：由於memory_target被設定為0，所以AMM此刻是被禁用的
         由於memory_target為0的情況下，sga_target為0，所以ASMM此刻是被禁用的

二、檢視資料庫開啟後各元件的真實大小以及依賴引數
1.動態檢視sga元件大小。
  1. SYS@proc> select component,current_size from v$sga_dynamic_components;

  3. COMPONENT                                                        CURRENT_SIZE
  4. ---------------------------------------------------------------- ------------
  5. shared pool                                                         159383552
  6. large pool                                                                  0
  7. java pool                                                             4194304
  8. streams pool                                                                0
  9. DEFAULT buffer cache                                                  4194304
  10. ...省略部分內容...

  12. 14 rows selected.

  14. SYS@proc> show sga

  16. Total System Global Area 175403008 bytes
  17. Fixed Size                 2251416 bytes
  18. Variable Size            163579240 bytes
  19. Database Buffers           4194304 bytes
  20. Redo Buffers               5378048 bytes
2.檢視相對應得隱含引數數值大小。
  1. SYS@proc> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2.           from sys.x$ksppi x,sys.x$ksppcv y
  3.           where x.inst_id=userenv('Instance')
  4.           and y.inst_id=userenv('Instance')
  5.           and x.indx=y.indx
  6.           and (x.ksppinm like '%sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');

  8. NAME                                     VALUE                          DESCRIB
  9. ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
  10. sga_target                               0                              Target size of SGA
  11. __sga_target                             0                              Actual size of SGA         --該值在AMM或者ASMM下真實反映資料庫剛啟動時候分配的sga大小
  12. memory_target                            0                              Target size of Oracle SGA and PGA memory
  13. memory_max_target                        12582912                       Max size for Memory Target
  14. __db_cache_size                          4194304                        Actual size of DEFAULT buffer pool for standard block size buffers
  15. db_cache_size                            4194304                        Size of DEFAULT buffer pool for standard block size buffers
  16. pga_aggregate_target                     0                              Target size for the aggregate PGA memory consumed by the instance
  17. __pga_aggregate_target                   0                              Current target size for the aggregate PGA memory consumed

  19. 8 rows selected.
說明：注意隱含引數的描述部分。比如“__sga_target”是Actual size of SGA

三、設定資料庫為AMM模式，對比各個元件變化情況
1.準備工作
1.1設定memory_max_target和memory_target等於1G。遇到了疑似BUG的報錯。
  1. SYS@proc> alter system set memory_max_target=1g scope=spfile;

  3. System altered.

  5. SYS@proc> startup force;
  6. ORACLE instance started.

  8. Total System Global Area 175403008 bytes
  9. Fixed Size                 2251416 bytes
  10. Variable Size            163579240 bytes
  11. Database Buffers           4194304 bytes
  12. Redo Buffers               5378048 bytes
  13. Database mounted.
  14. Database opened.

  16. SYS@proc> alter system set memory_target=1g;
  17. alter system set memory_target=1g
  18.                        *
  19. ERROR at line 1:
  20. ORA-03113: end-of-file on communication channel
  21. Process ID: 4862
  22. Session ID: 16 Serial number: 3

  24. SYS@proc> select status from v$instance;
  25. ERROR:
  26. ORA-03114: not connected to ORACLE


  29. SYS@proc> startup;
  30. ORA-24324: service handle not initialized
  31. ORA-01041: internal error. hostdef extension doesn
1.2疑似BUG的報錯對應的警告日誌。
  1. Errors in file /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_mman_5032.trc (incident=218646):
  2. ORA-00600: internal error code, arguments: [kmgsb_resize_memory_target_2], [43], [42], [], [], [], [], [], [], [], [], []
  3. Incident details in: /u01/app/oracle/diag/rdbms/proc/proc/incident/incdir_218646/proc_mman_5032_i218646.trc
  4. Use ADRCI or Support Workbench to package the incident.
  5. See Note 411.1 at My Oracle Support for error and packaging details.
  6. Errors in file /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_mman_5032.trc:
  7. ORA-00600: internal error code, arguments: [kmgsb_resize_memory_target_2], [43], [42], [], [], [], [], [], [], [], [], []
  8. MMAN (ospid: 5032): terminating the instance due to error 822
  9. Sun Dec 11 08:00:40 2016
  10. System state dump requested by (instance=1, osid=5032 (MMAN)), summary=[abnormal instance termination].
  11. System State dumped to trace file /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_diag_5026_20161211080040.trc
  12. Dumping diagnostic data in directory=[cdmp_20161211080040], requested by (instance=1, osid=5032 (MMAN)), summary=[abnormal instance termination].
  13. Instance terminated by MMAN, pid = 5032
說明：在mos上查詢不到相關錯誤，百度有類似kmgsb_resize_sga_target_1的ora600，是一個BUG。猜測kmgsb_resize_memory_target_2也是一個BUG。
         這裡做記錄即可。
1.3強制性在spfile中修改，然後重啟即可達到目的。
  1. SYS@proc> startup;
  2. ORACLE instance started.

  4. Total System Global Area 175403008 bytes
  5. Fixed Size                 2251416 bytes
  6. Variable Size            163579240 bytes
  7. Database Buffers           4194304 bytes
  8. Redo Buffers               5378048 bytes
  9. Database mounted.
  10. Database opened.
  11. SYS@proc> alter system set memory_target=1g scope=spfile;

  13. System altered.

  15. SYS@proc> startup force;
  16. ORACLE instance started.

  18. Total System Global Area 638889984 bytes       --對比下邊查出來的__sga_target可知從這裡看大小並不真實。有機會做個10046（驗證show sga的不準確）
  19. Fixed Size 2255872 bytes
  20. Variable Size 184550400 bytes
  21. Database Buffers 448790528 bytes
  22. Redo Buffers 3293184 bytes
  23. Database mounted.
  24. Database opened.
  25. SYS@proc> show parameter memory%target

  27. NAME                                 TYPE        VALUE
  28. ------------------------------------ ----------- ------------------------------
  29. memory_max_target                    big integer 1G
  30. memory_target                        big integer 1G
  31. SYS@proc>
  32. SYS@proc> col name for a40
  33. SYS@proc> col value for a30
  34. SYS@proc> col DESCRIB for a90
  35. SYS@proc> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  36.           from sys.x$ksppi x,sys.x$ksppcv y
  37.           where x.inst_id=userenv('Instance')
  38.           and y.inst_id=userenv('Instance')
  39.           and x.indx=y.indx
  40.           and (x.ksppinm like '%sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');

  42. NAME                                     VALUE                          DESCRIB
  43. ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
  44. sga_target                               0                              Target size of SGA
  45. __sga_target                             641728512                      Actual size of SGA
  46. memory_target                            1073741824                     Target size of Oracle SGA and PGA memory
  47. memory_max_target                        1073741824                     Max size for Memory Target
  48. __db_cache_size                          448790528                      Actual size of DEFAULT buffer pool for standard block size buffers
  49. db_cache_size                            0                              Size of DEFAULT buffer pool for standard block size buffers
  50. pga_aggregate_target                     0                              Target size for the aggregate PGA memory consumed by the instance
  51. __pga_aggregate_target                   432013312                      Current target size for the aggregate PGA memory consumed

  53. 8 rows selected.
2.sga_target和pga_aggregate_target同時設定大小
  1. SYS@proc> alter system set sga_max_size=500m scope=spfile;

  3. System altered.

  5. SYS@proc> alter system set sga_target=500m;

  7. System altered.

  9. SYS@proc> alter system set pga_aggregate_target=525m;        --1024-500=524,由於設定了sga_target=500m,可知pga_aggregate_target設定不能超過524
  10. alter system set pga_aggregate_target=525m     --反過來假設先設定pga_aggregate_target=500,在設定sga_target超過524是可以的,不過startup報ORA-00838:"Specified value of MEMORY_TARGET is too small, needs to be at least %sM"
  11. *
  12. ERROR at line 1:
  13. ORA-02097: parameter cannot be modified because specified value is invalid
  14. ORA-00840: PGA_AGGREGATE_TARGET cannot be modified to the specified value


  17. SYS@proc> alter system set pga_aggregate_target=400m;

  19. System altered.

  21. SYS@proc> startup force;
  22. ...省略部分內容...
  23. Database opened.
  24. SYS@proc> show parameter pga

  26. NAME                                 TYPE        VALUE
  27. ------------------------------------ ----------- ------------------------------
  28. pga_aggregate_target                 big integer 400M
  29. SYS@proc> show parameter sga

  31. NAME                                 TYPE        VALUE
  32. ------------------------------------ ----------- ------------------------------
  33. ...省略部分內容...
  34. sga_max_size                         big integer 500M
  35. sga_target                           big integer 500M
  36. SYS@proc> col name for a40
  37. SYS@proc> col value for a30
  38. SYS@proc> col DESCRIB for a90
  39. SYS@proc> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  40.           from sys.x$ksppi x,sys.x$ksppcv y
  41.           where x.inst_id=userenv('Instance')
  42.           and y.inst_id=userenv('Instance')
  43.           and x.indx=y.indx
  44.           and (x.ksppinm like 'SYS@proc> SYS@proc> SYS@proc> 2 3 4 5 6 %sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');

  46. NAME                                     VALUE                          DESCRIB
  47. ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
  48. sga_target                               524288000                      Target size of SGA
  49. __sga_target                             524288000                      Actual size of SGA
  50. memory_target                            1073741824                     Target size of Oracle SGA and PGA memory
  51. memory_max_target                        1073741824                     Max size for Memory Target
  52. __db_cache_size                          331350016                      Actual size of DEFAULT buffer pool for standard block size buffers
  53. db_cache_size                            0                              Size of DEFAULT buffer pool for standard block size buffers
  54. pga_aggregate_target                     419430400                      Target size for the aggregate PGA memory consumed by the instance
  55. __pga_aggregate_target                   549453824                      Current target size for the aggregate PGA memory consumed

  57. 8 rows selected.

  59. SYS@proc> alter system set sga_target=400m;

  61. System altered.
  62. SYS@proc> alter system set pga_aggregate_target=400m;
  63. System altered.

  65. SYS@proc> startup force;
  66. ...省略部分內容...
  67. Database opened.
  68. SYS@proc> show parameter sga

  70. NAME                                 TYPE        VALUE
  71. ------------------------------------ ----------- ------------------------------
  72. sga_max_size                         big integer 500M
  73. sga_target                           big integer 400M
  74. SYS@proc> show parameter pga

  76. NAME                                 TYPE        VALUE
  77. ------------------------------------ ----------- ------------------------------
  78. pga_aggregate_target                 big integer 400M

  80. SYS@proc>select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  81.          from sys.x$ksppi x,sys.x$ksppcv y
  82.          where x.inst_id=userenv('Instance')
  83.          and y.inst_id=userenv('Instance')
  84.          and x.indx=y.indx
  85.          and (x.ksppinm like '%sga%target%' or x.ksppinm like '%memory%target%' or x.ksppinm like '%pga%target%' or x.ksppinm like '%db_cache_size%');

  87. NAME                                     VALUE                          DESCRIB
  88. ---------------------------------------- ------------------------------ ------------------------------------------------------------------------------------------
  89. sga_target                               419430400                      Target size of SGA
  90. __sga_target                             524288000                      Actual size of SGA
  91. memory_target                            1073741824                     Target size of Oracle SGA and PGA memory
  92. memory_max_target                        1073741824                     Max size for Memory Target
  93. __db_cache_size                          331350016                      Actual size of DEFAULT buffer pool for standard block size buffers
  94. db_cache_size                            0                              Size of DEFAULT buffer pool for standard block size buffers
  95. pga_aggregate_target                     419430400                      Target size for the aggregate PGA memory consumed by the instance
  96. __pga_aggregate_target                   549453824                      Current target size for the aggregate PGA memory consumed

  98. 8 rows selected.
結論是:同時設定了sga_target和pga_aggregate_target兩個值的話，
              若這是sga_target+pga_aggregate_target>memory_target是更改不成功的，若是強制性更改(即加上scope=spfile)，則startup會報錯。
              若sga_target+pga_aggregate_target<memory_target
                    若更改的sga_target<__sga_target,則實際sga的大小還是為原來更改之前__sga_target （__sga_target有點像高水位線）
                    若更改的sga_target>__sga_target,令__sga_target=sga_target,sga大小設定為__sga_target
              而__pga_aggregate_target的實際的值則為memory_target-__sga_target,也就是__sga_target+__sga_target=memory_target。
3.sga_target和pga_aggregate_target同時設定大小(直接結論，實驗過程略去，下同)
結論是:sga_target設定大小，pga_aggregate_target沒有設定大小
那麼pga_aggregate_target初始化值=memory_target-sga_target
4.sga_target沒有設定大小，pga_aggregate_target設定大小
結論是:sga_target初始化值=memory_target-pga_aggregate_target
5.sga_target和pga_aggregate_target都沒有設定大小
結論是:Oracle將對這兩個值沒有最小值和預設值。Oracle將根據資料庫執行狀況進行分配大小。但在資料庫啟動是會有一個固定比例來分配：
sga_target = memory_target*60%
pga_aggregate_target = memory_target*40%

四、其它
1.如何降低“高水位線”“__sga_target”呢，經測試只能修改靜態引數檔案裡邊proc.__sga_target的值，而無法通過alter system set "__sga_target"=500m scope=spfile;的方式修改（會同時將sga_target和sga_max_size也手動設定調小），雖然可以成功執行。
2.實驗過程可得出AMM下，無論sga_target是否為0，ASMM是生效的。
    有幾種情況：
        2.1 memory_target<>0也就是AMM模式下，包含ASMM
        2.2 memory_target=0也就是10G一樣的情況下
              sga_target=0也就是禁用ASMM下，SGA中的各元件大小都要明確設定，不能自動調整各元件大小。
              sga_target<>0(ASMM)，系統自動調整SGA各元件大小。
              PGA則依賴pga_aggregate_target的大小，因為10G模式下PGA不能自動調整。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2139954/，如需轉載，請註明出處，否則將追究法律責任。

相關文章