[20161029]无法窥视在PLSQL.txt

[20161029]无法窥视在PLSQL.txt

--测试使用PL/SQL无法窥视绑定变量的情况:
--例子链接:https://connormcdonald.wordpress.com/2016/10/20/taking-a-peek-at-sys_context/

1.测试环境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table t ( x varchar2(10), y char(100));

insert into t select 'a', rownum from dual;
insert into t select 'b', rownum from dual connect by level <= 100000;
commit;

create index ix on t ( x ) ;

exec dbms_stats.gather_table_stats('','T',method_Opt=>'for all columns size 5');

--drop context blah;

create context blah using my_package;

create or replace PACKAGE MY_PACKAGE AS
procedure my_proc(p_val varchar2);
  function get_sys_context return varchar2;
  function get_variable return varchar2;
END MY_PACKAGE;

create or replace package body MY_PACKAGE AS
  my_var varchar2(10);
  procedure my_proc(p_val varchar2)  is
    begin
        my_var := p_val;
        sys.dbms_session.set_context('BLAH','ATTRIB',p_val);
    end my_proc;
    
  function get_sys_context return varchar2 is
    begin
      return sys_context('BLAH','ATTRIB');
    end get_sys_context;

  function get_variable return varchar2 is
    begin
      return my_var;
    end get_variable;

end MY_PACKAGE;

2.Now the real testcase starts:

exec my_package.my_proc('a');
select my_package.get_sys_context from dual;

SCOTT@test01p> select my_package.get_sys_context c10 from dual;
C10
----------
a

select my_package.get_variable from dual;

SCOTT@test01p> select my_package.get_variable c10 from dual;
C10
----------
a

SCOTT@test01p> select /*+ gather_plan_statistics */ count(y) from t where x = my_package.get_sys_context;
  COUNT(Y)
----------
         1

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4w5kfk85560fd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x =
my_package.get_sys_context
Plan hash value: 2966233522
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   418 (100)|          |      1 |00:00:00.67 |    1509 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |   103 |            |          |      1 |00:00:00.67 |    1509 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |  5029K|   418   (3)| 00:00:01 |      1 |00:00:00.67 |    1509 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("X"="MY_PACKAGE"."GET_SYS_CONTEXT"())

SCOTT@test01p> select /*+ gather_plan_statistics */ count(y) from t where x = my_package.get_variable;
  COUNT(Y)
----------
         1

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4sfj3d5djwbqg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x =
my_package.get_variable

Plan hash value: 2966233522

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   418 (100)|          |      1 |00:00:00.17 |    1509 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |   103 |            |          |      1 |00:00:00.17 |    1509 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |  5029K|   418   (3)| 00:00:01 |      1 |00:00:00.17 |    1509 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("X"="MY_PACKAGE"."GET_VARIABLE"())

CREATE OR REPLACE FUNCTION RETURN_BIND
(
  BIND_IN IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN BIND_IN;
END RETURN_BIND;

variable b1 varchar2(10)
exec :b1 := 'a';

SCOTT@test01p> select RETURN_BIND(:b1) from dual;
RETURN_BIND(:B1)
----------------
a

SCOTT@test01p> select /*+ gather_plan_statistics */ count(y) from t where x = RETURN_BIND(:b1);
  COUNT(Y)
----------
         1

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  31wcf0q2urgmh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x =
RETURN_BIND(:b1)
Plan hash value: 2966233522
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   418 (100)|          |      1 |00:00:00.24 |    1509 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |   103 |            |          |      1 |00:00:00.24 |    1509 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |  5029K|   418   (3)| 00:00:01 |      1 |00:00:00.24 |    1509 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("X"="RETURN_BIND"(:B1))

--//从以上测试可以看出无法窥视PL/SQL包以及函数返回的值,这样执行计划选择的是全表扫描.而直接使用参数值:

variable b1 varchar2(10)
exec :b1 := 'a';

SCOTT@test01p> select /*+ gather_plan_statistics */ count(y) from t where x = :b1;
  COUNT(Y)
----------
         1

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3s46kmk2u542g, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = :b1
Plan hash value: 2143077847
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |   103 |            |          |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |   103 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------------
--可以很好地使用索引.

3.突然想起可以使用ASSOCIATE STATISTICS关联统计,测试看看.

--使用ASSOCIATE STATISTICS 看看:

SCOTT@test01p> ASSOCIATE STATISTICS WITH FUNCTIONS RETURN_BIND DEFAULT SELECTIVITY 1, DEFAULT COST (312722, 5, 0);
Statistics associated.

SCOTT@test01p> Select /*+ gather_plan_statistics */ count(y) from t where x = RETURN_BIND(:b1);
  COUNT(Y)
----------
         1

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  15vu5j6v0n9nh, child number 0
-------------------------------------
Select /*+ gather_plan_statistics */ count(y) from t where x =
RETURN_BIND(:b1)
Plan hash value: 2143077847
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |   839 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |   103 |            |          |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |  50001 |  5029K|   839   (1)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                  | IX   |      1 |  50001 |       |    91   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("X"="RETURN_BIND"(:B1))

--不过这样我修改exec :b1 := 'b';执行计划依旧使用索引,不会改变,大家可以自己测试.还很奇怪的地方,查询视图无显示.

SCOTT@test01p> select * from USER_USTATS ;
no rows selected

--取消关联.
SCOTT@test01p> DISASSOCIATE STATISTICS from  FUNCTIONS RETURN_BIND ;
Statistics disassociated.
   
SCOTT@test01p> SElect /*+ gather_plan_statistics */ count(y) from t where x = RETURN_BIND(:b1);
  COUNT(Y)
----------
         1
--注意我修改sql语句Select 变成了SElect,这样要重新分析,不然还是使用原来的执行计划使用索引.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g5ttjj4jpfxxr, child number 0
-------------------------------------
SElect /*+ gather_plan_statistics */ count(y) from t where x =
RETURN_BIND(:b1)
Plan hash value: 2966233522
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   418 (100)|          |      1 |00:00:00.23 |    1509 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |   103 |            |          |      1 |00:00:00.23 |    1509 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |  5029K|   418   (3)| 00:00:01 |      1 |00:00:00.23 |    1509 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("X"="RETURN_BIND"(:B1))

--又回到了原来的全表扫描.

时间： 2025-01-28 05:41:00

[20161029]无法窥视在PLSQL.txt的相关文章

1211Bug with integer literals in PLSQL

[20171211]Bug with integer literals in PLSQL.txt --//链接 http://orasql.org/2017/12/09/bug-with-integer-literals-in-plsql/ --//重复测试: --//作者的观点好像是如果在PL/SQL中定义的整形变量并引用太多,最后出现异常. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION       

Uedit32与SQLPlus结合使用技巧

技巧                 Uedit32与SQLPlus结合使用技巧        用过Uedit32的人都知道,这个工具虽然小,却功能俱全.只要我们在工作中巧 妙的使用用它,可以使我们的工作效率事半功倍.那么,在下面的文章中,我将说说 Uedit32与Oracle SQLPlus用法.     首先下载并安装Uedit32,确保SQlPlus能正常运行, 接下来的事情就是两者 的系统配置了,具体按照以下的几个步骤进行:                步骤一:在Oracle安装目录下

[20161029]windows 7的文件名开头空格.txt

[20161029]windows 7的文件名开头是空格的问题.txt --今天遇到windows 7的文件名开头是空格的一个bug,通过一个例子来说明: 1.建立一个文件夹aaa: 里面包含2个文件1.txt," 1.txt", --注:第2个文件前面有一个空格要使用引号.实际上在图形界面上你无法建立前面带空格的文件,至少在widows7下. --我是使用记事本编辑1.txt ,然后选择另存为打入 " 1.txt"生成的. D:\temp\aaa>dir  

[20121102]PLSQL中的绑定变量.txt

[20121102]PLSQL中的绑定变量.txt     以前曾经遇到一个sql语句提交给开发,开发没有找到,最终确定是问题语句在PLSQL中,实际上PLSQL转化为大写, 加上自己没有注意.实际上SQL语句在PLSQL中,一些好像被"格式化一样",我举一个例子: 1.测试环境: SQL> select * from v$version where rownum BANNER ---------------------------------------------------

[20171220]toad plsql显示整形的bug.txt

[20171220]toad plsql显示整形的bug.txt --//下午有itpub网友反应,一个查询在sqlplus,pl/sql下不同.链接如下: --//http://www.itpub.net/thread-2095697-1-1.html --//我测试感觉是数据出现错误.直接那它的数据测试看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ---------

[20141213]11g ACS的一些问题4.txt

[20141213]11g ACS的一些问题4.txt --11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题,前一段时间看了2篇blog https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/ https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/ --我以前也写过一篇blog,链接如下: http://blog.itp

[20141213]11g ACS的一些问题3.txt

[20141213]11g ACS的一些问题3.txt --11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题,前一段时间看了2篇blog https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/ https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/ --我以前也写过一篇blog,链接如下: http://blog.itp

解决plsql dev无法在win7-64bit中运行问题

介绍一下情况,我自本地电脑装了一个win7-64位操作系统,然后安装了64位的oracle client,准本远程连接到服务器端的数据库,因为现在需要用到plsql dev做开发,所以打算安装此软件,安装完成之后报错,说是需要安装32位的oracle client,考虑到我已安装了64位的客户端了,而且操作系统本来就是64位,所以不打算再安装32位的oracle client,怎么办呢? 最后,从网上总结了如下解决办法: 使用PL/SQL Developer连接OracleX64版本: •1.

SQL和PLSQL

 过滤和排序.txt SQL>--where SQL>--查询工资大于2500的员工信息 SQL>select *   2  formemp   3 where sal>2500; form emp * 第 2 行出现错误: ORA-00923:未找到要求的 FROM 关键字 SQL>ed 已写入 file afiedt.buf     1 select *   2  formemp   3* where sal>2500 SQL> / form emp *