share pool使用率过高分析原因

数据库升级至10.2.0.5后发现share pool使用率99%，剩余几十M，目前share pool已经7.5G了，所以决定查找一下为什么share pool占用率那么高。

这条sql可以查询share pool的空闲内存

select * from v$sgastat a where a.NAME = 'free memory';

 

整个共享池分成了很多个小部分，按照功能来划分的话，共享池主要分成两部分：library cache 和dictionary cache ，library cache中主要存储的是执行的一些sql 的语句，plsql 语句等，而dictionary cache中主要存储的是数据库的一些数据字典信息，library cache 中变动的内容较多，而dictionary cache 内容相对来说比较固定，一般来说share pool占用率高都是sql导致，所以这次直接查询sql语句对share pool的占用

通过下面的sql查询占用share pool内存大于10M的sql：

SELECT substr(sql_text,1,100) "Stmt", count(*),
                sum(sharable_mem)    "Mem",
                sum(users_opening)   "Open",
                sum(executions)      "Exec"
          FROM v$sql
         GROUP BY substr(sql_text,1,100)
        HAVING sum(sharable_mem) > 10000000;

 

结果

Stmt           COUNT(*)           Mem         Open          Exec
sql1.....        9939              586536311 4141        2744178
sql2.....        736                34464774    1989        1393229
sql3.....        661                29698914    1914        1383670
sql4.....        425                27609803    556          423647
sql5.....        558                16594600    301          53726

sql1和sql2类似并且消耗了大量的share pool

 

接下来检查一下version count，version_count这个字段表示这个SQL在shared pool中的版本数，一个SQL语句在library cache中一般是对应一个cursor head和一个cursor body。

cursor head包含sql的代码和优化器的模式

cursor body则包括这个SQL的cursor的详细定义，比如excution plan, bind variable等等

通常情况下当Server process在shared pool中定位某条SQL语句的时候，首先要对这条SQL进行hash运算，通过运算结果去定位cursor head的地址，然后在根据这个地址去定位其相应的cursor body。如果version_count这个直过高比如>1,则表示针对这同一条SQL，在共享池中有一个cursor head和多个cursor body。

那么在定位一个SQL的时候由于存在过多的版本，需要扫描cursor header下面一链表私的一串儿的cursor body,那么这每一个body就对应了一个不同的版本。虽然SQl语句看似是一样的，但是由于body的不同，存在着不同的execution plan。如果这个version_count过高，那么随着cursor body数量的增加。在性能上也会有很大的影响，由于扫描cursor body的时候需要latch，对于shared pool来讲，latch是很宝贵的一种资源，因此这样的情况应该想办法来调整。来提高系统的性能。

 

查询一下version count过高的语句

SELECT address,
       sql_id,
       hash_value,
       version_count,
       users_opening,
       users_executing,
       sql_text
  FROM v$sqlarea
 WHERE version_count > 10;

 

查询出结果后，发现有部分sql的version count明显过高，和之前查出的消耗大量share pool内存的sql语句一致，现在问题sql已经定位到，接下来就要看看为什么这个sql会有过高的version count了

根据下面的sql来查询原因：

select SQL_ID,
       CHILD_NUMBER,
       UNBOUND_CURSOR,
       SQL_TYPE_MISMATCH,
       OPTIMIZER_MISMATCH,
       OUTLINE_MISMATCH,
       STATS_ROW_MISMATCH,
       LITERAL_MISMATCH,
       SEC_DEPTH_MISMATCH,
       EXPLAIN_PLAN_CURSOR,
       BUFFERED_DML_MISMATCH,
       PDML_ENV_MISMATCH,
       INST_DRTLD_MISMATCH,
       SLAVE_QC_MISMATCH,
       TYPECHECK_MISMATCH,
       AUTH_CHECK_MISMATCH,
       BIND_MISMATCH,
       DESCRIBE_MISMATCH,
       LANGUAGE_MISMATCH,
       TRANSLATION_MISMATCH,
       ROW_LEVEL_SEC_MISMATCH,
       INSUFF_PRIVS,
       INSUFF_PRIVS_REM,
       REMOTE_TRANS_MISMATCH,
       LOGMINER_SESSION_MISMATCH,
       INCOMP_LTRL_MISMATCH
  from v$sql_shared_cursor
 where sql_id = '5qtkht244b208';

查询完成后，结果为Y的就是问题的原因。

 

这次share pool使用率过高最终不是自己找到的问题，记录一下本次问题的原，是由于绑定变量的值变化，在oracle 10G CBO模式下根据不通绑定变量的值生成了不同的执行计划，导致产生了大量的cursor，最终通过修改隐藏参数_optim_peek_user_binds为false解决(9i里面值默认是false，10g默认是true)，关于这个参数的作用，下面转载了一个别人做的实验

 

SQL> create table t (id varchar2(10),text varchar2(4000));

表已创建。

SQL> insert into t select '1',object_name from dba_objects;

已创建92220行。

SQL> insert into t values( '2','aaa');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select id,count(*) from t group by id;

ID           COUNT(*)
---------- ----------
1               92220
2                   1

SQL> create index t_idx on t(id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed co
lumns',cascade=>true);

PL/SQL 过程已成功完成。

SQL> var fid varchar2;
SQL> exec :fid :='2';

PL/SQL 过程已成功完成。

SQL> select * from t where id=:fid;

ID           TEXT
----------  ------------
2              aaa


SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  fprdgayw5y5vq, child number 0
-------------------------------------
select * from t where id=:fid

Plan hash value: 470836197

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=:FID)

正常的binds peeking，由于2的选择度很高，采用索引扫描方式

 

SQL>alter session set "_optim_peek_user_binds"=false;

会话已更改。

SQL> select * from t where id=:fid;

ID           TEXT
----------  ------------
2              aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  fprdgayw5y5vq, child number 0
-------------------------------------
select * from t where id=:fid

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    85 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 92204 |  2070K|    85   (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=:FID)

通过以上实验看来，这个参数设置成false后确实能解决这个问题，使得不会因为执行计划产生过多的cursor，但是上述实验的部分中的这种索引在数据库中就不用武之地了

 

下面介绍一些常见的问题：

1.bind_mismatch一般是由于bind value的长度不同导致bind buffer无法重用，最终导致cursor无法重用。

例如：对于字符类型的字段，进行绑定变量的时候，第一次会使用32字节的BUFFER，如果该值小于32字节的话，第二次执行这个SQL的时候，如果小于32字节，那么可以共享这个CURSOR,如果大于，就无法共享，原因就是BIND_MISMATCH，此时会产生一个子CURSOR，同时分配128字节的BIND BUFFER，以此类推。

 针对这类的问题要查看一下绑定变量的值

select position, LAST_CAPTURED, datatype_string, value_string
  from v$sql_bind_capture
 where sql_id = '66xu41fc3p25f';

         1 2011-06-24 17:09:28 VARCHAR2(32)         BILLQQ

         2 2011-06-24 17:09:28 NUMBER               103

         3 2011-06-24 17:09:28 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 17:09:28 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:59:16 VARCHAR2(32)         wantai1472888

         2 2011-06-24 16:59:16 NUMBER               103

         3 2011-06-24 16:59:16 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:59:16 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:59:10 varchar2(32)         gy928888@vip.qq.com

         2 2011-06-24 16:59:10 NUMBER               103

         3 2011-06-24 16:59:10 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:59:10 VARCHAR2(32)         yyyy-mm-dd

 

2.关于cursor不能共享方面有大量的bug，可以去metalink上面搜索一下，根据版本和bug说明做一下判断。