[20170724]提示BIND_AWARE与PLSQL光标缓存

[20170724]提示BIND_AWARE与PLSQL光标缓存.txt

--//[20170724]Bind Sensitivity and PLSQL cursor caching.txt
--//曾经写过一个系列acs的文章.链接如下:
http://blog.itpub.net/267265/viewspace-721817/
http://blog.itpub.net/267265/viewspace-1336242/
http://blog.itpub.net/267265/viewspace-1336353/
http://blog.itpub.net/267265/viewspace-1368531/
http://blog.itpub.net/267265/viewspace-1369051/
http://blog.itpub.net/267265/viewspace-1483316/

--//我曾经提到这个功能可能就是鸡肋,因为有太多不可控因素,而使用提示bind_aware倒是一个不错的建议.
--//当然最佳的模式也是对于这种情况不使用绑定变量,而是直接带入文字值.

--//另外我在http://blog.itpub.net/267265/viewspace-1368531/测试中提到.PL/sql里面执行存在问题,实际上
--//这个是bug,我当时的版本是11.2.0.3. 估计11.2.0.4已经解决这个问题.

--//我曾在别人讲解绑定变量时提到,如果有什么提示要讲给开发的化,就是bind_aware提示.
--//因为我认为acs存在太多不确定因素.但是在PL/SQL中使用bind_aware提示会出现什么情况呢?
--//通过测试说明问题:

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

2.建立测试例子:

create table t nologging as
   select rownum id, 
        case mod(rownum,  10000) when 0 then 0 else 1 end flag,
        rpad('X', 100, 'X') padding
        from dual
        connect by level <= 2e5;
--//说明:这样字段flag存在不均衡,0很少(仅仅20个),而1很多,也就是在这个字段建立索引,查询=0时选择索引最佳,而查询=1,走全部扫描
--//最佳.

SCOTT@test01p> select count(*),flag from t group by flag;

  COUNT(*)       FLAG
---------- ----------
    199980          1
        20          0

SCOTT@test01p> create index i_t_flag on t(flag);
Index created.

SCOTT@test01p> exec dbms_stats.gather_table_stats(NULL, 'T', METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');
PL/SQL procedure successfully completed.

SCOTT@test01p> @ tab_lh scott t flag

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER TABLE_NAME COLUMN
SAMPLE  : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .

COLUMN_NAME  DATA_TYPE DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH  NUM_NULLS NUM_BUCKETS LAST_ANALYZED       HISTOGRAM DATA_DEFAULT
------------ --------- ----------- - ------------ ---------- ----------- --------- ---------- ---------- ----------- ------------------- --------- ------------
FLAG         NUMBER             22 Y            2   .0000025      200000 0         1                   0           2 2017-07-23 20:20:57 FREQUENCY 
--//建立直方图在flag字段.

create or replace procedure get_flag_count(p in number)
is
   dummy number;
begin
   select /*+ bind_aware sktest */ count(*) INTO dummy FROM t where flag = p;
end;
/

declare
   dummy number;
begin
   get_flag_count(0);
   get_flag_count(1);
end;  
/

--//确定sql_id='51wyyw23rhbc4'.

SCOTT@test01p> @ dpc 51wyyw23rhbc4 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  51wyyw23rhbc4, child number 0
-------------------------------------
SELECT /*+ bind_aware sktest */ COUNT(*) FROM T WHERE FLAG = :B1
Plan hash value: 3837301025
-------------------------------------------------------------------------------
| Id  | Operation         | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |        |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |          |      1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| I_T_FLAG |     20 |    60 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 0
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG"=:B1)

SCOTT@test01p> select sql_text,child_number, executions, parse_calls, is_bind_sensitive, is_bind_aware  from v$sql where sql_id = '51wyyw23rhbc4';
SQL_TEXT                                                     CHILD_NUMBER EXECUTIONS PARSE_CALLS I I
------------------------------------------------------------ ------------ ---------- ----------- - -
SELECT /*+ bind_aware sktest */ COUNT(*) FROM T WHERE FLAG =            0          2           1 Y Y
:B1

--//你可以发现提示bind_aware sktest失效!!
--//估计PL/SQL 光标缓存的原因,测试设置session_cached_cursors=0看看.

SCOTT@test01p> alter system flush shared_pool;
System altered.

SCOTT@test01p> select sql_text,child_number, executions, parse_calls, is_bind_sensitive, is_bind_aware  from v$sql where sql_id = '51wyyw23rhbc4';
no rows selected

declare
   dummy number;
begin
   execute immediate 'ALTER SESSION SET session_cached_cursors = 0';
   get_flag_count(0);
   get_flag_count(1);  
--   execute immediate 'ALTER SESSION RESET session_cached_cursors';
end;  
/

SCOTT@test01p> select sql_text c70,child_number, executions, parse_calls, is_bind_sensitive, is_bind_aware  from v$sql where sql_id = '51wyyw23rhbc4';
C70                                                                    CHILD_NUMBER EXECUTIONS PARSE_CALLS I I
---------------------------------------------------------------------- ------------ ---------- ----------- - -
SELECT /*+ bind_aware sktest */ COUNT(*) FROM T WHERE FLAG = :B1                  0          1           2 Y Y
SELECT /*+ bind_aware sktest */ COUNT(*) FROM T WHERE FLAG = :B1                  1          1           0 Y Y

--//可以发现现在生成新的子光标.换一句化正是参数session_cached_cursors,导致PL/sql中执行的sql语句光标的缓存,而提示失效.
--//上班在11.2.0.4重复测试看看.也存在相同的问题,大家可以自行测试.

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

转载于:http://blog.itpub.net/267265/viewspace-2142458/