--
SQL> drop table t1 purge;
Table dropped.
SQL>
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create index idx_t1_id on t1(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR all indexed COLUMNS SIZE 1',ESTIMATE_PERCENT=>100);
PL/SQL procedure successfully completed.
SQL> select status from t1 where object_id=99;
STATUS
-------
VALID
SQL> @all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gc3t90a78nksp, child number 0
-------------------------------------
select status from t1 where object_id=99
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=99)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "STATUS"[VARCHAR2,7]
2 - "T1".ROWID[ROWID,10]
31 rows selected.
--可以看到参数is_bind_sensitive=N,说明它没有使用绑定变量;
SQL> select child_number,plan_hash_value,parse_calls,buffer_gets,is_obsolete,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql
3 where sql_id='gc3t90a78nksp';
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 1 4 N N N Y
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string ALL
SQL> var aa number;
SQL> exec :aa :=22;
PL/SQL procedure successfully completed.
SQL> select status from t1 where object_id=:aa;
STATUS
-------
VALID
SQL> @all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dgkb6prhgfz2v, child number 0
-------------------------------------
select status from t1 where object_id=:aa
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:AA)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "STATUS"[VARCHAR2,7]
2 - "T1".ROWID[ROWID,10]
31 rows selected.
--可以看到参数is_bind_sensitive=Y,很好说明了此参数意义，延伸可以作为判断SQL有无使用绑定变量;
SQL> select child_number,plan_hash_value,parse_calls,buffer_gets,is_obsolete,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql
3 where sql_id='dgkb6prhgfz2v';
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 1 4 N Y N Y
--构造列object_id出现数据倾斜情况；
SQL> update t1 set object_id=999 where object_id >300;
72335 rows updated.
SQL> commit;
Commit complete.
--不作直方图统计
SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR all indexed COLUMNS SIZE 1',ESTIMATE_PERCENT=>100);
PL/SQL procedure successfully completed.
SQL> exec :aa :=33;
PL/SQL procedure successfully completed.
SQL> select status from t1 where object_id=:aa;
STATUS
-------
VALID
SQL> @all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dgkb6prhgfz2v, child number 0
-------------------------------------
select status from t1 where object_id=:aa
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:AA)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "STATUS"[VARCHAR2,7]
2 - "T1".ROWID[ROWID,10]
31 rows selected.
--可以看到is_bind_aware仍是N；
SQL> select child_number,plan_hash_value,parse_calls,buffer_gets,is_obsolete,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql
3 where sql_id='dgkb6prhgfz2v';
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 2 8 N Y N Y
exec :aa :=999; --这个结果有7万多行，执行完成后看下,is_bind_aware仍是N；
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10872 N Y N Y
--刷新下库，重新设置参数statistics_level=typical;
SQL> alter system flush shared_pool;
System altered.
SQL> var aa number;
SQL> exec :aa :=22;
PL/SQL procedure successfully completed.
SQL> select status from t1 where object_id=:aa;
STATUS
-------
VALID
SQL> @all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dgkb6prhgfz2v, child number 0
-------------------------------------
select status from t1 where object_id=:aa
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 252 | 2268 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 252 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
--看下结果
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 1 50 N Y N Y
exec :aa :=999; --执行后再看下结果
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 2 10914 N Y N Y
--再次执行,可以看到当第2次执行时，is_bind_aware变成Y，并且child_number=0的属性is_shareable=N
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 0 10864 N Y Y Y --注意这里的parse_calls=0,说明只是产生了新的执行计划但并未被使用过，或者说是启用了自适应游标共享；
--看下文档的说明
Because the database is now using adaptive cursor sharing,
the database no longer uses the original cursor (child 0),
which is not bind-aware. The shared SQL area will age out the defunct cursor.
--参数is_bind_aware说明，当两次执行的结果或者说是统计信息出现了巨大差异，那么O会将其标识为Y，看文档说明；
If execution statistics vary greatly, then the database marks the cursor bind-aware.
Bind-Aware Cursors
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.
When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
■Generates a new plan based on the new bind value.
■Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.
--再次执行一次
SQL> exec :aa :=11;
PL/SQL procedure successfully completed.
SQL> select status from t1 where object_id=:aa;
STATUS
-------
VALID
SQL> @all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dgkb6prhgfz2v, child number 1
-------------------------------------
select status from t1 where object_id=:aa
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 252 | 2268 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 252 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 1 10868 N Y Y Y --此游标已经开始被使用了！
小结：另外还说明一个参数statistics_level确实会影响自适应共享游标的使用，当设成all时，我们没有看到is_bind_aware=Y,
只有将其设为typical时才生效；
------------------------------<以上为列object_id不存在直方图的测试情况<--------------------------------------
------------------------------<以下为列object_id存在直方图的测试情况<--------------------------------------
SQL> exec dbms_stats.gather_table_stats('AIKI','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS SIZE 254 OBJECT_ID',ESTIMATE_PERCENT=>100);
PL/SQL procedure successfully completed.
运行3次的结果相同：（无论是小的数据量还是大的数据量);
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 4 32600 N Y Y Y
--重新开启一个新的会话，避免pga的影响;
exec :aa :=999;
--执行结果：72335 rows selected.
--已经看到产生了一个新的执行计划
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 4 32600 N Y Y Y
2 3617692013 1 5795 N Y N Y
--可以看到新的执行计划为全表扫描
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 291 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 72193 | 634K| 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
--由于会话连接默认是statistics_level=all,将其它改回typical，再次运行查看
--奇怪事情发生了，再次按999(7万多行)运行时竟然使用190799060执行计划（索引）;
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 1 5795 N Y N Y
--将参数改回all;
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 2 11590 N Y N Y
--更换结果少，走索引的变量，奇怪事情又来了；持续执行3次才再次产生正确的执行计划；
SQL> exec :aa :=44;
PL/SQL procedure successfully completed.
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 3 12629 N Y N Y
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 4 13668 N Y N Y
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 0 4 N Y Y Y
--从第4次开始已能正确使用
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 1 8 N Y Y Y
--再次换用全表扫的变量999，结果奇怪又来了，尽管执行超过了3次，但仍是使用上次产生的子游标3的执行计划；
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 2 10872 N Y Y Y
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 3 21736 N Y Y Y
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 4 32600 N Y Y Y
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 5 43464 N Y Y Y
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 6 54328 N Y Y Y
--新开了一会话再次执行重复4次，结果不变
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 5 43464 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 10 97784 N Y Y Y
--变量statistics_level=typical;再跑
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 6 54328 N Y Y Y --会自动在相应的相同环境变量里执行此执行计划；
2 3617692013 5 13668 N Y N Y
3 190799060 10 97784 N Y Y Y
--退出所有会话再次执行（全表扫的变量）
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 7 65192 N Y Y Y --仍然是这里增加
2 3617692013 5 13668 N Y N Y
3 190799060 10 97784 N Y Y Y
--重新收集统计信息，结果仍未变
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 190799060 8 76056 N Y Y Y
2 3617692013 5 13668 N Y N Y
3 190799060 10 97784 N Y Y Y
--造成人为ddl操作，使其重新解析
SQL> grant select on aiki.t1 to aiki2;
Grant succeeded.
SQL> revoke select on aiki.t1 from aiki2;
Revoke succeeded.
--结果为:可以看到1的子游标被替换成执行计划为全表的游标
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 3617692013 1 5819 N Y N Y --重新置为1
2 3617692013 5 13668 N Y N Y
3 190799060 10 97784 N Y Y Y
--重复执行3次
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 3617692013 4 23204 N Y N Y
2 3617692013 5 13668 N Y N Y
3 190799060 10 97784 N Y Y Y
--再次换成直索引变量，执行4次，结果有些出乎意料，仍使用全表扫描
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 190799060 3 10914 N Y N N
1 3617692013 8 27360 N Y N Y
2 3617692013 5 13668 N Y N Y
3 190799060 10 97784 N Y Y Y
--重刷内存池
SQL> alter system flush shared_pool;
System altered.
--重新在两个变量间切换执行，基本发现有时要执行3次以上才会出现正确的执行计划，并非想像中的那么及时智能（自适应游标);
SQL> /
CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS BUFFER_GETS I I I I
------------ --------------- ----------- ----------- - - - -
0 3617692013 9 27336 N Y N Y
1 190799060 14 86940 N Y Y Y
11g自适应游标及statistics_level
最新推荐文章于 2022-08-09 13:16:10 发布
239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
