Oracle SQL 硬解析和子游标(3)

第四次解析，optimizer_mode值为first_rows，谓语条件的值类型为varchar，与主键值类型不相同；优化器在对比第一次共享的游标时发现环境和变量类型均不一致，所以硬解析和产生新游标;

第五次解析，optimizer_mode值为first_rows，谓语条件的值类型为varchar，与主键值类型不相同；并且长度改变为300;优化器在对比第一次共享的游标时发现环境、变量类型和值长度均不一致，所以硬解析和产生新游标;

这些原因都可以在v$sql_shared_cursor视图中找到原因；

select t.ADDRESS,t.CHILD_ADDRESS,child_number,t.BIND_MISMATCH,t.OPTIMIZER_MODE_MISMATCH,t.BIND_LENGTH_UPGRADEABLE from v$sql_shared_cursor t where sql_id='1dmmz4yh0hrzx'; ADDRESS CHILD_ADDRESS CHILD_NUMBER BI OP BI ---------------- ---------------- ------------ -- -- -- 0000000069AC2D28 0000000062F19D70 0 N N N 0000000069AC2D28 00000000696F7E48 1 Y N N 0000000069AC2D28 000000006A3E05A8 2 N Y N 0000000069AC2D28 000000006636C6D8 3 Y Y N 0000000069AC2D28 0000000065AE2338 4 Y Y Y

对于第一次解析，由于共享池中不存在已经解析的游标，oracle必须硬解析SQL，然后共享，所以v$sql_shared_cursor视图中的mismatch值为N;
当第二次解析时， 由于共享池中已经存在解析的游标，但由于变量类型与主键类型不同，对比第一次解析时发生BIND_MISMATCH，oracle再次硬解析;
第三次解析时，由于绑定值与主键值类型相同，但优化器的设置不同，对比第一次解析时发生OPTIMIZER_MODE_MISMATCH,oracle再次硬解析;
第四次解析时，由于绑定值与主键值类型不同，并且优化器的设置也不同，对比第一次解析发生BIND_MISMATCH和OPTIMIZER_MODE_MISMATCH,oracle再次硬解析;
;
第五次解析时，由于绑定值与主键值类型不同，优化器的设置不同，并且绑定值长度较之前发生了变化，对比第一次解析时发生BIND_MISMATCH、OPTIMIZER_MODE_MISMATCH和BIND_LENGTH_UPGRADEABLE,oracle再次硬解析;

　　到现在我们了解了产生硬解析和子游标的原因，我们看看优化器在生成执行计划时的不同； 　　 首先看第一次的执行计划；

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1dmmz4yh0hrzx',0)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID 1dmmz4yh0hrzx, child number 0 ------------------------------------- SELECT * FROM TPARSE WHERE X>:B1 Plan hash value: 3289637765 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 13 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TPARSE | 500K| 14M| 13 (24)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SYS_C0013113 | 90000 | | 4 (50)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("X">:B1)

优化器使用了索引，谓语条件没有任何转换；
第二次

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1dmmz4yh0hrzx',1)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- SQL_ID 1dmmz4yh0hrzx, child number 1 ------------------------------------- SELECT * FROM TPARSE WHERE X>:B1 Plan hash value: 3289637765 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 13 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TPARSE | 500K| 14M| 13 (24)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SYS_C0013113 | 90000 | | 4 (50)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("X">TO_NUMBER(:B1))

优化器同样使用了索引，谓语条件中值类型发生隐形转换；
第三次解析

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1dmmz4yh0hrzx',2,'outline')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------- SQL_ID 1dmmz4yh0hrzx, child number 2 ------------------------------------- SELECT * FROM TPARSE WHERE X>:B1 Plan hash value: 3289637765 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TPARSE | 10 | 300 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SYS_C0013113 | 90000 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') FIRST_ROWS(10) OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "TPARSE"@"SEL$1" ("TPARSE"."X")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("X">:B1)

优化器设置改变了，评估的基数因优化器设置而变低。

如何避免