共享SQL语句减少硬解析

这篇文章是参考甲骨论老相老师所做的教学视频:
http://v.youku.com/v_show/id_XMzkyMTczMTQ4.html
所做的学习笔记:

接上文:
http://nvd11.blog.163.com/blog/static/200018312201301875752730/
sql 是在shared Pool里面解析成执行计划的.

如图:



        1.什么是SQL语句共享
        sql 语句传入shared pool后, 会被先转换成ASCII码, 然后经过一系列运算,其中包括HASH运算,得出HASH值,然后得出一串数字,并包含1个内存链chain号码.

        Server Process 就会拿着chain号码和HASH号码,到Library cache上找到对应的内存链chain, 然后遍历上面的chunk(比较Hash值), 如果有1个chunk的Hash与之相同,则证明这条sql曾经被硬解析过, 就直接可以拿出对应执行计划, 这就是软解析.

         我们也可以说这条sql与之前硬解析过的sql共享了.

        如果找不到hash值相同的chunk,  则认为这条sql未被硬解析过, 就要执行上图的动作2,进行硬解析.

        所以可以看出,那个HASH值十分重要, 而hash值是由ASCII码运算出来的, 所以共享sql之间的ASCII码要相同.

        而ASCII是由SQL语句的字符决定的.  所以两条共享sql语句的每个字符要相同. 而且大小写都要相同

        举个例子: 下面3条语句
        select /*comment_1*/ count(1) from dba_tables where table_name = 'EMP';
        select /*comment_1*/ count(1) from dba_tables where table_name = 'DEPT';
        select /*comment_1*/ count(1) from  dba_tables where table_name = 'DEPT';

       首先,很明显 第一条语句和第二条语句不相同, 因为EMP 和 DEPT 是两个不同的字符
       第二条和第三条呢?  也不相同, 因为第二条 from 与 dba_tables直接只有1个空格, 而第3条有2个! 所以他们的ASCII码不同,所以规范编写SQL语句很重要啊.

 ===============================================分割线===============================
2.判断SQL语句有无共享
下面我分别执行上面3条语句1次

 

 
注意第2条和第3条语句的空格区别哦

然后可以去v$sql 去查看已执行过的sql语句:
 select sql_id,sql_text,executions from v$sql where sql_text like '%comment_1%';

可以看出这3条sql语句分别被执行了1次    (因为调整显示格式, 查询v$sql的这条执行3次)
也就说进行了3次硬解析.

其实每执行1条sql语句, 都会分配1个sql_id(参见上图),上面3条语句被分配3个sql_id, 所以oracle认为上面3条语句是不一样的.

所以Oracle中sql语句要相同:
则所有字符要一样, 包括空格,空行,回车...

例如, 如果要第3条语句与第一条语句共享, 则要去掉from 和 dba_tables之间的1个空格:
我们在执行一次:

 
可发现,v$sql中并没有多出一个sql_id, 只不过第一条的执行次数变成了2,  也就是说第4次执行的那条跟第一条的sql_id是一样的,因为它们的字符完全一样.

那么第一条和第二条sql能不能共享呢, 因为条件'EMP' 跟 'DEPT' 完全不同啊, 貌似是个硬伤.

方法是有的:
就是用1个相同名字的变量, 分别赋值'EMP'和'DEPT'各执行一次, 因为变量名相同.
做个例子:

写1个 sql 脚本如下:

 

上面的脚本会插入11行数据进入表test, 虽然每一行的值都是不同, 但是我们可以用变量  i来装载它.
然后使用 execute 来执行sql语句.

下面执行这个脚本:

在查看v$sql 视图:

 
可以见到11次sql语句之分配了1个 sql_id , 用:1 来取代实际的值. 也就是说11条insert语句之产生1此硬解析, 其余10次是软解析,成功共享SQL.
这个方法很重要, 可以想象一下银行业务, 可能经常大量地使用同一条sql语句,但是参数次次都不同, 如果在写程序时不使用绑定变量,则会产生大量的硬解析,  而使用了绑定变量去取代参数, 就能避免这个情况!

所以总结一下共享SQL的关键:

1. 严格统一书写风格
2. 使用绑定变量

提醒下, v$sql实在上就是缓存在shared pool里的sql语句信息.
可以用alter system flush shared pool 来清空v$sql, 不过接下来就大量硬解 你懂的.


 ===============================================分割线===============================

3.如何找出未被共享(硬解析)的sql语句:

原理都好简单, 因为如果一条sql未被共享,则这条sql在v$sql里的执行次数为1.
所以只需查找v$sql
select sql_id, sql_fulltext,executions from v$sql where sql_text like '%from t%' and executions = 1 order by sql_text ;

注意绿色高亮部分根据实际情况修改

order by 排序很重要啊, 因为有些sql的确只被用户执行了一次, 用order by就可找出那些类似的sql,而被硬解析多次了~

下面做个例子,
修改一下上面的脚本, 令到循环中每次执行的语句都不一样(参数不一样)
如下图, 注意右边是修改后的:
执行了900几次..

 
然后执行这个修改后的脚本:

 
接下来用
select sql_id, sql_fulltext,executions from v$sql where sql_text like '%insert%' and executions = 1 order by sql_text;
查看 未被共享的语句.

 

接下来去查看导出来的log文件, 通常就可以分析出哪些sql语句未被共享而被执行大量硬解析了..

 
这是1个很经典的查找未被共享sql语句的方法, 用于数据库优化.

===============================================分割线===============================

4. 设置cursor_sharing参数
找到未被共享的sql语句后怎么做呢, 一般会交给程序员,让他们修改.

但是做过码农的都知道, 程序里的代码有些时候不是那么好改了,有时真的牵一发而动全身啊..

那改不了怎么办.

其实还有1个办法是设置oracle的一个参数: cursor_sharing
我们先查看当前默认的参数值是什么:

 
可以见到当前值是EXACT, 代表什么意思呢, 我们去oracle官方文档查下:

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Values:

• FORCE

  Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.

• EXACT

  Only allows statements with identical text to share the same cursor.

可以见到, 游标共享参数决定了什么情况下可以共享SQL

EXACT : 只有当sql语句完全一样才共享sql

关键是FORCE了:

首先共享SQL失败原因有两种:

1.是书写不规范(大小写, 空格)

2. 没有用绑定变量去取代参数.

对于第一种原因的sql共享失败, 这个cursor_sharing设置也没办法解决, 不过很容易交给程序员解决.

而将cursor_sharing设置为force 可以解决第2种情况,

我们测试一下:

首先将cursor_sharing 设置为force

 
跟住清空shared pool缓存:

 

然后我们再执行一次上面执行过的那个脚本:

 
执行:

 
我们再去查看v$sql..
但是由这个测试来讲是无效果的... 而老相在视频里没提供测试..
可以见到 sql语句并没有共享, 依然是没一条insert 语句分配1个sql id.

 

于是我私人再做多个测试,睇下这个参数会不会影响硬解析的数量.

首先将cursor_sharing设置成默认的'EXACT', 并清空shared pool缓存.

接着查看当前硬解析和软解析数量:

 
可以记下:
当前已经发生过的解析数量总数为: 611230
其中硬解析数量为: 35774

这时我们执行一次insert脚本:

 
再次查看解析数量:

 
其中解析数量总数增加了  613301 - 611230 =  2071
而硬解析数量增加了 36778 - 35774 =  1004
可以看出硬解析的数量是相当接近 脚本中循环执行的insert 语句数量的!

这是我们将cursor_sharing 设置为Force
并且清空shared pool

再查看当前的解析数量:

 

 当前已经发生过的解析数量总数为: 616473
其中硬解析数量为: 37432


这时我们再次执行一次insert脚本:

 
再次查看解析数量:

 

 
其中解析数量总数增加了  618008 - 616473 =  1535
而硬解析数量增加了 38511 - 37432 =  1079

尼玛硬解析数量反而增加了

我觉得原因是可能这个参数对我脚步那种sql 无作用, 至于到底会影响哪些sql共享, 以后再研究了...