linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

Oracle SQL 硬解析和子游标

时间:2016-11-19  来源:未知  作者:admin
阅读导航
  • 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

        
友情链接