如何分析shared_pool的sql命中率

这篇文章给大家介绍如何分析shared_pool的sql命中率，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

如果一个SQL语句命中,将大大降低数据库服务器的负载,因为一个sql的硬解析生成执行计划是很消耗资源的。

下面列一下一个sql语句的执行过程。

1.将SQL语句经过hash算法后得到一个值Hash_Value

2.如果该值在内存中存在，那么叫命中执行软分析

3.如果该值不存在，执行硬解析

4.进行语法分析

5.进行语意分析

6如果有视图，将视图的定义取出

7.进行SQL语句的自动改写，如将子查询改成为连接

8.优选最佳的执行计划

9.变量的绑定

10.运行执行计划

11.将结果返回给用户

如果是软分析，直接运行9以后的步骤。

共享池的命中率

select namespace,pins,pinhits,reloads,invalidations from v$librarycache order by namespace;

NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY                 29530      29446          0             0
CLUSTER                429        421          0             0
INDEX                   60          3          0             0
JAVA DATA                0          0          0             0
JAVA RESOURCE            0          0          0             0
JAVA SOURCE              0          0          0             0
OBJECT                   0          0          0             0
PIPE                     0          0          0             0
SQL AREA            134280     128465        226            32
TABLE/PROCEDURE      63727      59391          4             0
TRIGGER               2375       2356          0             0

已选择11行。

SQL> desc v$librarycache
 Name                                      Null    Type
 ----------------------------------------- -------- ----------------------------
 NAMESPACE                                          VARCHAR2(15)
 GETS                                               NUMBER  可以理解为某个object解析的时候查找的次数(解析阶段)
 GETHITS                                            NUMBER get命中次数
 GETHITRATIO                                        NUMBER 这个值等于gethits/gets
 PINS                                               NUMBER   某个object 解析过后被执行的次数(发生在执行阶段)
 PINHITS                                            NUMBER  pin命中次数
 PINHITRATIO                                        NUMBER  这个值等于pinhits/pins
 RELOADS                                            NUMBER 某个object 解析过后被从新加载的次数(需要从新从磁盘读取object)，也就是没有被缓存到library cache中，这个通常由于shared pool 过小
 INVALIDATIONS                                      NUMBER 某个对象无效，通常由于对象定义被更改，需要从新解析
 DLM_LOCK_REQUESTS                                  NUMBER
 DLM_PIN_REQUESTS                                   NUMBER
 DLM_PIN_RELEASES                                   NUMBER
 DLM_INVALIDATION_REQUESTS                          NUMBER
 DLM_INVALIDATIONS                                  NUMBER

查看总的library cache pinhitratio 应该大于90%，最理想大于95%

实例启动以来的命中率

select sum(pinhits)/sum(pins) from v$librarycache;

SUM(PINHITS)/SUM(PINS)
----------------------
            .955756135

根据如下视图可以查看shared_pool建议大小

select * from v$shared_pool_advice

如果SQL的命中率小于90%,我们就要优化,优化的手段如下:

1.加大shared_pool_size 的大小，v$shared_pool_advice 根据这个视图

2.编写程序的时候使用变量传入，而不是使用常量

3.将大的包定在内存中

4.修改初始化参数cursor_sharing

   a.Force是比较理想的情况时候使用，如果你的业务逻辑很清晰，应用设计的非常好，那么可以使用FORCE，我对   一  些小的项目设置成FORCE，这样可以减少shared_pool的开支，9i的不建议如此设置，10.2.0.3以前的版本不建议这么设置，有很多bug；
    b.EXACE是精确匹配变量的一种解析方式，这个模式下，如果一个sql查询的时候where条件里写a=1和a=2时，优化器会生成新的执行计划，而不认为是一直的sql，占用shared_pool比率很严重；10.2.0.3以后的版本不建议设置；
    c.SIMILAR是个折中的方案，让优化器自己去判断，是Oracle比较向往的方式，但是无论是基于规则的优化器还是基于成本的优化器，目前做的都不是很好，Similar的bug目前要比FORCE还要多；

实验进行验证，如下:

conn scott/tiger

create table t1 as select * from emp;

insert into t1 select * from t1;

/

/

commt;

update t1 set empno=1000;

commit;

update t1 set empno=2000 where rownum=1;

commit;

create index i_t1 on t1(empno);

//分析表，告诉数据库表的大小

analyze table t1 compute statistics;

//分析列，告诉数据库empno列的数据分布是不均匀的,只有一行为2000,其它所有行为1000

analyze table t1 compute statistics for columns empno;

show parameter cursor_sharing

exact

SQL> set autot traceonly explain
SQL> select * from scott.t1 where empno=1000;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   229K|  6943K|   320   (5)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |   229K|  6943K|   320   (5)| 00:00:04 |

SQL> select * from scott.t1 where empno=2000;

执行计划
----------------------------------------------------------
Plan hash value: 4068921349

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

实验 SIMILAR

conn /as sysdba

alter system set cursor_sharing=SIMILAR  scope=spfile

startup force

set autotrace traceonly

show parameter cursor_sharing

SIMILAR

SQL> set autot traceonly explain
SQL> select * from scott.t1 where empno=1000;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   229K|  6943K|   320   (5)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |   229K|  6943K|   320   (5)| 00:00:04 |

SQL> select * from scott.t1 where empno=2000;

执行计划
----------------------------------------------------------
Plan hash value: 4068921349

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

实验 FORCE

conn /as sysdba

alter system set cursor_sharing=FORCE scope=spfile

startup force

set autotrace traceonly

show parameter cursor_sharing

FORCE

SQL> set autot traceonly explain
SQL> select * from scott.t1 where empno=1000;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   229K|  6943K|   320   (5)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |   229K|  6943K|   320   (5)| 00:00:04 |

SQL> select * from scott.t1 where empno=2000;

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   229K|  6943K|   320   (5)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |   229K|  6943K|   320   (5)| 00:00:04 |

错误的执行计划
实验的总论:
强制匹配(FORCE) 将where条件都用变量来处理,提高了SQL的命中率,但不能区分列值的数据敏感性,会导致部
分sql语句的执行计划不是正确的.
近似匹配(SIMILAR) 将where条件都用变量来处理,提高了SQL的命中率,但可以区分列值的数据敏感性,既保证了
语句的复用,提高的命中率,又可以区分列的条件差异.但oralce有的时候会有bug,导致美好的东西变成
了泡影.所以我们改了以后一定观察一下性能.
精确匹配(EXACT) 将原语句不处理,降低了SQL的命中率,但保证执行计划都是正确的.精确匹配为默认值.

关于如何分析shared_pool的sql命中率就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。