[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.总结: --//即使没有直方图的字段,如果查询范围变化很大.也可能导致出现大量的子光标问题.