select * from EMP where name = ‘TOM’;

select * from EMP where name = ‘JERRY’;

假如把’TOM’ 和 ‘JERRY’ 换做变量V，那就是使用了bind var，我们可以认为是同样的SQL从而能很好地共享。共享SQL本来就是shared_pool_size这部分内存存在的本意，oracle的目的也在于此，而我们不使用bind var就是违背了oracle的初衷，这样将给我们的系统带来严重的问题。当然，如果通过在操作系统监控，没有发现严重的cpu问题，我们如果发现该共享池命中率不高可以适当的增加shred_pool_size。但是通常我们不主张这部分内存超过800M（特殊情况下可以更大）。

事实上，可能的话我们甚至要想办法避免软分析，这在不同的程序语言中实现方式有差异。我们也可能通过设置session_cached_cursors 参数来获得帮助（这将增大PGA）。

Data buffer

现在我们来谈数据缓冲区，在确定了SGA的大小并分配完了前面部分的内存后，其余的，都分配给这部分内存。通常，在允许的情况下，我们都尝试使得这部分内存更大。这部分内存的作用主要是缓存 DB BLOCK，减少甚至避免从磁盘上获取数据，在8i中是由db_block_buffers*db_block_size来决定大小的（包含default、keep、recycle）。如果我们设置了buffer_pool_keep 和buffer_pool_recycle，这两部分内存的大小包含在前面设置中（db_block_buffers*db_block_size）。

buffer_pool_keep 是用来取代8i版本以前的缓存频繁小表于LUR 的MOST  USED端的。通过开辟一段独立的内存用于缓存频繁的小表，在创建表的时候可以指定存储参数，或者也可以动态修改表的存储参数（alter table t storage(buffer_pool  keep);）。

Buffer_pool_recycle 作为一块单独开辟出来的内存，主要用于很少执行的大表全表扫描的查询，使得这些大表扫描不会影响到default里面LRU而冲击整个数据库缓冲区的性能。虽然这样有可能降低大表的全表扫描的性能，但是保护了整体性能不间歇性的受到较大的冲击。同样，除了设置参数外还需要在创建表的过程中使用存储参数或者动态修改表的存储参数（alter table t storage(buffer_pool  recycle);）

3、9i下参数的变化

oracle的版本的更新，总是伴随着参数的变化，并且越来越趋向于使得参数的设置更简单，因为复杂的参数设置使得DBA们经常焦头烂额。关于内存这部分的变化，我们可以考察下面的参数。事实上在9i中数据库本身可以给出一组适合当前运行系统的SGA相关部分的参数调整值（参考V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），关于PGA也有相关视图V$PGA_TARGET_ADVICE等。

Data buffer

9i中保留了8i中的参数，如设置了新的参数，则忽略旧的参数。9i中用db_cache_size来取代db_block_buffers，用db_keep_cache_size取代buffer_pool_keep,用db_recycle_cache_size取代buffer_pool_recycle；这里要注意9i中设置的是实际的缓存大小而不再是块的数量。另外9i新增加了db_nk_cache_size，这是为了支持在同一个数据库中使用不同的块大小而设置的。对于不同的表空间，可以定义不同的数据块的大小，而缓冲区的定义则依靠该参数的支持。其中n可以为2、4、6、8、16等不同的值。在这里顺便提及的一个参数就是db_block_lru_latches，该参数在9i中已经成为了保留参数，不推荐手工设置。

PGA

在9i里面这部分也有了很大的变化。在独立模式下，9i已经不再主张使用原来的UGA相关的参数设置，而代之以新的参数。假如workarea_size_policy=AUTO（缺省），则所有的会话的UGA共用一大块内存，该内存在 pga_aggregate_target 设置以内分配。在我们根据前面介绍的方法评估了所有进程可能使用的最大PGA内存之后，我们可以通过在初始化参数中设置这个参数，从而不再关心其他 ”*_area_size” 参数。

SGA_MAX_SIZE

在9i中若设置了SGA_MAX_SIZE，则在总和小于等于这个值内，可以动态的调整数据缓冲区和共享池的大小

SQL> show parameters sga_max_size

NAME                                 TYPE    VALUE

------------------------------------ ------- -------------

sga_max_size                         unknown 193752940

SQL> alter system set db_cache_size = 30000000;

System altered.

SQL> alter system set shared_pool_size = 20480000;

System altered.

3、Lock_sga = true 的问题

由于几乎所有的操作系统都支持虚拟内存，所以即使我们使用的内存小于物理内存，也不能避免操作系统将SGA换到虚拟内存（SWAP）。所以我们可以尝试使得SGA锁定在物理内存中不被换到虚拟内存中，这样减少页面的换入和换出，从而提高性能。但在这里遗憾的是，windows是无法避免这种情况的。

下面我们来参考在不同的几个系统下怎么实现lock_sga ：

AIX 5L（AIX 4.3.3以上）

logon aix as root

cd /usr/samples/kernel

./vmtune (信息如下) v_pingshm已经是1

./vmtune -S 1

然后oracle用户修改initSID.ora 中 lock_sga = true

重新启动数据库

HP UNIX

Root身份登陆

Create the file "/etc/privgroup":  vi /etc/privgroup

Add line "dba MLOCK" to file

As root, run the command "/etc/setprivgrp -f /etc/privgroup":

$/etc/setprivgrp -f /etc/privgroup

oracle用户修改initSID.ora中lock_sga=true

重新启动数据库

SOLARIS (solaris2.6以上)

8i版本以上数据库默认使用隐藏参数 use_ism = true ，自动锁定SGA于内存中,不用设置lock_sga, 如果设置 lock_sga =true 使用非 root 用户启动数据库将返回错误。

WINDOWS

不能设置lock_sga=true,可以通过设置pre_page_sga=true,使得数据库启动的时候就把所有内存页装载，这样可能起到一定的作用。

4、关于内存参数的调整

关于参数调整，是oracle的复杂性的一个具体体现。通常来讲，我们更倾向于让客户做        statspack报告，然后告诉我们os监控的状况，在这些的信息的基础上，再向客户索取具体的详细信息以诊断问题的所在。

系统的调整，现在我们通常采用从等待事件入手的方法。因为一个系统感觉到慢，必然是在某个环节上出现等待，那么我们从等待最多的事件入手逐步诊断并解决问题。

对于内存的调整，相对来说简单一些，我们首先可以针对数据缓冲区的大小来看。首先观察命中率

数据缓冲区命中率

SQL> select value from  v$sysstat where name ='physical reads';

     VALUE

----------

     14764

SQL>  select value from  v$sysstat where name ='physical reads direct';

     VALUE

----------

        50

SQL> select value from  v$sysstat where name ='physical reads direct (lob)';

     VALUE

----------

         0

SQL> select value from  v$sysstat where name ='consistent gets';

     VALUE

----------

167763

SQL> select value from v$sysstat where name = 'db block gets';

     VALUE

----------

     14305

这里命中率的计算应该是

令 x = physical reads direct + physical reads direct (lob)

命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区。

共享池的命中率

SQL> select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;

 hit radio

----------

 99.809291

假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var）或者增加内存。

关于排序部分

SQL>  select name,value from v$sysstat where name like '%sort%';

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

sorts (memory)                                                        67935

sorts (disk)                                                              1

sorts (rows)                                                           7070

假如我们发现sorts (disk)/ (sorts (memory)+ sorts (disk))的比例过高，则通常意味着sort_area_size部分内存较小，可考虑调整相应的参数。

关于log_buffer

SQL>  select name,value from v$sysstat

  2   where name in('redo entries','redo buffer allocation retries');

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12330444/viewspace-310096/ ，如需转载，请注明出处，否则将追究法律责任。