Oracle SQL 硬解析和子游标
- Oracle SQL 硬解析和子游标
- 硬解析和产生子游标的原因
- 如何避免
Oracle SQL 硬解析和子游标
What reasons will be happening sql hard parse and generating new child cursors
在一个繁忙的系统中,发现一个复杂且非常长的查询,产生40多个子游标和大量的硬解析,占用很多的内存、CPU资源;
SQL> @sql 3168229204 Show SQL text, child cursors and execution stats for SQL hash value 3168229204 child GGT report HASH_VALUE CH# PLAN_HASH FIRST_LOAD_TIME LAST_LOAD_TIME SQL_PROFIL ---------- ----- ---------- -------------------- -------------------- ---------- 3168229204 0 1144031096 2016-09-21/15:52:45 2016-11-03/16:43:40 3168229204 1 1144031096 2016-09-21/15:52:45 2016-11-03/17:39:50 3168229204 2 1144031096 2016-09-21/15:52:45 2016-11-03/18:52:26 3168229204 3 1144031096 2016-09-21/15:52:45 2016-11-04/08:41:15 3168229204 4 1144031096 2016-09-21/15:52:45 2016-11-05/08:12:52 3168229204 5 1144031096 2016-09-21/15:52:45 2016-11-07/08:00:49 3168229204 6 1144031096 2016-09-21/15:52:45 2016-11-07/13:15:24 3168229204 7 1144031096 2016-09-21/15:52:45 2016-11-08/08:07:12 3168229204 8 1144031096 2016-09-21/15:52:45 2016-11-09/08:11:57 3168229204 9 1144031096 2016-09-21/15:52:45 2016-11-09/08:31:15 3168229204 10 1144031096 2016-09-21/15:52:45 2016-11-09/08:46:13 3168229204 11 532057913 2016-09-21/15:52:45 2016-11-09/09:01:21 3168229204 12 1144031096 2016-09-21/15:52:45 2016-10-26/08:10:30 3168229204 13 1144031096 2016-09-21/15:52:45 2016-10-27/08:06:34 3168229204 14 1144031096 2016-09-21/15:52:45 2016-10-27/10:30:49 3168229204 15 1144031096 2016-09-21/15:52:45 2016-10-28/08:06:48 3168229204 16 1144031096 2016-09-21/15:52:45 2016-10-31/08:00:14 3168229204 17 1144031096 2016-09-21/15:52:45 2016-10-29/11:15:32 3168229204 18 1144031096 2016-09-21/15:52:45 2016-11-01/08:02:00 3168229204 19 1144031096 2016-09-21/15:52:45 2016-11-01/08:16:02 3168229204 44 532057913 2016-09-21/15:52:45 2016-10-25/08:36:46 21 rows selected. CH# PARENT_HANDLE OBJECT_HANDLE PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED LIOS PIOS SORTS CPU_MS ELA_MS USERS_EXECUTING ----- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------------- 0 000000099DC30528 000000099DC62120 1 117 1 11 20619 563097 16037 0 6707.98 1777858.92 0 1 000000099DC30528 000000099EC8B478 1 114 1 11 20795 539435 1030 0 3436.478 59351.813 0 2 000000099DC30528 000000099DE9FE00 3 109 3 33 62385 6765790 6028872 0 87585.686 927030.91 0 3 000000099DC30528 000000099FC011E8 8 105 8 82 155431 22164287 21124804 0 295961.008 6440049.63 0 4 000000099DC30528 000000099F5D9880 44 103 44 315 572091 134332996 129689322 0 1627595.57 26658408 0 5 000000099DC30528 000000099EC73B98 104 100 104 565 1007037 318502972 310719053 0 3833473.23 32819296.8 0 6 000000099DC30528 000000099F426050 21 95 21 30 25387 1980211 9151 0 11131.307 691583.17 0 7 000000099DC30528 000000099E1C8A58 31 91 31 81 134024 82881335 75710067 0 830793.701 12330642 0 8 000000099DC30528 000000099FAC91F8 51 86 51 221 399552 156405150 151167773 0 1859173.36 34943618.3 0 9 000000099DC30528 000000099F6D67B8 1 84 1 5 9331 545117 19 0 1828.722 2107.133 0 10 000000099DC30528 000000099FCF3EE8 1 78 1 5 9386 547695 188 0 2588.606 10211.348 0 11 000000099DC30528 000000099F50D9C8 32 76 32 203 372484 98467223 94342488 0 1153776.61 19565473.3 1 12 000000099DC30528 000000099FA1ED18 1 72 1 862 8610 626229 35266 0 8491.715 736156.11 0 13 000000099DC30528 000000099F0DA4C0 51 69 51 54046 540160 156744017 150198327 0 1901325.93 31480771.6 0 14 000000099DC30528 000000099E680C90 10 65 10 6566 65606 25179760 22590318 0 251589.755 3495357.72 0 15 000000099DC30528 000000099EF0DF50 42 57 42 36991 369806 115460484 102958163 0 1152703.76 15607683.6 0 16 000000099DC30528 000000099F5ACBC8 63 53 63 60623 606007 167981225 155721272 0 1724758.8 21204621.2 0 17 000000099DC30528 000000099FA0A6A0 1 53 1 888 8879 193856 1047 0 1283.808 2972.2 0 18 000000099DC30528 000000099E7B52D8 142 51 142 81062 810103 239175636 226077041 0 2483807.37 18198010.6 0 19 000000099DC30528 000000099DA92AA0 15 46 15 12766 127575 1847753 5046 0 15149.692 457626.043 0 44 000000099DC30528 000000099E6EBA18 48 1 48 37672 376331 149384376 144111692 0 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_GETS LOADS FETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME -------------------- ---------- ------------ ----------- ---------- ---------- ---------- -------------------- ------------------------------------------------------------ select * from tab01 2776366872 85836 220 2 2 2 ALL_ROWS USERB SQL> 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_GETS LOADS FETCHES EXECUTIONS OPTIMIZER_MODE PARSING_SCHEMA_NAME ------------ -------------------- ---------- ------------ ----------- ---------- ---------- ---------- -------------------- ------------------------------------------------------------ 0 select * from tab01 2776366872 44868 110 1 1 1 ALL_ROWS USERA 1 select * from tab01 2776366872 44868 110 1 1 1 ALL_ROWS USERB SQL> 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 N N 1 Y Y
- 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) ); begin dbms_stats.set_table_stats ( user,'tparse', numrows=>10000000, numblks=>100000 ); end; / begin dbms_stats.set_index_stats ( user,'SYS_C0013113', numrows=>10000000 ); end; /
这里创建了tparse表,然后虚拟设置了表和索引的统计信息;接着在pl/sql里用不同的优化器环境和不同的条件下执行SQL;
declare l_num_x number; l_var_x varchar2(30); l_var_x1 varchar2(300); begin execute 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 a50 select sql_id,CHILD_NUMBER,hash_value,SQL_TEXT , buffer_gets LIOS, disk_reads PIOS, sorts, cpu_time/1000 cpu_ms, elapsed_time/1000 ela_ms from v$sql where sql_text like 'SELECT %TPARSE WHERE X%' order by CHILD_NUMBER ; SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LIOS PIOS SORTS CPU_MS ELA_MS -------------------------- ------------ ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- 1dmmz4yh0hrzx 0 2684903421 SELECT * FROM TPARSE WHERE X>:B1 26 3 0 2 1.733 1dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 1.998 1.331 1dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 2 1.673 1dmmz4yh0hrzx 3 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 2.999 3.286 1dmmz4yh0hrzx 4 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 1 .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'; 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)
优化器设置改变了,评估的基数因优化器设置而变低。
如何避免
通过上面的例子可以看出,使用最频繁的情况(变量类型改变,变量长度改变,优化器设置改变等)均会导致重复的解析和新游标产生,但复杂且非常长的SQL在系统中是司空见惯的,如果才能避免或减少重复硬解析和资源的使用,又在一定程度上保护执行计划呢?
10g以前有outline,但使用受限;10g及以后有sql profile;让我们以第一次解析来创建SQL profile,看会发生什么;
SQL> @sqlprofile/create_sql_profile.sql '1dmmz4yh0hrzx' 0 Enter value for sql_id: 1dmmz4yh0hrzx Enter 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 a50 select sql_id,CHILD_NUMBER,hash_value,SQL_TEXT , buffer_gets LIOS, disk_reads PIOS, sorts, cpu_time/1000 cpu_ms, elapsed_time/1000 ela_ms from v$sql where sql_text like 'SELECT %TPARSE WHERE X%' order by CHILD_NUMBER ; SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LIOS PIOS SORTS CPU_MS ELA_MS -------------------------- ------------ ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- 1dmmz4yh0hrzx 0 2684903421 SELECT * FROM TPARSE WHERE X>:B1 1010 22 0 20.996 24.27 1dmmz4yh0hrzx 1 2684903421 SELECT * FROM TPARSE WHERE X>:B1 2 0 0 3 2.783 1dmmz4yh0hrzx 2 2684903421 SELECT * FROM TPARSE WHERE X>:B1 4 0 0 2 2.473 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 Y N Y
仅产生2个子游标,一次因为变量类型改变了,一次为变量类型和变量值长度改变了;优化器环境改变并没有影响到优化器;再继续查询优化器的行为;
SQL> @sql 2684903421 Show SQL text, child cursors and execution stats for SQL hash value 2684903421 child 0 HASH_VALUE CH# PLAN_HASH SQL_TEXT FIRST_LOAD_TIME LAST_LOAD_TIME SQL_PROFILE ---------- ----- ---------- -------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------------------ 2684903421 0 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765 2684903421 1 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765 2684903421 2 3289637765 SELECT * FROM TPARSE WHERE X>:B1 2016-11-15/20:09:37 2016-11-15/21:57:33 PROF_1dmmz4yh0hrzx_3289637765 3 rows selected. CH# PARENT_HANDLE OBJECT_HANDLE PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED LIOS PIOS SORTS CPU_MS ELA_MS USERS_EXECUTING ----- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------------- 0 0000000069AC2D28 0000000062F19D70 3 7 2 2 0 1010 22 0 20.996 24.27 0 1 0000000069AC2D28 00000000696F7E48 2 7 2 2 0 2 0 0 3 2.783 0 2 0000000069AC2D28 000000006A3E05A8 0 7 2 2 0 4 0 0 2 2.473 0
三个游标均使用了同样的SQL Profile,执行计划因SQL Profile而受到保护。