ORCLE SQL不绑定变量导致的性能问题

1、概念

1.1 SQL 执行过程简介

执行sql的过程，会将sql的文本进行hash运算，得到对象的hash值，然后拿hash值，去Hash Buckets里遍历缓存对象句柄链表，找到对应的缓存对象句柄，然后就可以得到缓存对象句柄里对应sql执行计划、解析树等对象，所以执行相同的sql第二次执行时是会比较快的，因为不需要解析获取执行计划，解析树等对象，如果找不到库缓存对象句柄，就需要重新解析，这个过程解析过多，容易造成硬解析问题.

硬解析：是指Oracle在执行目标SQL时，在库缓存中找不到可以重用的解析树和执行计划，而不得不从头开始解析目标SQL并生成相应的Parent Cursor和Child Cursor的过程。

软解析：是指Oracle在执行目标SQL时，在Library Cache中找到了匹配的Parent Cursor和Child Cursor，并将存储在Child Cursor中的解析树和执行计划直接拿过来重用，无须从头开始解析的过程。

由此可知，假如sql执行过程，在共享池里找不到执行计划、解析树等就会重现解析sql，生成执行计划和解析树等，这个过程是比较耗时间的，所以要想办法尽量不要重现解析sql，需要执行计划直接去共享池拿已经生成的

举个例子，select * from sys_user where userid=‘u10001’;和select * from sys_user where userid=‘u10002’;，这两个很类似的sql在执行过程，生成的执行计划很有可能是不一样的，也就是说第一条sql执行后，第二条sql继续执行，假如发现找不到对应执行计划，就会再解析sql，重现生成session cursor和一对shared cursor(parent cursor和child cursor)

然后，我们不想重新解析sql，有什么方法？方法就是用绑定变量的方法

1.2 SQL绑定变量

SQL绑定变量就是不在sql中明确指定一个条件值，而是通过一个变量来输入。例如上面的两条sql可写成 select * from sys_user where userid = :u;

这样这种类型的一堆sql都只会解析一次，不用每条sql都解析一遍，可以很好的提高系统处理能力。通过给变量u赋值实现每次的不同条件的查询。

java中SQL使用绑定变量的写法示例

String empno = 'xxxxx';
String query_sql = 'select ename from t_emp where empno = ? '; //嵌入绑定变量
stmt = con.prepareStatement( query_sql );
stmt.setString(1, empno ); //为绑定变量赋值
stmt.executeQuery();

1.3 sga、share pool 、db_cache

SGA（System Global Area）系统全局区。SGA分为不同的池，我们可以通过视图v$sgastat查看，如下所示。

SQL> select pool ,sum(bytes) bytes from v$sgastat group by pool;

POOL                   BYTES
-------------- -------------
shared pool      51002736640
java pool          536870912
streams pool      1073741824
numa pool        17943707280
                141867990288
large pool         536870912

我们可以看到SGA由java pool（java 池）、shared pool（共享池）、large pool（大池）和没有名字的池组成。其中那块没有名字的内存又包括块缓冲区（缓存的数据库块）、重做日志缓冲区和“固定SGA”区专用的内存。可以再细分如下

SQL> select nvl(pool,name) pool,round(sum(bytes)/1024/1024) sizemb from gv$sgastat group by nvl(pool,name) order by 1, 2 desc;

POOL                           SIZEMB
-------------------------- ----------
buffer_cache                   269312
fixed_sga                          58
java pool                        1024
large pool                       1024
log_buffer                        198
numa pool                       35592
shared pool                     92160
shared_io_pool                   1024
streams pool                     2048

我们这里重点关注的是 buffer_cache。buffer_cache 是数据库数据缓冲区，对于热数据，oracle会存到内存以方便读取，这样能避免通过磁盘读取，提高读取效率。当buffer_cache容量不足的情况下，大量热数据无法存到内容，需要通过磁盘读取，会导致效率变慢，涉及的数据量越大越慢。

shared pool（共享池）上面已经提过，sql硬解析后生成的解析树和执行计划会存到这里。若shared pool容量不足，则oracle会自动去回收shared pool内使用量少的数据，这个清理是需要时间的，所以也会导致sql的硬解析时间增大。

2、实际案例分析

2.1 问题描述

接到一个业务系统的负责人反馈系统变慢了，直接用sql查数据库也变慢，虽然只是从100ms变成500ms，但是因为一个请求要执行多次这个sql所以整体的请求时间变成了很多。而且RAC的两个节点，只有一个异常，另外一个正常，所以要求运维这边重启这个节点。

运维这边检查了下数据库状态、机器的CPU、内存、磁盘等资源没发现异常，本着尽快恢复业务的考虑重启了数据库。重启后确实好了。但是过了一周多又反馈问题再次出现，又重启了次，然后很快又出现，时间间隔更短了。

2.2 问题分析

由于主机资源不存在瓶颈，那就可能是实例本身的问题了。通过检查sga发现了问题:SGA 配置了48G，而share pool 就占用了近40G，db_cache只占了6G，而整个sga也已经占满。

可用下面sql查询：

select inst_id,nvl(pool,name) pool,round(sum(bytes)/1024/1024) sizemb from gv$sgastat group by inst_id,nvl(pool,name) order by 1, 3 desc;

由于share pool 占用了大量空间，db_cache能用的只有很少一部分，所以很多热数据都只能通过磁盘去读取，而且由于sga已经占满，当有新的硬解析sql时就需要等待空间释放也会导致耗时增加。

基于以上分析，怀疑是存在大量硬解析sql把share pool占满导致，通过以下sql查询硬解析sql的情况

select a.sql_ids,fms,cnt,exec_cnt,sql_text from 
(select max(sql_id) sql_ids,to_char(sq.FORCE_MATCHING_SIGNATURE) fms,sum(executions) exec_cnt,count(*) cnt
  from gv$sqlarea sq where sq.FORCE_MATCHING_SIGNATURE <> 0
 group by to_char(sq.FORCE_MATCHING_SIGNATURE)
having count(*) >= 500) a,gv$sqlarea b where a.sql_ids=b.sql_id order by cnt desc;

exec_cnt 是这些sql的执行总次数。cnt 是指同一类型的sql有多少条（也即是有多少条sql只是变量的值不一样其它都一样)

FORCE_MATCHING_SIGNATURE 这个字段是oracle用来标识sql是否有可能共用游标。当cursor_sharingde参数从默认的EXACT改成FORCE后，ORACLE会强制把FORCE_MATCHING_SIGNATURE这个字段的值相同的这类型sql当作同一条sql（也即是强制当作绑定变量sql）。但是不建议通过修改这个参数来实现绑定变量，可能会引发新的问题也可能会触发一些未知的BUG。

sql里的条件 count(*)>=500是指这样的类似的sql数量超过500条，可根据实际需要进行更改。cnt 这个数值越多，说明硬解析越多，该类型的sql执行的频率越高，是应该优先进行绑定变量改造的。

分析汇总：由于大量sql不绑定变量导致硬解析，占满share pool，从而导致db_cache太小，影响了sql效率。因为是RAC节点，业务又是长连接，大部分连接落到同一个节点，所以一个节点异常，另外一个节点的sga还没满所以还正常。

2.3 问题解决

2.3.1 根本解决

要想从根本上解决该问题，只能推动业务修改sql，优先把执行频率高的这些需要硬解析的sql改成绑定变量的方式执行。

但是涉及到推动业务改造，而且依赖供应商，进展不太理想。

2.3.2 临时解决办法

重启是最快的恢复办法。如果不想重启而要释放share pool，可使用一下语句：

alter system flush shared_pool;

但是正在执行的会话不会释放。如果应用是长连接，那需要将会话杀一次或者让应用重启才能生效。

db_cache没有设置默认最小值，所以导致其空间被share pool 都抢光了。可以尝试设置下：

alter system set db_cache_size=24G scope=spfile sid='*';

设置完要重启才能生效。这样就保证了db_cache_size 至少有24G的空间，但是这样 share pool 最多也只能使用20G多点的空间了，会导致硬解析时需要等待空间释放来的更快了。

而且因为修改了这个参数，导致数据库触发了一个BUG而整个实例重启了。所以并不是一个很好的办法。

3、上面提到过的，把cursor_sharingde参数从默认的EXACT改成FORCE，这个方案存在未知风险，也可能触发新的bug，若要尝试请慎重。

1、 概念#

1.1 SQL 执行过程简介#

1.2 SQL绑定变量#

1.3 sga、share pool 、db_cache#

2、实际案例分析#

2.1 问题描述#

2.2 问题分析#

2.3 问题解决#

2.3.1 根本解决#

2.3.2 临时解决办法#