ITPub博客

cursor_sharing=force强制绑定变量不会把变量值默认当成varchar2类型的理解

原创 Oracle 作者:lusklusklusk 时间:2018-03-05 18:07:43 1 删除 编辑
cursor_sharing=force绑定变量后,执行计划显示解析的sql对应的变量值加了双引号,很像转换成了varchar2类型,但是实际上并非如此,因为如果转换成了varchar2类型,那字段是number类型,还怎么走索引呢?
select * from salary where user_id=1;
select * from salary where user_id=2;
两条语句时,把user_id的值转换为变量时,解析后的sql执行计划显示user_id=:"SYS_B_0",但是执行计划仍旧走了索引,说明变量值并非真正的转换成了varchar2类型


关于cursor_sharing=force绑定变量后,执行计划显示解析的sql对应的变量值加了双引号,盖老师的解释:理解成一个占位符,原来1,100占的长度不同,现在变量替换,内存分配的长度都不同,但是类型属性还在。Oracle会做校验,确保输入类型符合。


手工设置绑定变量时,变量值对应的字段是什么类型则变量值就是什么类型
比如id是number类型,但是使用绑定变量如select * from salary where id=:id,这时不管:id是啥值,比如是5,代入到执行计划,都是把它当成number。




SQL> alter system set cursor_sharing=force;

SQL> create user test identified by 123456;
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> conn test/123456
Connected.

SQL> create table test1 as select * from dba_objects;
Table created.

SQL> create table test2 as select * from dba_objects;
Table created.

SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL>  create index ind_objectid on test1(OBJECT_ID);
Index created.
SQL>  create index ind_objectid2 on test2(OBJECT_ID);
Index created.
SQL>  exec DBMS_STATS.GATHER_SCHEMA_STATS ('TEST');
PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;
System altered.

SQL> set autotrace traceonly

SQL> select * from test1 where OBJECT_ID=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    98 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    98 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
         46  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
       1611  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from test1 where OBJECT_ID=8994;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    98 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    98 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=8994)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1627  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off;
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%test1%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
63td7cwx0n43j            0 select * from test1 where OBJECT_ID=:"SYS_B_0"
7749tvrvns89y            0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where OBJECT_ID=8994
8utckadc0tfwf            0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where OBJECT_ID=2

SQL> select * from table(dbms_xplan.display_cursor('63td7cwx0n43j',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  63td7cwx0n43j, child number 0
-------------------------------------
select * from test1 where OBJECT_ID=:"SYS_B_0"
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1        |     1 |    98 |     2   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=:SYS_B_0)
19 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('7749tvrvns89y',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7749tvrvns89y, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where
OBJECT_ID=8994
NOTE: cannot fetch plan for SQL_ID: 7749tvrvns89y, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('8utckadc0tfwf',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8utckadc0tfwf, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test1 where
OBJECT_ID=2
NOTE: cannot fetch plan for SQL_ID: 8utckadc0tfwf, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.
SQL>

以上说明,在cursor_sharing=force情况下,select * from test1 where OBJECT_ID=2和select * from test1 where OBJECT_ID=8994没有真实的执行计划,被解析为select * from test1 where OBJECT_ID=:"SYS_B_0"像转换成了varchar2类型,但是执行计划仍旧走了索引,说明变量值并非真正的转换成了varchar2类型






SQL> variable i number;
SQL>  exec :i :=100;
PL/SQL procedure successfully completed.

SQL> select * from test2 where OBJECT_ID=:i;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    98 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2        |     1 |    98 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1611  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> variable i varchar2(100);
SQL>  exec :i :=100;
PL/SQL procedure successfully completed.

SQL> select * from test2 where OBJECT_ID=:i;
Execution Plan
----------------------------------------------------------
Plan hash value: 2958957202
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    98 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2        |     1 |    98 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1611  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index ind_objname2 on test2(object_name);

SQL>  select * from test2 where object_name='STUDENT';
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   196 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2       |     2 |   196 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJNAME2 |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"='STUDENT')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1343  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select * from test2 where object_name='EMP';
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   196 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2       |     2 |   196 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJNAME2 |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"='EMP')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1612  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> variable i varchar2(100);
SQL> exec :i := 'DEPARTMENTS';
PL/SQL procedure successfully completed.

SQL> select * from test2 where object_name=:i;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2253262559
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   196 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2       |     2 |   196 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJNAME2 |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"=:I)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1343  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%test2%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------------------------------------------------------------
546169kbtwbpn            0 select * from test2 where object_name=:"SYS_B_0"
6w5ajr3sa8f6u            0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name='STUDENT'
bnh26hasb65tk            0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name='EMP'
fgg6b5j2yqfdv            0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where object_name=:i
gysqqgu0jaxmg            0 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where OBJECT_ID=:i
gysqqgu0jaxmg            1 EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where OBJECT_ID=:i
4byjth2n03k8k            0 select * from test2 where OBJECT_ID=:i
4byjth2n03k8k            1 select * from test2 where OBJECT_ID=:i
0q3jgnatqzyyx            0 select * from test2 where object_name=:i

SQL> select * from table(dbms_xplan.display_cursor('546169kbtwbpn',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  546169kbtwbpn, child number 0
-------------------------------------
select * from test2 where object_name=:"SYS_B_0"
Plan hash value: 2857596457
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2        |     2 |   196 |     4   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IND_OBJNAME2 |     2 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"=:SYS_B_0)
19 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('6w5ajr3sa8f6u',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6w5ajr3sa8f6u, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name='STUDENT'
NOTE: cannot fetch plan for SQL_ID: 6w5ajr3sa8f6u, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('bnh26hasb65tk',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bnh26hasb65tk, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name='EMP'
NOTE: cannot fetch plan for SQL_ID: bnh26hasb65tk, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('fgg6b5j2yqfdv',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fgg6b5j2yqfdv, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
object_name=:i
NOTE: cannot fetch plan for SQL_ID: fgg6b5j2yqfdv, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('gysqqgu0jaxmg',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gysqqgu0jaxmg, child number 0
EXPLAIN PLAN SET STATEMENT_ID='PLUS60017' FOR select * from test2 where
OBJECT_ID=:i
NOTE: cannot fetch plan for SQL_ID: gysqqgu0jaxmg, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
9 rows selected.

SQL>  select * from table(dbms_xplan.display_cursor('4byjth2n03k8k',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4byjth2n03k8k, child number 0
-------------------------------------
select * from test2 where OBJECT_ID=:i
Plan hash value: 1534018530
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2         |     1 |    98 |     2   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID2 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=:I)
19 rows selected

SQL> select * from table(dbms_xplan.display_cursor('0q3jgnatqzyyx',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0q3jgnatqzyyx, child number 0
-------------------------------------
select * from test2 where object_name=:i
Plan hash value: 2857596457
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2        |     2 |   196 |     4   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IND_OBJNAME2 |     2 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"=:I)
19 rows selected.

以上说明,在手工设置绑定变量时,select * from test2 where OBJECT_ID=:i,被解析为select * from test2where OBJECT_ID=:i变量值对应的字段是什么类型则变量值就是什么类型,为什么set autotrace traceonly使用了to_number,是因为sqlplus的工作原理explain plan 的限制,将所有输入作为 varchar ,然后转换

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2151531/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes ORALC HOME,11G OCM认证,5年以上DBA工作经验,两年运维团队管理经验,本博客仅记录自己的一个学习过程,不保证完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    329
  • 访问量
    285903