如何在自动SGA管理模式下调节参数设置

« 恩墨科技第二届Oracle性能优化与诊断案例培训 | Blog首页 | 《DBA手记 - 诊断案例与性能调整》一书定稿 »

我们知道，在Oracle10g自动SGA管理模式下，SGA的相关参数不受手动控制。
可是有些时候我们需要调整一下参数，如db_keep_cache_size，可能会遇到内存不足的错误：

SQL> alter system set db_keep_cache_size=3584M scope=both sid='*';
alter system set db_keep_cache_size=3584M scope=both sid='*'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

在自动模式下，内存设置由双下划线参数控制，如__db_cache_size:

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
3   WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4 /
Enter value for par: db_cache_size
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%db_cache_size%'

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
__db_cache_size                482344960            Actual size of DEFAULT buffer pool for standard block size
                                                    buffers

db_cache_size                  0                    Size of DEFAULT buffer pool for standard block size buffers

注意当前的SGA_TARGET和SGA_MAX_SIZE的设置：

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 680M
sga_target                           big integer 680M

我们可以先调节sga_target参数：

SQL> alter system set sga_target=400M;

System altered.

SQL> select * from v$sgainfo;

NAME                                BYTES RES
------------------------------ ---------- ---
Fixed SGA Size                    1263848 No
Redo Buffers                      7122944 No
Buffer Cache Size               205520896 Yes
Shared Pool Size                197132288 Yes
Large Pool Size                   4194304 Yes
Java Pool Size                    4194304 Yes
Streams Pool Size                       0 Yes
Granule Size                      4194304 No
Maximum SGA Size                713031680 No
Startup overhead in Shared Pool 41943040 No
Free SGA Memory Available       293601280

11 rows selected.

如果内存空闲，可以调节，那么可以看到参数的变化：

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
3   WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4 /
Enter value for par: db_cache_size
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%db_cache_size%'

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
__db_cache_size                188743680            Actual size of DEFAULT buffer pool for standard block size
                                                    buffers

db_cache_size                  0                    Size of DEFAULT buffer pool for standard block size buffers

再调高sga_target：

SQL> alter system set sga_target=600M;

System altered.

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
3   WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4 /
Enter value for par: db_cache
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%db_cache%'

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
__db_cache_size                398458880            Actual size of DEFAULT buffer pool for standard block size
                                                    buffers

db_cache_size                  0                    Size of DEFAULT buffer pool for standard block size buffers
_db_cache_pre_warm             TRUE                 Buffer Cache Pre-Warm Enabled : hidden parameter
_db_cache_crx_check            FALSE                check for costly crx examination functions
_db_cache_process_cr_pin_max                        maximum number of cr pins a process may have
db_cache_advice                ON                   Buffer cache sizing advisory
_db_cache_advice_sample_factor 4                    cache advisory sampling factor
_db_cache_advice_batch_size    128                  cache advisory simulation batch size

8 rows selected.

观察SGA组件的变化：

SQL> select * from v$sgainfo;

NAME                                BYTES RES
------------------------------ ---------- ---
Fixed SGA Size                    1263848 No
Redo Buffers                      7122944 No
Buffer Cache Size               415236096 Yes
Shared Pool Size                197132288 Yes
Large Pool Size                   4194304 Yes
Java Pool Size                    4194304 Yes
Streams Pool Size                       0 Yes
Granule Size                      4194304 No
Maximum SGA Size                713031680 No
Startup overhead in Shared Pool 41943040 No
Free SGA Memory Available        83886080

11 rows selected.

现在增加db_keep_cache_size，数据库会自动调整，去减少db_cache_size的内存分配：

SQL> show parameter keep

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer     7
db_keep_cache_size                   big integer 16M
SQL> alter system set db_keep_cache_size=200M;

System altered.

SQL> alter system set db_keep_cache_size=300M;

System altered.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 680M
sga_target                           big integer 600M
SQL> SET linesize 120
SQL> COL name for a30
SQL> COL value for a20
SQL> COL describ for a60
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2    FROM SYS.x$ksppi x, SYS.x$ksppcv y
3   WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4 /
Enter value for par: db_cache_size
old   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new   3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%cache_size%'

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
__db_cache_size                100663296            Actual size of DEFAULT buffer pool for standard block size
                                                    buffers

db_cache_size                  0                    Size of DEFAULT buffer pool for standard block size buffers