共享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次
![](http://img4.ph.126.net/QoCcMkky7uAhTs_-QXsjog==/6597853718447555244.jpg)
注意第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个空格:
我们在执行一次:
![](http://img6.ph.126.net/m-obuhgZ66389qIKGyb9-A==/6597744866796399186.jpg)
可发现,v$sql中并没有多出一个sql_id, 只不过第一条的执行次数变成了2, 也就是说第4次执行的那条跟第一条的sql_id是一样的,因为它们的字符完全一样.
那么第一条和第二条sql能不能共享呢, 因为条件'EMP' 跟 'DEPT' 完全不同啊, 貌似是个硬伤.
方法是有的:
就是用1个相同名字的变量, 分别赋值'EMP'和'DEPT'各执行一次, 因为变量名相同.
做个例子:
写1个 sql 脚本如下:
![](http://img8.ph.126.net/j01X2WKzNs-SiyFxTKmvAA==/6597818534075471408.jpg)
上面的脚本会插入11行数据进入表test, 虽然每一行的值都是不同, 但是我们可以用变量 i来装载它.
然后使用 execute 来执行sql语句.
下面执行这个脚本:
![](http://img0.ph.126.net/jkL4FtY24xaBVEchdlOYgg==/6597709682424310460.jpg)
在查看v$sql 视图:
![](http://img5.ph.126.net/Rm8-lE5RJHBesiY8-gvk5g==/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几次..
![](http://img0.ph.126.net/w2O4tvuFUKFndXI8rJ8zWQ==/6597732772168500120.jpg)
然后执行这个修改后的脚本:
![](http://img5.ph.126.net/TpU1gLJXRjpQzxIRuqtqNA==/6598244045075202995.jpg)
接下来用
select sql_id, sql_fulltext,executions from v$sql where sql_text like '%insert%' and executions = 1 order by sql_text;
查看 未被共享的语句.
![](http://img7.ph.126.net/aZjM74ghrCCNXJCCBTF9lg==/6597209404634992187.jpg)
接下来去查看导出来的log文件, 通常就可以分析出哪些sql语句未被共享而被执行大量硬解析了..
![](http://img9.ph.126.net/-3Lr7-K-ijODH3IgWbBCbA==/6597732772168500279.jpg)
这是1个很经典的查找未被共享sql语句的方法, 用于数据库优化.
===============================================分割线===============================
4. 设置cursor_sharing参数
找到未被共享的sql语句后怎么做呢, 一般会交给程序员,让他们修改.
但是做过码农的都知道, 程序里的代码有些时候不是那么好改了,有时真的牵一发而动全身啊..
那改不了怎么办.
其实还有1个办法是设置oracle的一个参数: cursor_sharing
我们先查看当前默认的参数值是什么:
![](http://img8.ph.126.net/dTwpfidul6aC5p2uWF7LxQ==/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
![](http://img6.ph.126.net/XuYtt1ddM5pH40CKwpxnOQ==/6598100009051967536.jpg)
跟住清空shared pool缓存:
![](http://img6.ph.126.net/o-c-37O06UHoBZOJp7-Rgw==/6598233049958926606.jpg)
然后我们再执行一次上面执行过的那个脚本:
![](http://img6.ph.126.net/7amVYTWyx9QdyyaIo0Wncw==/6597256683635004976.jpg)
执行:
![](http://img7.ph.126.net/PgcvO0TSQ-uJsdKkUvgOMg==/6598109904656620617.jpg)
我们再去查看v$sql..
但是由这个测试来讲是无效果的... 而老相在视频里没提供测试..
可以见到 sql语句并没有共享, 依然是没一条insert 语句分配1个sql id.
![](http://img2.ph.126.net/CqrrRevNfg5nMLd5NgB3Ug==/6597239091448951752.jpg)
于是我私人再做多个测试,睇下这个参数会不会影响硬解析的数量.
首先将cursor_sharing设置成默认的'EXACT', 并清空shared pool缓存.
![](http://img7.ph.126.net/VhLfhBUcobgysogiycZhcA==/6597606328331305237.jpg)
接着查看当前硬解析和软解析数量:
![](http://img0.ph.126.net/vXT8fokSk3aRA-gzfrnmRA==/6598112103679876360.jpg)
可以记下:
当前已经发生过的解析数量总数为: 611230
其中硬解析数量为: 35774
这时我们执行一次insert脚本:
![](http://img6.ph.126.net/u80_Q2cT77hgBuw32M7BjA==/6597909793540582556.jpg)
再次查看解析数量:
![](http://img7.ph.126.net/DuDpu2YnR1f6B0krSzmi1A==/6598178074377541927.jpg)
其中解析数量总数增加了 613301 - 611230 = 2071
而硬解析数量增加了 36778 - 35774 = 1004
可以看出硬解析的数量是相当接近 脚本中循环执行的insert 语句数量的!
这是我们将cursor_sharing 设置为Force
并且清空shared pool
![](http://img4.ph.126.net/3LA8C9byOo16Tqb4xRgkww==/6598162681214749004.jpg)
再查看当前的解析数量:
![](http://img5.ph.126.net/HIL-fUNxt0VWf5cju00wjw==/6598208860703119965.jpg)
其中硬解析数量为: 37432
这时我们再次执行一次insert脚本:
![](http://img7.ph.126.net/6_bBIlszXswan3z2ZEBv1w==/6597796543842923267.jpg)
再次查看解析数量:
![](http://img7.ph.126.net/cNE4mw7yCf0Ytjbr9p3YZw==/6598089013935688457.jpg)
其中解析数量总数增加了 618008 - 616473 = 1535
而硬解析数量增加了 38511 - 37432 = 1079
尼玛硬解析数量反而增加了
我觉得原因是可能这个参数对我脚步那种sql 无作用, 至于到底会影响哪些sql共享, 以后再研究了...