[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重複測試看看.也存在相同的問題,大家可以自行測試.