共享SQL语句减少硬解析

本是古典何须时尚 2022-03-19 13:08 356阅读 0赞

这篇文章是参考甲骨论老相老师所做的教学视频:  
http://v.youku.com/v\_show/id\_XMzkyMTczMTQ4.html  
所做的学习笔记:  
  
接上文:  
http://nvd11.blog.163.com/blog/static/200018312201301875752730/  
sql 是在shared Pool里面解析成执行计划的.  
  
如图:  
![Image 1][]  
  
  
**        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次

![6597853718447555244.jpg][]

注意第2条和第3条语句的空格区别哦  
  
然后可以去v$sql 去查看已执行过的sql语句:  
  **select sql\_id,sql\_text,executions from v$sql where sql\_text like '%comment\_1%';**  
![Image 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个空格:  
我们在执行一次:

![6597744866796399186.jpg][]

可发现,v$sql中并没有多出一个sql\_id, 只不过第一条的执行次数变成了2,  也就是说第4次执行的那条跟第一条的sql\_id是一样的,因为它们的字符完全一样.  
  
那么第一条和第二条sql能不能共享呢, 因为条件'EMP' 跟 'DEPT' 完全不同啊, 貌似是个硬伤.  
  
方法是有的:  
就是用1个相同名字的变量, 分别赋值'EMP'和'DEPT'各执行一次, 因为变量名相同.  
做个例子:  
  
写1个 sql 脚本如下:

![6597818534075471408.jpg][]

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

![6597709682424310460.jpg][]  
  
在查看v$sql 视图:

![6597175319774531595.jpg][]

可以见到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几次..

![6597732772168500120.jpg][]

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

![6598244045075202995.jpg][]

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

![6597209404634992187.jpg][]

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

![6597732772168500279.jpg][]

这是1个很经典的查找未被共享sql语句的方法, 用于数据库优化.  
  
**===============================================分割线===============================**  
  
**4. 设置cursor\_sharing参数**  
找到未被共享的sql语句后怎么做呢, 一般会交给程序员,让他们修改.  
  
但是做过码农的都知道, 程序里的代码有些时候不是那么好改了,有时真的牵一发而动全身啊..  
  
那改不了怎么办.  
  
其实还有1个办法是设置oracle的一个参数: **cursor\_sharing**  
我们先查看当前默认的参数值是什么:

![6598183571935681538.jpg][]

可以见到当前值是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

![6598100009051967536.jpg][]

跟住清空shared pool缓存:

![6598233049958926606.jpg][]

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

![6597256683635004976.jpg][]

执行:

![6598109904656620617.jpg][]

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

![6597239091448951752.jpg][]

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

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

![6597606328331305237.jpg][]  
  
接着查看当前硬解析和软解析数量:

![6598112103679876360.jpg][]

可以记下:  
当前已经发生过的解析数量总数为: **611230**  
其中硬解析数量为: **35774**  
  
这时我们执行一次insert脚本:

![6597909793540582556.jpg][]

再次查看解析数量:

![6598178074377541927.jpg][]

其中解析数量总数增加了  613301 - 611230 =  **2071**  
而硬解析数量增加了 36778 - 35774 =   **1004**  
**可以看出硬解析的数量是相当接近 脚本中循环执行的insert 语句数量的!**  
  
这是我们将cursor\_sharing 设置为Force  
并且清空shared pool

![6598162681214749004.jpg][]  
  
再查看当前的解析数量:

![6598208860703119965.jpg][]

当前已经发生过的解析数量总数为: **616473**  
其中硬解析数量为: **37432**  
  
  
这时我们再次执行一次insert脚本:

![6597796543842923267.jpg][]

再次查看解析数量:

![6598089013935688457.jpg][]

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

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

[Image 1]: 
[6597853718447555244.jpg]: http://img4.ph.126.net/QoCcMkky7uAhTs_-QXsjog==/6597853718447555244.jpg
[6597744866796399186.jpg]: http://img6.ph.126.net/m-obuhgZ66389qIKGyb9-A==/6597744866796399186.jpg
[6597818534075471408.jpg]: http://img8.ph.126.net/j01X2WKzNs-SiyFxTKmvAA==/6597818534075471408.jpg
[6597709682424310460.jpg]: http://img0.ph.126.net/jkL4FtY24xaBVEchdlOYgg==/6597709682424310460.jpg
[6597175319774531595.jpg]: http://img5.ph.126.net/Rm8-lE5RJHBesiY8-gvk5g==/6597175319774531595.jpg
[6597732772168500120.jpg]: http://img0.ph.126.net/w2O4tvuFUKFndXI8rJ8zWQ==/6597732772168500120.jpg
[6598244045075202995.jpg]: http://img5.ph.126.net/TpU1gLJXRjpQzxIRuqtqNA==/6598244045075202995.jpg
[6597209404634992187.jpg]: http://img7.ph.126.net/aZjM74ghrCCNXJCCBTF9lg==/6597209404634992187.jpg
[6597732772168500279.jpg]: http://img9.ph.126.net/-3Lr7-K-ijODH3IgWbBCbA==/6597732772168500279.jpg
[6598183571935681538.jpg]: http://img8.ph.126.net/dTwpfidul6aC5p2uWF7LxQ==/6598183571935681538.jpg
[6598100009051967536.jpg]: http://img6.ph.126.net/XuYtt1ddM5pH40CKwpxnOQ==/6598100009051967536.jpg
[6598233049958926606.jpg]: http://img6.ph.126.net/o-c-37O06UHoBZOJp7-Rgw==/6598233049958926606.jpg
[6597256683635004976.jpg]: http://img6.ph.126.net/7amVYTWyx9QdyyaIo0Wncw==/6597256683635004976.jpg
[6598109904656620617.jpg]: http://img7.ph.126.net/PgcvO0TSQ-uJsdKkUvgOMg==/6598109904656620617.jpg
[6597239091448951752.jpg]: http://img2.ph.126.net/CqrrRevNfg5nMLd5NgB3Ug==/6597239091448951752.jpg
[6597606328331305237.jpg]: http://img7.ph.126.net/VhLfhBUcobgysogiycZhcA==/6597606328331305237.jpg
[6598112103679876360.jpg]: http://img0.ph.126.net/vXT8fokSk3aRA-gzfrnmRA==/6598112103679876360.jpg
[6597909793540582556.jpg]: http://img6.ph.126.net/u80_Q2cT77hgBuw32M7BjA==/6597909793540582556.jpg
[6598178074377541927.jpg]: http://img7.ph.126.net/DuDpu2YnR1f6B0krSzmi1A==/6598178074377541927.jpg
[6598162681214749004.jpg]: http://img4.ph.126.net/3LA8C9byOo16Tqb4xRgkww==/6598162681214749004.jpg
[6598208860703119965.jpg]: http://img5.ph.126.net/HIL-fUNxt0VWf5cju00wjw==/6598208860703119965.jpg
[6597796543842923267.jpg]: http://img7.ph.126.net/6_bBIlszXswan3z2ZEBv1w==/6597796543842923267.jpg
[6598089013935688457.jpg]: http://img7.ph.126.net/cNE4mw7yCf0Ytjbr9p3YZw==/6598089013935688457.jpg