导航：首页 > 服务器 >

如何分析shared_pool的sql命中率

发表于：2025-02-02 作者：千家信息网编辑

千家信息网最后更新 2025年02月02日，这篇文章给大家介绍如何分析shared_pool的sql命中率，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。如果一个SQL语句命中,将大大降低数据库服务器的负载,因为一个sql的

千家信息网最后更新 2025年02月02日如何分析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

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

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

实验 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

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

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

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

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

很赞哦！