11g自适应游标及statistics_level
来源：互联网 发布：淘宝集市店怎样注册 编辑：程序博客网 时间：2024/06/01 10:08
--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-------VALIDSQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID gc3t90a78nksp, child number 0-------------------------------------select status from t1 where object_id=99Plan 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$1Predicate 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_levelNAME TYPE VALUE------------------------------------ ----------- ------------------------------statistics_level string ALLSQL> var aa number;SQL> exec :aa :=22;PL/SQL procedure successfully completed.SQL> select status from t1 where object_id=:aa;STATUS-------VALIDSQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID dgkb6prhgfz2v, child number 0-------------------------------------select status from t1 where object_id=:aaPlan 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$1Predicate 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-------VALIDSQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID dgkb6prhgfz2v, child number 0-------------------------------------select status from t1 where object_id=:aaPlan 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$1Predicate 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-------VALIDSQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID dgkb6prhgfz2v, child number 0-------------------------------------select status from t1 where object_id=:aaPlan 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=NSQL> /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 CursorsA 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-------VALIDSQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID dgkb6prhgfz2v, child number 1-------------------------------------select status from t1 where object_id=:aaPlan 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 YSQL> /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 YSQL> /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 YSQL> /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 YSQL> /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 YSQL> /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 YSQL> /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 0 0
- 11g自适应游标及statistics_level
- 绑定变量让sql重新硬解析的方法及11g自适应游标
- Oracle Database 11g Release 1中的自适应游标共享
- Oracle 11g 新特性 -- 自适应游标共享(Adaptive Cursor Sharing： ACS) 说明
- Oracle 数据库 11g新特性：自适应游标与 SQL 计划管理
- STATISTICS_LEVEL
- STATISTICS_LEVEL
- statistics_level
- 10g中表监控与statistics_level
- oracle 10g中表监控与statistics_level
- 10g中表监控与statistics_level
- 35、10g中表监控与statistics_level
- Oracle自适应共享游标
- Oracle自适应共享游标
- oracle 10g 游标
- oracle 10g 游标
- PL/SQL 11g R2 —— 游标
- 『ORACLE』父子游标（11g）--未完
- 数字整除
- Linux系统命令详解之ls
- Android 进阶学习：事件分发机制完全解析，带你从源码的角度彻底理解（上）
- dll里面分配的空间一定要在dll里面释放吗
- IOS程序发布到APP Store详细流程
- 11g自适应游标及statistics_level
- Windows如何压缩tar.gz格式
- .Net使用SSH.NET通过SSH访问Linux主机
- MATLAB金融工具箱（一）--快速入门
- USB架构浅谈
- 进程与线程的一个简单解释——图文类比
- DLL分配的内存如何在EXE里面释放
- Android实现批量添加联系人到通讯录
- [Linux内存]——内核地址空间
