在一資料庫版本為（標準版）Oracle Database 10g Release 10.2.0.4.0 - 64bit Production 的伺服器上調整 sga_target時，遇到命令執行了非常久都沒有執行完成的異常情況，覺得非常詫異、不解，因為一般調整sga_targt命令非常快速，檢查了告... ...
在一資料庫版本為（標準版）Oracle Database 10g Release 10.2.0.4.0 - 64bit Production 的伺服器上調整 sga_target時，遇到命令執行了非常久都沒有執行完成的異常情況，覺得非常詫異、不解，因為一般調整sga_targt命令非常快速，檢查了告警日誌，並沒有任何異常錯誤，等了好幾分鐘都沒有執行完成，於是執行了CTRL+C命令結束了SQL命令，具體過程如下：
$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Sat Sep 3 22:35:59 2016
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Release 10.2.0.4.0 - 64bit ProductionSQL> show parameter sga
NAME TYPE VALUE------------------------------------ ----------- ------------------------------lock_sga boolean FALSEpre_page_sga boolean FALSE
sga_max_size big integer 8Gsga_target big integer 6GSQL> SQL> alter system set sga_target=8g scope=both;
^Calter system set sga_target=8g scope=both
*
ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01013: user requested cancel of current operation
SQL> SQL>
後面檢查時，在$ORACLE_BASE/admin/$ORACLE_SID/bdump目錄下發現scm2_mmon_16798.trc跟蹤文件，在裡面看到大量“SGA POLICY: Cache below reserve request pending 1”這裡錯誤信息
/u01/app/oracle/admin/SCM2/bdump/scm2_mmon_16798.trcOracle Database 10g Release 10.2.0.4.0 - 64bit ProductionORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: xxxxxxxxx
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine: x86_64
Instance name: SCM2
Redo thread mounted by this instance: 1Oracle process number: 11
Unix process pid: 16798, image: xxxxxx (MMON)
*** 2016-09-03 22:36:49.845
*** SERVICE NAME:(SYS$BACKGROUND) 2016-09-03 22:36:49.836
*** SESSION ID:(931.1) 2016-09-03 22:36:49.836SGA POLICY: Cache below reserve getting from component1SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
*** 2016-09-03 22:37:19.880
SGA POLICY: Cache below reserve getting from component1SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
*** 2016-09-03 22:37:49.892
SGA POLICY: Cache below reserve getting from component1SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
*** 2016-09-03 22:38:19.909
當時查了一下資料，覺得有可能與db_cache_size被手工設定有關（這個參數被同事手工設置過），當時檢查了一下V$SGA_RESIZE_OPS，發現並沒有SGA組件重定義大小的操作失敗的記錄。由於這個不是急於調整，手頭還有正事處理，於是當時就擱置下來。
今天晚上去檢查、處理時，居然無法重現這個錯誤了， 調整sga_target_size一下子就成功了，沒法重現這個錯誤了，於是我又特意檢查了一下V$SGA_RESIZE_OPS,發現期間出現了shared pool 收縮， DEFAULT buffer cache的增長， 還有一個DEFAULT buffer cache的增長從2016-09-03 22:45:21持續到了2016-09-04 21:13:26，而且狀態為INACTIVE。
後面分析了這個時間段的ASH報告，發現這個時間點出現了“SGA: allocation forcing component growth”等待事件，
The SGA: allocation forcing component growth indicates that you can Automatic Memory Management (AMM) turned-on
This is an AMM message indicating that the size of sga_target or memory_target is too small, causing excessive AMM resize operations.
The solution is to turn off AMM and allocate your own db_cache_size and shared_pool_size and un-setting sga_target and memory_target.
分析至此，能確定一些東西：當調整SGA_TARGET大小時，操作系統的記憶體也會將記憶體裡面用作cache或buffers的部分數據踢出記憶體， 另外，由於che_size和shared_pool_size等SGA相關組件大小劇烈調整，導致了SGA: allocation forcing component growth等待事件，導致SQL語句耗費了非常長的時間。由於當時SQL執行時間長，我取消了調整sga_target的SQL語句
