105조회시에 12초 걸리는 경우
kwindow2 1학년

답변 채택시 : 0코아

답변 1 추천 0 조회 6052 2013.08.23
105건 조회하는데요
12초 걸리는 경우도 있고, 1초안에 조회되는 경우도 있습니다.
 
12초 걸리는 경우의 trace입니다.
- 물리적인 IO가 늘리다는거 이외에 다른 이유를 찾을 수 있을까요?
--

TKPROF: Release 11.2.0.3.0 - Development on Wed Aug 21 20:32:58 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: HISEDU_ora_47382576.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: 77xfzx37m45ua Plan Hash: 0
 
alter session set events '10357 trace name context forever, level 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        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: SYS
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        9.71          9.71
********************************************************************************
 
SQL ID: 3nkd3g3ju5ph1 Plan Hash: 853875749
 
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        1      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        5      0.00       0.00          0          6          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 OBJ$ (cr=3 pr=0 pw=0 time=35 us cost=4 size=86 card=1)
         0          0          0   INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=31 us cost=3 size=0 card=1)(object id 37)
 
********************************************************************************
 
SQL ID: 7ng34ruy5awxq Plan Hash: 3984801583
 
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        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        9      0.00       0.00          0         32          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         32          0           5
 
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
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT ORDER BY (cr=9 pr=0 pw=0 time=2567 us cost=7 size=384 card=2)
         1          1          1   HASH JOIN OUTER (cr=9 pr=0 pw=0 time=2543 us cost=6 size=384 card=2)
         1          1          1    NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=55 us cost=3 size=298 card=2)
         1          1          1     TABLE ACCESS CLUSTER IND$ (cr=4 pr=0 pw=0 time=46 us cost=2 size=194 card=2)
         1          1          1      INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=22 us cost=1 size=0 card=1)(object id 3)
         0          0          0     TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=4 us cost=1 size=52 card=1)
         0          0          0      INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=4 us cost=1 size=0 card=1)(object id 433)
         1          1          1    VIEW  (cr=4 pr=0 pw=0 time=68 us cost=3 size=43 card=1)
         1          1          1     SORT GROUP BY (cr=4 pr=0 pw=0 time=64 us cost=3 size=15 card=1)
         1          1          1      TABLE ACCESS CLUSTER CDEF$ (cr=4 pr=0 pw=0 time=25 us cost=2 size=15 card=1)
         1          1          1       INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=8 us cost=1 size=0 card=1)(object id 30)
 
********************************************************************************
 
SQL ID: 5n1fs4m2n2y0r Plan Hash: 992489688
 
select pos#,intcol#,col#,spare1,bo#,spare2,spare3
from
 icol$ where obj#=:1
 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       15      0.00       0.00          0         30          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.00          0         30          0          10
 
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
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS BY INDEX ROWID ICOL$ (cr=10 pr=0 pw=0 time=33 us cost=2 size=81 card=3)
         4          4          4   INDEX RANGE SCAN I_ICOL1 (cr=6 pr=0 pw=0 time=35 us cost=1 size=0 card=3)(object id 42)
 
********************************************************************************
 
SQL ID: 83taa7kaw59c1 Plan Hash: 2783779297
 
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        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch       58      0.00       0.00          0         27          0          54
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       63      0.00       0.00          0         27          0          54
 
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
---------- ---------- ----------  ---------------------------------------------------
        20         20         20  SORT ORDER BY (cr=4 pr=0 pw=0 time=101 us cost=3 size=944 card=16)
        20         20         20   TABLE ACCESS CLUSTER COL$ (cr=4 pr=0 pw=0 time=54 us cost=2 size=944 card=16)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=20 us cost=1 size=0 card=1)(object id 3)
 
********************************************************************************
 
SQL ID: 9gkq7rruycsjp Plan Hash: 2930975892
 
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        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          9          0           3
 
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=3 pr=0 pw=0 time=19 us cost=2 size=46 card=1)
         1          1          1   INDEX UNIQUE SCAN I_PARTOBJ$ (cr=2 pr=0 pw=0 time=12 us cost=1 size=0 card=1)(object id 565)
 
********************************************************************************
 
SQL ID: cbdfcfcp1pgtp Plan Hash: 2703454998
 
select intcol#, col# , type#, spare1, segcol#, charsetform
from
 partcol$  where obj# = :1 order by pos#
 

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.00       0.01          2          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.01          2          9          0           3
 
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=3 pr=1 pw=0 time=4343 us cost=3 size=27 card=1)
         1          1          1   TABLE ACCESS BY INDEX ROWID PARTCOL$ (cr=3 pr=1 pw=0 time=4329 us cost=2 size=27 card=1)
         1          1          1    INDEX RANGE SCAN I_PARTCOL$ (cr=2 pr=0 pw=0 time=2510 us cost=1 size=0 card=1)(object id 567)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.00          0.01
********************************************************************************
 
SQL ID: 1gu8t96d0bdmu Plan Hash: 2959582498
 
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        1      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         10          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0         10          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  NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=47 us cost=4 size=182 card=1)
         1          1          1   TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=33 us cost=2 size=143 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=20 us cost=1 size=0 card=1)(object id 3)
         0          0          0   TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=2 pr=0 pw=0 time=10 us cost=2 size=39 card=1)
         0          0          0    INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=2 pr=0 pw=0 time=9 us cost=1 size=0 card=1)(object id 431)
 
********************************************************************************
 
SQL ID: 130dvvr5s8bgn Plan Hash: 2800640262
 
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        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.05          9         24          0           8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.05          9         24          0           8
 
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: 2
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS BY INDEX ROWID TABPART$ (cr=4 pr=0 pw=0 time=903 us cost=3 size=656 card=4)
         4          4          4   INDEX RANGE SCAN I_TABPART_BOPART$ (cr=3 pr=0 pw=0 time=20 us cost=2 size=0 card=4)(object id 571)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                         9        0.00          0.05
********************************************************************************
 
SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3632979230
 
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        1      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          1         36          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.00       0.01          1         36          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: 2)
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS CLUSTER SEG$ (cr=3 pr=1 pw=0 time=8618 us cost=2 size=71 card=1)
         1          1          1   INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=1 pw=0 time=8603 us cost=1 size=0 card=1)(object id 9)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         1        0.00          0.00
********************************************************************************
 
SQL ID: 3ktacv9r56b51 Plan Hash: 458693102
 
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        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          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          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=0 pw=0 time=40 us cost=10 size=213 card=3)
         0          0          0   NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=26 us cost=9 size=213 card=3)
         0          0          0    TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=22 us cost=4 size=81 card=3)
         0          0          0     INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=0 pw=0 time=21 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=44 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: 872636971
 
select order#,columns,types
from
 access$ where d_obj#=: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          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          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=0 pw=0 time=16 us cost=3 size=168 card=7)
         0          0          0   INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=0 pw=0 time=14 us cost=2 size=0 card=7)(object id 108)
 
********************************************************************************
 
SQL ID: c3zymn7x3k6wy Plan Hash: 1164454930
 
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        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          2          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          2          8          0           4
 
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
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS BY INDEX ROWID INDPART$ (cr=4 pr=2 pw=0 time=14599 us cost=3 size=708 card=4)
         4          4          4   INDEX RANGE SCAN I_INDPART_BOPART$ (cr=3 pr=1 pw=0 time=8603 us cost=2 size=0 card=4)(object id 576)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.00          0.01
********************************************************************************
 
SQL ID: 96g93hntrzjtr Plan Hash: 841937906
 
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        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.01          2         12          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.01          2         12          0           3
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
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 HIST_HEAD$ (cr=4 pr=2 pw=0 time=15979 us)
         1          1          1   INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=3 pr=1 pw=0 time=5709 us)(object id 427)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.01          0.01
********************************************************************************
 
SQL ID: cb21bacyh3c7d Plan Hash: 3488560417
 
select metadata
from
 kopm$  where name='DB_FDO'
 

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          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1
 
Misses in library cache during parse: 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
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=12 us cost=1 size=108 card=1)
         1          1          1   INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=5 us cost=1 size=0 card=1)(object id 545)
 
********************************************************************************
 
SQL ID: 3w4qs0tbpmxr6 Plan Hash: 3924106966
 
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3
from
 cdef$ where robj#=:1
 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      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          4          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          4          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 CDEF$ (cr=2 pr=0 pw=0 time=25 us cost=2 size=99 card=3)
         0          0          0   INDEX RANGE SCAN I_CDEF3 (cr=2 pr=0 pw=0 time=22 us cost=1 size=0 card=3)(object id 55)
 
********************************************************************************
 
SQL ID: gx4mv66pvj3xz Plan Hash: 3886069984
 
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        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch       34      0.00       0.00          0         40          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       37      0.00       0.00          0         40          0          32
 
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
---------- ---------- ----------  ---------------------------------------------------
        12         12         12  TABLE ACCESS CLUSTER CDEF$ (cr=16 pr=0 pw=0 time=22 us cost=2 size=440 card=8)
         1          1          1   INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=10 us cost=1 size=0 card=1)(object id 30)
 
********************************************************************************
 
SQL ID: 53saa2zkr6wc3 Plan Hash: 2631433895
 
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
 ccol$ where con#=:1
 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     32      0.00       0.00          0          0          0           0
Fetch       68      0.00       0.01          1        136          0          36
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.00       0.01          1        136          0          36
 
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
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  TABLE ACCESS BY INDEX ROWID CCOL$ (cr=6 pr=1 pw=0 time=10026 us cost=3 size=18 card=1)
         2          2          2   INDEX RANGE SCAN I_CCOL1 (cr=4 pr=1 pw=0 time=10021 us cost=2 size=0 card=1)(object id 57)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         1        0.00          0.00
********************************************************************************
 
  SELECT
          otpt.elbulbodstat
        , otpt.ordtm
        , CASE WHEN
                    (case when count(ptbs.hngnm) over(partition by ptbs.hngnm) > 1
                    then
                        case when count(ptbs.hngnm) over(partition by ptbs.pid, ptbs.hngnm) > 1
                        then
                            1
                        else
                         count(ptbs.hngnm) over(partition by ptbs.hngnm)
                        end
                    else
                     count(ptbs.hngnm) over(partition by ptbs.hngnm)
                    end) > 1
                   THEN
                        ptbs.hngnm || '(.)'
                   ELSE
                        ptbs.hngnm
                   END AS hngnm
        , otpt.pid
        , otpt.orddeptcd
        , ptbs.sex || '/'
                   || COM.FN_ZZ_GETAGE_EMR(ptbs.rrgstno1, ptbs.rrgstno2, otpt.orddd, 'A', ptbs.brthdd)   AS sa
        , otpt.fsexamflag
        , otpt.etcordflag
              , (SELECT term.termengnm diagengnm
                   FROM emr.mmohdiag diag,                     -- .........
                        emr.mrtmterm term                      -- .....
                  WHERE diag.pid       = otpt.pid
                    AND diag.orddd     = otpt.orddd
                    AND diag.cretno    = otpt.cretno
                    AND diag.orddeptcd = otpt.orddeptcd
                    AND diag.instcd    = otpt.instcd
                    AND diag.diagcd    = term.termcd
                    AND diag.diagdd   >= term.termfromdd
                    AND diag.diagdd   <= term.termtodd
                    AND diag.instcd    = term.instcd
                    AND diag.diaghistcd     = 'O'
                    AND diag.diagtypecd     = 'D'
                    AND diag.diagkindcdflag = 'M'
                    AND ROWNUM = 1)                          AS diagnm                                             -- ...
        , otpt.instcd
        , otpt.orddd
        , otpt.cretno
        , otpt.orddrid
              , otpt.prcptdayaftrcptyn
              , otpt.rcptvipresncd
              , otpt.rcptvipetcresn
              , otpt.medamtpostyn
              , CASE WHEN (SELECT a.rcptdelivefact
                             FROM pam.pmchcapm a
                            WHERE a.instcd = otpt.instcd
                              AND a.pid    = otpt.pid
                              AND a.civilaplendflagcd IN ('Y', 'R')
                              AND a.drdelive = 'Y'
                              AND a.civilaplclamdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND a.civilenddd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND rownum = 1  ) is not NULL THEN 'Y'
                                                            ELSE 'N'
                     END                                                                     AS civilap
     ,   DECODE (otpt.ordstartdt, '00000000000000', null, SUBSTR(otpt.ordstartdt, 9, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 11, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 13, 2)         ) AS ordstartdt          -- ......
              ,   CASE WHEN otpt.dracptyn ='Y' THEN (DECODE (otpt.dracptdt,   '-', null, SUBSTR(otpt.dracptdt, 9, 2)  ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 11, 2) ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 13, 2)   )  )
                        END                                      AS dracptdt
              ,   CASE WHEN otpt.nursacptyn ='Y' THEN (DECODE (otpt.nursacptdt, '-', null, SUBSTR(otpt.nursacptdt, 9, 2)  ||':'||
                                                                                           SUBSTR(otpt.nursacptdt, 11, 2)         ) )
                        END                                AS nursacptdt
              ,   (SELECT CASE WHEN count(1) > 0 THEN  'Y'
                               ELSE 'N'
                           END
                     FROM pam.pmohotpt totp
                    WHERE otpt.pid           =       totp.pid
                      AND otpt.instcd        =       totp.instcd
                      AND otpt.orddd         >=      to_char(to_date(totp.orddd,  'YYYYMMDD')-7, 'YYYYMMDD')
                      AND otpt.orddd         <=       totp.orddd
                      AND otpt.cretno        !=       totp.cretno
                      AND totp.ordtype       =       'E'
                      AND rownum             =        1 )                       AS   eryn                 -- ER..
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                              ELSE 'N'
                         END
                    FROM emr.mmrhcnstinfo info
                   WHERE info.pid           =        otpt.pid
                     AND info.instcd        =        otpt.instcd
                     AND info.orddd         =        otpt.orddd
                     AND info.cretno        =        otpt.cretno
                     AND info.cnststat      in       ('12', '20', '21', '22', '30', '31', '32', '40')
                     AND rownum             =        1 )                        AS   coordyn            -- ....
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                             ELSE 'N'
                         END
                   FROM dual
                  WHERE (SELECT  CASE WHEN count(1) > 0 THEN 'Y'
                                      ELSE 'N'
                                  END
                           FROM  emr.mmrmformrec mrec,
                                 emr.mmrdrechist hist
                          WHERE  mrec.instcd       =      hist.instcd
                            AND  mrec.formrecseq   =      hist.formrecseq
                            AND  mrec.lastrechistseq = hist.rechistseq
                            AND  mrec.delyn        =      'N'
                            AND  mrec.valiyn       =      'Y'
                            AND  mrec.pid          =      otpt.pid
                            AND  mrec.orddd        =      otpt.orddd
                            AND  mrec.cretno       =      otpt.cretno
                            AND  hist.ESPISEQ      =      '0'
                            AND  rownum            =       1 ) ='Y'
                     OR
                        (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                                     ELSE 'N'
                                 END
                           FROM emr.mmohoprc oprc
                          WHERE oprc.instcd          =    otpt.instcd
                            AND oprc.pid             =    otpt.pid
                            AND oprc.orddd           =    otpt.orddd
                            AND oprc.cretno          =    otpt.cretno
                            AND oprc.prcphistcd      = 'T'
                            AND rownum               =    1 ) = 'Y'
                            AND rownum = 1 )                                  AS tempsaveyn          -- ......
              ,  (SELECT CASE WHEN  sasm.choiflag in ( 'B',  'C')  THEN 'Y'
                             ELSE 'N'
                         END
                        FROM pam.pmcmspif sasm --  pam.pmcmsasm sasm
                       WHERE sasm.instcd     =  otpt.instcd
                         AND sasm.pid        =  otpt.pid
                         AND sasm.orddeptcd  =  otpt.orddeptcd
                         AND sasm.orddrid    =  otpt.orddrid
                         AND sasm.ioflag     =  'O'
                         AND sasm.histstat   =  'Y'
                         AND sasm.specordyn  =  'Y'
                         AND sasm.fromdd     <= otpt.orddd
                         AND sasm.todd       >= otpt.orddd
                         AND rownum          =  1 )                          AS specordtype        --  ....
    ,(SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
               END
        FROM dual
       WHERE (
            SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohoprc oprc
             WHERE oprc.pid          = otpt.pid
               AND oprc.prcpdd       >=     (SELECT NVL(MAX(orddd), '20130605') AS fromdd
                                               FROM pam.pmohotpt
                                              WHERE instcd = '053'
                                                AND pid = otpt.pid
                                                AND orddd < '20130605'
                                                AND elbulbodstat = '2'
                                                AND histstat = 'R')
               AND oprc.prcpdd        <= otpt.orddd
               AND oprc.prcphistcd   = 'O'
               AND oprc.prcpflag     = '1'
               AND oprc.prcpstatcd   > '700'
               AND ROWNUM = 1 ) = 'Y'
          OR
            (SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohiprc iprc
             WHERE iprc.pid          = otpt.pid
               AND iprc.prcpdd       >=     ( SELECT NVL(MAX(dschdd), '20130605') AS formdd
                                                FROM pam.pmihinpt
                                               WHERE instcd = '053'
                                                 AND pid = otpt.pid
                                                 AND dschdd != '99991231'
                                                 AND histstat = 'Y')
               AND iprc.prcpdd        <= otpt.orddd
               AND iprc.prcphistcd   = 'O'
               AND iprc.prcpflag     = '1'
               AND iprc.prcpstatcd   > '700'
               AND ROWNUM = 1) = 'Y') as rsltyn         -- ....
      , otpt.insukind as insukind
    --, DECODE(code.cdid, '11','..', '21','..', '22','..', substr(code.cdnm,0,2)) insukind  --..(...2.....)
    , (SELECT NVL(MAX(a.orddd), otpt.orddd)
                     FROM pam.pmohotpt a
                    WHERE a.instcd = ptbs.instcd
                      AND a.pid = ptbs.pid
                      AND a.elbulbodstat = '2' --...... ..
                      AND a.orddd < otpt.orddd
                      AND a.histstat in ( 'R', 'T' )
                      ) as recsrchfromdd       --.... ....
                , (SELECT CASE WHEN count(*) = 1 then 'Y'
                               ELSE 'N'
                               END
                      FROM emr.mmrmformrec a,
                           emr.mmrdrechist b
                     WHERE a.instcd = otpt.instcd
                       AND a.pid = otpt.pid
                       AND a.chosflag = 'O'
                       AND a.lastformrecdd >= (SELECT NVL(MAX(a.orddd), otpt.orddd)
                                                 FROM pam.pmohotpt a
                                                WHERE a.instcd = otpt.instcd
                                                  AND a.pid = otpt.pid
                                                  AND a.orddeptcd = otpt.orddeptcd
                                                  AND a.orddrid = otpt.orddrid
                                                  AND a.orddd < otpt.orddd)
                       AND a.lastformrecdd <= otpt.orddd
                       AND a.valiyn = 'Y'
                       AND a.delyn = 'N'
                       AND b.instcd  = a.instcd
                       AND b.rechistseq = a.lastrechistseq
                       AND b.espiseq != 0
                       AND rownum = 1) as recyn
                  , (SELECT CASE WHEN count(*) = 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                       FROM emr.mmohoprc oprc
                      WHERE oprc.instcd = otpt.instcd
                        AND oprc.pid = otpt.pid
                        AND oprc.orddd = otpt.orddd
                        AND oprc.cretno = otpt.cretno
                        AND oprc.prcphistcd IN ('O', 'X', 'M')      -- Dump. ....
            AND oprc.prcpkindcd NOT IN ('30', '35', '40') -- ..... .. . .... ... ..
                        AND rownum = 1
                        ) as prcpyn
                   , (SELECT CASE WHEN count(*) >= 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                        FROM emr.mmrdmemo memo
                       WHERE memo.instcd = otpt.instcd
                         AND memo.pid = otpt.pid
                   --AND memo.deptcd = otpt.orddeptcd
                   AND (memo.memo IS NOT NULL OR nursememo IS NOT NULL)
                   AND memo.useflag IN ('P', 'D') --.... .. .. (P: .., D: ...)
                   ) as memoyn
                   , (SELECT  RTRIM (XMLAGG (XMLELEMENT ("a", cccl.conctypenm || ',')).EXTRACT ('//text()').getstringval (), ',') a
                        FROM  emr.mmohccpt ccpt
                            , emr.mmomcccl cccl
                       WHERE ccpt.conckindcd = cccl.conckindcd
                         AND ccpt.conctrgtcd = cccl.conctrgtcd
                         AND ccpt.conctypecd = cccl.conctypecd
                         AND ccpt.instcd     = cccl.instcd
                         AND ccpt.valifromdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                         AND ccpt.valitodd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                      AND ccpt.instcd      = '053'
                      AND ccpt.pid         = otpt.pid
                      AND ccpt.conctrgtcd   = CASE WHEN ccpt.conckindcd = 'D'  THEN otpt.orddeptcd
                                                                             ELSE '49170'
                                                                           END   )           AS conctypenm --....
       , otpt.rsrvflag
       , DECODE(   (SELECT 1
                         FROM emr.mrchmrhs mrhs
                        WHERE mrhs.instcd = otpt.instcd
                          AND mrhs.pid = otpt.pid
                          AND mrhs.ioflag = otpt.ordtype
                          AND mrhs.chrtflag = 'P'
                          AND mrhs.deldt >= SYSTIMESTAMP
                          AND ROWNUM  =1) , 1, 'Y', ''
                ) AS chartscanyn, --chart scan ..
       CASE WHEN DRACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(SUBSTR(DRACPTDT,9,4),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         WHEN NURSACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(TO_CHAR(sysdate,'hh24mi'),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         ELSE '-' END AS waittm--.... lsh
        /*,(SELECT CASE WHEN (SELECT hospgrade FROM ast.arhmchsp
                                        WHERE coophospcd = reqp.coophospcd
                                          AND instcd = reqp.instcd)  IN ('C1', 'C2', 'C3') THEN 'refer(..)'
                                 ELSE 'refer' END
                       FROM ast.arcmreqp reqp
                      WHERE reqp.pid = otpt.pid
                        AND reqp.instcd = otpt.instcd
                        AND reqp.orddeptcd = otpt.orddeptcd
                        AND otpt.orddd BETWEEN reqp.rgstdd and reqp.enddd
                        AND reqp.deldd = '00000000'
                        AND ROWNUM = 1)*/
                  ,DECODE( otpt.RQSTFLAG, '1','Y', '-')            AS refer
                        , DECODE((SELECT 'Y'
                           FROM emr.mnohdlvt z
                           WHERE z.instcd = otpt.instcd
                           AND z.pid = otpt.pid
                           AND z.seqno = (SELECT max(y.seqno)
                                            FROM emr.mnohdlvt y
                                           WHERE y.instcd = z.instcd
                                             AND y.pid = z.pid
                                             AND y.statcd = 'Y'
                                             AND rownum = 1)
                           AND (z.EXPTDLIVDD > = to_char(sysdate, 'YYYYMMDD') OR z.EXPTDLIVDD IS NULL) --....
                         ),'Y','01', case when ptbs.sex = 'F' and to_number( COM.FN_ZZ_GETAGE('-', '-', otpt.orddd, 'B', ptbs.brthdd)) between 15 and 55 then '02'
                            else '' end ) AS gravdpsbldliv  -- ....//  /*.... 01 .... 02 .... null. .. > .. ...... */
                , CASE WHEN  otpt.suppkind  IN ( '06' , '11', '61' , '62' , '63' ) THEN  '..'
                       WHEN  otpt.suppkind = '07' THEN '..'
                       WHEN  otpt.suppkind  IN ( '50' , '51' ) THEN  '..'
                       ELSE '-'
                  END as serdiag
               ,  decode( ( SELECT 'C'
                              FROM emr.mmohoprc oprc
                             WHERE oprc.instcd = otpt.instcd
                               AND oprc.pid = otpt.pid
                               AND oprc.orddd = otpt.orddd
                               AND oprc.cretno = otpt.cretno
                               AND oprc.prcphistcd = 'O'
                               AND oprc.prcpcd like 'XSCAN%'
                               AND ROWNUM = 1), 'C', 'C', decode(otpt.outercdrgstyn, 'N', '', otpt.outercdrgstyn )) as outercdrgstyn -- ....
               , decode(ptbs.kioskrcptnoyn, 'N','Y','') as kioskrcptnoyn  --....
               , CASE WHEN otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'E', 'S', 'P', 'B') AND otpt.etcordflag NOT IN ('M','J','N', '07') THEN ''
                      ELSE 'Y'
                 END as tempacpt
               , nvl((select bmtflag from emr.mmohpbmt pbmt WHERE pbmt.instcd = otpt.instcd AND pbmt.pid = otpt.pid and pbmt.histcd='O'),'-') as bmtflag
               , LPAD(NVL2(owgm.ordrsrvseqno,owgm.ordrsrvseqno,'0000'),4,0) as ordrsrvseqno     --......
               , owgm.ordrsrvtm                                                                 --......
          FROM  pam.pmohotpt otpt
        , pam.pmcmptbs ptbs
        , com.zbcmcode code
        , emr.mnvhwgim owgm
   WHERE otpt.histstat IN ('R', 'T')                                            -- .., .......
     AND otpt.ordtype  IN ('O', 'E')
     AND otpt.pid    = ptbs.pid
     AND otpt.instcd  = ptbs.instcd
     AND code.cdid     = otpt.insukind
     AND code.cdgrupid = 'P0008'
     AND  owgm.instcd(+) = otpt.instcd
           AND  owgm.pid(+)    = otpt.pid
        AND  owgm.orddd(+)  = otpt.orddd
        AND  owgm.cretno(+) = otpt.cretno
           AND otpt.orddd    = '20130605'
           AND otpt.instcd   = '053'
           AND otpt.orddeptcd    = 'ME'
           AND otpt.orddrid      = '49170'
           AND otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'S', 'P', 'B') --..., ...., ...., ....., .... .., ......, ..... ..
              --.... ... ..... .. ..
           AND NVL(otpt.etcordflag, '-') NOT IN ('M','J', 'N', '07')                 -- ...., .., .... ..
      ORDER BY owgm.ordrsrvtm, ordrsrvseqno, otpt.orddd, otpt.ordtm, otpt.NURSACPTDT , otpt.fstacptdt
     /* himed/his/emr/prcpmngtmgr/etcprcpmngtmgt/dao/sqls/etcprcpmngtdao_sqls.xml getSpeedOutPatList */
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.08          0         78          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.20      12.68       2506       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.25      12.77       2506       8175          0         105
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       104        104        104  COUNT STOPKEY (cr=838 pr=253 pw=0 time=1532038 us)
       104        104        104   NESTED LOOPS  (cr=838 pr=253 pw=0 time=1531311 us)
       104        104        104    NESTED LOOPS  (cr=734 pr=231 pw=0 time=1387057 us cost=5 size=131 card=1)
       104        104        104     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=422 pr=211 pw=0 time=1274024 us cost=4 size=58 card=1)
       104        104        104      TABLE ACCESS BY LOCAL INDEX ROWID MMOHDIAG PARTITION: KEY KEY (cr=422 pr=211 pw=0 time=1273694 us cost=4 size=58 card=1)
       161        161        161       INDEX RANGE SCAN IX_MMOHDIAG_01 PARTITION: KEY KEY (cr=315 pr=104 pw=0 time=648910 us cost=3 size=0 card=1)(object id 796990)
       104        104        104     INDEX RANGE SCAN PK_MRTMTERM (cr=312 pr=20 pw=0 time=112803 us cost=1 size=0 card=1)(object id 766242)
       104        104        104    TABLE ACCESS BY INDEX ROWID MRTMTERM (cr=104 pr=22 pw=0 time=143904 us cost=2 size=73 card=1)
         0          0          0  COUNT STOPKEY (cr=213 pr=1 pw=0 time=8139 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=213 pr=1 pw=0 time=7852 us cost=3 size=163 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCHCAPM PARTITION: KEY KEY (cr=213 pr=1 pw=0 time=7563 us cost=3 size=163 card=1)
         1          1          1     INDEX RANGE SCAN PK_PMCHCAPM PARTITION: KEY KEY (cr=212 pr=0 pw=0 time=996 us cost=2 size=0 card=1)(object id 775359)
       105        105        105  SORT AGGREGATE (cr=120 pr=0 pw=0 time=1804 us)
         0          0          0   COUNT STOPKEY (cr=120 pr=0 pw=0 time=1384 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=1145 us cost=5 size=29 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=891 us cost=5 size=29 card=1)
         0          0          0      INDEX RANGE SCAN IX_PMOHOTPT_05 PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=710 us cost=4 size=0 card=1)(object id 774419)
       105        105        105  SORT AGGREGATE (cr=315 pr=102 pw=0 time=602420 us)
         0          0          0   COUNT STOPKEY (cr=315 pr=102 pw=0 time=602086 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=315 pr=102 pw=0 time=601736 us cost=4 size=30 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRHCNSTINFO PARTITION: KEY KEY (cr=315 pr=102 pw=0 time=601442 us cost=4 size=30 card=1)
         0          0          0      INDEX RANGE SCAN IX_MMRHCNSTINFO_01 PARTITION: KEY KEY (cr=315 pr=102 pw=0 time=601250 us cost=3 size=0 card=1)(object id 767676)
       105        105        105  SORT AGGREGATE (cr=1147 pr=464 pw=0 time=2067691 us)
         7          7          7   COUNT  (cr=1147 pr=464 pw=0 time=2067470 us)
         7          7          7    FILTER  (cr=1147 pr=464 pw=0 time=2067346 us)
       105        105        105     FAST DUAL  (cr=0 pr=0 pw=0 time=128 us cost=2 size=0 card=1)
       105        105        105     SORT AGGREGATE (cr=651 pr=117 pw=0 time=651870 us)
         7          7          7      COUNT STOPKEY (cr=651 pr=117 pw=0 time=651597 us)
         7          7          7       NESTED LOOPS  (cr=651 pr=117 pw=0 time=651299 us)
         7          7          7        NESTED LOOPS  (cr=644 pr=115 pw=0 time=644139 us cost=10 size=78 card=1)
         7          7          7         PARTITION LIST ALL PARTITION: 1 4 (cr=623 pr=112 pw=0 time=625535 us cost=9 size=51 card=1)
         7          7          7          TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: 1 4 (cr=623 pr=112 pw=0 time=624997 us cost=9 size=51 card=1)
         7          7          7           INDEX RANGE SCAN IX_MMRMFORMREC_03 PARTITION: 1 4 (cr=616 pr=108 pw=0 time=603035 us cost=8 size=0 card=1)(object id 767726)
         7          7          7         INDEX RANGE SCAN IX_MMRDRECHIST_02 (cr=21 pr=3 pw=0 time=18417 us cost=1 size=0 card=1)(object id 766493)
         7          7          7        TABLE ACCESS BY GLOBAL INDEX ROWID MMRDRECHIST PARTITION: ROW LOCATION ROW LOCATION (cr=7 pr=2 pw=0 time=7003 us cost=1 size=27 card=1)
        98         98         98     SORT AGGREGATE (cr=496 pr=347 pw=0 time=1408125 us)
         0          0          0      COUNT STOPKEY (cr=496 pr=347 pw=0 time=1407855 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=496 pr=347 pw=0 time=1407473 us cost=4 size=29 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=496 pr=347 pw=0 time=1407134 us cost=4 size=29 card=1)
       824        824        824         INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=300 pr=164 pw=0 time=923313 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  COUNT STOPKEY (cr=456 pr=230 pw=0 time=1238484 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=456 pr=230 pw=0 time=1238052 us cost=4 size=49 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCMSPIF PARTITION: KEY KEY (cr=456 pr=230 pw=0 time=1237620 us cost=4 size=49 card=1)
       608        608        608     INDEX RANGE SCAN IX_PMCMSPIF_01 PARTITION: KEY KEY (cr=321 pr=110 pw=0 time=652231 us cost=3 size=0 card=1)(object id 775434)
       105        105        105  SORT AGGREGATE (cr=1198 pr=558 pw=0 time=2989389 us)
        74         74         74   FILTER  (cr=1198 pr=558 pw=0 time=2989056 us)
       105        105        105    FAST DUAL  (cr=0 pr=0 pw=0 time=102 us cost=2 size=0 card=1)
       105        105        105    SORT AGGREGATE (cr=809 pr=369 pw=0 time=2241094 us)
        72         72         72     COUNT STOPKEY (cr=809 pr=369 pw=0 time=2240764 us)
        72         72         72      PARTITION LIST ALL PARTITION: 1 4 (cr=809 pr=369 pw=0 time=2240388 us cost=8 size=27 card=1)
        72         72         72       INDEX RANGE SCAN IX_MMOHOPRC_19 PARTITION: 1 4 (cr=809 pr=369 pw=0 time=2239916 us cost=8 size=27 card=1)(object id 794293)
       105        105        105        SORT AGGREGATE (cr=317 pr=185 pw=0 time=1187875 us)
       104        104        104         PARTITION LIST SINGLE PARTITION: 2 2 (cr=317 pr=185 pw=0 time=1187354 us cost=4 size=27 card=1)
       104        104        104          FIRST ROW  (cr=317 pr=185 pw=0 time=1187002 us cost=4 size=27 card=1)
       104        104        104           INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_07 PARTITION: 2 2 (cr=317 pr=185 pw=0 time=1186889 us cost=4 size=27 card=1)(object id 795597)
        33         33         33    SORT AGGREGATE (cr=389 pr=189 pw=0 time=746794 us)
         2          2          2     COUNT STOPKEY (cr=389 pr=189 pw=0 time=746669 us)
         2          2          2      PARTITION LIST ALL PARTITION: 1 4 (cr=389 pr=189 pw=0 time=746550 us cost=13 size=27 card=1)
         2          2          2       TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 4 (cr=389 pr=189 pw=0 time=746361 us cost=13 size=27 card=1)
       302        302        302        INDEX RANGE SCAN IX_MMOHIPRC_03 PARTITION: 1 4 (cr=297 pr=82 pw=0 time=472729 us cost=12 size=0 card=1)(object id 767321)
        33         33         33         SORT AGGREGATE (cr=68 pr=32 pw=0 time=177672 us)
        27         27         27          PARTITION LIST SINGLE PARTITION: 2 2 (cr=68 pr=32 pw=0 time=177551 us cost=3 size=25 card=1)
        27         27         27           FIRST ROW  (cr=68 pr=32 pw=0 time=177452 us cost=3 size=25 card=1)
        27         27         27            INDEX RANGE SCAN (MIN/MAX) IX_PMIHINPT_11 PARTITION: 2 2 (cr=68 pr=32 pw=0 time=177413 us cost=3 size=25 card=1)(object id 795657)
       105        105        105  SORT AGGREGATE (cr=657 pr=26 pw=0 time=126144 us)
      5369       5369       5369   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=657 pr=26 pw=0 time=126694 us cost=1 size=27 card=1)
      5369       5369       5369    INLIST ITERATOR  (cr=657 pr=26 pw=0 time=123836 us)
      5369       5369       5369     INDEX RANGE SCAN IX_PMOHOTPT_07 PARTITION: KEY KEY (cr=657 pr=26 pw=0 time=121885 us cost=1 size=27 card=1)(object id 795597)
       105        105        105  SORT AGGREGATE (cr=856 pr=418 pw=0 time=2251952 us)
         0          0          0   COUNT STOPKEY (cr=856 pr=418 pw=0 time=2251603 us)
         0          0          0    NESTED LOOPS  (cr=856 pr=418 pw=0 time=2251086 us)
        50         50         50     NESTED LOOPS  (cr=805 pr=383 pw=0 time=2099354 us cost=5 size=56 card=1)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY KEY (cr=680 pr=351 pw=0 time=1954331 us cost=4 size=39 card=1)
        50         50         50       TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: KEY KEY (cr=680 pr=351 pw=0 time=1953940 us cost=4 size=39 card=1)
        50         50         50        INDEX RANGE SCAN IX_MMRMFORMREC_01 PARTITION: KEY KEY (cr=634 pr=315 pw=0 time=1808435 us cost=3 size=0 card=1)(object id 767736)
       105        105        105         SORT AGGREGATE (cr=319 pr=201 pw=0 time=1207478 us)
        98         98         98          PARTITION LIST SINGLE PARTITION: KEY KEY (cr=319 pr=201 pw=0 time=1207139 us cost=4 size=33 card=1)
        98         98         98           FIRST ROW  (cr=319 pr=201 pw=0 time=1206760 us cost=4 size=33 card=1)
        98         98         98            INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_08 PARTITION: KEY KEY (cr=319 pr=201 pw=0 time=1206632 us cost=4 size=33 card=1)(object id 795652)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY(AP) KEY(AP) (cr=125 pr=32 pw=0 time=131470 us cost=1 size=0 card=1)
        50         50         50       INDEX UNIQUE SCAN PK_MMRDRECHIST PARTITION: KEY(AP) KEY(AP) (cr=125 pr=32 pw=0 time=131203 us cost=1 size=0 card=1)(object id 767661)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRDRECHIST PARTITION: 1 1 (cr=51 pr=35 pw=0 time=142910 us cost=1 size=17 card=1)
       105        105        105  SORT AGGREGATE (cr=419 pr=16 pw=0 time=114743 us)
       104        104        104   COUNT STOPKEY (cr=419 pr=16 pw=0 time=114365 us)
       104        104        104    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=419 pr=16 pw=0 time=113939 us cost=4 size=32 card=1)
       104        104        104     TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=419 pr=16 pw=0 time=113500 us cost=4 size=32 card=1)
       104        104        104      INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=315 pr=10 pw=0 time=59538 us cost=3 size=0 card=1)(object id 767381)
       105        105        105  SORT AGGREGATE (cr=41 pr=0 pw=0 time=1052 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID MMRDMEMO (cr=41 pr=0 pw=0 time=850 us cost=4 size=41 card=1)
         0          0          0    INDEX RANGE SCAN PK_MMRDMEMO (cr=41 pr=0 pw=0 time=705 us cost=2 size=0 card=1)(object id 765770)
       105        105        105  SORT AGGREGATE (cr=114 pr=10 pw=0 time=58089 us)
         0          0          0   NESTED LOOPS  (cr=114 pr=10 pw=0 time=44474 us)
         0          0          0    NESTED LOOPS  (cr=114 pr=10 pw=0 time=44320 us cost=4 size=65 card=1)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=114 pr=10 pw=0 time=44191 us cost=3 size=42 card=1)
         0          0          0      TABLE ACCESS BY LOCAL INDEX ROWID MMOHCCPT PARTITION: 2 2 (cr=114 pr=10 pw=0 time=43922 us cost=3 size=42 card=1)
         6          6          6       INDEX RANGE SCAN IX_MMOHCCPT_01 PARTITION: 2 2 (cr=108 pr=4 pw=0 time=19182 us cost=1 size=0 card=1)(object id 779517)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
         0          0          0      INDEX UNIQUE SCAN PK_MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 767656)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=184 card=8)
        40         40         40  COUNT STOPKEY (cr=355 pr=130 pw=0 time=663012 us)
        40         40         40   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=355 pr=130 pw=0 time=662568 us cost=4 size=29 card=1)
        40         40         40    TABLE ACCESS BY LOCAL INDEX ROWID MRCHMRHS PARTITION: KEY KEY (cr=355 pr=130 pw=0 time=662201 us cost=4 size=29 card=1)
        40         40         40     INDEX RANGE SCAN IX_MRCHMRHS_01 PARTITION: KEY KEY (cr=315 pr=91 pw=0 time=404165 us cost=3 size=0 card=1)(object id 767891)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=114 pr=4 pw=0 time=34100 us cost=3 size=23 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=114 pr=4 pw=0 time=33747 us cost=3 size=23 card=1)
         0          0          0    INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=114 pr=4 pw=0 time=33532 us cost=2 size=0 card=1)(object id 767826)
         2          2          2     SORT AGGREGATE (cr=6 pr=2 pw=0 time=15100 us)
         0          0          0      COUNT STOPKEY (cr=6 pr=2 pw=0 time=15082 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=6 pr=2 pw=0 time=15075 us cost=3 size=19 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=6 pr=2 pw=0 time=15068 us cost=3 size=19 card=1)
         6          6          6         INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=19 us cost=2 size=0 card=1)(object id 767826)
         0          0          0  COUNT STOPKEY (cr=526 pr=3 pw=0 time=17781 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=526 pr=3 pw=0 time=17455 us cost=4 size=38 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=526 pr=3 pw=0 time=17167 us cost=4 size=38 card=1)
       884        884        884     INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=321 pr=0 pw=0 time=1724 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=1181 us cost=2 size=18 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MMOHPBMT PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=724 us cost=2 size=18 card=1)
         0          0          0    INDEX RANGE SCAN IX_MMOHPBMT_01 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=507 us cost=1 size=0 card=1)(object id 779522)
       105        105        105  SORT ORDER BY (cr=8097 pr=2506 pw=0 time=12685172 us cost=13 size=227 card=1)
       105        105        105   WINDOW SORT (cr=725 pr=291 pw=0 time=955800 us cost=13 size=227 card=1)
       105        105        105    NESTED LOOPS OUTER (cr=725 pr=291 pw=0 time=955171 us cost=11 size=227 card=1)
       105        105        105     NESTED LOOPS  (cr=513 pr=291 pw=0 time=953161 us cost=9 size=190 card=1)
       105        105        105      NESTED LOOPS  (cr=195 pr=181 pw=0 time=69377 us cost=8 size=137 card=1)
       105        105        105       PARTITION LIST SINGLE PARTITION: 2 2 (cr=186 pr=181 pw=0 time=68425 us cost=7 size=252 card=2)
       105        105        105        TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: 2 2 (cr=186 pr=181 pw=0 time=68418 us cost=7 size=252 card=2)
       347        347        347         INDEX RANGE SCAN IX_PMOHOTPT_01 PARTITION: 2 2 (cr=5 pr=3 pw=0 time=22197 us cost=4 size=0 card=1)(object id 796979)
       105        105        105       INDEX UNIQUE SCAN UK_ZBCMCODE_02 (cr=9 pr=0 pw=0 time=328 us cost=1 size=11 card=1)(object id 96118)
       105        105        105      PARTITION LIST SINGLE PARTITION: 2 2 (cr=318 pr=110 pw=0 time=613090 us cost=1 size=53 card=1)
       105        105        105       TABLE ACCESS BY LOCAL INDEX ROWID PMCMPTBS PARTITION: 2 2 (cr=318 pr=110 pw=0 time=612756 us cost=1 size=53 card=1)
       105        105        105        INDEX UNIQUE SCAN PK_PMCMPTBS PARTITION: 2 2 (cr=212 pr=12 pw=0 time=66565 us cost=1 size=0 card=1)(object id 774609)
         0          0          0     TABLE ACCESS BY INDEX ROWID MNVHWGIM (cr=212 pr=0 pw=0 time=1605 us cost=2 size=37 card=1)
         0          0          0      INDEX RANGE SCAN PK_MNVHWGIM (cr=212 pr=0 pw=0 time=1135 us cost=1 size=0 card=1)(object id 765479)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  Disk file operations I/O                      104        0.00          0.00
  db file sequential read                      2423        0.05         12.32
  db file parallel read                           9        0.02          0.14
  SQL*Net message from client                     8        3.92          4.83
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.05       0.08          0         78          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        8      0.20      12.68       2506       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.25      12.77       2506       8175          0         105
 
Misses in library cache during parse: 1
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      10        0.00          0.00
  SQL*Net message from client                    10        9.71         22.23
  SQL*Net more data from client                   2        0.00          0.00
  Disk file operations I/O                      104        0.00          0.00
  db file sequential read                      2423        0.05         12.32
  db file parallel read                           9        0.02          0.14
 

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       22      0.00       0.00          0          0          0           0
Execute     80      0.01       0.02          0          0          0           0
Fetch      223      0.00       0.12         17        390          0         173
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      325      0.03       0.15         17        390          0         173
 
Misses in library cache during parse: 17
Misses in library cache during execute: 16
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        3        0.00          0.00
  db file sequential read                        17        0.01          0.11
 
    2  user  SQL statements in session.
   22  internal SQL statements in session.
   24  SQL statements in session.
********************************************************************************
Trace file: HISEDU_ora_47382576.trc
Trace file compatibility: 11.1.0.7
Sort options: default
 
       1  session in tracefile.
       2  user  SQL statements in trace file.
      22  internal SQL statements in trace file.
      24  SQL statements in trace file.
      19  unique SQL statements in trace file.
    4389  lines in trace file.
      27  elapsed seconds in trace file.
 

----
참고로
disk IO가 발생하지 않는 경우입니다.
 

TKPROF: Release 11.2.0.3.0 - Development on Fri Aug 23 11:57:19 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: HISEDU_ora_3998130.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: 77xfzx37m45ua Plan Hash: 0
 
alter session set events '10357 trace name context forever, level 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        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: SYS
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        7.64          7.64
********************************************************************************
 
SQL ID: cb21bacyh3c7d Plan Hash: 3488560417
 
select metadata
from
 kopm$  where name='DB_FDO'
 

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          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1
 
Misses in library cache during parse: 0
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
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=27 us cost=1 size=108 card=1)
         1          1          1   INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=15 us cost=1 size=0 card=1)(object id 545)
 
********************************************************************************
 
  SELECT
          otpt.elbulbodstat
        , otpt.ordtm
        , CASE WHEN
                    (case when count(ptbs.hngnm) over(partition by ptbs.hngnm) > 1
                    then
                        case when count(ptbs.hngnm) over(partition by ptbs.pid, ptbs.hngnm) > 1
                        then
                            1
                        else
                         count(ptbs.hngnm) over(partition by ptbs.hngnm)
                        end
                    else
                     count(ptbs.hngnm) over(partition by ptbs.hngnm)
                    end) > 1
                   THEN
                        ptbs.hngnm || '(.)'
                   ELSE
                        ptbs.hngnm
                   END AS hngnm
        , otpt.pid
        , otpt.orddeptcd
        , ptbs.sex || '/'
                   || COM.FN_ZZ_GETAGE_EMR(ptbs.rrgstno1, ptbs.rrgstno2, otpt.orddd, 'A', ptbs.brthdd)   AS sa
        , otpt.fsexamflag
        , otpt.etcordflag
              , (SELECT term.termengnm diagengnm
                   FROM emr.mmohdiag diag,                     -- .........
                        emr.mrtmterm term                      -- .....
                  WHERE diag.pid       = otpt.pid
                    AND diag.orddd     = otpt.orddd
                    AND diag.cretno    = otpt.cretno
                    AND diag.orddeptcd = otpt.orddeptcd
                    AND diag.instcd    = otpt.instcd
                    AND diag.diagcd    = term.termcd
                    AND diag.diagdd   >= term.termfromdd
                    AND diag.diagdd   <= term.termtodd
                    AND diag.instcd    = term.instcd
                    AND diag.diaghistcd     = 'O'
                    AND diag.diagtypecd     = 'D'
                    AND diag.diagkindcdflag = 'M'
                    AND ROWNUM = 1)                          AS diagnm                                             -- ...
        , otpt.instcd
        , otpt.orddd
        , otpt.cretno
        , otpt.orddrid
              , otpt.prcptdayaftrcptyn
              , otpt.rcptvipresncd
              , otpt.rcptvipetcresn
              , otpt.medamtpostyn
              , CASE WHEN (SELECT a.rcptdelivefact
                             FROM pam.pmchcapm a
                            WHERE a.instcd = otpt.instcd
                              AND a.pid    = otpt.pid
                              AND a.civilaplendflagcd IN ('Y', 'R')
                              AND a.drdelive = 'Y'
                              AND a.civilaplclamdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND a.civilenddd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND rownum = 1  ) is not NULL THEN 'Y'
                                                            ELSE 'N'
                     END                                                                     AS civilap
     ,   DECODE (otpt.ordstartdt, '00000000000000', null, SUBSTR(otpt.ordstartdt, 9, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 11, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 13, 2)         ) AS ordstartdt          -- ......
              ,   CASE WHEN otpt.dracptyn ='Y' THEN (DECODE (otpt.dracptdt,   '-', null, SUBSTR(otpt.dracptdt, 9, 2)  ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 11, 2) ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 13, 2)   )  )
                        END                                      AS dracptdt
              ,   CASE WHEN otpt.nursacptyn ='Y' THEN (DECODE (otpt.nursacptdt, '-', null, SUBSTR(otpt.nursacptdt, 9, 2)  ||':'||
                                                                                           SUBSTR(otpt.nursacptdt, 11, 2)         ) )
                        END                                AS nursacptdt
              ,   (SELECT CASE WHEN count(1) > 0 THEN  'Y'
                               ELSE 'N'
                           END
                     FROM pam.pmohotpt totp
                    WHERE otpt.pid           =       totp.pid
                      AND otpt.instcd        =       totp.instcd
                      AND otpt.orddd         >=      to_char(to_date(totp.orddd,  'YYYYMMDD')-7, 'YYYYMMDD')
                      AND otpt.orddd         <=       totp.orddd
                      AND otpt.cretno        !=       totp.cretno
                      AND totp.ordtype       =       'E'
                      AND rownum             =        1 )                       AS   eryn                 -- ER..
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                              ELSE 'N'
                         END
                    FROM emr.mmrhcnstinfo info
                   WHERE info.pid           =        otpt.pid
                     AND info.instcd        =        otpt.instcd
                     AND info.orddd         =        otpt.orddd
                     AND info.cretno        =        otpt.cretno
                     AND info.cnststat      in       ('12', '20', '21', '22', '30', '31', '32', '40')
                     AND rownum             =        1 )                        AS   coordyn            -- ....
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                             ELSE 'N'
                         END
                   FROM dual
                  WHERE (SELECT  CASE WHEN count(1) > 0 THEN 'Y'
                                      ELSE 'N'
                                  END
                           FROM  emr.mmrmformrec mrec,
                                 emr.mmrdrechist hist
                          WHERE  mrec.instcd       =      hist.instcd
                            AND  mrec.formrecseq   =      hist.formrecseq
                            AND  mrec.lastrechistseq = hist.rechistseq
                            AND  mrec.delyn        =      'N'
                            AND  mrec.valiyn       =      'Y'
                            AND  mrec.pid          =      otpt.pid
                            AND  mrec.orddd        =      otpt.orddd
                            AND  mrec.cretno       =      otpt.cretno
                            AND  hist.ESPISEQ      =      '0'
                            AND  rownum            =       1 ) ='Y'
                     OR
                        (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                                     ELSE 'N'
                                 END
                           FROM emr.mmohoprc oprc
                          WHERE oprc.instcd          =    otpt.instcd
                            AND oprc.pid             =    otpt.pid
                            AND oprc.orddd           =    otpt.orddd
                            AND oprc.cretno          =    otpt.cretno
                            AND oprc.prcphistcd      = 'T'
                            AND rownum               =    1 ) = 'Y'
                            AND rownum = 1 )                                  AS tempsaveyn          -- ......
              ,  (SELECT CASE WHEN  sasm.choiflag in ( 'B',  'C')  THEN 'Y'
                             ELSE 'N'
                         END
                        FROM pam.pmcmspif sasm --  pam.pmcmsasm sasm
                       WHERE sasm.instcd     =  otpt.instcd
                         AND sasm.pid        =  otpt.pid
                         AND sasm.orddeptcd  =  otpt.orddeptcd
                         AND sasm.orddrid    =  otpt.orddrid
                         AND sasm.ioflag     =  'O'
                         AND sasm.histstat   =  'Y'
                         AND sasm.specordyn  =  'Y'
                         AND sasm.fromdd     <= otpt.orddd
                         AND sasm.todd       >= otpt.orddd
                         AND rownum          =  1 )                          AS specordtype        --  ....
    ,(SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
               END
        FROM dual
       WHERE (
            SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohoprc oprc
             WHERE oprc.pid          = otpt.pid
               AND oprc.prcpdd       >=     (SELECT NVL(MAX(orddd), '20130605') AS fromdd
                                               FROM pam.pmohotpt
                                              WHERE instcd = '053'
                                                AND pid = otpt.pid
                                                AND orddd < '20130605'
                                                AND elbulbodstat = '2'
                                                AND histstat = 'R')
               AND oprc.prcpdd        <= otpt.orddd
               AND oprc.prcphistcd   = 'O'
               AND oprc.prcpflag     = '1'
               AND oprc.prcpstatcd   > '700'
               AND ROWNUM = 1 ) = 'Y'
          OR
            (SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohiprc iprc
             WHERE iprc.pid          = otpt.pid
               AND iprc.prcpdd       >=     ( SELECT NVL(MAX(dschdd), '20130605') AS formdd
                                                FROM pam.pmihinpt
                                               WHERE instcd = '053'
                                                 AND pid = otpt.pid
                                                 AND dschdd != '99991231'
                                                 AND histstat = 'Y')
               AND iprc.prcpdd        <= otpt.orddd
               AND iprc.prcphistcd   = 'O'
               AND iprc.prcpflag     = '1'
               AND iprc.prcpstatcd   > '700'
               AND ROWNUM = 1) = 'Y') as rsltyn         -- ....
      , otpt.insukind as insukind
    --, DECODE(code.cdid, '11','..', '21','..', '22','..', substr(code.cdnm,0,2)) insukind  --..(...2.....)
    , (SELECT NVL(MAX(a.orddd), otpt.orddd)
                     FROM pam.pmohotpt a
                    WHERE a.instcd = ptbs.instcd
                      AND a.pid = ptbs.pid
                      AND a.elbulbodstat = '2' --...... ..
                      AND a.orddd < otpt.orddd
                      AND a.histstat in ( 'R', 'T' )
                      ) as recsrchfromdd       --.... ....
                , (SELECT CASE WHEN count(*) = 1 then 'Y'
                               ELSE 'N'
                               END
                      FROM emr.mmrmformrec a,
                           emr.mmrdrechist b
                     WHERE a.instcd = otpt.instcd
                       AND a.pid = otpt.pid
                       AND a.chosflag = 'O'
                       AND a.lastformrecdd >= (SELECT NVL(MAX(a.orddd), otpt.orddd)
                                                 FROM pam.pmohotpt a
                                                WHERE a.instcd = otpt.instcd
                                                  AND a.pid = otpt.pid
                                                  AND a.orddeptcd = otpt.orddeptcd
                                                  AND a.orddrid = otpt.orddrid
                                                  AND a.orddd < otpt.orddd)
                       AND a.lastformrecdd <= otpt.orddd
                       AND a.valiyn = 'Y'
                       AND a.delyn = 'N'
                       AND b.instcd  = a.instcd
                       AND b.rechistseq = a.lastrechistseq
                       AND b.espiseq != 0
                       AND rownum = 1) as recyn
                  , (SELECT CASE WHEN count(*) = 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                       FROM emr.mmohoprc oprc
                      WHERE oprc.instcd = otpt.instcd
                        AND oprc.pid = otpt.pid
                        AND oprc.orddd = otpt.orddd
                        AND oprc.cretno = otpt.cretno
                        AND oprc.prcphistcd IN ('O', 'X', 'M')      -- Dump. ....
            AND oprc.prcpkindcd NOT IN ('30', '35', '40') -- ..... .. . .... ... ..
                        AND rownum = 1
                        ) as prcpyn
                   , (SELECT CASE WHEN count(*) >= 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                        FROM emr.mmrdmemo memo
                       WHERE memo.instcd = otpt.instcd
                         AND memo.pid = otpt.pid
                   --AND memo.deptcd = otpt.orddeptcd
                   AND (memo.memo IS NOT NULL OR nursememo IS NOT NULL)
                   AND memo.useflag IN ('P', 'D') --.... .. .. (P: .., D: ...)
                   ) as memoyn
                   , (SELECT  RTRIM (XMLAGG (XMLELEMENT ("a", cccl.conctypenm || ',')).EXTRACT ('//text()').getstringval (), ',') a
                        FROM  emr.mmohccpt ccpt
                            , emr.mmomcccl cccl
                       WHERE ccpt.conckindcd = cccl.conckindcd
                         AND ccpt.conctrgtcd = cccl.conctrgtcd
                         AND ccpt.conctypecd = cccl.conctypecd
                         AND ccpt.instcd     = cccl.instcd
                         AND ccpt.valifromdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                         AND ccpt.valitodd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                      AND ccpt.instcd      = '053'
                      AND ccpt.pid         = otpt.pid
                      AND ccpt.conctrgtcd   = CASE WHEN ccpt.conckindcd = 'D'  THEN otpt.orddeptcd                                                                             ELSE '49170'
                                                                          END   )           AS conctypenm --....
       , otpt.rsrvflag
       , DECODE(   (SELECT 1
                         FROM emr.mrchmrhs mrhs
                        WHERE mrhs.instcd = otpt.instcd
                          AND mrhs.pid = otpt.pid
                          AND mrhs.ioflag = otpt.ordtype
                          AND mrhs.chrtflag = 'P'
                          AND mrhs.deldt >= SYSTIMESTAMP
                          AND ROWNUM  =1) , 1, 'Y', ''
                ) AS chartscanyn, --chart scan ..
       CASE WHEN DRACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(SUBSTR(DRACPTDT,9,4),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         WHEN NURSACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(TO_CHAR(sysdate,'hh24mi'),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         ELSE '-' END AS waittm--.... lsh
        /*,(SELECT CASE WHEN (SELECT hospgrade FROM ast.arhmchsp
                                        WHERE coophospcd = reqp.coophospcd
                                          AND instcd = reqp.instcd)  IN ('C1', 'C2', 'C3') THEN 'refer(..)'
                                 ELSE 'refer' END
                       FROM ast.arcmreqp reqp
                      WHERE reqp.pid = otpt.pid
                        AND reqp.instcd = otpt.instcd
                        AND reqp.orddeptcd = otpt.orddeptcd
                        AND otpt.orddd BETWEEN reqp.rgstdd and reqp.enddd
                        AND reqp.deldd = '00000000'
                        AND ROWNUM = 1)*/
                  ,DECODE( otpt.RQSTFLAG, '1','Y', '-')            AS refer
                        , DECODE((SELECT 'Y'
                           FROM emr.mnohdlvt z
                           WHERE z.instcd = otpt.instcd
                           AND z.pid = otpt.pid
                           AND z.seqno = (SELECT max(y.seqno)
                                            FROM emr.mnohdlvt y
                                           WHERE y.instcd = z.instcd
                                             AND y.pid = z.pid
                                             AND y.statcd = 'Y'
                                             AND rownum = 1)
                           AND (z.EXPTDLIVDD > = to_char(sysdate, 'YYYYMMDD') OR z.EXPTDLIVDD IS NULL) --....
                         ),'Y','01', case when ptbs.sex = 'F' and to_number( COM.FN_ZZ_GETAGE('-', '-', otpt.orddd, 'B', ptbs.brthdd)) between 15 and 55 then '02'
                            else '' end ) AS gravdpsbldliv  -- ....//  /*.... 01 .... 02 .... null. .. > .. ...... */
                , CASE WHEN  otpt.suppkind  IN ( '06' , '11', '61' , '62' , '63' ) THEN  '..'
                       WHEN  otpt.suppkind = '07' THEN '..'
                       WHEN  otpt.suppkind  IN ( '50' , '51' ) THEN  '..'
                       ELSE '-'
                  END as serdiag
               ,  decode( ( SELECT 'C'
                              FROM emr.mmohoprc oprc
                             WHERE oprc.instcd = otpt.instcd
                               AND oprc.pid = otpt.pid
                               AND oprc.orddd = otpt.orddd
                               AND oprc.cretno = otpt.cretno
                               AND oprc.prcphistcd = 'O'
                               AND oprc.prcpcd like 'XSCAN%'
                               AND ROWNUM = 1), 'C', 'C', decode(otpt.outercdrgstyn, 'N', '', otpt.outercdrgstyn )) as outercdrgstyn -- ....
               , decode(ptbs.kioskrcptnoyn, 'N','Y','') as kioskrcptnoyn  --....
               , CASE WHEN otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'E', 'S', 'P', 'B') AND otpt.etcordflag NOT IN ('M','J','N', '07') THEN ''
                      ELSE 'Y'
                 END as tempacpt
               , nvl((select bmtflag from emr.mmohpbmt pbmt WHERE pbmt.instcd = otpt.instcd AND pbmt.pid = otpt.pid and pbmt.histcd='O'),'-') as bmtflag
               , LPAD(NVL2(owgm.ordrsrvseqno,owgm.ordrsrvseqno,'0000'),4,0) as ordrsrvseqno     --......
               , owgm.ordrsrvtm                                                                 --......
          FROM  pam.pmohotpt otpt
        , pam.pmcmptbs ptbs
        , com.zbcmcode code
        , emr.mnvhwgim owgm
   WHERE otpt.histstat IN ('R', 'T')                                            -- .., .......
     AND otpt.ordtype  IN ('O', 'E')
     AND otpt.pid    = ptbs.pid
     AND otpt.instcd  = ptbs.instcd
     AND code.cdid     = otpt.insukind
     AND code.cdgrupid = 'P0008'
     AND  owgm.instcd(+) = otpt.instcd
           AND  owgm.pid(+)    = otpt.pid
        AND  owgm.orddd(+)  = otpt.orddd
        AND  owgm.cretno(+) = otpt.cretno
           AND otpt.orddd    = '20130605'
           AND otpt.instcd   = '053'
           AND otpt.orddeptcd    = 'ME'
           AND otpt.orddrid      = '49170'
           AND otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'S', 'P', 'B') --..., ...., ...., ....., .... .., ......, ..... ..
              --.... ... ..... .. ..
           AND NVL(otpt.etcordflag, '-') NOT IN ('M','J', 'N', '07')                 -- ...., .., .... ..
      ORDER BY owgm.ordrsrvtm, ordrsrvseqno, otpt.orddd, otpt.ordtm, otpt.NURSACPTDT , otpt.fstacptdt
     /* himed/his/emr/prcpmngtmgr/etcprcpmngtmgt/dao/sqls/etcprcpmngtdao_sqls.xml getSpeedOutPatList */
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.08          0         78          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.05       0.08          0       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.10       0.16          0       8175          0         105
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       104        104        104  COUNT STOPKEY (cr=838 pr=0 pw=0 time=4265 us)
       104        104        104   NESTED LOOPS  (cr=838 pr=0 pw=0 time=3759 us)
       104        104        104    NESTED LOOPS  (cr=734 pr=0 pw=0 time=3087 us cost=5 size=131 card=1)
       104        104        104     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=422 pr=0 pw=0 time=2121 us cost=4 size=58 card=1)
       104        104        104      TABLE ACCESS BY LOCAL INDEX ROWID MMOHDIAG PARTITION: KEY KEY (cr=422 pr=0 pw=0 time=1841 us cost=4 size=58 card=1)
       161        161        161       INDEX RANGE SCAN IX_MMOHDIAG_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1232 us cost=3 size=0 card=1)(object id 796990)
       104        104        104     INDEX RANGE SCAN PK_MRTMTERM (cr=312 pr=0 pw=0 time=824 us cost=1 size=0 card=1)(object id 766242)
       104        104        104    TABLE ACCESS BY INDEX ROWID MRTMTERM (cr=104 pr=0 pw=0 time=437 us cost=2 size=73 card=1)
         0          0          0  COUNT STOPKEY (cr=213 pr=0 pw=0 time=1300 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=213 pr=0 pw=0 time=1049 us cost=3 size=163 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCHCAPM PARTITION: KEY KEY (cr=213 pr=0 pw=0 time=849 us cost=3 size=163 card=1)
         1          1          1     INDEX RANGE SCAN PK_PMCHCAPM PARTITION: KEY KEY (cr=212 pr=0 pw=0 time=705 us cost=2 size=0 card=1)(object id 775359)
       105        105        105  SORT AGGREGATE (cr=120 pr=0 pw=0 time=1344 us)
         0          0          0   COUNT STOPKEY (cr=120 pr=0 pw=0 time=1115 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=899 us cost=5 size=29 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=647 us cost=5 size=29 card=1)
         0          0          0      INDEX RANGE SCAN IX_PMOHOTPT_05 PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=503 us cost=4 size=0 card=1)(object id 774419)
       105        105        105  SORT AGGREGATE (cr=315 pr=0 pw=0 time=1848 us)
         0          0          0   COUNT STOPKEY (cr=315 pr=0 pw=0 time=1645 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1416 us cost=4 size=30 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRHCNSTINFO PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1170 us cost=4 size=30 card=1)
         0          0          0      INDEX RANGE SCAN IX_MMRHCNSTINFO_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1025 us cost=3 size=0 card=1)(object id 767676)
       105        105        105  SORT AGGREGATE (cr=1147 pr=0 pw=0 time=9436 us)
         7          7          7   COUNT  (cr=1147 pr=0 pw=0 time=9259 us)
         7          7          7    FILTER  (cr=1147 pr=0 pw=0 time=9144 us)
       105        105        105     FAST DUAL  (cr=0 pr=0 pw=0 time=61 us cost=2 size=0 card=1)
       105        105        105     SORT AGGREGATE (cr=651 pr=0 pw=0 time=5096 us)
         7          7          7      COUNT STOPKEY (cr=651 pr=0 pw=0 time=4908 us)
         7          7          7       NESTED LOOPS  (cr=651 pr=0 pw=0 time=4660 us)
         7          7          7        NESTED LOOPS  (cr=644 pr=0 pw=0 time=4485 us cost=10 size=78 card=1)
         7          7          7         PARTITION LIST ALL PARTITION: 1 4 (cr=623 pr=0 pw=0 time=4323 us cost=9 size=51 card=1)
         7          7          7          TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: 1 4 (cr=623 pr=0 pw=0 time=3955 us cost=9 size=51 card=1)
         7          7          7           INDEX RANGE SCAN IX_MMRMFORMREC_03 PARTITION: 1 4 (cr=616 pr=0 pw=0 time=2499 us cost=8 size=0 card=1)(object id 767726)
         7          7          7         INDEX RANGE SCAN IX_MMRDRECHIST_02 (cr=21 pr=0 pw=0 time=61 us cost=1 size=0 card=1)(object id 766493)
         7          7          7        TABLE ACCESS BY GLOBAL INDEX ROWID MMRDRECHIST PARTITION: ROW LOCATION ROW LOCATION (cr=7 pr=0 pw=0 time=49 us cost=1 size=27 card=1)
        98         98         98     SORT AGGREGATE (cr=496 pr=0 pw=0 time=3224 us)
         0          0          0      COUNT STOPKEY (cr=496 pr=0 pw=0 time=3035 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=496 pr=0 pw=0 time=2778 us cost=4 size=29 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=496 pr=0 pw=0 time=2527 us cost=4 size=29 card=1)
       824        824        824         INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=300 pr=0 pw=0 time=1543 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  COUNT STOPKEY (cr=456 pr=0 pw=0 time=2705 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=456 pr=0 pw=0 time=2423 us cost=4 size=49 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCMSPIF PARTITION: KEY KEY (cr=456 pr=0 pw=0 time=2186 us cost=4 size=49 card=1)
       608        608        608     INDEX RANGE SCAN IX_PMCMSPIF_01 PARTITION: KEY KEY (cr=321 pr=0 pw=0 time=1507 us cost=3 size=0 card=1)(object id 775434)
       105        105        105  SORT AGGREGATE (cr=1197 pr=0 pw=0 time=8690 us)
        74         74         74   FILTER  (cr=1197 pr=0 pw=0 time=8469 us)
       105        105        105    FAST DUAL  (cr=0 pr=0 pw=0 time=68 us cost=2 size=0 card=1)
       105        105        105    SORT AGGREGATE (cr=809 pr=0 pw=0 time=5066 us)
        72         72         72     COUNT STOPKEY (cr=809 pr=0 pw=0 time=4854 us)
        72         72         72      PARTITION LIST ALL PARTITION: 1 4 (cr=809 pr=0 pw=0 time=4612 us cost=8 size=27 card=1)
        72         72         72       INDEX RANGE SCAN IX_MMOHOPRC_19 PARTITION: 1 4 (cr=809 pr=0 pw=0 time=4284 us cost=8 size=27 card=1)(object id 794293)
       105        105        105        SORT AGGREGATE (cr=317 pr=0 pw=0 time=1888 us)
       104        104        104         PARTITION LIST SINGLE PARTITION: 2 2 (cr=317 pr=0 pw=0 time=1620 us cost=4 size=27 card=1)
       104        104        104          FIRST ROW  (cr=317 pr=0 pw=0 time=1392 us cost=4 size=27 card=1)
       104        104        104           INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_07 PARTITION: 2 2 (cr=317 pr=0 pw=0 time=1278 us cost=4 size=27 card=1)(object id 818350)
        33         33         33    SORT AGGREGATE (cr=388 pr=0 pw=0 time=2810 us)
         2          2          2     COUNT STOPKEY (cr=388 pr=0 pw=0 time=2742 us)
         2          2          2      PARTITION LIST ALL PARTITION: 1 4 (cr=388 pr=0 pw=0 time=2670 us cost=13 size=27 card=1)
         2          2          2       TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 4 (cr=388 pr=0 pw=0 time=2559 us cost=13 size=27 card=1)
       255        255        255        INDEX RANGE SCAN IX_MMOHIPRC_03 PARTITION: 1 4 (cr=296 pr=0 pw=0 time=1718 us cost=12 size=0 card=1)(object id 767321)
        33         33         33         SORT AGGREGATE (cr=68 pr=0 pw=0 time=532 us)
        27         27         27          PARTITION LIST SINGLE PARTITION: 2 2 (cr=68 pr=0 pw=0 time=453 us cost=3 size=25 card=1)
        27         27         27           FIRST ROW  (cr=68 pr=0 pw=0 time=383 us cost=3 size=25 card=1)
        27         27         27            INDEX RANGE SCAN (MIN/MAX) IX_PMIHINPT_11 PARTITION: 2 2 (cr=68 pr=0 pw=0 time=354 us cost=3 size=25 card=1)(object id 795657)
       105        105        105  SORT AGGREGATE (cr=660 pr=0 pw=0 time=8139 us)
      5369       5369       5369   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=660 pr=0 pw=0 time=8038 us cost=1 size=27 card=1)
      5369       5369       5369    INLIST ITERATOR  (cr=660 pr=0 pw=0 time=5547 us)
      5369       5369       5369     INDEX RANGE SCAN IX_PMOHOTPT_07 PARTITION: KEY KEY (cr=660 pr=0 pw=0 time=3853 us cost=1 size=27 card=1)(object id 818350)
       105        105        105  SORT AGGREGATE (cr=855 pr=0 pw=0 time=5517 us)
         0          0          0   COUNT STOPKEY (cr=855 pr=0 pw=0 time=5300 us)
         0          0          0    NESTED LOOPS  (cr=855 pr=0 pw=0 time=4983 us)
        50         50         50     NESTED LOOPS  (cr=804 pr=0 pw=0 time=4616 us cost=5 size=56 card=1)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY KEY (cr=679 pr=0 pw=0 time=3864 us cost=4 size=39 card=1)
        50         50         50       TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: KEY KEY (cr=679 pr=0 pw=0 time=3593 us cost=4 size=39 card=1)
        50         50         50        INDEX RANGE SCAN IX_MMRMFORMREC_01 PARTITION: KEY KEY (cr=633 pr=0 pw=0 time=3246 us cost=3 size=0 card=1)(object id 767736)
       105        105        105         SORT AGGREGATE (cr=318 pr=0 pw=0 time=1872 us)
        98         98         98          PARTITION LIST SINGLE PARTITION: KEY KEY (cr=318 pr=0 pw=0 time=1656 us cost=4 size=33 card=1)
        98         98         98           FIRST ROW  (cr=318 pr=0 pw=0 time=1383 us cost=4 size=33 card=1)
        98         98         98            INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_08 PARTITION: KEY KEY (cr=318 pr=0 pw=0 time=1270 us cost=4 size=33 card=1)(object id 795652)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY(AP) KEY(AP) (cr=125 pr=0 pw=0 time=570 us cost=1 size=0 card=1)
        50         50         50       INDEX UNIQUE SCAN PK_MMRDRECHIST PARTITION: KEY(AP) KEY(AP) (cr=125 pr=0 pw=0 time=407 us cost=1 size=0 card=1)(object id 767661)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRDRECHIST PARTITION: 1 1 (cr=51 pr=0 pw=0 time=247 us cost=1 size=17 card=1)
       105        105        105  SORT AGGREGATE (cr=419 pr=0 pw=0 time=2296 us)
       104        104        104   COUNT STOPKEY (cr=419 pr=0 pw=0 time=2047 us)
       104        104        104    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=419 pr=0 pw=0 time=1720 us cost=4 size=32 card=1)
       104        104        104     TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=419 pr=0 pw=0 time=1415 us cost=4 size=32 card=1)
       104        104        104      INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=864 us cost=3 size=0 card=1)(object id 767381)
       105        105        105  SORT AGGREGATE (cr=41 pr=0 pw=0 time=747 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID MMRDMEMO (cr=41 pr=0 pw=0 time=546 us cost=4 size=41 card=1)
         0          0          0    INDEX RANGE SCAN PK_MMRDMEMO (cr=41 pr=0 pw=0 time=442 us cost=2 size=0 card=1)(object id 765770)
       105        105        105  SORT AGGREGATE (cr=114 pr=0 pw=0 time=8098 us)
         0          0          0   NESTED LOOPS  (cr=114 pr=0 pw=0 time=1438 us)
         0          0          0    NESTED LOOPS  (cr=114 pr=0 pw=0 time=1307 us cost=4 size=65 card=1)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=114 pr=0 pw=0 time=1205 us cost=3 size=42 card=1)
         0          0          0      TABLE ACCESS BY LOCAL INDEX ROWID MMOHCCPT PARTITION: 2 2 (cr=114 pr=0 pw=0 time=1023 us cost=3 size=42 card=1)
         6          6          6       INDEX RANGE SCAN IX_MMOHCCPT_01 PARTITION: 2 2 (cr=108 pr=0 pw=0 time=808 us cost=1 size=0 card=1)(object id 779517)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
         0          0          0      INDEX UNIQUE SCAN PK_MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 767656)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=184 card=8)
        40         40         40  COUNT STOPKEY (cr=355 pr=0 pw=0 time=2061 us)
        40         40         40   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=355 pr=0 pw=0 time=1757 us cost=4 size=29 card=1)
        40         40         40    TABLE ACCESS BY LOCAL INDEX ROWID MRCHMRHS PARTITION: KEY KEY (cr=355 pr=0 pw=0 time=1460 us cost=4 size=29 card=1)
        40         40         40     INDEX RANGE SCAN IX_MRCHMRHS_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1039 us cost=3 size=0 card=1)(object id 767891)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=114 pr=0 pw=0 time=1268 us cost=3 size=23 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=114 pr=0 pw=0 time=984 us cost=3 size=23 card=1)
         0          0          0    INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=114 pr=0 pw=0 time=815 us cost=2 size=0 card=1)(object id 767826)
         2          2          2     SORT AGGREGATE (cr=6 pr=0 pw=0 time=51 us)
         0          0          0      COUNT STOPKEY (cr=6 pr=0 pw=0 time=37 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=6 pr=0 pw=0 time=31 us cost=3 size=19 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=6 pr=0 pw=0 time=28 us cost=3 size=19 card=1)
         6          6          6         INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=19 us cost=2 size=0 card=1)(object id 767826)
         0          0          0  COUNT STOPKEY (cr=526 pr=0 pw=0 time=2857 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=526 pr=0 pw=0 time=2554 us cost=4 size=38 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=526 pr=0 pw=0 time=2320 us cost=4 size=38 card=1)
       884        884        884     INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=321 pr=0 pw=0 time=1394 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=742 us cost=2 size=18 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MMOHPBMT PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=480 us cost=2 size=18 card=1)
         0          0          0    INDEX RANGE SCAN IX_MMOHPBMT_01 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=313 us cost=1 size=0 card=1)(object id 779522)
       105        105        105  SORT ORDER BY (cr=8097 pr=0 pw=0 time=84438 us cost=13 size=228 card=1)
       105        105        105   WINDOW SORT (cr=724 pr=0 pw=0 time=7849 us cost=13 size=228 card=1)
       105        105        105    NESTED LOOPS OUTER (cr=724 pr=0 pw=0 time=13364 us cost=11 size=228 card=1)
       105        105        105     NESTED LOOPS  (cr=512 pr=0 pw=0 time=11160 us cost=9 size=191 card=1)
       105        105        105      NESTED LOOPS  (cr=194 pr=0 pw=0 time=2194 us cost=8 size=138 card=1)
       105        105        105       PARTITION LIST SINGLE PARTITION: 2 2 (cr=185 pr=0 pw=0 time=1454 us cost=7 size=254 card=2)
       105        105        105        TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: 2 2 (cr=185 pr=0 pw=0 time=1344 us cost=7 size=254 card=2)
       347        347        347         INDEX RANGE SCAN IX_PMOHOTPT_01 PARTITION: 2 2 (cr=5 pr=0 pw=0 time=384 us cost=4 size=0 card=1)(object id 796979)
       105        105        105       INDEX UNIQUE SCAN UK_ZBCMCODE_02 (cr=9 pr=0 pw=0 time=258 us cost=1 size=11 card=1)(object id 96118)
       105        105        105      PARTITION LIST SINGLE PARTITION: 2 2 (cr=318 pr=0 pw=0 time=1485 us cost=1 size=53 card=1)
       105        105        105       TABLE ACCESS BY LOCAL INDEX ROWID PMCMPTBS PARTITION: 2 2 (cr=318 pr=0 pw=0 time=1229 us cost=1 size=53 card=1)
       105        105        105        INDEX UNIQUE SCAN PK_PMCMPTBS PARTITION: 2 2 (cr=212 pr=0 pw=0 time=652 us cost=1 size=0 card=1)(object id 774609)
         0          0          0     TABLE ACCESS BY INDEX ROWID MNVHWGIM (cr=212 pr=0 pw=0 time=1214 us cost=2 size=37 card=1)
         0          0          0      INDEX RANGE SCAN PK_MNVHWGIM (cr=212 pr=0 pw=0 time=896 us cost=1 size=0 card=1)(object id 765479)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  SQL*Net message from client                     8        2.88          3.80
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.05       0.08          0         78          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        8      0.05       0.08          0       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.10       0.16          0       8175          0         105
 
Misses in library cache during parse: 1
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      10        0.00          0.00
  SQL*Net message from client                    10        8.30         19.75
  SQL*Net more data from client                   2        0.00          0.00
 

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
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          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1
 
Misses in library cache during parse: 0
 
    2  user  SQL statements in session.
    1  internal SQL statements in session.
    3  SQL statements in session.
********************************************************************************
Trace file: HISEDU_ora_3998130.trc
Trace file compatibility: 11.1.0.7
Sort options: default
 
       1  session in tracefile.
       2  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
     546  lines in trace file.
      11  elapsed seconds in trace file.
 

 
댓글 (0)
목록 답변등록
105조회시에 12초 걸리는 경우
jhchoi 2013.08.28


Trace 상에서 아래에 해당하는 부분이 메인쿼리에 대한 마지막 부분이며
그 위에 있는 Opertaion 들은 모두 스칼라서브쿼리에 대한 부분입니다.


......
105  SORT ORDER BY (cr=8097 pr=2506 pw=0 time=12685172 us cost=13 size=227 card=1)
105   WINDOW SORT (cr=725 pr=291 pw=0 time=955800 us cost=13 size=227 card=1)
......


위의 Trace 결과에서 WINDOW SORT 까지 cr은 725 블럭, Elapsed Time은 0.95초가 소요되었습니다.
여기까지는 메인쿼리가 성능상에 큰 문제가 있다고 보여지지 않습니다.
그러나 그 다음 SORT ORDER BY 부분에서 cr이 갑자기 8097 블럭으로 급격히 증가하면서
Elapsed Time도 12.68초로 증가하고 있습니다.

 

이는 메인쿼리의 최종 결과인 105건을 Sort 하면서 발생하고 있는 문제가 아닙니다.
위의 WINDOW SORT 와 SORT ORDER BY 사이에는 메인쿼리의 SELECT절에 사용된 수많은
스칼라서브쿼리를 처리한 결과가 반영되어 있는 것입니다.

 

16개 정도 되는 스칼라서브쿼리 블럭 각각에서 발생한 cr 과 Elapsed Time 을 모두 더해보면
메인쿼리의 최종 Operation (SORT ORDER BY) 에 나타나고 있는 cr과 Elapsed Time 과
거의 들어맞음을 알 수 있습니다.

 

따라서, 이 쿼리의 성능의 문제는 메인쿼리 부분이 아니라 수많은 스칼라서브쿼리가
원인입니다.

 

그러나 더 큰 문제는, 16개의 스칼라서브쿼리블럭 중 특정 쿼리블럭에서만 문제가 발생하고
있는 것이 아니라 대부분의 스칼라서브쿼리블럭에서 문제가 발생하고 있고 그 결과의 누적이
전체 쿼리의 성능문제로 나타나고 있는 점입니다.


각 스칼라서브쿼리에서 발생하고 있는 가장 큰 문제점은 바로 인덱스의 비효율입니다.
인덱스 비효율은 크게 아래 2가지 유형으로 나타나고 있습니다.


 

인덱스 비효율 유형 1)  각 인덱스에서 처리한 결과 건수에 비해 cr 블럭수가 지나치게 크게 나타나고 있습니다.

 

......
1     INDEX RANGE SCAN PK_PMCHCAPM PARTITION: KEY KEY (cr=212 pr=0 pw=0 time=996 us cost=2 size=0 card=1)(object id 775359)
......
0      INDEX RANGE SCAN IX_PMOHOTPT_05 PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=710 us cost=4 size=0 card=1)(object id 774419)
......
0      INDEX RANGE SCAN IX_MMRHCNSTINFO_01 PARTITION: KEY KEY (cr=315 pr=102 pw=0 time=601250 us cost=3 size=0 card=1)(object id 767676)
......
7           INDEX RANGE SCAN IX_MMRMFORMREC_03 PARTITION: 1 4 (cr=616 pr=108 pw=0 time=603035 us cost=8 size=0 card=1)(object id 767726)


위의 몇개 예와 같이 가장 좌측에 보이는 인덱스 Range Scan 의 결과건수에 비해서 많은 블럭을 액세스하고 있음을 알 수 있습니다. (cr)

이는 일반적으로 인덱스를 구성하고 있는 컬럼의 순서가 문제가 있음을 나타냅니다.
인덱스에서 Range Scan 의 범위를 크게 줄여줄 수 있는 조건의 컬럼이 Access 조건이 아닌 Filter 조건으로 처리되고 있으며
이로 인해 불필요한 많은 범위를 Range Scan 하고나서 모두 버리는 비효율이 발생하고 있습니다.

 

대부분의 인덱스에서 이러한 비효율이 나타나고 있는데
이러한 비효율을 제거하기 위해서는 해당 테이블을 액세스하는 SQL들의 WHERE 조건을 모두 조사하고

모든 조건들을 커버할 수 있도록 관련 인덱스의 컬럼 순서를 재조정하여

인덱스 Range Scan 의 범위를 크게 줄여줄 수 있는 조건컬럼들이
Filter 가 아닌 Access 조건으로 처리될 수 있도록 해야 합니다.

 

하지만 운영중인 시스템에서 이렇게 많은 인덱스들의 컬럼을 재조정하는 작업은 Risk가 매우 큽니다.
해당 인덱스를 이용하고 있는 SQL들을 모두 파악하지 않고서는 자칫 다른 SQL에서 성능문제가
발생할 수 있기 때문입니다.

 

엔코아에는 SQL들을 수집하고 WHERE 조건절을 자동 파싱해 주는 IDO 라는 툴이 있어서
인덱스를 최적화 작업을 좀 더 수월하게 할 수 있지만  이러한 툴 없이 SQL들을 모두 수집하고

WHERE 조건들을 눈으로 확인하는 작업은 만만치 않은 작업입니다.

 

 

인덱스 비효율 유형 2)  테이블 랜덤 I/O 를 한 후에 대부분의 데이터가 버려지는 비효율이 발생하고 있습니다.

 

......
  0        TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=496 pr=347 pw=0 time=1407134 us cost=4 size=29 card=1)
824         INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=300 pr=164 pw=0 time=923313 us cost=3 size=0 card=1)(object id 767381)
......
  0    TABLE ACCESS BY LOCAL INDEX ROWID PMCMSPIF PARTITION: KEY KEY (cr=456 pr=230 pw=0 time=1237620 us cost=4 size=49 card=1)
608     INDEX RANGE SCAN IX_PMCMSPIF_01 PARTITION: KEY KEY (cr=321 pr=110 pw=0 time=652231 us cost=3 size=0 card=1)(object id 775434)
......


인덱스에서 Range Scan 한 결과 건수가 824건, 608건 등으로 적지 않은 건수가
테이블을 액세스한 후에 모두 버려지는 비효율이 발생하고 있습니다.

이는 처리범위를 크게 줄여주는 조건의 컬럼이 인덱스에 존재하지 않아 테이블까지 액세스할 수 밖에
없는 상황입니다.


해당 조건컬럼을 인덱스에 포함하여 인덱스를 구성한다면 테이블까지 액세스하기 전에 인덱스에서
먼저 최대한 Filtering 할 수 있어 테이블 랜덤 I/O 를 줄일 수 있습니다.

 

이렇게 SQL이 처리되는 과정에서 불필요한 블럭을 많이 액세스하면 할 수록
Buffer Cache 에서 밀려나서 Disk I/O가 자꾸 발생하고 그래서 SQL 성능문제가
발생할 가능성이 커집니다.
따라서  SQL이 비효율 없이 최소한의 필요한 블럭만 액세스하도록 개선하는 것이

SQL 튜닝의 근본적인 원리입니다.

 

 

또 한가지 고려해봐야 할 점은,
Buffer Cache 에서 블럭이 계속 밀려나서 Disk I/O 가 많이 발생하고 있다면
이렇게 Buffer Cache 의 효율을 떨어뜨리는 원인이 무엇인지 파악해 볼 필요가 있습니다.

 

특정 SQL이 매우 비효율적으로 많은 블럭을 액세스 하고 있다거나
특정 시간대에 성능문제가 자주 발생한다면 그 시점에 배치작업과 같은 큰 SQL들이
같이 돌아가고 있지 않은가... 등을 파악해서
문제의 SQL을 튜닝하거나 배치프로그램이라면 처리시간대를 조정하거나 하는 등의
노력이 필요할 것 같습니다.

 

불필요하게 많은 블럭을 액세스하는 비효율적인 SQL들이 많지 않음에도 Buffer Cache Hit율이 현저히
떨어진다면 Buffer Cache 의 사이즈가 적정한지도 검토해보아야 할 것입니다.

 

 

 

 

---------------------------------------  원문 ----------------------------------------------

 

105건 조회하는데요

12초 걸리는 경우도 있고, 1초안에 조회되는 경우도 있습니다.
 
12초 걸리는 경우의 trace입니다.
- 물리적인 IO가 늘리다는거 이외에 다른 이유를 찾을 수 있을까요?
--

TKPROF: Release 11.2.0.3.0 - Development on Wed Aug 21 20:32:58 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: HISEDU_ora_47382576.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: 77xfzx37m45ua Plan Hash: 0
 
alter session set events '10357 trace name context forever, level 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        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: SYS
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        9.71          9.71
********************************************************************************
 
SQL ID: 3nkd3g3ju5ph1 Plan Hash: 853875749
 
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        1      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        5      0.00       0.00          0          6          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 OBJ$ (cr=3 pr=0 pw=0 time=35 us cost=4 size=86 card=1)
         0          0          0   INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=31 us cost=3 size=0 card=1)(object id 37)
 
********************************************************************************
 
SQL ID: 7ng34ruy5awxq Plan Hash: 3984801583
 
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        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        9      0.00       0.00          0         32          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         32          0           5
 
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
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT ORDER BY (cr=9 pr=0 pw=0 time=2567 us cost=7 size=384 card=2)
         1          1          1   HASH JOIN OUTER (cr=9 pr=0 pw=0 time=2543 us cost=6 size=384 card=2)
         1          1          1    NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=55 us cost=3 size=298 card=2)
         1          1          1     TABLE ACCESS CLUSTER IND$ (cr=4 pr=0 pw=0 time=46 us cost=2 size=194 card=2)
         1          1          1      INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=22 us cost=1 size=0 card=1)(object id 3)
         0          0          0     TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=4 us cost=1 size=52 card=1)
         0          0          0      INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=4 us cost=1 size=0 card=1)(object id 433)
         1          1          1    VIEW  (cr=4 pr=0 pw=0 time=68 us cost=3 size=43 card=1)
         1          1          1     SORT GROUP BY (cr=4 pr=0 pw=0 time=64 us cost=3 size=15 card=1)
         1          1          1      TABLE ACCESS CLUSTER CDEF$ (cr=4 pr=0 pw=0 time=25 us cost=2 size=15 card=1)
         1          1          1       INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=8 us cost=1 size=0 card=1)(object id 30)
 
********************************************************************************
 
SQL ID: 5n1fs4m2n2y0r Plan Hash: 992489688
 
select pos#,intcol#,col#,spare1,bo#,spare2,spare3
from
 icol$ where obj#=:1
 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       15      0.00       0.00          0         30          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.00          0         30          0          10
 
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
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS BY INDEX ROWID ICOL$ (cr=10 pr=0 pw=0 time=33 us cost=2 size=81 card=3)
         4          4          4   INDEX RANGE SCAN I_ICOL1 (cr=6 pr=0 pw=0 time=35 us cost=1 size=0 card=3)(object id 42)
 
********************************************************************************
 
SQL ID: 83taa7kaw59c1 Plan Hash: 2783779297
 
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        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch       58      0.00       0.00          0         27          0          54
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       63      0.00       0.00          0         27          0          54
 
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
---------- ---------- ----------  ---------------------------------------------------
        20         20         20  SORT ORDER BY (cr=4 pr=0 pw=0 time=101 us cost=3 size=944 card=16)
        20         20         20   TABLE ACCESS CLUSTER COL$ (cr=4 pr=0 pw=0 time=54 us cost=2 size=944 card=16)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=20 us cost=1 size=0 card=1)(object id 3)
 
********************************************************************************
 
SQL ID: 9gkq7rruycsjp Plan Hash: 2930975892
 
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        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          9          0           3
 
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=3 pr=0 pw=0 time=19 us cost=2 size=46 card=1)
         1          1          1   INDEX UNIQUE SCAN I_PARTOBJ$ (cr=2 pr=0 pw=0 time=12 us cost=1 size=0 card=1)(object id 565)
 
********************************************************************************
 
SQL ID: cbdfcfcp1pgtp Plan Hash: 2703454998
 
select intcol#, col# , type#, spare1, segcol#, charsetform
from
 partcol$  where obj# = :1 order by pos#
 

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.00       0.01          2          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.01          2          9          0           3
 
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=3 pr=1 pw=0 time=4343 us cost=3 size=27 card=1)
         1          1          1   TABLE ACCESS BY INDEX ROWID PARTCOL$ (cr=3 pr=1 pw=0 time=4329 us cost=2 size=27 card=1)
         1          1          1    INDEX RANGE SCAN I_PARTCOL$ (cr=2 pr=0 pw=0 time=2510 us cost=1 size=0 card=1)(object id 567)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.00          0.01
********************************************************************************
 
SQL ID: 1gu8t96d0bdmu Plan Hash: 2959582498
 
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        1      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         10          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0         10          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  NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=47 us cost=4 size=182 card=1)
         1          1          1   TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=33 us cost=2 size=143 card=1)
         1          1          1    INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=20 us cost=1 size=0 card=1)(object id 3)
         0          0          0   TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=2 pr=0 pw=0 time=10 us cost=2 size=39 card=1)
         0          0          0    INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=2 pr=0 pw=0 time=9 us cost=1 size=0 card=1)(object id 431)
 
********************************************************************************
 
SQL ID: 130dvvr5s8bgn Plan Hash: 2800640262
 
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        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.05          9         24          0           8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.05          9         24          0           8
 
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: 2
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS BY INDEX ROWID TABPART$ (cr=4 pr=0 pw=0 time=903 us cost=3 size=656 card=4)
         4          4          4   INDEX RANGE SCAN I_TABPART_BOPART$ (cr=3 pr=0 pw=0 time=20 us cost=2 size=0 card=4)(object id 571)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                         9        0.00          0.05
********************************************************************************
 
SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3632979230
 
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        1      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          1         36          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.00       0.01          1         36          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: 2)
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS CLUSTER SEG$ (cr=3 pr=1 pw=0 time=8618 us cost=2 size=71 card=1)
         1          1          1   INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=1 pw=0 time=8603 us cost=1 size=0 card=1)(object id 9)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         1        0.00          0.00
********************************************************************************
 
SQL ID: 3ktacv9r56b51 Plan Hash: 458693102
 
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        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          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          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=0 pw=0 time=40 us cost=10 size=213 card=3)
         0          0          0   NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=26 us cost=9 size=213 card=3)
         0          0          0    TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=0 pw=0 time=22 us cost=4 size=81 card=3)
         0          0          0     INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=0 pw=0 time=21 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=44 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: 872636971
 
select order#,columns,types
from
 access$ where d_obj#=: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          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          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=0 pw=0 time=16 us cost=3 size=168 card=7)
         0          0          0   INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=0 pw=0 time=14 us cost=2 size=0 card=7)(object id 108)
 
********************************************************************************
 
SQL ID: c3zymn7x3k6wy Plan Hash: 1164454930
 
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        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          2          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          2          8          0           4
 
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
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS BY INDEX ROWID INDPART$ (cr=4 pr=2 pw=0 time=14599 us cost=3 size=708 card=4)
         4          4          4   INDEX RANGE SCAN I_INDPART_BOPART$ (cr=3 pr=1 pw=0 time=8603 us cost=2 size=0 card=4)(object id 576)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.00          0.01
********************************************************************************
 
SQL ID: 96g93hntrzjtr Plan Hash: 841937906
 
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        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.01          2         12          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.01          2         12          0           3
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
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 HIST_HEAD$ (cr=4 pr=2 pw=0 time=15979 us)
         1          1          1   INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=3 pr=1 pw=0 time=5709 us)(object id 427)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.01          0.01
********************************************************************************
 
SQL ID: cb21bacyh3c7d Plan Hash: 3488560417
 
select metadata
from
 kopm$  where name='DB_FDO'
 

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          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1
 
Misses in library cache during parse: 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
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=12 us cost=1 size=108 card=1)
         1          1          1   INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=5 us cost=1 size=0 card=1)(object id 545)
 
********************************************************************************
 
SQL ID: 3w4qs0tbpmxr6 Plan Hash: 3924106966
 
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3
from
 cdef$ where robj#=:1
 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      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          4          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          4          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 CDEF$ (cr=2 pr=0 pw=0 time=25 us cost=2 size=99 card=3)
         0          0          0   INDEX RANGE SCAN I_CDEF3 (cr=2 pr=0 pw=0 time=22 us cost=1 size=0 card=3)(object id 55)
 
********************************************************************************
 
SQL ID: gx4mv66pvj3xz Plan Hash: 3886069984
 
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        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch       34      0.00       0.00          0         40          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       37      0.00       0.00          0         40          0          32
 
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
---------- ---------- ----------  ---------------------------------------------------
        12         12         12  TABLE ACCESS CLUSTER CDEF$ (cr=16 pr=0 pw=0 time=22 us cost=2 size=440 card=8)
         1          1          1   INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=10 us cost=1 size=0 card=1)(object id 30)
 
********************************************************************************
 
SQL ID: 53saa2zkr6wc3 Plan Hash: 2631433895
 
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
 ccol$ where con#=:1
 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     32      0.00       0.00          0          0          0           0
Fetch       68      0.00       0.01          1        136          0          36
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.00       0.01          1        136          0          36
 
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
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  TABLE ACCESS BY INDEX ROWID CCOL$ (cr=6 pr=1 pw=0 time=10026 us cost=3 size=18 card=1)
         2          2          2   INDEX RANGE SCAN I_CCOL1 (cr=4 pr=1 pw=0 time=10021 us cost=2 size=0 card=1)(object id 57)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         1        0.00          0.00
********************************************************************************
 
  SELECT
          otpt.elbulbodstat
        , otpt.ordtm
        , CASE WHEN
                    (case when count(ptbs.hngnm) over(partition by ptbs.hngnm) > 1
                    then
                        case when count(ptbs.hngnm) over(partition by ptbs.pid, ptbs.hngnm) > 1
                        then
                            1
                        else
                         count(ptbs.hngnm) over(partition by ptbs.hngnm)
                        end
                    else
                     count(ptbs.hngnm) over(partition by ptbs.hngnm)
                    end) > 1
                   THEN
                        ptbs.hngnm || '(.)'
                   ELSE
                        ptbs.hngnm
                   END AS hngnm
        , otpt.pid
        , otpt.orddeptcd
        , ptbs.sex || '/'
                   || COM.FN_ZZ_GETAGE_EMR(ptbs.rrgstno1, ptbs.rrgstno2, otpt.orddd, 'A', ptbs.brthdd)   AS sa
        , otpt.fsexamflag
        , otpt.etcordflag
              , (SELECT term.termengnm diagengnm
                   FROM emr.mmohdiag diag,                     -- .........
                        emr.mrtmterm term                      -- .....
                  WHERE diag.pid       = otpt.pid
                    AND diag.orddd     = otpt.orddd
                    AND diag.cretno    = otpt.cretno
                    AND diag.orddeptcd = otpt.orddeptcd
                    AND diag.instcd    = otpt.instcd
                    AND diag.diagcd    = term.termcd
                    AND diag.diagdd   >= term.termfromdd
                    AND diag.diagdd   <= term.termtodd
                    AND diag.instcd    = term.instcd
                    AND diag.diaghistcd     = 'O'
                    AND diag.diagtypecd     = 'D'
                    AND diag.diagkindcdflag = 'M'
                    AND ROWNUM = 1)                          AS diagnm                                             -- ...
        , otpt.instcd
        , otpt.orddd
        , otpt.cretno
        , otpt.orddrid
              , otpt.prcptdayaftrcptyn
              , otpt.rcptvipresncd
              , otpt.rcptvipetcresn
              , otpt.medamtpostyn
              , CASE WHEN (SELECT a.rcptdelivefact
                             FROM pam.pmchcapm a
                            WHERE a.instcd = otpt.instcd
                              AND a.pid    = otpt.pid
                              AND a.civilaplendflagcd IN ('Y', 'R')
                              AND a.drdelive = 'Y'
                              AND a.civilaplclamdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND a.civilenddd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND rownum = 1  ) is not NULL THEN 'Y'
                                                            ELSE 'N'
                     END                                                                     AS civilap
     ,   DECODE (otpt.ordstartdt, '00000000000000', null, SUBSTR(otpt.ordstartdt, 9, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 11, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 13, 2)         ) AS ordstartdt          -- ......
              ,   CASE WHEN otpt.dracptyn ='Y' THEN (DECODE (otpt.dracptdt,   '-', null, SUBSTR(otpt.dracptdt, 9, 2)  ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 11, 2) ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 13, 2)   )  )
                        END                                      AS dracptdt
              ,   CASE WHEN otpt.nursacptyn ='Y' THEN (DECODE (otpt.nursacptdt, '-', null, SUBSTR(otpt.nursacptdt, 9, 2)  ||':'||
                                                                                           SUBSTR(otpt.nursacptdt, 11, 2)         ) )
                        END                                AS nursacptdt
              ,   (SELECT CASE WHEN count(1) > 0 THEN  'Y'
                               ELSE 'N'
                           END
                     FROM pam.pmohotpt totp
                    WHERE otpt.pid           =       totp.pid
                      AND otpt.instcd        =       totp.instcd
                      AND otpt.orddd         >=      to_char(to_date(totp.orddd,  'YYYYMMDD')-7, 'YYYYMMDD')
                      AND otpt.orddd         <=       totp.orddd
                      AND otpt.cretno        !=       totp.cretno
                      AND totp.ordtype       =       'E'
                      AND rownum             =        1 )                       AS   eryn                 -- ER..
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                              ELSE 'N'
                         END
                    FROM emr.mmrhcnstinfo info
                   WHERE info.pid           =        otpt.pid
                     AND info.instcd        =        otpt.instcd
                     AND info.orddd         =        otpt.orddd
                     AND info.cretno        =        otpt.cretno
                     AND info.cnststat      in       ('12', '20', '21', '22', '30', '31', '32', '40')
                     AND rownum             =        1 )                        AS   coordyn            -- ....
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                             ELSE 'N'
                         END
                   FROM dual
                  WHERE (SELECT  CASE WHEN count(1) > 0 THEN 'Y'
                                      ELSE 'N'
                                  END
                           FROM  emr.mmrmformrec mrec,
                                 emr.mmrdrechist hist
                          WHERE  mrec.instcd       =      hist.instcd
                            AND  mrec.formrecseq   =      hist.formrecseq
                            AND  mrec.lastrechistseq = hist.rechistseq
                            AND  mrec.delyn        =      'N'
                            AND  mrec.valiyn       =      'Y'
                            AND  mrec.pid          =      otpt.pid
                            AND  mrec.orddd        =      otpt.orddd
                            AND  mrec.cretno       =      otpt.cretno
                            AND  hist.ESPISEQ      =      '0'
                            AND  rownum            =       1 ) ='Y'
                     OR
                        (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                                     ELSE 'N'
                                 END
                           FROM emr.mmohoprc oprc
                          WHERE oprc.instcd          =    otpt.instcd
                            AND oprc.pid             =    otpt.pid
                            AND oprc.orddd           =    otpt.orddd
                            AND oprc.cretno          =    otpt.cretno
                            AND oprc.prcphistcd      = 'T'
                            AND rownum               =    1 ) = 'Y'
                            AND rownum = 1 )                                  AS tempsaveyn          -- ......
              ,  (SELECT CASE WHEN  sasm.choiflag in ( 'B',  'C')  THEN 'Y'
                             ELSE 'N'
                         END
                        FROM pam.pmcmspif sasm --  pam.pmcmsasm sasm
                       WHERE sasm.instcd     =  otpt.instcd
                         AND sasm.pid        =  otpt.pid
                         AND sasm.orddeptcd  =  otpt.orddeptcd
                         AND sasm.orddrid    =  otpt.orddrid
                         AND sasm.ioflag     =  'O'
                         AND sasm.histstat   =  'Y'
                         AND sasm.specordyn  =  'Y'
                         AND sasm.fromdd     <= otpt.orddd
                         AND sasm.todd       >= otpt.orddd
                         AND rownum          =  1 )                          AS specordtype        --  ....
    ,(SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
               END
        FROM dual
       WHERE (
            SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohoprc oprc
             WHERE oprc.pid          = otpt.pid
               AND oprc.prcpdd       >=     (SELECT NVL(MAX(orddd), '20130605') AS fromdd
                                               FROM pam.pmohotpt
                                              WHERE instcd = '053'
                                                AND pid = otpt.pid
                                                AND orddd < '20130605'
                                                AND elbulbodstat = '2'
                                                AND histstat = 'R')
               AND oprc.prcpdd        <= otpt.orddd
               AND oprc.prcphistcd   = 'O'
               AND oprc.prcpflag     = '1'
               AND oprc.prcpstatcd   > '700'
               AND ROWNUM = 1 ) = 'Y'
          OR
            (SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohiprc iprc
             WHERE iprc.pid          = otpt.pid
               AND iprc.prcpdd       >=     ( SELECT NVL(MAX(dschdd), '20130605') AS formdd
                                                FROM pam.pmihinpt
                                               WHERE instcd = '053'
                                                 AND pid = otpt.pid
                                                 AND dschdd != '99991231'
                                                 AND histstat = 'Y')
               AND iprc.prcpdd        <= otpt.orddd
               AND iprc.prcphistcd   = 'O'
               AND iprc.prcpflag     = '1'
               AND iprc.prcpstatcd   > '700'
               AND ROWNUM = 1) = 'Y') as rsltyn         -- ....
      , otpt.insukind as insukind
    --, DECODE(code.cdid, '11','..', '21','..', '22','..', substr(code.cdnm,0,2)) insukind  --..(...2.....)
    , (SELECT NVL(MAX(a.orddd), otpt.orddd)
                     FROM pam.pmohotpt a
                    WHERE a.instcd = ptbs.instcd
                      AND a.pid = ptbs.pid
                      AND a.elbulbodstat = '2' --...... ..
                      AND a.orddd < otpt.orddd
                      AND a.histstat in ( 'R', 'T' )
                      ) as recsrchfromdd       --.... ....
                , (SELECT CASE WHEN count(*) = 1 then 'Y'
                               ELSE 'N'
                               END
                      FROM emr.mmrmformrec a,
                           emr.mmrdrechist b
                     WHERE a.instcd = otpt.instcd
                       AND a.pid = otpt.pid
                       AND a.chosflag = 'O'
                       AND a.lastformrecdd >= (SELECT NVL(MAX(a.orddd), otpt.orddd)
                                                 FROM pam.pmohotpt a
                                                WHERE a.instcd = otpt.instcd
                                                  AND a.pid = otpt.pid
                                                  AND a.orddeptcd = otpt.orddeptcd
                                                  AND a.orddrid = otpt.orddrid
                                                  AND a.orddd < otpt.orddd)
                       AND a.lastformrecdd <= otpt.orddd
                       AND a.valiyn = 'Y'
                       AND a.delyn = 'N'
                       AND b.instcd  = a.instcd
                       AND b.rechistseq = a.lastrechistseq
                       AND b.espiseq != 0
                       AND rownum = 1) as recyn
                  , (SELECT CASE WHEN count(*) = 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                       FROM emr.mmohoprc oprc
                      WHERE oprc.instcd = otpt.instcd
                        AND oprc.pid = otpt.pid
                        AND oprc.orddd = otpt.orddd
                        AND oprc.cretno = otpt.cretno
                        AND oprc.prcphistcd IN ('O', 'X', 'M')      -- Dump. ....
            AND oprc.prcpkindcd NOT IN ('30', '35', '40') -- ..... .. . .... ... ..
                        AND rownum = 1
                        ) as prcpyn
                   , (SELECT CASE WHEN count(*) >= 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                        FROM emr.mmrdmemo memo
                       WHERE memo.instcd = otpt.instcd
                         AND memo.pid = otpt.pid
                   --AND memo.deptcd = otpt.orddeptcd
                   AND (memo.memo IS NOT NULL OR nursememo IS NOT NULL)
                   AND memo.useflag IN ('P', 'D') --.... .. .. (P: .., D: ...)
                   ) as memoyn
                   , (SELECT  RTRIM (XMLAGG (XMLELEMENT ("a", cccl.conctypenm || ',')).EXTRACT ('//text()').getstringval (), ',') a
                        FROM  emr.mmohccpt ccpt
                            , emr.mmomcccl cccl
                       WHERE ccpt.conckindcd = cccl.conckindcd
                         AND ccpt.conctrgtcd = cccl.conctrgtcd
                         AND ccpt.conctypecd = cccl.conctypecd
                         AND ccpt.instcd     = cccl.instcd
                         AND ccpt.valifromdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                         AND ccpt.valitodd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                      AND ccpt.instcd      = '053'
                      AND ccpt.pid         = otpt.pid
                      AND ccpt.conctrgtcd   = CASE WHEN ccpt.conckindcd = 'D'  THEN otpt.orddeptcd
                                                                             ELSE '49170'
                                                                           END   )           AS conctypenm --....
       , otpt.rsrvflag
       , DECODE(   (SELECT 1
                         FROM emr.mrchmrhs mrhs
                        WHERE mrhs.instcd = otpt.instcd
                          AND mrhs.pid = otpt.pid
                          AND mrhs.ioflag = otpt.ordtype
                          AND mrhs.chrtflag = 'P'
                          AND mrhs.deldt >= SYSTIMESTAMP
                          AND ROWNUM  =1) , 1, 'Y', ''
                ) AS chartscanyn, --chart scan ..
       CASE WHEN DRACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(SUBSTR(DRACPTDT,9,4),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         WHEN NURSACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(TO_CHAR(sysdate,'hh24mi'),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         ELSE '-' END AS waittm--.... lsh
        /*,(SELECT CASE WHEN (SELECT hospgrade FROM ast.arhmchsp
                                        WHERE coophospcd = reqp.coophospcd
                                          AND instcd = reqp.instcd)  IN ('C1', 'C2', 'C3') THEN 'refer(..)'
                                 ELSE 'refer' END
                       FROM ast.arcmreqp reqp
                      WHERE reqp.pid = otpt.pid
                        AND reqp.instcd = otpt.instcd
                        AND reqp.orddeptcd = otpt.orddeptcd
                        AND otpt.orddd BETWEEN reqp.rgstdd and reqp.enddd
                        AND reqp.deldd = '00000000'
                        AND ROWNUM = 1)*/
                  ,DECODE( otpt.RQSTFLAG, '1','Y', '-')            AS refer
                        , DECODE((SELECT 'Y'
                           FROM emr.mnohdlvt z
                           WHERE z.instcd = otpt.instcd
                           AND z.pid = otpt.pid
                           AND z.seqno = (SELECT max(y.seqno)
                                            FROM emr.mnohdlvt y
                                           WHERE y.instcd = z.instcd
                                             AND y.pid = z.pid
                                             AND y.statcd = 'Y'
                                             AND rownum = 1)
                           AND (z.EXPTDLIVDD > = to_char(sysdate, 'YYYYMMDD') OR z.EXPTDLIVDD IS NULL) --....
                         ),'Y','01', case when ptbs.sex = 'F' and to_number( COM.FN_ZZ_GETAGE('-', '-', otpt.orddd, 'B', ptbs.brthdd)) between 15 and 55 then '02'
                            else '' end ) AS gravdpsbldliv  -- ....//  /*.... 01 .... 02 .... null. .. > .. ...... */
                , CASE WHEN  otpt.suppkind  IN ( '06' , '11', '61' , '62' , '63' ) THEN  '..'
                       WHEN  otpt.suppkind = '07' THEN '..'
                       WHEN  otpt.suppkind  IN ( '50' , '51' ) THEN  '..'
                       ELSE '-'
                  END as serdiag
               ,  decode( ( SELECT 'C'
                              FROM emr.mmohoprc oprc
                             WHERE oprc.instcd = otpt.instcd
                               AND oprc.pid = otpt.pid
                               AND oprc.orddd = otpt.orddd
                               AND oprc.cretno = otpt.cretno
                               AND oprc.prcphistcd = 'O'
                               AND oprc.prcpcd like 'XSCAN%'
                               AND ROWNUM = 1), 'C', 'C', decode(otpt.outercdrgstyn, 'N', '', otpt.outercdrgstyn )) as outercdrgstyn -- ....
               , decode(ptbs.kioskrcptnoyn, 'N','Y','') as kioskrcptnoyn  --....
               , CASE WHEN otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'E', 'S', 'P', 'B') AND otpt.etcordflag NOT IN ('M','J','N', '07') THEN ''
                      ELSE 'Y'
                 END as tempacpt
               , nvl((select bmtflag from emr.mmohpbmt pbmt WHERE pbmt.instcd = otpt.instcd AND pbmt.pid = otpt.pid and pbmt.histcd='O'),'-') as bmtflag
               , LPAD(NVL2(owgm.ordrsrvseqno,owgm.ordrsrvseqno,'0000'),4,0) as ordrsrvseqno     --......
               , owgm.ordrsrvtm                                                                 --......
          FROM  pam.pmohotpt otpt
        , pam.pmcmptbs ptbs
        , com.zbcmcode code
        , emr.mnvhwgim owgm
   WHERE otpt.histstat IN ('R', 'T')                                            -- .., .......
     AND otpt.ordtype  IN ('O', 'E')
     AND otpt.pid    = ptbs.pid
     AND otpt.instcd  = ptbs.instcd
     AND code.cdid     = otpt.insukind
     AND code.cdgrupid = 'P0008'
     AND  owgm.instcd(+) = otpt.instcd
           AND  owgm.pid(+)    = otpt.pid
        AND  owgm.orddd(+)  = otpt.orddd
        AND  owgm.cretno(+) = otpt.cretno
           AND otpt.orddd    = '20130605'
           AND otpt.instcd   = '053'
           AND otpt.orddeptcd    = 'ME'
           AND otpt.orddrid      = '49170'
           AND otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'S', 'P', 'B') --..., ...., ...., ....., .... .., ......, ..... ..
              --.... ... ..... .. ..
           AND NVL(otpt.etcordflag, '-') NOT IN ('M','J', 'N', '07')                 -- ...., .., .... ..
      ORDER BY owgm.ordrsrvtm, ordrsrvseqno, otpt.orddd, otpt.ordtm, otpt.NURSACPTDT , otpt.fstacptdt
     /* himed/his/emr/prcpmngtmgr/etcprcpmngtmgt/dao/sqls/etcprcpmngtdao_sqls.xml getSpeedOutPatList */
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.08          0         78          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.20      12.68       2506       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.25      12.77       2506       8175          0         105
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       104        104        104  COUNT STOPKEY (cr=838 pr=253 pw=0 time=1532038 us)
       104        104        104   NESTED LOOPS  (cr=838 pr=253 pw=0 time=1531311 us)
       104        104        104    NESTED LOOPS  (cr=734 pr=231 pw=0 time=1387057 us cost=5 size=131 card=1)
       104        104        104     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=422 pr=211 pw=0 time=1274024 us cost=4 size=58 card=1)
       104        104        104      TABLE ACCESS BY LOCAL INDEX ROWID MMOHDIAG PARTITION: KEY KEY (cr=422 pr=211 pw=0 time=1273694 us cost=4 size=58 card=1)
       161        161        161       INDEX RANGE SCAN IX_MMOHDIAG_01 PARTITION: KEY KEY (cr=315 pr=104 pw=0 time=648910 us cost=3 size=0 card=1)(object id 796990)
       104        104        104     INDEX RANGE SCAN PK_MRTMTERM (cr=312 pr=20 pw=0 time=112803 us cost=1 size=0 card=1)(object id 766242)
       104        104        104    TABLE ACCESS BY INDEX ROWID MRTMTERM (cr=104 pr=22 pw=0 time=143904 us cost=2 size=73 card=1)
         0          0          0  COUNT STOPKEY (cr=213 pr=1 pw=0 time=8139 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=213 pr=1 pw=0 time=7852 us cost=3 size=163 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCHCAPM PARTITION: KEY KEY (cr=213 pr=1 pw=0 time=7563 us cost=3 size=163 card=1)
         1          1          1     INDEX RANGE SCAN PK_PMCHCAPM PARTITION: KEY KEY (cr=212 pr=0 pw=0 time=996 us cost=2 size=0 card=1)(object id 775359)
       105        105        105  SORT AGGREGATE (cr=120 pr=0 pw=0 time=1804 us)
         0          0          0   COUNT STOPKEY (cr=120 pr=0 pw=0 time=1384 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=1145 us cost=5 size=29 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=891 us cost=5 size=29 card=1)
         0          0          0      INDEX RANGE SCAN IX_PMOHOTPT_05 PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=710 us cost=4 size=0 card=1)(object id 774419)
       105        105        105  SORT AGGREGATE (cr=315 pr=102 pw=0 time=602420 us)
         0          0          0   COUNT STOPKEY (cr=315 pr=102 pw=0 time=602086 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=315 pr=102 pw=0 time=601736 us cost=4 size=30 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRHCNSTINFO PARTITION: KEY KEY (cr=315 pr=102 pw=0 time=601442 us cost=4 size=30 card=1)
         0          0          0      INDEX RANGE SCAN IX_MMRHCNSTINFO_01 PARTITION: KEY KEY (cr=315 pr=102 pw=0 time=601250 us cost=3 size=0 card=1)(object id 767676)
       105        105        105  SORT AGGREGATE (cr=1147 pr=464 pw=0 time=2067691 us)
         7          7          7   COUNT  (cr=1147 pr=464 pw=0 time=2067470 us)
         7          7          7    FILTER  (cr=1147 pr=464 pw=0 time=2067346 us)
       105        105        105     FAST DUAL  (cr=0 pr=0 pw=0 time=128 us cost=2 size=0 card=1)
       105        105        105     SORT AGGREGATE (cr=651 pr=117 pw=0 time=651870 us)
         7          7          7      COUNT STOPKEY (cr=651 pr=117 pw=0 time=651597 us)
         7          7          7       NESTED LOOPS  (cr=651 pr=117 pw=0 time=651299 us)
         7          7          7        NESTED LOOPS  (cr=644 pr=115 pw=0 time=644139 us cost=10 size=78 card=1)
         7          7          7         PARTITION LIST ALL PARTITION: 1 4 (cr=623 pr=112 pw=0 time=625535 us cost=9 size=51 card=1)
         7          7          7          TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: 1 4 (cr=623 pr=112 pw=0 time=624997 us cost=9 size=51 card=1)
         7          7          7           INDEX RANGE SCAN IX_MMRMFORMREC_03 PARTITION: 1 4 (cr=616 pr=108 pw=0 time=603035 us cost=8 size=0 card=1)(object id 767726)
         7          7          7         INDEX RANGE SCAN IX_MMRDRECHIST_02 (cr=21 pr=3 pw=0 time=18417 us cost=1 size=0 card=1)(object id 766493)
         7          7          7        TABLE ACCESS BY GLOBAL INDEX ROWID MMRDRECHIST PARTITION: ROW LOCATION ROW LOCATION (cr=7 pr=2 pw=0 time=7003 us cost=1 size=27 card=1)
        98         98         98     SORT AGGREGATE (cr=496 pr=347 pw=0 time=1408125 us)
         0          0          0      COUNT STOPKEY (cr=496 pr=347 pw=0 time=1407855 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=496 pr=347 pw=0 time=1407473 us cost=4 size=29 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=496 pr=347 pw=0 time=1407134 us cost=4 size=29 card=1)
       824        824        824         INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=300 pr=164 pw=0 time=923313 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  COUNT STOPKEY (cr=456 pr=230 pw=0 time=1238484 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=456 pr=230 pw=0 time=1238052 us cost=4 size=49 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCMSPIF PARTITION: KEY KEY (cr=456 pr=230 pw=0 time=1237620 us cost=4 size=49 card=1)
       608        608        608     INDEX RANGE SCAN IX_PMCMSPIF_01 PARTITION: KEY KEY (cr=321 pr=110 pw=0 time=652231 us cost=3 size=0 card=1)(object id 775434)
       105        105        105  SORT AGGREGATE (cr=1198 pr=558 pw=0 time=2989389 us)
        74         74         74   FILTER  (cr=1198 pr=558 pw=0 time=2989056 us)
       105        105        105    FAST DUAL  (cr=0 pr=0 pw=0 time=102 us cost=2 size=0 card=1)
       105        105        105    SORT AGGREGATE (cr=809 pr=369 pw=0 time=2241094 us)
        72         72         72     COUNT STOPKEY (cr=809 pr=369 pw=0 time=2240764 us)
        72         72         72      PARTITION LIST ALL PARTITION: 1 4 (cr=809 pr=369 pw=0 time=2240388 us cost=8 size=27 card=1)
        72         72         72       INDEX RANGE SCAN IX_MMOHOPRC_19 PARTITION: 1 4 (cr=809 pr=369 pw=0 time=2239916 us cost=8 size=27 card=1)(object id 794293)
       105        105        105        SORT AGGREGATE (cr=317 pr=185 pw=0 time=1187875 us)
       104        104        104         PARTITION LIST SINGLE PARTITION: 2 2 (cr=317 pr=185 pw=0 time=1187354 us cost=4 size=27 card=1)
       104        104        104          FIRST ROW  (cr=317 pr=185 pw=0 time=1187002 us cost=4 size=27 card=1)
       104        104        104           INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_07 PARTITION: 2 2 (cr=317 pr=185 pw=0 time=1186889 us cost=4 size=27 card=1)(object id 795597)
        33         33         33    SORT AGGREGATE (cr=389 pr=189 pw=0 time=746794 us)
         2          2          2     COUNT STOPKEY (cr=389 pr=189 pw=0 time=746669 us)
         2          2          2      PARTITION LIST ALL PARTITION: 1 4 (cr=389 pr=189 pw=0 time=746550 us cost=13 size=27 card=1)
         2          2          2       TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 4 (cr=389 pr=189 pw=0 time=746361 us cost=13 size=27 card=1)
       302        302        302        INDEX RANGE SCAN IX_MMOHIPRC_03 PARTITION: 1 4 (cr=297 pr=82 pw=0 time=472729 us cost=12 size=0 card=1)(object id 767321)
        33         33         33         SORT AGGREGATE (cr=68 pr=32 pw=0 time=177672 us)
        27         27         27          PARTITION LIST SINGLE PARTITION: 2 2 (cr=68 pr=32 pw=0 time=177551 us cost=3 size=25 card=1)
        27         27         27           FIRST ROW  (cr=68 pr=32 pw=0 time=177452 us cost=3 size=25 card=1)
        27         27         27            INDEX RANGE SCAN (MIN/MAX) IX_PMIHINPT_11 PARTITION: 2 2 (cr=68 pr=32 pw=0 time=177413 us cost=3 size=25 card=1)(object id 795657)
       105        105        105  SORT AGGREGATE (cr=657 pr=26 pw=0 time=126144 us)
      5369       5369       5369   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=657 pr=26 pw=0 time=126694 us cost=1 size=27 card=1)
      5369       5369       5369    INLIST ITERATOR  (cr=657 pr=26 pw=0 time=123836 us)
      5369       5369       5369     INDEX RANGE SCAN IX_PMOHOTPT_07 PARTITION: KEY KEY (cr=657 pr=26 pw=0 time=121885 us cost=1 size=27 card=1)(object id 795597)
       105        105        105  SORT AGGREGATE (cr=856 pr=418 pw=0 time=2251952 us)
         0          0          0   COUNT STOPKEY (cr=856 pr=418 pw=0 time=2251603 us)
         0          0          0    NESTED LOOPS  (cr=856 pr=418 pw=0 time=2251086 us)
        50         50         50     NESTED LOOPS  (cr=805 pr=383 pw=0 time=2099354 us cost=5 size=56 card=1)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY KEY (cr=680 pr=351 pw=0 time=1954331 us cost=4 size=39 card=1)
        50         50         50       TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: KEY KEY (cr=680 pr=351 pw=0 time=1953940 us cost=4 size=39 card=1)
        50         50         50        INDEX RANGE SCAN IX_MMRMFORMREC_01 PARTITION: KEY KEY (cr=634 pr=315 pw=0 time=1808435 us cost=3 size=0 card=1)(object id 767736)
       105        105        105         SORT AGGREGATE (cr=319 pr=201 pw=0 time=1207478 us)
        98         98         98          PARTITION LIST SINGLE PARTITION: KEY KEY (cr=319 pr=201 pw=0 time=1207139 us cost=4 size=33 card=1)
        98         98         98           FIRST ROW  (cr=319 pr=201 pw=0 time=1206760 us cost=4 size=33 card=1)
        98         98         98            INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_08 PARTITION: KEY KEY (cr=319 pr=201 pw=0 time=1206632 us cost=4 size=33 card=1)(object id 795652)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY(AP) KEY(AP) (cr=125 pr=32 pw=0 time=131470 us cost=1 size=0 card=1)
        50         50         50       INDEX UNIQUE SCAN PK_MMRDRECHIST PARTITION: KEY(AP) KEY(AP) (cr=125 pr=32 pw=0 time=131203 us cost=1 size=0 card=1)(object id 767661)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRDRECHIST PARTITION: 1 1 (cr=51 pr=35 pw=0 time=142910 us cost=1 size=17 card=1)
       105        105        105  SORT AGGREGATE (cr=419 pr=16 pw=0 time=114743 us)
       104        104        104   COUNT STOPKEY (cr=419 pr=16 pw=0 time=114365 us)
       104        104        104    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=419 pr=16 pw=0 time=113939 us cost=4 size=32 card=1)
       104        104        104     TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=419 pr=16 pw=0 time=113500 us cost=4 size=32 card=1)
       104        104        104      INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=315 pr=10 pw=0 time=59538 us cost=3 size=0 card=1)(object id 767381)
       105        105        105  SORT AGGREGATE (cr=41 pr=0 pw=0 time=1052 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID MMRDMEMO (cr=41 pr=0 pw=0 time=850 us cost=4 size=41 card=1)
         0          0          0    INDEX RANGE SCAN PK_MMRDMEMO (cr=41 pr=0 pw=0 time=705 us cost=2 size=0 card=1)(object id 765770)
       105        105        105  SORT AGGREGATE (cr=114 pr=10 pw=0 time=58089 us)
         0          0          0   NESTED LOOPS  (cr=114 pr=10 pw=0 time=44474 us)
         0          0          0    NESTED LOOPS  (cr=114 pr=10 pw=0 time=44320 us cost=4 size=65 card=1)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=114 pr=10 pw=0 time=44191 us cost=3 size=42 card=1)
         0          0          0      TABLE ACCESS BY LOCAL INDEX ROWID MMOHCCPT PARTITION: 2 2 (cr=114 pr=10 pw=0 time=43922 us cost=3 size=42 card=1)
         6          6          6       INDEX RANGE SCAN IX_MMOHCCPT_01 PARTITION: 2 2 (cr=108 pr=4 pw=0 time=19182 us cost=1 size=0 card=1)(object id 779517)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
         0          0          0      INDEX UNIQUE SCAN PK_MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 767656)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=184 card=8)
        40         40         40  COUNT STOPKEY (cr=355 pr=130 pw=0 time=663012 us)
        40         40         40   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=355 pr=130 pw=0 time=662568 us cost=4 size=29 card=1)
        40         40         40    TABLE ACCESS BY LOCAL INDEX ROWID MRCHMRHS PARTITION: KEY KEY (cr=355 pr=130 pw=0 time=662201 us cost=4 size=29 card=1)
        40         40         40     INDEX RANGE SCAN IX_MRCHMRHS_01 PARTITION: KEY KEY (cr=315 pr=91 pw=0 time=404165 us cost=3 size=0 card=1)(object id 767891)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=114 pr=4 pw=0 time=34100 us cost=3 size=23 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=114 pr=4 pw=0 time=33747 us cost=3 size=23 card=1)
         0          0          0    INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=114 pr=4 pw=0 time=33532 us cost=2 size=0 card=1)(object id 767826)
         2          2          2     SORT AGGREGATE (cr=6 pr=2 pw=0 time=15100 us)
         0          0          0      COUNT STOPKEY (cr=6 pr=2 pw=0 time=15082 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=6 pr=2 pw=0 time=15075 us cost=3 size=19 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=6 pr=2 pw=0 time=15068 us cost=3 size=19 card=1)
         6          6          6         INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=19 us cost=2 size=0 card=1)(object id 767826)
         0          0          0  COUNT STOPKEY (cr=526 pr=3 pw=0 time=17781 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=526 pr=3 pw=0 time=17455 us cost=4 size=38 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=526 pr=3 pw=0 time=17167 us cost=4 size=38 card=1)
       884        884        884     INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=321 pr=0 pw=0 time=1724 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=1181 us cost=2 size=18 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MMOHPBMT PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=724 us cost=2 size=18 card=1)
         0          0          0    INDEX RANGE SCAN IX_MMOHPBMT_01 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=507 us cost=1 size=0 card=1)(object id 779522)
       105        105        105  SORT ORDER BY (cr=8097 pr=2506 pw=0 time=12685172 us cost=13 size=227 card=1)
       105        105        105   WINDOW SORT (cr=725 pr=291 pw=0 time=955800 us cost=13 size=227 card=1)
       105        105        105    NESTED LOOPS OUTER (cr=725 pr=291 pw=0 time=955171 us cost=11 size=227 card=1)
       105        105        105     NESTED LOOPS  (cr=513 pr=291 pw=0 time=953161 us cost=9 size=190 card=1)
       105        105        105      NESTED LOOPS  (cr=195 pr=181 pw=0 time=69377 us cost=8 size=137 card=1)
       105        105        105       PARTITION LIST SINGLE PARTITION: 2 2 (cr=186 pr=181 pw=0 time=68425 us cost=7 size=252 card=2)
       105        105        105        TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: 2 2 (cr=186 pr=181 pw=0 time=68418 us cost=7 size=252 card=2)
       347        347        347         INDEX RANGE SCAN IX_PMOHOTPT_01 PARTITION: 2 2 (cr=5 pr=3 pw=0 time=22197 us cost=4 size=0 card=1)(object id 796979)
       105        105        105       INDEX UNIQUE SCAN UK_ZBCMCODE_02 (cr=9 pr=0 pw=0 time=328 us cost=1 size=11 card=1)(object id 96118)
       105        105        105      PARTITION LIST SINGLE PARTITION: 2 2 (cr=318 pr=110 pw=0 time=613090 us cost=1 size=53 card=1)
       105        105        105       TABLE ACCESS BY LOCAL INDEX ROWID PMCMPTBS PARTITION: 2 2 (cr=318 pr=110 pw=0 time=612756 us cost=1 size=53 card=1)
       105        105        105        INDEX UNIQUE SCAN PK_PMCMPTBS PARTITION: 2 2 (cr=212 pr=12 pw=0 time=66565 us cost=1 size=0 card=1)(object id 774609)
         0          0          0     TABLE ACCESS BY INDEX ROWID MNVHWGIM (cr=212 pr=0 pw=0 time=1605 us cost=2 size=37 card=1)
         0          0          0      INDEX RANGE SCAN PK_MNVHWGIM (cr=212 pr=0 pw=0 time=1135 us cost=1 size=0 card=1)(object id 765479)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  Disk file operations I/O                      104        0.00          0.00
  db file sequential read                      2423        0.05         12.32
  db file parallel read                           9        0.02          0.14
  SQL*Net message from client                     8        3.92          4.83
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.05       0.08          0         78          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        8      0.20      12.68       2506       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.25      12.77       2506       8175          0         105
 
Misses in library cache during parse: 1
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      10        0.00          0.00
  SQL*Net message from client                    10        9.71         22.23
  SQL*Net more data from client                   2        0.00          0.00
  Disk file operations I/O                      104        0.00          0.00
  db file sequential read                      2423        0.05         12.32
  db file parallel read                           9        0.02          0.14
 

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       22      0.00       0.00          0          0          0           0
Execute     80      0.01       0.02          0          0          0           0
Fetch      223      0.00       0.12         17        390          0         173
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      325      0.03       0.15         17        390          0         173
 
Misses in library cache during parse: 17
Misses in library cache during execute: 16
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        3        0.00          0.00
  db file sequential read                        17        0.01          0.11
 
    2  user  SQL statements in session.
   22  internal SQL statements in session.
   24  SQL statements in session.
********************************************************************************
Trace file: HISEDU_ora_47382576.trc
Trace file compatibility: 11.1.0.7
Sort options: default
 
       1  session in tracefile.
       2  user  SQL statements in trace file.
      22  internal SQL statements in trace file.
      24  SQL statements in trace file.
      19  unique SQL statements in trace file.
    4389  lines in trace file.
      27  elapsed seconds in trace file.
 

----
참고로
disk IO가 발생하지 않는 경우입니다.
 

TKPROF: Release 11.2.0.3.0 - Development on Fri Aug 23 11:57:19 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Trace file: HISEDU_ora_3998130.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: 77xfzx37m45ua Plan Hash: 0
 
alter session set events '10357 trace name context forever, level 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        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: SYS
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        7.64          7.64
********************************************************************************
 
SQL ID: cb21bacyh3c7d Plan Hash: 3488560417
 
select metadata
from
 kopm$  where name='DB_FDO'
 

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          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1
 
Misses in library cache during parse: 0
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
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=27 us cost=1 size=108 card=1)
         1          1          1   INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=15 us cost=1 size=0 card=1)(object id 545)
 
********************************************************************************
 
  SELECT
          otpt.elbulbodstat
        , otpt.ordtm
        , CASE WHEN
                    (case when count(ptbs.hngnm) over(partition by ptbs.hngnm) > 1
                    then
                        case when count(ptbs.hngnm) over(partition by ptbs.pid, ptbs.hngnm) > 1
                        then
                            1
                        else
                         count(ptbs.hngnm) over(partition by ptbs.hngnm)
                        end
                    else
                     count(ptbs.hngnm) over(partition by ptbs.hngnm)
                    end) > 1
                   THEN
                        ptbs.hngnm || '(.)'
                   ELSE
                        ptbs.hngnm
                   END AS hngnm
        , otpt.pid
        , otpt.orddeptcd
        , ptbs.sex || '/'
                   || COM.FN_ZZ_GETAGE_EMR(ptbs.rrgstno1, ptbs.rrgstno2, otpt.orddd, 'A', ptbs.brthdd)   AS sa
        , otpt.fsexamflag
        , otpt.etcordflag
              , (SELECT term.termengnm diagengnm
                   FROM emr.mmohdiag diag,                     -- .........
                        emr.mrtmterm term                      -- .....
                  WHERE diag.pid       = otpt.pid
                    AND diag.orddd     = otpt.orddd
                    AND diag.cretno    = otpt.cretno
                    AND diag.orddeptcd = otpt.orddeptcd
                    AND diag.instcd    = otpt.instcd
                    AND diag.diagcd    = term.termcd
                    AND diag.diagdd   >= term.termfromdd
                    AND diag.diagdd   <= term.termtodd
                    AND diag.instcd    = term.instcd
                    AND diag.diaghistcd     = 'O'
                    AND diag.diagtypecd     = 'D'
                    AND diag.diagkindcdflag = 'M'
                    AND ROWNUM = 1)                          AS diagnm                                             -- ...
        , otpt.instcd
        , otpt.orddd
        , otpt.cretno
        , otpt.orddrid
              , otpt.prcptdayaftrcptyn
              , otpt.rcptvipresncd
              , otpt.rcptvipetcresn
              , otpt.medamtpostyn
              , CASE WHEN (SELECT a.rcptdelivefact
                             FROM pam.pmchcapm a
                            WHERE a.instcd = otpt.instcd
                              AND a.pid    = otpt.pid
                              AND a.civilaplendflagcd IN ('Y', 'R')
                              AND a.drdelive = 'Y'
                              AND a.civilaplclamdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND a.civilenddd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                              AND rownum = 1  ) is not NULL THEN 'Y'
                                                            ELSE 'N'
                     END                                                                     AS civilap
     ,   DECODE (otpt.ordstartdt, '00000000000000', null, SUBSTR(otpt.ordstartdt, 9, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 11, 2) ||':'||
                                                          SUBSTR(otpt.ordstartdt, 13, 2)         ) AS ordstartdt          -- ......
              ,   CASE WHEN otpt.dracptyn ='Y' THEN (DECODE (otpt.dracptdt,   '-', null, SUBSTR(otpt.dracptdt, 9, 2)  ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 11, 2) ||':'||
                                                                                         SUBSTR(otpt.dracptdt, 13, 2)   )  )
                        END                                      AS dracptdt
              ,   CASE WHEN otpt.nursacptyn ='Y' THEN (DECODE (otpt.nursacptdt, '-', null, SUBSTR(otpt.nursacptdt, 9, 2)  ||':'||
                                                                                           SUBSTR(otpt.nursacptdt, 11, 2)         ) )
                        END                                AS nursacptdt
              ,   (SELECT CASE WHEN count(1) > 0 THEN  'Y'
                               ELSE 'N'
                           END
                     FROM pam.pmohotpt totp
                    WHERE otpt.pid           =       totp.pid
                      AND otpt.instcd        =       totp.instcd
                      AND otpt.orddd         >=      to_char(to_date(totp.orddd,  'YYYYMMDD')-7, 'YYYYMMDD')
                      AND otpt.orddd         <=       totp.orddd
                      AND otpt.cretno        !=       totp.cretno
                      AND totp.ordtype       =       'E'
                      AND rownum             =        1 )                       AS   eryn                 -- ER..
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                              ELSE 'N'
                         END
                    FROM emr.mmrhcnstinfo info
                   WHERE info.pid           =        otpt.pid
                     AND info.instcd        =        otpt.instcd
                     AND info.orddd         =        otpt.orddd
                     AND info.cretno        =        otpt.cretno
                     AND info.cnststat      in       ('12', '20', '21', '22', '30', '31', '32', '40')
                     AND rownum             =        1 )                        AS   coordyn            -- ....
              ,  (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                             ELSE 'N'
                         END
                   FROM dual
                  WHERE (SELECT  CASE WHEN count(1) > 0 THEN 'Y'
                                      ELSE 'N'
                                  END
                           FROM  emr.mmrmformrec mrec,
                                 emr.mmrdrechist hist
                          WHERE  mrec.instcd       =      hist.instcd
                            AND  mrec.formrecseq   =      hist.formrecseq
                            AND  mrec.lastrechistseq = hist.rechistseq
                            AND  mrec.delyn        =      'N'
                            AND  mrec.valiyn       =      'Y'
                            AND  mrec.pid          =      otpt.pid
                            AND  mrec.orddd        =      otpt.orddd
                            AND  mrec.cretno       =      otpt.cretno
                            AND  hist.ESPISEQ      =      '0'
                            AND  rownum            =       1 ) ='Y'
                     OR
                        (SELECT CASE WHEN count(1) > 0 THEN 'Y'
                                     ELSE 'N'
                                 END
                           FROM emr.mmohoprc oprc
                          WHERE oprc.instcd          =    otpt.instcd
                            AND oprc.pid             =    otpt.pid
                            AND oprc.orddd           =    otpt.orddd
                            AND oprc.cretno          =    otpt.cretno
                            AND oprc.prcphistcd      = 'T'
                            AND rownum               =    1 ) = 'Y'
                            AND rownum = 1 )                                  AS tempsaveyn          -- ......
              ,  (SELECT CASE WHEN  sasm.choiflag in ( 'B',  'C')  THEN 'Y'
                             ELSE 'N'
                         END
                        FROM pam.pmcmspif sasm --  pam.pmcmsasm sasm
                       WHERE sasm.instcd     =  otpt.instcd
                         AND sasm.pid        =  otpt.pid
                         AND sasm.orddeptcd  =  otpt.orddeptcd
                         AND sasm.orddrid    =  otpt.orddrid
                         AND sasm.ioflag     =  'O'
                         AND sasm.histstat   =  'Y'
                         AND sasm.specordyn  =  'Y'
                         AND sasm.fromdd     <= otpt.orddd
                         AND sasm.todd       >= otpt.orddd
                         AND rownum          =  1 )                          AS specordtype        --  ....
    ,(SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
               END
        FROM dual
       WHERE (
            SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohoprc oprc
             WHERE oprc.pid          = otpt.pid
               AND oprc.prcpdd       >=     (SELECT NVL(MAX(orddd), '20130605') AS fromdd
                                               FROM pam.pmohotpt
                                              WHERE instcd = '053'
                                                AND pid = otpt.pid
                                                AND orddd < '20130605'
                                                AND elbulbodstat = '2'
                                                AND histstat = 'R')
               AND oprc.prcpdd        <= otpt.orddd
               AND oprc.prcphistcd   = 'O'
               AND oprc.prcpflag     = '1'
               AND oprc.prcpstatcd   > '700'
               AND ROWNUM = 1 ) = 'Y'
          OR
            (SELECT CASE WHEN COUNT(1) > 0 THEN 'Y'
                                  ELSE 'N'
                    END
              FROM emr.mmohiprc iprc
             WHERE iprc.pid          = otpt.pid
               AND iprc.prcpdd       >=     ( SELECT NVL(MAX(dschdd), '20130605') AS formdd
                                                FROM pam.pmihinpt
                                               WHERE instcd = '053'
                                                 AND pid = otpt.pid
                                                 AND dschdd != '99991231'
                                                 AND histstat = 'Y')
               AND iprc.prcpdd        <= otpt.orddd
               AND iprc.prcphistcd   = 'O'
               AND iprc.prcpflag     = '1'
               AND iprc.prcpstatcd   > '700'
               AND ROWNUM = 1) = 'Y') as rsltyn         -- ....
      , otpt.insukind as insukind
    --, DECODE(code.cdid, '11','..', '21','..', '22','..', substr(code.cdnm,0,2)) insukind  --..(...2.....)
    , (SELECT NVL(MAX(a.orddd), otpt.orddd)
                     FROM pam.pmohotpt a
                    WHERE a.instcd = ptbs.instcd
                      AND a.pid = ptbs.pid
                      AND a.elbulbodstat = '2' --...... ..
                      AND a.orddd < otpt.orddd
                      AND a.histstat in ( 'R', 'T' )
                      ) as recsrchfromdd       --.... ....
                , (SELECT CASE WHEN count(*) = 1 then 'Y'
                               ELSE 'N'
                               END
                      FROM emr.mmrmformrec a,
                           emr.mmrdrechist b
                     WHERE a.instcd = otpt.instcd
                       AND a.pid = otpt.pid
                       AND a.chosflag = 'O'
                       AND a.lastformrecdd >= (SELECT NVL(MAX(a.orddd), otpt.orddd)
                                                 FROM pam.pmohotpt a
                                                WHERE a.instcd = otpt.instcd
                                                  AND a.pid = otpt.pid
                                                  AND a.orddeptcd = otpt.orddeptcd
                                                  AND a.orddrid = otpt.orddrid
                                                  AND a.orddd < otpt.orddd)
                       AND a.lastformrecdd <= otpt.orddd
                       AND a.valiyn = 'Y'
                       AND a.delyn = 'N'
                       AND b.instcd  = a.instcd
                       AND b.rechistseq = a.lastrechistseq
                       AND b.espiseq != 0
                       AND rownum = 1) as recyn
                  , (SELECT CASE WHEN count(*) = 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                       FROM emr.mmohoprc oprc
                      WHERE oprc.instcd = otpt.instcd
                        AND oprc.pid = otpt.pid
                        AND oprc.orddd = otpt.orddd
                        AND oprc.cretno = otpt.cretno
                        AND oprc.prcphistcd IN ('O', 'X', 'M')      -- Dump. ....
            AND oprc.prcpkindcd NOT IN ('30', '35', '40') -- ..... .. . .... ... ..
                        AND rownum = 1
                        ) as prcpyn
                   , (SELECT CASE WHEN count(*) >= 1 THEN 'Y'
                                 ELSE 'N'
                                 END
                        FROM emr.mmrdmemo memo
                       WHERE memo.instcd = otpt.instcd
                         AND memo.pid = otpt.pid
                   --AND memo.deptcd = otpt.orddeptcd
                   AND (memo.memo IS NOT NULL OR nursememo IS NOT NULL)
                   AND memo.useflag IN ('P', 'D') --.... .. .. (P: .., D: ...)
                   ) as memoyn
                   , (SELECT  RTRIM (XMLAGG (XMLELEMENT ("a", cccl.conctypenm || ',')).EXTRACT ('//text()').getstringval (), ',') a
                        FROM  emr.mmohccpt ccpt
                            , emr.mmomcccl cccl
                       WHERE ccpt.conckindcd = cccl.conckindcd
                         AND ccpt.conctrgtcd = cccl.conctrgtcd
                         AND ccpt.conctypecd = cccl.conctypecd
                         AND ccpt.instcd     = cccl.instcd
                         AND ccpt.valifromdd <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                         AND ccpt.valitodd   >= TO_CHAR(SYSDATE, 'YYYYMMDD')
                      AND ccpt.instcd      = '053'
                      AND ccpt.pid         = otpt.pid
                      AND ccpt.conctrgtcd   = CASE WHEN ccpt.conckindcd = 'D'  THEN otpt.orddeptcd                                                                             ELSE '49170'
                                                                          END   )           AS conctypenm --....
       , otpt.rsrvflag
       , DECODE(   (SELECT 1
                         FROM emr.mrchmrhs mrhs
                        WHERE mrhs.instcd = otpt.instcd
                          AND mrhs.pid = otpt.pid
                          AND mrhs.ioflag = otpt.ordtype
                          AND mrhs.chrtflag = 'P'
                          AND mrhs.deldt >= SYSTIMESTAMP
                          AND ROWNUM  =1) , 1, 'Y', ''
                ) AS chartscanyn, --chart scan ..
       CASE WHEN DRACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(SUBSTR(DRACPTDT,9,4),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         WHEN NURSACPTYN = 'Y' THEN SUBSTR(NUMTODSINTERVAL(TO_DATE(TO_CHAR(sysdate,'hh24mi'),'hh24mi') - TO_DATE(SUBSTR(NURSACPTDT,9,4),'hh24mi'),'day'),12,5)
         ELSE '-' END AS waittm--.... lsh
        /*,(SELECT CASE WHEN (SELECT hospgrade FROM ast.arhmchsp
                                        WHERE coophospcd = reqp.coophospcd
                                          AND instcd = reqp.instcd)  IN ('C1', 'C2', 'C3') THEN 'refer(..)'
                                 ELSE 'refer' END
                       FROM ast.arcmreqp reqp
                      WHERE reqp.pid = otpt.pid
                        AND reqp.instcd = otpt.instcd
                        AND reqp.orddeptcd = otpt.orddeptcd
                        AND otpt.orddd BETWEEN reqp.rgstdd and reqp.enddd
                        AND reqp.deldd = '00000000'
                        AND ROWNUM = 1)*/
                  ,DECODE( otpt.RQSTFLAG, '1','Y', '-')            AS refer
                        , DECODE((SELECT 'Y'
                           FROM emr.mnohdlvt z
                           WHERE z.instcd = otpt.instcd
                           AND z.pid = otpt.pid
                           AND z.seqno = (SELECT max(y.seqno)
                                            FROM emr.mnohdlvt y
                                           WHERE y.instcd = z.instcd
                                             AND y.pid = z.pid
                                             AND y.statcd = 'Y'
                                             AND rownum = 1)
                           AND (z.EXPTDLIVDD > = to_char(sysdate, 'YYYYMMDD') OR z.EXPTDLIVDD IS NULL) --....
                         ),'Y','01', case when ptbs.sex = 'F' and to_number( COM.FN_ZZ_GETAGE('-', '-', otpt.orddd, 'B', ptbs.brthdd)) between 15 and 55 then '02'
                            else '' end ) AS gravdpsbldliv  -- ....//  /*.... 01 .... 02 .... null. .. > .. ...... */
                , CASE WHEN  otpt.suppkind  IN ( '06' , '11', '61' , '62' , '63' ) THEN  '..'
                       WHEN  otpt.suppkind = '07' THEN '..'
                       WHEN  otpt.suppkind  IN ( '50' , '51' ) THEN  '..'
                       ELSE '-'
                  END as serdiag
               ,  decode( ( SELECT 'C'
                              FROM emr.mmohoprc oprc
                             WHERE oprc.instcd = otpt.instcd
                               AND oprc.pid = otpt.pid
                               AND oprc.orddd = otpt.orddd
                               AND oprc.cretno = otpt.cretno
                               AND oprc.prcphistcd = 'O'
                               AND oprc.prcpcd like 'XSCAN%'
                               AND ROWNUM = 1), 'C', 'C', decode(otpt.outercdrgstyn, 'N', '', otpt.outercdrgstyn )) as outercdrgstyn -- ....
               , decode(ptbs.kioskrcptnoyn, 'N','Y','') as kioskrcptnoyn  --....
               , CASE WHEN otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'E', 'S', 'P', 'B') AND otpt.etcordflag NOT IN ('M','J','N', '07') THEN ''
                      ELSE 'Y'
                 END as tempacpt
               , nvl((select bmtflag from emr.mmohpbmt pbmt WHERE pbmt.instcd = otpt.instcd AND pbmt.pid = otpt.pid and pbmt.histcd='O'),'-') as bmtflag
               , LPAD(NVL2(owgm.ordrsrvseqno,owgm.ordrsrvseqno,'0000'),4,0) as ordrsrvseqno     --......
               , owgm.ordrsrvtm                                                                 --......
          FROM  pam.pmohotpt otpt
        , pam.pmcmptbs ptbs
        , com.zbcmcode code
        , emr.mnvhwgim owgm
   WHERE otpt.histstat IN ('R', 'T')                                            -- .., .......
     AND otpt.ordtype  IN ('O', 'E')
     AND otpt.pid    = ptbs.pid
     AND otpt.instcd  = ptbs.instcd
     AND code.cdid     = otpt.insukind
     AND code.cdgrupid = 'P0008'
     AND  owgm.instcd(+) = otpt.instcd
           AND  owgm.pid(+)    = otpt.pid
        AND  owgm.orddd(+)  = otpt.orddd
        AND  owgm.cretno(+) = otpt.cretno
           AND otpt.orddd    = '20130605'
           AND otpt.instcd   = '053'
           AND otpt.orddeptcd    = 'ME'
           AND otpt.orddrid      = '49170'
           AND otpt.rsrvflag NOT IN ('3', '4', '8', 'A', 'S', 'P', 'B') --..., ...., ...., ....., .... .., ......, ..... ..
              --.... ... ..... .. ..
           AND NVL(otpt.etcordflag, '-') NOT IN ('M','J', 'N', '07')                 -- ...., .., .... ..
      ORDER BY owgm.ordrsrvtm, ordrsrvseqno, otpt.orddd, otpt.ordtm, otpt.NURSACPTDT , otpt.fstacptdt
     /* himed/his/emr/prcpmngtmgr/etcprcpmngtmgt/dao/sqls/etcprcpmngtdao_sqls.xml getSpeedOutPatList */
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.08          0         78          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.05       0.08          0       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.10       0.16          0       8175          0         105
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
       104        104        104  COUNT STOPKEY (cr=838 pr=0 pw=0 time=4265 us)
       104        104        104   NESTED LOOPS  (cr=838 pr=0 pw=0 time=3759 us)
       104        104        104    NESTED LOOPS  (cr=734 pr=0 pw=0 time=3087 us cost=5 size=131 card=1)
       104        104        104     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=422 pr=0 pw=0 time=2121 us cost=4 size=58 card=1)
       104        104        104      TABLE ACCESS BY LOCAL INDEX ROWID MMOHDIAG PARTITION: KEY KEY (cr=422 pr=0 pw=0 time=1841 us cost=4 size=58 card=1)
       161        161        161       INDEX RANGE SCAN IX_MMOHDIAG_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1232 us cost=3 size=0 card=1)(object id 796990)
       104        104        104     INDEX RANGE SCAN PK_MRTMTERM (cr=312 pr=0 pw=0 time=824 us cost=1 size=0 card=1)(object id 766242)
       104        104        104    TABLE ACCESS BY INDEX ROWID MRTMTERM (cr=104 pr=0 pw=0 time=437 us cost=2 size=73 card=1)
         0          0          0  COUNT STOPKEY (cr=213 pr=0 pw=0 time=1300 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=213 pr=0 pw=0 time=1049 us cost=3 size=163 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCHCAPM PARTITION: KEY KEY (cr=213 pr=0 pw=0 time=849 us cost=3 size=163 card=1)
         1          1          1     INDEX RANGE SCAN PK_PMCHCAPM PARTITION: KEY KEY (cr=212 pr=0 pw=0 time=705 us cost=2 size=0 card=1)(object id 775359)
       105        105        105  SORT AGGREGATE (cr=120 pr=0 pw=0 time=1344 us)
         0          0          0   COUNT STOPKEY (cr=120 pr=0 pw=0 time=1115 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=899 us cost=5 size=29 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=647 us cost=5 size=29 card=1)
         0          0          0      INDEX RANGE SCAN IX_PMOHOTPT_05 PARTITION: KEY KEY (cr=120 pr=0 pw=0 time=503 us cost=4 size=0 card=1)(object id 774419)
       105        105        105  SORT AGGREGATE (cr=315 pr=0 pw=0 time=1848 us)
         0          0          0   COUNT STOPKEY (cr=315 pr=0 pw=0 time=1645 us)
         0          0          0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1416 us cost=4 size=30 card=1)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRHCNSTINFO PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1170 us cost=4 size=30 card=1)
         0          0          0      INDEX RANGE SCAN IX_MMRHCNSTINFO_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1025 us cost=3 size=0 card=1)(object id 767676)
       105        105        105  SORT AGGREGATE (cr=1147 pr=0 pw=0 time=9436 us)
         7          7          7   COUNT  (cr=1147 pr=0 pw=0 time=9259 us)
         7          7          7    FILTER  (cr=1147 pr=0 pw=0 time=9144 us)
       105        105        105     FAST DUAL  (cr=0 pr=0 pw=0 time=61 us cost=2 size=0 card=1)
       105        105        105     SORT AGGREGATE (cr=651 pr=0 pw=0 time=5096 us)
         7          7          7      COUNT STOPKEY (cr=651 pr=0 pw=0 time=4908 us)
         7          7          7       NESTED LOOPS  (cr=651 pr=0 pw=0 time=4660 us)
         7          7          7        NESTED LOOPS  (cr=644 pr=0 pw=0 time=4485 us cost=10 size=78 card=1)
         7          7          7         PARTITION LIST ALL PARTITION: 1 4 (cr=623 pr=0 pw=0 time=4323 us cost=9 size=51 card=1)
         7          7          7          TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: 1 4 (cr=623 pr=0 pw=0 time=3955 us cost=9 size=51 card=1)
         7          7          7           INDEX RANGE SCAN IX_MMRMFORMREC_03 PARTITION: 1 4 (cr=616 pr=0 pw=0 time=2499 us cost=8 size=0 card=1)(object id 767726)
         7          7          7         INDEX RANGE SCAN IX_MMRDRECHIST_02 (cr=21 pr=0 pw=0 time=61 us cost=1 size=0 card=1)(object id 766493)
         7          7          7        TABLE ACCESS BY GLOBAL INDEX ROWID MMRDRECHIST PARTITION: ROW LOCATION ROW LOCATION (cr=7 pr=0 pw=0 time=49 us cost=1 size=27 card=1)
        98         98         98     SORT AGGREGATE (cr=496 pr=0 pw=0 time=3224 us)
         0          0          0      COUNT STOPKEY (cr=496 pr=0 pw=0 time=3035 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=496 pr=0 pw=0 time=2778 us cost=4 size=29 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=496 pr=0 pw=0 time=2527 us cost=4 size=29 card=1)
       824        824        824         INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=300 pr=0 pw=0 time=1543 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  COUNT STOPKEY (cr=456 pr=0 pw=0 time=2705 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=456 pr=0 pw=0 time=2423 us cost=4 size=49 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID PMCMSPIF PARTITION: KEY KEY (cr=456 pr=0 pw=0 time=2186 us cost=4 size=49 card=1)
       608        608        608     INDEX RANGE SCAN IX_PMCMSPIF_01 PARTITION: KEY KEY (cr=321 pr=0 pw=0 time=1507 us cost=3 size=0 card=1)(object id 775434)
       105        105        105  SORT AGGREGATE (cr=1197 pr=0 pw=0 time=8690 us)
        74         74         74   FILTER  (cr=1197 pr=0 pw=0 time=8469 us)
       105        105        105    FAST DUAL  (cr=0 pr=0 pw=0 time=68 us cost=2 size=0 card=1)
       105        105        105    SORT AGGREGATE (cr=809 pr=0 pw=0 time=5066 us)
        72         72         72     COUNT STOPKEY (cr=809 pr=0 pw=0 time=4854 us)
        72         72         72      PARTITION LIST ALL PARTITION: 1 4 (cr=809 pr=0 pw=0 time=4612 us cost=8 size=27 card=1)
        72         72         72       INDEX RANGE SCAN IX_MMOHOPRC_19 PARTITION: 1 4 (cr=809 pr=0 pw=0 time=4284 us cost=8 size=27 card=1)(object id 794293)
       105        105        105        SORT AGGREGATE (cr=317 pr=0 pw=0 time=1888 us)
       104        104        104         PARTITION LIST SINGLE PARTITION: 2 2 (cr=317 pr=0 pw=0 time=1620 us cost=4 size=27 card=1)
       104        104        104          FIRST ROW  (cr=317 pr=0 pw=0 time=1392 us cost=4 size=27 card=1)
       104        104        104           INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_07 PARTITION: 2 2 (cr=317 pr=0 pw=0 time=1278 us cost=4 size=27 card=1)(object id 818350)
        33         33         33    SORT AGGREGATE (cr=388 pr=0 pw=0 time=2810 us)
         2          2          2     COUNT STOPKEY (cr=388 pr=0 pw=0 time=2742 us)
         2          2          2      PARTITION LIST ALL PARTITION: 1 4 (cr=388 pr=0 pw=0 time=2670 us cost=13 size=27 card=1)
         2          2          2       TABLE ACCESS BY LOCAL INDEX ROWID MMOHIPRC PARTITION: 1 4 (cr=388 pr=0 pw=0 time=2559 us cost=13 size=27 card=1)
       255        255        255        INDEX RANGE SCAN IX_MMOHIPRC_03 PARTITION: 1 4 (cr=296 pr=0 pw=0 time=1718 us cost=12 size=0 card=1)(object id 767321)
        33         33         33         SORT AGGREGATE (cr=68 pr=0 pw=0 time=532 us)
        27         27         27          PARTITION LIST SINGLE PARTITION: 2 2 (cr=68 pr=0 pw=0 time=453 us cost=3 size=25 card=1)
        27         27         27           FIRST ROW  (cr=68 pr=0 pw=0 time=383 us cost=3 size=25 card=1)
        27         27         27            INDEX RANGE SCAN (MIN/MAX) IX_PMIHINPT_11 PARTITION: 2 2 (cr=68 pr=0 pw=0 time=354 us cost=3 size=25 card=1)(object id 795657)
       105        105        105  SORT AGGREGATE (cr=660 pr=0 pw=0 time=8139 us)
      5369       5369       5369   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=660 pr=0 pw=0 time=8038 us cost=1 size=27 card=1)
      5369       5369       5369    INLIST ITERATOR  (cr=660 pr=0 pw=0 time=5547 us)
      5369       5369       5369     INDEX RANGE SCAN IX_PMOHOTPT_07 PARTITION: KEY KEY (cr=660 pr=0 pw=0 time=3853 us cost=1 size=27 card=1)(object id 818350)
       105        105        105  SORT AGGREGATE (cr=855 pr=0 pw=0 time=5517 us)
         0          0          0   COUNT STOPKEY (cr=855 pr=0 pw=0 time=5300 us)
         0          0          0    NESTED LOOPS  (cr=855 pr=0 pw=0 time=4983 us)
        50         50         50     NESTED LOOPS  (cr=804 pr=0 pw=0 time=4616 us cost=5 size=56 card=1)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY KEY (cr=679 pr=0 pw=0 time=3864 us cost=4 size=39 card=1)
        50         50         50       TABLE ACCESS BY LOCAL INDEX ROWID MMRMFORMREC PARTITION: KEY KEY (cr=679 pr=0 pw=0 time=3593 us cost=4 size=39 card=1)
        50         50         50        INDEX RANGE SCAN IX_MMRMFORMREC_01 PARTITION: KEY KEY (cr=633 pr=0 pw=0 time=3246 us cost=3 size=0 card=1)(object id 767736)
       105        105        105         SORT AGGREGATE (cr=318 pr=0 pw=0 time=1872 us)
        98         98         98          PARTITION LIST SINGLE PARTITION: KEY KEY (cr=318 pr=0 pw=0 time=1656 us cost=4 size=33 card=1)
        98         98         98           FIRST ROW  (cr=318 pr=0 pw=0 time=1383 us cost=4 size=33 card=1)
        98         98         98            INDEX RANGE SCAN (MIN/MAX) IX_PMOHOTPT_08 PARTITION: KEY KEY (cr=318 pr=0 pw=0 time=1270 us cost=4 size=33 card=1)(object id 795652)
        50         50         50      PARTITION LIST SINGLE PARTITION: KEY(AP) KEY(AP) (cr=125 pr=0 pw=0 time=570 us cost=1 size=0 card=1)
        50         50         50       INDEX UNIQUE SCAN PK_MMRDRECHIST PARTITION: KEY(AP) KEY(AP) (cr=125 pr=0 pw=0 time=407 us cost=1 size=0 card=1)(object id 767661)
         0          0          0     TABLE ACCESS BY LOCAL INDEX ROWID MMRDRECHIST PARTITION: 1 1 (cr=51 pr=0 pw=0 time=247 us cost=1 size=17 card=1)
       105        105        105  SORT AGGREGATE (cr=419 pr=0 pw=0 time=2296 us)
       104        104        104   COUNT STOPKEY (cr=419 pr=0 pw=0 time=2047 us)
       104        104        104    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=419 pr=0 pw=0 time=1720 us cost=4 size=32 card=1)
       104        104        104     TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=419 pr=0 pw=0 time=1415 us cost=4 size=32 card=1)
       104        104        104      INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=864 us cost=3 size=0 card=1)(object id 767381)
       105        105        105  SORT AGGREGATE (cr=41 pr=0 pw=0 time=747 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID MMRDMEMO (cr=41 pr=0 pw=0 time=546 us cost=4 size=41 card=1)
         0          0          0    INDEX RANGE SCAN PK_MMRDMEMO (cr=41 pr=0 pw=0 time=442 us cost=2 size=0 card=1)(object id 765770)
       105        105        105  SORT AGGREGATE (cr=114 pr=0 pw=0 time=8098 us)
         0          0          0   NESTED LOOPS  (cr=114 pr=0 pw=0 time=1438 us)
         0          0          0    NESTED LOOPS  (cr=114 pr=0 pw=0 time=1307 us cost=4 size=65 card=1)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=114 pr=0 pw=0 time=1205 us cost=3 size=42 card=1)
         0          0          0      TABLE ACCESS BY LOCAL INDEX ROWID MMOHCCPT PARTITION: 2 2 (cr=114 pr=0 pw=0 time=1023 us cost=3 size=42 card=1)
         6          6          6       INDEX RANGE SCAN IX_MMOHCCPT_01 PARTITION: 2 2 (cr=108 pr=0 pw=0 time=808 us cost=1 size=0 card=1)(object id 779517)
         0          0          0     PARTITION LIST SINGLE PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)
         0          0          0      INDEX UNIQUE SCAN PK_MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 767656)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOMCCCL PARTITION: 2 2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=184 card=8)
        40         40         40  COUNT STOPKEY (cr=355 pr=0 pw=0 time=2061 us)
        40         40         40   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=355 pr=0 pw=0 time=1757 us cost=4 size=29 card=1)
        40         40         40    TABLE ACCESS BY LOCAL INDEX ROWID MRCHMRHS PARTITION: KEY KEY (cr=355 pr=0 pw=0 time=1460 us cost=4 size=29 card=1)
        40         40         40     INDEX RANGE SCAN IX_MRCHMRHS_01 PARTITION: KEY KEY (cr=315 pr=0 pw=0 time=1039 us cost=3 size=0 card=1)(object id 767891)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=114 pr=0 pw=0 time=1268 us cost=3 size=23 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=114 pr=0 pw=0 time=984 us cost=3 size=23 card=1)
         0          0          0    INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=114 pr=0 pw=0 time=815 us cost=2 size=0 card=1)(object id 767826)
         2          2          2     SORT AGGREGATE (cr=6 pr=0 pw=0 time=51 us)
         0          0          0      COUNT STOPKEY (cr=6 pr=0 pw=0 time=37 us)
         0          0          0       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=6 pr=0 pw=0 time=31 us cost=3 size=19 card=1)
         0          0          0        TABLE ACCESS BY LOCAL INDEX ROWID MNOHDLVT PARTITION: KEY KEY (cr=6 pr=0 pw=0 time=28 us cost=3 size=19 card=1)
         6          6          6         INDEX RANGE SCAN PK_MNOHDLVT PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=19 us cost=2 size=0 card=1)(object id 767826)
         0          0          0  COUNT STOPKEY (cr=526 pr=0 pw=0 time=2857 us)
         0          0          0   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=526 pr=0 pw=0 time=2554 us cost=4 size=38 card=1)
         0          0          0    TABLE ACCESS BY LOCAL INDEX ROWID MMOHOPRC PARTITION: KEY KEY (cr=526 pr=0 pw=0 time=2320 us cost=4 size=38 card=1)
       884        884        884     INDEX RANGE SCAN IX_MMOHOPRC_01 PARTITION: KEY KEY (cr=321 pr=0 pw=0 time=1394 us cost=3 size=0 card=1)(object id 767381)
         0          0          0  PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=742 us cost=2 size=18 card=1)
         0          0          0   TABLE ACCESS BY LOCAL INDEX ROWID MMOHPBMT PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=480 us cost=2 size=18 card=1)
         0          0          0    INDEX RANGE SCAN IX_MMOHPBMT_01 PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=313 us cost=1 size=0 card=1)(object id 779522)
       105        105        105  SORT ORDER BY (cr=8097 pr=0 pw=0 time=84438 us cost=13 size=228 card=1)
       105        105        105   WINDOW SORT (cr=724 pr=0 pw=0 time=7849 us cost=13 size=228 card=1)
       105        105        105    NESTED LOOPS OUTER (cr=724 pr=0 pw=0 time=13364 us cost=11 size=228 card=1)
       105        105        105     NESTED LOOPS  (cr=512 pr=0 pw=0 time=11160 us cost=9 size=191 card=1)
       105        105        105      NESTED LOOPS  (cr=194 pr=0 pw=0 time=2194 us cost=8 size=138 card=1)
       105        105        105       PARTITION LIST SINGLE PARTITION: 2 2 (cr=185 pr=0 pw=0 time=1454 us cost=7 size=254 card=2)
       105        105        105        TABLE ACCESS BY LOCAL INDEX ROWID PMOHOTPT PARTITION: 2 2 (cr=185 pr=0 pw=0 time=1344 us cost=7 size=254 card=2)
       347        347        347         INDEX RANGE SCAN IX_PMOHOTPT_01 PARTITION: 2 2 (cr=5 pr=0 pw=0 time=384 us cost=4 size=0 card=1)(object id 796979)
       105        105        105       INDEX UNIQUE SCAN UK_ZBCMCODE_02 (cr=9 pr=0 pw=0 time=258 us cost=1 size=11 card=1)(object id 96118)
       105        105        105      PARTITION LIST SINGLE PARTITION: 2 2 (cr=318 pr=0 pw=0 time=1485 us cost=1 size=53 card=1)
       105        105        105       TABLE ACCESS BY LOCAL INDEX ROWID PMCMPTBS PARTITION: 2 2 (cr=318 pr=0 pw=0 time=1229 us cost=1 size=53 card=1)
       105        105        105        INDEX UNIQUE SCAN PK_PMCMPTBS PARTITION: 2 2 (cr=212 pr=0 pw=0 time=652 us cost=1 size=0 card=1)(object id 774609)
         0          0          0     TABLE ACCESS BY INDEX ROWID MNVHWGIM (cr=212 pr=0 pw=0 time=1214 us cost=2 size=37 card=1)
         0          0          0      INDEX RANGE SCAN PK_MNVHWGIM (cr=212 pr=0 pw=0 time=896 us cost=1 size=0 card=1)(object id 765479)
 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  SQL*Net message from client                     8        2.88          3.80
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.05       0.08          0         78          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        8      0.05       0.08          0       8097          0         105
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.10       0.16          0       8175          0         105
 
Misses in library cache during parse: 1
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      10        0.00          0.00
  SQL*Net message from client                    10        8.30         19.75
  SQL*Net more data from client                   2        0.00          0.00
 

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
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          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1
 
Misses in library cache during parse: 0
 
    2  user  SQL statements in session.
    1  internal SQL statements in session.
    3  SQL statements in session.
********************************************************************************
Trace file: HISEDU_ora_3998130.trc
Trace file compatibility: 11.1.0.7
Sort options: default
 
       1  session in tracefile.
       2  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
     546  lines in trace file.
      11  elapsed seconds in trace file.
 

댓글 (2)