Oracle中shared pool调优

shared pool调优主要目标是减少hard parse。

导致hard parse的因素有：

• 没有使用绑定变量
• library cache空间不足，导致解析过的SQL被换出，导致reparsed shared SQL
• 避免对SQL访问的对象进行DDL操作
  

现象：

• awr报告中Instance Efficiency Percentages, 如下所示

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	98.72	In-memory Sort %:	99.86
Library Hit %:	99.97	Soft Parse %:	99.92
Execute to Parse %:	89.09	Latch Hit %:	99.99
Parse CPU to Parse Elapsd %:	7.99	% Non-Parse CPU:	99.95

其中Library Hit表示Oracle从Library Cache中检索一个解析过的SQL或PL/SQL语句的比率。当应用程序调用SQL或存储过程时，Oracle检查Library Cache确定是否存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要调大shared pool区。

其中Soft Parse:软解析的百分比（softs/softs+hards），近似当作sql在共享区的命中率，太低则需要调整应用使用绑定变量。

• awr报告中Shared Pool Statistics,如下图所示

	Begin	End
Memory Usage %:	47.19	47.50
% SQL with executions>1:	88.48	79.81
% Memory for SQL w/exec>1:	79.99	73.52

Memory Usage %：对于一个已经运行一段时间的数据库来说，共享池内存使用率，应该稳定在75%-90%间，如果太小，说明Shared Pool有浪费，而如果高于90，说明共享池中有争用，内存不足。如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。SQL with executions>1：执行次数大于1的sql比率，Memory for SQL w/exec>1：执行次数大于1的SQL消耗内存的占比。

• v$librarycache

查看读取命中和执行命中 SQL> select namespace, gets,gethits, pins,pinhits, reloads, invalidations from v$librarycache;

NAMESPACE        GETS    GETHITS       PINS    PINHITS    RELOADS INVALIDATIONS
---------- ---------- ---------- ---------- ---------- ---------- -------------
SQL AREA         9034       3389      54875      51544         77           187
TABLE/PROC      10311       7896       9385       7254         99             0
EDUREBODY              738        671       1068        996          0             0
TRIGGER            10          7         33         30          0             0
INDEX              98         34         64          0          0             0
CLUSTER           509        501        315        307          0             0
QUEUE             870        830       2740       2691          5             0
RULESET            28         27         28         27          0             0
SUBSCRIPTION         12          7         12          7          0             0
EDITION            61         60         95         93          0             0
DBLINK              3          2          0          0          0             0
OBJECT ID          59          0          0          0          0             0
SCHEMA           3601       3590          0          0          0             0
DBINSTANCE          1          0          0          0          0             0

途径有：

• 使用动态绑定，如下所示：

select * from emp where empno = :empno;    //set the bind variable :empno to 123 and execute the query

• 增加shared pool size:

查看v$shared_pool_advice，如下所示：

SQL> select shared_pool_size_for_estimate as pool_size, SHARED_POOL_SIZE_FACTOR,estd_lc_size, estd_lc_time_saved from v$shared_pool_advice;

 POOL_SIZE SHARED_POOL_SIZE_FACTOR ESTD_LC_SIZE ESTD_LC_TIME_SAVED
---------- ----------------------- ------------ ------------------
       124                   .4697           19                242
       152                   .5758           27                242
       180                   .6818           27                242
       208                   .7879           27                242
       236                   .8939           27                242
       264                       1           27                242
       292                  1.1061           27                242
       320                  1.2121           27                242
       348                  1.3182           27                242
       376                  1.4242           27                242
       404                  1.5303           27                242
       432                  1.6364           27                242
       460                  1.7424           27                242
       488                  1.8485           27                242
       516                  1.9545           27                242
       544                  2.0606           27                242

从上图看出，变化shared pool size没有对reloads的估计时间有多少影响，所以当前不需要调整shared pool size.