Oracle性能优化 之 库缓存与Pin

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。

http://blog.chinaunix.net/space.php?uid=26762723&do=blog&id=3247720 

一、使用视图进行库缓存大小测试：

     如果你的企业新开启了一项业务，并为此开发了一套新的应用程序，这需要一个新的数据库为后台。你负责规化此新的数据库。那么，你该如何确定共享池的大小呢？下面的测试可以有助于你确定共享池大小。注意，这样确定的共享池大小，只是估计的大小。到底多大的共享池才适合你的数据库，这需要数据库运行一段时间后，通过观察历史数据才能得知。这就好像你第一次炒菜，你不知道放多少盐，下面的方法只是告诉你大概放多少盐，而具体放多少数量的盐饭菜的才可口，这需要你实验个几次才知道。第一次你放一匙，如果淡了，第二次就放一匙半，等等。数据库的调节也是如此。这就是我们以前尽力讲述历史数据重要性的原因。没有历史数据，没有了可供参考的数据，你就不知道这一次放多少盐才合适。好，下面开始讲述如何测试共享池大小。

     首先将你的共享池设置的非常大。如果是在10g下，就将sga_target设置的非常大，重新启动你的数据，然后启动数据库上的所有应用。在应用运行一段时间时间后，通过如下这个语句，可以计算出大概的库缓存的大小：

     select mem1+mem2 from (select sum(sharable_mem) mem1 from v$db_object_cache) a,(select value*250 mem2 from v$sysstat where name= 'opened cursors current') b;

     这个语句包括两个子查询，第一个子查询是利用v$db_object_cache视图，求得当前库缓存中所有对象所占用的内存总量。另外，Oracle为每个游标还要额外分配250字节的内存用于存储一些管理性信息。第二个子查询就是计算当前打开的游标数量，并用它乘以250，所得结果就是Oracle为游标额外分配的内存总数了。将两个子查询所得结果相加，就是你的应用程序所需的库缓存的大小了。

     知道了库缓存的大概大小，我一般的方法是用它剩以2，用的得的结果作为整个共享池的大小。

     如果你的应用程序比较散，很难这样为了观察共享池的内存占用情况而全部都集中的运行一下，那么，还有一种更为简单、使用更广的评估共享池、Buffer cache等内存组件大小的方法。对于OLTP系统来说，如果主机上除数据库外不再运行其他软件，我们应该保留20%或30%左右的内存给OS，50%左右的分给SGA，剩下的给PGA。在SGA中，可以将40%分配给Buffer cache，共享池也要占40%，剩下的分给SGA中其他内存组件。如果是OLAP系统，除给OS留20%、30%左右的内存外，SGA和PGA可以一样大，甚至PGA还可以超过SGA。在SGA中，共享池要尽量的小，可以将大部分的内存都分给Buffer cache。

     无论你怎么分配，我们这样只是在事先估计一下共享池等内存组件的大小。每个内存组件到底多大合适，等到数据库运行一段时间后，还要参考历史资料再做具体的调整。

 

二、Pin频繁使用的对象和大对象：

1．什么对象应该被PIN在共享池中

     Pin住对象的意思是将对象常驻在库缓存中，即使空间不足了，也不会老化这些被PIN住的对象。那么，什么样的对象应该被PIN在库缓存中呢？有三种对象应该被PIN：

Ÿ 频繁使用的对象

Ÿ 大对象

Ÿ 序列

频繁使用的对象将它们PIN在库缓存中，避免它们老化，这是很合理的。不过，频繁使用的对象你PIN或者不去PIN它都无所谓。因为如果它的确频繁使用，它不会被老化的。Oracle对库缓存中对象的老化，是采用的Oracle改进的LRU法则，这种法则保证频繁使用的对象可以不被老化，而只老化那些不频繁使用的对象。但是，为了保险期间，如果你可以预知那个对象的确会被频繁的使用，那么将它用命令PIN在库缓存中比较保险，这样可以防止它被意外的老化。

占用字节达到1KB以上的对象，我们就可以称为大对象。大对象是一定要PIN在库缓存中的。特别是不太常用的大对象。常用的大对象因为会被频繁使用，即使不PIN它，也会停留在库缓存中，不过，我们上说了，保险期间，如果你可以确定它会被频繁的调用，那么还是PIN住它吧。但是，为什么不太常用的大对象也一定要PIN呢？这样不占内存吗？大对象被加载到内存中时，会老化很多小对象，这将非常耗时。而且，大对象的加载、老化非常容易引出大量的碎片。因此，越是不太常用的大对象，第一次使用时，它被加载到内存中，由于不太常用，过一会儿它被老化了。当再次调用到它时，它又要被加载到内存中。这样反复的加载、老化，再加载、再老化，等等，库缓存中不一会儿就充满碎片了。所在，这些不太常用的大对象一定要PIN到内存中。一般来说，只要是大对象，最好都PIN住它们。以免它们的加载、老化产生碎片。

序列为什么也要被PIN在库缓存中呢？因为序列有一个CACHE功能，这一点我们可以复习一下以前的内容，我就不讲了。如果序列老化，CACHE中预先生成的值就会丢失，序列将会出现间隙。因此，将有CACHE并且不想出现间隙序列PIN住也就很重要了。如果你不明白这其中的原理，只是看一些文章、资料中说要将序列PIN在共享池中，这就容易造成共享池内存的浪费。对于出现间隙也无所谓的，还有没有CACHE的序列，PIN它们是没有必要的。

 

2．如果PIN对象

（1）、PIN住SQL语句

第一步查出SQL语句在库缓存中的地址和SQL语句的HASH值：

SQL> select substr(sql_text,1,40) sqlt, address, hash_value from v$sql where sql_text = 'select name from ui1 where id=101';

SQLT                                     ADDRESS  HASH_VALUE

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

select name from ui1 where id=101        2345C39C 2874596039

 

第二步调用DMBS_SHARED_POOL.KEEP过程PIN住对象：

先来看看文档中此过程的定义：

DBMS_SHARED_POOL.KEEP (

   name VARCHAR2, 

   flag CHAR      DEFAULT 'P');

 

     它有两个参数，第一个参数是要PIN在内存中的对象的名字，也可以对象的地址、HASH值组成的字符串。对于上面的游标对象，必须使用地址、HASH值字符串，使用形式为：‘2345C39C，287459603’。也就是‘Address,hash_value’。

第二个参数，说明被PIN的对象的类型。有如下几种类型：

'P' or 'p' ：被PIN对象是包、过程或函数，这个是缺省值

'T' or 't' ：被PIN对象是TYPE。它类似C语句中的结构，是开发中常用的组合型数据。

'R' or 'r' ：被PIN对象是触发器

'Q' or 'q' ：被PIN对象是序列

 

     如果是游标对象，可以随变指定一个非上面列出的字母，我一般常用C，它是Cursor的第一个字母。另外，表、索引还有视图不能被PIN到内存中。好，下面调用此过程将上面例子中的游标PIN到内存中：

SQL> exec dbms_shared_pool.keep('2345C39C,2874596039','c');

PL/SQL 过程已成功完成。

被PIN住的对象，我们可以使用UNKEEP过程取消PIN，调用方法非常简单：

SQL> exec dbms_shared_pool.unkeep('2345C39C,2874596039','c');

PL/SQL 过程已成功完成。

     （注意，此处不必讲述，被PIN的游标对象，只有父子对象的句柄还有父对象的0号子堆内存将会一直保留，其他子堆仍会老化。这个可以通过Alter system flush shared_pool查看，刷新共享池后，只有对象句柄和父对象0号子堆还有共享池中。对于过程，在刷新共享池后，将保留句柄和堆0的内存，其他内存也会被清掉）