oracle学习笔记 共享SQL减少硬解析

oracle学习笔记

共享SQL减少硬解析

上节课讲了硬解析多了会消耗cpu资源，容易产生4031错误
这是我们不希望出现的

如何去减少硬解析呢
一个最好的办法，最常用的最有效的办法就是让sql共享

一）sql语句完全相同
执行一百个sql语句
如果有99个sql语句都是一样的话
这里这个相同的语句就发生了98次软解析而只有1次硬解析

共享sql就是让sql完全相同

librarycache里面有好多链
链上挂了好多chunk，
chunk里面有sql和sql执行计划

一个sql语句进入到数据库，
要对sql语句进行解析
首先看这个sql语句有没有被解析过
要做下面的工作：
sql语句转为ASCII码值
然后进行hash运算，得到一个hash值，
再进行一次运算，判断sql语句在哪个链上或者应该挂在哪个链上。
然后找到相应的链，
拿着sql语句的hash值，
在链上进行遍历和该链上的每一个chunk进行比较。
找到相同的则此语句被解析过，
若整条链从头到尾都找不到相同的，则此语句没有被解析过。
查找过程比较的是hash值
hash值是从ascii码值来的
scii码值不一样，hash值就不一样
hash值不一样我们认为两个sql不一样就不共享

ascii码值是根据sql语句里面的字母计算出的ascii码值
学过计算机的都知道
每一个字母对应ascii码值
所以这个地方强调一下
两个sql语句要共享必须相同
里面的每一个字母都必须相同
才能说sql语句相同，才能实现共享

如三个sql语句

select /*hello*/ count(*) from t1 where OBJECT_ID=1;

select /*hello*/ count(*) from t1 where OBJECT_ID=2;

select /*hello*/ count(*)  from t1 where OBJECT_ID=1;

第一个sql语句和第二个sql语句不一样
因为有两个字母1 和 2 不一样
不能共享。

第一个和第三个也不能共享
第三个语句from关键字前面比第一个语句多了一个空格
因为空格也是一个字符
就是说第三个语句比第一个多了一个字符，就是多了一个ascii码值
显然又不一样。

sql要共享的话，语句必须写的完全一模一样，
多空格、多回车、大小写不一样、里面的数值不一样都不行
都不能实现共享。

二）判断语句有没有共享

后面的一些举例结果较长，
为了阅读方便需为sql输出设置较大的页长和较宽的页宽
SQL> set pagesize 1000
SQL> set linesize 100

怎么来判断他们不能共享呢
下面执行一下三个语句
SQL> select /*hello*/ count(*) from t1 where OBJECT_ID=1;


  COUNT(*)
----------
         0


SQL> select /*hello*/ count(*) from t1 where OBJECT_ID=2;


  COUNT(*)
----------
         1


SQL> select /*hello*/ count(*)  from t1 where OBJECT_ID=1;


  COUNT(*)
----------
         0


再查一个数据
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> select sql_id,sql_text,executions from v$sql where sql_text like '%hello%';


SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
EXECUTIONS
----------
1vx8fabpy9dwv
select sql_id,sql_text,executions from v$sql where sql_text like '%hello%'
         1


4zj8791gxaafp
select /*hello*/ count(*)  from t1 where OBJECT_ID=1
         1


gwunm630jqjrr
select /*hello*/ count(*) from t1 where OBJECT_ID=2
         1


dkxtdpmw5ztxm
select /*hello*/ count(*) from t1 where OBJECT_ID=1
         1


！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！


结果中都是下面的形式：
4zj8791gxaafp
select /*hello*/ count(*)  from t1 where OBJECT_ID=1
         1

select /*hello*/ count(*)  from t1 where OBJECT_ID=1
为执行的sql语句
4zj8791gxaafp
为此sql语句的编码
1
为此sql语句的执行次数

一个sql语句进入后我们对sql语句进行解析时
我们首先给sql语句分配一个sql ID即编码

我们找到刚才执行的三条sql语句对应的三个id
分别是
dkxtdpmw5ztxm
gwunm630jqjrr
4zj8791gxaafp
这是本人试验的结果，和老师得到的结果一样。

给三个sql语句分配三个ID，
是因为oracle认为这三个sql语句不一样
它们的执行次数也都为1，没有共享。

sql语句要共享的话必须完全相同。
空格、大小写、数值不一样、回车等等不同
统统认为是sql语句不一样。
不一样也就不能共享、不能共享就会发生硬解析。

三）使用绑定变量

为了共享sql减少硬解析
1、统一书写风格 
包括空格、大小写、回车 
要统一书写风格会使得一定数量的sql语句能够共享
2、前两个sql语句 
一个OBJECT_ID=1
一个OBJECT_ID=2
不管怎么统一风格都不一样
针对这种情况
我们使用绑定变量

举一个绑定变量的例子：
declare  v_sql varchar2(50);
begin  for i in 1..10000 loop
v_sql := 'insert /*hello*/ into test1(id) values (:1)';  
execute immediate v_sql using i;
end loop;  
commit;  
end; 

在例子中有一个sql语句
insert /*hello*/ into test(id) values (:1)

/*hello*/为注释
实际执行时没有任何作用，但可以起到标记这条sql语句的作用。
不过作为整句的一部分也会使语句ID发生变化

values (:1)部分用了:1
是用的占位符
执行时要用实值替换，和变量类似但又不同
这里的:1改写成:i
整体效果是一样的都是占位符

:1部分执行时如果使用变量i的话
需要这样改两句
v_sql := 'insert /*hello*/ into test1(id) values (' || i ||')';
execute immediate v_sql;
执行整个语句块的时候
使用变量这条sql语句会执行
insert /*hello*/ into test(id) values (1)
然后
insert /*hello*/ into test(id) values (2)
以此类推
values i 部分就会在每次语句单独执行时使用一个常量
每次执行就会不一样。
就会出现大量的硬解析。

用:1时是占位符相当于用了变量n1,n2......
执行
execute immediate v_sql using i;
时
i为1时将1传给占位符但执行时是执行的
insert /*hello*/ into test(id) values (:1)
为2时仍是执行的
insert /*hello*/ into test(id) values (:1)
这样每次执行的语句就会相同

我们执行看一下

先清一下内存
SQL> alter system flush shared_pool;


System altered.

本课程前面的部分并没有创建test表，
这里要先建立一个test表
SQL> create table test1(id number,name varchar2(8));


Table created.

再执行上面的plsql语句块
老师提供的语句中要修改10000句太长我改为了100并不影响效果

SQL> declare  v_sql varchar2(50);
begin  for i in 1..100 loop
v_sql := 'insert /*hello*/ into test1(id) values (:1)';
execute immediate v_sql using i;
end loop;
commit;
end;   2    3    4    5    6    7
  8  /


PL/SQL procedure successfully completed.


plsql语句块在sqlplus命令行模式下运行方法
1、需要在命令块后在新行输入"/"来终止和运行plsql块命令
2、命令后新行输入"."可以结束命令，
   这时不会马上执行
   要紧接着在后续的SQL>后输入run命令
以上面的命令块为例：
SQL> declare  v_sql varchar2(50);
begin  for i in 1..100 loop
v_sql := 'insert /*hello*/ into test1(id) values (:1)';
execute immediate v_sql using i;
end loop;
commit;
end;  2    3    4    5    6    7
  8  .
SQL> run
  1  declare  v_sql varchar2(50);
  2  begin  for i in 1..100 loop
  3  v_sql := 'insert /*hello*/ into test1(id) values (:1)';
  4  execute immediate v_sql using i;
  5  end loop;
  6  commit;
  7* end;


PL/SQL procedure successfully completed.

执行了语句块后查询
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> select sql_id,sql_text,executions from v$sql where sql_text like '%hello%';


SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
EXECUTIONS
----------
4t6sqmhr6ctwg
declare  v_sql varchar2(50); begin  for i in 1..100 loop v_sql := 'insert /*hello*/ into test1(id) v
alues (:1)'; execute immediate v_sql using i; end loop; commit; end;
         1


1vx8fabpy9dwv
select sql_id,sql_text,executions from v$sql where sql_text like '%hello%'
         1


3z92h5wnqdxs0
insert /*hello*/ into test1(id) values (:1)
       100


！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

结果里面有
3z92h5wnqdxs0
insert /*hello*/ into test1(id) values (:1)
       100
oracle执行时把:1替换为实际值
它被执行了100次
通过这个例子看
insert /*hello*/ into test1(id) values (:1)
这条语句实现了绑定变量了，进一步实现了共享sql

这是oracle非常经典的一种使用绑定变量来实现共享sql的一种方法。
在开发时要掌握。

sql语句分为动态部分和静态部分

如：
select /*hello*/ count(*) from t1 where OBJECT_ID=1;语句
OBJECT_ID为列名
t1 为表名
where等为关键字
其中
select /*hello*/ count(*) from t1 where OBJECT_ID=
为静态部分
数值1
为动态部分

静态部分内容是有限的
动态部分字面值可以无限制的多

假设这个业务是银行的查询业务
一万个人来查，除了字面值以外前面的值都一样
不常改变的是静态部分，字面值部分叫动态部分。
我们要共享sql，
我们在动态部分尽量的使用绑定变量。
进而大大的减少了我们sql语句的数量，
大量的减少了硬解析


四）cursor_sharing参数
oracle还有另外一个参数
cursor_sharing
如果由于各种原因未能在开发初期使用绑定变量
为了减少硬解析
退而求其次的方法是设置cursor_sharing

SQL> show parameter cursor


NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           exact
cursor_space_for_time                boolean                          FALSE
open_cursors                         integer                          300
session_cached_cursors               integer                          150


目前cursor_sharing 
的值是
EXACT

介绍一下在sql命令行下修改某个参数的值方法
如：
SQL> alter system set session_cached_cursors=150 scope=both;
alter system set session_cached_cursors=150 scope=both
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

错误显示这个参数是静态参数，不能使用scope=both参数修改
可以使用：
SQL> alter system set session_cached_cursors=150 scope=spfile;


System altered.

在这里只是告诉大家，oracle里面有很多参数，参数是可以修改的。
而某些参数如session_cached_cursors参数
只能静态改，改完以后数据库重启以后才能有效。
有的参数可以动态改，改完马上生效。

我们要改一个参数要了解一个参数
oracle官方文档里面有，写的很清晰。

oracle的参数内容在官方文档的Reference部分
然后在Initialization Parameters中可以找到
CURSOR_SHARING
点击后得到详细说明

可看到这个参数有三个值可取
SIMILAR | EXACT | FORCE
并且每个值什么意思也讲的很清晰。

FORCE


Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.


SIMILAR


Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.


EXACT


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

翻译过来的意思就是：
     1)、EXACT(精确的)：exact值是Oracle推荐的，也是默认的，
         它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。


     2)、SIMILAR(相似的)：similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时，
         才会被重新分析，否则将重用SQL。


     3)、FORCE(强制)：force是在任何情况下，无条件重用SQL。

修改cursor_sharing方法
SQL> alter system set cursor_sharing='force';


System altered.
然后看一下
SQL> show parameter cursor


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      force
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     20


修改以后对简单的sql语句有效果

修改前cursor_sharing的值为默认EXACT
看看效果
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> insert /*hello*/ into test1(id) values (1111);


1 row created.


SQL> insert /*hello*/ into test1(id) values (2222);


1 row created.


SQL> insert /*hello*/ into test1(id) values (3333);


1 row created.


SQL> select sql_text,sql_id,executions from v$sql where sql_text like '%hello%';


SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID        EXECUTIONS
------------- ----------
insert /*hello*/ into test1(id) values (1111)
34wszcsuxhxww          1


select sql_text,sql_id,executions from v$sql where sql_text like '%hello%'
5tmdtcpq0p7dr          1


insert /*hello*/ into test1(id) values (2222)
9tgjs7a8w2421          1


insert /*hello*/ into test1(id) values (3333)
8gctdvbahgrv3          1
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

将cursor_sharing修改为force后
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> alter system set cursor_sharing='force';


System altered.


SQL> alter system flush shared_pool;


System altered.


SQL> insert /*hello*/ into test1(id) values (1111);


1 row created.


SQL> insert /*hello*/ into test1(id) values (2222);


1 row created.


SQL> insert /*hello*/ into test1(id) values (3333);


1 row created.


SQL> select sql_text,sql_id,executions from v$sql where sql_text like '%hello%';


SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID        EXECUTIONS
------------- ----------
insert /*hello*/ into test1(id) values (:"SYS_B_0")
96g55cvg0b627          3
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
可以看出 values()中的值系统自动替换为了:"SYS_B_0" 
:"SYS_B_0" 是oracle系统自动起的名字在这里是占位符类型
而且EXECUTIONS的次数为3
说明此语句被共享了。

对cursor_sharing参数修改使用的时间长了你会发现它经常出各种各样的问题
如cursor_sharing='force'
只对简单的语句有效，而稍长一点的语句就会没有反应。
如执行
begin  for i in 1..10000 loop
execute immediate 'insert /*hello*/ into test1(id) values (' || i ||')';
end loop;
commit;
end;
就不会共享i变量

在官方文档中有这么一句话：
Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE. 
说明这个参数还是有一定使用范围的。
在复杂查询和DSS(decision support system)决策支持系统环境不建议使用。

而且从网上反应的情况看
cursor_sharing参数的设置是oracle很大的一个报错点
总之这个地方非常的不好用
而且由它引起的报错类型很多，
很多人士包括老师都建议尽量不要使用此参数而是在源码开发时共享变量

原因是oracle使用计算机在语句中自动找到正确的谓词并使语句正确共享是很困难的事，特别是长语句。

网上也有一些解决方法要使force生效大多要使用到flush shared_pool
这在生产环境非常的不且实际
它删除了很多原有的执行计划反而造成了大量的硬解析

最好的办法是由程序员在开发程序时绑定变量。

五）找出sql语句的共享状态

如何找出没有共享的sql语句呢

语句
select SQL_FULLTEXT from v$sql where EXECUTIONS=1 and sql_text like '%from t%';
查执行次数等于一的语句
说明这个sql语句执行过，但执行次数等于一
这个时候还不能完全说明这个sql语句就没有共享
有可能这个sql语句就执行一次

我们可以使用
select SQL_FULLTEXT from v$sql where EXECUTIONS=1 order by sql_text;
它是将执行次数等于一次的sql语句列出来，同时按照sql语句去排序
如果有很多相邻的语句非常相似而且都只执行了一次那么这些语句没有共享

select SQL_FULLTEXT,EXECUTIONS from v$sql where EXECUTIONS>1 order by sql_text;
可以找执行了2次以上的sql语句，也就是已经共享的语句

六）例子中语句块变量的使用

首先删除test表中的所有内容
SQL> delete from test1;
再清内存
SQL> alter system flush shared_pool;
以便查看语句的结果

不使用绑定变量而直接使用变量执行
SQL> declare  v_sql varchar2(50);
begin  for i in 1..100 loop
v_sql := 'insert /*hello*/ into test1(id) values (' || i ||')';
execute immediate v_sql;
end loop;
commit;
end;  2    3    4    5    6    7
  8  /


PL/SQL procedure successfully completed.

或者
SQL> begin  for i in 1..100 loop
execute immediate 'insert /*hello*/ into test1(id) values (' || i ||')';
end loop;
commit;
end;  2    3    4    5
  6  /


PL/SQL procedure successfully completed.
都可以

select sql_text,sql_id,executions from v$sql where sql_text like '%hello%';

结果中都是
SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID        EXECUTIONS
------------- ----------
insert /*hello*/ into test1(id) values (86)
c5j1z1yujs0zg          1


insert /*hello*/ into test1(id) values (99)
d3qwcqfbjn1bk          1


insert /*hello*/ into test1(id) values (67)
这样的结果。
说明：
insert /*hello*/ into test1(id) values (i)
每个i不同，语句各执行一次
即语句没有共享。

如果使用下面的方法
SQL> begin  for i in 1..100 loop
insert /*hello*/ into test1(id) values (i);
end loop;
commit;
end;  2    3    4    5
  6  /


PL/SQL procedure successfully completed.


SQL> select sql_text,sql_id,executions from v$sql where sql_text like '%hello%';


SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_ID        EXECUTIONS
------------- ----------
select sql_text,sql_id,executions from v$sql where sql_text like '%hello%'
5tmdtcpq0p7dr          1


begin  for i in 1..100 loop insert /*hello*/ into test1(id) values (i); end loop; commit; end;
0ba439q9j9f54          1

虽然没有绑定变量
但是整个plsql语句作为整体只执行了一次。
并没有
insert /*hello*/ into test1(id) values (i);语句的单独执行
和共享变量效果相似

如果把 :1改为:i
SQL> declare  v_sql varchar2(50);
begin  for i in 1..100 loop
v_sql := 'insert /*hello*/ into test1(id) values (:i)';
execute immediate v_sql using i;
end loop;
commit;
end;  2    3    4    5    6    7
  8  /


PL/SQL procedure successfully completed.


SQL> select sql_id,sql_text,executions from v$sql where sql_text like '%hello%';


SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
EXECUTIONS
----------
1vx8fabpy9dwv
select sql_id,sql_text,executions from v$sql where sql_text like '%hello%'
         1


ck6uvahuukch4
declare  v_sql varchar2(50); begin  for i in 1..100 loop v_sql := 'insert /*hello*/ into test1(id) v
alues (:i)'; execute immediate v_sql using i; end loop; commit; end;
         1


dtc5n31qzugsh
insert /*hello*/ into test1(id) values (:i)
       100
:i在这里仍是占位符，不是变量
上面是plsql语句块中values(:1)位置使用变量和占位符的几种办法。


2016年8月27日
文字：韵筝