---------------------------------------------------------------------------- Time Breakdown (seconds) ---------------------------------------------------------------------------- elapsed 73 total wait 33 cpu 40 unaccounted -1 idle 17 ---------------------------------------------------------------------------- Summary of Activity in trace file ---------------------------------------------------------------------------- event count total secs avg ms 1) ELAPSED 73 2) CPU 40 3) db file sequential read 10037 32 3.256 4) SQL*Net message from client 2 17 8550.436 5) db file scattered read 35 1 34.235 6) SQL*Net message to client 2 0 0.002 ---------------------------------------------------------------------------- Histogram of latencies for: db file sequential read ---------------------------------------------------------------------------- 0 64u .1m .2m .5m 1m 2m 4m 8m 16m 33m 65m .1s .1s+ 0 846 38 27 5335 138 139 397 1585 1327 165 38 2 ---------------------------------------------------------------------------- Histogram of latencies by I/O size in # of blocks for: db file scattered read direct path read direct path read temp ---------------------------------------------------------------------------- 0 64u .1m .2m .5m 1m 2m 4m 8m 16m 33m 65m .1s .1s+ db file scattered read 63 0 0 0 0 0 0 0 0 0 0 16 13 1 61 0 0 0 0 0 0 0 0 0 0 3 1 0 16 0 0 0 0 0 0 0 0 0 0 1 0 0 ---------------------------------------------------------------------------- Breakdown by SQL Statement ---------------------------------------------------------------------------- SQL ID PLAN HASH ELAPSED CPU WAITS MISSING CURN PARSING IN CURSOR #2 len=654 dep=0 uid=0 oct=6 lid=0 tim=13859073118840 hv=3733909562 ad='bda701c8 0 73 40 33 -1 2_1 0 0 0 0 3_ PARSING IN CURSOR #1 len=37 dep=1 uid=0 oct=3 lid=0 tim=13859072955777 hv=1398610540 ad='bdb8f550 0 0 0 0 0 1_1 --------------------------- SQL ID: PARSING IN CURSOR #2 len=654 dep=0 uid=0 oct=6 lid=0 tim=13859073118840 hv=3733909562 ad='bda701c8 PLAN HASH:0 CURSOR # :2_1 update dsrdba.forecast f set value=0 where 0<(select count(*) from dwhdba.acustomer_ship_to b, dwhdba.afinished_good c, dsrdba.man_fore_entry m where f.acustomer_ship_to__code=b.code and f.finished_good__code=c.code and b.region__code like m.region__code and b.sales_area__code like m.sales_area__code and b.ship_to_group__code like m.ship_to_group__code and c.product_group__code like m.product_group__code and c.product_division__code like m.product_division__code and c.product_pnl_family__code like m.product_pnl_family__code and c.commercial_product__code like m.commercial_product__code and m.month__code='M201204') ------------------------------------------------------------- events for quyery ------------------------------------------------------------- event count total secs avg ms ------------------------------ ---------- ---------- --------- ELAPSED 73 CPU 40 db file sequential read 10037 32 3.256 SQL*Net message from client 1 5 5408.174 db file scattered read 35 1 34.235 SQL*Net message to client 1 0 0.003 ------------------------------------------------------------- execution plan for quyery ------------------------------------------------------------- 'UPDATE FORECAST (cr=2068345 pr=12187 pw=0 time=73275907 us)' 'FILTER (cr=2068345 pr=12172 pw=0 time=73179486 us)' 'PARTITION RANGE ALL PARTITION: 1 4 (cr=2166 pr=2157 pw=0 time=1733285 us)' 'TABLE ACCESS FULL FORECAST PARTITION: 1 4 (cr=2166 pr=2157 pw=0 time=1511608 us)' 'NESTED LOOPS (cr=2066179 pr=10015 pw=0 time=70705165 us)' 'NESTED LOOPS (cr=1131343 pr=10012 pw=0 time=37230283 us)' 'TABLE ACCESS BY INDEX ROWID AFINISHED_GOOD_NEW (cr=568651 pr=8175 pw=0 time=20227871 us)' 'INDEX UNIQUE SCAN AFINISHED_GOOD_NEW_PK (cr=375128 pr=729 pw=0 time=2166172 us)' 'TABLE ACCESS BY INDEX ROWID ACUSTOMER_SHIP_TO (cr=562692 pr=1837 pw=0 time=16578162 us)' 'INDEX UNIQUE SCAN ACUST_SHIP_TO_IDX_CODE (cr=375128 pr=223 pw=0 time=2247964 us)' 'TABLE ACCESS BY INDEX ROWID MAN_FORE_ENTRY (cr=934836 pr=3 pw=0 time=33118049 us)' 'INDEX RANGE SCAN MAN_FORE_ENTRY_IDX_MM (cr=375128 pr=1 pw=0 time=2535274 us)' --------------------------- SQL ID: PLAN HASH: CURSOR # :3_ ------------------------------------------------------------- events for quyery ------------------------------------------------------------- event count total secs avg ms ------------------------------ ---------- ---------- --------- SQL*Net message from client 1 11 11692.698 SQL*Net message to client 1 0 0.001 ------------------------------------------------------------- execution plan for quyery ------------------------------------------------------------- --------------------------- SQL ID: PARSING IN CURSOR #1 len=37 dep=1 uid=0 oct=3 lid=0 tim=13859072955777 hv=1398610540 ad='bdb8f550 PLAN HASH:0 CURSOR # :1_1 select text from view$ where rowid=:1 ------------------------------------------------------------- events for quyery ------------------------------------------------------------- event count total secs avg ms ------------------------------ ---------- ---------- --------- ELAPSED 0 CPU 0 ------------------------------------------------------------- execution plan for quyery ------------------------------------------------------------- 'TABLE ACCESS BY USER ROWID VIEW$ (cr=2 pr=0 pw=0 time=30 us)' ---------------------------------------------------------------------------- I/O throughput per second (includes any files system cache reads) ---------------------------------------------------------------------------- time min mult MB/s max mult blocks mn single MB/s mx single MB/s dpr mx dpr MB/s dprt mx dprt 13859073 68.3 1.9 68.3 61 0.4 3.3 2083.3 0.0 0.0 0.0 0.0 13859087 64.9 2.0 64.9 63 0.6 6.0 1736.1 0.0 0.0 0.0 0.0 13859092 41.0 2.0 41.0 63 0.8 6.5 1838.2 0.0 0.0 0.0 0.0 13859097 42.9 2.0 42.9 63 1.0 7.9 1953.1 0.0 0.0 0.0 0.0 13859101 105.2 2.0 105.2 63 1.1 7.4 1953.1 0.0 0.0 0.0 0.0 13859104 89.0 2.0 89.0 63 0.9 7.6 1736.1 0.0 0.0 0.0 0.0 13859108 53.7 2.0 53.7 63 1.1 3.3 1644.7 0.0 0.0 0.0 0.0 13859109 42.6 2.0 42.6 63 1.5 2.7 1644.7 0.0 0.0 0.0 0.0 13859111 41.4 2.0 41.4 63 0.9 2.4 1644.7 0.0 0.0 0.0 0.0 13859112 44.9 2.0 44.9 63 0.9 2.8 1736.1 0.0 0.0 0.0 0.0 13859114 78.3 2.0 78.3 63 0.8 1.9 2232.1 0.0 0.0 0.0 0.0 13859115 82.7 2.0 82.7 63 1.2 1.8 1838.2 0.0 0.0 0.0 0.0 13859117 87.4 2.0 87.4 63 1.2 2.0 1420.5 0.0 0.0 0.0 0.0 13859119 33.5 3.9 97.3 63 0.9 2.9 1358.7 0.0 0.0 0.0 0.0 13859121 50.8 2.0 50.8 63 2.6 3.9 1736.1 0.0 0.0 0.0 0.0 13859123 58.4 2.0 58.4 63 1.6 2.7 1838.2 0.0 0.0 0.0 0.0 13859124 27.4 2.0 27.4 63 2.6 2.4 1736.1 0.0 0.0 0.0 0.0 13859126 113.7 2.0 113.7 63 2.8 1.5 1644.7 0.0 0.0 0.0 0.0 13859127 76.8 2.0 76.8 63 2.3 2.6 1488.1 0.0 0.0 0.0 0.0 13859129 99.8 2.0 99.8 63 3.0 2.3 1562.5 0.0 0.0 0.0 0.0 13859130 77.6 2.0 77.6 63 2.9 0.9 1644.7 0.0 0.0 0.0 0.0 13859131 71.5 2.0 71.5 63 3.4 0.7 1488.1 0.0 0.0 0.0 0.0 13859132 83.4 2.0 83.4 63 36.9 0.7 1562.5 0.0 0.0 0.0 0.0 13859133 37.3 2.0 37.3 63 59.2 0.7 1077.6 0.0 0.0 0.0 0.0 13859134 79.7 2.0 79.7 63 3.6 0.5 1562.5 0.0 0.0 0.0 0.0 13859135 30.4 2.0 30.4 63 3.1 0.7 1736.1 0.0 0.0 0.0 0.0 13859136 53.5 2.0 53.5 63 3.0 0.9 104.5 0.0 0.0 0.0 0.0 13859137 70.9 1.9 70.9 61 1.0 2.7 1420.5 0.0 0.0 0.0 0.0 13859139 78.7 1.9 78.7 61 1.3 2.5 1488.1 0.0 0.0 0.0 0.0 13859140 58.7 2.0 58.7 63 0.8 1.8 1644.7 0.0 0.0 0.0 0.0 13859142 69.6 2.0 69.6 63 1.5 1.4 106.3 0.0 0.0 0.0 0.0 13859143 30.5 2.0 30.5 63 2.7 1.0 1302.1 0.0 0.0 0.0 0.0 13859145 81.6 2.0 81.6 63 2.8 0.6 1562.5 0.0 0.0 0.0 0.0 13859146 27.4 0.5 27.4 16 3.3 0.5 1201.9 0.0 0.0 0.0 0.0 minute MB/s count 230984 2.95 5412 230985 3.40 4660