[20161029]無法窺視在PLSQL.txt

lfree發表於2016-10-30
[20161029]無法窺視在PLSQL.txt

--測試使用PL/SQL無法窺視繫結變數的情況:
--例子連結:

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))

--又回到了原來的全表掃描.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2127341/,如需轉載,請註明出處,否則將追究法律責任。

相關文章