Oracle 学习笔记（三） 内存管理配置

主要用于数据库的调优工作：

1      查看SGA：

SQL> select * from v$sga
  2  ;

NAME                      VALUE
-------------------- ----------
Fixed Size              1336960
Variable Size         281020800
Database Buffers      134217728
Redo Buffers            6094848

———————————————————————————————————————————————
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
———————————————————————————————————————————————

2    如何设置SGA自动管理？

（1）首先，找出SGA_TARGET的值：
SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
 422670336

（2）其次，得到有效内存的大小：

SQL> select CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY;

CURRENT_SIZE
------------
   171966464

（3）所以, SGA_TARGET的大小为：  403M － 169M ＝ 234M

（4）把数据库的系统级别设置成TYPICAL 或者 ALL， TYPICAL表示收集主要统计信息， ALL表示收集全部系统信息。
SQL> alter system set STATISTICS_LEVEL=TYPICAL;

System altered.

（5）显示SGA的最大值：
SQL> show parameter SGA_MAX_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ -----------
sga_max_size                         big integer 404M

（6）将共享池，大池，流池，java池，数据高速缓冲区的值设置为0。
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 STREAMS_POOL_SIZE=0;

System altered.

SQL> alter system set DB_CACHE_SIZE=0;

System altered.

（7）最后设置SGA_TARGET的大小：
SQL> alter system set SGA_TARGET=200M;

System altered.

———————————————————————————————————————————————
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
———————————————————————————————————————————————

3   如何禁用SGA自动管理？？

只需要将SGA_TARGET的值设置为0，就可以禁用自动SGA管理了。

4  如何改变SGA_MAX_SIZE的值？

需要关闭数据库，改参数文件（如spfile当中的内容，然后重新生成参数文件，再打开数据库就可以了）。

———————————————————————————————————————————————
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
———————————————————————————————————————————————

5    如何查看SGA最小单位的大小（Granule的大小）？？

SQL> select * from v$sgainfo where name = 'Granule Size';

NAME                                  BYTES RES
-------------------------------- ---------- ---
Granule Size                        4194304 No

———————————————————————————————————————————————
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
———————————————————————————————————————————————

6  计算高速缓冲区中，hit ratio（命中概率）。

公式：  hit ratio ＝ 1 － physical reads cache／（consistent gets from cache ＋　db　block　gets　from　cache）。

SQL> select name, value from v$sysstat where name in ('physical reads cache','db block gets from cache','consistent gets from cache');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets from cache                                              83212
consistent gets from cache                                           793653
physical reads cache                                                  15014

so, hit ratio = 98%

如果数据库高速缓冲区命中率太低，这个时候，ＤＢＡ就需要调整高速缓冲区的大小，不过，在此之前，最好做一下评估，　数据缓冲区太小，会造成命中率降低，但是缓冲区太大，也会造成内存的浪费：

SQL> select size_for_estimate, buffers_for_estimate, estd_physical_reads
  2  from v$DB_CACHE_ADVICE
  3  WHERE NAME = 'DEFAULT'
  4  AND block_size=(select value from v$parameter where name = 'db_block_size')
  5  AND advice_status = 'ON';

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READS
----------------- -------------------- -------------------
               12                 1488               47424
               24                 2976               29358
               36                 4464               25254
               48                 5952               22115
               60                 7440               18590
               72                 8928               16827
               84                10416               16455
               96                11904               15615
              108                13392               15422
              120                14880               14968
              132                16368               14968

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READS
----------------- -------------------- -------------------
              136                16864               14968
              144                17856               14968
              156                19344               14968
              168                20832               14968
              180                22320               14968
              192                23808               14968
              204                25296               14968
              216                26784               14968
              228                28272               14968
              240                29760               14968

21 rows selected.

从结果可以看出，　预计的磁盘读取值随着buffer的增大而减少，但是到了某一个特定的值的时候，就趋于稳定了，所以在这个例子当中，把ＤＢ_CACHE_SIZE 设置为１３Ｍ左右是最合适的了。

———————————————————————————————————————————————
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
———————————————————————————————————————————————

７　　查看数据字典的命中率：

SQL> select sum(gets - getmisses - fixed)/sum(gets) as ratio from v$rowcache;

     RATIO
----------
.919020305

可以看出shared　pool　当中对于数据字典的命中率是　９２％，　如果这个值太小，则需要调整shared　pool　的大小了。

———————————————————————————————————————————————
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
———————————————————————————————————————————————

８　　查看日志缓冲区的大小，以及决定是否需要修改大小（注意：日志缓冲区的大小，不能动态修改，需要修改参数文件才行）

首先，查看表项：

SQL> select name,value from v$sysstat where name = 'redo buffer allocation retries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries                                            0

看看值如果接近０，则不需要修改，如果值偏大，则说明有日志等待写入数据缓冲区，这时候需要调整大小。

如果需要改，步骤如下，首先由spfile　来创建pfile，　然后修改pfile当中的log_buffer的值，然后关闭数据库，重新生成spfile，　然后再打开数据库即可验证修改后的spfile内容了。