最新使用动态SQL进行变量绑定 分析

hponiang

在这里和大家说一下,这种方法不行,因大数据量使用全表扫描的时候sys_context与直接变量绑定的差异很大,因为sys_context其实也相当于一个函数,从一个内存表中取数据
类似于

1. 
   
2.     SELECT * 　from dba_objects WHERE object_name = (SELECT object_name
   
3.           　from dba_objects WHERE object_name = 'UNDO$');
   

复制代码

这句SQL的执行的时间并不是简单的子查询的用时加上外层查询用时,上面耗时0.2,下面的耗时0.015

1. 
   
2.    
   
3.           SELECT *
   
4.           　from dba_objects WHERE object_name = 'UNDO$';
   
5. 
   

复制代码

我也不是很明白为什么会产生的这样的问题,如果有人明白请告诉我 一下

[ 本帖最后由 hponiang 于 2010-9-24 14:04 编辑 ]

sundog315

原帖由 hponiang 于 2010-9-21 16:32 发表
比如我使用一个存储过程A ,有五个参数,当参数为-1的时候代表参数不生效,当不是-1的时候要使用参数,你用动态SQl使用你那种方法怎么搞,要麻烦死了

对于一些不重要的，不影响执行计划的，可以用decode等函数来处理

当然，楼主的方法也是解决这个问题的很好的思路

sundog315

原帖由 hponiang 于 2010-9-24 14:03 发表
在这里和大家说一下,这种方法不行,因大数据量使用全表扫描的时候sys_context与直接变量绑定的差异很大,因为sys_context其实也相当于一个函数,从一个内存表中取数据
类似于

    SELECT * 　from dba_objects WHERE object_name = (SELECT object_name
          　from dba_objects WHERE object_name = 'UNDO$');

这句SQL的执行的时间并不是简单的子查询的用时加上外层查询用时,上面耗时0.2,下面的耗时0.015


   
          SELECT *
          　from dba_objects WHERE object_name = 'UNDO$';


我也不是很明白为什么会产生的这样的问题,如果有人明白请告诉我 一下

可能有影响，但影响不会很大

你的这两个例子，肯定差异很大。不要用dba_objects，ctas一个表，看看执行计划就清楚了。

hponiang

我使用其它的表也是有这个问题的,如果你们那里有数据量比较,并且选择性很好的列,你们可以按我说的测试一下,这个问题很令人费解

hponiang

我想了一个相当土的办法

1. 
   
2. nb_sql :='select * from customer where 1=1'
   
3. if v_id <> null then
   
4.   nb_sql:=nb_sql||' custid=:1';
   
5.   nb_param:= nb_param||','||to_char(v_id);
   
6. end if;
   
7. 再在后面把nb_param按逗号拆开成一个数组,根据数组的count
   
8. case count when
   
9. 1
   
10. then exec.... using param(1)
    
11. when 2
    
12. then exec.... using param(1),param(2);
    
13. 
    

复制代码

你不要说我雷,你有更好的办法么

hponiang

你有更好的办法么

hponiang

插,这个好冷啊

sundog315

原帖由 hponiang 于 2010-9-24 14:03 发表
在这里和大家说一下,这种方法不行,因大数据量使用全表扫描的时候sys_context与直接变量绑定的差异很大,因为sys_context其实也相当于一个函数,从一个内存表中取数据
类似于

    SELECT * 　from dba_objects WHERE object_name = (SELECT object_name
          　from dba_objects WHERE object_name = 'UNDO$');

这句SQL的执行的时间并不是简单的子查询的用时加上外层查询用时,上面耗时0.2,下面的耗时0.015


   
          SELECT *
          　from dba_objects WHERE object_name = 'UNDO$';


我也不是很明白为什么会产生的这样的问题,如果有人明白请告诉我 一下

既然已经全表扫描，那么对于一个表，是不大会有什么区别的

SUNDOG315>create table t (id number,text varchar2(4000));

表已创建。

SUNDOG315>insert into t select 1,object_name from dba_objects;

已创建55087行。

SUNDOG315>insert into t select 2,'aaa' from dual;

已创建 1 行。

SUNDOG315>commit;

提交完成。

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for
all columns',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>select count(*) from t where id=2;

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

SUNDOG315>select * from table(dbms_xplan.display_cursor());

SQL_ID  8tfp2xac71yd6, child number 0
-------------------------------------
select count(*) from t where id=2

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   102 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |   102   (0)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=2)

已选择19行。

SUNDOG315>create context test using test_p;

上下文已创建。

SUNDOG315>create or replace procedure test_p is
  2  begin
  3    dbms_session.set_context('TEST','ID',2);
  4  end;
  5  /

过程已创建。

SUNDOG315>exec test_p;

PL/SQL 过程已成功完成。

SUNDOG315>select sys_context('TEST','ID') from dual;

SYS_CONTEXT('TEST','ID')
--------------------------------------------------------------------------------

2

SUNDOG315>select count(*) from t where id=sys_context('test','id');

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

SUNDOG315>select * from table(dbms_xplan.display_cursor());

SQL_ID  69x691fhtckm0, child number 0
-------------------------------------
select count(*) from t where id=sys_context('test','id')

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   103 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 27544 | 82632 |   103   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=TO_NUMBER(SYS_CONTEXT('test','id')))


已选择19行。

SUNDOG315>set autot trace stat
SUNDOG315>select count(*) from t where id=2;


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        300  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SUNDOG315>select count(*) from t where id=sys_context('test','id');


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        300  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sundog315

但是，如果在ID列有索引，不绑定SQL和普通变量绑定SQL，由于bind peeking的存在，都会选择正确的执行计划。sys_context由于将值进行了封装，无法进行bind peeking，将会导致选择错误的执行计划

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 9月 29 08:43:56 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SUNDOG315>conn test/test
已连接。
SUNDOG315>create table t (id number,text varchar2(4000));

表已创建。

SUNDOG315>insert into t select 1,object_name from dba_objects;

已创建55085行。

SUNDOG315>insert into t select 2,'aaa' from dual;

已创建 1 行。

SUNDOG315>commit;

提交完成。

SUNDOG315>create index t_idx on t(id);

索引已创建。

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for
all columns',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>select id,count(*) from t group by id;

        ID   COUNT(*)
---------- ----------
         1      55085
         2          1

SUNDOG315>select count(*) from t where id=2;

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

SUNDOG315>select * from table(dbms_xplan.display_cursor());

SQL_ID  8tfp2xac71yd6, child number 0
-------------------------------------
select count(*) from t where id=2

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_IDX |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=2)

已选择19行。

--选择了正确的索引范围扫描

SUNDOG315>var fid number;
SUNDOG315>exec :fid := 2;

PL/SQL 过程已成功完成。


SUNDOG315>select count(*) from t where id=:fid;

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

SUNDOG315>select * from table(dbms_xplan.display_cursor());

SQL_ID  3u1847dua6k75, child number 0
-------------------------------------
select count(*) from t where id=:fid

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_IDX |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=:FID)

已选择19行。

--由于bind_peeking的存在，依然选择了正确的索引范围扫描

SUNDOG315>create context test using test_p;

上下文已创建。

SUNDOG315>create or replace procedure test_p is
  2  begin
  3    dbms_session.set_context('test','id',2);
  4  end;
  5  /

过程已创建。

SUNDOG315>exec test_p;

PL/SQL 过程已成功完成。

SUNDOG315>select sys_context('TEST','ID') from dual;

SYS_CONTEXT('TEST','ID')
--------------------------------------------------------------------------------

2

SUNDOG315>select count(*) from t where id=sys_context('test','id');

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

SUNDOG315>select * from table(dbms_xplan.display_cursor());

SQL_ID  69x691fhtckm0, child number 0
-------------------------------------
select count(*) from t where id=sys_context('test','id')

Plan hash value: 1058879072

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |       |    32 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_IDX | 27543 | 82629 |    32   (4)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=TO_NUMBER(SYS_CONTEXT('test','id')))

已选择19行。

--TO_NUMBER(SYS_CONTEXT('test','id'))无法peeking到正确的值，因此，选择了错误的执行计划

SUNDOG315>set autot trace stat
SUNDOG315>select count(*) from t where id=2;


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SUNDOG315>select count(*) from t where id=:fid;


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SUNDOG315>select count(*) from t where id=sys_context('test','id');


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        116  consistent gets                                 --逻辑读增大
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

目前，估计用dbms_session的人还是比较少，像这种问题，Oracle其实完全可以处理掉的。估计如果使用的人多，就会进行处理了。呵呵

hponiang

执行计划我也关注过程,二者完全是一样的,但我在plsql里执行的耗时很有差异
我发现如果列出列名二者的差异就会少好多

1. 
   
2. SELECT * 　from dba_objects WHERE object_name = (SELECT object_name
   
3.           　from dba_objects WHERE object_name =  'UNDO$');
   
4. 
   
5.    
   
6.           SELECT *
   
7.           　from dba_objects WHERE object_name = 'UNDO$';
   
8. 
   

复制代码

你就用这二句进行测试就可得知,如果你觉得这个视图太复杂,不便于分析你可以找一个你们使用中类似的表,要求 [object_name = 'UNDO$'](能唯一确定一条值且这列上没有索引)

[ 本帖最后由 hponiang 于 2010-9-29 10:54 编辑 ]