ITPub博客

首页 > Linux操作系统 > Linux操作系统 > shared_pool的sql命中率

shared_pool的sql命中率

原创 Linux操作系统 作者:legend_oracle 时间:2013-07-18 13:58:36 0 删除 编辑
如果一个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的命中率,但保证执行计划都是正确的.精确匹配为默认值.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26700801/viewspace-766622/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-17

  • 博文量
    3
  • 访问量
    13900