这篇文章给大家介绍如何分析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命中率就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/tech/aiops/199265.html