[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重复测试看看.也存在相同的问题,大家可以自行测试.