Oracle SQL 硬解析和子游标

阅读导航

Oracle SQL 硬解析和子游标
硬解析和产生子游标的原因
如何避免

Oracle SQL 硬解析和子游标

What reasons will be happening sql hard parse and generating new child cursors

在一个繁忙的系统中，发现一个复杂且非常长的查询，产生40多个子游标和大量的硬解析，占用很多的内存、CPU资源；

SQL> @sql 3168229204Show SQL text, child cursors and execution stats for SQL hash value 3168229204 child GGT reportHASH_VALUE CH#PLAN_HASHFIRST_LOAD_TIMELAST_LOAD_TIME SQL_PROFIL---------- ----- ------------------------------ -------------------- ----------3168229204 0 11440310962016-09-21/15:52:452016-11-03/16:43:403168229204 1 11440310962016-09-21/15:52:452016-11-03/17:39:503168229204 2 11440310962016-09-21/15:52:452016-11-03/18:52:263168229204 3 11440310962016-09-21/15:52:452016-11-04/08:41:153168229204 4 11440310962016-09-21/15:52:452016-11-05/08:12:523168229204 5 11440310962016-09-21/15:52:452016-11-07/08:00:493168229204 6 11440310962016-09-21/15:52:452016-11-07/13:15:243168229204 7 11440310962016-09-21/15:52:452016-11-08/08:07:123168229204 8 11440310962016-09-21/15:52:452016-11-09/08:11:573168229204 9 11440310962016-09-21/15:52:452016-11-09/08:31:15316822920410 11440310962016-09-21/15:52:452016-11-09/08:46:133168229204115320579132016-09-21/15:52:452016-11-09/09:01:21316822920412 11440310962016-09-21/15:52:452016-10-26/08:10:30316822920413 11440310962016-09-21/15:52:452016-10-27/08:06:34316822920414 11440310962016-09-21/15:52:452016-10-27/10:30:49316822920415 11440310962016-09-21/15:52:452016-10-28/08:06:48316822920416 11440310962016-09-21/15:52:452016-10-31/08:00:14316822920417 11440310962016-09-21/15:52:452016-10-29/11:15:32316822920418 11440310962016-09-21/15:52:452016-11-01/08:02:00316822920419 11440310962016-09-21/15:52:452016-11-01/08:16:023168229204445320579132016-09-21/15:52:452016-10-25/08:36:4621 rows selected.CH# PARENT_HANDLEOBJECT_HANDLEPARSES H_PARSES EXECUTIONSFETCHES ROWS_PROCESSED LIOS PIOSSORTS CPU_MS ELA_MS USERS_EXECUTING----- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------0 000000099DC30528 000000099DC6212011171 1120619 5630971603706707.98 1777858.92 01 000000099DC30528 000000099EC8B47811141 1120795 539435 10300 3436.47859351.813 02 000000099DC30528 000000099DE9FE0031093 336238567657906028872087585.686927030.91 03 000000099DC30528 000000099FC011E881058 82 155431 22164287 211248040 295961.008 6440049.63 04 000000099DC30528 000000099F5D9880 44103 44315 5720911343329961296893220 1627595.57 26658408 05 000000099DC30528 000000099EC73B9810410010456510070373185029723107190530 3833473.23 32819296.8 06 000000099DC30528 000000099F426050 21 95 21 30253871980211 9151011131.307691583.17 07 000000099DC30528 000000099E1C8A58 31 91 31 81 134024 82881335 757100670 830793.701 12330642 08 000000099DC30528 000000099FAC91F8 51 86 51221 3995521564051501511677730 1859173.36 34943618.3 09 000000099DC30528 000000099F6D67B81 8415 9331 545117 190 1828.722 2107.133 0 10 000000099DC30528 000000099FCF3EE81 7815 9386 5476951880 2588.60610211.348 0 11 000000099DC30528 000000099F50D9C8 32 76 32203 372484 98467223 943424880 1153776.61 19565473.3 1 12 000000099DC30528 000000099FA1ED181 721862 8610 626229352660 8491.715736156.11 0 13 000000099DC30528 000000099F0DA4C0 51 69 5154046 5401601567440171501983270 1901325.93 31480771.6 0 14 000000099DC30528 000000099E680C90 10 65 10 656665606 25179760 225903180 251589.755 3495357.72 0 15 000000099DC30528 000000099EF0DF50 42 57 4236991 3698061154604841029581630 1152703.76 15607683.6 0 16 000000099DC30528 000000099F5ACBC8 63 53 6360623 60600716798122515572127201724758.8 21204621.2 0 17 000000099DC30528 000000099FA0A6A01 531888 8879 193856 10470 1283.808 2972.2 0 18 000000099DC30528 000000099E7B52D8142 5114281062 8101032391756362260770410 2483807.37 18198010.6 0 19 000000099DC30528 000000099DA92AA0 15 46 1512766 1275751847753 5046015149.692 457626.043 0 44 000000099DC30528 000000099E6EBA18 481 4837672 3763311493843761441116920 1825119.51 31195023.6 0

而且由于某些原因优化器不能够做出正确的评估，导致执行计划不一样，产生了大量的物理读等待事件；所以作为开发人员我们要了解清楚硬解析和产生子游标的原因，做出必要的调整和优化，使优化器能够正确做出评估，巩固和保护执行计划，竭力避免重复硬解析和使用不正确的执行计划。

硬解析和产生子游标的原因

Oracle中有很多的原因导致硬解析和产生子游标，比如有两个用户USERA和USERB,它们都有相同的表TAB01,两个用户都执行了如下的查询操作；select * from tab01;这样就会在v$sqlarea,v$sql,v$sql_shared_cursor产生如下的记录；

SQL> select sql_text,hash_value,sharable_mem,buffer_gets,loads,fetches,executions,optimizer_mode,PARSING_SCHEMA_NAME from v$sqlarea where sql_id="5b42g2fkrrzss";SQL_TEXT HASH_VALUE SHARABLE_MEM BUFFER_GETSLOADSFETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME-------------------- ---------- ------------ ----------- ---------- ---------- ---------- -------------------- ------------------------------------------------------------select * from tab01277636687285836 220222 ALL_ROWS USERBSQL> select t.CHILD_NUMBER,sql_text,hash_value,sharable_mem,buffer_gets,loads,fetches,executions,optimizer_mode,t.PARSING_SCHEMA_NAME from v$sql t where sql_id="5b42g2fkrrzss";CHILD_NUMBER SQL_TEXT HASH_VALUE SHARABLE_MEM BUFFER_GETSLOADSFETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME------------ -------------------- ---------- ------------ ----------- ---------- ---------- ---------- -------------------- ------------------------------------------------------------ 0 select * from tab01277636687244868 110111 ALL_ROWS USERA 1 select * from tab01277636687244868 110111 ALL_ROWS USERBSQL> select child_number,t.AUTH_CHECK_MISMATCH,t.TRANSLATION_MISMATCH from v$sql_shared_cursor t where sql_id="5b42g2fkrrzss";CHILD_NUMBER AU TR------------ -- -- 0 NN 1 YY

v$sqlarea中记录父游标，统计所有包括子游标的数据（buffer_gets,loads,fetches,executions），PARSING_SCHEMA_NAME记录最后一次解析的用户；
v$sql中记录所有子游标，游标号码从0开始递增，每个游标记录自身的统计信息，这里需要注意，对于非长事务而言，oracle在运行完成后更新统计信息；但对于长事务，oracle每5秒钟更新一次统计信息；
v$sql_shared_cursor 中记录为什么子游标没有使用共享池里存在的游标而重新解析原因；上面的例子导致硬解析和产生子游标的原因是授权检查（AUTH_CHECK_MISMATCH）和对象检查（TRANSLATION_MISMATCH）失败；

其它还有非常多的原因导致硬解析和产生子游标，接下来会讨论一些日常开发中容易导致的原因；

create table tparse（x number primary key,y varchar2（30））;begindbms_stats.set_table_stats（user,"tparse",numrows=>10000000,numblks=>100000 ）;end;/begindbms_stats.set_index_stats（user,"SYS_C0013113",numrows=>10000000 ）;end;/

这里创建了tparse表，然后虚拟设置了表和索引的统计信息;接着在pl/sql里用不同的优化器环境和不同的条件下执行SQL；

declarel_num_x number;l_var_x varchar2（30）;l_var_x1 varchar2（300）;beginexecute immediate "alter session set optimizer_mode=all_rows";for i in （select * from tparse where x>l_num_x）loop null; end loop;for i in （select * from tparse where x>l_var_x）loop null; end loop;execute immediate "alter session set optimizer_mode=first_rows_10";for i in （select * from tparse where x>l_num_x）loop null; end loop;for i in （select * from tparse where x>l_var_x）loop null; end loop;for i in （select * from tparse where x>l_var_x1）loop null; end loop;end;/

成功执行pl/sql后，检查v$sql表；

col SQL_TEXT for a50select sql_id,CHILD_NUMBER,hash_value,SQL_TEXT ,buffer_gets LIOS,disk_reads PIOS,sorts, cpu_time/1000 cpu_ms,elapsed_time/1000 ela_msfrom v$sql where sql_text like "SELECT %TPARSE WHERE X%" order by CHILD_NUMBER ;SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LIOS PIOSSORTS CPU_MS ELA_MS-------------------------- ------------ ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ----------1dmmz4yh0hrzx 0 2684903421 SELECT * FROM TPARSE WHERE X>:B1 263021.7331dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B14001.9981.3311dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B140021.6731dmmz4yh0hrzx 3 2684903421 SELECT * FROM TPARSE WHERE X>:B12002.9993.2861dmmz4yh0hrzx 4 2684903421 SELECT * FROM TPARSE WHERE X>:B12001 .783

这里产生了5条记录，sql_id,hash_value都相同，但是它们有不同之处；

第一次解析，optimizer_mode值为all_rows；谓语条件的值类型与主键值类型相同，此时共享池里没有匹配的已经共享的游标，oracle硬解析并共享游标；
第二次解析，optimizer_mode值为all_rows，谓语条件的值为类型为varchar，与主键值类型不相同；优化器隐形转换值类型,然后对比第一次共享的游标时因为值变量类型不同，所以硬解析和产生新游标;
第三次解析，optimizer_mode值为first_rows；谓语条件的值类型与主键值类型相同，优化器在对比第一次共享的游标时发现环境不一致，所以硬解析和产生新游标;
第四次解析，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";ADDRESSCHILD_ADDRESSCHILD_NUMBER BI OP BI---------------- ---------------- ------------ -- -- --0000000069AC2D28 0000000062F19D700 NNN0000000069AC2D28 00000000696F7E481 YNN0000000069AC2D28 000000006A3E05A82 NYN0000000069AC2D28 000000006636C6D83 YYN0000000069AC2D28 0000000065AE23384 YYY

对于第一次解析，由于共享池中不存在已经解析的游标，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_ID1dmmz4yh0hrzx, child number 0-------------------------------------SELECT * FROM TPARSE WHERE X>:B1Plan 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_ID1dmmz4yh0hrzx, child number 1-------------------------------------SELECT * FROM TPARSE WHERE X>:B1Plan 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_ID1dmmz4yh0hrzx, child number 2-------------------------------------SELECT * FROM TPARSE WHERE X>:B1Plan 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_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_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）优化器设置改变了，评估的基数因优化器设置而变低。

如何避免

通过上面的例子可以看出，使用最频繁的情况（变量类型改变,变量长度改变,优化器设置改变等）均会导致重复的解析和新游标产生，但复杂且非常长的SQL在系统中是司空见惯的，如果才能避免或减少重复硬解析和资源的使用，又在一定程度上保护执行计划呢？
10g以前有outline，但使用受限；10g及以后有sql profile；让我们以第一次解析来创建SQL profile，看会发生什么；

SQL> @sqlprofile/create_sql_profile.sql "1dmmz4yh0hrzx" 0Enter value for sql_id: 1dmmz4yh0hrzxEnter value for child_no （0）:Enter value for profile_name （PROF_sqlid_planhash）:Enter value for category （DEFAULT）:Enter value for force_matching （FALSE）:SQL> alter system flush shared_pool;

创建好SQL profile后清空共享池，然后再重新运行上面的PL/SQL;再观察v$sql；

col SQL_TEXT for a50select sql_id,CHILD_NUMBER,hash_value,SQL_TEXT ,buffer_gets LIOS,disk_reads PIOS,sorts, cpu_time/1000 cpu_ms,elapsed_time/1000 ela_msfrom v$sql where sql_text like "SELECT %TPARSE WHERE X%" order by CHILD_NUMBER ;SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LIOS PIOSSORTS CPU_MS ELA_MS-------------------------- ------------ ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ----------1dmmz4yh0hrzx 0 2684903421 SELECT * FROM TPARSE WHERE X>:B1 1010 220 20.99624.271dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B120032.7831dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B140022.473select 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";ADDRESSCHILD_ADDRESSCHILD_NUMBER BI OP BI---------------- ---------------- ------------ -- -- --0000000069AC2D28 0000000062F19D700 NNN0000000069AC2D28 00000000696F7E481 YNN0000000069AC2D28 000000006A3E05A82 YNY

仅产生2个子游标，一次因为变量类型改变了，一次为变量类型和变量值长度改变了；优化器环境改变并没有影响到优化器；再继续查询优化器的行为；

SQL> @sql 2684903421Show SQL text, child cursors and execution stats for SQL hash value 2684903421 child 0HASH_VALUE CH#PLAN_HASH SQL_TEXT FIRST_LOAD_TIMELAST_LOAD_TIME SQL_PROFILE---------- ----- ---------- -------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------------------2684903421 0 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:372016-11-15/21:57:33PROF_1dmmz4yh0hrzx_32896377652684903421 1 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:372016-11-15/21:57:33PROF_1dmmz4yh0hrzx_32896377652684903421 2 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:372016-11-15/21:57:33PROF_1dmmz4yh0hrzx_32896377653 rows selected.CH# PARENT_HANDLEOBJECT_HANDLEPARSES H_PARSES EXECUTIONSFETCHES ROWS_PROCESSED LIOS PIOSSORTS CPU_MS ELA_MS USERS_EXECUTING----- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------0 0000000069AC2D28 0000000062F19D7037220 1010 220 20.99624.27 01 0000000069AC2D28 00000000696F7E482722020032.783 02 0000000069AC2D28 000000006A3E05A80722040022.473 0

三个游标均使用了同样的SQL Profile，执行计划因SQL Profile而受到保护。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址