Oracle 课程九之绑定变量

课程目标

　　完成本课程的学习后，您应该能够：

　　•变量绑定的目的

　　•父子游标

　　•游标共享

　　•绑定窥探

　　•SQL语句处理流程

　　•硬解析、软解析、软软解析

　　•变量绑定的应用场景

 

1。游标

　　游标可以理解为SQL语句的一个句柄，也叫SQL语句的指针，游标指向一条SQL语句，oracle会话要执行一条SQL时，首先要打开游标。

1.1父子游标:

　　同样的SQL，因某些其它的差异，会产生另外的cursor。解析操作的结果是将一个父游标与子游标保存到库缓存中。很显然，将它们保存到共享内存的目的是为了重用它们，从而避免硬解析。

　　父游标 parent cursor ---第一条运行的SQL。
　　子游标 child cursor ---后续运行的SQL 。
　　父游标的关键信息是SQL语句的文本。
　　子游标的关键是执行计划和执行环境。

实验：

sqlplus test/test
drop table t_cursor purge;
create table t_cursor as select * from dba_objects;
alter system flush shared_pool;

select count(*) from t_cursor;
select count(*) from T_cursor;
col sql_text format a40;
select sql_id, sql_text, executions from v$sql
 where sql_text like '%select count(*) from%_cursor%'
   and sql_text not like '%sql_text%';
alter system flush shared_pool;
select sql_id, sql_text, executions from v$sql
 where sql_text like '%select count(*) from%_cursor%'
   and sql_text not like '%sql_text%';

select count(*) from t_cursor;
select count(*) from t_cursor;
select sql_id, sql_text, executions from v$sql
 where sql_text like '%select count(*) from%_cursor%'
   and sql_text not like '%sql_text%';
alter system flush shared_pool;

--父子游标
alter session set optimizer_mode = all_rows;
select count(*) from t_cursor;
select sql_id, sql_text,child_number,executions from v$sql
 where sql_text like '%select count(*) from%_cursor%'
   and sql_text not like '%sql_text%';
alter session set optimizer_mode = first_rows_10;
select count(*) from t_cursor;
select sql_id, sql_text,child_number,executions from v$sql
 where sql_text like '%select count(*) from%_cursor%'
   and sql_text not like '%sql_text%';

grant select any dictionary to scott;

sqlplus scott/tiger
drop table t_cursor purge;
create table t_cursor as select * from user_objects;
col sql_text format a40;
select count(*) from t_cursor;
select sql_id, sql_text,child_number, executions from v$sql
 where sql_text like '%select count(*) from%_cursor%'
   and sql_text not like '%sql_text%';

1.2V$SQL_SHARED_CURSOR,提供游标为什么不能共享的原因:

　　Desc V$SQL_SHARED_CURSOR:

　　字段意思参考帮助文档：Reference/V$SQL_SHARED_CURSOR

　　Select * from V$SQL_SHARED_CURSOR;

　　逻辑优化：这个阶段，通过应用各种不同的转换技巧，会生成语义上等同的新的SQL语句。

　　物理优化：首先会生成与每个逻辑优化产生的SQL语句有关的执行计划。接着，根据数据字典找到的统计信息或动态取样收集的统计信息，计算出一个与各个执行计划相关的开销。最后，拥有最低开销的执行计划会被选中。

1.3硬解析步骤

　　　　1.) 对SQL语句进行语法检查，看是否有语法错误。如果存在语法错误，则退出解析过程；
　　　　2.) 通过数据字典(row cache)，检查SQL语句中涉及的对象和列是否存在。如果不存在，则退出解析过程;
　　　　3.)检查SQL语句的用户是否对涉及到的对象是否有权限。没有则退出解析；
　　　　4.)通过优化器创建一个最优的执行计划。这个过程会根据数据字典中的对象的统计信息，来计算多个执行计划的cost，从而得到一个最优的执行计划。
　　　　　 这一步涉及到大量的数据运算，从而会消耗大量的CPU资源；(library cache最主要的目的就是通过软解析来减少这个步骤);

　　　　5.)将该游标所产生的执行计划，SQL文本等装载进library cache中的heap中。

1.4软解析、软软解析

　　软解析： 所谓软解析，就是因为相同文本的SQL语句存在于library cache中，所以本次SQL语句的解析就可以去掉硬解析中的多个步骤。从而节省大量的资源的　　耗费。
　　软软解析：所谓的软软解析，就是没有任何解析过程。当设置了session_cached_cursors参数时，当某个session第三次执行相同的SQL语句时，则会把该SQL语句的游标缓存到PGA中。这样，当该session在执行该SQL语句时，会直接从PGA中取出执行计划，从而跳过解析的所有步骤。

drop table test purge;
alter system flush shared_pool;
create table test as select * from dba_objects where 1<>1;
exec dbms_stats.gather_table_stats(user,'test');

select * from test where object_id=20;
select * from test where object_id=30;
select * from test where object_id=40;
select * from test where object_id=50;
var oid number;
exec :oid:=20;
select * from test where object_id=:oid;
exec :oid:=30;
select * from test where object_id=:oid;
exec :oid:=40;
select * from test where object_id=:oid;
exec :oid:=50;
select * from test where object_id=:oid;
begin
     for i in 1..4 loop
     execute immediate 'select * from test where object_id=:i' using i;
     end loop;
     end;
 /
 
select sql_text,s.PARSE_CALLS,loads,executions from v$sql s
    where sql_text like 'select * from test where object_id%'
    order by 1,2,3,4; 

 

2.绑定变量

2.1变量绑定的目的

　　变量绑定的目的是把硬解析变成软解析:

　　a.减少SQL硬解析的次数。
　　b.减少系统资源开销。
　　c.减少latch争用。

　　绑定变量也有冬天:bind peeking

　　bind peeking的由来：在执行含有绑定变量的查询语句时，完成解析和最优化操作之后才能对绑定变量进行绑定，这意味着在实现最优化操作时无法使用绑定变　　　　量列的统计信息。
　　为了解决这个问题，数据库使用了窥探技术，在第一次解析SQL时，按照窥探变量的值生成执行计划，以后这样的SQL都按照这个执行。
隐藏参数_optim_peek_user_binds=true则启用绑定变量窥探，否则CBO认为统计列是均匀的。
由绑定窥探你想到了什么?

 

drop table test purge;
create table test as select * from dba_objects;
create index ind_object_id on TEST (object_id);
exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
set autotrace traceonly
select count(object_name) from test where object_id < 10;
select count(object_name) from test where object_id < 1000000;
set autotrace off

alter session set statistics_level=all;
alter system flush shared_pool;
var ccc number;
exec :ccc:=10;
select count(object_name) from test where object_id < :ccc;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

exec :ccc:=1000000;
select count(object_name) from test where object_id < :ccc;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

alter system flush shared_pool;
exec :ccc:=1000000;
select count(object_name) from test where object_id < :ccc;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

exec :ccc:=10;
select count(object_name) from test where object_id < :ccc;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

 

2.2变量绑定的应用场景

　　OLTP环境应该使用绑定变量：

　　•操作的结果集小，虽然有些表数据量很大，但是往往操作的记录数可能很小。比如改部门，只需要在庞大的用户表里通过索引找到其中一行数据来做修改。即使每个用户的谓词条件不一样，执行计划也是一样的，也就是说，在执行计划几乎唯一的情况下，使用变量来代替谓词条件，是非常合适的。

　　•用户量非常大、执行频繁。

---OLTP环境模拟
drop table t purge;
create table t (name varchar2(20),department_id number(10));

begin
  for i in 1 .. 10000 loop
    insert into t values ('test'||i,i);
  end loop;
  commit;
end;
/
select * from t;
------------以上为创造实验环境--------------
---如果我们要将test1、test2两个用户转部门，那么产生的sql应该是以下两条内容
select department_id from t where name = 'test1' 
update t set department_id=1000 where name ='test1';
commit;

select department_id from t where name = 'test2' 
update t set department_id=1000 where name ='test2';
commit;
---如果使用绑定变量，那么sql应该是以下内容
select department_id from t where name = :x;
update t set department_id=1000 where name =:x;
---通过绑定变量，我们将两次硬解析变成了一次硬解析

---接下来我们看绑定变量与不绑定变量的性能区别
alter session set tracefile_identifier = 'Look_For_Me';
alter session set events '10046 trace name context forever,level 12';

begin                        ---不绑定
for x in 1..10000 loop
execute immediate 'select * from t where name ='||x;
end loop;
end;
/

begin                        ---绑定
for x in 1..10000 loop
execute immediate 'select * from t where name =:x' using x;
end loop;
end;
/

alter session set events '10046 trace name context off';

　　OLAP环境不应该使用绑定变量：

　　OLAP环境大多时候是报表处理，操作的结果集庞大，并且数据往往不均匀，在这种情况下，谓词条件不同，获取到的数据量也不一定相同，执行计划就不一定相同，这个时候，变量就不那么合适了

　　在OLAP系统中，根据不同谓词条件选择不同的执行计划是至关重要的，这时候，解析的代价和大查询的代价比较起来，sql解析消耗的资源是可以忽略不计的
用户量较少、执行次数少。

2.3游标共享(cursor_sharing)

　　cursor sharing用来告诉oracle什么情况下可以共享游标，即SQL重用。oracle默认cursor_sharing 是exact  指的是SQL语句必须绝对一样的情况下才能共享游标，否则作为新的SQL语句处理。
　　适合场景：使用exact 高效的前提是应用代码中使用了绑定变量，这也是oracle推荐的。

Drop table t purge;
show parameter cursor_sharing;
create table t as select * from dba_objects;
alter system flush shared_pool;
select /*+test_exact*/count(1) from t where object_id=100;
select /*+test_exact*/count(1) from t where object_id=200;
col sql_text format a80;
select sql_text from v$sql where sql_text like '%/*+test_exact*/%'
and  sql_text not like '%sql_text%';

alter system flush shared_pool;
var x number;
exec :x:=100;
select /*+test_exact*/count(1) from t where object_id=:x;
exec :x:=200;
select /*+test_exact*/count(1) from t where object_id=:x;
select sql_text from v$sql where sql_text like '%/*+test_exact*/%'
and  sql_text not like '%sql_text%';

　　cursor sharing=force,oracle将2条类似的SQL的谓词用一个变量代替，同时将它们看做同一条SQL语句处理
　　适用场景：在无法将应用的代码修改为绑定变量情况下，oracle提供的一种解决方法。
　　注意：游标共享特性有个不太好的名声，就是它不是很稳定，过去数年，大量与之相关的bug被发现和确认。

Drop table t purge;
show parameter cursor_sharing;
create table t as select * from dba_objects;
alter session set cursor_sharing=force;
alter system flush shared_pool;
select /*+test_force*/count(1) from t where object_id=100;
select /*+test_force*/count(1) from t where object_id=100;
select /*+test_force*/count(1) from t where object_id=200;
col sql_text format a80;
select sql_id,child_number,sql_text from v$sql where sql_text like '%/*+test_force*/%'
and  sql_text not like '%sql_text%'
And  sql_text not like '%EXPLAIN PLAN%';

　　cursor_sharing=similar ，exact和force折中的参数。当探测到谓词会导致执行计划的改变，就会重新解析，否则不会。
　　适用场景：在无法将应用的代码修改为绑定变量情况下，oracle提供的又一种解决方法，similar的好处是最大限度地避免CBO在绑定变量的情况下做出的错误的判断，但它的代价是分析的次数变大。

Drop table t purge;
show parameter cursor_sharing;
create table t as select * from dba_objects;
Create index ind_t_object_id on t(object_id) nologging;
exec dbms_stats.gather_table_stats(user,'t',cascade => true);
alter session set cursor_sharing=similar;
Set autotrace traceonly 
select /*+test_similar*/* from t where object_id<10;
select /*+test_similar*/* from t where object_id<100000;
Set autotrace off

alter system flush shared_pool;
Set autotrace traceonly
select /*+test_similar*/* from t where object_id<10;
select /*+test_similar*/* from t where object_id<100000;
Set autotrace off
col sql_text format a80;
select sql_id,child_number,sql_text from v$sql where sql_text like '%/*+test_similar*/%'
and  sql_text not like '%sql_text%'
And  sql_text not like '%EXPLAIN PLAN%';

alter system flush shared_pool;
Set autotrace traceonly
select /*+test_similar*/* from t where object_name='aaa';
select /*+test_similar*/* from t where object_name='bbb';
select /*+test_similar*/* from t where object_name='ccc';
select /*+test_similar*/* from t where object_name='ddd';
Set autotrace off
select sql_id,child_number,sql_text from v$sql where sql_text like '%/*+test_similar*/%'
and  sql_text not like '%sql_text%'
And  sql_text not like '%EXPLAIN PLAN%';