This discussion is archived
1 2 3 Previous Next 41 Replies Latest reply: Oct 18, 2012 1:39 PM by 439902 Go to original post RSS
  • 15. Re: Hard Parse Time - too long...
    530897 Explorer
    Currently Being Moderated
    You mentioned above earlier that you are using 11gR2. Is that base or 11.2.0.3? I have recently worked through an issue were Bug 12345980 was causing high parse times. The database was configured with CURSOR_SHARING to force (similar was also a problem) and SESSION_CACHED_CURSORS was 2000. Setting SESSION_CACHED_CURSORS to a lower value or CURSROR_SHARING to exact resolved the problem.

    This may not be the case in your situation, but you should consider raising an SR with Oracle Support.
  • 16. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    We are using session_cached_cursors=50 (I believe, this is a default), and cursor_sharing = EXACT.

    To answer your question, we are on 11.2.0.3.0.
  • 17. Re: Hard Parse Time - too long...
    sb92075 Guru
    Currently Being Moderated
    in the posted EXPLAIN PLAN a majority of the lines report only a single row in the view or table.
    is this an accurate reflection of reality?

    Have you done a CBO trace to see what & where the CBO is spending its time?
  • 18. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    Entire schema has been analyzed as follows:
               SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
                         OwnName            => USER
                        ,estimate_percent   => 100
                        ,granularity        => 'ALL'     
                        ,cascade            => TRUE   
                        ,no_invalidate      => FALSE 
                        ,force              => TRUE    
                        ,method_opt         => 'FOR ALL COLUMNS SIZE 254'
                        ,degree             => 16
                );
    So, stats are up to date on all the tables. There are no new rows added.
    I do not know why the majority of estimates come up with a single row.
    It is not entirely true. Or, rather, entirely not true :)
  • 19. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    Apologies for the long attachment - here is the CBO trace, broken into parts:
    TKPROF: Release 11.2.0.3.0 - Development on Sat Oct 13 14:37:46 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Trace file: orcl_ora_25306.trc
    Sort options: default
    
    ********************************************************************************
    count    = number of times OCI procedure was executed
    cpu      = cpu time in seconds executing 
    elapsed  = elapsed time in seconds executing
    disk     = number of physical reads of buffers from disk
    query    = number of buffers gotten for consistent read
    current  = number of buffers gotten in current mode (usually for update)
    rows     = number of rows processed by the fetch or execute call
    ********************************************************************************
    
    SQL ID: aw6bkfm3qj9xx Plan Hash: 0
    
    ALTER SESSION SET sql_trace=TRUE
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        1      0.00       0.00          0          0          0           0
    
    Misses in library cache during parse: 0
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 144  
    ********************************************************************************
    
    SQL ID: 3nkd3g3ju5ph1 Plan Hash: 2853959010
    
    select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, 
      spare2 
    from
     obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null 
      and linkname is null and subname is null
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     49      0.00       0.00          0          0          0           0
    Fetch       49      0.00       0.05         19        195          0          48
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       98      0.00       0.05         19        195          0          48
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: b5m19dkfsmbpn Plan Hash: 0
    
    BEGIN DBMS_SESSION.set_sql_trace(sql_trace => TRUE); END;
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          0          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 144  
    ********************************************************************************
    
    SQL ID: 4tk6t8tfsfqbf Plan Hash: 0
    
    alter session set sql_trace=true
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          0          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 144     (recursive depth: 1)
    ********************************************************************************
    
    SQL ID: 62armjnxu7h08 Plan Hash: 0
    
    ALTER SESSION SET EVENTS '10053 trace name context forever, level 12'
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          0          0           0
    
    Misses in library cache during parse: 0
    Parsing user id: 144  
    ********************************************************************************
    
    SQL ID: 1gu8t96d0bdmu Plan Hash: 2035254952
    
    select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
      nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
      t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
      t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
      nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
      nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
      ts.logicalread 
    from
     tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     13      0.00       0.00          0          0          0           0
    Fetch       13      0.00       0.02         10         70          0          13
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       26      0.00       0.02         10         70          0          13
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    ********************************************************************************
    
    SQL ID: 7ng34ruy5awxq Plan Hash: 3992920156
    
    select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
      i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
      i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
      nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
      i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
      nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
      null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
      ist.logicalread 
    from
     ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
      min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) 
      valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where 
      i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     13      0.00       0.00          0          0          0           0
    Fetch       59      0.00       0.03          9        104          0          46
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       72      0.00       0.03          9        104          0          46
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    ********************************************************************************
    
    SQL ID: 5n1fs4m2n2y0r Plan Hash: 299250003
    
    select pos#,intcol#,col#,spare1,bo#,spare2,spare3 
    from
     icol$ where obj#=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     46      0.00       0.00          0          0          0           0
    Fetch      120      0.00       0.01          3        240          0          74
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      166      0.00       0.01          3        240          0          74
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    ********************************************************************************
    
    SQL ID: 83taa7kaw59c1 Plan Hash: 3765558045
    
    select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
      nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
      scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
      rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
      nvl(spare3,0) 
    from
     col$ where obj#=:1 order by intcol#
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     13      0.00       0.00          0          0          0           0
    Fetch      173      0.00       0.00          0         50          0         160
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      186      0.00       0.00          0         50          0         160
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    ********************************************************************************
  • 20. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    part 2
    SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3755742892
    
    select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
      NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
      NVL(scanhint,0),NVL(bitmapranges,0) 
    from
     seg$ where ts#=:1 and file#=:2 and block#=:3
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute   1195      0.00       0.01          0          0          0           0
    Fetch     1195      0.04       0.28        112       3585          0        1195
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     2390      0.05       0.29        112       3585          0        1195
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    ********************************************************************************
    
    SQL ID: 87gaftwrm2h68 Plan Hash: 1218588913
    
    select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname 
    from
     obj$ o where o.obj#=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     38      0.00       0.00          0          0          0           0
    Fetch       38      0.00       0.07         28        114          0          38
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       76      0.00       0.07         28        114          0          38
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 9gkq7rruycsjp Plan Hash: 3362549386
    
    select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, 
      definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, 
      defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) 
      subparttype, mod(trunc(spare2/256), 256) subpartkeycols, 
      mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296),
       256) defhscflags, mod(spare3, 256) interval_dty, rowid, defmaxsize 
    from
     partobj$ where obj# = :1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       17      0.00       0.00          0          0          0           0
    Execute     17      0.00       0.00          0          0          0           0
    Fetch       17      0.00       0.00          2         29          0          12
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       51      0.00       0.01          2         29          0          12
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  TABLE ACCESS BY INDEX ROWID PARTOBJ$ (cr=2 pr=1 pw=0 time=3039 us cost=1 size=45 card=1)
             1          1          1   INDEX UNIQUE SCAN I_PARTOBJ$ (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)(object id 565)
    
    ********************************************************************************
    
    SQL ID: cbdfcfcp1pgtp Plan Hash: 142600749
    
    select intcol#, col# , type#, spare1, segcol#, charsetform 
    from
     partcol$  where obj# = :1 order by pos#
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       12      0.00       0.00          0          0          0           0
    Execute     12      0.00       0.00          0          0          0           0
    Fetch       24      0.00       0.01          2         24          0          12
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       48      0.00       0.01          2         24          0          12
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT ORDER BY (cr=2 pr=1 pw=0 time=3520 us cost=3 size=24 card=1)
             1          1          1   TABLE ACCESS BY INDEX ROWID PARTCOL$ (cr=2 pr=1 pw=0 time=3508 us cost=2 size=24 card=1)
             1          1          1    INDEX RANGE SCAN I_PARTCOL$ (cr=1 pr=0 pw=0 time=2153 us cost=1 size=0 card=1)(object id 567)
    
    ********************************************************************************
    
    SQL ID: 96g93hntrzjtr Plan Hash: 2239883476
    
    select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 
      sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, 
      spare1, spare2, avgcln 
    from
     hist_head$ where obj#=:1 and intcol#=:2
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute    111      0.00       0.00          0          0          0           0
    Fetch      111      0.00       0.03         13        445          0         108
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      222      0.00       0.03         13        445          0         108
    
    Misses in library cache during parse: 0
    Optimizer mode: RULE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: db78fxqxwxt7r Plan Hash: 3312420081
    
    select /*+ rule */ bucket, endpoint, col#, epvalue 
    from
     histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute    316      0.00       0.00          0          0          0           0
    Fetch      316      0.03       0.19         60       1078          0        5916
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      632      0.03       0.20         60       1078          0        5916
    
    Misses in library cache during parse: 0
    Optimizer mode: RULE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 130dvvr5s8bgn Plan Hash: 1160622595
    
    select obj#, dataobj#, part#, hiboundlen, hiboundval, ts#, file#, block#, 
      pctfree$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize, 
      rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, length(bhiboundval), 
      bhiboundval 
    from
     tabpart$ where bo# = :1 order by part#
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        3      0.00       0.00          0          0          0           0
    Execute      3      0.00       0.00          0          0          0           0
    Fetch        6      0.02       0.07         14        115          0         320
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      0.03       0.08         14        115          0         320
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
           128        107        128  TABLE ACCESS BY INDEX ROWID TABPART$ (cr=38 pr=5 pw=0 time=11425 us cost=5 size=12144 card=69)
           128        107        128   INDEX RANGE SCAN I_TABPART_BOPART$ (cr=3 pr=2 pw=0 time=5392 us cost=2 size=0 card=69)(object id 571)
    
    ********************************************************************************
    
    SQL ID: 6aq34nj2zb2n7 Plan Hash: 2874733959
    
    select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) 
    from
     objauth$ where obj#=:1 and col# is not null group by privilege#, col#, 
      grantee# order by col#, grantee#
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      9      0.00       0.00          0          0          0           0
    Fetch        9      0.00       0.00          1         18          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       18      0.00       0.00          1         18          0           0
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 2q93zsrvbdw48 Plan Hash: 2874733959
    
    select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
    from
     objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by 
      grantee#
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      9      0.00       0.00          0          0          0           0
    Fetch       11      0.00       0.00          0         20          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       20      0.00       0.00          0         20          0           2
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 7nuw4xwrnuwxq Plan Hash: 1720483994
    
    select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, 
      nvl(typidcol#, 0) 
    from
     coltype$ where obj#=:1 order by intcol# desc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0          6          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          6          0           0
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 9rfqm06xmuwu0 Plan Hash: 832500465
    
    select intcol#, toid, version#, intcols, intcol#s, flags, synobj# 
    from
     subcoltype$ where obj#=:1 order by intcol# asc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0          6          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          6          0           0
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: f3g84j69n0tjh Plan Hash: 2335623859
    
    select col#,intcol#,ntab# 
    from
     ntab$ where obj#=:1 order by intcol# asc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0          2          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          2          0           0
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 6qz82dptj0qr7 Plan Hash: 2819763574
    
    select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, 
      l.pctversion$, l.flags, l.property, l.retention, l.freepools 
    from
     lob$ l where l.obj# = :1 order by l.intcol# asc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        4      0.00       0.00          0          6          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        8      0.00       0.00          0          6          0           2
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT ORDER BY (cr=3 pr=0 pw=0 time=25 us cost=3 size=153 card=3)
             1          1          1   TABLE ACCESS CLUSTER LOB$ (cr=3 pr=0 pw=0 time=15 us cost=2 size=153 card=3)
             1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=8 us cost=1 size=0 card=1)(object id 3)
    
    ********************************************************************************
    
    SQL ID: 9g485acn2n30m Plan Hash: 2544153582
    
    select col#,intcol#,reftyp,stabid,expctoid 
    from
     refcon$ where obj#=:1 order by intcol# asc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0          6          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          6          0           0
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 32bhha21dkv0v Plan Hash: 3765558045
    
    select col#,intcol#,charsetid,charsetform 
    from
     col$ where obj#=:1 order by intcol# asc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch       56      0.00       0.00          0          6          0          54
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       58      0.00       0.00          0          6          0          54
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 0fr8zhn4ymu3v Plan Hash: 1231101765
    
    select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum 
    from
     opqtype$ where obj# = :1 order by intcol# asc
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0          2          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          2          0           0
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: gnkrt49h24x8a Plan Hash: 866645418
    
    select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, 
      maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, 
      maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg,
       cmpflag_stg, cmplvl_stg 
    from
     deferred_stg$  where obj# =:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute    303      0.00       0.00          0          0          0           0
    Fetch      303      0.00       0.06         18        909          0         303
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      607      0.00       0.07         18        909          0         303
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  TABLE ACCESS BY INDEX ROWID DEFERRED_STG$ (cr=3 pr=3 pw=0 time=13559 us cost=2 size=37 card=1)
             1          1          1   INDEX UNIQUE SCAN I_DEFERRED_STG1 (cr=2 pr=2 pw=0 time=7421 us cost=1 size=0 card=1)(object id 133)
    
    ********************************************************************************
    
    SQL ID: 57guu81bd4bc5 Plan Hash: 2397009925
    
    select name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,
      dflinit,dflincr,dflextpct,dflminext, dflminlen, owner#,scnwrp,scnbas, 
      NVL(pitrscnwrp, 0), NVL(pitrscnbas, 0), dflogging, bitmapped, inc#, flags, 
      plugged, NVL(spare1,0), NVL(spare2,0), affstrength 
    from
     ts$ where ts#=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.00       0.00          1          2          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.00       0.00          1          2          0           1
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  TABLE ACCESS CLUSTER TS$ (cr=2 pr=1 pw=0 time=3558 us cost=1 size=91 card=1)
             1          1          1   INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=16 us cost=0 size=0 card=1)(object id 7)
    
    ********************************************************************************
  • 21. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    Part 3
    SQL ID: 3ktacv9r56b51 Plan Hash: 4184428695
    
    select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, 
     nvl(property,0),subname,type#,d_attrs from
     dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        9      0.00       0.00          0          0          0           0
    Execute      9      0.00       0.00          0          0          0           0
    Fetch        9      0.00       0.00          2         27          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       27      0.00       0.00          2         27          0           0
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  SORT ORDER BY (cr=3 pr=1 pw=0 time=3037 us cost=11 size=342 card=3)
             0          0          0   NESTED LOOPS OUTER (cr=3 pr=1 pw=0 time=3025 us cost=10 size=342 card=3)
             0          0          0    TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=1 pw=0 time=3021 us cost=4 size=84 card=3)
             0          0          0     INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=1 pw=0 time=3020 us cost=3 size=0 card=3)(object id 106)
             0          0          0    TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=86 card=1)
             0          0          0     INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
    
    ********************************************************************************
    
    SQL ID: 8swypbbr0m372 Plan Hash: 893970548
    
    select order#,columns,types 
    from
     access$ where d_obj#=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        9      0.00       0.00          0          0          0           0
    Execute      9      0.00       0.00          0          0          0           0
    Fetch        9      0.00       0.00          1         18          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       27      0.00       0.00          1         18          0           0
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=2 pr=1 pw=0 time=1923 us cost=3 size=161 card=7)
             0          0          0   INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=1 pw=0 time=1921 us cost=2 size=0 card=7)(object id 108)
    
    ********************************************************************************
    SQL ID: c3zymn7x3k6wy Plan Hash: 3446064519
    
    select obj#, dataobj#, part#, hiboundlen, hiboundval, flags, ts#, file#, 
      block#, pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt, 
      blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, pctthres$, 
      length(bhiboundval), bhiboundval 
    from
     indpart$ where bo# = :1 order by part#
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        9      0.00       0.00          0          0          0           0
    Execute      9      0.00       0.00          0          0          0           0
    Fetch       18      0.07       0.18         32         57          0        1152
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       36      0.08       0.19         32         57          0        1152
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
           128        128        128  TABLE ACCESS BY INDEX ROWID INDPART$ (cr=6 pr=4 pw=0 time=10661 us cost=5 size=7644 card=42)
           128        128        128   INDEX RANGE SCAN I_INDPART_BOPART$ (cr=3 pr=1 pw=0 time=5565 us cost=2 size=0 card=42)(object id 576)
    **************************************
    
    SQL ID: 3w4qs0tbpmxr6 Plan Hash: 1224215794
    
    select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 
    from
     cdef$ where robj#=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     10      0.00       0.00          0          0          0           0
    Fetch       86      0.00       0.00          2        172          0          76
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       96      0.00       0.00          2        172          0          76
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    *************************************
    
    SQL ID: gx4mv66pvj3xz Plan Hash: 1932954096
    
    select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
      rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 
    from
     cdef$ where obj#=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     10      0.00       0.00          0          0          0           0
    Fetch       90      0.00       0.00          0        180          0          80
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      100      0.00       0.00          0        180          0          80
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    ********************************************************************************
    
    SQL ID: 53saa2zkr6wc3 Plan Hash: 3954488388
    
    select intcol#,nvl(pos#,0),col#,nvl(spare1,0) 
    from
     ccol$ where con#=:1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute     80      0.00       0.00          0          0          0           0
    Fetch      162      0.00       0.02          5        325          0          82
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      242      0.00       0.02          5        325          0          82
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 2)
    *************************************
    
    SQL ID: 5ngnyxjbgpuyh Plan Hash: 2967171960
    
    SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 
      NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') 
      NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), 
      NVL(SUM(C2),:"SYS_B_01") 
    FROM
     (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("VAL_000002") FULL("VAL_000002") 
      NO_PARALLEL_INDEX("VAL_000002") */ :"SYS_B_02" AS C1, CASE WHEN 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_03" ESCAPE :"SYS_B_04" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_05" ESCAPE :"SYS_B_06" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_07" ESCAPE :"SYS_B_08" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_09" ESCAPE :"SYS_B_10" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_11" ESCAPE :"SYS_B_12" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_13" ESCAPE :"SYS_B_14" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_15" ESCAPE :"SYS_B_16" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_17" ESCAPE :"SYS_B_18" THEN 
      :"SYS_B_19" ELSE :"SYS_B_20" END AS C2 FROM "BISWEBB"."RECORDTEXTVALUE" 
      SAMPLE BLOCK (:"SYS_B_21" , :"SYS_B_22") SEED (:"SYS_B_23") "VAL_000002" 
      WHERE LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_24" ESCAPE :"SYS_B_25" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_26" ESCAPE :"SYS_B_27" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_28" ESCAPE :"SYS_B_29" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_30" ESCAPE :"SYS_B_31" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_32" ESCAPE :"SYS_B_33" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_34" ESCAPE :"SYS_B_35" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_36" ESCAPE :"SYS_B_37" OR 
      LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_38" ESCAPE :"SYS_B_39") SAMPLESUB
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        5      0.00       0.00          0          0          0           0
    Execute      5      0.00       0.00          0          0          0           0
    Fetch        5     21.41      24.14      11108      37331          0           5
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       15     21.41      24.15      11108      37331          0           5
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 144     (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=7466 pr=3703 pw=0 time=5230126 us)
       3137126    3137126    3137126   PARTITION HASH ALL PARTITION: 1 128 (cr=7466 pr=3703 pw=0 time=2547843 us cost=18758 size=131597088 card=3133264)
       3137126    3137126    3137126    TABLE ACCESS SAMPLE RECORDTEXTVALUE PARTITION: 1 128 (cr=7466 pr=3703 pw=0 time=2372509 us cost=18758 size=131597088 card=3133264)
    
    ***************************************
  • 22. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    LAST
    SQL ID: 85za07ya8zj7u Plan Hash: 1326032260
    
    update object_usage set flags=flags+1 
    where
     obj#=:1 and bitand(flags, 1)=0
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       17      0.00       0.00          0          0          0           0
    Execute     17      0.00       0.01          2         34          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       34      0.00       0.01          2         34          0           0
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  UPDATE  OBJECT_USAGE (cr=2 pr=1 pw=0 time=4216 us)
             0          0          0   TABLE ACCESS BY INDEX ROWID OBJECT_USAGE (cr=2 pr=1 pw=0 time=4212 us cost=2 size=9 card=1)
             1          1          1    INDEX RANGE SCAN I_STATS_OBJ# (cr=1 pr=0 pw=0 time=2117 us cost=1 size=0 card=1)(object id 220)
    
    ********************************************************************************
    
    SELECT
        COUNT (DISTINCT ent_000000.pk)
    FROM entity ent_000000
        INNER JOIN recordtextvalue val_000002
            ON val_000002.sourceentityfk =  ent_000000.pk
            AND val_000002.fieldfk = HEXTORAW ('80B58FEACC79E011AD2400155D018001')
            AND (   LOWER (val_000002.VALUE) LIKE LOWER ('%1%') ESCAPE '~'
                  OR LOWER (val_000002.VALUE) LIKE LOWER ('%2%') ESCAPE '~'
                  OR LOWER (val_000002.VALUE) LIKE LOWER ('%3%') ESCAPE '~'
                  OR LOWER (val_000002.VALUE) LIKE LOWER ('%4%') ESCAPE '~'
                  OR LOWER (val_000002.VALUE) LIKE LOWER ('%5%') ESCAPE '~'
                  OR LOWER (val_000002.VALUE) LIKE LOWER ('%6%') ESCAPE '~'
                  OR LOWER (val_000002.VALUE) LIKE LOWER ('%7%') ESCAPE '~'
                  OR LOWER (val_000002.VALUE) LIKE LOWER ('%8%') ESCAPE '~'
                 )
        INNER JOIN recordsinglechoicevalue val_000012
            ON val_000012.sourceentityfk = ent_000000.pk
            AND val_000012.fieldfk = HEXTORAW ('8DB58FEACC79E011AD2400155D018001')
        INNER JOIN record_ rec_000014
            ON rec_000014.pk = val_000012.recordfk
            AND rec_000014.entityfk = val_000012.sourceentityfk
            AND rec_000014.recordtypefk = HEXTORAW ('7EB58FEACC79E011AD2400155D018001')
        INNER JOIN
           (SELECT
                MAX (rec_000016.TIMESTAMP) AS TIMESTAMP,
                rec_000016.entityfk AS groupkey
            FROM record_ rec_000016
                INNER JOIN recordsinglechoicevalue val_000018
                    ON val_000018.recordfk = rec_000016.pk
                    AND val_000018.sourceentityfk = rec_000016.entityfk
                    AND val_000018.fieldfk = HEXTORAW ('8DB58FEACC79E011AD2400155D018001')
            WHERE 1 = 1
                AND rec_000016.recordtypefk = HEXTORAW ('7EB58FEACC79E011AD2400155D018001')
            GROUP BY rec_000016.entityfk
            ) tbl_000020
            ON tbl_000020.TIMESTAMP = rec_000014.TIMESTAMP
            AND tbl_000020.groupkey = rec_000014.entityfk
        INNER JOIN recordsinglechoicevalue val_000021
            ON val_000021.sourceentityfk = ent_000000.pk
            AND val_000021.fieldfk = HEXTORAW ('AF5F45338C58DE4CB3833AD9D54351A7')
        INNER JOIN record_ rec_000023
            ON rec_000023.pk = val_000021.recordfk
            AND rec_000023.entityfk = val_000021.sourceentityfk
            AND rec_000023.recordtypefk = HEXTORAW ('7EB58FEACC79E011AD2400155D018001')
        JOIN
           (SELECT   MIN (rec_000025.TIMESTAMP) AS TIMESTAMP, rec_000025.entityfk AS groupkey
                FROM record_ rec_000025 INNER JOIN recordsinglechoicevalue val_000027
                     ON val_000027.recordfk = rec_000025.pk
                   AND val_000027.sourceentityfk = rec_000025.entityfk
                   AND val_000027.fieldfk = HEXTORAW ('AF5F45338C58DE4CB3833AD9D54351A7')
               WHERE 1 = 1 AND rec_000025.recordtypefk = HEXTORAW ('7EB58FEACC79E011AD2400155D018001')
            GROUP BY rec_000025.entityfk) tbl_000029
            ON tbl_000029.TIMESTAMP = rec_000023.TIMESTAMP
            AND tbl_000029.groupkey = rec_000023.entityfk
            AND val_000021.choicefk IN
                (HEXTORAW ('A038588B67E6C6418360E92AB231EA30'),
                 HEXTORAW ('DA6E7059FB243F40B97CF91BA75EE532'),
                 HEXTORAW ('DA6BE7C37D157A4BBD34032F76983916')
                )
        INNER JOIN record_ val_000033
            ON val_000033.pk = rec_000023.pk
            AND val_000033.entityfk = rec_000023.entityfk
            AND val_000033.TIMESTAMP IS NOT NULL
            AND val_000033.recordtypefk IN (SELECT rt.pk
                                           FROM recordtype rt JOIN recordtypemember rtm ON rtm.recordtypefk = rt.pk
                                          WHERE rtm.fieldfk = HEXTORAW ('83B58FEACC79E011AD2400155D018001'))
            AND val_000033.TIMESTAMP >= TO_DATE ('1/1/1925 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
        INNER JOIN recordsinglechoicevalue val_000036
            ON val_000036.sourceentityfk = ent_000000.pk
            AND val_000036.fieldfk = HEXTORAW ('125A01C9DAF726448D8DB1F7B27091DE')
        INNER JOIN record_ rec_000038
            ON rec_000038.pk = val_000036.recordfk
            AND rec_000038.entityfk = val_000036.sourceentityfk
            AND rec_000038.recordtypefk = HEXTORAW ('7EB58FEACC79E011AD2400155D018001')
        JOIN
            (SELECT
                MIN (rec_000040.TIMESTAMP) AS TIMESTAMP, rec_000040.entityfk AS groupkey
                FROM record_ rec_000040 INNER JOIN recordsinglechoicevalue val_000042
                     ON val_000042.recordfk = rec_000040.pk
                   AND val_000042.sourceentityfk = rec_000040.entityfk
                   AND val_000042.fieldfk = HEXTORAW ('125A01C9DAF726448D8DB1F7B27091DE')
               WHERE 1 = 1 AND rec_000040.recordtypefk = HEXTORAW ('7EB58FEACC79E011AD2400155D018001')
            GROUP BY rec_000040.entityfk) tbl_000044
            ON tbl_000044.TIMESTAMP = rec_000038.TIMESTAMP
            AND tbl_000044.groupkey = rec_000038.entityfk
            AND val_000036.choicefk IN
                (HEXTORAW ('9DDEB479A5F44C4AB9C03B0FBE2CCD89'),
                 HEXTORAW ('723CBD322AB52B439E9160F29BB27E17'),
                 HEXTORAW ('492F484AD5FED04893F82F4824F1B5E2'),
                 HEXTORAW ('C3F9C1F514AFB146A0F4BABAA2577B84')
                )
        INNER JOIN record_ val_000049
            ON val_000049.entityfk = ent_000000.pk
            AND 'Diagnosis' IS NOT NULL
            AND val_000049.recordtypefk IN (SELECT rt.pk
                                           FROM recordtype rt JOIN recordtypemember rtm ON rtm.recordtypefk = rt.pk
                                          WHERE rtm.fieldfk = HEXTORAW ('F85D675BF5D04448A608F9479A653BD3'))
     WHERE 1 = 1
     AND ent_000000.entitytypefk = HEXTORAW ('66B9B948F000DB1192370014A5726350')
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1     46.20      46.48          1          5          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.43       3.37       1103      30321          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4     46.63      49.86       1104      30326          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 144  
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             1          1          1  SORT AGGREGATE (cr=30321 pr=1103 pw=0 time=3374927 us)
           863        863        863   VIEW  VM_NWVW_1 (cr=30321 pr=1103 pw=0 time=3374893 us cost=1331 size=10 card=1)
           863        863        863    HASH GROUP BY (cr=30321 pr=1103 pw=0 time=3374768 us cost=1331 size=581 card=1)
          2589       2589       2589     NESTED LOOPS  (cr=30321 pr=1103 pw=0 time=4989741 us cost=1330 size=581 card=1)
          2589       2589       2589      NESTED LOOPS  (cr=24979 pr=1103 pw=0 time=4939535 us cost=1326 size=568 card=1)
          2589       2589       2589       NESTED LOOPS  (cr=20554 pr=1103 pw=0 time=4923377 us cost=1324 size=515 card=1)
          2589       2589       2589        NESTED LOOPS  (cr=15814 pr=1090 pw=0 time=4840683 us cost=1319 size=502 card=1)
          2589       2589       2589         NESTED LOOPS SEMI (cr=11379 pr=1090 pw=0 time=4823535 us cost=1317 size=449 card=1)
          2589       2589       2589          NESTED LOOPS  (cr=11377 pr=1089 pw=0 time=4816279 us cost=1316 size=415 card=1)
          2589       2589       2589           NESTED LOOPS  (cr=5716 pr=1034 pw=0 time=4684303 us cost=1311 size=402 card=1)
          3009       3009       3009            NESTED LOOPS  (cr=2861 pr=155 pw=0 time=251171 us cost=1303 size=1396 card=4)
          3018       3018       3018             NESTED LOOPS  (cr=1021 pr=114 pw=0 time=190779 us cost=1295 size=1156 card=4)
          3333       3333       3333              HASH JOIN  (cr=80 pr=74 pw=0 time=1051302 us cost=1291 size=952 card=4)
          4626       4626       4626               HASH JOIN  (cr=65 pr=63 pw=0 time=105663 us cost=1284 size=3570 card=21)
          1542       1542       1542                PARTITION HASH SINGLE PARTITION: 98 98 (cr=9 pr=7 pw=0 time=10885 us cost=4 size=19924 card=293)
          1542       1542       1542                 INLIST ITERATOR  (cr=9 pr=7 pw=0 time=10287 us)
          1542       1542       1542                  INDEX RANGE SCAN RDSINGLECHOICEVALUE_CFER_IDX PARTITION: 98 98 (cr=9 pr=7 pw=0 time=17409 us cost=4 size=19924 card=293)(object id 1274261)
         20507      20507      20507                HASH JOIN  (cr=56 pr=56 pw=0 time=79403 us cost=1280 size=120972 card=1186)
         16538      16538      16538                 INDEX RANGE SCAN ENTITY_TYPE_PK_IDX (cr=18 pr=18 pw=0 time=15823 us cost=19 size=562292 card=16538)(object id 1263004)
         20507      20507      20507                 NESTED LOOPS  (cr=38 pr=38 pw=0 time=31188 us cost=1257 size=9983624 card=146818)
             1          1          1                  SORT UNIQUE (cr=2 pr=2 pw=0 time=8330 us cost=2 size=34 card=1)
             1          1          1                   INDEX RANGE SCAN RDTYPEMEMBER_FIELD_RDTYPE_IDX (cr=2 pr=2 pw=0 time=8283 us cost=2 size=34 card=1)(object id 1263046)
         20507      20507      20507                  INDEX RANGE SCAN RECORD_TYPE_ENTITY_PK_IDX (cr=36 pr=36 pw=0 time=19171 us cost=1254 size=4805764 card=141346)(object id 1263050)
          3631       3631       3631               PARTITION HASH SINGLE PARTITION: 30 30 (cr=15 pr=11 pw=0 time=11957 us cost=6 size=224400 card=3300)
          3631       3631       3631                INLIST ITERATOR  (cr=15 pr=11 pw=0 time=11324 us)
          3631       3631       3631                 INDEX RANGE SCAN RDSINGLECHOICEVALUE_CFER_IDX PARTITION: 30 30 (cr=15 pr=11 pw=0 time=10169 us cost=6 size=224400 card=3300)(object id 1274261)
          3018       3018       3018              PARTITION HASH SINGLE PARTITION: 9 9 (cr=941 pr=40 pw=0 time=194777 us cost=1 size=51 card=1)
          3018       3018       3018               INDEX RANGE SCAN RDSINGLECHOICEVALUE_EFR_IDX PARTITION: 9 9 (cr=941 pr=40 pw=0 time=190011 us cost=1 size=51 card=1)(object id 1274390)
          3009       3009       3009             PARTITION HASH SINGLE PARTITION: 5 5 (cr=1840 pr=41 pw=0 time=106281 us cost=2 size=60 card=1)
          3009       3009       3009              TABLE ACCESS BY LOCAL INDEX ROWID RECORDTEXTVALUE PARTITION: 5 5 (cr=1840 pr=41 pw=0 time=102355 us cost=2 size=60 card=1)
          3018       3018       3018               INDEX RANGE SCAN RDTEXTVALUE_EFR_IDX PARTITION: 5 5 (cr=931 pr=40 pw=0 time=62766 us cost=1 size=0 card=1)(object id 1271843)
          2589       2589       2589            TABLE ACCESS BY INDEX ROWID RECORD_ (cr=2855 pr=879 pw=0 time=2480856 us cost=2 size=53 card=1)
          3009       3009       3009             INDEX UNIQUE SCAN RECORD_TYPE_ENTITY_PK_IDX (cr=1851 pr=40 pw=0 time=36566 us cost=1 size=0 card=1)(object id 1263050)
          2589       2589       2589           VIEW PUSHED PREDICATE  (cr=5661 pr=55 pw=0 time=203499 us cost=5 size=13 card=1)
          2589       2589       2589            FILTER  (cr=5661 pr=55 pw=0 time=199376 us)
          2589       2589       2589             SORT AGGREGATE (cr=5661 pr=55 pw=0 time=195814 us)
          2589       2589       2589              NESTED LOOPS  (cr=5661 pr=55 pw=0 time=188821 us)
          2589       2589       2589               NESTED LOOPS  (cr=3072 pr=55 pw=0 time=176479 us cost=5 size=73 card=1)
          2589       2589       2589                VIEW  VW_GBF_11 (cr=1244 pr=55 pw=0 time=162103 us cost=3 size=20 card=1)
          2589       2589       2589                 SORT GROUP BY (cr=1244 pr=55 pw=0 time=159604 us cost=3 size=51 card=1)
          2589       2589       2589                  PARTITION HASH SINGLE PARTITION: 98 98 (cr=1244 pr=55 pw=0 time=136951 us cost=2 size=51 card=1)
          2589       2589       2589                   INDEX RANGE SCAN RDSINGLECHOICEVALUE_EFR_IDX PARTITION: 98 98 (cr=1244 pr=55 pw=0 time=131850 us cost=2 size=51 card=1)(object id 1274390)
          2589       2589       2589                INDEX UNIQUE SCAN RECORD_TYPE_ENTITY_PK_IDX (cr=1828 pr=0 pw=0 time=10320 us cost=1 size=0 card=1)(object id 1263050)
          2589       2589       2589               TABLE ACCESS BY INDEX ROWID RECORD_ (cr=2589 pr=0 pw=0 time=8496 us cost=2 size=53 card=1)
             1          1          1          INDEX RANGE SCAN RDTYPEMEMBER_FIELD_RDTYPE_IDX (cr=2 pr=1 pw=0 time=5386 us cost=1 size=34 card=1)(object id 1263046)
          2589       2589       2589         TABLE ACCESS BY INDEX ROWID RECORD_ (cr=4435 pr=0 pw=0 time=13414 us cost=2 size=53 card=1)
          2589       2589       2589          INDEX UNIQUE SCAN RECORD_TYPE_ENTITY_PK_IDX (cr=1846 pr=0 pw=0 time=7912 us cost=1 size=0 card=1)(object id 1263050)
          2589       2589       2589        VIEW PUSHED PREDICATE  (cr=4740 pr=13 pw=0 time=90460 us cost=5 size=13 card=1)
          2589       2589       2589         FILTER  (cr=4740 pr=13 pw=0 time=87730 us)
          2589       2589       2589          SORT AGGREGATE (cr=4740 pr=13 pw=0 time=85239 us)
          2589       2589       2589           NESTED LOOPS  (cr=4740 pr=13 pw=0 time=79732 us)
          2589       2589       2589            NESTED LOOPS  (cr=2151 pr=13 pw=0 time=70400 us cost=5 size=73 card=1)
          2589       2589       2589             VIEW  VW_GBF_17 (cr=293 pr=13 pw=0 time=58750 us cost=3 size=20 card=1)
          2589       2589       2589              SORT GROUP BY (cr=293 pr=13 pw=0 time=56528 us cost=3 size=51 card=1)
          2589       2589       2589               PARTITION HASH SINGLE PARTITION: 30 30 (cr=293 pr=13 pw=0 time=40585 us cost=2 size=51 card=1)
          2589       2589       2589                INDEX RANGE SCAN RDSINGLECHOICEVALUE_EFR_IDX PARTITION: 30 30 (cr=293 pr=13 pw=0 time=35916 us cost=2 size=51 card=1)(object id 1274390)
          2589       2589       2589             INDEX UNIQUE SCAN RECORD_TYPE_ENTITY_PK_IDX (cr=1858 pr=0 pw=0 time=8289 us cost=1 size=0 card=1)(object id 1263050)
          2589       2589       2589            TABLE ACCESS BY INDEX ROWID RECORD_ (cr=2589 pr=0 pw=0 time=6075 us cost=2 size=53 card=1)
          2589       2589       2589       TABLE ACCESS BY INDEX ROWID RECORD_ (cr=4425 pr=0 pw=0 time=11906 us cost=2 size=53 card=1)
          2589       2589       2589        INDEX UNIQUE SCAN RECORD_TYPE_ENTITY_PK_IDX (cr=1836 pr=0 pw=0 time=7096 us cost=1 size=0 card=1)(object id 1263050)
          2589       2589       2589      VIEW PUSHED PREDICATE  (cr=5342 pr=0 pw=0 time=44755 us cost=4 size=13 card=1)
          2589       2589       2589       FILTER  (cr=5342 pr=0 pw=0 time=42218 us)
          2589       2589       2589        SORT AGGREGATE (cr=5342 pr=0 pw=0 time=39971 us)
          2589       2589       2589         NESTED LOOPS  (cr=5342 pr=0 pw=0 time=34879 us)
          2589       2589       2589          NESTED LOOPS  (cr=2753 pr=0 pw=0 time=27157 us cost=4 size=104 card=1)
          2589       2589       2589           PARTITION HASH SINGLE PARTITION: 9 9 (cr=923 pr=0 pw=0 time=17390 us cost=2 size=51 card=1)
          2589       2589       2589            INDEX RANGE SCAN RDSINGLECHOICEVALUE_EFR_IDX PARTITION: 9 9 (cr=923 pr=0 pw=0 time=13142 us cost=2 size=51 card=1)(object id 1274390)
          2589       2589       2589           INDEX UNIQUE SCAN RECORD_TYPE_ENTITY_PK_IDX (cr=1830 pr=0 pw=0 time=6697 us cost=1 size=0 card=1)(object id 1263050)
          2589       2589       2589          TABLE ACCESS BY INDEX ROWID RECORD_ (cr=2589 pr=0 pw=0 time=4750 us cost=2 size=53 card=1)
    
    ********************************************************************************
    
    SQL ID: bh7jda5rbamvk Plan Hash: 0
    
    ALTER SESSION SET sql_trace=FALSE
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.02       0.02          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.02       0.02          0          0          0           0
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 144  
    
    *********************************
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        4     46.23      46.51          1          5          0           0
    Execute      5      0.00       0.00          0          0          0           1
    Fetch        2      0.43       3.37       1103      30321          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       11     46.66      49.89       1104      30326          0           2
    
    Misses in library cache during parse: 3
    Misses in library cache during execute: 1
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       86      0.00       0.00          0          0          0           0
    Execute   2312      0.04       0.06          2         34          0           0
    Fetch     2894     21.61      25.26      11442      45142          0        9699
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     5292     21.66      25.34      11444      45176          0        9699
    
    Misses in library cache during parse: 12
    Misses in library cache during execute: 11
    
        9  user  SQL statements in session.
       40  internal SQL statements in session.
       49  SQL statements in session.
    ***********************************
    Trace file: orcl_ora_25306.trc
    Trace file compatibility: 11.1.0.7
    Sort options: default
    
           1  session in tracefile.
           9  user  SQL statements in trace file.
          40  internal SQL statements in trace file.
          49  SQL statements in trace file.
          37  unique SQL statements in trace file.
     4463910  lines in trace file.
          75  elapsed seconds in trace file.
  • 23. Re: Hard Parse Time - too long...
    JohnWatson Guru
    Currently Being Moderated
    I reckon there is something wrong with the environment.I've already said that I don't think any statement should take 45s to parse, and the fact that the RBO is as slow proves, I think, that there is a non-Oracle problem. It reminds of my first encounter with the T3 chip, which just didn't perform some tasks as well as the much older v490.
    If you use dbms_sqldiag.export_sql_testcase to reproduce the environment on any old PC or laptop you happen to have, I reckon it will parse it instantaneously.
  • 24. Re: Hard Parse Time - too long...
    rp0428 Guru
    Currently Being Moderated
    >
    If you use dbms_sqldiag.export_sql_testcase to reproduce the environment on any old PC or laptop you happen to have, I reckon it will parse it instantaneously.
    >
    For those not familiar with that package and procedure here is a link to an article by Maria Colgan, a member of the Optimizer developer team and author of many 'Inside the Oracle Optimizer - Removing The Black Magic' blogs.

    http://optimizermagic.blogspot.com/2008_03_01_archive.html
  • 25. Re: Hard Parse Time - too long...
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    al**** wrote:
    SQL ID: 5ngnyxjbgpuyh Plan Hash: 2967171960
    
    SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 
    NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') 
    NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), 
    NVL(SUM(C2),:"SYS_B_01") 
    FROM
    (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("VAL_000002") FULL("VAL_000002") 
    NO_PARALLEL_INDEX("VAL_000002") */ :"SYS_B_02" AS C1, CASE WHEN 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_03" ESCAPE :"SYS_B_04" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_05" ESCAPE :"SYS_B_06" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_07" ESCAPE :"SYS_B_08" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_09" ESCAPE :"SYS_B_10" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_11" ESCAPE :"SYS_B_12" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_13" ESCAPE :"SYS_B_14" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_15" ESCAPE :"SYS_B_16" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_17" ESCAPE :"SYS_B_18" THEN 
    :"SYS_B_19" ELSE :"SYS_B_20" END AS C2 FROM "BISWEBB"."RECORDTEXTVALUE" 
    SAMPLE BLOCK (:"SYS_B_21" , :"SYS_B_22") SEED (:"SYS_B_23") "VAL_000002" 
    WHERE LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_24" ESCAPE :"SYS_B_25" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_26" ESCAPE :"SYS_B_27" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_28" ESCAPE :"SYS_B_29" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_30" ESCAPE :"SYS_B_31" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_32" ESCAPE :"SYS_B_33" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_34" ESCAPE :"SYS_B_35" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_36" ESCAPE :"SYS_B_37" OR 
    LOWER("VAL_000002"."VALUE") LIKE :"SYS_B_38" ESCAPE :"SYS_B_39") SAMPLESUB
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        5      0.00       0.00          0          0          0           0
    Execute      5      0.00       0.00          0          0          0           0
    Fetch        5     21.41      24.14      11108      37331          0           5
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       15     21.41      24.15      11108      37331          0           5
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 144     (recursive depth: 1)
    Number of plan statistics captured: 3
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
    1          1          1  SORT AGGREGATE (cr=7466 pr=3703 pw=0 time=5230126 us)
    3137126    3137126    3137126   PARTITION HASH ALL PARTITION: 1 128 (cr=7466 pr=3703 pw=0 time=2547843 us cost=18758 size=131597088 card=3133264)
    3137126    3137126    3137126    TABLE ACCESS SAMPLE RECORDTEXTVALUE PARTITION: 1 128 (cr=7466 pr=3703 pw=0 time=2372509 us cost=18758 size=131597088 card=3133264)
    
    ***************************************
    It's interesting to note that this statement (which is a dynamic sampling statement) has been executed 5 times - at the same sample size, with the same number of rows. This is claiming responsibility for 24 .14 seconds of your parse time (elapsed) and 21.41 seconds CPU. It's possible, I suppose, that the execution of a sampled full tablescan is actually accounting for less time that it actually takes - though that seems a little unlikely.

    As a clue to the full impact of dynamic sampling, you could try running the statement with optimizer_dynamic_sampling set to zero. If the plan and the estimated stats (E-Rows) don't change, then this will give you some idea of the cumulative effect of the sampling.

    To be more confident of whether the sampling is the primary culprit, you would need to do a detailed study of the 10053, initially checking where the sampling happened and if it made a big difference to row estimates; then to see how the two traces differ in the number of join orders examined and transformation considered. It's not a trivial task, and in your position I would probably hand the problem over to Oracle support.


    Regards
    Jonahtan Lewis
  • 26. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    To try and kill two birds with one stone, I restored the database onto a different box. Different memory configuration, CPU count, etc.
    Where original box box had 16 CPUs, this one has 8. The numbers changed for the worse.

    I turned off dynamic sampling (set to 0), and optimizer did not do the LIKE portion of the query 5 times as before.
    Instead, the full statement itself produced the following:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1    120.88     121.28          0          0          0           0
    Execute      1      0.02     133.13          0          5          0           0
    Fetch        2      0.04       3.98          0        206          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4    120.96     258.40          0        211          0           1
    So, parse times are still there, regardless of dynamic sampling.
  • 27. Re: Hard Parse Time - too long...
    sb92075 Guru
    Currently Being Moderated
    Consider submitting Service Request

    http://optimizermagic.blogspot.com/2008_03_01_archive.html
  • 28. Re: Hard Parse Time - too long...
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    But since no two queries are alike in our app, that does not help us much :)
    Is it because value literals are different or queries are created dynamically?

    By a style how SQL is formatted I see it is not created by program.
    I assume that the SQL is coded statically in application, with plugging of value literals into it.

    The fact that your app is DB agnostic, and even when queries are created dynamically does not prevent you from using bind variables.

    If it is impossible to re-program the application consider to use CURSOR_SHARING=FORCE/SIMILAR so Oracle will do it for you.
    Sharing cursors should decrease hard parsing.
  • 29. Re: Hard Parse Time - too long...
    439902 Newbie
    Currently Being Moderated
    Queries are all different. It is not about the literals. SQL was formatted by me for ease of reading. The app that generates it, obviously, does not do it :)

    We ARE using bind variables in the application. I just plugged in the real values for readability/data type info. Otherwise, every literal that the app sends to the database is as bind variable. We do not include literals in the SQL itself.

    CURSOR_SHARING is currently set to EXACT.
    SIMILAR is being removed, so we are left with EXACT or FORCE.

    The issue is not so much that there is parsing of (almost) every query that hits the database, but, rather, the time it takes to parse.

    Thank you for your input.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points