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;