[20221227]Adaptive Cursor Sharing & 直方圖.txt

[20221227]Adaptive Cursor Sharing & 直方圖.txt

--//前一陣子在做最佳化時我想當然以為重新分析取消某個日期欄位的直方圖資訊,就不會出現大量子游標問題,結果發現我錯了.
--//可能我以前也做過類似測試,加強記憶重複測試.
--//做一個例子說明,另外我一直認為ACS是一個不好的設計,問題多多.大家可以看我以前的測試.

1.環境:
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.建立例子:

create table t as select object_id, object_type from dba_objects;
create index i_t_object_id on t(object_id);

SCOTT@test01p> @ tpt/gts t
Gather Table Statistics for table t...
exec dbms_stats.gather_table_stats(null, upper('t'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.

SCOTT@test01p> select column_name, histogram from user_tab_col_statistics where table_name = 'T';

COLUMN_NAME          HISTOGRAM
-------------------- ---------------
OBJECT_ID            NONE
OBJECT_TYPE          NONE

SCOTT@test01p> select count(*), min(object_id), max(object_id) from t;
  COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
     22478              2          29182

3.測試:
var N1 number;
var N2 number;
exec :N1 := 10;
exec :N2 := 11;
select * from t where object_id >= :N1 and object_id <= :N2;

SCOTT@test01p> select * from t where object_id >= :N1 and object_id <= :N2;
 OBJECT_ID OBJECT_TYPE
---------- --------------------
        10 CLUSTER
        11 INDEX

SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1361934982 db58kqj8kuyn6            0      96902      2339744171  512d7a86  2023-01-18 21:15:42    16777217        

column i_b_s format a10
column i_b_a format a10
column i_sh  format a10

SCOTT@test01p> select sql_id, is_bind_sensitive i_b_s, is_bind_aware i_b_a, is_shareable i_sh, executions, fetches from v$sql v where sql_id='db58kqj8kuyn6';
SQL_ID        I_B_S      I_B_A      I_SH       EXECUTIONS    FETCHES
------------- ---------- ---------- ---------- ---------- ----------
db58kqj8kuyn6 Y          N          Y                   2          4
--//注意已經執行2次.

exec :N2 := 1000000;

select * from t where object_id >= :N1 and object_id <= :N2;
select * from t where object_id >= :N1 and object_id <= :N2;
--//再次執行2次.輸出太長忽略.

SCOTT@test01p> select sql_id, is_bind_sensitive i_b_s, is_bind_aware i_b_a, is_shareable i_sh, executions, fetches from v$sql v where sql_id='db58kqj8kuyn6';
SQL_ID        I_B_S      I_B_A      I_SH       EXECUTIONS    FETCHES
------------- ---------- ---------- ---------- ---------- ----------
db58kqj8kuyn6 Y          N          Y                   4        232
--//可以發現當改變執行範圍很大2次時執行計劃並沒有改變,也就是沒有產生子游標.

select * from t where object_id >= :N1 and object_id <= :N2;
--//再次執行1次.輸出太長忽略.

SCOTT@test01p> select sql_id, child_number,is_bind_sensitive i_b_s, is_bind_aware i_b_a, is_shareable i_sh, executions, fetches from v$sql v where sql_id='db58kqj8kuyn6';
SQL_ID        CHILD_NUMBER I_B_S      I_B_A      I_SH       EXECUTIONS    FETCHES
------------- ------------ ---------- ---------- ---------- ---------- ----------
db58kqj8kuyn6            0 Y          N          N                   4        232
db58kqj8kuyn6            1 Y          N          Y                   1        114
2 rows selected.
--//可以發現改變查詢範圍第3次(執行次數第5次)後,發現產生新的子游標.
--//child_number=0的is_shareable=N,已經不再共享了.

select * from t where object_id >= :N1 and object_id <= :N2;
--//再次執行1次.輸出太長忽略.

SCOTT@test01p> @ dpcx db58kqj8kuyn6  '' 1
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  db58kqj8kuyn6, child number 1
-------------------------------------
select * from t where object_id >= :N1 and object_id <= :N2
Plan hash value: 1322348184
----------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |    18 (100)|          |
|*  1 |  FILTER            |      |        |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  22471 |   263K|    18   (6)| 00:00:01 |
----------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 10
   2 - :2 (NUMBER): 1000000
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:N2>=:N1)
   2 - filter(("OBJECT_ID">=:N1 AND "OBJECT_ID"<=:N2))
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
32 rows selected.
--//可以發現child_number=1選擇全表掃描.

SCOTT@test01p> exec :N2 := 12;

PL/SQL procedure successfully completed.

SCOTT@test01p> select * from t where object_id >= :N1 and object_id <= :N2;
 OBJECT_ID OBJECT_TYPE
---------- --------------------
        10 CLUSTER
        12 TABLE
        11 INDEX
3 rows selected.

SCOTT@test01p> select sql_id, child_number,is_bind_sensitive i_b_s, is_bind_aware i_b_a, is_shareable i_sh, executions, fetches from v$sql v where sql_id='db58kqj8kuyn6';
SQL_ID        CHILD_NUMBER I_B_S      I_B_A      I_SH       EXECUTIONS    FETCHES
------------- ------------ ---------- ---------- ---------- ---------- ----------
db58kqj8kuyn6            0 Y          N          N                   4        232
db58kqj8kuyn6            1 Y          N          Y                   3        230
2 rows selected.

--//再次修改N2=12,縮小查詢範圍,但是可以發現選擇child_number=1的執行計劃,也就是全表掃描.

SCOTT@test01p> select * from t where object_id >= :N1 and object_id <= :N2;
 OBJECT_ID OBJECT_TYPE
---------- --------------------
        10 CLUSTER
        12 TABLE
        11 INDEX

3 rows selected.

SCOTT@test01p> select sql_id, child_number,is_bind_sensitive i_b_s, is_bind_aware i_b_a, is_shareable i_sh, executions, fetches from v$sql v where sql_id='db58kqj8kuyn6';
SQL_ID        CHILD_NUMBER I_B_S      I_B_A      I_SH       EXECUTIONS    FETCHES
------------- ------------ ---------- ---------- ---------- ---------- ----------
db58kqj8kuyn6            0 Y          N          N                   4        232
db58kqj8kuyn6            1 Y          N          Y                   4        232

2 rows selected.

SCOTT@test01p> select * from t where object_id >= :N1 and object_id <= :N2;
 OBJECT_ID OBJECT_TYPE
---------- --------------------
        10 CLUSTER
        11 INDEX
        12 TABLE
3 rows selected.

SCOTT@test01p> select sql_id, child_number,is_bind_sensitive i_b_s, is_bind_aware i_b_a, is_shareable i_sh, executions, fetches from v$sql v where sql_id='db58kqj8kuyn6';
SQL_ID        CHILD_NUMBER I_B_S      I_B_A      I_SH       EXECUTIONS    FETCHES
------------- ------------ ---------- ---------- ---------- ---------- ----------
db58kqj8kuyn6            0 Y          N          N                   4        232
db58kqj8kuyn6            1 Y          N          N                   4        232
db58kqj8kuyn6            2 Y          Y          Y                   1          2
3 rows selected.

--//你可以發現child_number=0,1的is_shareable=N,不再共享,你可以發現acs的缺點.這樣導致大量的子游標.

4.總結:
--//即使沒有直方圖的欄位,如果查詢範圍變化很大.也可能導致出現大量的子游標問題.