[20161029]无法窥视在PLSQL.txt

心已赠人 2022-01-31 15:53 188阅读 0赞

\[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))  
  
\--又回到了原来的全表扫描.