[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.总结:
--//即使没有直方图的字段,如果查询范围变化很大.也可能导致出现大量的子光标问题.
--//前一阵子在做优化时我想当然以为重新分析取消某个日期字段的直方图信息,就不会出现大量子光标问题,结果发现我错了.
--//可能我以前也做过类似测试,加强记忆重复测试.
--//做一个例子说明,另外我一直认为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.总结:
--//即使没有直方图的字段,如果查询范围变化很大.也可能导致出现大量的子光标问题.
