Oracle性能优化 之 库缓存

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

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

一、库缓存中的信息

1．什么是执行计划

2．为什么要共享执行计划

执行计划的生成要耗费许多CPU时间，而且优化器会将生成的执行计划存放到共享池中。如果你执行很多相同的语句，但没有共享执行计划，优化器每次都要搜索共享池、查找有没有可以共享的执行计划，没有查找后它自己生成，再把生成的执行计划存入共享池。就是说相同的语句如果你没有共享计划，不但消耗了更多的CPU生成执行计划，而且每次还要搜索共享池、保存新生成的执行计划，并且，管理共享池中的执行计划还会有一些额外的负担。这些工作都会拖慢SQL语句的执行速度。Oracle并不会因为你没有共享执行计划而取消有关执行计划的一些管理性工作。打个比方，你有一辆自行车，你本来可以骑着它加快速度，但你不但不骑它，反而扛着它，结果是速度大大减慢。Oracle明明提供了一块内存叫做库缓存，希望你可以在其中共享执行计划，就算你不共享，库缓存还是要存在的，这个时候，你就相当于扛着自行车在走了。你没有把库缓存的优势发挥出来，你却承受了库缓存的管理负担。因此，共享执行计划是最优化使用共享池的最重要一点。

二、库缓存的调优

库缓存的调优最重要一点就是确做用户可以共享执行计划。这应该从程序员和DBA两个角度去作。作为程序员，应该学会使用绑定变量，这可以使本来相似的语句变得一模一样，从而使它们可以共享执行计划。下面，让我们先来了解一下什么样的情况下，执行计划才能被共享。

1．共享执行计划

要共享执行计划，语句的文本必须一模一样，比如，如下的语句就不能共享执行计划：

语句1：Select * from tab1 where id=1;

语句2：select * from tab1 where id=1;

为什么语句1和语句2不能共享执行计划呢？第一个语句的第一个字母是大写，而第二个语句第一个字母是小写。不但大小写，就算多了一个空格，也不能共享执行计划。

假设上面两条语句的文本完全一样的，但语句1在用户USER1下发出，语句2在用户USER2中发出。并且这两个用户下都有自己的TAB1表，那么这两个语句也不能共享计划。

如果两条语句要想共享计划，两条语句的文本不但要完全相同，语句执行时的环境也必须完成相同才行。这里所说的“环境”，指的是一些初始化参数的值。并不是指不同的用户。当然，如果两个用户分别操作不同的表，在表名相同的情况下，是不会共享执行计划的。如果两个用户发出文本相同的语句，操作的又是同一个表，那么，是可以共享执行计划的。那么，不同的用户，如何用同样的名字操作同一表呢？接着上面的例子，假设USER1中有一个TAB1表，而USER2中没有，USER1的查询语句形式如下：

    select * from tab1 where id=1;

USER2想要查询USER1中的表，形式如下：

    select * from user1.tab1 where id=1;

这两条语句也不能共享执行计划，因为语句文本有很大的不同。USER2的语句多了一个“USER1.”。对于这样的情况，我们可以使用公用同义词使USER2访问TAB1表时，不必在表名前加“USER1.”来解决。看下面的例子：

（举一个这样的例子）

也就是说，只要语句文本一模一样，执行语句时的环境一模一样，两条语句就可以共享执行计划。

2．绑定变量

再看下面一种情况。假设有一个大型网站，每天要有大量的用户登录，用户信息存储在一个User_info表中，每个用户登录时，都要输入用户ID和密码，数据库根据用户ID在User_info中进行查询，取出用户密码和其他的一些用户基本信息，等等，后面的工作我就不说了，就是每个用户在登录时，数据库都要根据用户ID进行一次查询，假设又有两个用户登录了，一个用户的ID是1另一个用户ID是2。这两个用户登录时的查询语句如下：

用户1的查询语句：select * from user_info where id=1;

用户2的查询语句：select * from user_info where id=2;

这两条语句是不会共享执行计划的。我们可以实验一下：

Step1：在139号会话中发布查询语句：

SQL> select * from tab1 where id=1;

        ID NAME

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

         1 ICOL$

         1 I_OBJ#

此语句是首次查询TAB1表，这将引出大量的递归调用，这些递归调用将会进行多次硬解析。以后我们在发布以TAB1的查询时，将不会有递归调用。这一步，是会下面的实验做准备，下面开始实验。

Step2：在另一会话中查询会话139的解析情况：

SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';

NAME                                                                  VALUE

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

parse count (total)                                                     386

parse count (hard)                                                       60

parse count (failures)                                                    0

Step3：在139会话发出查询语句：

SQL> select * from tab1 where id=2;

        ID NAME

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

         2 I_USER1

         2 PROXY_ROLE

Step4：在另一会话中再次查询会话139的解析情况：

SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';

NAME                                                                  VALUE

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

parse count (total)                                                     387

parse count (hard)                                                       61

parse count (failures)                                                    0

Step5：在139人再次发出相似的查询语句：

SQL> select * from tab1 where id=3;

        ID NAME

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

         3 CON$

         3 I_IND1

Step6：再次查询139会话中的解析情况：

SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';

NAME                                                                  VALUE

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

parse count (total)                                                     388

parse count (hard)                                                       62

parse count (failures)                                                    0

这个实验的结果我们已经看到了，这两条语句不同共享执行计划。试想如果每天有大量的用户登录，每个用户在登录时，都无法共享相似语句的执行计划，这将白白耗费多少CPU时间啊。这个时间就是绑定变量派上用场的时候了。下面我们先来看个使用绑定变量的例子：

Step1：在139会话中定义绑定变量User_id，并将它的值赋为4。

SQL> var user_id number;

SQL> exec :user_id:=4;

PL/SQL 过程已成功完成。

Step2：在139会话中使用绑定变量进行查询：

SQL> select * from tab1 where id=:user_id;

        ID NAME

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

         4 UNDO$

         4 I_CDEF2

Step3：观察139会话的解析次数：

SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';

NAME                                                                  VALUE

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

parse count (total)                                                     395

parse count (hard)                                                       69

parse count (failures)                                                    0

Step4：将绑定变量User_id的值变为5，再次执行查询：

SQL> exec :user_id:=5;

PL/SQL 过程已成功完成。

注意，此语句也要解析，因此，我们在这里还要查看一下解析次数：

Step5：查看139会话的解析次数：

SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';

NAME                                                                  VALUE

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

parse count (total)                                                     396

parse count (hard)                                                       70

parse count (failures)                                                    0

Step6：在139会话中，用绑定变量再次做一次查询：

SQL> select * from tab1 where id=:user_id;

        ID NAME

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

         5 C_COBJ#

         5 I_PROXY_RO

查询的结果，是ID为5的行。这里，我们使用了同样的语句分别查询出了ID为4的行和ID为5的行。

Step7：查看139会话的解析次数：

SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';

NAME                                                                  VALUE

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

parse count (total)                                                     397

parse count (hard)                                                       70

parse count (failures)                                                    0

可以看到最后一次执行查询后，硬解析没有增加。这就是绑定变量的作用。

在这里只能使用绑定变量，不能使用普通变量。绑定变量是Oracle的一种特殊变量，对它赋值的过程，是在Oracle优化器解析过语句之后，解析语句的任务就是确定语句的执行计划。当使用绑定变量后，优化器在解析语句时，解析的是“select * from tab1 where id=:user_id”，而不是“select * from tab1 where id=5;”。优化器将按照“select * from tab1 where id=:user_id”确定语句的执行计划，而不是“select * from tab1 where id=5”。在解析过语句已经确定了语句的执行计划后，优化器将执行计划交给服务器进程去执行时，再用实际的值替换绑定变量。如果你使用普通的变量，Oracle优化器将在解析前用变量的值替换变量，这样在解析语句、生成执行计划时，优化器看到的语句就是“select * from tab1 where id=4”或“select * from tab1 where id=5”等等，因为它们的文本不同，优化器将选择重新为它们生成执行计划。而不是取出已经存入库缓存的执行计划直接执行。