[20221228]Adaptive Cursor Sharing & 直方图2.txt
[20221228]Adaptive Cursor Sharing & 直方图2.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)
---------- -------------- --------------
22513 2 29751
SCOTT@test01p> @ descz t object_id
eXtended describe of t
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" or 1=1 or 1.
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- ---------- ----------- ------------------- ---- ----------- ---------- --------- ------------ -------------- ---------- --------- ----------- --------- ----------
SCOTT T 22511 2023-01-23 16:46:21 1 OBJECT_ID NUMBER(,) 22511 .00004442273 2 1 2 29751
--//使用我前面写的minmaxtab脚本清除object_id统计信息中记录的最大最小值.
--//参考链接 http://blog.itpub.net/267265/viewspace-2932709/ =>[20221216]建立修改表统计信息minmaxtab.sql脚本.txt
SCOTT@test01p> @ minmaxtab t object_id
modify table:t column=object_id stats min and max = NULL
input argument list : owner.table_name column_name
PL/SQL procedure successfully completed.
SCOTT@test01p> @ descz t object_id
eXtended describe of t
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" or 1=1 or 1.
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- ---------- ----------- ------------------- ---- ----------- ---------- --------- ------------ -------------- ---------- --------- ----------- --------- ----------
SCOTT T 22511 2023-01-23 16:46:21 1 OBJECT_ID NUMBER(,) 22511 .00004442273 2 1
--//OK!!
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> @ 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, 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 N N Y 5 346
--//可以发现现在is_bind_sensitive=N,没有生成新的子光标.
--//看看执行计划:
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID db58kqj8kuyn6, child number 0
-------------------------------------
select * from t where object_id >= :N1 and object_id <= :N2
Plan hash value: 2339744171
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 56 | 672 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T_OBJECT_ID | 101 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 10
2 - :2 (NUMBER): 1000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N2>=:N1)
3 - access("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
40 rows selected.
--//注意看下划线现在抓取的绑定变量N2:=1000000.说明重新抓取绑定变量值.
--//当然查询范围变大,最佳的执行计划是选择全表扫描.我这里仅仅例子说明删除统计字段的最大最小值,可以规避acs的问题.
--//前一阵子在做优化时我想当然以为重新分析取消某个日期字段的直方图信息,就不会出现大量子光标问题,结果发现我错了.
--//可能我以前也做过类似测试,加强记忆重复测试.
--//前几天做了测试说明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)
---------- -------------- --------------
22513 2 29751
SCOTT@test01p> @ descz t object_id
eXtended describe of t
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" or 1=1 or 1.
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- ---------- ----------- ------------------- ---- ----------- ---------- --------- ------------ -------------- ---------- --------- ----------- --------- ----------
SCOTT T 22511 2023-01-23 16:46:21 1 OBJECT_ID NUMBER(,) 22511 .00004442273 2 1 2 29751
--//使用我前面写的minmaxtab脚本清除object_id统计信息中记录的最大最小值.
--//参考链接 http://blog.itpub.net/267265/viewspace-2932709/ =>[20221216]建立修改表统计信息minmaxtab.sql脚本.txt
SCOTT@test01p> @ minmaxtab t object_id
modify table:t column=object_id stats min and max = NULL
input argument list : owner.table_name column_name
PL/SQL procedure successfully completed.
SCOTT@test01p> @ descz t object_id
eXtended describe of t
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" or 1=1 or 1.
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- ---------- ----------- ------------------- ---- ----------- ---------- --------- ------------ -------------- ---------- --------- ----------- --------- ----------
SCOTT T 22511 2023-01-23 16:46:21 1 OBJECT_ID NUMBER(,) 22511 .00004442273 2 1
--//OK!!
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> @ 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, 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 N N Y 5 346
--//可以发现现在is_bind_sensitive=N,没有生成新的子光标.
--//看看执行计划:
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID db58kqj8kuyn6, child number 0
-------------------------------------
select * from t where object_id >= :N1 and object_id <= :N2
Plan hash value: 2339744171
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 56 | 672 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T_OBJECT_ID | 101 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 10
2 - :2 (NUMBER): 1000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N2>=:N1)
3 - access("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
40 rows selected.
--//注意看下划线现在抓取的绑定变量N2:=1000000.说明重新抓取绑定变量值.
--//当然查询范围变大,最佳的执行计划是选择全表扫描.我这里仅仅例子说明删除统计字段的最大最小值,可以规避acs的问题.
