炼数成金 门户 大数据 Oracle 查看内容

基于ORACLE SQL优化之绑定变量(1)

2017-3-15 18:00| 发布者: 炼数成金_小数| 查看: 11946| 评论: 0|原作者: Silence 陈喜强|来自: DBA求职布道者

摘要: 众所周知,绑定变量窥探是oracle 10g版本的一个比较厉害的新角色,它的出现加强了oracle在SQL语句性能上的提升,尤其对于批量sql语句和硬解析的环境有一定作用,但是同样也带来了一些麻烦。今天开始来了解一下绑定变 ...

SQL 测试 Hadoop Oracle PL/SQL

众所周知,绑定变量窥探是oracle 10g版本的一个比较厉害的新角色,它的出现加强了oracle在SQL语句性能上的提升,尤其对于批量sql语句和硬解析的环境有一定作用,但是同样也带来了一些麻烦。今天开始来了解一下绑定变量的世界。

对于使用了绑定变量的目标SQL而言,Oracle可以选择如下两种方法来决定其执行计划。

a、使用绑定变量窥探。
b、如果不使用绑定变量窥探,则对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率(例如5%)。

绑定变量窥探(bind peeking)受到隐含参数_optim_peek_user_binds的控制,_optim_peek_user_binds默认值是true,表示默认是被启用的。
绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析树和执行计划的特性一直饱受 诟病,这种情况一直到Oracle 11g中引入自适应游标共享后才有所缓解,因为它可能使CBO在某些情况下对应绑定变量的某些具体输入值所选择的执行计划并不是目标SQL在当前情形下的最优执行计划,而且它可能会带来目标SQL执行计划的突然改变,进而直接影响应用系统的性能。

如下进行绑定变量窥探的实例:
创建一张测试表t1,并且在object_id上创建索引,并且可知t1表的选择性很好,收集表的统计信息
SQL> create table t1 as select * from dba_objects;
 
表已创建。
 
SQL> create index idx_t1 on t1(object_id);
 
索引已创建。
SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1',
estimate_percent=>100,cascade=>true,no_invalidate=>false,
method_opt=>'FOR ALL COLUMNS SIZE 1');
 
PL/SQL 过程已成功完成。
然后执行如下两条SQL,都进行了硬解析,Oracle分别生成一个parent cursor和child cursor,因为并未使用绑定变量
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';
 
SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS
----------------------------------------------------------------- --------------- ------------- ----------
select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1
select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1

从查询结果看,第一条SQL执行走的idx_t1的索引范围扫描:

第二条SQL执行走的是idx_t1的索引快速全扫描:

然后使用x和y绑定变量来替换上述两条SQL的等价形式,分别赋予x和y值999和1000;
SQL> var x number;
SQL> var y number;
SQL> exec :x := 999;
 
PL/SQL 过程已成功完成。
 
SQL> exec :y := 1000;
 
PL/SQL 过程已成功完成。
然后执行如下SQL,就会先和第一条SQL执行结果一致;并且由于第一次执行,所以依然也是硬解析:

SQL> select count(*) from t1 where object_id between :x and :y;
 
  COUNT(*)
----------
         2
 
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';
 
SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS
----------------------------------------------------------------- --------------- ------------- ----------
select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1
select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1
select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531               1          1

其执行计划走的也是索引的范围扫描,并且使用了绑定变量的窥探

然后保持x不变,将y值修改为60000;再次执行上述SQL,查看执行计划发现,version_count并未改变,execution变成了2,说明Oracle在第二次执行SQL用的是软解析:
SQL> exec :y := 60000;
 
PL/SQL 过程已成功完成。
 
SQL> select count(*) from t1 where object_id between :x and :y;
 
  COUNT(*)
----------
     58577
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';
 
SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS
----------------------------------------------------------------- --------------- ------------- ----------
select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1
select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1
select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531               1          2

上述实验证明Oracle这里依然沿用SQL之前硬解析时产生的解析树和执行计划,不再重复执行窥探动作。
然后我们让上述使用绑定变量的SQL再次执行时选择有索引快速全扫描---实验的方式就是让SQL使用硬解析,一旦存在硬解析,那么就会再一次的执行窥探操作。而执行硬解析的方式最常用的就是DDL操作,这时候库缓存的所有SQL文本包含的shared cursor会标记为失效,其解析树和执行计划不会被再次使用,而DDL中最常用的就是comment操作,添加注释信息对数据库环境的影响最小,如下进行测试:
SQL> comment on table t1 is 'test table of bind peeking';
 
注释已创建。
 
SQL> select count(*) from t1 where object_id between :x and :y;
 
  COUNT(*)
----------
     58577
然后再次查看上述SQL的version_count变为1,executions也变为1,说明真是执行的是硬解析操作,因为原先的cursor全部invalid。
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';
 
SQL_TEXT                                                          SQL_ID          VERSION_COUNT EXECUTIONS
----------------------------------------------------------------- --------------- ------------- ----------
select count(*) from t1 where object_id  between 999 and 1000     5y9rdffcnchk6               1          1
select count(*) from t1 where object_id between 999 and 60000     b8xxw70vja3tn               1          1
select count(*) from t1 where object_id between :x and :y         9dhu3xk2zu531               1          1

查看执行计划,确实发现走了索引快速全扫描,并且窥探动作沿用了999和60000的值。

从上述执行计划看出cardinality的值在使用idx_t1索引范围扫描的值为3,而索引快速全扫描的值为58185,如下解释这两个值如何计算出来
对于目标SQL而言,其where条件的selectivity和cardinality计算公式如下:
Cardinality = NUM_ROWS * Selectivity
Selectivity = ((y - x)/(HIGH_VALUE - LOW_VALUE)+2/NUM_DISTINCT) * Null_Adjust
Null_Adjust = (NUM_ROWS - NUM_NULLS)/NUM_ROWS

注释:
1)、上述计算公式适用于启用了绑定变量窥探且where条件为“目标列between x and y”的Selectivity和Cardinality的计算,且x和y均处于目标列的low_value和high_value之间。
2)、num_rows表示目标列所在表的记录数。
3)、low_value和high_value分别表示目标列的最小值和较大值
4)、num_distinct表示目标列的distinct值的数量。
5)、num_nulls表示目标列的null值的数量。
如下查询结果看到num_rows值为91944
SQL> select table_name,num_rows from dba_tables where owner='TEST' 
 and table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  91944
如下查询结果看,查出对用公式的low_value,high_value,num_distinct,num_nulls
SQL> col low_value for a20
SQL> col high_value for a20
SQL> select low_value,high_value,num_distinct,num_nulls from dba_tab_col_statistics where owner='TEST' and table_name='T1' and column_name='OBJECT_ID';
 
LOW_VALUE            HIGH_VALUE           NUM_DISTINCT  NUM_NULLS
-------------------- -------------------- ------------ ----------
C103                 C30A2126                    91943          1
如下查询看到,high_value的C30A2126实际上就等于93237,low_value值C103值实际上就等于2;
SQL> col dump(max(object_id),16) for a30
SQL> select max(object_id),dump(max(object_id),16) from t1;
 
MAX(OBJECT_ID) DUMP(MAX(OBJECT_ID),16)
-------------- ------------------------------
         93237 Typ=2 Len=4: c3,a,21,26
SQL> col dump(min(object_id),16) for a30
SQL> select min(object_id),dump(min(object_id),16) from t1;
 
MIN(OBJECT_ID) DUMP(MIN(OBJECT_ID),16)
-------------- ------------------------------
             2 Typ=2 Len=2: c1,3
然后将查询出来的num_rows,low_value,high_value,num_distinct,num_nulls和x,y带入公式计算,当x,y为999,1000时,计算结果为3,而当x,y值为999,60000时计算结果为58185,这就算计算Cardinality结果的原因。
SQL> select round(91944*(((1000-999)/(93237-2)+2/91943)*((91944-2)/91944))) 
 from dual;
 
ROUND(91944*(((1000-999)/(93237-2)+2/91943)*((91944-2)/91944)))
---------------------------------------------------------------
                                                              3
SQL> select round(91944*(((60000-999)/(93237-2)+2/91943)*((91944-2)/91944))) 
 from dual;
 
ROUND(91944*(((60000-999)/(93237-2)+2/91943)*((91944-2)/91944)))
----------------------------------------------------------------
                                                           58185
==================================
not the end !!!

欢迎加入本站公开兴趣群
软件开发技术群
兴趣范围包括:Java,C/C++,Python,PHP,Ruby,shell等各种语言开发经验交流,各种框架使用,外包项目机会,学习、培训、跳槽等交流
QQ群:26931708

Hadoop源代码研究群
兴趣范围包括:Hadoop源代码解读,改进,优化,分布式系统场景定制,与Hadoop有关的各种开源项目,总之就是玩转Hadoop
QQ群:288410967 

鲜花

握手

雷人

路过

鸡蛋

相关阅读

最新评论

热门频道

  • 大数据
  • 商业智能
  • 量化投资
  • 科学探索
  • 创业

即将开课

 

GMT+8, 2018-2-22 07:11 , Processed in 0.159513 second(s), 25 queries .